Library: White Papers

In this document, I give some tips for writing INMOD routines for Teradata FastLoad in Cobol. I assume that you are using a recent LE/370 compiler from IBM, such as IBM COBOL for OS/390 and VM 2.1 (5648-A25). First I give tips for a plain Cobol routine, then for a routine that uses SQL to read rows from DB2/MVS.

Plain Cobol without SQL

The name in the PROGRAM-ID paragraph of your Cobol routine must be BLKEXIT; this is required by FastLoad.

Before compiling the routine, you must assemble and link-edit a tiny module that turns on the RTEREUS run-time option. The JCL to do this is in Exhibit B. For more information, see NCR SupportLink article SA1000772A2.

Compiling the routine is straightforward; you should be able to use your standard compile protocol. The routine must be compiled with the DYNAM option, which in most shops is the default.

See Exhibit C, step LKED, for a sample of how to link-edit the Cobol routine. Be sure to specify the following input:

INCLUDE SYSLIB(CEEUOPT) [This is the tiny RTEREUS module, which comes from your own library]

NAME anyname(R) [Specify your desired name, not BLKEXIT]

Do not include DSNALI; this is only for programs that use DB2.

When running your FastLoad:

- In your STEPLIB, you will need the proper TDxx libraries for Teradata and the library that contains your INMOD load module.

- As you would expect, the BEGIN LOADING statement will contain the INMOD=program parameter rather than the DDNAME=dd parameter.

Cobol with Embedded SQL

The following describes a prototype INMOD routine that loads rows directly from a DB2 table into Teradata. This poses some additional challenges.

1. Write the Cobol program

2. Precompile, compile, link-edit, and bind

3. Run the FastLoad

4. What about MultiLoad?

1. Write the Cobol program

My prototype Cobol program is in Exhibit A. You will, of course, want to make changes appropriate for your application: for instance, you should bring in the DCLGEN from a separate copylib member, and of course the source table and target record will be different.

- As mentioned above, the name in the PROGRAM-ID paragraph must be BLKEXIT.

- Keep the SELECT statement as simple as possible. In particular, do not include ORDER BY.

2. Precompile, compile, link-edit, and bind

Prototype JCL to prepare the program is in Exhibit C.

As mentioned above, you must assemble and link-edit a tiny module that turns on the RTEREUS run-time option. The JCL to do this is in Exhibit B.

- Precompile: Your INMOD must use Call Attach; i.e., it must be precompiled with ATTACH(CAF), not ATTACH(TSO). The ISPF panel for the precompiler seems to insist on using ATTACH(TSO), so I recommend that you precompile in a batch job. HOST(IBMCOB) should also be specified, although HOST(COB2) seems to work just fine.

- Compile: Straightforward. As mentioned above, the module must be compiled with the DYNAM option.

- Link-edit: When link-editing the Cobol module, be sure to specify the following input:

INCLUDE SYSLIB(DSNALI) [This is the Call Attach module; it comes from DBxx.DSNLOAD]

INCLUDE SYSLIB(CEEUOPT) [This is the tiny RTEREUS module, which comes from your own library]

NAME anyname(R) [Specify your desired name, not BLKEXIT]

- Bind: Straightforward. You should be able to use your standard conventions here.

3. Run the FastLoad

- Your FastLoad must run in the jobclass associated with DB2.

- In your STEPLIB, you will need the proper DBxx libraries for DB2, the proper TDxx libraries for Teradata, and the library that contains your INMOD load module.

- As mentioned above, the BEGIN LOADING statement will contain the INMOD=program parameter rather than the DDNAME=dd parameter.

4. What about MultiLoad?

Once you have written your INMOD routine, it may work with either FastLoad or MultiLoad. I have not confirmed this, however, and I have tested only with FastLoad. If you want to use MultiLoad, you are on your own.

Acknowledgment:

Thanks to NCR's SupportLink Web site, which clued me in to the RTEREUS routine.

Exhibit A: COBOL program

