IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Performance issue with DB2 query


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
vaishali tambe

New User


Joined: 16 Apr 2008
Posts: 24
Location: pune

PostPosted: Wed Oct 29, 2008 12:46 pm
Reply with quote

Hi,
folowing DB2 query is taking too much time..Would like to tune this query.Any suggestion? Also there are around 500000 to 600000 records in P4PCLM table.

Code:
  EXEC SQL                                               
      SELECT COUNT(*),                                   
             SUM(P4PCLM_ALWD_AMT)                       
        INTO :WS-TSQ-CLMNO,                             
             :WS-TSQ-CLMAMT                             
        FROM P4PCLM                                     
       WHERE P4PPRVRN_SEQ_NO   = :WS-TSQ-PAYID           
         AND P4PRUN_ID         = :WS-TSQ-RUN-ID         
         AND P4PCLM_IDFMT_CD   = :WS-TSQ-TIN-FORMAT-CD   
         AND P4PCLM_LCPITIN_ID = :WS-TSQ-TIN-NUM         
         WITH UR                                         
  END-EXEC


Edited: Please use BBcode when You post some code, that's rather readable...Anuj
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Wed Oct 29, 2008 12:58 pm
Reply with quote

look at the
  • dcl (table declare)
  • index definitions
  • explain
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Wed Oct 29, 2008 12:59 pm
Reply with quote

insure that the host-variables are correct
Back to top
View user's profile Send private message
Bharath Bhat

Active User


Joined: 20 Mar 2008
Posts: 283
Location: chennai

PostPosted: Wed Oct 29, 2008 1:03 pm
Reply with quote

Unload the records into a flat file, Process them in a (Cobol) program.

Thats the approach we follow.
Back to top
View user's profile Send private message
vaishali tambe

New User


Joined: 16 Apr 2008
Posts: 24
Location: pune

PostPosted: Wed Oct 29, 2008 1:04 pm
Reply with quote

Hi,

Yah host variables are correct..
I m very new to mainframe also to DB2.. what is explain.. how to see index defenation??
Back to top
View user's profile Send private message
Anuj Dhawan

Superior Member


Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India

PostPosted: Wed Oct 29, 2008 1:46 pm
Reply with quote

Hi,
vaishali tambe wrote:
.. what is explain..

When an SQL is executed against or bound to a DB2 database DB2 Optimizer tool defines the access path used to access the data. This access path is defined according to tables’ statistics generated by DB2 Runstats tool.

The Explain command details the access path defined by DB2 and allows you to analyze how the data will be accessed and how you can improve the command’s performance.

You might like to visit this link as well:

publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db2tools.adb.doc.ug/h1xexchap.htm
Back to top
View user's profile Send private message
vab987

New User


Joined: 01 Dec 2006
Posts: 29
Location: S.A

PostPosted: Wed Oct 29, 2008 3:06 pm
Reply with quote

Hi,

A quick query :
How reading a file is more efficient than reading from a table ?

Regards,
Vaibhav
Back to top
View user's profile Send private message
Bharath Bhat

Active User


Joined: 20 Mar 2008
Posts: 283
Location: chennai

PostPosted: Wed Oct 29, 2008 3:27 pm
Reply with quote

Calls to the DB2 are costlier than reading a flat file.
Back to top
View user's profile Send private message
vaishali tambe

New User


Joined: 16 Apr 2008
Posts: 24
Location: pune

PostPosted: Wed Oct 29, 2008 4:07 pm
Reply with quote

can't we modify query only... bcz i have less time... and if i move for batch then it will be lot of work...
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Wed Oct 29, 2008 4:40 pm
Reply with quote

again,
provide the:
dcl for the table create,
host variable definitions

you can get the indexs for the table from this query:
Code:

SELECT NAME
     , UNIQUERULE
     , COLCOUNT
     , INDEXTYPE
     , REMARKS
FROM SYSIBM.SYSINDEXES
WHERE TBNAME =   '<table name>'
AND TBCREATOR = '<creator name>'
ORDER BY TBNAME, NAME;


and the index definitions from this query:
Code:

SELECT K.IXNAME
     , I.UNIQUERULE AS UNIQ
     , K.COLNO
     , K.COLSEQ
     , K.ORDERING
     , K.COLNAME
FROM SYSIBM.SYSKEYS K
   , SYSIBM.SYSINDEXES I
