Declaring in DCLGEN an array for variable

Cecil · New User Joined: 23 Oct 2007 Posts: 9 Location: chennai

Hello all,

I need to declare a variable CDCONF char(3) which occurs 3 times, in DCLGEN and also create an SQL code for reading the value of this variable from the DB2 table. The table is a new one and will be created during prod move by client.

Craq Giegerich · Posted: Tue Jun 03, 2008 7:12 pm

I would very much like to see the SQL for defining this column.

ashimer · Posted: Tue Jun 03, 2008 7:13 pm

in DCLGEN ????

create an SQL code for reading the value of this variable ????

pls explain ...

Cecil · New User Joined: 23 Oct 2007 Posts: 9 Location: chennai

To explain more.
My client is making a new table. He has asked me to create a pgm where this table is to be read using SELECT stmt.
In the data defn of the different columns of the new table, I have the variable or column name CDCONF which occurs 3 times.

Wat shud I do? Im kinda really stuck on this..

agkshirsagar · Posted: Tue Jun 03, 2008 7:34 pm

"WITH ROWSET POSITIONING" feature of DB2 V8.1 and above can help you.
Please see the below link and let us know if you wanted anything different than this.

agkshirsagar · Posted: Tue Jun 03, 2008 7:37 pm

I just wanted to know if your database is DB2 for sure? The way you explained the field reminds me of ADABAS.

Cecil · New User Joined: 23 Oct 2007 Posts: 9 Location: chennai

Yes. Its DB2 for sure.

Cecil · New User Joined: 23 Oct 2007 Posts: 9 Location: chennai

I'm not sure that its the ROWSET stmt... coz in the pgm Im reading all columns of the table into a copybook..

agkshirsagar · Posted: Tue Jun 03, 2008 7:44 pm

Back to sqare one. Answer the Craq's question.

Cecil · New User Joined: 23 Oct 2007 Posts: 9 Location: chennai

I dont have the SQL..if that's wat u meant by Craq'q question.
I need to create it!

enrico-sorichetti · Posted: Tue Jun 03, 2008 7:53 pm

can You please show the relevant create table statements
( at least for the three column involved )
I guess it has been defined as column char(9)

in this case the host variable were the column will be fetched
might be redefined as an array
( a supposition, I do not speak cobol, can be certainly be done in PL/1 )

the select could use the substring operand
the COBOL the reference modifier

agkshirsagar · Posted: Tue Jun 03, 2008 8:01 pm

As enrico suggested we need TABLE creation SQL (At least).
As we understand you need to create SQL query to fetch data from that table.

Cecil · New User Joined: 23 Oct 2007 Posts: 9 Location: chennai

The table is not yet created.. It'll be done by the client later. So, I dont have the create table stmt.
But even if it is the case as u said (char 9), how will the sql stmt b like?
EXEC SQL SELECT
CDCONF(1:3),
CDCONF(3:6),
CDCONF(6:9)
INTO

,
:y,
:z
FROM ..etc

Is this a correct query?

Cecil · New User Joined: 23 Oct 2007 Posts: 9 Location: chennai

oops.. sorry for the emoticons.. tht was a typo

Cecil · New User Joined: 23 Oct 2007 Posts: 9 Location: chennai

I think Ive found a probable somn on internet- host variable array.
Do u think it is the soln? Does anyone have any info regarding using host variable arrays?

enrico-sorichetti · Posted: Tue Jun 03, 2008 9:18 pm

Manuneedhi K · Posted: Tue Jun 03, 2008 9:38 pm

EXEC SQL SELECT
CDCONF(1:3),
CDCONF(3:6),
CDCONF(6:9)
INTO

,
:y,
:z

Are you referrring to splitting the column CDCONF (using substring function) into three different host variables? The above sql looks like it.

dick scherrer · Posted: Wed Jun 04, 2008 1:10 am

Hello,

I've joined the party somewhat late, but i suspect it will be futile to try to write the SELECT code before the table definition is known. . .

Cecil · New User Joined: 23 Oct 2007 Posts: 9 Location: chennai

enrico-sorichetti,
Could u explain a bit more ur soln?I mean how shud the query code look like?

dbzTHEdinosauer · Posted: Wed Jun 04, 2008 6:51 pm

CECIL,

you should defne your table, and each of these 3 variables should be a separate column.

rowsets is used for multiple rows, not multiple columns.

if your user is a complete idiot and tries to jam 3 numeric fields into one column, you should tell him that is not the way to go. he should have a column per field. because of he does not, there is no way to update the '2nd' field in the column without selecting, parsing, modifiying, re-bundling and then updating the column. (or the 1st or 2nd).

everytime somebody uses a char field to hold numerics or combines fields into an char or refuses to use date/time db2 datatypes, just makes the system harder to deal with. db2 is not vsam.

dick scherrer · Posted: Wed Jun 04, 2008 10:19 pm

Hello Cecil,