LOAD CLPPlus command for z/OS

The LOAD CLPPlus command for z/OS is supported for remotely loading data against Db2® for z/OS® servers.

Invocation

You must run the LOAD command from the CLPPlus interface.

Authorization

Both of the following authorities:
  • Ownership of the table
  • LOAD privilege for the database

Required connection

You must be connected to a database.

Command syntax

Read syntax diagramSkip visual syntax diagramLOAD"<filename>" LOADSTMT "<loadstmt>" UTILITYID "<utilityid>" VERBOSE ONOFF

Command parameters

filename
File from which data is to be loaded. The parameter value has to be enclosed in double quotes.
LOADSTMT
Load statement. User can directly specify the LOADSTMT in the command, or write the LOADSTMT in a file and specify the complete path of the file. This parameter value has to be enclosed in double quotes. Refer to Syntax and options of the LOAD control statement for a detailed definition on LOADSTMT
UTILITYID
Specifies the utility-id for LOAD operation. This is an optional parameter. This parameter value has to be enclosed in double quotes. If the driver returns java.sql.SQLException, CLPPlus will display the same. If the driver returns java.sql.SQLException, CLPPlus will display the same.
VERBOSE
Specifies whether CLPPlus should display the LOAD result on console. This is an optional parameter. The default value for this parameter is ON. Unless the user specifies verbose as OFF, CLPPlus will display the LOAD result on the console. If the CLPPlus tracing is enabled, verbose will be put into the trace file irrespective of the value specified for verbose.

Example

The following LOAD command loads the data from block.cust.del into Db2 for z/OS serve:
SQL> load file "C:\Users\IBM_ADMIN\Desktop\CLPPlus_Deliver\zFastLoadTesting\block.cust.del" 
     loadstmt "TEMPLATE SORTIN DSN &JO..&ST..SORTIN.T&TIME. UNIT SYSVIO SPACE(10,10) CYL DISP(NEW,DELETE,DELETE) 
     TEMPLATE SORTOUT DSN &JO..&ST..SORTOUT.T&TIME. UNIT SYSVIO SPACE(10,10) CYL DISP(NEW,DELETE,DELETE) 
     LOAD DATA INDDN SYSCLIEN WORKDDN(SORTIN,SORTOUT) 
     REPLACE PREFORMAT LOG(NO) REUSE NOCOPYPEND FORMAT DELIMITED EBCDIC INTO TABLE ADMF001.CUSTOMER_LOCAL NUMRECS 30000" verbose off;

DB250000I: The command completed successfully.
Return Code : 0
The following LOAD command loads the data from block.cust.del into Db2 for z/OS server. LOADSTMT is read from a file loadStatement.txt, and VERBOSE is ON:
SQL> Load file "C:\Users\IBM_ADMIN\Desktop\CLPPlus_Deliver\zFastLoadTesting\block.cust.del" loadstmt "@c:\loadStatement.txt" verbose on;

DB250000I: The command completed successfully.
1DSNU000I    110 22:04:44.76 DSNUGUTC - OUTPUT START FOR UTILITY, UTILID = ZLOAD11110342195 
 DSNU1045I   110 22:04:44.80 DSNUGTIS - PROCESSING SYSIN AS UNICODE UTF-8 
0DSNU050I    110 22:04:44.80 DSNUGUTC -  TEMPLATE SORTIN DSN &JO..&ST..SORTIN.T&TIME. UNIT SYSVIO SPACE(10, 10) CYL 
 DISP(NEW, DELETE, DELETE)
 DSNU1035I   110 22:04:44.80 DSNUJTDR - TEMPLATE STATEMENT PROCESSED SUCCESSFULLY 
0DSNU050I    110 22:04:44.81 DSNUGUTC -  TEMPLATE SORTOUT DSN &JO..&ST..SORTOUT.T&TIME. UNIT SYSVIO SPACE(10, 10) 
 CYL DISP(NEW, DELETE, DELETE)
 DSNU1035I   110 22:04:44.81 DSNUJTDR - TEMPLATE STATEMENT PROCESSED SUCCESSFULLY 
