MVSFORUMS.com

Jamylady · Beginner Joined: 04 Nov 2004 Posts: 68 Topics: 22

Hi,
Is there any way where I can use an array as a host variable in the where condition of a cursor declaration?
Eg:
01 WS-STATE-ARRAY.
05 WS-STATES OCCOURS 50 TIMES PIC X(02).

01 WS-STATE-ARRAY-RED REDEFINES WS-STATE-ARRAY.
05 WS-STATE-1 PIC X(02) VALUE 'AA'.
.................................................
05 WS-STATE-1 PIC X(02) VALUE 'AZ'.

PROCEDURE DIVISION.
...
EXEC SQL
DECLARE STATE_CURS CURSOR FOR
SELECT STATE_NAME FROM
DNTSTATES
WHERE STATE_CD IN (:WS-STATE-ARRAY)
END-EXEC.
when I tried this, I am getting the follwoing error.
UNDEFINED OR UNUSABLE HOST VARIABLE \"WS-STATE-ARRAY\"
Could any one explain please?
Thanks
JA

dz · Beginner Joined: 02 Apr 2006 Posts: 26 Topics: 0

DB2 precompiler can only resolve elementary declarations. For example, if you declare:

01 MAIN-ARRAY.
05 ELEM1 PIC X(02).
.........................
05 ELEM50 PIC X(02).

and use this in the cursor, then it will work. Remember to:
- move elements from OCCURS array to this array
- back fill elements at the end with, say, ELEM1 (assuming you do not need spaces).

kolusu · Posted: Thu Jun 15, 2006 8:27 am Post subject:

Jamylady,

Try this code

mangsk · Beginner Joined: 05 Jun 2004 Posts: 46 Topics: 18

Kolusu,
I was facing the same issue and I searched the forum and found this 3 year old post. I tried the solution you suggested, but unfortunately this does not work.
I tried with 3 values and it gave me SQLCODE=100. But when I run it with just 1 value (only moving 'AL' in your example), then it works.
So, is there any solution on this problem yet?
Thank you.
_________________
Regards,
Mangsk

kolusu · Posted: Thu Apr 09, 2009 5:43 pm Post subject:

mangsk,

How is your look up column defined? The example I posted above is when the column is defined as character of 2 bytes. If your Db2 column has a different length then you need to change the length of your host variable too.

Show me your DB2 column definition and a sample data

Kolusu

mangsk · Beginner Joined: 05 Jun 2004 Posts: 46 Topics: 18

Kolusu,
Thank you for your quick turnaround on this.
Here is my array and redefined array definitions.

kolusu · Posted: Fri Apr 10, 2009 6:46 pm Post subject:

mangsk,

I don't have a way to test it but can you try this and see if it works. Your look up field is 26 bytes and character type , so I am enclosing each host variable in quotes , so the length is increased to 28 bytes(opening and closing quote) and I padded spaces up to the 26th byte.

mangsk · Beginner Joined: 05 Jun 2004 Posts: 46 Topics: 18

Kolusu,
Thanks for your help.
I tried this, but it does not work. Now when I comment out last 2 lines (as mentioned in earlier post), it still returns SQLCODE=100.
_________________
Regards,
Mangsk

dbzTHEdinosauer · Posted: Mon Apr 13, 2009 9:32 am Post subject:

MATRIX_ID looks suspiciously like a timestamp.
please do a spufi of the table and select one of the MATRIX_ID's and cut&paste in your reply.

also, please provide the DDL definition of the column in the table. (again, please cut&paste).

what Kolusu has shown you will work, as long as everything is defined properly.
_________________
Dick Brenholtz
American living in Varel, Germany

mangsk · Beginner Joined: 05 Jun 2004 Posts: 46 Topics: 18

