DSNTIAUL unload and load example

Problem:

Is it possible to import data quickly to XDB Server locations using the Mainframe emulation utility?

Resolution:

This article provides a small example to assist you with how to import data quickly to XDB Server locations using our Mainframe emulation utility.

The examples ZIP file contains three files. EMP.SQL is for building the table needed to test DSNUTILB, DSN8810.EMP. There are also two JCL streams, UNLOAD.JCL and LOAD.JCL.

The SYSOUT from the UNLOAD job looks as follows:

DSNU000I DSNUGUTC - OUTPUT START FOR UTILITY, UTILID = XDBUTILB

DSNU050I DSNUGUTC - UNLOAD DATA FROM TABLE DSN8810.EMP

DSNU010I DSNUGBAC - UTILITY EXECUTION COMPLETE, HIGHEST RETURN CODE=0

The SYSPUNCH created by DSNUTILB looks as follows:

LOAD DATA INDDN SYSREC LOG NO RESUME YES

INTO TABLE DSN8810.EMP

WHEN(00001:00002) = X'0001'

( EMPNO

POSITION( 00003:00008) CHAR(00006)

, FIRSTNME

POSITION( 00009:00022) VARCHAR

, MIDINIT

POSITION( 00023:00023) CHAR(00001)

, LASTNAME

POSITION( 00024:00040) VARCHAR

, WORKDEPT

POSITION( 00042:00044) CHAR(00003)

NULLIF(00041)=X'FF'

, PHONENO

POSITION( 00046:00049) CHAR(00004)

NULLIF(00045)=X'FF'

, HIREDATE

POSITION( 00051:00060) DATE EXTERNAL

NULLIF(00050)=X'FF'

, JOB

POSITION( 00062:00069) CHAR(00008)

NULLIF(00061)=X'FF'

, EDLEVEL

POSITION( 00071:00072) SMALLINT

NULLIF(00070)=X'FF'

, 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 LOAD job produces the following SYSOUT:

DSNU000I DSNUGUTC - OUTPUT START FOR UTILITY, UTILID = XDBUTILB

DSNU050I DSNUGUTC - LOAD DATA INDDN SYSREC LOG NO RESUME YES

DSNU650I DSNUGUTC - INTO TABLE DSN8810.EMP

DSNU650I DSNUGUTC - WHEN(00001:00002) = X'0001'

DSNU650I DSNUGUTC - ( EMPNO

DSNU650I DSNUGUTC - POSITION( 00003:00008) CHAR(00006)

DSNU650I DSNUGUTC - , FIRSTNME

DSNU650I DSNUGUTC - POSITION( 00009:00022) VARCHAR

DSNU650I DSNUGUTC - , MIDINIT

DSNU650I DSNUGUTC - POSITION( 00023:00023) CHAR(00001)

DSNU650I DSNUGUTC - , LASTNAME

DSNU650I DSNUGUTC - POSITION( 00024:00040) VARCHAR

DSNU650I DSNUGUTC - , WORKDEPT

DSNU650I DSNUGUTC - POSITION( 00042:00044) CHAR(00003)

DSNU650I DSNUGUTC - NULLIF(00041)=X'FF'

DSNU650I DSNUGUTC - , PHONENO

DSNU650I DSNUGUTC - POSITION( 00046:00049) CHAR(00004)

DSNU650I DSNUGUTC - NULLIF(00045)=X'FF'

DSNU650I DSNUGUTC - , HIREDATE

DSNU650I DSNUGUTC - POSITION( 00051:00060) DATE EXTERNAL

DSNU650I DSNUGUTC - NULLIF(00050)=X'FF'

DSNU650I DSNUGUTC - , JOB

DSNU650I DSNUGUTC - POSITION( 00062:00069) CHAR(00008)

DSNU650I DSNUGUTC - NULLIF(00061)=X'FF'

DSNU650I DSNUGUTC - , EDLEVEL

DSNU650I DSNUGUTC - POSITION( 00071:00072) SMALLINT

DSNU650I DSNUGUTC - NULLIF(00070)=X'FF'

DSNU650I DSNUGUTC - , SEX

DSNU650I DSNUGUTC - POSITION( 00074:00074) CHAR(00001)

DSNU650I DSNUGUTC - NULLIF(00073)=X'FF'

DSNU650I DSNUGUTC - , BIRTHDATE

DSNU650I DSNUGUTC - POSITION( 00076:00085) DATE EXTERNAL

DSNU650I DSNUGUTC - NULLIF(00075)=X'FF'

DSNU650I DSNUGUTC - , SALARY

DSNU650I DSNUGUTC - POSITION( 00087:00091) DECIMAL

DSNU650I DSNUGUTC - NULLIF(00086)=X'FF'

DSNU650I DSNUGUTC - , BONUS

DSNU650I DSNUGUTC - POSITION( 00093:00097) DECIMAL

DSNU650I DSNUGUTC - NULLIF(00092)=X'FF'

DSNU650I DSNUGUTC - , COMM

DSNU650I DSNUGUTC - POSITION( 00099:00103) DECIMAL

DSNU650I DSNUGUTC - NULLIF(00098)=X'FF'

DSNU650I DSNUGUTC - )

DSNU320I DSNURWI - RESUME(YES) WAS SPECIFIED FOR EMPTY TABLESPACE

DSNU300I DSNURILD - (RE)LOAD PHASE COMPLETE, ELAPSED TIME=00:00:00

DSNU304I DSNURWT - (RE)LOAD PHASE STATISTICS - NUMBER OF RECORDS=42 FOR TABLE DSN8810.EMP

DSNU302I DSNURWBF - (RE)LOAD PHASE STATISTICS - NUMBER OF INPUT RECORDS PROCESSED=42

DSNU010I DSNUGBAC - UTILITY EXECUTION COMPLETE, HIGHEST RETURN CODE=4

The UNLOAD dumps the generated LOAD statement into a PDS in this example.