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
 

 

How does Declare Table work?

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

New User


Joined: 06 Mar 2007
Posts: 32
Location: India

PostPosted: Thu Sep 11, 2008 4:06 pm    Post subject: How does Declare Table work?
Reply with quote

Hi,

I have a COBOL/DB2 code, which has a declare statement as given below,

EXEC SQL
DECLARE AAA_BBB_CCC TABLE
(
Field names with their data types are defined.......
)
END-EXEC.

A cursor is defined on the table that is declared above,from which data is fetched and written to output files.
Nowhere in the code, data is inserted into this temp table, which can latter be retrieved using the cursor.

Please let me know how(usually) delcared tables are populated with data.

Thanks,
Siva
Back to top
View user's profile Send private message

Aaru

Senior Member


Joined: 03 Jul 2007
Posts: 1288
Location: Chennai, India

PostPosted: Thu Sep 11, 2008 4:24 pm    Post subject: Reply to: How does Declare Table work?
Reply with quote

Siva,

Quote:
EXEC SQL
DECLARE AAA_BBB_CCC TABLE
(
Field names with their data types are defined.......
)
END-EXEC.


By any chance, are you referring to DECLARE table of DCLGEN???

Quote:
Nowhere in the code, data is inserted into this temp table, which can latter be retrieved using the cursor.


Why do you want the data to be inserted into the temp table??? Cursor is usually declared which is then opened, fetched and closed.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Thu Sep 11, 2008 4:40 pm    Post subject:
Reply with quote

The Declaration statement is not for a temp table.
Back to top
View user's profile Send private message
revel

Active User


Joined: 05 Apr 2005
Posts: 135
Location: Bangalore/Chennai-INDIA

PostPosted: Thu Sep 11, 2008 5:01 pm    Post subject:
Reply with quote

Hello,
Quote:

A cursor is defined on the table that is declared above,from which data is fetched and written to output files.
Nowhere in the code, data is inserted into this temp table, which can latter be retrieved using the cursor.


This Concept were explained many times in forum...

Anyhow here is answer for you

The

Code:
EXEC SQL
DECLARE AAA_BBB_CCC TABLE
(
Field names with their data types are defined.......
)
END-EXEC.


will execute only when fallowing statement executed

ie

Code:
EXEC SQL
         OPEN <cursor name>
END-EXEC.


The above statement will execute the statement which was present in DECLRATION and the results will dumped to temperuary set(buffer) which is presnt in main memory.

Buffer


record 1
record 2
record 3
record 4
record 5

-------------------


To fetch information from above buffer, we will use

Code:
EXEC SQL
         FETCH <cursor>
END_EXEC



Hope you clear
Back to top
View user's profile Send private message
Aaru

Senior Member


Joined: 03 Jul 2007
Posts: 1288
Location: Chennai, India

PostPosted: Thu Sep 11, 2008 5:20 pm    Post subject: Reply to: How does Declare Table work?
Reply with quote

Revel,

Quote:
EXEC SQL
DECLARE AAA_BBB_CCC TABLE
(
Field names with their data types are defined.......
)
END-EXEC.


Are you saying that the above statements are for the declaration of a cusrsor?

I am confused here icon_sad.gif
Back to top
View user's profile Send private message
revel

Active User


Joined: 05 Apr 2005
Posts: 135
Location: Bangalore/Chennai-INDIA

PostPosted: Thu Sep 11, 2008 5:24 pm    Post subject:
Reply with quote

Aaru,

Thats what he said, He mistyped the code

Please check his above post


here is his post

Code:
EXEC SQL
DECLARE AAA_BBB_CCC TABLE
(
Field names with their data types are defined.......
)
END-EXEC.

A cursor is defined on the table that is declared above,


icon_wink.gif
Back to top
View user's profile Send private message
Sivagurunathan Jagadeesan

New User


Joined: 06 Mar 2007
Posts: 32
Location: India

PostPosted: Fri Sep 12, 2008 4:37 pm    Post subject: Reply to: How does Declare Table work?
Reply with quote

Thanks for the responses…

dbz, if this not a table(even a temp), then what else can it be, where a cursor is declared on it and data is fetched. Excuse me if the question is absurd.