dbzTHEdinosauer,
Thanks for the reply.
The MATRIX_ID's mentioned in above mentioned posts are directly taken from SPUFI's against the table.
The definition of MATRIX_ID is (actual copy and pasted from platinum screen)-
MATRIX_ID CHAR(26)
whereas other fields such as CREATE_TIMESTAMP who are of type 'TIMESTMP' show the datatype as 'TIMESTMP'.
You are right, it is a derived value from actual timestamp. This table is populated from a JAVA program which creates unique MATRIX_ID's to insert the row (with information user has entered on a screen) into the table and it derives this timestamp-look-alike MATRIX_ID from actual timestamp of creation.
_________________
Regards,
Mangsk

dbzTHEdinosauer · Posted: Mon Apr 13, 2009 12:56 pm Post subject:

I apologize. The single-quote is incorrect.

return to the 26 char definition for your cobol internal item size.

you are getting a hit on one of the pseudo-timestamps.
try testing by making it the 2nd occurance in your cobol internal table.

either you have incorrect pseudo-timestamps in your IN List
or your columns are sometimes right justified
or your columns sometimes contain low-values.

the methodology works, something, I believe is wrong with your data.
_________________
Dick Brenholtz
American living in Varel, Germany

mangsk · Beginner Joined: 05 Jun 2004 Posts: 46 Topics: 18

dbzTHEdinosauer,

Now I reverted back to kolusu's original solution of 26 bytes.
While I carry out my test, I am using an Intertest(debugging tool similar to Xpeditor). There I can see the values of variables. Here I looked at the value of MTRX-ARRAY-R and it has no low-values, all X'40' in hex padded everywhere.
Also, I commented out 1st and 3rd occurrence and executed just by populating 2nd occurrence, but a SQLCODE=100 this time.
One question though - In the IN clause, we specify values seperated by commas. So when we use redefines clause, there are no commas to seperate our 26 byte values. Now does DB2 know that it has to match the column value with 1st 26 bytes or next 26 bytes etc?
_________________
Regards,
Mangsk

dbzTHEdinosauer · Posted: Tue Apr 14, 2009 9:01 am Post subject:

what I wanted was you to test with the 3 timestamps.
but switch the 1st with the 2nd.
earlier you tested with 3. sqlcode +100
then you removed the last 2, sqlcode 0
so, 1st is good, 2nd and 3rd are bad.
so, put the 1st in the 2nd position
and the 2nd in the 1st position.

no, don't need the commas when your IN List is a host variable array.
db2 knows the column size. it just looks at each 26 bytes.
when you use literals or separate host variables,
the comma in the sql is used by the pre-compiler to build an IN List table.

you could also test by having references to each item, and change your sql
to be a list of host variables, separated by commas. that will proved the validity of all your timestamps.

that is the first thing that you need to do. test each timestamp to insure that it will return a result table for the cursor.
_________________
Dick Brenholtz
American living in Varel, Germany

jsharon1248 · Posted: Tue Apr 14, 2009 9:58 am Post subject:

I really hate to disagree with dbz and Kolusu, but I don't think you can use a host variable array in an IN clause of a SELECT. I know I've tried this, but I never got it to work. DB2 is going to interpret the host variable specified in the IN clause as a single character string. DB2 won't know that there's an OCCURS clause in the COBOL pgm when the variable is used in the context of a WHERE clause. To the best of my knowledge, host variable arrays can only be used for multiple-row FETCH and INSERT. I found this statement in the Using host variables and host variable arrays section in Chapter 9 Embedding SQL statements in host languages of the DB2 Application and Programming and SQL Guide:

dbzTHEdinosauer · Posted: Tue Apr 14, 2009 10:07 am Post subject:

jsharon1248,

sorry, but you are wrong.

it could be that we need to pre-fill the array with high values,
that the IN List objects actually are in error - only one will gen a hit.

I have used these with alpha and numeric types, even substr(?) IN list

not having access to the code in question in this topic,
not being able to motivate the OP to test as I would.
Trying to inspire someone to experiment in the environment that seems to
prevail in the sweatshops in India is itself a daunting task.

But, you can build an host array for IN list and a LIKE list.

why would it work in dynamic SQL and not in imbedded?
_________________
Dick Brenholtz
American living in Varel, Germany