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
 

 

Use Select * in Cursor of a Cobol-DB2 program

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> COBOL Programming
View previous topic :: :: View next topic  
Author Message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Thu Aug 13, 2009 11:04 am    Post subject: Use Select * in Cursor of a Cobol-DB2 program
Reply with quote

Hi All,

I am aware that it's not advisable to use Select * in Cursor.

But can we code Select * while declaring a cursor.

Also if we specify Select * in a cursor then do we need to specify all Host variables corresponding to Columns of the table or there any other way so that we can get all columns values in a group variables.

E.g. My table is having 40 columns and I am mentioning Select * in cursor then do I need to mention 40 Host variable in INTO clause of FETCH statement or by any way the column values will be fetched in group variable..

Thanks in advance

regards,
Chandan
Back to top
View user's profile Send private message

Succor

New User


Joined: 20 Feb 2009
Posts: 96
Location: Bangalore :)

PostPosted: Thu Aug 13, 2009 11:18 am    Post subject: Reply to: Use Select * in Cursor of a Cobol-DB2 program
Reply with quote

Yes you can give Select * in Declare statement , instead of writing each and every Column name in the Fetch clause ..use the DCLGEN name in the FETCH INTO clause.I think you must be aware why SELECT * is not advisable.
Back to top
View user's profile Send private message
VivekKhanna

New User


Joined: 09 Feb 2009
Posts: 57
Location: India

PostPosted: Thu Aug 13, 2009 12:44 pm    Post subject: Reply to: Use Select * in Cursor of a Cobol-DB2 program
Reply with quote

I am not sure, but I guess Group-Vairable-Name can be used inplace of using 40 Host-Variables. Again, agreeing to the above remarks by Succor, you should know the consequences of using the SELECT * statement.
Back to top
View user's profile Send private message
Ketan Varhade

Active User


Joined: 29 Jun 2009
Posts: 197
Location: Mumbai

PostPosted: Thu Aug 13, 2009 12:47 pm    Post subject:
Reply with quote

if there is such requirement that u require all the column in the program one of the better way is to unload the table and then process the file. This will save the CPU mins as compared to processing it using DB2

Yes u can unload using select * and can get it into the DCLGEN variable.
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Thu Aug 13, 2009 1:39 pm    Post subject:
Reply with quote

Hi all,

Thanks for the replies..

I am aware of the consequences of using Select * in Cursor..

I want to use it for single time so just wanted to know can I use the group variable name instaed of mentioning all Host variables..

Regards,
Chandan
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Fri Sep 04, 2009 4:47 pm    Post subject:
Reply with quote

Hi All,

I tried the abovve query but its givin -303 at Run time

Can anyone plz help me out how to give Null indicators for the columns if I am using Select * or I dont have any other option otherr than coding a column individually icon_cry.gif

Regards,
Chandan
Back to top
View user's profile Send private message
Ketan Varhade

Active User


Joined: 29 Jun 2009
Posts: 197
Location: Mumbai

PostPosted: Fri Sep 04, 2009 5:02 pm    Post subject:
Reply with quote

Hi
Quote:
A VALUE CANNOT BE ASSIGNED TO OUTPUT HOST VARIABLE NUMBER position-number BECAUSE THE DATA TYPES ARE NOT COMPARABLE.


This what is -303 comes for.


Please Verify that table definitions are current and that the host variable has the correct data type.

Hope this help
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Fri Sep 04, 2009 5:08 pm    Post subject:
Reply with quote

why do you want to worry about NULL-INDICATORS?

RT FM - it is free-- learn what a -303 SQLCODE means.
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Fri Sep 04, 2009 5:08 pm    Post subject:
Reply with quote

Hi Ketan,

I am aware of the SQLcode its giving that error as Ia m having Null-indicators under same group variable, so its assiging value to the Nill indicator and giving -303

So I want to know how to code Select * exactly in embedded SQL

regards,
Chandan
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Fri Sep 04, 2009 5:12 pm    Post subject:
Reply with quote

Quote:
Please Verify that table definitions are current and that the host variable has the correct data type


unnecessary if the OP was not so lazy. With ISPF editing features and EDIT MACROs, it is so easy to populate an sql statement with all the column names for both the select and the into phrase.

yeah, it is quicker type SELECT * /FETCH INTO GROUP.
But if you do not know what you are doing, you are going to spend 2 days debugging BS instead of writing proper sql in the first place.
Back to top
View user's profile Send private message
Ketan Varhade

Active User


Joined: 29 Jun 2009
Posts: 197
Location: Mumbai

PostPosted: Fri Sep 04, 2009 5:13 pm    Post subject:
Reply with quote

Modify\Change the DCL gen variable and then try again
Back to top
View user's profile Send private message
Ketan Varhade

