DB2 Notes
SQLCODEs
| 0 | normal |
| +100 | Query result is empty table; Row not found for FETCH, UPDATE,
or DELETE |
| -305 | No indicator variable supplied for column returning null value |
| -803 | INSERT or UPDATE attempted to create a duplicate
value in a column defined as unique |
| -805 | DBRM or package name not found in plan; Most likely remedied by binding DBRM for first time |
| -811 | Simple SELECT result table has more than 1 row;Subquery produces more than 1 value |
| -818 | Mismatch of timestamps in load module and bind; Most likely remedied by rebind |
| -911 | Cursor deadlock or timeout |
SQL Communication Area
Inserted into Working Storage by INCLUDE SQLCA.
Useful fields include:
05 SQLCODE PIC S9(9) COMP. SQL return code unique to MVS
05 SQLERRD(3) PIC S9(9) COMP. Number of rows changed by
INSERT, DELETE, or UPDATE
05 SQLSTATE PIC X(5). SQL return code recognized by all ANSI-compliant
platforms. Recommended for MVS programs that
communicate across platforms.
Row Names
A literal, computed value, or qualified name is given a unique name in the result table
by the AS clause.
For example:
EXEC SQL
SELECT 'FULL AMOUNT' AS AMOUNT
,PRINC + INT AS LOAN
,H.CURR AS CURRENCY
,C.CUST AS CUSTOMER
INTO :AMOUNT
,:LOAN
,:CURRENCY
,:CUSTOMER
FROM T100.HIST H Implicit syntax for inner join
,T100.CUST C
WHERE H.CUST = C.CUST
END-EXEC.
Unions
The UNION clause combines the rows resulting from multiple SELECT
clauses into a single result table. It sorts the result table to match and remove
duplicate rows. To forgo the sort, keep the duplicate rows, and produce a result table
with rows in selected sequence use UNION ALL. To sort the result table
in a designated order, code the ORDER BY clause after the final SELECT
clause. Note that any SQL statement may contain at most one ORDER BY clause
which applies to the final result table rather than the result of the closest SELECT
clause.
For example:
EXEC SQL
DECLARE CURSOR ROW-CSR FOR
SELECT ROW_A AS ROW1
,ROW_B AS ROW2
FROM T01.TABLE
WHERE CDE > 55
UNION ALL Include duplicate rows
SELECT ROW_C AS ROW1
,ROW_D AS ROW2
FROM T02.TABLE
WHERE CDE > 55
UNION ALL
SELECT ROW_E AS ROW1
,ROW_F AS ROW2
FROM T03.TABLE
WHERE CDE > 55
ORDER BY ROW1, ROW2
END-EXEC.
Joins
A join matches rows of multiple tables by designated columns, and creates a result table having rows composed of columns taken from the different tables. An inner join selects columns from only the matched rows. An outer join, selects columns from both matched and unmatched rows.
The inner join has an implicit and explicit syntax. In the explicit syntax,
JOIN denotes INNER JOIN.
For example:
Implicit Syntax
EXEC SQL
DECLARE CURSOR ROW-CSR FOR Multiple rows expected
SELECT ACT.REG AS REGION
,HST.CURR AS CURRENCY
,TRN.AMT AS AMOUNT
FROM T100.ACCT ACT All joined tables named
,T100.HIST HST
,T100.TRAN TRN
WHERE ACT.CUST = HST.CUST Need not include matching columns in result table
AND ACT.CUST = TRN.CUST
AND ACT.CUST = '5221'
ORDER BY REGION, CURRENCY, AMOUNT
END-EXEC.
Explicit Syntax
EXEC SQL
DECLARE CURSOR ROW-CSR FOR Multiple rows expected
SELECT ACT.REG AS REGION
,HST.CURR AS CURRENCY
,TRN.AMT AS AMOUNT
FROM T100.ACCT ACT First outer table named
JOIN T100.HIST HST
ON ACT.CUST = HST.CUST Need not include matching columns in result table
JOIN T100.TRAN TRN
ON ACT.CUST = TRN.CUST
WHERE ACT.CUST = '5221'
ORDER BY REGION, CURRENCY, AMOUNT
END-EXEC.
The result table is assembled in a series of steps in which each unjoined component table, called the new table or inner table, is joined to the provisional result table (which in the first step, is the first component table chosen for the join), called the composite table or outer table.
In the explicit syntax, the sequence of tables entering the join is the order in which the tables appear in the SQL. In the implicit syntax, the sequence is unclear.
Read-Only Tables
A view or cursor produces a read-only result table when the underlying
SELECT statement uses a union, join, subquery of a certain type, column
function, or any of the keywords:
DISTINCT, ORDER BY,
GROUP BY, or HAVING.
No update nor delete operation may be performed on a read-only table.
Cursor-Controlled Tables
When multiple rows are expected in the result table, they must be accessed through a
cursor, which is created by a SELECT statement embedded within a
DECLARE CURSOR cursor-name statement. The result table
is created by an OPEN cursor-name statement. Each row is
retrieved into host variables, in sequence, by repeated
FETCH cursor-name statements. Finally, resources are released
by a CLOSE cursor-name statement.
Views
A view is a SELECT statement that is stored with the database and
associated with a name by the CREATE VIEW view-name
statement. The view’s name may be used in almost any context as a table’s
name. A query performed on a view begins by executing the stored
SELECT, and then further extracts data from the result.
Handling Nulls
The value of an indicator variable tells the status of a row after a query.
01 FILLER.
05 WS-AMOUNT PIC S9(5)V9(2) COMP-3. Host variables
05 WS-CUSTNUM PIC X(5).
01 FILLER.
05 AMT-IND PIC S9(4) COMP. Indicator variable
EXEC SQL
SELECT CUST_AMOUNT
INTO :WS-AMOUNT:AMT-IND No intervening space
FROM T100.CUST
WHERE CUST_ID = :WS-CUSTNUM
END-EXEC.
After a query, the indicator variable contains the following:
| 0 | Column is not null | |
| -1 | Column is null | |
| -2 | Column is null as result of conversion error | |
| +length | Full length of column that was truncated to fit a short host variable |
Load -1 to the indicator variable to set a column to a null value, during
UPDATE or INSERT of a row.
If a column is always to be set to a null value, code the NULL keyword
for the column: in the UPDATE statement’s SET
clause; or in the INSERT statement’s VALUES clause.
A column omitted from the row list of an INSERT statement will always be
set to a null value, if the column was defined as NOT NULL;
otherwise, an error will occur.
Code a predicate to test for null with the following syntax:
WHERE column name IS [NOT] NULL
The scalar functions, VALUE and COALESCE, are equivalent, and
they can be used only in outer joins; each takes a list
of multiple parameters and returns the first parameter that is not null. The
following will return either a non-null column value or a literal:
EXEC SQL
SELECT ACCT_REG AS REGION
,VALUE(ACCT_A1, ACCT_A2, 'NO ACCT') AS ACCOUNT
INTO :WS-REGION
,:WS-ACCOUNT
FROM T200.ACCT
WHERE ACCT_REG <> '65'
END-EXEC.
Variable-Length Columns
The host variable for a variable-length column must be coded as a group item containing two fields: first, a length field, then a data field. Both must be 49-levels.
01 FILLER.
05 WS-NAME.
49 WS-NAME-LEN PIC S9(4) COMP. Column length
49 WS-NAME-TEXT PIC X(128). Column data
EXEC SQL
SELECT CUST_NAME
INTO :WS-NAME Load the group-level
FROM T100.CUST
WHERE CUST_ID = :WS-CUST-ID
END-EXEC.
EXEC SQL
SELECT CUST_ID
INTO :WS-CUST-ID
FROM T100.CUST
WHERE CUST_NAME = :WS-NAME-TEXT Test the data field
END-EXEC.
To UPDATE or INSERT a variable-length column, load
the new length to its host length variable.
Special Predicates “It’s like in-between.”
The LIKE phrase defines a mask for comparing characters:
WHERE COL_VAL [NOT] LIKE mask
A mask may be a host variable or a literal enclosed in quotes and may contain any number
of:
| character literal | for an exact match | |
| underscore character | _ | for any single character |
| percent sign character | % | for any sequence of characters of length 0 or more |
For example:
|
'NEW %' |
masks | 'NEW YORK' but not 'NEWARK' |
'NEW%' |
masks | 'NEWARK' |
|
'T_N' |
masks | 'TAN', 'TIN', or 'TON',
but not 'TUNE' |
|
'T_N%' |
masks | 'TUNE' |
|
'%CA%' |
masks | 'CAT', 'GO CART',
'MOCA', etc. |
|
'%CA% ' |
masks | 'CAT ' but not 'CAT' |
To use a host variable for a mask to produce the same effect as the literal mask in the second-to-last example, code it right-padded with “%” characters to avoid the effect of the last example.
05 WS-MASK PIC X(6) VALUE '%CA%%%'.
The IN phrase chooses from a given set:
WHERE COL_VAL [NOT] IN (:HOST-VAR, 'LITERAL', COL1 + COL2, ...)
Multiple list items that contain the same value are considered as a single item.
The BETWEEN phrase chooses from a range of inclusive limits:
WHERE COL_VAL [NOT] BETWEEN [:HOST-VAR1, 'LIT1']
AND [:HOST-VAR2, 'LIT2']Subqueries
A subquery is an inner SELECT that is nested within the predicate of an outer
query, providing its result for comparison. An uncorrelated subquery has no predicates
that reference columns returned by the outer query. It is evaluated a single time
before the outer query is executed, and its result does not vary depending upon the rows
examined by the outer query.
EXEC SQL
DECLARE CURSOR ACCT_CSR FOR
SELECT ACCT_ID
,ACCT_NAME
FROM T100.ACCT
WHERE ACCT_ID NOT IN
(SELECT TRD_ACCT Select a fixed list of accounts
FROM T100.TRADE before the outer query
WHERE TRD_ADDR-STATE = :WS-STATE)
ORDER BY ACCT_ID
END-EXEC.
A correlated subquery has one or more predicates that reference columns returned by the outer query. It is evaluated anew for each row that the outer query examines, and its result does vary depending upon the rows examined by the outer query.
EXEC SQL
DECLARE CURSOR ACCT_CSR FOR
SELECT ACCT_ID
,ACCT_NAME
FROM T100.ACCT ACT
WHERE EXISTS
(SELECT * FROM T100.BROKER Select a new result table
WHERE ACT.ACCT_ID BETWEEN BKR_CUSTID_LOW for each row of the
AND BKR_CUSTID_HI) outer query
ORDER BY ACCT_ID
END-EXEC.
In a correlated subquery, the outer query must provide a correlation name, in this
case, the qualifier ACT, to identify the correlation reference, in
this case ACT.ACCT_ID, to the inner SELECT.
Grouping of Rows
The GROUP BY group-col1, group-col2,... clause sorts the
selected rows by the grouping columns into a work table from which it summarizes each group of
rows whose grouping columns have a unique combination of values into a single row in
the result table. The SELECT clause may include only grouping columns
and columns of aggregate values. The scope of column functions is restricted to the
individual groups.
The HAVING clause restricts the number of groups of rows in the work table that
will be summarized into the result table.
For example, to find the monthly total dollar-amount of trades in a given foreign currency, for each account executing at least 3 trades in that currency in a given month:
EXEC SQL
DECLARE CURSOR TRD-CSR FOR
SELECT TRD_ACCT
,SUM(TRD_DOLAMT)
FROM T100.TRADE
WHERE TRD_CUR_CDE = :WS-CUR-CODE Limit rows selected into the work table
AND TRD_MON_YR = :WS-MONTH-YEAR
GROUP BY TRD_ACCT
HAVING COUNT(*) >= 3 Limit groups of rows summarized into
ORDER BY TRD_ACCT the result table
END-EXEC.
To find the monthly total dollar-amount of all trades in all currencies, for each account executing at least 3 trades in a given currency in a given month:
EXEC SQL
DECLARE CURSOR TRD-CSR FOR
SELECT TRD_ACCT
,SUM(TRD_DOLAMT)
FROM T100.TRADE
WHERE TRD_ACCT IN
(SELECT TRD_ACCT
FROM T100.TRADE
WHERE TRD_CUR_CDE = :WS-CUR-CODE
AND TRD_MON_YR = :WS-MONTH-YEAR
GROUP BY TRD_ACCT Group subquery by account
HAVING COUNT(*) >= 3)
AND TRD_MON_YR = :WS-MONTH-YEAR
GROUP BY TRD_ACCT Group outer select by account
ORDER BY TRD_ACCT
END-EXEC.
Efficiency and Performance
A compound condition will not use row indexes, but a UNION will use them.