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
 

 

Query Tuning

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

New User


Joined: 11 Jul 2005
Posts: 87

PostPosted: Tue Jun 22, 2010 11:58 am    Post subject: Query Tuning
Reply with quote

Can you please let me know whether the below query can be fine tuned.

Looking at the high level, can it be fin tuned?

Code:
SELECT A.NAME,
SUM(TBL2.VAL1),
SUM(TBL2.VAL2),
SUM(TBL2.VAL3),
SUM(TBL2.VAL4),
SUM(TBL2.VAL1 + TBL2.VAL3),
SUM(TBL2.VAL2 + TBL2.VAL4),
DT.C_EXT
TBL1.TOTAL,
'TEST VALUE'

FROM KA05.U_NAME  A,
SI.101_T2 TBL2,
KA05.AK_CON CT
KA05.AK_HPS TS
KA05.DATS DT
SI.101_T1 TBL1

WHERE CT.TYPE IN ('Z', 'H')
AND CT.STAT IN ('K','L','M','S')
AND CT.ITYP IN ('J','R','F')
AND TBL2.T_3 = 'B'
AND CT.VER = DT.C_EXT
AND CT.TCD = TS.TCD
AND TS.CMP = A.CMP
AND TS.CMP = CT.CMP
A.CMP = TBL1.CMP
AND TS.A_IND = 'N'
AND TS.PBLE = TBL2.TB_1
AND TS.ASLE = TBL2.TB_2

GROUP BY A.NAME,
DT.C_EXT,
TBL1.TOTAL

UNION ALL

SELECT A.NAME,
SUM(TBL2.VAL1),
SUM(TBL2.VAL2),
SUM(TBL2.VAL3),
SUM(TBL2.VAL4),
SUM(TBL2.VAL1 + TBL2.VAL3),
SUM(TBL2.VAL2 + TBL2.VAL4),
DT.C_EXT
TBL1.TOTAL,
'TEST VALUE'

FROM KA05.U_NAME  A,
SI.101_T2 TBL2,
KA05.AK_CON CT
KA05.AK_HPS TS
KA05.DATS DT
SI.101_T1 TBL1

WHERE CT.TYPE IN ('Z', 'H')
AND CT.STAT IN ('K','L','M','S')
AND CT.ITYP IN ('J','R','F')
AND TBL2.T_3 = 'A'
AND YEAR(CT.VER) = YEAR(DT.C_EXT)
AND MONTH(CT.VER) = MONTH(DT.C_EXT)
AND CT.TCD = TS.TCD
AND TS.CMP = A.CMP
AND TS.CMP = CT.CMP
A.CMP = TBL1.CMP
AND TS.A_IND = 'N'
AND TS.PBLE = TBL2.TB_1
AND TS.ASLE = TBL2.TB_2

GROUP BY A.NAME,
DT.C_EXT,
TBL1.TOTAL

ORDER BY 10, 9 DESC, 1


Thanks in advance.

Rregards,
Chinni.
Back to top
View user's profile Send private message

Ketan Varhade

Active User


Joined: 29 Jun 2009
Posts: 197
Location: Mumbai

PostPosted: Tue Jun 22, 2010 2:35 pm    Post subject:
Reply with quote

are you using this query in a src code or you are simply unload this data using this query in the file
Back to top
View user's profile Send private message
chinnielr

New User


Joined: 11 Jul 2005
Posts: 87

PostPosted: Tue Jun 22, 2010 4:43 pm    Post subject: Reply to: Query Tuning
Reply with quote

Ketan,

This query is being used in source code.

Regards,
Chinni.
Back to top
View user's profile Send private message
Ketan Varhade

Active User


Joined: 29 Jun 2009
Posts: 197
Location: Mumbai

PostPosted: Tue Jun 22, 2010 4:46 pm    Post subject:
Reply with quote

try to unload the query using the unload utility and the process the file in the SRC, this will reduce CPU usage and also reduce the usage of the DB2
Back to top
View user's profile Send private message
chinnielr

New User


Joined: 11 Jul 2005
Posts: 87

PostPosted: Tue Jun 22, 2010 7:49 pm    Post subject: Reply to: Query Tuning
Reply with quote

I have tried executing the query from the file and the result is same.
I do not see much of difference in terms of performance.

Regards,
Chinni
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 Jun 22, 2010 8:47 pm    Post subject:
Reply with quote

Hello,

Queries that are "resource hogs" will be so whether run from a file or within code.

You need to analyze the sql and determine where the time is being wasted. . .

Suggest you work with the dba to determine alternatives to this query.
Back to top
View user's profile Send private message
Ketan Varhade

