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
 

 

Refining an SQL

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

New User


Joined: 07 Apr 2010
Posts: 93
Location: Bangalore, India

PostPosted: Wed May 25, 2016 3:12 am    Post subject: Refining an SQL
Reply with quote

Hi...

I have the below SQL as a Cursor declaration in my application (COBOL)

Could any one please help refine it?

Code:
SELECT COLUMN1, COLUMN2 from TABLE1
Where
(COLUMN3 = :input1 value AND "N" = :WS-flag)
Or
(COLUMN4 = :input2 value AND "Y" = :WS-flag)


COLUMN3 and COLUMN4 are 2 different indexes on TABLE1.
When I checked the EXPLAIN, the access type is M, MX, MI.

COBOL logic behind WS-flag derivation
Code:
IF INPUT3 = 'valid value'
MOVE 'Y' TO WS-flag
ELSE
MOVE 'N' TO WS-flag



Thanks...
Back to top
View user's profile Send private message

rajesh1183

New User


Joined: 07 Jan 2008
Posts: 98
Location: Hyderabad

PostPosted: Wed May 25, 2016 11:42 am    Post subject: Reply to: Refining an SQL
Reply with quote

is it possible to split the query into two..icon_question.gif such that the optimizer would pick the required index based on your query which improves the performance..

Code:

Cursor1:
SELECT COLUMN1, COLUMN2 from TABLE1
Where
(COLUMN3 = :input1 value )


Code:

Cursor2:
SELECT COLUMN1, COLUMN2 from TABLE1
Where
(COLUMN4 = :input2 value )



Code:

IF INPUT3 = 'valid value'
    OPEN, FETCH -- CURSOR2
ELSE
    OPEN, FETCH -- CURSOR1
END-IF
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


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

PostPosted: Wed May 25, 2016 8:09 pm    Post subject:
Reply with quote

Quote:
is it possible to split the query into two..
why not?
Try
Code:
UNION
instead of
Code:
OR

Also try something like this too, it works and we implemented it.
Code:
('N' = :WS-flag OR  :WS-flag = ' ')
Back to top
View user's profile Send private message
TS70363

New User


Joined: 07 Apr 2010
Posts: 93
Location: Bangalore, India

PostPosted: Wed May 25, 2016 11:13 pm    Post subject:
Reply with quote

Hi...Rajesh..

Thanks.

Yes. I can split the queries into 2 which I know is the best possible way and was also thought of before posting the query.

The only reason I am not planning to split is because I have 3 such cursors in the production version of application program. And implementing a split would result in declaration, open, fetch, close of 3 more cursors. Can still be implemented if I do not find any other way.

Hi Rohit,
Can you please elaborate?
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


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

PostPosted: Thu May 26, 2016 12:20 am    Post subject:
Reply with quote

Quote:
And implementing a split would result in declaration, open, fetch, close of 3 more cursors. Can still be implemented if I do not find any other way.

So what ? At a moment you are only going to work with one cursor and not 2 or 3 together.
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:



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