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
 

 

Query on DCLGEN

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

New User


Joined: 08 Jun 2005
Posts: 27

PostPosted: Tue Feb 21, 2006 1:39 pm    Post subject: Query on DCLGEN
Reply with quote

Hi all,

I have follwing queries for ur clarifications.

1)I read one db2 manual about the host varialble declaration in DCLGEN
i.e the host variable is declared in beween
EXEC SQL BEGIN DECLARE SECTION
01 EMP
10 EMP-NAME PIC X(14).
10 EMP-SALL PIC S9(5)V USAGE COMP-3.
EXEC SQL END DECLARE SECTION

Is it correct one?

But in the real time mainframe environment it's not looking like that.
Instad,
EXEC SQL DECLARE EMP TABLE
(EMP-NAME char(14) not null,
EMP-SALL decimal (5,00 not null
)END-EXEC.
* COBOL DECLARATION FOR TABLE EMP
01 EMP
10 EMP-NAME PIC X(14).
10 EMP-SALL PIC S9(5)V USAGE COMP-3.

The questinon is
which is host variable in this?
which is cobol equivalent variable for coloumns in the table?
diff between the host variable and cobol equivalent variable?
Back to top
View user's profile Send private message

sonasheetal

New User


Joined: 08 Jun 2005
Posts: 27

PostPosted: Tue Feb 21, 2006 2:02 pm    Post subject: Query on DCLGEN
Reply with quote

iN ADDITION THIS Questions above asked by me

one more ,

are the host variable and cobol declaration copy book in DCLGEN same?
Back to top
View user's profile Send private message
small_world

New User


Joined: 22 Jul 2005
Posts: 24
Location: pune

PostPosted: Tue Feb 21, 2006 3:26 pm    Post subject:
Reply with quote

Host variables are data items defined within a COBOL program. They are used to pass values to and receive values from a database. Host variables can be defined in the File Section, Working-Storage Section, Local-Storage Section or Linkage Section of your COBOL program and have any level number between 1 and 48. Level 49 is reserved for VARCHAR data items.

When a host variable name is used within an embedded SQL statement, the data item name must begin with a colon (icon_smile.gif to enable the Compiler to distinguish between host variables and tables or columns with the same name.

Host variables are used in one of two ways:

Input host variables
These are used to specify data that will be transferred from the COBOL program to the database.

Output host variables
These are used to hold data that is returned to the COBOL program from the database.

For example, in the following statement, :book-id is an input host variable that contains the ID of the book to search for, while :book-title is an output host variable that returns the result of the search:

EXEC SQL
SELECT title INTO :book-title FROM titles
WHERE title_id=:book-id
END-EXEC
Back to top
View user's profile Send private message
small_world

New User


Joined: 22 Jul 2005
Posts: 24
Location: pune

PostPosted: Tue Feb 21, 2006 3:34 pm    Post subject:
Reply with quote

Declaring Host Variables
Before you can use a host variable in an embedded SQL statement, you must declare it. Host variable declarations should be bracketed by the embedded SQL statements BEGIN DECLARE SECTION and END DECLARE SECTION, for example:

EXEC SQL
BEGIN DECLARE SECTION
END-EXEC
01 id pic x(4).
01 name pic x(30).
EXEC SQL
END DECLARE SECTION
END-EXEC

display "Type your identification number: "
accept id.

* The following statement retrieves the name of the
* employee whose ID is the same as the contents of
* the host variable "id". The name is returned in
* the host variable "name".

EXEC SQL
SELECT emp_name INTO :name FROM employees
WHERE emp_id=:id
END-EXEC
display "Hello " name.

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

New User


Joined: 09 Aug 2005
Posts: 67

PostPosted: Tue Feb 21, 2006 6:28 pm    Post subject:
Reply with quote

Quote:
The questinon is
1. which is host variable in this?
2. which is cobol equivalent variable for coloumns in the table?
3. diff between the host variable and cobol equivalent variable?


EMP-NAME is a host variable. Host variable in COBOL perspective is any cobol variable used in an SQL. So 1 and 2 are same. there is no difference.

Quote:
are the host variable and cobol declaration copy book in DCLGEN same?


Yes.
Back to top
View user's profile Send private message
sonasheetal

New User


Joined: 08 Jun 2005
Posts: 27

PostPosted: Wed Feb 22, 2006 12:42 pm    Post subject: Will Host variable not be the same ?
Reply with quote

Hi guys,

Thank u for ur quick reply.

as per my knowledge concerned the host variable name and the column name may or may not be same.

Since the host variable and cobol declaration copy book in DCLGEN are same , If the host variable name is different from the column name in the table
column name Emp-Name
host variable HEmp_Name
1)how can we correlate the the host variable name with column name?
2)we won't have the cobol declaration for the actual column name.WILL IT BE A PROBLEM?
3)How cobol compiler recognize these host variable(HEmp_Name) for that table?

I have not seen the follwing part in the coding for host variable decalration using DCLGEN utility. Will it be used as follows when coding manually?
EXEC SQL
BEGIN DECLARE SECTION
END-EXEC
01 id pic x(4).
01 name pic x(30).
EXEC SQL
END DECLARE SECTION
END-EXEC