000100 Identification Division.
000200 Program-id. BLKEXIT.
000300*---------------------------------------------------------------
000400*  AUTHOR       : W. Geoffrey Rommel
000500*  DATE WRITTEN : 2001-11-26
000600*  CALLED FROM  : FastLoad
000700*  CALLS        : none
000800*
000900*  FUNCTION     : This is an INMOD routine for Teradata FastLoad.
001000*                 It reads a SYSIBM table and passes every
001100*    row from the table to FastLoad.
001200*  *** PROTOTYPE FOR CMS TABLES -- TESTING ONLY! ***
001300*    This INMOD does not support restarting after checkpoints.
001400*
001500*  NOTE ABOUT THE NAME: FastLoad requires the INMOD's entry
001600*  point name to be BLKEXIT, but we have more than one INMOD in
001700*  our libraries.  The names we really want are assigned to the
001800*  load module members when they are link-edited; those names
001900*  are passed to FastLoad in the DEFINE statement.
002000*
002100*  PARAMETERS   : 1.  Structure passed by FastLoad.
002200*  RETURN CODES : As defined by Teradata:
002300*                 0 = We are returning a record to FastLoad
002400*                 4 = End of file
002500*
002600*  2000-11-26  00  G. Rommel -- Initial release
002700*---------------------------------------------------------------
002800     skip3
002900 Environment Division.
003000 Configuration Section.
003100 Source-computer. IBM-370.
003200 Object-computer. IBM-370.
003300
003400 Input-Output Section.
003500 File-Control.
003600
003700 Data Division.
003800 File Section.
003900 Working-Storage Section.
004000 77  Cow-catcher     pic X(40)
004100         value 'CMST01   WORKING-STORAGE BEGINS HERE'.
004200
004300******************************************************************
004400* DCLGEN TABLE(SYSIBM.SYSTABLES)                                 *
004500*        LIBRARY(GROMMEL.LCI.COBOL(SYSTABL))                     *
004600*        LANGUAGE(COBOL)                                         *
004700*        QUOTE                                                   *
004800* ... IS THE DCLGEN COMMAND THAT MADE THE FOLLOWING STATEMENTS   *
004900******************************************************************
005000     EXEC SQL DECLARE SYSIBM.SYSTABLES TABLE
005100     ( NAME                           VARCHAR(18) NOT NULL,
005200       CREATOR                        CHAR(8) NOT NULL,
[etc.]
009400     ) END-EXEC.
009500******************************************************************
009600* COBOL DECLARATION FOR TABLE SYSIBM.SYSTABLES                   *
009700******************************************************************
009800 01  DCLSYSTABLES.
009900     10 NAME.
010000        49 NAME-LEN          PIC S9(4) USAGE COMP.
010100        49 NAME-TEXT         PIC X(18).
010200     10 CREATOR              PIC X(8).
[etc.]
015200******************************************************************
015300* THE NUMBER OF COLUMNS DESCRIBED BY THIS DECLARATION IS 43      *
015400******************************************************************
015500     EXEC SQL
015600      INCLUDE SQLCA
015700     END-EXEC.
015800
015900 01  Miscellaneous.
016000     03  Length-returned         pic S9(9)   comp  value +38.
016100     03  DB2-rows-read           pic S9(11)  comp-3  value zero.
016200     03  User-abend-code         pic S9(9)   comp  value +12.
016300     03  Cleanup-flag            pic S9(9)   comp  value +1.
016400     03  DB2-rows-edited         pic ZZZZZZZZZZ9.
016500
016600 01  Record-to-return.
016700     03 R-NAME                 PIC X(18).
016800     03 R-CREATOR              PIC X(8).
016900     03 R-DBNAME               PIC X(8).
017000     03 R-CARD                 PIC S9(9) USAGE COMP.
017100
017200 01  ERROR-MESSAGE.
017300     05  ERROR-LEN         PIC S9(004) COMP  VALUE +720.
017400     05  ERROR-TEXT        PIC  X(072) OCCURS 10 TIMES
017500                                 INDEXED BY ERROR-INDEX.
017600 77  ERROR-TEXT-LEN        PIC S9(009) COMP  VALUE +72.
017700
017800 01  Time-stamp.
017900     03  TS-date                 pic 9(8).
018000     03  TS-time.
018100         05  TS-time-hh          pic 99.
018200         05  TS-time-mm          pic 99.
018300         05  TS-time-ss          pic 99.
018400         05  TS-time-csec        pic 99.
018500     03  TS-time-seconds         pic 9(6).
018600     03  TS-disp.
018700         05  TS-disp-date        pic 9999/99/99B.
018800         05  TS-disp-time.
018900             07  TS-disp-hh      pic 99.
019000             07  filler          pic X  value ':'.
019100             07  TS-disp-mm      pic 99.
019200             07  filler          pic X  value ':'.
019300             07  TS-disp-ss      pic 99.
019400             07  filler          pic X  value space.
019500
019600 Linkage Section.
019700 01  Inmod-record.
019800     03  FL-status-code          pic s9(9) comp.
019900     03  Record-length           pic s9(9) comp.
020000     03  Record-body             pic x(32004).
020100     eject
020200 Procedure Division using Inmod-record.
020300
020400     EXEC SQL
020500      WHENEVER SQLERROR    GO TO Y040-SQL-ERROR
020600     END-EXEC.
020700     EXEC SQL
020800      WHENEVER NOT FOUND   GO TO B020-END-OF-TABLE
020900     END-EXEC.
021000
021100 A000-Main.
021200     EXEC SQL
021300       DECLARE  SYSTAB_CUR   CURSOR
021400       FOR
021500       SELECT NAME, CREATOR, DBNAME, CARD
021600         FROM SYSIBM.SYSTABLES
021700     END-EXEC.
021800*---- 0 = first time.  Initialize and return a record.
021900     if FL-status-code = 0 then
022000        perform Y010-Get-Timestamp
022100        display TS-disp 'INMOD beginning'
022200        EXEC SQL
022300         OPEN SYSTAB_CUR
022400        END-EXEC
022500        perform B010-Build-Record
022600        move zero         to FL-status-code
022700        goback
022800*---- 1 = return a record.
022900     else if FL-status-code = 1 then
023000        perform B010-Build-Record
023100        move zero         to FL-status-code
023200        goback
023300*---- 3 = a checkpoint was taken.  We do nothing.
023400     else if FL-status-code = 3 then
023500        move zero         to FL-status-code
023600        goback
023700*---- anything else: probably a restart.  Abend.
023800     else
023900        perform Y010-Get-Timestamp
024000        display TS-disp 'CMST01 does not support restart after che
024100-          'ckpoint -- abending'
024200        move +12          to FL-status-code
024300        call 'CEE3ABD'  using User-abend-code, Cleanup-flag
024400        goback.
024500
024600*--- Get the next row from DB2 and build the next record to
024700*--- return to FastLoad.
024800 B010-Build-Record.
024900     EXEC SQL
025000       FETCH SYSTAB_CUR
025100         INTO :NAME, :CREATOR, :DBNAME, :CARD
025200     END-EXEC.
025300     add 1                   to DB2-rows-read.
025400     move spaces             to R-NAME.
025500     move NAME               to R-NAME.
025600     move CREATOR            to R-CREATOR.
025700     move DBNAME             to R-DBNAME.
025800     move CARD               to R-CARD.
025900     move Record-to-return   to Record-body.
026000     move Length-returned    to Record-length.
026100     skip3
026200*--- End of the result set.  Clean up and exit.
026300 B020-End-of-Table.
026400     EXEC SQL
026500       CLOSE SYSTAB_CUR
026600     END-EXEC.
026700
026800     move DB2-rows-read   to DB2-rows-edited.
026900     perform Y010-Get-Timestamp.
027000     display TS-disp 'INMOD ending normally; ' DB2-rows-edited
027100         ' rows read from DB2'.
027200     move +4           to FL-status-code.
027300     move zero         to Record-length.
027400     goback.
027500     skip3
027600 Y010-Get-Timestamp.
027700     Accept TS-date    from DATE YYYYMMDD.
027800     Accept TS-time    from TIME.
027900     Move TS-date            to TS-disp-date.
028000     Move TS-time-hh         to TS-disp-hh.
028100     Move TS-time-mm         to TS-disp-mm.
028200     Move TS-time-ss         to TS-disp-ss.
028300
028400 Y040-SQL-ERROR.
028500     CALL 'DSNTIAR' USING SQLCA ERROR-MESSAGE ERROR-TEXT-LEN
028600     PERFORM VARYING ERROR-INDEX FROM +1 BY +1
028700             UNTIL   ERROR-INDEX > 10
028800                OR   ERROR-TEXT(ERROR-INDEX) = SPACES
028900         DISPLAY ERROR-TEXT(ERROR-INDEX)
029000     END-PERFORM.
029100*--- Terminate the FastLoad.
029200     move +8           to FL-status-code.
029300     move zero         to Record-length.
029400     goback.

