SQLCODEs 0 normal +100 Query result is empty table; Row not found for FETCH , UPDATE , or DELETE ...
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:
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:
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 includes rows selected from different tables in 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 useUNION ALL. To sort the result table in a designated order, code the ORDER BY after the final SELECT clause.
For example:
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 denotesINNER JOIN.
For example:
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 aDECLARE CURSOR cursor-name statement. The result table is created by anOPEN cursor-name statement. Each row is retrieved into host variables, in sequence, by repeated FETCH cursor-name statements. Finally, resources are released by aCLOSE 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 INSERTof 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
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
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.
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, providing a result that does not vary depending upon the rows examined by the outer query. It is evaluated a single time before the outer query is executed.
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, providing a result that does vary depending upon the rows examined by the outer query. It is evaluated anew for each row that the outer query examines.
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, and then summarizes each group of rows having a unique combination of values in the grouping columns 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 Outer select grouped by account
,SUM(TRD_DOLAMT)
FROM T100.TRADE
WHERE TRD_ACCT IN
(SELECT TRD_ACCT Subquery also grouped by account
FROM T100.TRADE
WHERE TRD_CUR_CDE = :WS-CUR-CODE
AND TRD_MON_YR = :WS-MONTH-YEAR
GROUP BY TRD_ACCT
HAVING COUNT(*) >= 3)
GROUP BY TRD_ACCT
ORDER BY TRD_ACCT
END-EXEC.
Efficiency and Performance
A compound condition will not use row indexes, but a UNION will use them.
No comments
Post a Comment