Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Performance tuning of a DB2 query consuming high CPU

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
adilkhan4all

New User


Joined: 12 Jun 2014
Posts: 3
Location: INDIA

PostPosted: Thu Sep 18, 2014 6:27 am    Post subject: Performance tuning of a DB2 query consuming high CPU
Reply with quote

Hi,
I am working on performance tuning of a DB2 query which is consuming a lot of CPU resources. The query is selecting a huge number of records from multiple tables using JOIN and UNION. The query is basically an unload which is a 5 table join with a correlated sub-query existence check, which is then UNIONed to the same 5 table join with a similar non-existence check. Here is how the query looks like:
Code:

SELECT A BUNCH OF COLUMNS,
           CHAR('N')
  FROM   
       TABLE1 A,
       TABLE2 B,
       TABLE3 C,
       TABLE4 D,
       TABLE5 E
WHERE A.COL1 = B.COL1
AND    C.COL2 = D.COL2
AND    A.COL3 = C.COL3
AND    A.COL3 = E.COL3
AND    E.COL4 = '0200'
AND    D.COL5 = 02
AND    A.COL6 IN (4,5)
AND    A.COL7 = 'R'
AND    E.COL8 IN('01','02')
AND EXISTS (SELECT '1'
                    FROM TABLE6 F
                    WHERE F.COL1  = A.COL1
                    AND   F.COL3  = 0
                    AND   F.COL9  = 0
                    AND   F.COL10 = '074'
                    AND   F.COL11 = 02)
  UNION
SELECT SAME BUNCH OF COLUMNS,
            CHAR('Y')
   FROM
       TABLE1 A,
       TABLE2 B,
       TABLE3 C,
       TABLE4 D,
       TABLE5 E
WHERE A.COL1 = B.COL1
AND    C.COL2 = D.COL2
AND    A.COL3 = C.COL3
AND    A.COL3 = E.COL3
AND    E.COL4 = '0200'
AND    D.COL5 = 02
AND    A.COL6 IN (4,5)
AND    A.COL7 = 'R'
AND    E.COL8 IN('01','02')
AND NOT EXISTS (SELECT '1'
                    FROM TABLE6 F
                    WHERE F.COL1  = A.COL1
                    AND   F.COL3  = 0
                    AND   F.COL9  = 0
                    AND   F.COL10 = '074'
                    AND   F.COL11 = 02)

Currently, this query is basically resolving the 5 table join twice. So to reduce the time taken by the query, can we replace the UNION somehow and still populate all the rows with N at the end for rows which satisfy the existence check and Y for the rows which do not.
I tried using CASE instead of UNION but surprisingly it took almost same CPU time as the above query. Here is how I coded the above query using CASE:
Code:

SELECT A BUNCH OF COLUMNS
       CASE WHEN EXISTS
       (SELECT '1'
       FROM TABLE6 F
       WHERE F.COL1  = A.COL1
           AND F.COL3  = 0
           AND F.COL9  = 0
           AND F.COL10 = '074'
           AND F.COL11 = 02) THEN CHAR ('N')
        ELSE CHAR ('Y')
        END
FROM   
       TABLE1 A,
       TABLE2 B,
       TABLE3 C,
       TABLE4 D,
       TABLE5 E
WHERE  A.COL1 = B.COL1
AND    C.COL2 = D.COL2
AND    A.COL3 = C.COL3
AND    A.COL3 = E.COL3
AND    E.COL4 = '0200'
AND    D.COL5 = 02
AND    A.COL6 IN (4,5)
AND    A.COL7 = 'R'
AND    E.COL8 IN('01','02')

The result set was same with both the queries (approx. 3.2 million), but it still did not solve my problem with the CPU consumption. So, can anyone help me figure out a better way to achieve this, if at all that is possible.

Regards,
Adil Khan
Back to top
View user's profile Send private message

Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1609
Location: NY,USA

PostPosted: Thu Sep 18, 2014 8:11 pm    Post subject:
Reply with quote

Try below query.

Code:
SELECT SAME BUNCH OF COLUMNS,
            case when lookup.col1 is null then 'N' else 'Y' end
   FROM
       TABLE1 A,
       TABLE2 B,
       TABLE3 C,
       TABLE4 D,
       TABLE5 E
left join (SELECT F.col1
                    FROM TABLE6 F
                    WHERE F.COL1  = A.COL1
                    AND   F.COL3  = 0
                    AND   F.COL9  = 0
                    AND   F.COL10 = '074'
                    AND   F.COL11 = 02) lookup
on                         lookup.COL1  = A.COL1
                    AND   lookup.COL3  = 0
                    AND   lookup.COL9  = 0
                    AND   lookup.COL10 = '074'
                    AND   lookup.COL11 = 02
WHERE A.COL1 = B.COL1
AND    C.COL2 = D.COL2
AND    A.COL3 = C.COL3
AND    A.COL3 = E.COL3
AND    E.COL4 = '0200'
AND    D.COL5 = 02
AND    A.COL6 IN (4,5)
AND    A.COL7 = 'R'
AND    E.COL8 IN('01','02')


However, reduction of CPU is not only depending on the efficient query but majorly depends on the Indexes, runstats, reorg, BF size, bind options and many more to list down. Being said that did you run explaain and what did you find ?

