SAP Basis Tech Supports

Symptom
This Note describes the old implementation of the Fastload process using the z/OS BatchPipes subsystem or MVS datasets. The Note was known as Note 1297362 before.
You want to perform a System Copy or Unicode Migration and you want to speed up the database load phase.
Other terms
DB2 z/OS Load Utility
Fast Load
System Copy
Unicode Conversion
BatchPipes
Reason and Prerequisites
Prerequisites:
BatchPipe Subsystem:Ftp patches for APARPK37032andPK54242on z/OS 1.8 are needed for the Ftp connection to the BatchPipe subsystem.BatchPipe Subsystem Support for the DB2 Template Utility: APARPK34251ABEND0C4 in DFSORT while doing RUNSTATS (inline statistics): APARPK50279.DB2 V9 load utility enhancements: APARPK61759ABEND04E RC00C90101 IN DSNONLLE ERQUAL5063: APARPK63781RTS ABEND04E RC00C90101 DSNKFTCH ERQUAL 1006 INVALID PARENT TOKEN RECEIVED FROM IRLM ON A LOCK REQUEST: APARPK62953LOAD UTILITY LOADING ROWS IN WRONG PARTITION WITH VARGRAPHIC PARTITIONING KEYS AND UNICODE TABLE: APARPK78713Size files generated by R3szchk on the export are necessary (The size – or .EXT – files will be referenced in the R3load cmd files).The import oflob tableswith the Load Utility is not yet supported. You can set the environment variable “R3LD_FLD_LOB” to 1 to enable the usage of the Load Utility for tables that contain lob fields.Tables that contain lob fields and are split will not be loaded with theload utility. As DB2 Load Utility does not support to load lob records that are bigger than 32k we need to insert them temporarily into a shadow table. If a lob table contains a high number of long records thismay lead to performance issues or long running threads without commits. Fix for APARPK48432is needed for the creation of the shadow tables.Windows platforms are currently not supported.Tables that share a tablespace with other tables cannot be loaded with the Load Utility (all table in a tablespace with the name ‘XSAP’).Note: To be able to recover a tablespace after load with the load utility you need to perform an image copy of at least each table space that was loaded with the load utility. This is necessary due to the parameters “LOG NO” and “NOCOPYPEND”.This method is not compatible with DB2 V9NOT LOGGEDfast load (Note 1014786) which uses DB2 version 9 tablespace attributeNOT LOGGED.
Tool and library support:
710:R3load Patch #28R3ta Patch #4700:db2radm Patch #10db2jcllib Patch #2dbdb2slib Patch #128R3load Patch #94
Starting with R3load#91, load utility improvements are exploited on
DB2 version 9 and APARPK61759is needed.R3ta Patch #09701:R3ta Patch #01640:db2radm Patch #11db2jclib Patch #6lib_dbsl Patch #187R3load Patch #XX (will be downported on request)
Usage of Load Utility in combination withSAP table splitter(R3ta)

