[USflag] The American Programmer [USflag]


Home
Books on Mainframe Programming
Mainframe Manuals and Tutorials
System Abend codes, Sqlcodes, VSAM/QSAM codes
Everything about the IBM AS/400 Midrange Computer
Everything about CICS
Everything about COBOL
Everything about DB2 and SQL
  DB2/SQL Singleton Select embedded in a COBOL program
  DB2/SQL Add Change Delete program
  DB2/SQL program to read table with cursor
  DB2/SQL load table program
  DB2/SQL program to read a table randomly
  DB2/SQL bare bones skeleton program
  DB2/SQL program to insert a row into a table
  DB2/SQL program to update a row
  DB2/SQL program for handling variable length column
  DB2/SQL program for handling column with null indicator
  Embedded SQL. Short tutorial.
  Examples of DCLGEN. Short tutorial.
  Examples of SQL Joins. Short tutorial.
  Examples of DB2 View. Short tutorial.
  Just Enough SQL/QMF/SPUFI To Be Dangerous
  Sample tables STAFF, ORG, EMP and DEPT
  QMF Procedures Written in REXX
  Using REXX Subroutines with the QMF Calc panel
  Using QMF to Execute Your SQL
  QMF Cheat Sheet. Edit codes. QMF Commands, Text Variables, Usage Codes
  Executing SQL in a batch job
  Using Spufi to Execute Your SQL
  Books on DB2 and SQL
  DB2 and SQL, Structured Query Language manuals
  SQLCODES and Their Causes
Everything about IMS
Everything about Java and JavaScript
Everything about JCL and JES
Everything about REXX
Everything about zOS, VSAM, Tivoli, Assembler
Everything about TSO, ISPF, Spufi
Site Map and Site Search

           Home   > DB2   > Examples of DB2/SQL DCLGENS

Examples of DCLGENS - Generated Declarations

Description of the example tables STAFF, ORG, EMP and DEPT

DCLGEN is SQL and COBOL code generated by DB2 
	will be INCLUDED into your program by the precompiler
	created on a panel within DB2I within TSO/ISPF, or by JCL.

You do the DCLGEN yourself. In some companies it may be done by a DBA.
	Go into DB2I within ISPF and specify:
		the name of table, its owner/high level qualifier,
		the name of your COBOL library 
			or a library that your company has set aside to hold DCLGENs.
		the name of the declaration member in the library
			(generally the same as the table, abbreviated if necessary.)
   DCLGEN                         SSID: db2-subsystem     
         ==>
          Enter table name for which declarations are required:
           1  SOURCE TABLE NAME ===> staff < the name of your table
           2  TABLE OWNER ..... ===> 	 < the high level qualifier
		    	   	         < of the table, if any
           3  AT LOCATION ..... ===>          
               Enter destination data set:        
           4  DATA SET NAME ... ===> ‘userid.DB2.COBOL(STAFF)’
						the name of your COBOL library 
						with the member name of your 
                                                declaration.

           Enter options as desired:
           6  ACTION .......... ===> REPLACE 
           7  COLUMN LABEL .... ===> NO       
           8  STRUCTURE NAME .. ===>          
           9  FIELD NAME PREFIX ===>          
				PRESS ENTER


 


What the declarations look like.

The generated declaration contains two parts: table declaration used by the DB2 precompiler to check whether you are using the table columns properly COBOL data items you can use to reference the table columns contains correct COBOL pictures don't invent COBOL pictures, use these, they are right occasionally, the name of a table column is a COBOL reserved word and you have to change the name of the COBOL variable The DCLGEN can be used as an IO-area for reading and writing

STAFF

Note that I changed the COBOL variables ID and NAME to ID-x and NAME-x because ID and NAME are not legal in COBOL. Blame the designer of the table at IBM for not knowing COBOL. Use ID and NAME when referring to table columns Use ID-x and NAME-x when referring to COBOL variables Most companies require you to unqualify the table name EXEC SQL DECLARE STAFF TABLE ( ID SMALLINT NOT NULL, NAME VARCHAR(9), DEPT SMALLINT, JOB CHAR(5), YEARS SMALLINT, SALARY DECIMAL(7, 2), COMM DECIMAL(7, 2) ) END-EXEC. * COBOL DECLARATION FOR TABLE STAFF 01 DCLSTAFF. 10 ID-x PIC S9(4) USAGE COMP. 10 NAME-x. 49 NAME?LEN PIC S9(4) USAGE COMP. 49 NAME?TEXT PIC X(9). 10 DEPT PIC S9(4) USAGE COMP. 10 JOB PIC X(5). 10 YEARS PIC S9(4) USAGE COMP. 10 SALARY PIC S9(5)V9(2) USAGE COMP-3. 10 COMM PIC S9(5)V9(2) USAGE COMP-3.

