Query on DCLGEN

sonasheetal · New User Joined: 08 Jun 2005 Posts: 27

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?

sonasheetal · New User Joined: 08 Jun 2005 Posts: 27

iN ADDITION THIS Questions above asked by me

one more ,

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

small_world · New User Joined: 22 Jul 2005 Posts: 24 Location: pune

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 (

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

small_world · New User Joined: 22 Jul 2005 Posts: 24 Location: pune

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.

bonniem · New User Joined: 09 Aug 2005 Posts: 67

sonasheetal · New User Joined: 08 Jun 2005 Posts: 27

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.

Pradeepshan · New User Joined: 20 Jan 2006 Posts: 6 Location: bangalore

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

bonniem · New User Joined: 09 Aug 2005 Posts: 67

sonasheetal · New User Joined: 08 Jun 2005 Posts: 27

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.

bonniem · New User Joined: 09 Aug 2005 Posts: 67

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.