The cursor is declared in the conventional way, similar to that of a normal DB2 table as following,
EXEC SQL
DECLARE CURSOR1 CURSOR FOR
SELECT
AAA_BBB_CCC.Field1
AAA_BBB_CCC.Field2
AAA_BBB_CCC.Field3
AAA_BBB_CCC.Field4
FROM
AAA_BBB_CCC AAA_BBB_CCC --> this is not a typo, but how it is actually coded ie. the name is coded twice.

If AAA_BBB_CCC is queried in SPUFI, I get a SQL -204(undefined name).

The declare statement on AAA_BBB_CCC which I had given in my first post is same as how it is coded, there is no typo in it, I didn’t really understand what Revel is talking about.

Aaru, I understand that a cursor is Opened/Fetched/Closed, but I am wondering where from the data is coming, since I am not able to retrieve records from AAA_BBB_CCC in SPUFI using a Select query.

Thanks,
Siva
Back to top
View user's profile Send private message
revel

Active User


Joined: 05 Apr 2005
Posts: 135
Location: Bangalore/Chennai-INDIA

PostPosted: Fri Sep 12, 2008 4:50 pm    Post subject:
Reply with quote

Hello Siva,

tell me first, What is your requirement. I hope this is not a home work question... :o)

What did you explained in previous post...then??

Quote:
Hi,

I have a COBOL/DB2 code, which has a declare statement as given below,

EXEC SQL
DECLARE AAA_BBB_CCC TABLE
(
Field names with their data types are defined.......
)
END-EXEC.

A cursor is defined on the table that is declared above,from which data is fetched and written to output files.
Nowhere in the code, data is inserted into this temp table, which can latter be retrieved using the cursor.

Please let me know how(usually) delcared tables are populated with data.


Please come up with correct requirement
Back to top
View user's profile Send private message
Bharath Bhat

Active User


Joined: 20 Mar 2008
Posts: 283
Location: chennai

PostPosted: Fri Sep 12, 2008 5:28 pm    Post subject:
Reply with quote

Hi,

The declare table statement you are referring to is very much ismiliar to a DCLGEN. Do you have equivalent cobol variables declared right after the END-EXEC? If your answer is Yes, Then it is a DCLGEN and it reprenents a physical table in your Cobol program. The cursor which you are referring to is declared on the real table.

IF the name is coded twice, then try the same name in SPUFI.
Back to top
View user's profile Send private message
Sivagurunathan Jagadeesan

New User


Joined: 06 Mar 2007
Posts: 32
Location: India

PostPosted: Mon Sep 15, 2008 2:06 pm    Post subject: Reply to: How does Declare Table work?
Reply with quote

Hi,

I am working on converting VSCOBOLII/OS390 modules to eCOBOL standard. We do a regression testing of the output files (files produced by executing test jobs against Prod and changed code).

When the output files are empty, we will manipulate the DB so that we get some records in the output. For one such module the files were empty and I couldn’t locate the table to manipulate the data and hence the question.

Bharath, as you said I have COBOL variables declared which is similar in structure to the declaration of AAA_BBB_CCC, but I am not able to query the table AAA_BBB_CCC(I get a SQL -204).

Thanks,
Siva
Back to top
View user's profile Send private message
Sivagurunathan Jagadeesan

New User


Joined: 06 Mar 2007
Posts: 32
Location: India

PostPosted: Mon Sep 15, 2008 2:30 pm    Post subject: Reply to: How does Declare Table work?
Reply with quote

Hi,

I got the table name by querying SYSIBM.SYSTABLES, I gave a wrong qualifier while querying the table, thats why I was getting a -204.

Thanks for the help.

Regards,
Siva
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 SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
No new posts Need Rexx Program to Load in to DB2 T... Hareesh Verma CLIST & REXX 22 Tue Sep 20, 2016 2:59 pm
No new posts Command UNIQUE CONSTRAINT on any tabl... rohanthengal DB2 2 Thu Aug 18, 2016 3:48 pm
No new posts Updating online Db2 table kishpra DB2 3 Fri Aug 12, 2016 2:44 pm
No new posts Getting soc4 on internal table in cobol abdulrafi COBOL Programming 13 Fri May 06, 2016 3:39 pm


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