Generate COBOL Copybook Output From DB2 Using JCL

Several of my last few posts have involved data manipulation, and for the most part I've been using Groovy. However, what I really like about Groovy is writing less code, and there are times when other tools, JCL in this case, can help me write even less code.

IBM provides two programs, IKJEFT01 and DSNTIAUL which allows you to pipe the results of a SQL statement into an output stream (SYSPRINT or a flat file). So, assuming that I had a simple COBOL copybook like this.

01 MY-NAME-FILE.
   03 FIRST  PIC X(10).
   03 MIDDLE PIC X(10).
   03 LAST   PIC X(10).

I could easily pipe the first, middle, and last name from a DB2 file into a flat file using an JCL that looked something like this (fill in your DBNAME and PLANNAME values).

//TESTJOB JOB PROD,'USER',CLASS=A,MSGCLASS=V,REGION=0M,
// NOTIFY=&SYSUID                                        
//COLUMNS  EXEC PGM=IKJEFT01                             
//SYSTSPRT DD   SYSOUT=*                                 
//SYSPRINT DD   SYSOUT=*                                 
//SYSUDUMP DD   SYSOUT=*                                 
//SYSREC00 DD   SYSOUT=T01.MYDATASET
//SYSPUNCH DD DUMMY                                      
//SYSTSIN  DD *                                          
DSN SYSTEM(DBNAME)                                         
RUN PROGRAM(DSNTIAUL) PLAN(PLANNAME) PARMS('SQL') -     
    LIB('DBNAME.RUNLIB.LOAD')                           
//SYSIN    DD *                                          
SELECT  LPAD(FIRST_NAME, 10, ' ')
     || LPAD(MIDDLE_NAME, 10, ' ') 

     || LPAD(LAST_NAME, 10, ' ')
FROM MY_NAME_TABLE; 
/*                                                      

Okay, its not the most intuitive, but it is a simple script that can be run as part of a job stream that creates a file compatible with the copybook that I provided above. The SQL concats the three columns together and pads the fields with spaces. The padding may be unnecessary depending on how the columns are defined, but just wanted to get across the point that the records that will be written to the file will be the 30 bytes that match the copybook.

But what happens with a slightly more complex copybook, specifically a copybook involving binary data.

01 MY-NAME-FILE.
   03 FIRST  PIC X(10).
   03 MIDDLE PIC X(10).
   03 LAST   PIC X(10).
   03 ZIP    PIC 9(9) COMP-3.

Yikes! Binary data, how can you format a column in a SELECT statement to produce binary data that could be written to a file. Well, there is the HEX command in DB2, so you could potentially create a binary string and figure out how to convert it, but the IBM programs make it much easier than that. Using the same JCL above, change the SQL to be like this:

//SYSIN    DD *                                          
SELECT FIRST_NAME,
       MIDDLE_NAME,
       LAST_NAME,

       ZIP_CODE,
FROM MY_NAME_TABLE; 
/*    

I'm making an assumption here that the ZIP_CODE field is defined in the database as INTEGER, or DECIMAL(9, 0), because if it is, the default formatting of the data of DSNTIAUL will cause the ZIP_CODE field to be written out as binary data that fits perfectly into PIC 9(9) COMP-3 variable. This is different than the default formatting used by tools on a windows platform, like say Data Studio, but because DSNTIAUL is written to run on a z/OS, it assumes that COBOL datatypes will be used.

If your data isn't defined as binary (COMP), then that actually become trickier, and you might have to do something like REPLACING(CHAR(ZIP_CODE), '.', '') to get rid of that decimal point. I didn't get into that very far, cause the dataset that I needed to produce was using COMP-3 fields for it's numeric data.