Page 2 of 2
Re: DB2 Tips.
Posted: Wed Nov 27, 2013 3:23 pm
by Anuj Dhawan
15. NULL and Scalar Function consideratrions:
- If you're working on indexed columns, avoid IS NULL or IS NOT NULL condition.
- 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.
Re: DB2 Tips.
Posted: Wed Mar 12, 2014 1:21 am
by 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!
Re: DB2 Tips.
Posted: Wed Mar 12, 2014 1:48 am
by 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
Re: DB2 Tips.
Posted: Wed Mar 12, 2014 7:06 pm
by 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