Active User


Joined: 29 Jun 2009
Posts: 197
Location: Mumbai

PostPosted: Wed Jun 23, 2010 10:34 am    Post subject:
Reply with quote

If you have some tools like APTUNE or STROBE you can get the detail analysis of the code where the code is consuming more CPU usage. check that.
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 Jun 23, 2010 7:05 pm    Post subject:
Reply with quote

Hello,

Tracking cpu tme for a query is often just spinning wheels. . .

The single major cause of time wasted in a query is how much interaction with the database is needed. . . Certainly more interaction uses more cpu, but the issue/cause is this interaction, not the cpu usage.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Thu Jun 24, 2010 3:44 pm    Post subject:
Reply with quote

Probably you could try making a temporary table with the complete data satisfying all your where clause and then do a sum on this table.
For populating the temp table use an INSERT with full select.
Or else you can even use a CTE (common table expression) which would help you eliminate the creation of temp table and inserts.
Back to top
View user's profile Send private message
ankvardhan

New User


Joined: 05 Sep 2008
Posts: 14
Location: Mumbai

PostPosted: Wed Jun 30, 2010 1:18 pm    Post subject: Regarding query tuninig...!!
Reply with quote

Hi,

As per your Query, try the following activities:
1) Replace Union by (IN OR Between clause these give better performance)
2) Try to Optimize for "N" possible rows (during fetch).
3) Avoid "SORT" if possible.

Also from DBA prespective, check the Clusterratio in Sysibm.Sysindexes it should be 100, if its less than 80 your I/O operations performance get degraded....!!

Hope these things might help you...!!

Thanks
Ank
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Wed Jun 30, 2010 6:15 pm    Post subject:
Reply with quote

Hi Chinnielr,

Can you tell me,

1. How often this query is executed ?

2. How often table is updated ?


Thanks,
Sushanth
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Jun 30, 2010 6:28 pm    Post subject:
Reply with quote

This is a UNION of a six (6) table join
to
a six (6) table join.


we do not have any idea of the machine & OS,
version of DB2

we have no idea of the size of the tables,
the expected number of returned rows.
their keys,
their runstats,
their rebinds.


Nor do we have any idea of the relationships of the tables,
the columns used as predicates ... and on and on and on and on and on

The fact that this is imbedded sql,
my first inclination would to be break-up this crap into several CURSORS and do a match merge.

But we only have this bare sql, and are asked how to tune it.
That is like being given a nail-file and told to tune up a car.......

we need more information from the TS.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Jun 30, 2010 6:42 pm    Post subject:
Reply with quote

This reminds me of the sql written by 'graduates' of IBM sql classes,
who come back and write these complicated queries,
that in theory work and can run,
and save all that requirement to write application code
but we forget that this takes iron to run on,
and IBM is very good at linking everything to more sales
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 Jun 30, 2010 8:27 pm    Post subject: Reply to: Query Tuning
Reply with quote

Quote:
who come back and write these complicated queries,
that in theory work and can run,

And these are always demonstrated using tables that only have a few rows. . . . See? The query runs sub-second. . .

When one has the hardware concession, the only time tuning comes into play is when the wall-time gets really ugly.

The tendancy to put way too much code into a single query is quite scary . . . At least to me icon_rolleyes.gif

d
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Jun 30, 2010 9:17 pm    Post subject:
Reply with quote

as an aside,

IBM, IMUO, sells the best hardware/OPS-System in the world.
I thoroughly enjoy IBM COBOL, REXX, etc..
I also support DB2 over other rational db systems.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Wed Jun 30, 2010 9:22 pm    Post subject:
Reply with quote

Hi,

Quote:
This reminds me of the sql written by 'graduates' of IBM sql classes,
who come back and write these complicated queries,
that in theory work and can run,


Quote:
And these are always demonstrated using tables that only have a few rows.

The tendancy to put way too much code into a single query is quite scary . . . At least to me

When i was in college, 3 years back. I had no idea about, there could be so much of performance issues and stuff like that.
All i knew is, a table will have columns and it can hold lots of rows. And, i have created a few tables in college and those tables will not have more than 10 - 20 rows. Most of the time in college, i had put of lots of things in the queries itself, so there would be less programming work.

Now, the scenario is completely opposite.

Since the query the TS posted has a lot of calculations, if the table is updated rarely and query is executed oftenly. MQT would be a good choice, i think.

Thank You,
Sushanth
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 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 Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts SQL Query optimization. arunsoods DB2 12 Sun Sep 04, 2016 6:27 am


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