MVSFORUMS.com

[jsharon1248]

dbz

Like I said, I've tried this in the past, but have not been able to get this to work. Does my failure mean it can't be done? Of course not. I actually prefer to be wrong on this because this technique would be useful. I base my disagreement on my own failures and the statement in the DB2 manual. I just don't see how DB2 would distinquish between a host variable and a host variable array in the context of the WHERE clause. In the previous examples, the host variables are REDEFINEs of the COBOL variables with the subordinate OCCURS. How would DB2 know that it should be using a host variable array and not just a host variable?

[dbzTHEdinosauer]

jsharon1248,

1. I apologize. I was incorrect

2. After a little research (thank someone that I have indexed all my code on my pc)
I found that I used a temporary table, IN (Select whatever from temp.table)

3. otherwise, as you stated, each element must be referenced individually.
_________________
Dick Brenholtz
American living in Varel, Germany

[mangsk]

dbzTHEdinosauer, jsharon1248,

Thanks a lot jsharon1248.
jsharon1248 has elaborated the same question I had in my earlier post. But unfortunately the listed 2 alternatives I am trying to avoid. Defining each element is to be avoided because there could be >120 elements there and for dynamic SQL, our shop doesn't allow that.

dbzTHEdinosauer, I tried what you have suggested, but everytime I get SQLCODE=0 whenever I put any of the MATRIX_ID in the first position of the array, provided other 2 MOVE statements are commented out. There is nothing wrong in L or R justification or padded values.
_________________
Regards,
Mangsk

[dbzTHEdinosauer]

Mangsk,

as I said in the previous post, I was incorrect that you could reference an array as an IN List.

need to list each item separately or - nowadays, global temp tables are easy, quick,
and the dba's don't complain too much.

we had a table with 3 columns that we called a scratch pad (our dba's did not allow global temp tables - or dynamic sql).

1st column was application key (every module that required the scratch pad would load this column with program name.

2nd column was integer

3rd column was char 40 (sometimes we had to look for names)

loaded the 3rd column with the input values for the IN List.

then did the cursor select with an IN (select 3rd-column where 1st column = program name.

after the cursor was complete, would delete scratchpad where 1st column = prg name.

sorta hookey, but it worked.
_________________
Dick Brenholtz
American living in Varel, Germany

[mangsk]

dbzTHEdinosauer,

Thanks for the suggestion.
_________________
Regards,
Mangsk

[jsharon1248]

dbz, no apologies necessary. You post more than your share of informative responses. Michael Jordan missed a shot now and then too.

mangsk
No dynamic SQL?!??!? In my opinion, this is one of the most powerful features available to every day grunts like you and me crunching out code. This is an opportunity to educate whoever prohibits dynamic SQL. This is a problem that can be solved very easily with minimal coding using dynamic SQL. If you can't sell that, use dbz's suggestion, declared global temp tables (DECLARE GLOBAL TEMPORARY TABLE). You might need to work with a DBA to get a tablespace and authorizations. These are nice because they're defined to your thread and only your thread can use it. Once the thread is killed, your table goes down with it. All you'd do is update the program to add the DECLARE and loop through INSERTs to populate the table. Then add the IN predicate with the non-correlated sub-query to the temp table (where xyz_col in (select xyz_val from session.tmp_tbl)).

[dbzTHEdinosauer]

Mangsk,

The pseudo-timestamps are generated via timestamp access.
How many rows do you expect to be returned for each pseudo-timestamp?

If 1 or just a few(<100), then I would suggest changing your logic:

if more than one, do a rowset singleton select for each pseudo-timestamp.
if only 1, then do a singleton select w/o rowset

either way, demand an index on the id column. (after implementation).

reasons:

1. 100 or so IN list items would be 'somewhat slow'.
2. with an index, the singleton selects would be 'reasonably fast'.
3. • dynamic sql or temp tables would require fighting management,
   • and the dba's just might tell you to do what I am suggesting here.
4. most importantly, you can implement this in hours, not days of discussion

after the implementation, you then have time to do the necessary politiking to get dynamic sql or temp tables in use at your site.

just a suggestion.
_________________
Dick Brenholtz
American living in Varel, Germany

[mangsk]

dbzTHEdinosauer, jsharon1248,

Thanks for all your help.
_________________
Regards,
Mangsk

[kolusu]

Mangsk,

I was pretty tied up for the past 2 days and I have no way of testing this , but I guess I have to take that array processing does NOT work with host-variable array in DB2. sorry.

Thanks DBZ and Jsharon.

Kolusu