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

SQL Query optimization.


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
arunsoods

New User


Joined: 13 Jul 2016
Posts: 35
Location: India

PostPosted: Sun Sep 04, 2016 6:27 am
Reply with quote

Hi Team,

While executing below query I am getting 8175.5351 as cost when i am checking through EXPLAIN in BMCADM.

EXEC SQL
SELECT MAX(SEQ_ID)
INTO :DCLTB99-TRANS.SEQ-ID
:SOF-TRANS-SEQ-ID
FROM TEST_TABLE
END-EXEC

Do we have any way by which we can optimize the query so that it can costs lesser.

Thanks and Regards
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Sun Sep 04, 2016 1:36 pm
Reply with quote

What do you want to do with that MAX value? Can't you store the value somewhere when you know it, rather than lazily rummaging about for the highest one?
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK

PostPosted: Sun Sep 04, 2016 2:12 pm
Reply with quote

Why are you using SQL to put it into 2 variables? Put it into one and then copy it to the other in your program. Have you got an index on that table? Probably not - add one.
Back to top
View user's profile Send private message
arunsoods

New User


Joined: 13 Jul 2016
Posts: 35
Location: India

PostPosted: Sun Sep 04, 2016 11:35 pm
Reply with quote

Thanks Bill and Nic for replying...

Nic - If i create INDEX as below on the table

CREATE INDEX INX_SEQ_ID
ON test_table (SEQ_ID)

and then run this query

EXEC SQL
SELECT MAX(SEQ_ID)
INTO :DCLTB99-TRANS.SEQ-ID
:SOF-TRANS-SEQ-ID
FROM TEST_TABLE
END-EXEC

Will this reduce the cost...??

Bill - "Can't you store the value somewhere when you know it, rather than lazily rummaging about for the highest one?."

By the above do you mean that if I select all SEQ_ID using below query.

SELECT SEQ_ID FROM TEST_TABLE

and store the values in a PS file and then applying SORT to find the highest...

Will this work...???
Please suggest icon_idea.gif
Back to top
View user's profile Send private message
arunsoods

New User


Joined: 13 Jul 2016
Posts: 35
Location: India

PostPosted: Sun Sep 04, 2016 11:40 pm
Reply with quote

Bill Woodger wrote:
Can't you store the value somewhere when you know it, rather than lazily rummaging about for the highest one?


By the above do you mean that if I select all SEQ_ID using below query.

SELECT SEQ_ID FROM TEST_TABLE

and store the values in a PS file and then applying SORT to find the highest...

Will this work...???
Back to top
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 316
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Sun Sep 04, 2016 11:56 pm
Reply with quote

Quote:
Will this reduce the cost...??

Quote:
Will this work...???

what stops you from trying?
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL

PostPosted: Mon Sep 05, 2016 12:13 am
Reply with quote

arunsoods wrote:
Nic - If i create INDEX as below on the table

CREATE INDEX INX_SEQ_ID
ON test_table (SEQ_ID)

and then run this query

EXEC SQL
SELECT MAX(SEQ_ID)
INTO :DCLTB99-TRANS.SEQ-ID
:SOF-TRANS-SEQ-ID
FROM TEST_TABLE
END-EXEC

Will this reduce the cost...??

Almost certainly.
Quote:
Bill - "Can't you store the value somewhere when you know it, rather than lazily rummaging about for the highest one?."

By the above do you mean that if I select all SEQ_ID using below query.

SELECT SEQ_ID FROM TEST_TABLE

and store the values in a PS file and then applying SORT to find the highest...

Will this work...???

I believe that what Mr. Woodger meant was to run the query once, and then to reuse either DCLTB99-TRANS.SEQ-ID OR SOF-TRANS-SEQ-ID , whichever you decide to derive directly from the DB2 table.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Tue Sep 06, 2016 5:06 am
Reply with quote

1. Avoid hitting the same query multiple times in the same call.
2.if the table is every day populated by a batch then insert or update a new row with the max count so that online don't have to do the same work again and again. This is what I guess Bill say's.
3. As long as you have a index you could try order by desc clause "with ur and fetch first 1 row only or optimize for 1 row only"
4.Look for OLAP, if that does the trick.
5 BMC cost is not true alway so try executing the query and see for issues
6 Have you spoken to DBA as yet?
Back to top
View user's profile Send private message
arunsoods

New User


Joined: 13 Jul 2016
Posts: 35
Location: India

PostPosted: Tue Sep 27, 2016 4:13 pm
Reply with quote

Hi Team,

Thanks for the support I am getting reduced cost using following query.

Code:
SELECT   SEQ_ID                                             
INTO      :DCLTB996-SOF-TRANS.SEQ-ID
             :W770-SOF-TRANS-SEQ-ID 
FROM     TEST_TABLE                                 
ORDER   BY SEQ_ID DESC
FETCH FIRST ROW ONLY               
WITH     UR         


Using MAX() was increasing the cost. icon_idea.gif
Back to top
View user's profile Send private message
Kerry Ropar

New User


Joined: 14 Sep 2016
Posts: 25
Location: Australia

PostPosted: Wed Sep 28, 2016 8:05 am
Reply with quote

arunsoods wrote:
I am getting reduced cost


You can still get better results if you introduce an index on table.
Back to top
View user's profile Send private message
arunsoods

New User


Joined: 13 Jul 2016
Posts: 35
Location: India

PostPosted: Wed Sep 28, 2016 7:44 pm
Reply with quote

I have tried using indexes too.

But I am not getting Authorization Error.

"SQLCODE = -551, ERROR: YOU DOES NOT HAVE THE PRIVILEGE TO
PERFORM OPERATION CREATE INDEX ON OBJECT TEST_TABLE".

Secondly i want to ask if a field is already a Primary Key do I still need to make index for that...????
In above case it is one of the Primary key.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Wed Sep 28, 2016 8:29 pm
Reply with quote

Quote:
"SQLCODE = -551, ERROR: YOU DOES NOT HAVE THE PRIVILEGE TO
PERFORM OPERATION CREATE INDEX ON OBJECT TEST_TABLE".

Why you do yourself, don't you have any DBA's at your site?

Quote:
Secondly i want to ask if a field is already a Primary Key do I still need to make index for that...????
In above case it is one of the Primary key
It is must to have unique index.
Back to top
View user's profile Send private message
arunsoods

New User


Joined: 13 Jul 2016
Posts: 35
Location: India

PostPosted: Thu Sep 29, 2016 2:25 pm
Reply with quote

Thanks Team for the support i am getting the required results now... icon_biggrin.gif
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