Exhibit B: RTEREUS module

//...job card...
//*
//*-----------------------------------------------------------------
//* *** TERADATA ***
//* FOR USE WITH INMOD ROUTINES WRITTEN IN LE/370 COBOL.
//* ASSEMBLE A 'CEEUOPT' MODULE WITH THE RTEREUS=ON OPTION TO WRAP
//* AROUND THE COBOL ROUTINE.  THIS ALLOWS US TO WRITE THE INMOD IN
//* COBOL RATHER THAN ASSEMBLER.
//*-----------------------------------------------------------------
//*
//*------- ASSEMBLE
//*
//ASM      EXEC PGM=ASMA90
//SYSLIB   DD DSN=SYS1.SCEEMAC,DISP=SHR
//         DD DSN=SYS1.MACLIB,DISP=SHR
//SYSUT1   DD  DSN=&&SYSUT1,SPACE=(4096,(120,120),,,ROUND),
//             UNIT=SYSALLDA,DCB=BUFNO=1
//SYSPRINT DD  SYSOUT=*
//SYSLIN   DD  DSN=&&OBJ,SPACE=(3040,(40,40),,,ROUND),
//             UNIT=SYSALLDA,DISP=(MOD,PASS),
//             DCB=(BLKSIZE=3040,LRECL=80,RECFM=FB,BUFNO=1)
//SYSIN    DD *
CEEUOPT  CSECT
CEEUOPT  AMODE ANY
CEEUOPT  RMODE ANY
         CEEXOPT RTEREUS=(ON)
         END
