|
View previous topic :: View next topic
|
| Author |
Message |
vaishali tambe
New User
Joined: 16 Apr 2008
Posts: 24
Location: pune
|
|
|
|
From Explain ..
when I see index.. it's showing XP4PD00.
I think this query using this index.
can we use XP4PD02? how? |
|
| Back to top |
|
 |
vaishali tambe
New User
Joined: 16 Apr 2008
Posts: 24
Location: pune
|
|
|
|
Hi Manohar,
PIC clouse in working storage is:
WS-TSQ-PAYID PIC S9(10)V USAGE COMP-3.
WS-TSQ-RUN-ID PIC S9(09) COMP.
WS-TSQ-TIN-FORMAT-CD PIC X(1).
WS-TSQ-TIN-NUM PIC S9(9) USAGE COMP. |
|
| Back to top |
|
|
vaishali tambe
New User
Joined: 16 Apr 2008
Posts: 24
Location: pune
|
|
|
|
And these declarations are equal to cobol declaration for table P4PCLM in DCLGEN.
So i think it's not a problem.
 |
|
| Back to top |
|
|
vaishali tambe
New User
Joined: 16 Apr 2008
Posts: 24
Location: pune
|
|
|
|
Hi Suresh,
I tried running 2 diffrent queries as given above.
no increase inperformance.
|
|
| Back to top |
|
|
Suresh Ponnusamy
Active User
Joined: 22 Feb 2008
Posts: 107
Location: New York
|
|
|
|
Hi Vaishali,
You can query SYSIBM.SYSINDEXES to get the CLUSTERRATIO for the index. Please let us know the value?
If the CLUSTERRATIO for the index XP4PD02 is very less, please talk to your DBA to execute the RUNSTAT utility for the table. Once it is done, you can see the latest CLUSTERRATIO (should be increased) for each index.
And also, please request your DBA to rebind your program (After executing RUNSTAT), run EXPLAIN again and let us know if there are improvements? |
|
| Back to top |
|
|
vaishali tambe
New User
Joined: 16 Apr 2008
Posts: 24
Location: pune
|
|
|
|
Hi Suresh,
Details for index : XP4PD00
Max. ds piece size (KB) : 2097152
Clustering ratio : 86
Is it actually clustered: No
Details for index : XP4PD02
Index type : Type 2 index
Max. ds piece size (KB) : 2097152
Clustering ratio : 0-Not gathere
I don't know which index using this query.[/img] |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
Hi Vaishali,
First of all you need to see which INDEX is being used by your SQL ...you said from EXPLAIN the query is using XP4PD00 which is actually good as it is unique ...if you want to change it to other use WHERE 0=1 in you WHERE clause so as to confuse DB2 ... Now you SQL is a very simple one ...what you can do is put the most restrictive columns first in your WHERE clause ...by restrictive i mean the one for which there will be least number of different data ....
Do a RUNSTATS and maybe a REORG ... |
|
| Back to top |
|
|
Suresh Ponnusamy
Active User
Joined: 22 Feb 2008
Posts: 107
Location: New York
|
|
|
|
Hi Ashimer,
If we use 0 = 1 in the WHERE Clause, the query will return SQLCODE 100.. Am I correct?
I executed a query with WHERE 0 = 1 from one of my tables and got SQLCODE 100.
Hi Vaishali,
As per your query, if it uses XP4PD02 index, it would give better results in performance.
Since the CLUSTERRATIO for the Index XP4PD02 is 0, please talk to your DBA to run the REORG and RUNSTAT utility for this table and check whether the query is using the above index.
Please request you DBA to REBIND your program which has the above query and check the performance. |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
| Sorry use " OR 0 = 1 " |
|
| Back to top |
|
|
tambe vaishali
New User
Joined: 25 Oct 2008
Posts: 1
Location: India
|
|
|
|
Hi,
In my 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 |
When I comment out condition AND P4PRUN_ID = :WS-TSQ-RUN-ID ... then it's working fine.... 
But it's a primary key.. it must be there.... don't know whats happening in db2... |
|
| Back to top |
|
|
Suresh Ponnusamy
Active User
Joined: 22 Feb 2008
Posts: 107
Location: New York
|
|
|
|
| Quote: |
When I comment out condition AND P4PRUN_ID = :WS-TSQ-RUN-ID ... then it's working fine....
|
That means your requirement is satisfied or it is running fast?. If the requirement is satisfied then you can use the above one. or if it is taking less time after removing the key, then we need to analyse further. |
|
| Back to top |
|
|
Bharath Bhat
Active User
Joined: 20 Mar 2008
Posts: 283
Location: chennai
|
|
|
|
| Quote: |
| When I comment out condition AND P4PRUN_ID = :WS-TSQ-RUN-ID ... then it's working fine.... |
I guess the index is taking more time. Try adding OR 0=1 in the where clause as Ashimer said. This will avoid the use of index. |
|
| Back to top |
|
|
vaishali tambe
New User
Joined: 16 Apr 2008
Posts: 24
Location: pune
|
|
|
|
Hi,
I tried using "Or 0 = 1 " in my query and using AND P4PRUN_ID = :WS-TSQ-RUN-ID condition.
It's still getting time...
Any othere way to change index during run-time?? |
|
| Back to top |
|
|
|
|
