Basic SELECT in DB2 for z/OS

What SELECT does

The SELECT clause specifies the columns of the final result table. Db2 applies your select list to the rows produced by the rest of the subselect (FROM, then WHERE, and later GROUP BY / HAVING). If you only write SELECT and FROM, every row of the table-reference is a candidate; the select list decides which values appear and in which order.

1
2
SELECT EMPNO, LASTNAME, WORKDEPT
  FROM HR.EMPLOYEE;

Read it as: “From the EMPLOYEE table in schema HR, build a result with three columns: employee number, last name, and department.” The result is a table. It is not stored unless you INSERT it somewhere. Tools such as SPUFI, QMF, DSNTEP2, and interactive SQL in your IDE simply display that result.

The optional word ALL after SELECT is the default: keep every row, including duplicates. DISTINCT is the other choice and has its own page next. Until then, remember SELECT means SELECT ALL.

1
2
3
4
5
6
SELECT ALL WORKDEPT
  FROM HR.EMPLOYEE;
 
-- Same as:
SELECT WORKDEPT
  FROM HR.EMPLOYEE;

SELECT *

SELECT * means “every exposed column of the FROM result,” in the order those columns are defined. It is the fastest way to peek at a table in SPUFI:

1
2
SELECT *
  FROM HR.EMPLOYEE;

Rules and cautions:

• Implicitly hidden columns (for example some temporal or internally generated columns) are not returned by SELECT *. You must name them if you need them.
• Column order is the table’s column order, not “the interesting columns first.” Programs that FETCH into a COBOL layout by position break when someone ALTER TABLE … ADD COLUMN in the middle or at the end.
• Joins — SELECT * returns columns from every table-reference, which duplicates join keys and surprises people. Prefer SELECT E.EMPNO, D.DEPTNAME or qualified stars: E.*
• Views — SELECT * on a view returns the view’s result columns, not necessarily every base-table column

1
2
3
4
SELECT E.*
  FROM HR.EMPLOYEE E
  INNER JOIN HR.DEPARTMENT D
          ON E.WORKDEPT = D.DEPTNO;

Use SELECT * for exploration. In application SQL, list the columns you need. Auditors, performance analysts, and your future self all prefer an explicit list: less data over the wire, stable FETCH layouts, and a clear contract with the table.

SELECT specific columns

Write a comma-separated list. Order in the list is the order in the result, independent of CREATE TABLE order.

1
2
SELECT LASTNAME, EMPNO, HIREDATE
  FROM HR.EMPLOYEE;

Qualify names when the same column name could come from more than one table-reference, or simply to make the statement self-explanatory:

1
2
3
4
SELECT EMPLOYEE.EMPNO,
       EMPLOYEE.LASTNAME,
       EMPLOYEE.WORKDEPT
  FROM HR.EMPLOYEE;

You need a table, view, alias, or synonym that your authorization ID can SELECT. Unqualified EMPLOYEE is resolved with the current schema rules (CURRENT SCHEMA / QUALIFIER). Two-part names HR.EMPLOYEE are the usual tutorial style. Three-part names appear when you reach a remote location through DDF.

Duplicate column names in the select list are allowed in some contexts but painful. Prefer distinct result names. If you select DEPTNO from two tables, rename one with AS.

SELECT expressions

A select-list item can be any expression, not only a stored column. IBM calls these derived columns: they are computed when the query runs.

1
2
3
4
5
SELECT EMPNO,
       SALARY + BONUS + COMM AS TOTAL_PAY,
       SALARY * 1.03 AS AFTER_RAISE,
       YEAR(HIREDATE) AS HIRE_YEAR
  FROM HR.EMPLOYEE;

If BONUS or COMM can be null, addition of nulls makes TOTAL_PAY null. Use COALESCE(BONUS, 0) when “missing” should mean zero for the report:

1
2
3
4
5
SELECT EMPNO,
       SALARY
         + COALESCE(BONUS, 0)
         + COALESCE(COMM, 0) AS TOTAL_PAY
  FROM HR.EMPLOYEE;

Expressions can include CONCAT, scalar functions, CASE, CAST, special registers, and nested arithmetic. The result data type follows Db2’s type resolution. DECIMAL salary times 1.03 is still a decimal with a documented precision and scale—check it before you FETCH into a COBOL PIC.

You cannot use a select-list AS name in the same query’s WHERE clause. WHERE is determined before the select list is applied. Repeat the expression, or wrap the query in a nested table expression / CTE and filter on the name in the outer WHERE.

SELECT constants and literals

A result column can be a constant: a string literal, a numeric literal, a datetime literal, or NULL. That is useful for report labels, default flags, and “select an expression that does not need table data.”

1
2
3
4
5
6
SELECT EMPNO,
       LASTNAME,
       'ACTIVE' AS STATUS,
       1 AS ROW_KIND,
       CURRENT DATE AS RUN_DATE
  FROM HR.EMPLOYEE;

Every result row repeats those constants. They are not stored in EMPLOYEE. The string 'ACTIVE' is a character constant with the NOT NULL attribute. If you need a typed null column in the result, write CAST(NULL AS CHAR(1)) (or another type) so the result column has a defined type.

Selecting without business data: SYSIBM.SYSDUMMY1

Db2 for z/OS traditionally wants a FROM clause on a subselect. When you only want to evaluate an expression—current timestamp, a CONCAT of literals, a bit function demo—use the catalog table SYSIBM.SYSDUMMY1. It has one row. The contents of that row do not matter.

