Need Clarification on 'IN'

pkmurali · Active User Joined: 15 Dec 2005 Posts: 271

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

CICS Guy · Posted: Thu Mar 12, 2009 6:52 pm

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.....

dbzTHEdinosauer · Posted: Thu Mar 12, 2009 9:47 pm

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.

pkmurali · Active User Joined: 15 Dec 2005 Posts: 271

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

pkmurali · Active User Joined: 15 Dec 2005 Posts: 271

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.

rajesh_mbt · New User Joined: 27 Mar 2006 Posts: 97 Location: India

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!!!

pkmurali · Active User Joined: 15 Dec 2005 Posts: 271

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.

rajesh_mbt · New User Joined: 27 Mar 2006 Posts: 97 Location: India

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 :-)

pkmurali · Active User Joined: 15 Dec 2005 Posts: 271

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?????

Thanks,
Murali

Arun Raj · Posted: Thu Mar 19, 2009 4:24 pm

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

pkmurali · Active User Joined: 15 Dec 2005 Posts: 271

Arun,

As dick said, All Fouled Up for 'IN'

. 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?