R3LOAD & JLOAD Files
Unit Overview
This unit gives an overview about all the R3LOAD and JLOAD control and data files.
Unit Objectives
After completing this unit, you will be able to:
• Understand the purpose, contents, and structure of the R3LOAD control and data files
• Understand the purpose, contents, and structure of the JLOAD control, and data files
Unit Contents
Lesson: R3LOAD Files... 130 Lesson: JLOAD Files ... 174 Exercise 7: R3LOAD & JLOAD Files (Part I) ... 187 Exercise 8: R3LOAD & JLOAD Files (Part II, Hands-On Exercise) ... 197
Lesson: R3LOAD Files
Lesson Overview
Purpose, contents and structure of the R3LOAD control and data files
Lesson Objectives
After completing this lesson, you will be able to:
• Understand the purpose, contents, and structure of the R3LOAD control and data files
Business Example
During a R3LOAD system copy some problems occurred. For troubleshooting, you need to know the purpose of all the various control files created.
Figure 87: Overview: R3LOAD Control and Data Files
R3LOAD writes <PACKAGE>*.XML files during Unicode conversions. They contain the primary key of each row which cannot be properly translated to Unicode. The content is used by transaction SUMG to fix the problems in the target system. These files are not discussed in this course.
TADM70 Lesson: R3LOAD Files
Figure 88: R3LOAD: DDL<DBS>.TPL
Figure 89: DDL<DBS>.TPL: Description
The “DDL<DBS>.TPL” files contain the database-specific description of the create table/index statements. R3LOAD uses these descriptions to generate the tables and indexes.
Depending on the database used, the primary key or secondary indexes are generated either before, or after the data is loaded.
Normally the R3LOAD based data export is done sorted by primary key. This default behavior can be switched on and off in the DDL<DBS>.TPL file.
A negative list can be used to exclude tables, views, or indexes from the load process. Typical examples include tables LICHECK and MLICHECK.
The assignment of TABART and data/index storage is made here for databases that support the distribution of data among database storage units.
“Next Extent Size” classes are defined separately for tables and indexes, provided this is supported by the target database.
Database specific drop, delete and truncate data SQL statements can be defined for better performance in R3LOAD restart situations.
Figure 90: DDL<DBS>.TPL: Naming Conventions
The “DDL<DBS>.TPL” files are generated by R3LDCTL. Since R3LDCTL 6.40 “DDL<DBS>_LRG.TPL” files are created to support unsorted exports (were it makes sense). For Oracle parallel index creation was added. You can also see a DDLMYS.TPL file, but this is not used.
TADM70 Lesson: R3LOAD Files
Figure 91: DDL<DBS>.TPL: Internal Structure ≤ 4.6D
Do not change the sections marked “do not change” unless explicitly asked to do so in an SAP Note or by SAP support.
Function / Section names:
• Create primary index order, sorted / unsorted export: prikey • Create secondary index order: seckey
• Create table: cretab
• Create primary key: crepkey • Create secondary index: creind • Do not create and load table: negtab • Do not create index: negind
• Do not create view: negvie • Do not compress table: negcpr • Storage location: loc
Figure 92: DDL<DBS>.TPL: Structure – Create Table
The DDL files are templates used to generate database specific SQL statements for creating tables, primary keys and secondary indexes by R3load. Variables are indicated by “&” and filled with various values from *.STR, *.EXT files, and from the storage sections of the DDL<DBS>.TPL file itself.
Figure 93: DDL<DBS>.TPL: Structure − Create Index
TADM70 Lesson: R3LOAD Files
Figure 94: DDL<DBS>.TPL: Structure − Negative List
The negative list can be used to prevent tables, indexes, and views from being loaded. The entries are separated by blanks and can be inserted into a single line.
Figure 95: DDL<DBS>.TPL: Structure − Table Storage
The default initial extent is only used when no <PACKAGE>.EXT exists or when it does not contain the table.
New TABARTs for additional storage units (i.e. tablespaces for Oracle) can be added to the DDL<DBS>.TPL by changing the table and index storage parameters. If you do this, change the *.STR files, and the corresponding create database templates for R3SETUP (DBSIZE.TPL) or SAPINST (DBSIZE.XML). It is easier to change the ABAP Dictionary before the export, than to change the R3LOAD control files. If R3LOAD cannot find a specific table or index entry in the <PACAKAGE>.EXT file, the missing entry is ignored and default values are used.
Figure 96: DDLDBS.TPL: Structure − Index Storage
The default initial extent is only used when no “<PACKAGE>.EXT” exists, or when it does not contain the index.
The same index storage parameters are used for primary and secondary indexes.
Figure 97: DDL<DBS>.TPL: Structure − Second Example
TADM70 Lesson: R3LOAD Files
Figure 98: DDL<DBS>.TPL: Internal Structure ≥ 6.10
Do not change the sections marked “do not change” unless explicitly asked to do so in an SAP Note or by SAP support.
Function / Section names:
• Create primary key order, sorted / unsorted export: prikey • Create secondary index order: seckey
• Create table: cretab, drop table: drptab
• Create primary key: crepky, drop primary key: drppky • Create secondary index: creind, drop secondary index: drpind • Create view: crevie, drop view: drpvie
• Truncate data: trcdat • Delete data: deldat
• Do not create table: negtab • Do not load data: negdat • Do not create index: negind • Do not create view: negvie • Do not compress table: negcpr • Storage location: loc
Figure 99: DDL<DBS>.TPL: Structure − DROP/DELETE Data
Above are the templates for dropping objects and deleting/truncating table data. The “&where&” condition is used when restarting the import of splitted tables. All other sections are similar to 4.6D and below. Some functions apply to specific database types or database releases only.
TADM70 Lesson: R3LOAD Files
Figure 101: <PACKAGE>.EXT: Initial Extent (1)
The <PACKAGE>.EXT files will created for all database types, because the extent values are used to compute the size of the target database DBSIZE.TPL/DBSIZE.XML and for package splitting.
Figure 102: <PACKAGE>.EXT: Initial Extent (2)
The size of “initial extent” is based on assumptions about the expected space requirements of a table. Factors such as the number and average length of the data records, compression, and the data type used, play an important role.
In case of Oracle dictionary managed tablespaces the values for the “initial extent” can be increased or decreased as required. Observe database-specific limitations for maximum “initial extent” sizes.
R3ZSCHK limits the maximum initial extent to a value of 1.78 GB. This was implemented to prevent data load errors of very large tables because of having not enough consecutive space in a single storage unit, otherwise small tables or indexes could block the storage unit easily. Today's database releases handle the storage more flexibly, making this mechanism obsolete.
Even if the maximum size of a table is limited to 1.78 GB (more precisely 1700 MB), this information is accurate enough for package splitting.
If R3LOAD cannot find a specific table or index entry in the <PACAKAGE>.EXT file, the missing entry is ignored and default values are used.
Typical warning in R3SZCHK.log if reaching the size limit:
• WARNING: REPOLOAD in SLEXC: initial extent reduced to 1782579200 • WARNING: /BLUESKY/FECOND in APPL0: initial extent reduced to
1782579200
TADM70 Lesson: R3LOAD Files
Figure 104: <PACKAGE>.STR: Description (1)
The term “package” is used as a synonym for R3LOAD structure files (*.STR). The data of tables in SAP0000.STR will never be exported. ABAP report loads must be regenerated on the target system.
The ABAP Nametab tables DDNTF / DDNTT (and since 6.x DDNTF_CONV_UC / DDNTT_CONV_UC for Unicode conversions) require a certain import order. The JAVA-based Package Splitter makes sure that the Nametab tables are always put into the same file (SAPNTAB.STR).
If R3LOAD cannot find a specific table or index entry in the <PACAKAGE>.EXT file, the missing entry is ignored and defaults values are used.
Figure 105: <PACKAGE>.STR: Description (2)
The buffer flag is used for OS/390 migrations (as of Release 4.5A). It indicates how to buffer tables in an OS/390 DB2 database.
Table type (conversion type with code page change): • C = Cluster table
• D = Dynpro (screen) table
• N = Nametab (active ABAP Dictionary) • P = Pooled table
• Q = Unicode conversion related purpose • R = Report table
• T = Transparent table
• X = Unicode conversion related purpose R3LOAD activity:
• all = Create table/index and load data
• data = Load data only (table must be created manually) • struct = Create table/index, but do not load any data
For tables which are marked with “struct”, R3LOAD will not create a data export or import row inside the task file. This will prevent the export or import of unwanted table data.
Comments are indicated by a “#” character in the first column.
Figure 106: <PACKAGE>.STR: Object Structure (1)
The total of field lengths is the offset of the next data record to read in the “<PACKAGE>.<nnn>” file.
TADM70 Lesson: R3LOAD Files
Figure 107: <PACKAGE>.STR: Object Structure (2)
The “dbs:” list specifies databases for which the object should be created. A leading “!” means the opposite. In the above example, the index MLST~1 will be created on all databases except ADA and MSS. The index MLST~1AD will be created on ADA and MSS only. The “dbs:” was implemented, starting with R3LOAD 6.40.
Figure 108: SAPVIEW.STR: View Structure
Views are not generated in the target system until all of the tables and data have been imported.
The corresponding “SAPVIEW.EXT” file does not contain any entries or does not even exist, since views do not require any storage space other than for their definition in the DB Data Dictionary.
Figure 109: R3LOAD: <PACKAGE>.TOC
Figure 110: <PACKAGE>.TOC: Description
The content of the <PACKAGE>.TOC file is used by R3LOAD version 4.6D and below, to restart an interrupted export. As of R3LOAD 6.10, the <PACKAGE>.TSK file is used for the restart.
TADM70 Lesson: R3LOAD Files
Figure 111: <PACKAGE>.TOC: Internal Structure ≤ 4.6D
Figure 112: <PACKAGE>.TOC: R3LOAD Restart Export
The above restart description is only valid for R3LOAD less or equal to 4.6D! A restart without option “-r” will force R3LOAD to begin the export at the very first table of the *.STR file in charge. The existing import *.LOG file will be automatically renamed to *.SAV and the existing *.TOC file will be reused, but not cleared. It is recommended to delete the related *.LOG, *.TOC, and dump files before repeating a complete export of a single *.STR file or of the whole database.
If R3LOAD export processes are interrupted due to a system crash or a power failure, the *.TOC file may list more exported tables than the dump file really contains (since the operating system was not able to write all the dump file buffers to disk). In this case, a restart can be dangerous as it starts after the last *.TOC entry which might not be valid. This can lead to missing data or duplicate keys later on. See the troubleshooting chapter for details on how to prevent this situation.
R3SETUP adds the “-r” command line option automatically when restarting R3LOAD.
Figure 113: <PACKAGE>.TOC: Internal Structure ≥ 6.10
Since R3LOAD 6.10, the *.TSK file is used to restart a terminated data export! The *.TOC file is read to find the last write position only.
TADM70 Lesson: R3LOAD Files
In case of splitted tables, the WHERE condition used during the export is written into the respective *.TOC file. Before starting the import, R3LOAD compares the WHERE condition in the *.TOC file against the where condition in the *.TSK file. R3LOAD assumes a problem if they do not match and stops on error.
If there is an error during data load and R3LOAD must be restarted, the WHERE condition is used for selective deletion of already imported data.
Unicode code pages: 4102 Big Endian, 4103 Little Endian.
Non-Unicode code pages: 1100, MDMP (for exports of MDMP systems).
Figure 115: R3LOAD: <PACKAGE>.<nnn>
Depending on the source database used for the export, a data compression ratio of between 1:4, and 1:10 or more can be achieved. The compression is performed at block level, so the file cannot be decompressed as a whole.
Some versions of R3SETUP/SAPINST are asking for the maximum dump file size (other versions use different defaults - check the *.CMD file for the used value). Each additional dump file (for the same *.STR file) is assigned to a new number (such as SAPAPPL1.001 or SAPAPPL1.002). The files of a PACKAGE are all generated in the same directory (if not specified differently in the *.CMD file – >=6.10 only!). Make sure that the available disk space is sufficient.
A checksum calculation at block level is implemented as of R3LOAD 4.5A to ensure data integrity.
R3LOAD versions 4.5A and above compare source system information obtained from the dump file against the actual system information. If R3LOAD detects a difference in OS or DB, a migration key is necessary to perform the import (see GSI section in export log file).
Figure 117: <PACKAGE>.<nnn>: Internal Dump File Structure
R3LOAD reads a certain amount of database data into an internal buffer and does a compression on it. The number of written blocks (group) will depend on the compression result and block size used. This figure is also written into the dump, to tell R3LOAD how many blocks to read later on.
Since 4.5A, a header block is used to identify heterogeneous system copies and to verify the migration key.
TADM70 Lesson: R3LOAD Files
Implemented with 4.5A, was that every group of compressed data blocks has its own checksum. Before a checksum can be verified, all blocks of a group must be read by R3LOAD. If a dump file has been corrupted during a file transfer, typical R3LOAD read errors will be: RFF (cannot read from file), RFB (cannot read from buffer), or “cannot allocate buffer of size ...”. For more details, see unit “Troubleshooting”.
Figure 118: R3LOAD: <PACKAGE>.LOG
The header entries (GSI) of the export *.LOG file show important information which can be useful for the migration key generation.
Figure 120: <PACKAGE.LOG>: Export Log ≥ 6.10
Since R3LOAD 6.10 the installation number of the exported system was added.
TADM70 Lesson: R3LOAD Files
Figure 122: <PACKAGE>.LOG: R3LOAD Restart Import
Up to R3LOAD 4.6D, the restart point for an interrupted import is read from the <PACKAGE>.log file. The restart performs a delete data (DELETE FROM) or a drop table/index.
A restart without option “-r” will force R3LOAD to begin at the very first table of the *.STR. The existing import *.LOG file will be automatically renamed to *.SAV. The import process will terminate on error, as the database objects already exist.
R3SETUP adds the “-r” option automatically when restarting R3LOAD.
Since R3LOAD 6.10, only the *.TSK file is used to restart an interrupted import! The restart point for the data load is the first entry in the *.TSK file of status error (err) or execute (xeq).
Figure 124: <PACKAGE.LOG>: Import Log ≥ 6.40
As of R3LOAD 6.40, separate time stamps for create table, load data, and create index are implemented. This allows a much better load analytics than on previous releases.
TADM70 Lesson: R3LOAD Files
Figure 125: R3LOAD: <PACKAGE>.CMD
Figure 126: <PACKAGE>.CMD: Description
Command files are automatically generated by SAP installation programs R3SETUP, SAPINST, and MIGMON.
Figure 127: <PACKAGE>.CMD: Internal Structure ≤ 4.6D
The “<PACKAGE>.CMD” files contain the names and paths of the files from where R3LOAD retrieves its instructions. The name of the “<PACKAGE>.CMD” file must be supplied in the R3LOAD command line.
R3LOAD dump files can be redirected to different file systems by adapting the “dat:” entry.
The default maximum size (fs) of a dump file is often 1000M (1000 MB). Possible units:
• B = Byte • K = Kilobyte • M = Megabyte • G = Gigabyte
Do not change the block size (bs). Meaning of section names: • icf: Independent control file
• dcf: Database dependent control file • dat: Data dump file location
• dir: Directory file (table of contents) • ext: Extent file (not required at export time)
The DDL<DBS>.TPL file is often read from the installation directory. In this case, R3SETUP/SAPINST copied it from the export directory. This is done for the option to adapt storage locations and so on.
TADM70 Lesson: R3LOAD Files
Figure 128: <PACKAGE>.CMD: Internal Structure ≥ 6.10
Meaning of section names: • tsk: Task file
• icf: Independent control file
• dcf: Database dependent control file
• dat: Data dump file location (up to 16 different locations) • dir: Directory file (table of contents)
• ext: Extent file (not required at export time)
In the above example, the first dump file SAPPOOL.001 will be written to
/migration/DATA, the second dump file SAPPOOL.002 to /mig1/DATA, and so on. The 4th , 5th, etc., ... dump file will be stored in the last defined dump location. If more than one PACKAGE is mentioned in a *.CMD file, a single R3LOAD will execute them in sequential order. This might be useful in certain cases.
Figure 129: R3LOAD: <PACKAGE>.STA
Figure 130: <PACKAGE>.STA: Description
The values are estimates and serve primarily to display the load progress.
The generation of statistic files is switched off by default. Use R3LOAD option -s <stat file> to make use of the statistic feature.
TADM70 Lesson: R3LOAD Files
Figure 131: R3LOAD: <PACKAGE>.TSK
Figure 132: <PACKAGE>.TSK: Description
Since R3LOAD is using task files, the restart points are no longer read from *.LOG or *.TOC files. Complex restart situations with manual user interventions are minimized or more easy to handle.
Objects or data can be easily omitted from the import process by simply changing the status of the corresponding <PACKAGE>.TSK row.
Figure 133: <PACKAGE>.TSK: Internal Structure for Export
The slide above shows the initial <PACKAGE>.TSK file content, after it was created by R3LOAD.