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
 

 

Host variables in a Predicate

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
mkk157

Active User


Joined: 17 May 2006
Posts: 310

PostPosted: Thu Dec 20, 2007 12:58 pm    Post subject: Host variables in a Predicate
Reply with quote

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).
Back to top
View user's profile Send private message

arivazhagan_k

New User


Joined: 05 Dec 2007
Posts: 57
Location: chennai

PostPosted: Thu Dec 20, 2007 2:23 pm    Post subject:
Reply with quote

I thing it is not possible . While preprocessing itself all copybook names will be expanded with the content of copybook.
Back to top
View user's profile Send private message
Santoshdorge

New User


Joined: 27 Jun 2006
Posts: 48
Location: Pune

PostPosted: Thu Dec 20, 2007 2:28 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
vasanthamugi
Currently Banned

New User


Joined: 05 Jun 2007
Posts: 5
Location: chennai

PostPosted: Thu Dec 20, 2007 3:54 pm    Post subject: Reply to: Host variables in a Predicate
Reply with quote

can any one tell me a good book on the topic of host variables and DB2 applications
Back to top
View user's profile Send private message
mkk157

Active User


Joined: 17 May 2006
Posts: 310

PostPosted: Thu Dec 20, 2007 4:21 pm    Post subject: hI
Reply with quote

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


Back to top
View user's profile Send private message
Santoshdorge

New User


Joined: 27 Jun 2006
Posts: 48
Location: Pune

PostPosted: Thu Dec 20, 2007 4:45 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Thu Dec 20, 2007 5:38 pm    Post subject:
Reply with quote

It is not :copybook-name.col-2 , it is 01levelabovecol-2.col-2
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Fri Dec 21, 2007 12:43 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Fri Dec 21, 2007 12:45 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
senthilssg

New User


Joined: 09 Dec 2005
Posts: 64
Location: USA

PostPosted: Sat Dec 22, 2007 1:28 pm    Post subject:
Reply with quote

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 .

Code:
EXEC SQL
INCLUDE  file-copy-book
END-SQL

instead of coding COPY file-copy-book .


"correct me if I am wrong"

Thanks and Regards
Senthil
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    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 Testing rerad cursor for status with ... John F Dutcher DB2 8 Fri May 19, 2017 9:35 pm
No new posts DB2 9.1.0 not move values to Host-Var... Miguel Fernandez DB2 8 Wed Jan 25, 2017 8:24 pm
No new posts DB2 9.1.0 not move values to Host-Var... Miguel Fernandez DB2 0 Wed Jan 25, 2017 8:23 pm
No new posts DB2 9.1.0 not move values to Host-Var... Miguel Fernandez DB2 0 Wed Jan 25, 2017 8:21 pm
No new posts Protection Exception while move 0 to ... Kevin Vaz CICS 10 Tue Oct 18, 2016 4:19 pm


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