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
 

 

Need Clarification on 'IN'

 
Post new topic   This topic is locked: you cannot edit posts or make replies.    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
pkmurali
Warnings : 1

Active User


Joined: 15 Dec 2005
Posts: 237

PostPosted: Thu Mar 12, 2009 5:50 pm    Post subject: Need Clarification on 'IN'
Reply with quote

Dear all,
I have declared an cursor in cob-db2 program as,

EXEC SQL
DECLARE CSR1 CURSOR
SELECT C1 FROM TAB1
WHERE C2 IN(:WS-VAR-GRP)
END-EXEC

Declaration for WS-VAR-GRP
01 WS-VAR-GRP.
05 WS-VAR1 PIC S9(9) COMP.
05 WS-VAR2 PIC S9(9) COMP.
.
.
.
05 WS-VAR20 PIC S9(9) COMP.

Assuming WS-VAR1 & WS-VAR2 has mapping values to C2 of TAB1, On Second fetch will it fetches the value corresponding to WS-VAR2? Generally to obtain the second value SQL will be issued like IN(:WS-VAR1,:WS-VAR2). In this case i am getting the second fetch also. Please explain how this 'IN' works with this group item.

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

CICS Guy

Senior Member


Joined: 18 Jul 2007
Posts: 2150
Location: At my coffee table

PostPosted: Thu Mar 12, 2009 6:52 pm    Post subject:
Reply with quote

Dang, I like learning something new every day. Now if I had a MF handy, I'd test that knowledge.
Try reading Host-variable-arrays in PL/I, C, C++, and COBOL.....
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Thu Mar 12, 2009 9:47 pm    Post subject:
Reply with quote

pkmurali,

your understanding of the 'IN' clause is AFU.

The contents of the IN LIST (it is not a host variable array)
do not have any effect on the access order or the results order;
only determines if a row is to be included in the results table,
as any other where predicate.

Suggest you re-visit the DB2 documentation.
Back to top
View user's profile Send private message
pkmurali
Warnings : 1

Active User


Joined: 15 Dec 2005
Posts: 237

PostPosted: Fri Mar 13, 2009 11:28 am    Post subject:
Reply with quote

Dick,

Correct me if i am wrong.
I agree the point that it is not a host variable array, when i have values for WS-VAR1 & WS-VAR2 while fetching(Second Fetch) it determines the row mapping to WS-VAR2 also, if the group level item is declared as array and the Declare cursor is changed to this order then the fetching is correct.

EXEC SQL
DECLARE CSR1 CURSOR
SELECT C1 FROM TAB1
WHERE C2 IN(:WS-VAR-GRP(1),:WS-VAR-GRP(2))
END-EXEC

But in the existing scenario it is declared as group level item only. then how the values for second fetch is determined?

Please clarify.

Thanks,
Murali
Back to top
View user's profile Send private message
pkmurali
Warnings : 1

Active User


Joined: 15 Dec 2005
Posts: 237

PostPosted: Fri Mar 13, 2009 11:49 am    Post subject:
Reply with quote

In Addition,

1. What’s the difference we get for this

EXEC SQL
DECLARE CSR1 CURSOR
SELECT C1 FROM TAB1
WHERE C2 = :WS-VAR-GRP
END-EXEC

2. There is a chance to get a second row if C2 = :WS-VAR-GRP happens multiple times.

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

New User


Joined: 27 Mar 2006
Posts: 95
Location: India

PostPosted: Fri Mar 13, 2009 12:32 pm    Post subject:
Reply with quote

Hi Murali,
I would like clear one things here. In DB2 cursor, the values are not fetched everytime whenever we use Fetch statement, rather all the matching records/data would have been already fetched when we issue open cursor. And, all the values would be stored in storage space from there we read it record by record using Fetch command.

So, it's not like the second time Fetch would again check with table and get values. Hope, am clear!!!
Back to top
View user's profile Send private message
pkmurali
Warnings : 1

Active User


Joined: 15 Dec 2005
Posts: 237

PostPosted: Fri Mar 13, 2009 1:00 pm    Post subject:
Reply with quote

Rajesh,

I meant to say second fetch in the sense, we normally issue fetch till SQLCODE reach 100, assuming the condition as i mentioned if we have two matchable values in table we will have three execution of fetch statement as third one will end up with sqlcode 100.

Hope my question is clear, how does the 'IN' takes the second (WS-VAR2) since the host variable is an group level item rather host variable array?

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

New User


Joined: 27 Mar 2006
Posts: 95
Location: India

PostPosted: Mon Mar 16, 2009 3:07 pm    Post subject:
Reply with quote

It's really increasing my curiosity on DB2.
I guess based on the size of variables the IN works for group level item.
In your example the variables size S9(9) COMP would be the equivalent size of column(C2) in the table. So, i guess it takes size of the first value and size of the second value. I am not pretty confident, just my guess :-)
Back to top
View user's profile Send private message
pkmurali
Warnings : 1

Active User


Joined: 15 Dec 2005
Posts: 237

PostPosted: Thu Mar 19, 2009 1:12 pm    Post subject:
Reply with quote

Hi Rajesh,

C1 is INTEGER Data type--> DCLGEN C1 Pic s9(9) comp. Thats why i am confused how this code handles the comparison of group level with s9(9) comp (Integer).

Anyone please help me to understand how this works????? icon_cry.gif

Thanks,
Murali
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2284
Location: @my desk

PostPosted: Thu Mar 19, 2009 4:24 pm    Post subject:
Reply with quote

Hello,

We have used the same technique a couple of times. If you have an "IN" clause like this, DB2 considers each sub-item as a separate entry in your IN clause and you just need to specify the group-item variable in the DECLARE CURSOR statement. It works something like this. If you have a working-storage structure like this
Code:
01 group-var
   05 var1 PIC ..
   05 var2 PIC ..
   05 var3 PIC ..
   05 var4 PIC ..
.....
.....
The below WHERE condition
Code:
WHERE col1 IN (:group-var)
works the same way as
Code:
WHERE col1 IN (:var1,:var2,:var3,:var4)
Back to top
View user's profile Send private message
pkmurali
Warnings : 1

Active User


Joined: 15 Dec 2005
Posts: 237

PostPosted: Tue Dec 01, 2009 1:59 pm    Post subject:
Reply with quote

Arun,

As dick said, All Fouled Up for 'IN' icon_biggrin.gif . But the nature of fetch of a cursor statement is fetch one row at a time except multi row-set cursors(implicit cursors). Do 'IN' statement Fouls the fetch concept?
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   This topic is locked: you cannot edit posts or make replies.    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
No new posts Need clarification of VSAM FCT and DF... pkmurali CICS 15 Sun Apr 10, 2016 1:06 am
No new posts Clarification regarding BUFNI & B... thirumalasetty JCL & VSAM 14 Tue Jun 17, 2014 8:22 pm
No new posts DB2 9 - Utilities - COPY,REORG Clarif... gylbharat DB2 3 Mon Oct 22, 2012 12:30 pm
No new posts Clarification on INDEX & SET stat... yugendran COBOL Programming 2 Sat Feb 11, 2012 5:32 pm
No new posts Evaluate statement - clarification Vijay Subramaniyan COBOL Programming 2 Fri Feb 03, 2012 1:53 pm


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