Starting with Enterprise Developer 2.3 Update 2, Enterprise Developer 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:
Neither LOAD nor UNLOAD support parallel data movement from and to multiple partitions of a partitioned table within one JCL job.
MBDT supports a partitioning key consisting of a single column only, and does not support a partitioning key consisting of multiple columns.
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.