WHERE IXNAME  IN (
                 '<index name1 from prev query>'
--  how evermany indexes.
                ,'<index name2 from prev query>'
                ,'<index name3 from prev query>'
                 )
  AND IXCREATOR = '<same creator as for table>'
  AND IXCREATOR = I.TBCREATOR
  AND IXNAME    = I.NAME
ORDER BY IXNAME, COLSEQ, COLNO
;
Back to top
View user's profile Send private message
vaishali tambe

New User


Joined: 16 Apr 2008
Posts: 24
Location: pune

PostPosted: Wed Oct 29, 2008 6:08 pm
Reply with quote

Thanks .. I tried 1st query to get indexs for this table... nothing is returned.. icon_sad.gif
zero raw retrieved.. icon_sad.gif
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Wed Oct 29, 2008 11:39 pm
Reply with quote

Hello,

Quote:
I tried 1st query to get indexs for this table...
Post your version of this query. . .
Back to top
View user's profile Send private message
vaishali tambe

New User


Joined: 16 Apr 2008
Posts: 24
Location: pune

PostPosted: Thu Oct 30, 2008 9:51 am
Reply with quote

SELECT NAME
, UNIQUERULE
, COLCOUNT
, INDEXTYPE
, REMARKS
FROM SYSIBM.SYSINDEXES
WHERE TBNAME = 'P4PCLM'
AND TBCREATOR = 'GAHPT00D'
ORDER BY TBNAME, NAME;
Back to top
View user's profile Send private message
vaishali tambe

New User


Joined: 16 Apr 2008
Posts: 24
Location: pune

PostPosted: Thu Oct 30, 2008 12:10 pm
Reply with quote

Hi,
I could find out the index and columns for this table .there are 3 indexes for this table.I don't know which one is used.

Index Defenitions P4PCLM XP4PD00
Column Name Seq No O Col Type Length Scale Null Def FP Col Card
P4PRUN_ID 1 A INTEGER 4 0 N N N 253
P4PPRVRN_SEQ_NO 2 A DECIMAL 10 0 N N N 254
P4PCLM_SRCCLM_ID 3 A CHAR 19 0 N N N 786432
P4PCLM_APPL_CD 4 A CHAR 2 0 N N N 3
P4PCLM_CLMLINE_ID 5 A DECIMAL 18 0 N N N 1474560

Index Defenitions P4PCLM XP4PD01
Column Name Seq No O Col Type Length Scale Null Def FP Col Card
P4PPRVRN_SEQ_NO 1 A DECIMAL 10 0 N N N 254
P4PRUN_ID 2 A INTEGER 4 0 N N N 253
P4PCLM_LCPIPIN_ID 3 A DECIMAL 10 0 Y Y N 4672
P4PCLM_LCPITIN_ID 4 A INTEGER 4 0 Y Y N 1216
P4PCLM_IDFMT_CD 5 A CHAR 1 0 Y Y N 3

Index Defenitions P4PCLM XP4PD02
Column Name Seq No O Col Type Length Scale Null Def FP Col Card
P4PPRVRN_SEQ_NO 1 A DECIMAL 10 0 N N N 254
P4PRUN_ID 2 A INTEGER 4 0 N N N 253
P4PCLM_LCPITIN_ID 3 A INTEGER 4 0 Y Y N 1216
P4PCLM_IDFMT_CD 4 A CHAR 1 0 Y Y N 3
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Thu Oct 30, 2008 1:02 pm
Reply with quote

Quote:

....I don't know which one is used.

that is why you need the plan table info (EXPLAIN).

Info on EXPLAIN can be found in any db2 vsn of the application and programmers guide.
Back to top
View user's profile Send private message
vaishali tambe

New User


Joined: 16 Apr 2008
Posts: 24
Location: pune

PostPosted: Thu Oct 30, 2008 1:03 pm
Reply with quote

Hi,
Out of the above 3 indexes first is the unique index and others are duplicate.
Please ltell me whether all columns in index def should be there in query??
Back to top
View user's profile Send private message
vini_srcna

Active User


Joined: 26 May 2005
Posts: 178
Location: Copenhagen, Denmark

PostPosted: Thu Oct 30, 2008 4:08 pm
Reply with quote

Anuj,

DB2 Runstats Tool is inappropriate.

