Support for Partitioned Tables in DB2 LUW

Starting with Enterprise Developer 2.3 Update 2, Micro Focus MBDT DSNUTILB supports the DB2 LUW Table Partition Feature for all supported IBM versions of DB2 LUW in terms of data movement from and to partitioned tables in a single partition scope:

• LOAD can load one partition of a partitioned table within one JCL job
• UNLOAD can unload one partition of a partitioned table within one JCL job

Neither LOAD nor UNLOAD support parallel data movement from and to multiple partitions of a partitioned table within one JCL job.

To load data into a single partition of partitioned table, include the PART n option in the control statement in the JCL. For example:

//DB2LDPAR JOB
//DSNUTILB EXEC PGM=TSTUTILB,PARM='DB2C,SH      '
//*
//SYSPRINT DD   SYSOUT=*
//UTPRINT  DD   SYSOUT=*
//SYSUDUMP DD   SYSOUT=*
//STPRIN01 DD   SYSOUT=*
//IDIOFF   DD   DUMMY
//*
//SYSUT1   DD   DISP=MOD,DSN=SH.DSNUTILB.SYSUT1
//SORTOUT  DD   DISP=MOD,DSN=SH.DSNUTILB.SYSOUT
//SYSMAP   DD   DISP=MOD,DSN=SH.DSNUTILB.SYSMAP
//SYSERR   DD   DISP=MOD,DSN=SH.DSNUTILB.SYSERR
//SYSREC   DD   DISP=SHR,DSN=MCSDB2.UNLOAD.ULDB2EMP
//SYSIN    DD   *
LOAD DATA INDDN(SYSREC) ASCII                                  
 INTO TABLE TEST.EMPLOYEE PART 1 REPLACE                                                    
 WHEN(00001:00002) = X'0001'                                                     
 ( "EMPNO"                                                                       
        POSITION(  00003:00008) CHAR(00006)                                      
 , "FIRSTNME"                                                                    
        POSITION(  00009:00022) VARCHAR                                          
 , "MIDINIT"                                                                     
        POSITION(  00024:00024) CHAR(00001)                                      
                          NULLIF(00023)=X'FF'                                    
 , "LASTNAME"                                                                    
        POSITION(  00025:00041) VARCHAR                                          
 , "WORKDEPT"                                                                    
        POSITION(  00043:00045) CHAR(00003)                                      
                          NULLIF(00042)=X'FF'                                    
 , "PHONENO"                                                                     
        POSITION(  00047:00050) CHAR(00004)                                      
                          NULLIF(00046)=X'FF'                                    
 , "HIREDATE"                                                                    
        POSITION(  00052:00061) DATE EXTERNAL                                    
                          NULLIF(00051)=X'FF'                                    
 , "JOB"                                                                         
        POSITION(  00063:00070) CHAR(00008)                                      
                          NULLIF(00062)=X'FF'                                    
 , "EDLEVEL"                                                                     
        POSITION(  00071:00072) SMALLINT                                         
 , "SEX"                                                                         
        POSITION(  00074:00074) CHAR(00001)                                      
                          NULLIF(00073)=X'FF'                                    
 , "BIRTHDATE"                                                                   
        POSITION(  00076:00085) DATE EXTERNAL                                    
                          NULLIF(00075)=X'FF'                                    
 , "SALARY"                                                                      
        POSITION(  00087:00091) DECIMAL                                          
                          NULLIF(00086)=X'FF'                                    
 , "BONUS"                                                                       
        POSITION(  00093:00097) DECIMAL                                          
                          NULLIF(00092)=X'FF'                                    
 , "COMM"                                                                        
        POSITION(  00099:00103) DECIMAL                                          
                          NULLIF(00098)=X'FF'                                    
 )                  
/*
//

The syntax and partition numbering of the JCL follow mainframe DB2 conventions. The partition numbering is 1-based whereas the numbering in DB2 LUW is 0-based. MBDT DSNUTILB converts the two conventions internally. The executed JCL job loads the data set into Partition 0 in DB2 LUW.

To unload data from a single partition of a partitioned table, include the PART n option in the control statement in the JCL in two forms:

UNLOAD TABLESPACE tabespacename PART n FROM TABLE
tablename

And:

UNLOAD TABLESPACE tabespacename PART n

For example:

//ULDB2PAR JOB CLASS=A,MSGCLASS=X,NOTIFY=&SYSUID,REGION=0M
/*JOBPARM  SYSAFF=ANY
//DELETE  EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=*
//SYSIN    DD *
  DELETE MCSDB2.UNLOAD.ULDB2PAR
  SET    MAXCC=0
//*------------------------------------------------------------------
//*       EXECUTE DSNUPROC 
//*------------------------------------------------------------------
//DSNUTILB EXEC PGM=TSTUTILB,PARM='DB2C,ULDB2PAR’
//SYSUT1   DD UNIT=SYSDA,SPACE=(CYL,(10,10))
//SORTOUT  DD UNIT=SYSDA,SPACE=(CYL,(10,10))
//SYSPRINT DD SYSOUT=*
//UTPRINT  DD SYSOUT=*
//SYSPUNCH DD SYSOUT=*
//SYSREC   DD DSN=MCSDB2.UNLOAD.ULDB2PAR,DISP=(MOD,CATLG),
//            UNIT=SYSDA,SPACE=(CYL,(1,1))
//SYSIN    DD *
   UNLOAD TABLESPACE PARTSPACE1 PART 1
   FROM TABLE XINYUC.EMPLOYEE2 
/*                                                                      
//

Again, the partition numbering convention follows mainframe DB2 convention. The JCL specifies PART 1 and the actual partition number in DB2 LUW is 0. A punch card specified in the JCL is generated following execution. The unload data set, MCSDB2.UNLOAD.ULDB2PAR, is automatically cataloged by DSNUTILB. The default code set is ASCII, and can be optionally changed to EBCDIC.