DB2 Tips.

[Anuj Dhawan]

15. NULL and Scalar Function consideratrions:

1. If you're working on indexed columns, avoid IS NULL or IS NOT NULL condition.
2. It's not advisable to use the scalar functions such as SUBSTR, CHAR, INT etc. in the WHERE clause. If any scalar function is used for a column in the WHERE clause, the DB2 optimizer will not use a matching index column for the retrieval of the records from the table and that will behave as CPU hogger.

[Quasar Chunawala]

16. Use Multi-row fetch : A multi-row fetch retrieves multiple rows into a host-variable array in your application program.

Code: Select all

       EXEC SQL
          DECLARE CURSOR C1 WITH ROWSET POSITIONING FOR
          SELECT FIRST_NAME,LAST_NAME
          FROM DSN8910.EMP
      END-EXEC

Multi-row fetch is good technique - it can even be used with utilities such as DSNTIAUL. It is actually a good idea to do some profiling of your old programs, measure their performance and see where multi-row FETCH can fit in and retrofit them!

[Quasar Chunawala]

17. An SQL join on most occasions out-performs a programmatic join. Programmatic joins are bad and should be avoided at all times.

Programmatic join

Code: Select all

----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
           EXEC SQL
             OPEN C1
           END-EXEC
       
           PERFORM UNTIL END-OF-C1
             EXEC SQL
                FETCH C1 INTO :HV-A, :HV-B, :HV-C
             END-EXEC
             EVALUATE SQLCODE
                WHEN 0
                   PERFORM 1000-OPEN-C2  THRU 1000-EXIT
                   PERFORM 2000-FETCH-C2 THRU 2000-EXIT
                   UNTIL END-OF-C2
                   PERFORM 3000-CLOSE-C2 THRU 3000-EXIT
                WHEN +100
                   MOVE SPACES TO :HV-A, :HV-B, :HV-C
                   SET END-OF-C1 TO TRUE
                WHEN OTHER
                    PERFORM 9000-PROCESS-ERROR THRU 9000-EXIT
              END-EVALUATE
           END-PERFORM

           EXEC SQL
              CLOSE C1
           END-EXEC

[Quasar Chunawala]

18. Common Table Expressions and WITH Clause

Very often, you might have used in-line SELECT's in SQL Queries(look at Query X and Query Y).

Code: Select all

SELECT X.col1,X.col2,Y.col1,Y.col2
FROM
(SELECT a,b,c
 FROM tbl1
) AS X,
(SELECT d,e,f
FROM tbl2
) AS Y

With common table expressions allow you can re-structure inline queries in a neat way. Declare all temporary tables, you'd like to have in the WITH Clause. You can then use them anywhere in the main query. Makes up for much more readable code.

Code: Select all

WITH
  UNPOOLED (UNP_TRADES) AS
           (SELECT ACTN_ARG_TX
            FROM TBL
            WHERE ...),

  IN_PROG  (IPG_TRADES) AS
           (SELECT ACTN_ARG_TX
            FROM TBL
            WHERE ...),

  POOLED   (END_TRADES) AS
           (SELECT ACTN_ARG_TX
            FROM A_GSF_GNL_CTRL
            WHERE ...)
SELECT *
FROM UNPOOLED, IN_PROG, POOLED