APPLICANT

Note that I changed the COBOL variables NAME and ADDRESS to NAME-x and ADDRESS-x because NAME and ADDRESS are not legal in COBOL. EXEC SQL DECLARE APPLICANT TABLE ( TEMPID SMALLINT NOT NULL, NAME VARCHAR(9), ADDRESS VARCHAR(17), EDLEVEL SMALLINT, COMMENTS VARCHAR(29) ) END-EXEC. * COBOL DECLARATION FOR TABLE APPLICANT * 01 DCLAPPLICANT. 10 TEMPID PIC S9(4) USAGE COMP. 10 NAME-x. 49 NAME-LEN PIC S9(4) USAGE COMP. 49 NAME-TEXT PIC X(9). 10 ADDRESS-x. 49 ADDRESS-LEN PIC S9(4) USAGE COMP. 49 ADDRESS-TEXT PIC X(17). 10 EDLEVEL PIC S9(4) USAGE COMP. 10 COMMENTS. 49 COMMENTS-LEN PIC S9(4) USAGE COMP. 49 COMMENTS-TEXT PIC X(29).

ORG

Note that I changed the COBOL variable DIVISION to DIVISION-x because DIVISION is not legal in COBOL. EXEC SQL DECLARE ORG TABLE ( DEPTNUMB SMALLINT NOT NULL, DEPTNAME VARCHAR(14), MANAGER SMALLINT, DIVISION VARCHAR(10), LOCATION VARCHAR(13) ) END-EXEC. * COBOL DECLARATION FOR TABLE ORG * 01 DCLORG. 10 DEPTNUMB PIC S9(4) USAGE COMP. 10 DEPTNAME. 49 DEPTNAME-LEN PIC S9(4) USAGE COMP. 49 DEPTNAME-TEXT PIC X(14). 10 MANAGER PIC S9(4) USAGE COMP. 10 DIVISION-x. 49 DIVISION-LEN PIC S9(4) USAGE COMP. 49 DIVISION-TEXT PIC X(10). 10 LOCATION. 49 LOCATION-LEN PIC S9(4) USAGE COMP. 49 LOCATION-TEXT PIC X(13).

EMP

EXEC SQL DECLARE EMP TABLE ( EMPNO CHAR(6) NOT NULL, FIRSTNME VARCHAR(12) NOT NULL, MIDINIT CHAR(1) NOT NULL, LASTNAME VARCHAR(15) NOT NULL, WORKDEPT CHAR(3), PHONENO CHAR(4), HIREDATE DATE, JOB CHAR(8), EDLEVEL SMALLINT, SEX CHAR(1), BIRTHDATE DATE, SALARY DECIMAL(9, 2), BONUS DECIMAL(9, 2), COMM DECIMAL(9, 2) ) END-EXEC. 01 DCLEMP. 10 EMPNO PIC X(6). 10 FIRSTNME. 49 FIRSTNME-LEN PIC S9(4) USAGE COMP. 49 FIRSTNME-TEXT PIC X(12). 10 MIDINIT PIC X(1). 10 LASTNAME. 49 LASTNAME-LEN PIC S9(4) USAGE COMP. 49 LASTNAME-TEXT PIC X(15). 10 WORKDEPT PIC X(3). 10 PHONENO PIC X(4). 10 HIREDATE PIC X(10). 10 JOB PIC X(8). 10 EDLEVEL PIC S9(4) USAGE COMP. 10 SEX PIC X(1). 10 BIRTHDATE PIC X(10). 10 SALARY PIC S9(7)V9(2) USAGE COMP-3. 10 BONUS PIC S9(7)V9(2) USAGE COMP-3. 10 COMM PIC S9(7)V9(2) USAGE COMP-3.

DEPT

EXEC SQL DECLARE DEPT TABLE ( DEPTNO CHAR(3) NOT NULL, DEPTNAME VARCHAR(36) NOT NULL, MGRNO CHAR(6), ADMRDEPT CHAR(3) NOT NULL ) END-EXEC. * COBOL DECLARATION FOR TABLE DEPT * 01 DCLDEPT. 10 DEPTNO PIC X(3). 10 DEPTNAME. 49 DEPTNAME-LEN PIC S9(4) USAGE COMP. 49 DEPTNAME-TEXT PIC X(36). 10 MGRNO PIC X(6). 10 ADMRDEPT PIC X(3).

Top of Page




















































































List of books on DB2 and other mainframe topics

[Books Computer]

Home Books for Computer Professionals Privacy Terms |
Site Map and Site Search Programming Manuals and Tutorials The REXX Files Top of Page |