//*
//*------- BIND
//*
//BND      EXEC PGM=HEWL,PARM='MAP,LET,LIST,NCAL',COND=(8,LT,ASM)
//SYSLIN   DD  DSN=&&OBJ,DISP=(OLD,DELETE)
//         DD  DDNAME=SYSIN
//SYSLMOD  DD  DISP=SHR,DSN=GROMMEL.LCI.LOAD
//SYSUT1   DD  DSN=&&SYSUT1,SPACE=(1024,(120,120),,,ROUND),
//             UNIT=SYSALLDA,DCB=BUFNO=1
//SYSPRINT DD  SYSOUT=*
//SYSIN    DD *
  ENTRY   CEEUOPT
  NAME    CEEUOPT(R)
//*

Exhibit C: Precompile, compile, etc.

//...job card...
//*
//*------- PREPROCESS, COMPILE, LINK-EDIT, AND BIND PLAN
//*
//COMPDB2  PROC PROGRAM=CMST01,
//         HLVL='GROMMEL.LCI',
//         DB2SYS=DB91
//*
//DB2XLAT EXEC PGM=DSNHPC,REGION=2M,
//  PARM=(APOST,SOURCE,'HOST(IBMCOB),VERSION(AUTO),FLAG(E)',
//    'ATTACH(CAF)')
//STEPLIB  DD DISP=SHR,DSN=&DB2SYS..DSNEXIT
//         DD DISP=SHR,DSN=&DB2SYS..DSNLOAD
//DBRMLIB  DD DISP=SHR,DSN=&HLVL..DBRMLIB(&PROGRAM)
//SYSTERM  DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUT1   DD SPACE=(800,(500,500)),UNIT=SYSDA
//SYSUT2   DD SPACE=(800,(500,500)),UNIT=SYSDA
//SYSIN    DD DISP=SHR,DSN=&HLVL..COBOL(&PROGRAM)
//SYSCIN   DD DSN=&&DB2OUT,DISP=(NEW,PASS),
//            DCB=(RECFM=FB,LRECL=80,BLKSIZE=6160),
//            UNIT=SYSDA,SPACE=(6160,(30,30),RLSE)
//*
//COB2    EXEC PGM=IGYCRCTL,PARM=('BUF(31K)',RENT,'FLAG(W)',
//             OBJECT,APOST,OPT,MAP,OFF,XREF,NOSEQ,DYNAM,
//             OFFSET,SOURCE,'TRUNC(BIN)')
//STEPLIB  DD DISP=SHR,DSN=SYS1.SCEERUN
//         DD DISP=SHR,DSN=SYS1.SIGYCOMP
//SYSLIB   DD DISP=SHR,DSN=SYS1.MACLIB
//SYSPRINT DD SYSOUT=*
//SYSLIN   DD DSNAME=&&LOADSET,UNIT=SYSDA,DISP=(MOD,PASS),
//            SPACE=(TRK,(5,5))
//SYSUT1   DD UNIT=SYSDA,SPACE=(CYL,(1,1))
//SYSUT2   DD UNIT=SYSDA,SPACE=(CYL,(1,1))
//SYSUT3   DD UNIT=SYSDA,SPACE=(CYL,(1,1))
//SYSUT4   DD UNIT=SYSDA,SPACE=(CYL,(1,1))
//SYSUT5   DD UNIT=SYSDA,SPACE=(CYL,(1,1))
//SYSUT6   DD UNIT=SYSDA,SPACE=(CYL,(1,1))
//SYSUT7   DD UNIT=SYSDA,SPACE=(CYL,(1,1))
//SYSIN    DD DSN=&&DB2OUT,DISP=(MOD,DELETE)
//*
//LKED    EXEC PGM=HEWL,PARM='LIST,XREF,LET,MAP,AMODE=31,RMODE=ANY',
//             REGION=2M,COND=(5,LT,COB2)
//SYSLIN   DD DISP=(OLD,DELETE),DSNAME=&&LOADSET
//SYSLIB   DD DISP=SHR,DSN=SYS1.SCEELKED
//         DD DISP=SHR,DSN=&DB2SYS..DSNEXIT
//         DD DISP=SHR,DSN=&DB2SYS..DSNLOAD
//         DD DISP=SHR,DSN=&HLVL..LOAD
//         DD DISP=SHR,DSN=SYS1.COB2LIB
//SYSUT1   DD UNIT=SYSDA,SPACE=(CYL,(1,1))
//SYSPRINT DD SYSOUT=*
//SYSLMOD  DD DISP=SHR,DSN=&HLVL..LOAD(&PROGRAM)
//SYSIN    DD DUMMY,DISP=SHR
//*
//BIND    EXEC PGM=IKJEFT01,DYNAMNBR=100,REGION=512K,
//             COND=(5,LT,COB2)
//STEPLIB  DD DISP=SHR,DSN=&DB2SYS..DSNEXIT
//         DD DISP=SHR,DSN=&DB2SYS..DSNLOAD
//DBRMLIB  DD DISP=SHR,DSN=&HLVL..DBRMLIB
//SYSUDUMP DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSTSPRT DD SYSOUT=*
//SYSTSIN  DD DUMMY
//         PEND
//*
//JS01     EXEC  COMPDB2
//*
//LKED.SYSLIN DD
//       DD *
  INCLUDE SYSLIB(DSNALI)
  INCLUDE SYSLIB(CEEUOPT)
  NAME CMST01(R)
//*
//BIND.SYSTSIN DD *
  DSN SYSTEM(DB91)
  BIND PLAN        (CMST01) -
       OWNER       (DBUCMST) -
       QUALIFIER   (DBUCMST) -
       MEMBER      (CMST01) -
       ACTION      (REPLACE) -
       CURRENTDATA (NO) -
       DEGREE      (1) -
       EXPLAIN     (NO) -
       ISOLATION   (CS) -
       RELEASE     (COMMIT) -
       VALIDATE    (BIND)
//*