vaishali,

I would suggest to use Null indicator in the INTO clause for SUM fucntion. If no rows are qualified for your query, then the program fails with -305 (if am right) SQLCODE.
Back to top
View user's profile Send private message
Anuj Dhawan

Superior Member


Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India

PostPosted: Fri Oct 31, 2008 5:49 am
Reply with quote

vini_srcna,

Please elobrate a little more.

What I know is RUNSTATS gathers statistics about the data in table spaces and indexes and records the statistics in the DB2® catalog. The information helps DB2 determine access paths at bind time.
Back to top
View user's profile Send private message
vaishali tambe

New User


Joined: 16 Apr 2008
Posts: 24
Location: pune

PostPosted: Fri Oct 31, 2008 10:00 am
Reply with quote

Hey All,
Thakns for all your replies.. but I realy don't know what to do..
Below is the query and index details for the query(Better view in xls).

Query:
Code:
EXEC SQL                                             
        SELECT COUNT(*),                                 
               SUM(P4PCLM_ALWD_AMT)                     
          INTO :WS-TSQ-CLMNO,                           
               :WS-TSQ-CLMAMT                           
          FROM P4PCLM                                   
         WHERE P4PPRVRN_SEQ_NO   = :WS-TSQ-PAYID         
           AND P4PRUN_ID         = :WS-TSQ-RUN-ID       
           AND P4PCLM_IDFMT_CD   = :WS-TSQ-TIN-FORMAT-CD
           AND P4PCLM_LCPITIN_ID = :WS-TSQ-TIN-NUM       
           WITH UR                                       
    END-EXEC 


Code:
Index Defenitions   P4PCLM   XP4PD00   (Unique index)                  
Column Name        Seq No    O   Col Type   Length   Scale   Null   Def   FP   Col Card
P4PRUN_ID                  1    A    INTEGER          4         0    N       N      N             253
P4PPRVRN_SEQ_NO            2    A    DECIMAL         10         0    N       N      N             254
P4PCLM_SRCCLM_ID           3    A    CHAR            19         0    N       N      N          786432
P4PCLM_APPL_CD             4    A    CHAR             2         0    N       N      N               3
P4PCLM_CLMLINE_ID          5    A    DECIMAL         18         0    N       N      N         1474560
                           
Index Defenitions   P4PCLM   XP4PD01    (Duplicate index)                  
Column Name        Seq No    O   Col Type   Length   Scale   Null   Def   FP   Col Card
P4PPRVRN_SEQ_NO            1    A    DECIMAL         10         0    N       N      N             254
P4PRUN_ID                  2    A    INTEGER          4         0    N       N      N             253
P4PCLM_LCPIPIN_ID          3    A    DECIMAL         10         0    Y       Y      N            4672
P4PCLM_LCPITIN_ID          4    A    INTEGER          4         0    Y       Y      N            1216
P4PCLM_IDFMT_CD            5    A    CHAR             1         0    Y       Y      N               3
                           
Index Defenitions   P4PCLM   XP4PD02   (Duplicate index)                  
Column Name        Seq No    O   Col Type   Length   Scale   Null   Def   FP   Col Card
P4PPRVRN_SEQ_NO            1    A    DECIMAL         10         0    N       N      N             254
P4PRUN_ID                  2    A    INTEGER          4         0    N       N      N             253
P4PCLM_LCPITIN_ID          3    A    INTEGER          4         0    Y       Y      N            1216
P4PCLM_IDFMT_CD            4    A    CHAR             1         0    Y       Y      N               3

I tried query with whatever order of columns in index ... no use icon_sad.gif
what else can be done??
Please help.
And to do explain I don't have authority.


Edited: Please use BBcode when You post some code, that's rather readable...Anuj
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Fri Oct 31, 2008 2:48 pm
Reply with quote

Quote:

And to do explain I don't have authority.


you don't have authority to create an entry to a plan table that is not yours.
you need to set up your own plan table.
Back to top
View user's profile Send private message
Suresh Ponnusamy

Active User


Joined: 22 Feb 2008
Posts: 107
Location: New York

PostPosted: Fri Oct 31, 2008 9:53 pm
Reply with quote

Hi

Did you try splitting the query in to 2 and check?

Code:

EXEC SQL                                             
        SELECT COUNT(*),                                 
           INTO :WS-TSQ-CLMNO,                           
           FROM P4PCLM                                   
         WHERE P4PPRVRN_SEQ_NO   = :WS-TSQ-PAYID         
           AND P4PRUN_ID         = :WS-TSQ-RUN-ID       
           AND P4PCLM_IDFMT_CD   = :WS-TSQ-TIN-FORMAT-CD
           AND P4PCLM_LCPITIN_ID = :WS-TSQ-TIN-NUM       
           WITH UR                                       
END-EXEC 



and

Code:

EXEC SQL                                             
        SELECT SUM(P4PCLM_ALWD_AMT)
           INTO :WS-TSQ-CLMAMT                           
          FROM P4PCLM                                   
         WHERE P4PPRVRN_SEQ_NO   = :WS-TSQ-PAYID         
           AND P4PRUN_ID         = :WS-TSQ-RUN-ID       
           AND P4PCLM_IDFMT_CD   = :WS-TSQ-TIN-FORMAT-CD
           AND P4PCLM_LCPITIN_ID = :WS-TSQ-TIN-NUM       
           WITH UR                                       
END-EXEC 


Please try and let us know if there is any improvement in the performance.

And also, please check whether you have any utilities like BMC Apptune etc. to find out the SQL Performance. Please discuss with your Team mates and DBA about the tool.. I am sure your shop must be having something..
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Fri Oct 31, 2008 11:10 pm
Reply with quote

Suresh,

The count may run faster than the double select,
but the select for P4PCLM_ALWD_AMT is still going to take as long,
since that column needs to be retrieved from the row -
it is not available in an index.

so you would take a query that runs with x amount of resources
and split it into two,
one would still take x (minus the cost for the SUM which is not going to amount to much)
and the second (the count) would take y.

instead of x amount of resources you want to have two queries that would amount to x+y.

way i see it, only way to reduce resource cost would be to add P4PCLM_ALWD_AMT to an index -
and I believe that is not an option for the OP.
Back to top
View user's profile Send private message
manoopatil

New User


Joined: 07 Dec 2003
Posts: 56
Location: Montreal

PostPosted: Sat Nov 01, 2008 2:53 am
Reply with quote

Vaishali,

What is the working storage definition - PIC clause for the following host variables used in the WHERE clause.

:WS-TSQ-PAYID
:WS-TSQ-RUN-ID
:WS-TSQ-TIN-FORMAT-CD
:WS-TSQ-TIN-NUM

Sometime if the host variable definition is not compatible with column definition the index is not used.

Regards
-Manohar
Back to top
View user's profile Send private message
Suresh Ponnusamy

Active User


Joined: 22 Feb 2008
Posts: 107
Location: New York

PostPosted: Sat Nov 01, 2008 2:58 am
Reply with quote

Hi Dick,

I accept this. But here I have a question. Do you think if we select list of columns from a table which are not part of the index, to be added as part of the index to improve the performance?.

Here the both the query will be using INDEX since the columns which are used in the WHERE clause are part of the Index XP4PD02.

So both the query will use INDEX SCAN to fetch the records. The SUM query should fetch all the values satisfying the WHERE clause to calculate the sum of the amounts. May be if we add the select column also as part of INDEX, even then it would take the same time since we are selecting all the records for WHERE Clause. Please correct me if I am wrong.

Vaishali,

Please check the CLUSTERRATIO for the INDEX XP4PD02 and this query is using the above index. Please discuss with your DBA to execute the RUNSTAT as advised and check the performance again.

Suggestions are welcome.
Back to top
View user's profile Send private message
vaishali tambe

New User


Joined: 16 Apr 2008
Posts: 24
Location: pune

PostPosted: Wed Nov 05, 2008 12:39 pm
Reply with quote

hI,
I am able to rn EXPLAIN for this query.But Don,t know How to analyse it ?.
How to see clusterratio?

How RUNSTAT will help?
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2 Goto page 1, 2  Next

 


Similar Topics
Topic Forum Replies
No new posts SFTP Issue - destination file record ... All Other Mainframe Topics 2
No new posts RC query -Time column CA Products 3
No new posts Dynamically pass table name to a sele... DB2 2
No new posts exploiting Z16 performance PL/I & Assembler 2
No new posts Query on edit primary command CLIST & REXX 5
Search our Forums:

Back to Top