0DSNU050I    110 22:04:44.81 DSNUGUTC -  LOAD DATA INDDN SYSCLIEN WORKDDN(SORTIN, SORTOUT) REPLACE PREFORMAT LOG(NO)
  REUSE NOCOPYPEND FORMAT DELIMITED EBCDIC
 DSNU650I  -DB2A 110 22:04:44.81 DSNURWI -  INTO TABLE ADMF001.CUSTOMER_LOCAL NUMRECS 30000
 DSNU1038I   110 22:04:44.89 DSNUGDYN - DATASET ALLOCATED.  TEMPLATE=SORTIN 
                        DDNAME=SYS00007   
                        DSN=DB2AUTL1.DB2AUTL1.SORTIN.T050444
 DSNU1038I   110 22:04:44.90 DSNUGDYN - DATASET ALLOCATED.  TEMPLATE=SORTOUT
                        DDNAME=SYS00008   
                        DSN=DB2AUTL1.DB2AUTL1.SORTOUT.T050444 
 DSNU350I  -DB2A 110 22:04:44.97 DSNURRST - EXISTING RECORDS DELETED FROM TABLESPACE
 DSNU3340I   110 22:04:44.98 DSNURPIB - UTILITY PERFORMS DYNAMIC ALLOCATION OF SORT DISK SPACE
 DSNU3342I   110 22:04:45.01 DSNURPIB - NUMBER OF OPTIMAL SORT TASKS = 1, NUMBER OF ACTIVE SORT TASKS = 1 
 DSNU395I    110 22:04:45.01 DSNURPIB - INDEXES WILL BE BUILT IN PARALLEL, NUMBER OF TASKS = 2
 DSNU3345I   110 22:04:45.01 DSNURPIB - MAXIMUM UTILITY PARALLELISM IS 3 BASED ON NUMBER OF PARTITIONS AND INDEXES  
 DSNU397I    110 22:04:45.01 DSNURPIB - NUMBER OF TASKS CONSTRAINED BY CPUS TO 3  
 DSNU304I  -DB2A 110 22:05:29.65 DSNURWT - (RE)LOAD PHASE STATISTICS - NUMBER OF RECORDS=30000 FOR TABLE
 ADMF001.CUSTOMER_LOCAL 
 DSNU1147I -DB2A 110 22:05:29.65 DSNURWT - (RE)LOAD PHASE STATISTICS - TOTAL NUMBER OF RECORDS LOADED=30000 FOR 
 TABLESPACE DSNDB04.TCUST000
 DSNU302I    110 22:05:29.65 DSNURILD - (RE)LOAD PHASE STATISTICS - NUMBER OF INPUT RECORDS PROCESSED=30000 
 DSNU300I    110 22:05:29.65 DSNURILD - (RE)LOAD PHASE COMPLETE, ELAPSED TIME=00:00:44
 DSNU3350I   110 22:05:29.68 DSNUGSRP - SORT TASK SW01: 30000 RECORDS SORTED, ESTIMATED 30000, VARIATION 0 PERCENT
 DSNU3352I   110 22:05:29.70 DSNUGSRP - SORT TASK SW01: USED DFSORT 
 DSNU3354I   110 22:05:29.70 DSNUGSRP - SORT TASK SW01: MEMORY BELOW THE BAR: OPTIMAL 6 MB, USED 6 MB 
 DSNU394I  -DB2A 110 22:05:29.74 DSNURBXA - SORTBLD PHASE STATISTICS - NUMBER OF KEYS=30000 FOR INDEX ADMF001.XCUST000
 DSNU391I    110 22:05:29.76 DSNURPTB - SORTBLD PHASE STATISTICS. NUMBER OF INDEXES = 1 
 DSNU392I    110 22:05:29.76 DSNURPTB - SORTBLD PHASE COMPLETE, ELAPSED TIME = 00:00:00 
 DSNU3357I   110 22:05:29.79 DSNUGUTC - MAXIMUM SORT AMOUNT ESTIMATION VARIATION WAS 0 PERCENT
 DSNU3355I   110 22:05:29.79 DSNUGUTC - TOTAL SORT MEMORY BELOW THE BAR: OPTIMAL 6 MB, USED 6 MB
 DSNU010I    110 22:05:29.80 DSNUGBAC - UTILITY EXECUTION COMPLETE, HIGHEST RETURN CODE=0 

Return Code : 0