IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Declaring in DCLGEN an array for variable


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Cecil

New User


Joined: 23 Oct 2007
Posts: 9
Location: chennai

PostPosted: Tue Jun 03, 2008 7:06 pm
Reply with quote

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

Senior Member


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

PostPosted: Tue Jun 03, 2008 7:12 pm
Reply with quote

I would very much like to see the SQL for defining this column.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue Jun 03, 2008 7:13 pm
Reply with quote

in DCLGEN ????

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

pls explain ...
Back to top
View user's profile Send private message
Cecil

New User


Joined: 23 Oct 2007
Posts: 9
Location: chennai

PostPosted: Tue Jun 03, 2008 7:33 pm
Reply with quote

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

Active Member


Joined: 27 Feb 2007
Posts: 691
Location: Earth

PostPosted: Tue Jun 03, 2008 7:34 pm
Reply with quote

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

Active Member


Joined: 27 Feb 2007
Posts: 691
Location: Earth

PostPosted: Tue Jun 03, 2008 7:37 pm
Reply with quote

I just wanted to know if your database is DB2 for sure? The way you explained the field reminds me of ADABAS.
Back to top
View user's profile Send private message
Cecil

New User


Joined: 23 Oct 2007
Posts: 9
Location: chennai

PostPosted: Tue Jun 03, 2008 7:39 pm
Reply with quote

Yes. Its DB2 for sure.
Back to top
View user's profile Send private message
Cecil

New User


Joined: 23 Oct 2007
Posts: 9
Location: chennai

PostPosted: Tue Jun 03, 2008 7:43 pm
Reply with quote

I'm not sure that its the ROWSET stmt... coz in the pgm Im reading all columns of the table into a copybook..
Back to top
View user's profile Send private message
agkshirsagar

Active Member


Joined: 27 Feb 2007
Posts: 691
Location: Earth

PostPosted: Tue Jun 03, 2008 7:44 pm
Reply with quote

Back to sqare one. Answer the Craq's question.
Back to top
View user's profile Send private message
Cecil

New User


Joined: 23 Oct 2007
Posts: 9
Location: chennai

PostPosted: Tue Jun 03, 2008 7:52 pm
Reply with quote

I dont have the SQL..if that's wat u meant by Craq'q question.
I need to create it!
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10888
Location: italy

PostPosted: Tue Jun 03, 2008 7:53 pm
Reply with quote

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

Active Member


Joined: 27 Feb 2007
Posts: 691
Location: Earth

PostPosted: Tue Jun 03, 2008 8:01 pm
Reply with quote

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

New User


Joined: 23 Oct 2007
Posts: 9
Location: chennai

PostPosted: Tue Jun 03, 2008 8:11 pm
Reply with quote

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
icon_mad.gif,
:y,
:z
FROM ..etc

Is this a correct query?
Back to top
View user's profile Send private message
Cecil

New User


Joined: 23 Oct 2007
Posts: 9
Location: chennai

PostPosted: Tue Jun 03, 2008 8:23 pm
Reply with quote

oops.. sorry for the emoticons.. tht was a typo
Back to top
View user's profile Send private message
Cecil

New User


Joined: 23 Oct 2007
Posts: 9
Location: chennai

PostPosted: Tue Jun 03, 2008 9:00 pm
Reply with quote

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?
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10888
Location: italy

PostPosted: Tue Jun 03, 2008 9:18 pm
Reply with quote

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

right from a syntax pint of view but...

why waste resources? having db2 make three moves,
when a
Code:
... SELECT CDCONF INTO ....

and redefining Your variable as three variables or as an array
or using reference modification
is better
Back to top
View user's profile Send private message
Manuneedhi K

Active User


Joined: 07 May 2008
Posts: 115
Location: Chennai

PostPosted: Tue Jun 03, 2008 9:38 pm
Reply with quote

EXEC SQL SELECT
CDCONF(1:3),
CDCONF(3:6),
CDCONF(6:9)
INTO
icon_mad.gif,
:y,
:z


Are you referrring to splitting the column CDCONF (using substring function) into three different host variables? The above sql looks like it.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix

PostPosted: Wed Jun 04, 2008 1:10 am
Reply with quote

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. . .

Code:
SELECT WhatIWant FROM WhereverItIs WHERE SomeConditon
will not resolve to something useful. . .

Maybe i misunderstand, but if i do understand, coding needs to wait until the table definition is resolved.
Back to top
View user's profile Send private message
Cecil

New User


Joined: 23 Oct 2007
Posts: 9
Location: chennai

PostPosted: Wed Jun 04, 2008 6:40 pm
Reply with quote

enrico-sorichetti,
Could u explain a bit more ur soln?I mean how shud the query code look like?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Wed Jun 04, 2008 6:51 pm
Reply with quote

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

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix

PostPosted: Wed Jun 04, 2008 10:19 pm
Reply with quote

Hello Cecil,

Quote:
I mean how shud the query code look like?
Please consider - how can anyone tell you the code until you can tell us the table?

As DBZ mentions, the approach being considered (recommended?) by whoever at your organization is a bad choice. One of the main rules of db2 / sql / relatonal / etc is that there will be no repeating groups. Give each "number" a column and proveed from there.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts REXX/CMS How to place command console... CLIST & REXX 4
No new posts CA7 count with DATE variable CA Products 0
No new posts Extracting Variable decimal numbers f... DFSORT/ICETOOL 17
No new posts Variable Output file name DFSORT/ICETOOL 8
No new posts Moving Or setting POINTER to another ... COBOL Programming 2
Search our Forums:

Back to Top