SELECT INTO and VALUES in DB2 for z/OS

SELECT INTO: one row into variables

The SELECT INTO statement produces a result table that contains at most one row and assigns the values in that row to targets. Targets can be host variables (with optional indicator variables), SQL variables or parameters inside SQL PL, global variables, or array elements in supported contexts. The number of items in the select list must equal the number of INTO targets, and a target must not appear more than once.

Think of SELECT INTO as “look up this key and put the columns in my working storage.” It is not a replacement for a cursor. If you need to walk many rows, declare a cursor and FETCH. If you need a single known row, SELECT INTO keeps the program shorter and avoids OPEN / FETCH / CLOSE for a one-shot read.

1
2
3
4
SELECT EMPNO, LASTNAME, SALARY
  INTO :HV-EMPNO, :HV-LASTNAME, :HV-SALARY:HV-SALARY-IND
  FROM DSN8C10.EMP
 WHERE EMPNO = :HV-EMPNO-IN;

Assignment follows Db2 assignment and comparison rules, left to right. If any assignment fails, Db2 does not leave a half-updated list of targets in a usable state for that statement—treat the whole INTO as failed and inspect SQLCODE / GET DIAGNOSTICS.

What each outcome means

Always check SQLCODE after SELECT INTO. A program that assumes SQLCODE 0 and then uses host variables after +100 is reading leftover values from the previous successful call. That class of bug is extremely common in payroll and inquiry transactions.

• SQLCODE +100 / SQLSTATE 02000 — empty result. No assignment. This is a warning-class “not found,” not a crash, unless your WHENEVER SQLERROR handler treats it as fatal.
• SQLCODE -811 / SQLSTATE 21000 — more than one row qualified. Statement processing is terminated. Fix the predicate, add aggregation, or add FETCH FIRST 1 ROW ONLY.
• SQLCODE -305 — a selected column was null and the corresponding host variable had no indicator. Declare an indicator (SMALLINT) and test it after a successful fetch.

Guaranteeing a single row

Three honest ways to keep SELECT INTO legal:

• Unique predicate — equality on a primary key or unique index (EMPNO, account number).
• Aggregate without GROUP BY leftover columns — SELECT AVG(SALARY) INTO :HV-AVG FROM EMP WHERE WORKDEPT = :DEPT returns one row even if many employees match (the average of the set). If no rows match, AVG still returns a row whose value is null—so you get SQLCODE 0 and a null indicator, not +100. COUNT(*) similarly returns 0, not “not found.”
• FETCH FIRST 1 ROW ONLY — IBM documents this as a way to ensure only one row is returned so INTO does not see an unpredictable extra row. Combine with ORDER BY when “the” row must be the latest hire, highest salary, or similar. Without ORDER BY, which row is first is not a business rule you should rely on.

1
2
3
4
5
6
SELECT LASTNAME, HIREDATE
  INTO :HV-LASTNAME, :HV-HIREDATE
  FROM DSN8C10.EMP
 WHERE WORKDEPT = :HV-DEPT
 ORDER BY HIREDATE DESC
 FETCH FIRST 1 ROW ONLY;

SELECT list items

The select list is not limited to column names. You can mix columns, host variables, constants, expressions, and aggregate functions—as long as the result is still one row and each item has a matching target.

1
2
3
4
5
6
7
8
9
10
SELECT EMPNO,
       LASTNAME,
       :HV-RAISE,
       SALARY + :HV-RAISE
  INTO :HV-EMPNO,
       :HV-LASTNAME,
       :HV-RAISE-OUT,
       :HV-NEW-SAL
  FROM DSN8C10.EMP
 WHERE EMPNO = :HV-KEY;

Isolation clauses (WITH UR, WITH CS, and so on), FOR READ ONLY, common table expressions, and ORDER BY / FETCH / OFFSET can appear on SELECT INTO the same way they appear on a cursor SELECT, with the extra rule that FOR UPDATE must not be specified on SELECT INTO (SQLSTATE 42829). SELECT INTO is a read into variables, not a positioned update.

SELECT without FROM

Beginners coming from other databases often write SELECT CURRENT DATE with no FROM clause. In Db2 for z/OS application programming, the documented pattern for “I need a SELECT but I am not reading a business table” is to use a dummy table, or to skip SELECT entirely and use VALUES INTO.

SYSIBM.SYSDUMMY1 is a one-row catalog table Db2 provides so you can select expressions. IBM notes that it uses the EBCDIC encoding scheme. Related dummy tables exist for other encodings (commonly discussed as SYSDUMMYA for ASCII and SYSDUMMYU for Unicode). If a special register or function result looks “wrong” in a Unicode application, check which dummy table you used—the FROM object’s encoding can influence string results.

1
2
3
4
5
6
7
8
SELECT CURRENT DATE,
       CURRENT TIME,
       CURRENT TIMESTAMP,
       CURRENT SQLID
  FROM SYSIBM.SYSDUMMY1;
 
SELECT RAND(:HV-SEED)
  FROM SYSIBM.SYSDUMMY1;

Dummy-table SELECT is also how you pull session data into a result set for QMF, SPUFI, or DSNTEP2 when you are not querying EMP or DEPT. It is a real table reference: you still need SELECT privilege on SYSIBM.SYSDUMMY1 (normally granted to PUBLIC in a typical install), and the statement still goes through prepare/bind.

When the goal is only to fill host variables, prefer VALUES INTO (next section) or a SET assignment in SQL PL. Those forms make the intent obvious: “evaluate expressions,” not “query a table that happens to have one row.”

