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
 

 

How to optimize DB2 SELECT query to reduce cost

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

New User


Joined: 20 Sep 2006
Posts: 61
Location: pune

PostPosted: Tue Jan 08, 2013 3:45 pm    Post subject: How to optimize DB2 SELECT query to reduce cost
Reply with quote

Hi All,

We have one daily running job and in this job one program is getting called in this program below query is taking huge
time to execute, around 95-96% time it's wasting in I/O calls. Please suggest how I can optimize this query.
Please find required details here.

Code:
EXEC SQL                                           
   SELECT IFNULL(SUM(X_AMT),0)         
   INTO :WS-IS-CURNT                             
   FROM XYZ_ABC                           
      WHERE XX_NUM      = :XYZ-ABC.XX-NUM (Table host variables)
       AND YY_NUM       = :XYZ-ABC.YY-NUM  (Table host variables)
       AND X_YEAR   = :WS-YEAR-CUR     (Working-storage variables)       
       AND Y_MONTH = :WS-MONTH-CUR (Working-storage variables)       
   GROUP BY YY_NUM, XX_NUM,                   
            X_YEAR, Y_MONTH           
END-EXEC.                                 


INDEX Columns :
Code:
YY_NUM
XX_NUM
X_YEAR
Y_MONTH
P_CD


Tables Details :
Code:
XX_NUM   CHAR        12
YY_NUM   CHAR        12
X_YEAR    DECIMAL   02
Y_MONTH DECIMAL   02
P_CD       CHAR        02

X_AMT DECIMAL 11



Recommendations From APPTUNE :
Code:
THIS SELECT STATEMENT WAS EXECUTED  1605K TIMES DURING THE REPORTED   
INTERVAL.                                                             
                                                                     
THE STATEMENT'S AVERAGE IN-SQL ELAPSED TIME WAS  00:00.00486 AND THE 
AVERAGE IN-SQL CPU TIME PER STATEMENT WAS  00:00.00011.               
                                                                     
THE NUMBER OF GETPAGES REQUIRED FOR THIS STATEMENT AVERAGES     3.42,
WHICH IS EXCELLENT (LESS THAN  10 ).      0.00  PAGES, OR   0.0 %, ARE
RETRIEVED FROM THE BUFFER POOL WITHOUT INCURRING THE COST OF AN I/O. 
THIS BUFFER POOL HIT RATIO IS POOR (LESS THAN OR EQUAL TO 50 %).     
THIS STATEMENT SPENDS 97.9 % OF ITS TIME IN I/O, WHICH IS THE SINGLE 
LARGEST COMPONENT OF ITS ELAPSED TIME.   


APPTUNE LOGS :


CALL     SECT  STMT   +--- SQL --+ +- TOTAL IN-SQL TIME -+         
TYPE      NO.   NO.   CALLS  ERRS  ELAPSED     CPU         GETPAGES
-------- ----- ------ ------ ----- ----------- ----------- --------
SELECT      30  15505  1502K     0 2:17:03.163 02:26.77652  5243681
                        AVERAGES:  00:00.00548 00:00.00010        3


EXPLIAN for this query is as below
Code:
STMTNO    COST*RATE SQL-STATEMENT                       
15505       6.096961 SELECT IFNULL ( SUM ( X_AMT ) , 0 )


Details of Query are as below -

Code:
STATEMENT TYPE.... STATIC             DISTRIBUTED TYPE.... N/P
CALL TYPE......... SELECT             REQUESTING LOCATION. N/P

EVENT                                      TOTAL     SQL CALL
----------------------------------- ------------ ------------
SQL CALLS..........................:     1501547             
IN-SQL ELAPSED.....................: 2:17:03.163  00:00.00548
IN-SQL CPU.........................: 02:26.77652  00:00.00010
IN-SQL ZIIP........................: 00:00.00000  00:00.00000
GETPAGE COUNT......................:     5243681            3
                                                             
                                                   AVERAGE PE
EVENT                                      TOTAL      SQL CAL
                                                             
IN-SQL CPU TIME...................: 02:26.77652   00:00.00010
SYNC I/O TIME.....................: 1:42:41.312   00:00.00410
ASYNC I/O TIME....................: 31:47.87067   00:00.00127
LOCK TIME.........................: 00:00.17485   00:00.00000
OTHER WAIT TIME...................: 00:00.00000   00:00.00000
UNCATEGORIZED.....................: 00:07.02914   00:00.00000
                                                             
--------------------------------------------------- HIGH/LOW
                   HIGH                                     
CPU.............................................: 00:00.00094
ELAPSED TIME....................................: 00:00.70539
GET PAGES..........................................:        8
SYNCHRONOUS READ I/OS..............................:        6


                                                     AVERAGE 
BUFFER STATISTICS                             TOTAL  PER CALL
------------------------------------------- -------  --------
SQL CALLS.................................: 1501547           
GETPAGE REQUEST...........................: 5243681         3
  GETPAGE FAILURE.........................:       0         0
PREFETCH REQUEST..........................:  651374         0
  SEQUENTIAL..............................:       0         0
  LIST....................................:       0         0
  DYNAMIC.................................:  651374         0
PFETCH PAGES READ.........................:  14885K        10
                                                             
BPOOL UPDATES.............................:       0         0


                                                       TOTAL         TOTAL 
TYPE OF WAIT                                           NUMBER      WAIT TIME
---------------------------------------------------- --------     ----------
I/O WAITS                                                                   
  SYNC I/O.........................................:  2127219     1:42:41.31
  ASYNC READ I/O...................................:   542510     31:47.8706
  ASYNC WRITE I/O..................................:        0             N/
  LOG WRITE I/O....................................:        0             N/
  COMMIT PHASE 1 WRITE I/O WAIT....................:        0             N/
  ARCHIVE LOG......................................:        0             N/
  TAPE ARCHIVE READ................................:        0             N/
Back to top
View user's profile Send private message

enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10256
Location: italy

PostPosted: Tue Jan 08, 2013 4:23 pm    Post subject: Reply to: How to optimize DB2 SELECT query to reduce cost
Reply with quote

the statement was executed about 1.5 millions times
the I/O does not depend on the query per se, but from the <data/row> access pattern and <data/row> distribution

if the data accessed is <uniformly> spread across the whole database/table
no query/buffering optimization will get rid of the I/O,
unless You make the buffer pool capable of holding ...
number of rows returned for a select times 1500000,
but then You will face problems somewhere else
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Tue Jan 08, 2013 9:13 pm    Post subject:
Reply with quote

if x_amt is not updated often, a possible solution could be to add x_amt in the index as an include column.
You didn't mention the clusterratio of the index and whether you are allowed to manipulate the order of the input-records/variables.
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: Tue Jan 08, 2013 9:36 pm    Post subject:
Reply with quote

Hello,

How many rows are in the table? How many of these rows must be "touched" by the process?

Sometimes unloading data and working from a flat file performs far better than a query run over and over or some "monster query". Yours does not appear to be a "monster" by itself, but the number of executions would surely drive up the cost.

It may help someone help you if you explain the entire process briefly and what this particular query does for the process. Possibly there are alternatives . . .

Edit: Didn't see the post from Guy . . . icon_redface.gif
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 JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Omegamon for DB2 query ashek15 DB2 0 Wed Feb 15, 2017 11:25 am
No new posts Query on DB2 WLM stored procedures ashek15 DB2 8 Sun Feb 12, 2017 11:13 pm
No new posts GETMAIN/FREEMAIN query Suja.Sai CICS 9 Tue Jan 31, 2017 12:01 pm


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