If you use the Load Utility in combination withsplit tables(R3ta) parallel load into the same table is only supported by DB2 into different partitions. The following scenarios are possible:
If the table ispartitioned, use parallel load into different partitions at the same time.To preserve partitioning information on the target table implement SAP Note 1021551. To instruct R3ta on the source system to use partition specific splitpoints use ‘+’ character instead of ‘%’ or ‘:’. The number of packages has to be the number of partitions. With partition specific where clause generation, please consider the following point:Character based limits have to be of the same length as the maximum column length. This is the case as ibm pads with X’FF’ and where clausesas “WHERE COL < ‘A’” are semantically different from “WHERE COL < X’41FFFFFF’”.MINVALUE is not yet supported for limits.You need a Migration Monitor at least 700 SR 3 or 710 SP4. A supported version is attached to this Note. If you use an unsupported Migration Monitor, there will be missing “ext: ” entries in the …__TPI.cmd filesin your SAPinst installation directory. This entry is necessary to use the Load Utility with split tables.You need a custom control.xml to accept ‘+’ as splitpoint character if you want to run R3ta within SAPinst. Exchange the control.xml on your copy of the SAPinst master DVD (described in step one of this Note). A control.xml for Installation Master 700 SR2 and SR3 is attached to this Note. If you want to use a different support release, ask SAP support for help.To test the WHERE clauses for partitioned tables splitted with R3ta, youmay use the shell script attached to this Note (TESTWHR.SAR) and check if the number of rows per split is equivalent to the partition cardinality of a recent RUNSTATS run.If the table isnot partitioned, you can use R3ta to speed up theunload and serialize the Load Utility for the import. This is done via Migration Monitor package ordering. A detailed description when to change the Migration Monitor parameters is given in the step by step solution. As R3load starts DB2 Load Utility then with RESUME YES, gathering of inlinse statistics is not possible in that case.Solution
The following steps are necessary for fast load with the DB2 z/OS Load Utility:
Make a copy of the Installation Master DVD. Exchage the file JAR/db2radm.jar in the sapinst executable for your platform. This can bedone for example on Linux platforms with the zip command. The new version of db2radm.jar will only gather statistics for tables when necessary. On AIX install the zip utility available as rpm package on the linux toolbox cd.mkdir JAR; copy <path_to_jar>/db2radm.jar JAR/mv sapinst sapinst.zipzip -u sapinst JAR/db2radm.jarmv sapinst.zip sapinst
Exchange the control.xml file of the Installation Master DVD. A control.xml for Netweaver 7.00 SR2 and SR3 based installations is attached to this Note:unzip <path_to>/control.zip (for sr2)
or
unzip <path_to>/700_sr3.zipcp <path_to>/control.xml \ <im_dvd>/IM_<PLATFORM>/NW04S/WEBAS/IND/control.xmlCreate the file param.ftp in the installation directory as user <SID>adm. It is needed for the ftp upload connected to the DB2 Utility.db2radm -m enc -s <host> -u <user> -p <password> -f param.ftpTest the ftp connection:db2radm -m tst -f param.ftpSet the following environment variables:R3LD_FASTL_THR -> threshold in bytes when fast load will be used. The sizes – dumpsizes – from the .EXT files will be used. Suggested value: 10000000 for 10 MBytes.dbs_db2_hlq -> high level qualifier where z/OS datasets or BatchPipes will be created. Your connect user must be able to create datasets with this HLQ.dbs_db2_batch_pipe -> name of the BatchPipe subsystem if BatchPipes should be used.dbs_db2_recfm -> record format, either FB (fixed blocklength) or VB (variable blocklength). Variable blocklength is strongly recommended.In addition set the following environment variables if BatchPipes should not be used:dbs_db2_volumes -> volume names that will be used for ftp file upload.(E.g. SAPSMD,SAPSME,SAPSMF,SAPSMA,SAPSMB,SAPSMC)dbs_db2_vcount -> volume count for z/OS datasets.The following optional environment variables can be set:R3LD_FLD_LOB: Set to enable the usage of the Load Utility for tables that contain lob fields. If lob tables should not be loaded with the load utility, the variable must not be set to any value; use unset (sh) or unsetenv (csh).R3LD_FLD_NO_STATS: If set to any value, no inline STATISTICS are gathered.R3LD_FLD_STATS_SAMPLE: If set to avalueand getenv(R3LD_FLD_NO_STATS) == NULL, then the SAMPLE option for the statistics run will be used withvalue.DB2_MAX_RETRY: If you encounter deadlocks on DSNDB06.SYSDBASE in *loader* logfiles (db2radm tries to create the tablespace for the DB2FLDPROT protocol table), you can set this value to force a retry on deadlocks. A suggested value is 10.Perform the following steps to enable “-loadprocedure fast” as additional R3load argument and (if necessary) define load groups:If you use Distribution Monitor you need to add -loadprocedure fast to the r3load.import.loadArgs parameter as described in the Distribution Monitor documentation attached to SAP Note 855772. If you need to serialize the load of several packages, see Migration Monitor documentation (you can find documentation on SMP as described in SAP Note 784118).On installations based on Installation Master 640 PC and installations for releases based on SAP kernel 700, you need to start Migration Monitor manually. Therefore select the option “Start Migration Monitor manually” in SAPinst. Then add -loadprocedure fast to the loadArgs parameter in the file import_monitor_cmd.properties. Example line: “loadArgs=-loadprocedure fast”. If you need to serialize the load of several packages, see Migration Monitor documentation (find documentation on SMP as described in SAP Note 784118).Install in SAPinst Custom Mode. Use the possibility to “stop to verify CREATE DATABASE statements”. When SAPinst stops here, exchange all tools/libraries in the SAP exe directory (e.g. /usr/sap/JCM/SYS/exe/run). Exchange db2radm in the SAPinst installation directory as well.