View previous topic :: View next topic
|
Author |
Message |
vaishali tambe
New User
Joined: 16 Apr 2008 Posts: 24 Location: pune
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
look at the- dcl (table declare)
- index definitions
- explain
|
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
insure that the host-variables are correct |
|
Back to top |
|
|
Bharath Bhat
Active User
Joined: 20 Mar 2008 Posts: 283 Location: chennai
|
|
|
|
Unload the records into a flat file, Process them in a (Cobol) program.
Thats the approach we follow. |
|
Back to top |
|
|
vaishali tambe
New User
Joined: 16 Apr 2008 Posts: 24 Location: pune
|
|
|
|
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 |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006 Posts: 6250 Location: Mumbai, India
|
|
|
|
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 |
|
|
vab987
New User
Joined: 01 Dec 2006 Posts: 29 Location: S.A
|
|
|
|
Hi,
A quick query :
How reading a file is more efficient than reading from a table ?
Regards,
Vaibhav |
|
Back to top |
|
|
Bharath Bhat
Active User
Joined: 20 Mar 2008 Posts: 283 Location: chennai
|
|
|
|
Calls to the DB2 are costlier than reading a flat file. |
|
Back to top |
|
|
vaishali tambe
New User
Joined: 16 Apr 2008 Posts: 24 Location: pune
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
vaishali tambe
New User
Joined: 16 Apr 2008 Posts: 24 Location: pune
|
|
|
|
Thanks .. I tried 1st query to get indexs for this table... nothing is returned..
zero raw retrieved.. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Quote: |
I tried 1st query to get indexs for this table... |
Post your version of this query. . . |
|
Back to top |
|
|
vaishali tambe
New User
Joined: 16 Apr 2008 Posts: 24 Location: pune
|
|
|
|
SELECT NAME
, UNIQUERULE
, COLCOUNT
, INDEXTYPE
, REMARKS
FROM SYSIBM.SYSINDEXES
WHERE TBNAME = 'P4PCLM'
AND TBCREATOR = 'GAHPT00D'
ORDER BY TBNAME, NAME; |
|
Back to top |
|
|
vaishali tambe
New User
Joined: 16 Apr 2008 Posts: 24 Location: pune
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
vaishali tambe
New User
Joined: 16 Apr 2008 Posts: 24 Location: pune
|
|
|
|
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 |
|
|
vini_srcna
Active User
Joined: 26 May 2005 Posts: 178 Location: Copenhagen, Denmark
|
|
|
|
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 |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006 Posts: 6250 Location: Mumbai, India
|
|
Back to top |
|
|
vaishali tambe
New User
Joined: 16 Apr 2008 Posts: 24 Location: pune
|
|
|
|
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
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
Suresh Ponnusamy
Active User
Joined: 22 Feb 2008 Posts: 107 Location: New York
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
manoopatil
New User
Joined: 07 Dec 2003 Posts: 56 Location: Montreal
|
|
|
|
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 |
|
|
Suresh Ponnusamy
Active User
Joined: 22 Feb 2008 Posts: 107 Location: New York
|
|
|
|
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 |
|
|
vaishali tambe
New User
Joined: 16 Apr 2008 Posts: 24 Location: pune
|
|
|
|
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 |
|
|
|