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

Refining an SQL


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

New User


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

PostPosted: Wed May 25, 2016 3:12 am
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
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

Global Moderator


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

PostPosted: Wed May 25, 2016 8:09 pm
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: 94
Location: Bangalore, India

PostPosted: Wed May 25, 2016 11:13 pm
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

Global Moderator


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

PostPosted: Thu May 26, 2016 12:20 am
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Search our Forums:

Back to Top