1
2
3
4
5
6
SELECT CURRENT DATE,
       CURRENT TIME,
       CURRENT TIMESTAMP,
       2 + 3 * 4 AS PRECEDENCE_DEMO,
       'HELLO' CONCAT ' ' CONCAT 'DB2' AS GREETING
  FROM SYSIBM.SYSDUMMY1;

SYSDUMMY1 lives in an EBCDIC table space (SYSEBCDC), unlike most Unicode catalog tables. That rarely matters for SELECT of special registers, but it is why some encoding experiments use it on purpose. Later pages cover VALUES as a query source and SELECT without FROM; for everyday z/OS SQL, SYSDUMMY1 is the idiom interviewers expect.

Naming result columns with AS

Base columns keep their names in the result unless you rename them. Derived columns such as (SALARY + BONUS + COMM) have no name until you give one. The AS clause (the keyword AS is optional but recommended) assigns a new-column-name.

1
2
3
4
SELECT EMPNO,
       (SALARY + COALESCE(BONUS, 0) + COALESCE(COMM, 0)) AS TOTAL_PAY
  FROM HR.EMPLOYEE
 ORDER BY TOTAL_PAY DESC;

ORDER BY can use that name. WHERE cannot. HAVING in grouped queries can use result names in some cases, but beginners should repeat expressions until they are sure of the rules. Choose names that are valid ordinary identifiers, or use delimited identifiers when you need mixed case or spaces (usually you do not).

Putting a first full statement together

1
2
3
4
5
6
7
SELECT E.EMPNO,
       STRIP(E.FIRSTNME) CONCAT ' ' CONCAT STRIP(E.LASTNAME) AS NAME,
       E.WORKDEPT,
       E.SALARY * 1.03 AS NEXT_SALARY,
       'PROJ' AS SOURCE
  FROM HR.EMPLOYEE AS E
 WHERE E.WORKDEPT = 'A00';

That statement uses specific columns, an expression, a literal, AS names, a correlation name on FROM, and a simple WHERE. The next pages deepen DISTINCT and FROM. WHERE, JOIN, and ORDER BY follow after that.

In COBOL, this SELECT typically sits in a cursor: DECLARE CURSOR, OPEN, FETCH into host variables that match the select list left to right, CLOSE. SELECT INTO is a one-row form for when you know the statement returns at most one row. Both come later; the select list rules you learned here still apply.

Special registers and functions in the select list

Besides table columns, the select list can include special registers such as CURRENT DATE, CURRENT TIME, CURRENT TIMESTAMP, CURRENT SQLID, USER, and CURRENT MEMBER. They are evaluated in the statement’s context and repeat on every result row unless you select them only from SYSDUMMY1. That is how batch jobs stamp a report with the run date without storing the date on the employee row.

1
2
3
4
5
6
SELECT EMPNO,
       LASTNAME,
       CURRENT DATE AS RUN_DATE,
       USER AS RUN_USER
  FROM HR.EMPLOYEE
 WHERE WORKDEPT = 'A00';

Scalar functions belong in the same list: STRIP, SUBSTR, UPPER, DECIMAL, COALESCE, CAST. Keep the list readable. If an expression is huge, a nested table expression or a view can name it once. Duplicate expressions in SELECT and WHERE are not “wrong,” but they are easy to update in only one place—watch for that in code review.

Result column names, order, and programs

SPUFI and QMF label columns using the AS name or the base column name. Unnamed expressions show up as blank or generated headings depending on the tool. COBOL does not care about headings; it cares about left-to-right order and data types when you FETCH into a list of host variables. If you swap two select-list items, the program can silently store last names in the salary field.

Static SQL in COBOL usually lists columns explicitly for that reason. Dynamic SQL that builds SELECT * and then describes the result with SQLDA can tolerate added columns, but that is a different programming model. Beginners should list columns. Include ROWID or IDENTITY columns only when you need them; they are easy to forget in SELECT * debates because some are implicitly hidden.

SELECT does not lock the discussion for later pages: WITH UR versus CS, cursor stability, and isolation all affect what your SELECT sees if other jobs are updating. For this lesson, assume you are reading committed rows in a quiet table. When two sessions disagree about a salary, that is concurrency, not a broken select list.

A SELECT list can repeat a column, compute the same column twice with different expressions, or mix literals from different types as long as each item is valid on its own. There is no requirement that the result look like the base table. Reports often select ten expressions from one table and zero “raw” columns. That is still a basic SELECT: one FROM, one list of items, optional WHERE later.

Explain It Like I'm Five

A table is a big grid on a whiteboard. SELECT is you pointing at which columns to copy onto a new smaller grid. SELECT * means “copy every column.” Listing names means “only those.” An expression is you doing math or gluing words while you copy, so the new grid has a column that was never on the original whiteboard. A literal is you writing the same sticker on every copied row, like stamping “ACTIVE” on each line. SYSDUMMY1 is a whiteboard with one blank row so you still have somewhere to stand when you only want to do the math.

Exercises

1. Write a SELECT that returns EMPNO and LASTNAME from HR.EMPLOYEE.
2. Write SELECT * against a table you are allowed to read, then rewrite it with an explicit column list of three columns.
3. Add a derived column NEXT_SALARY as SALARY * 1.10 with an AS name.
4. Select CURRENT TIMESTAMP from SYSIBM.SYSDUMMY1.
5. Explain why a COBOL program that FETCHes SELECT * can break after ALTER TABLE ADD COLUMN.