DB2 Tips.

A Mainframe-Tip a Day keeps the bugs away!
Forum rules
All of these Tips/Tuning-suggestions should be tested your own, at your shop, prior to use in Prod.
User avatar

Topic Author
Anuj Dhawan
Founder
Posts: 2624
Joined: Sun Apr 21, 2013 7:40 pm
Location: Mumbai, India
Zodiac: Sagittarius

Re: DB2 Tips.

Post by Anuj Dhawan » Wed Nov 27, 2013 3:23 pm

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.


Thanks,
Anuj

Disclaimer: My comments on this website are my own and do not represent the opinions or suggestions of any other person or business entity, in any way.


Quasar Chunawala
Registered Member
Posts: 35
Joined: Sun Aug 11, 2013 4:48 pm
Location: Pune
Zodiac: Aquarius

Re: DB2 Tips.

Post by Quasar Chunawala » Wed Mar 12, 2014 1:21 am

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
Registered Member
Posts: 35
Joined: Sun Aug 11, 2013 4:48 pm
Location: Pune
Zodiac: Aquarius

Re: DB2 Tips.

Post by Quasar Chunawala » Wed Mar 12, 2014 1:48 am

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
Registered Member
Posts: 35
Joined: Sun Aug 11, 2013 4:48 pm
Location: Pune
Zodiac: Aquarius

Re: DB2 Tips.

Post by Quasar Chunawala » Wed Mar 12, 2014 7:06 pm

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



Return to β€œTip Of the Day.”