VALUES INTO: expressions into variables

VALUES INTO assigns the result of one or more expressions to variables. It does not name a FROM table. Use it for special registers, arithmetic, scalar functions, and other expressions you would otherwise wrap in SELECT … FROM SYSIBM.SYSDUMMY1 INTO …

1
2
3
4
5
VALUES CURRENT DATE, CURRENT TIME
  INTO :HV-DATE, :HV-TIME;
 
VALUES 1 + 1, CURRENT SQLID
  INTO :HV-TWO, :HV-SQLID;

Rules that match SELECT INTO in spirit:

• Count must match — one expression per target, in order.
• Types must be assignable — same assignment rules as other INTO statements. Cast explicitly when a special register’s type does not match the host declaration (for example DATE into a CHAR(10) layout your copybook expects).
• Nulls still need indicators — an expression can be null (a scalar subquery that returns no row, a null argument to a function, and so on).

Do not confuse VALUES INTO with the VALUES statement used in a basic trigger. In a triggered action, VALUES expression is a way to invoke a user-defined function for its side effect; the result is discarded and is not assigned to host variables. That trigger VALUES is not how application programs retrieve dates.

VALUES as a query source

A values-clause is a form of fullselect. That means VALUES can supply rows wherever a fullselect is legal: as a nested table in FROM, as a branch of UNION / EXCEPT / INTERSECT, or as the source of INSERT. This is how you build a tiny in-line table of constants without CREATE TABLE.

1
2
3
4
5
6
7
8
9
SELECT ID, DEPT
  FROM (VALUES (1, 'A00'),
               (2, 'B01'),
               (3, 'C01')) AS T(ID, DEPT);
 
INSERT INTO SESSION.STAGING (CODE, LABEL)
  SELECT CODE, LABEL
    FROM (VALUES ('A00', 'SPIFFY COMPUTER SERVICE DIV.'),
                 ('B01', 'PLANNING')) AS X(CODE, LABEL);

Name the result columns in the correlation clause (AS T(ID, DEPT)). Without names, the engine still produces unnamed columns that are awkward to reference. Keep row widths consistent: every VALUES row must have the same number of expressions, and corresponding expressions must have compatible types (the same compatibility rules used for UNION).

Practical uses on z/OS:

• Test data — prototype a join or CASE without loading a table.
• Code lists — a small set of status codes you want to LEFT JOIN to find missing rows.
• INSERT … SELECT — build several constant rows in one statement instead of repeated INSERT VALUES.
• UNION with a real table — append sentinel rows (totals, “unassigned”) for a report.

VALUES as a table is not a substitute for a real lookup table when many programs share the codes. Constants in SQL drift. For production reference data, prefer a maintained table. Use VALUES when the set is tiny, local to one statement, and honestly constant.

Choosing SELECT INTO, VALUES INTO, or a cursor

• Primary-key inquiry — SELECT INTO from the table, check +100 for not found.
• Special registers / expressions only — VALUES INTO (or SET in SQL PL).
• Need a FROM for encoding or for a SELECT-only tool — SYSIBM.SYSDUMMY1.
• Zero to many rows — cursor, or a set-oriented statement (INSERT SELECT, MERGE, SELECT that feeds a file).
• Ad hoc constant rows — VALUES nested table.

In COBOL, host variables in INTO must be declared so the precompiler can see them (WORKING STORAGE, a DCLGEN copybook, or an SQL TYPE). Varying-length strings need a length prefix the precompiler understands. LOB targets may be locators or file reference variables when the value is large. Keep SELECT INTO lists short and aligned with the copybook so a DCLGEN change is obvious at compile time.

Explain It Like I'm Five

SELECT INTO is like asking the librarian for one specific book and putting it in your backpack. If the book is missing, your backpack stays as it was and the librarian says “not found” (+100). If two books match the title, the librarian refuses to guess which one you meant (-811). VALUES INTO is like writing today’s date on a sticky note without walking into the stacks at all. VALUES as a query source is like making a tiny homemade shelf of three sample books so you can practice lining them up before you touch the real library.

Exercises

1. Write a SELECT INTO that loads EMPNO, LASTNAME, and SALARY for employee '000010'. List the host variables and an indicator for SALARY.
2. Describe what your program should do on SQLCODE +100 versus -811 for that statement.
3. Rewrite SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1 INTO :HV-TS as a VALUES INTO statement.
4. Build a nested VALUES table of three department codes and join it to DSN8C10.DEPT to see which codes are missing.
5. Explain why COUNT(*) INTO :HV-N FROM EMP WHERE WORKDEPT = 'ZZZ' does not return +100 when the department has no employees.

Frequently asked questions

Is SELECT INTO slower than a cursor that FETCHes once?

For a true single-row path, SELECT INTO is the usual choice and avoids cursor overhead. The bigger cost is usually the access path (index versus tablespace scan), not INTO versus FETCH. Do not use SELECT INTO inside a loop that should have been one set-oriented statement.

Can I SELECT INTO a table?

No. INTO names variables, not a table. To put query results into a table, use INSERT … SELECT, CREATE TABLE … AS, or a declared global temporary table plus INSERT.

Does FETCH FIRST 1 ROW ONLY hide a data problem?

It prevents -811, but if two rows qualify and you did not ORDER BY, you may silently pick an arbitrary row. Use it when you truly want “any one” or “the first after ORDER BY,” not as a way to ignore duplicate keys you should have prevented.