Also, if you have a load files of these tables everyday then you can achieve the same via SORT instead of unload.
Back to top
View user's profile Send private message
adilkhan4all

New User


Joined: 12 Jun 2014
Posts: 3
Location: INDIA

PostPosted: Mon Sep 22, 2014 6:34 pm    Post subject:
Reply with quote

Hi Rohit,
Thanks for the query, I tried running this but got a couple of issues, first it gave me this error:
Code:

DSNT408I SQLCODE = -206, ERROR:  A.COL1 IS NOT VALID IN THE CONTEXT WHERE IT IS USED
DSNT418I SQLSTATE   = 42703 SQLSTATE RETURN CODE
DSNT415I SQLERRP    = DSNXORSO SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD    = -225  0  0  -1  4039  802 SQL DIAGNOSTIC INFORMATION


I thought it was because of the fact that the query after LEFT JOIN was referring to A.COL1 but A was declared outside, so it gave the error. I declared the table inside the lookup query

Code:


left join (SELECT F.col1
                    FROM TABLE6 F,
                         TABLE1 G
                    WHERE F.COL1  = G.COL1
                    AND   F.COL3  = 0
                    AND   F.COL9  = 0
                    AND   F.COL10 = '074'
                    AND   F.COL11 = 02) lookup
on                         lookup.COL1  = A.COL1
                    AND   lookup.COL3  = 0
                    AND   lookup.COL9  = 0
                    AND   lookup.COL10 = '074'
                    AND   lookup.COL11 = 02


I changed the query as above but this time got the same error for LOOKUP this time::

Code:

DSNT408I SQLCODE = -206, ERROR:  LOOKUP.COL3 IS NOT VALID IN THE CONTEXT WHERE IT IS USED
DSNT418I SQLSTATE   = 42703 SQLSTATE RETURN CODE
DSNT415I SQLERRP    = DSNXORSO SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD    = -600  0  0  -1  4111  802 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD    = X'FFFFFDA8'  X'00000000'  X'00000000'  X'FFFFFFFF'  X'0000
         INFORMATION


Any suggestions ?

Regards,
Adil Khan
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Mon Sep 22, 2014 8:09 pm    Post subject:
Reply with quote

adilkhan4all,

have you thought of simply unloading all five tables,
and using your sort product to do the joins and unions?

you will use less computer resources than when you attempt a
5 (large) table join.

i have found that offloading a lot of this esoteric nonsense to pure batch
(sort/cobol programs) is a lot faster than sophisticted sql involving many rows.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1609
Location: NY,USA

PostPosted: Mon Sep 22, 2014 9:54 pm    Post subject:
Reply with quote

Sorry , I could not notice what I suggested but try sometging below. However, Dick and I suggested another better option to look for.

Code:
SELECT SAME BUNCH OF COLUMNS,
            case when lookup.col1 is null then 'N' else 'Y' end
   FROM
       TABLE1 A,
       TABLE2 B,
       TABLE3 C,
       TABLE4 D,
       TABLE5 E
left join (SELECT F.col1
                    FROM TABLE6 F
                    WHERE 
                             F.COL3  = 0
                    AND   F.COL9  = 0
                    AND   F.COL10 = '074'
                    AND   F.COL11 = 02) lookup
on                         lookup.COL1  = A.COL1

WHERE A.COL1 = B.COL1
AND    C.COL2 = D.COL2
AND    A.COL3 = C.COL3
AND    A.COL3 = E.COL3
AND    E.COL4 = '0200'
AND    D.COL5 = 02
AND    A.COL6 IN (4,5)
AND    A.COL7 = 'R'
AND    E.COL8 IN('01','02')
Back to top
View user's profile Send private message
adilkhan4all

New User


Joined: 12 Jun 2014
Posts: 3
Location: INDIA

PostPosted: Tue Sep 23, 2014 3:58 pm    Post subject:
Reply with quote

Hi Rohit,
This one is giving me syntax error:
Code:

DSNT408I SQLCODE = -338, ERROR:  AN ON CLAUSE IS INVALID
DSNT418I SQLSTATE   = 42972 SQLSTATE RETURN CODE
DSNT415I SQLERRP    = DSNXOJAJ SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD    = -50  0  0  -1  3679  802 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD    = X'FFFFFFCE'  X'00000000'  X'00000000'  X'FFFFFFFF'  X'00000E5F'  X'00000322'  SQL DIAGNOSTIC INFORMATOIN



Dick,
I am trying to more information on the tables and will definitely look for Unload/sort option this week.

Thanks all for your suggestions.

Regards,
Adil Khan
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1609
Location: NY,USA

PostPosted: Sat Sep 27, 2014 2:53 am    Post subject:
Reply with quote

flip flop below tables.

TABLE1 A,
TABLE2 B,
TABLE3 C,
TABLE4 D,
TABLE5 E
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts High CPU consumption Job using IAM fi... aswinir JCL & VSAM 8 Thu Dec 01, 2016 8:28 pm
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts What are the way we can improve CPU p... Gunapala CN DB2 10 Mon Oct 24, 2016 2:16 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us