Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Why to use DCLGEN variables in the DB2 query?

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

New User


Joined: 05 Feb 2008
Posts: 2
Location: pune

PostPosted: Thu Feb 28, 2008 9:52 am    Post subject: Why to use DCLGEN variables in the DB2 query?
Reply with quote

i know it is advisable to use DCLGEN variables in the query instead of working storage variables.
but why cant we use working storage variables in the query?
Back to top
View user's profile Send private message

vasanthkumarhb

Active User


Joined: 06 Sep 2007
Posts: 276
Location: Bang,iflex

PostPosted: Thu Feb 28, 2008 10:26 am    Post subject:
Reply with quote

Hello,

When you extract the values or datas from the Table using SQL the values are feeded to DCLGEN variables first. So DCLGEN must be used when passing variables to working-storage variables, for further caluculation.
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Thu Feb 28, 2008 12:39 pm    Post subject:
Reply with quote

Hi,

Have a look here DCLGEN.
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Thu Feb 28, 2008 6:18 pm    Post subject:
Reply with quote

vasanthkumarhb wrote:
Hello,

When you extract the values or datas from the Table using SQL the values are feeded to DCLGEN variables first. So DCLGEN must be used when passing variables to working-storage variables, for further caluculation.


WRONG
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Thu Feb 28, 2008 6:25 pm    Post subject:
Reply with quote

You can use working storage instead of the DCLGEN. It is just advised to use the DCLGEN for various reasons, among them everything is then named consistently between the database and your program.
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Thu Feb 28, 2008 6:56 pm    Post subject:
Reply with quote

And the variables are properly defined!
Back to top
View user's profile Send private message
Phrzby Phil

Active Member


Joined: 31 Oct 2006
Posts: 965
Location: Richmond, Virginia

PostPosted: Thu Feb 28, 2008 7:18 pm    Post subject:
Reply with quote

Perhaps we should say why vasanthkumarhb is WRONG:

A SELECT or FETCH quite clearly states the host vars into which the results are to be populated.

amol - your question also is not quite clear. You (correctly) state that it is "advisable" to use DCLGEN vars, so assuming you know what "advisable" means, you should not be asking "why can't we use..." [my bold], because clearly we can.

Also - DCLGEN vars are working-storage - the compiler doesn't know any difference between them and any other w-s var, because there is none, other than how they got there.

Finally, there might be times when my DCLGEN vars hold the last row SELECTed or FETCHed, but I need one or more cols from another row. I find it cleaner to use other vars for this ancillary access.
Back to top
View user's profile Send private message
amol.kulkarni05

New User


Joined: 05 Feb 2008
Posts: 2
Location: pune

PostPosted: Fri Feb 29, 2008 5:28 pm    Post subject: DB2 query.
Reply with quote

Phrzby Phil wrote:
Perhaps we should say why vasanthkumarhb is WRONG:

A SELECT or FETCH quite clearly states the host vars into which the results are to be populated.

amol - your question also is not quite clear. You (correctly) state that it is "advisable" to use DCLGEN vars, so assuming you know what "advisable" means, you should not be asking "why can't we use..." [my bold], because clearly we can.

Also - DCLGEN vars are working-storage - the compiler doesn't know any difference between them and any other w-s var, because there is none, other than how they got there.

Finally, there might be times when my DCLGEN vars hold the last row SELECTed or FETCHed, but I need one or more cols from another row. I find it cleaner to use other vars for this ancillary access.


Thanks for giving valuable responses.
actually i wanted to ask that what parameters are affected when we use working storage variables rather than DCLGEN variables in DB2 queries?
Why it is advised to use DCLGEN variable in the DB2 query?
Back to top
View user's profile Send private message
ksk

Active User


Joined: 08 Jun 2006
Posts: 356
Location: New York

PostPosted: Fri Feb 29, 2008 5:37 pm    Post subject:
Reply with quote

No parameters will get effected when you use Working-storage variables instead of DCLGEN variables.

Possible syntax errors are high,as per my knowledge,when you use Working storage. DCLGEN is system generated, so no syntax errors would occur.

KSK
Back to top
View user's profile Send private message
Phrzby Phil

Active Member


Joined: 31 Oct 2006
Posts: 965
Location: Richmond, Virginia

PostPosted: Fri Feb 29, 2008 7:01 pm    Post subject:
Reply with quote

Again - DCLGEN vars are in working-storage, as are all vars, I think, except for the few in LINKAGE.

DCLGEN is just a convenience to generate a COBOL record guaranteed to match your table.
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Fri Feb 29, 2008 11:27 pm    Post subject: Re: DB2 query.
Reply with quote

amol.kulkarni05 wrote:
actually i wanted to ask that what parameters are affected when we use working storage variables rather than DCLGEN variables in DB2 queries?
Hi,

Did You want to say that if one defines the same table definition, same as by DCLGEN but with 'different names', then can those 'different-variablea' be used in program or not?
Back to top
View user's profile Send private message
acevedo

Active User


Joined: 11 May 2005
Posts: 344
Location: Spain

PostPosted: Sat Mar 01, 2008 12:28 am    Post subject:
Reply with quote

In my humble opinion the real reason is:

Craq Giegerich wrote:
And the variables are properly defined!
Back to top
View user's profile Send private message
dr_te_z

New User


Joined: 08 Jun 2007
Posts: 71
Location: Zoetermeer, the Netherlands

PostPosted: Tue Mar 04, 2008 4:17 pm    Post subject:
Reply with quote

acevedo wrote:
In my humble opinion the real reason is:

Craq Giegerich wrote:
And the variables are properly defined!
Yes. That's because you do not notice, but DB2 does. Has to do with stage-1/stage-2 predicates. End result is the same but the database performance is worse. As from V8 all this becomes less critical, but programming standards like that were created in the stone-age.
Back to top
View user's profile Send private message
Phrzby Phil

Active Member


Joined: 31 Oct 2006
Posts: 965
Location: Richmond, Virginia

PostPosted: Tue Mar 04, 2008 6:54 pm    Post subject:
Reply with quote

dr_te_z:

Please clarify your comments. Thanks.
Back to top
View user's profile Send private message
dr_te_z

New User


Joined: 08 Jun 2007
Posts: 71
Location: Zoetermeer, the Netherlands

PostPosted: Mon Mar 10, 2008 4:08 am    Post subject:
Reply with quote

Phrzby Phil wrote:
dr_te_z:
Please clarify your comments. Thanks.

suppose you code
Code:
select col1 from tab1 where col2 = :my-own-ws-field

when col2 is defined in db2 as char(8) and you define my-own-ws-fiels as pix x(9) everything seems okay, but they do not match exactly.
When there is an index defined on col2, DB2 might not use it. When you use a host-variable which is exaclty the same format, db2 will use the index.
As from V8 DB2 is smarter in choosing an index or not, but still.
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 HEX value search in a DB2 query maxsubrat DB2 2 Wed Oct 04, 2017 3:04 pm
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts Can we limit length in concatenation ... balaji81_k DB2 7 Tue Aug 22, 2017 2:50 am
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us