- 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.
DB2 Tips.
Forum rules
All of these Tips/Tuning-suggestions should be tested your own, at your shop, prior to use in Prod.
All of these Tips/Tuning-suggestions should be tested your own, at your shop, prior to use in Prod.
- Anuj Dhawan
- Founder
- Posts: 2803
- Joined: Sun Apr 21, 2013 7:40 pm
- Location: Mumbai, India
- Contact:
Re: DB2 Tips.
15. NULL and Scalar Function consideratrions:
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.
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.
-
- Registered Member
- Posts: 34
- Joined: Sun Aug 11, 2013 4:48 pm
- Location: Pune
Re: DB2 Tips.
16. Use Multi-row fetch : A multi-row fetch retrieves multiple rows into a host-variable array in your application program.
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!
Code: Select all
EXEC SQL
DECLARE CURSOR C1 WITH ROWSET POSITIONING FOR
SELECT FIRST_NAME,LAST_NAME
FROM DSN8910.EMP
END-EXEC
-
- Registered Member
- Posts: 34
- Joined: Sun Aug 11, 2013 4:48 pm
- Location: Pune
Re: DB2 Tips.
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
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
-
- Registered Member
- Posts: 34
- Joined: Sun Aug 11, 2013 4:48 pm
- Location: Pune
Re: DB2 Tips.
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).
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.
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
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
Create an account or sign in to join the discussion
You need to be a member in order to post a reply
Create an account
Not a member? register to join our community
Members can start their own topics & subscribe to topics
It’s free and only takes a minute