DB2 Notes

SQLCODEs

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 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 use UNION ALL.  To sort the result table in a designated order, code the ORDER BY after the final 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:

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:

For example:

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, 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.