Regards,
Sona.
Back to top
View user's profile Send private message
Pradeepshan

New User


Joined: 20 Jan 2006
Posts: 6
Location: bangalore

PostPosted: Wed Feb 22, 2006 8:31 pm    Post subject:
Reply with quote

DISPLAY REQUIRED RECORD
000001 IDENTIFICATION DIVISION.
000002 PROGRAM-ID. EMPLO.
000003 ENVIRONMENT DIVISION.
000004 DATA DIVISION.
000005 WORKING-STORAGE SECTION.
000006 EXEC SQL
000007 INCLUDE EMP ==>dclgen
000008 END-EXEC.
000009 EXEC SQL
000010 INCLUDE SQLCA
000011 END-EXEC.
000012 PROCEDURE DIVISION.
000013 EXEC SQL
000014 SELECT EMPNO INTO:EMPNO
000015 FROM EMPLOYEES WHERE PHONENO = 22345620
000016 END-EXEC.
000017 DISPLAY EMPNO.
000018 STOP RUN.
-----------------------------------------------------------------------------------------------------------------------------------------------
INPUT
EMPNO FIRSTNAME MIDINIT LASTNAME WORKDEPT PHONENO HIREDATE
---------+---------+---------+---------+---------+---------+---------+---------+
88 SUSMI T TH MCOM 22345620 2004-03-25
15 TULSI O P EEE 25675620 2003-06-15
20 ARYA B NA MTEC 26783420 2003-05-11
22 SANKU V NA EEC 22244760 2003-02-06
45 KALPAN S NA MBBS 275655620 2004-03-28
90 SUS T TH -------- 22345620 2004-03-25
DSNE610I NUMBER OF ROWS DISPLAYED IS 6
-----------------------------------------------------------------------------------------------------------------------------------------------
OUTPUT
90
-----------------------------------------------------------------------------------------------
Back to top
View user's profile Send private message
bonniem

New User


Joined: 09 Aug 2005
Posts: 67

PostPosted: Thu Feb 23, 2006 10:40 am    Post subject:
Reply with quote

Quote:
Since the host variable and cobol declaration copy book in DCLGEN are same , If the host variable name is different from the column name in the table
column name Emp-Name
host variable HEmp_Name
1)how can we correlate the the host variable name with column name?
2)we won't have the cobol declaration for the actual column name.WILL IT BE A PROBLEM?
3)How cobol compiler recognize these host variable(HEmp_Name) for that table?


1. The variable names are in order, first field in the table corresponds to first field in the cobol declaration and so on. And what you have written is wrong, '_' cannot come in a host variable, COBOL will throw errors.

for example, if the field name is emp_name, host variable name would come like emp-name. You can even add a prefix to this if you specify the option when creating the dclgen. for eg. like ws-emp-nm.

3) There is no hard and fast rule that you have to use the host variable generated in the dclgen. They are just corresponding COBOL declarations of the db2 data types. you are free to change the names as long as you keep the PIC of the variables intact.

Hope you are clear now.
Back to top
View user's profile Send private message
sonasheetal

New User


Joined: 08 Jun 2005
Posts: 27

PostPosted: Thu Feb 23, 2006 11:58 am    Post subject: Re: Query on DCLGEN
Reply with quote

Hi bonniem and small_world,

Thanks a lot for ur deatailed answer. if u don't mind can u clarify the following thing,

I have not seen the following structure of declaration part in the coding for host variable decalration using DCLGEN utility. Will it be used as follows when coding manually?
EXEC SQL
BEGIN DECLARE SECTION
END-EXEC
01 EMP
10 EMP-NAME PIC X(14).
10 EMP-SALL PIC S9(5)V USAGE COMP-3.
EXEC SQL
END DECLARE SECTION
END-EXEC

instad, in the real time mainframe environment it's looking like that.
EXEC SQL DECLARE EMP TABLE
(EMP-NAME char(14) not null,
EMP-SALL decimal (5,00 not null
)END-EXEC.
* COBOL DECLARATION FOR TABLE EMP
01 EMP
10 EMP-NAME PIC X(14).
10 EMP-SALL PIC S9(5)V USAGE COMP-3.


Regards,
Sona.
Back to top
View user's profile Send private message
bonniem

New User


Joined: 09 Aug 2005
Posts: 67

PostPosted: Thu Feb 23, 2006 12:04 pm    Post subject:
Reply with quote

I dont think it will be used like that. I have seen begin declare only in procedural SQL, like PL/SQL.

Even when you code manually, you can simply find out what is the corresponding cobol declaration for the DB2 data type and declare a variable using that PIC clause in working storage.

Say for example if it is an integer data type, i guess the corresponding declaration would be

01 ws-integer pic s9(09) comp-3.

you can use any name, and you can use that variable in SQLs now.
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 JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Omegamon for DB2 query ashek15 DB2 0 Wed Feb 15, 2017 11:25 am
No new posts Query on DB2 WLM stored procedures ashek15 DB2 8 Sun Feb 12, 2017 11:13 pm
No new posts GETMAIN/FREEMAIN query Suja.Sai CICS 9 Tue Jan 31, 2017 12:01 pm


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