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 issue with DB2 query
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Performance issue with DB2 query
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject: Reply to: Performance issue
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

Senior Member


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

PostPosted: Wed Oct 29, 2008 1:46 pm    Post subject: Re: Reply to: Performance issue
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:

http://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    Post subject:
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    Post subject:
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    Post subject: Reply to: Performance issue with DB2 query
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    Post subject:
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    Post subject: Reply to: Performance issue with DB2 query
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

Site Director


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

PostPosted: Wed Oct 29, 2008 11:39 pm    Post subject:
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    Post subject: Reply to: Performance issue with DB2 query
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    Post subject: Reply to: Performance issue with DB2 query
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    Post subject:
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    Post subject: Reply to: Performance issue with DB2 query
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    Post subject:
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

Senior Member


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

PostPosted: Fri Oct 31, 2008 5:49 am    Post subject:
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    Post subject: Reply to: Performance issue with DB2 query
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    Post subject:
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
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Goto page 1, 2  Next
Page 1 of 2

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts PL/I code tuning/Performance improvement Virendra Shambharkar PL/I & Assembler 4 Mon Dec 05, 2016 11:57 am
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 DFHRESPONSE returns issue divated CICS 3 Wed Nov 02, 2016 6:32 pm
No new posts What are the way we can improve CPU p... Gunapala CN DB2 10 Mon Oct 24, 2016 2:16 pm


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