Use Select * in Cursor of a Cobol-DB2 program

chandan.inst · Posted: Thu Aug 13, 2009 11:04 am

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

Succor · Posted: Thu Aug 13, 2009 11:18 am

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.

VivekKhanna · New User Joined: 09 Feb 2009 Posts: 57 Location: India

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.

Ketan Varhade · Posted: Thu Aug 13, 2009 12:47 pm

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.

chandan.inst · Posted: Thu Aug 13, 2009 1:39 pm

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

chandan.inst · Posted: Fri Sep 04, 2009 4:47 pm

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

Regards,
Chandan

Ketan Varhade · Posted: Fri Sep 04, 2009 5:02 pm

Hi

dbzTHEdinosauer · Posted: Fri Sep 04, 2009 5:08 pm

why do you want to worry about NULL-INDICATORS?

RT

FM - it is free-- learn what a -303 SQLCODE means.

chandan.inst · Posted: Fri Sep 04, 2009 5:08 pm

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

dbzTHEdinosauer · Posted: Fri Sep 04, 2009 5:12 pm

Ketan Varhade · Posted: Fri Sep 04, 2009 5:13 pm

Modify\Change the DCL gen variable and then try again

Ketan Varhade · Posted: Fri Sep 04, 2009 5:15 pm

Hi Dick,
Even in my earlier post I tried saying that only but the requirement was so , that’s why I recommended that

dbzTHEdinosauer · Posted: Fri Sep 04, 2009 5:25 pm

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.

chandan.inst · Posted: Mon Sep 07, 2009 12:24 pm

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

Ketan Varhade · Posted: Mon Sep 07, 2009 12:34 pm

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 *?

chandan.inst · Posted: Mon Sep 07, 2009 12:40 pm

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

prino · Posted: Mon Sep 07, 2009 12:43 pm

chandan.inst · Posted: Mon Sep 07, 2009 12:47 pm

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

enrico-sorichetti · Posted: Mon Sep 07, 2009 1:03 pm