Active User


Joined: 29 Jun 2009
Posts: 197
Location: Mumbai

PostPosted: Fri Sep 04, 2009 5:15 pm    Post subject:
Reply with quote

Hi Dick,
Even in my earlier post I tried saying that only but the requirement was so , that’s why I recommended that
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Fri Sep 04, 2009 5:25 pm    Post subject:
Reply with quote

Ketan, I was not disagreeing with you.
The OP obviously has little knowledge of DB2 and is creating problems for himself while trying to be slick.

I have never seen a DCLGEN'd copybook that lumps the NULL-INDICATORs in the same group as the row structure. Sounds to me as if the COPYBOOK is as poorly generated as the requirement.
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Mon Sep 07, 2009 12:24 pm    Post subject:
Reply with quote

Hi Dick,

I guess people have interpreted my issue in wrong sense..

Still I am not getting clear idea what exactly the solution..

This might be due to lack of my knowledge in DB2

My concern is can I select columns in a group variable and if one of my column having null values then how to handle it if I am using group variable as a host variable

E.g. I am coding like below

EXEC SQL
select *
into :ws-group-variable
END-EXEC.

This is working fine if there is no null value in any of the columns

My question was how to handle above code if there is null value in any one of the column..

If I try like

EXEC SQL
select *
into :ws-group-variable
:ws-group-variable-null
END-EXEC.

It does not work and gives an error

and if there is no solution for this then I am aware that I need to code all columns in SELECT as well as in INTO clause with null indicators

I will be very thankful to you if you give solution for this and it will help me to enhance my knowledge..

Regards,
Chandan
Back to top
View user's profile Send private message
Ketan Varhade

Active User


Joined: 29 Jun 2009
Posts: 197
Location: Mumbai

PostPosted: Mon Sep 07, 2009 12:34 pm    Post subject:
Reply with quote

Hi Chandan,
Could you tell us why and for what condition you are coding select *
istead of writing all the column names?
I mean what is your requirement that need to stick to select *?
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Mon Sep 07, 2009 12:40 pm    Post subject:
Reply with quote

Hi Ketan,

I need to select all columns of a table for a particular requirement and its going to be a one time job..

this table having around 150 columns and as its going to be one time job I wanted save the coding efforts for 150 column names,thier host variable names..

If I can't check Null Indicators when I use SELECT * then I dont have other option than coding all columns..

So I have started this post to get if there is any solution apart from coding all columns..

Regards,
Chandan
Back to top
View user's profile Send private message
prino

Active Member


Joined: 07 Feb 2009
Posts: 984
Location: Oostende, Belgium

PostPosted: Mon Sep 07, 2009 12:43 pm    Post subject:
Reply with quote

chandan.inst wrote:
I will be very thankful to you if you give solution for this and it will help me to enhance my knowledge..

There is one very simple rule to enhance your knowledge regarding this:

Never ever use SELECT * in embedded SQL!
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Mon Sep 07, 2009 12:47 pm    Post subject:
Reply with quote

Hi Prino..

I am very well aware of this rule and I ahve mentioned it in my earlier post..

I just wanted to know is there any way to solve my issue
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Mon Sep 07, 2009 1:03 pm    Post subject: Reply to: Use Select * in Cursor of a Cobol-DB2 program
Reply with quote

Quote:
this table having around 150 columns and as its going to be one time job I wanted save the coding efforts for 150 column names,thier host variable names..

If I can't check Null Indicators when I use SELECT * then I dont have other option than coding all columns..

So I have started this post to get if there is any solution apart from coding all columns..
Quote:
I just wanted to know is there any way to solve my issue
OK .. You tried
by now You should have understood that You cannot do it
what is that is not clear in the replies You got icon_question.gif
what else do You want from us icon_question.gif icon_evil.gif


You started this topic on August 13
if You had coded 6 :<destination_variables> and 6 :<null_indicator_variables>
every working day You would have been finished for now

stop wasting everybody's time
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 -> COBOL Programming All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts I can not compile my program PL1 V3.R... Miguel Fernandez PL/I & Assembler 13 Tue Dec 06, 2016 8:30 pm
No new posts IMS BMP program causes 878 system abend Artemk IMS DB/DC 7 Tue Nov 22, 2016 8:26 pm
This topic is locked: you cannot edit posts or make replies. RANDOM Function in COBOL swapnil781 COBOL Programming 2 Tue Nov 15, 2016 6:17 pm
No new posts Updating Cursor row withour using FOR... chandan.inst DB2 15 Tue Nov 08, 2016 11:17 am
No new posts Regarding COBOL Stored Procedure opti... selvamsrinivasan85 DB2 4 Fri Nov 04, 2016 8:57 pm


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