Host variables in a Predicate

mkk157 · Active User Joined: 17 May 2006 Posts: 310

Hi,

My Question is
What are all the possibilities to quote a predicate in a COBOL-DB2 program?

Select col-1
,col-2
into field-1
,field-2
where col-2 = xxxx

The possibilities of XXXX are as follows:

Xxxx can be hard coded like where col-2 = 'abc' or where col-2 = 100
Xxxx can be a variable retrieving from another table where a.col-2 = b.col-2
Xxxx can be a host variable (DCLGEN) wheer col-2 = :dclgenname.col-2
Xxxx can be a file attribute where col-2 = :copybook-name.col-2

First 3 are possible, But My requirement is 4th example.
While coding the same example in the query, I am getting an error "UNDEFINED OR UNUSABLE HOST VARIABLE".

Is the 4th example possible or only DCLGEN names should be used (like 3rd example).

arivazhagan_k · New User Joined: 05 Dec 2007 Posts: 57 Location: chennai

I thing it is not possible . While preprocessing itself all copybook names will be expanded with the content of copybook.

Santoshdorge · New User Joined: 27 Jun 2006 Posts: 48 Location: Pune

Hi M K K,

You can use "Xxxx can be a file attribute where col-2 = :copybook-name.col-2" in your embeded query.You might be getting error "UNDEFINED OR UNUSABLE HOST VARIABLE" because of your copybook variable PIC clause and data type of col-2 are not matching.
Please do confirm same and try it once again.

Thanks,
Santosh.

vasanthamugi · New User Joined: 05 Jun 2007 Posts: 5 Location: chennai

can any one tell me a good book on the topic of host variables and DB2 applications

mkk157 · Active User Joined: 17 May 2006 Posts: 310

Hi Santosh,

As per the Performance aspect, Which is better between using the file attribute directly in the Query and the working-storeage variable provided that, beofre the query the value of the file attribute is going to move to the working-storage variable.

First case:

SELECT COL-1
,COL-2
INTO FIELD-1
,FIELD-2
FROM TABLE
WHERE COL-2 = COPYBOOK.ATTRIBUTE

Second Case:

MOVE attribute of copybook to ws-field-name

SELECT COL-1
,COL-2
INTO FIELD-1
,FIELD-2
FROM TABLE
WHERE COL-2 = :WS-FILED-NAME

Santoshdorge · New User Joined: 27 Jun 2006 Posts: 48 Location: Pune

Hi M K K

Looking by the performance angle both things are one and the same.
Moving the file value to any working storage section variable would need one more stmt to execute that is the only diff.Otherwise performance in above two cases would be one and the same.

Thanks,
Santosh.

stodolas · Posted: Thu Dec 20, 2007 5:38 pm

It is not :copybook-name.col-2 , it is 01levelabovecol-2.col-2

dbzTHEdinosauer · Posted: Fri Dec 21, 2007 12:43 am

the qualifier does not have to be an 01 level. Just needs to be a level above (or numerically less than) the data element that adequately resolves the data address.

stodolas · Posted: Fri Dec 21, 2007 12:45 am

Thanks, I wasn't exactly sure on that. I don't normally think of copybook-name as being a level, but rather the filename that is being copied in. Thats what I was trying to get across.

senthilssg · New User Joined: 09 Dec 2005 Posts: 64 Location: USA

Hi All ,

The error was occured in Db2 precompilation phase . Reason for the above error is , Precomplier wont expand the file copy book during the precompilation phase . So Pre-compiler wont understand that file-varaible in the copy book . Suppose if you want to use file-copy book varaible in your SQL , you have to code copy book as follows .