IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Query Tuning


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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
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
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
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
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

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Tue Jun 22, 2010 8:47 pm
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
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

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Wed Jun 23, 2010 7:05 pm
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
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
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: 1020
Location: India

PostPosted: Wed Jun 30, 2010 6:15 pm
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
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
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

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Wed Jun 30, 2010 8:27 pm
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
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: 1020
Location: India

PostPosted: Wed Jun 30, 2010 9:22 pm
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts RC query -Time column CA Products 3
No new posts Dynamically pass table name to a sele... DB2 2
No new posts Query on edit primary command CLIST & REXX 5
No new posts Query on edit primary command CLIST & REXX 1
No new posts Issue with EXEC CICS QUERY SECURITY c... CICS 6
Search our Forums:

Back to Top