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
 

 

SQL Query optimization.

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

New User


Joined: 13 Jul 2016
Posts: 11
Location: India

PostPosted: Sun Sep 04, 2016 6:27 am    Post subject: SQL Query optimization.
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

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7234

PostPosted: Sun Sep 04, 2016 1:36 pm    Post subject: Reply to: SQL Query optimization.
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: 1715
Location: UK

PostPosted: Sun Sep 04, 2016 2:12 pm    Post subject:
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: 11
Location: India

PostPosted: Sun Sep 04, 2016 11:35 pm    Post subject:
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: 11
Location: India

PostPosted: Sun Sep 04, 2016 11:40 pm    Post subject: Re: Reply to: SQL Query optimization.
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: 268
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Sun Sep 04, 2016 11:56 pm    Post subject:
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: 1738
Location: Bloomington, IL

PostPosted: Mon Sep 05, 2016 12:13 am    Post subject:
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

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Tue Sep 06, 2016 5:06 am    Post subject:
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: 11
Location: India

PostPosted: Tue Sep 27, 2016 4:13 pm    Post subject:
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    Post subject:
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: 11
Location: India

PostPosted: Wed Sep 28, 2016 7:44 pm    Post subject:
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

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Wed Sep 28, 2016 8:29 pm    Post subject:
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: 11
Location: India

PostPosted: Thu Sep 29, 2016 2:25 pm    Post subject:
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    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
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 Query on BLSR Nileshkul JCL & VSAM 2 Sat Aug 13, 2016 5:18 am
No new posts Require help in DB2 Query Shruti Takkar DB2 3 Wed Aug 03, 2016 10:57 pm


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