Books on DB2 and SQL DB2 and SQL Manuals Examples of DCLGENS - Generated Declarations 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 in the class. In real life 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 (see Section 18 on how to create one. ) 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 JCL and other mainframe topics