Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Query takes 67% of the CPU

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

Active User


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

PostPosted: Fri Dec 28, 2007 6:24 pm    Post subject: Query takes 67% of the CPU
Reply with quote

Hi All,

I have a complaint from DBA saying that one the queries takes 67% of the CPU. Query has left outer join. I copied this query from one of the existing packages to do the same function. This query is used in online program.

DBA has sent me the following information which i could not decode.

SQL_CALL STMTÆ SECTÆ SQL TIMEPCT CPUPCT INDB2_TIME INDB2_CPU
-------- ----- ----- ---------- ------- ------- ------------ ------------
SELECT 01451 00002 5476504 22.09% 39.64% 00:00.000232 00:00.000090
OPEN 01390 00001 12810 63.09% 31.13% 00:00.283133 00:00.030186
SELECT 01490 00004 5431293 6.23% 11.79% 00:00.000066 00:00.000027
SELECT 01473 00003 5476504 2.76% 10.13% 00:00.000029 00:00.000023
FETCH 01550 00001 5431746 1.22% 5.24% 00:00.000013 00:00.000012
SELECT 01519 00005 107072 4.58% 1.92% 00:00.002461 00:00.000223
CLOSE 01564 00001 12811 .00% .00% 00:00.000009 00:00.000008

I dont event know what those columns say. The query is pasted below. query uses the synonyms instead of the qulaified tables.

DECLARE ERID_CURSOR CURSOR FOR
SELECT DISTINCT B.INV_ID
FROM ORG_STRUKTUR_S A
LEFT OUTER JOIN IP_IP_REL_S B
ON SUBSTR(A.IP_ID,1,10) = B.INV_ID_2
WHERE B.IR_KD = 'PRIMAER'
AND (CURRENT TIMESTAMP <= B.TIL_TS OR B.TIL_TS IS NULL)
AND A.OEST_NV = 'RAADGIV'
AND A.OEST_REL_TP IN ('DIREKTE','SAMLAGT','STAB')
AND A.IP_ID_2 = :USER_ID;

DBA says its at the OPEN statement where the query is actually executed. EXPLAIN says it is going for Index scan with MATCHCOLS = 3 for first table and MATCHCOLS = 1 for the second table. COST_CATEGORY is B and i guess its because of RANGE predicates in the WHERE clause and Host variable. (Reason for COST CATEGORY is Host variable).

Do anyone of you has any idea about the data DBA has sent..?. Is that from any of the monitor tools..?. Does COST_TABLE, STRUCTURE_TABLE, PREDICATE_TABLE has something to do with EXPLAIN in DB2 V8 for Z/os..?.

Sorry for lots of questions. I still have lots of questions. Performance tuning is always been a difficult job for me. Please help me with this issue.

Here is the explain results:

PLANNO METHOD TNAME ACCESSTYPE MATCHCOLS INDEXONLY
------ ------ ------------------ ---------- --------- ---------
1 0 ORG_STRUKTUR_S N 3 Y
2 1 IP_IP_REL_S I 1 N
3 3 0 N
Back to top
View user's profile Send private message

PeD

Active User


Joined: 26 Nov 2005
Posts: 456
Location: Belgium

PostPosted: Fri Dec 28, 2007 6:28 pm    Post subject:
Reply with quote

For the clearness then to be more prepared to help, please use the tags ( code /code).
Back to top
View user's profile Send private message
vini_srcna

Active User


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

PostPosted: Fri Dec 28, 2007 6:43 pm    Post subject:
Reply with quote

Thanks PeD.

Here it is in the Code format.

Code:
SQL_CALL  STMTÆ  SECTÆ  SQL        TIMEPCT CPUPCT  INDB2_TIME   INDB2_CPU   
--------  -----  -----  ---------- ------- ------- ------------ ------------
SELECT    01451  00002     5476504  22.09%  39.64% 00:00.000232 00:00.000090
OPEN      01390  00001       12810  63.09%  31.13% 00:00.283133 00:00.030186
SELECT    01490  00004     5431293   6.23%  11.79% 00:00.000066 00:00.000027
SELECT    01473  00003     5476504   2.76%  10.13% 00:00.000029 00:00.000023
FETCH     01550  00001     5431746   1.22%   5.24% 00:00.000013 00:00.000012
SELECT    01519  00005      107072   4.58%   1.92% 00:00.002461 00:00.000223
CLOSE     01564  00001       12811    .00%    .00% 00:00.000009 00:00.000008


Explain results:

Code:
PLANNO  METHOD  TNAME                  TABNO  ACCESSTYPE  MATCHCOLS  INDEXONL
 ------  ------  --------------------  ------  ----------  ---------  --------
      1       0  ORG_STRUKTUR_S             1  N                   3  Y       
      2       1  IP_IP_REL_S                2  I                   1  N       
      3       3                             0                      0  N       


The no of rows in the tables are:

IP_IP_REL_S: 18450790
ORG_STRUKTUR_S: 68652
Back to top
View user's profile Send private message
socker_dad

Active User


Joined: 05 Dec 2006
Posts: 147
Location: Columbia, MO

PostPosted: Mon Dec 31, 2007 10:28 pm    Post subject: Reply to: Query takes 67% of the CPU
Reply with quote

To help us help you, please give the offending query/queries, as well as table information, including keys and foreign keys.

This will help us better analyze your query and make intelligent suggestions.
Back to top
View user's profile Send private message
jontyjun

New User


Joined: 12 Sep 2006
Posts: 12
Location: India

PostPosted: Thu Jan 03, 2008 2:10 pm    Post subject: Share Info Plz
Reply with quote

Hi vini_srcna,

Please share the info if you understood the DBA Results.

Regards,
Arjun.
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 HEX value search in a DB2 query maxsubrat DB2 2 Wed Oct 04, 2017 3:04 pm
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts Can we limit length in concatenation ... balaji81_k DB2 7 Tue Aug 22, 2017 2:50 am
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us