Analyzing a table with a migration strategy does not generate POSITION ( 1) for the 1st column in LOAD SYSIN

Article Id: 19781

Status: Published

Updated On: 21-03-2018 07:16

Legacy Id: TEC602304

Products:

CA RC/Migrator , CA Endevor SCM Interface , DB2 Administration , CA RC Compare for DB2 for z/OS , CA RC Extract for DB2 for z/OS , CA RC/Query , CA RC Secure for DB2 for z/OS , CA RC Update for DB2 for z/OS

Issue/Introduction:

Question:

A migration where data are being unloaded/loaded generates LOAD cards where the first column in the load cards has a starting position higher than 1; e.g. POSITION( 2). Why?

Answer:

When the Batch Processor UNLOAD program is utilized, the unload dataset will have extra byte(s) at the beginning of each row if a nullable column exists on the table.
Because of this, the first column in the load cards is coded as starting at a higher position than 1 and the NULLIF parm are added as the last card, e.g. (NULLIF( 1) = X'FF).
Here is an example where the first column EMPNO starts in POSITION( 11) because the first 10 bytes are NULLIF indicators.

.DATA                                                           
   LOAD DATA INDDN PTILOAD RESUME YES     LOG NO                
    ENFORCE CONSTRAINTS                                         
    DISCARDDN SYSDISC                                           
    ERRDDN   SYSERR                                             
    MAPDDN   SYSMAP                                             
    WORKDDN  (PTIUT1,PTIOUT)                                    
    SORTDEVT SYSDA                                              
    CONTINUEIF (72) = X'FF'                                     
    INTO TABLE DSN81012.EMP                                     
         IGNOREFIELDS NO                                        
    (EMPNO                            POSITION(    11 )         
     CHAR(     6)                                               
    ,FIRSTNME                         POSITION(      *)         
     VARCHAR                                                    
    ,MIDINIT                          POSITION(      *)         
     CHAR(     1)                                               
    ,LASTNAME                         POSITION(      *)         
     VARCHAR                                                    
    ,WORKDEPT                         POSITION(      *)         
     CHAR(     3)                     NULLIF(     1) = X'FF'    
    ,PHONENO                          POSITION(      *)         
     CHAR(     4)                     NULLIF(     2) = X'FF'    
    ,HIREDATE                         POSITION(      *)         
     DATE EXTERNAL(    10)            NULLIF(     3) = X'FF'    
    ,JOB                              POSITION(      *)         
     CHAR(     8)                     NULLIF(     4) = X'FF'    
    ,EDLEVEL                          POSITION(      *)         
     SMALLINT                         NULLIF(     5) = X'FF'    
    ,SEX                              POSITION(      *)         
     CHAR(     1)                     NULLIF(     6) = X'FF'    
    ,BIRTHDATE                        POSITION(      *)         
     DATE EXTERNAL(    10)            NULLIF(     7) = X'FF'           
    ,SALARY                           POSITION(      *)                
     DECIMAL                          NULLIF(     8) = X'FF'           
    ,BONUS                            POSITION(      *)                
     DECIMAL                          NULLIF(     9) = X'FF'           
    ,COMM                             POSITION(      *)                
     DECIMAL                          NULLIF(    10) = X'FF'           
    )

Environment:

Release:
Component: RCM