SAP unload/load, SQL*Loader (SAP Library - Database Administration (Oracle) with SAPDBA)

SAP unload/load, SQL*Loader

Use

SAPDBA provides the tools SAP unload/load and SAP unload/SQL*Loader (as of Oracle 7.2) for export/import processes, in addition to Oracle export/import (default). SAP load and SQL*Loader use the same data format created by SAP unload.

Before you start the SQL*Loader, the ORACLE script catldr.sql must be running in directory <ORACLE_HOME>/rdbms/admin under Unix or <ORACLE_HOME>\rdbms<nr>\admin under Windows NT.

Advantages of these Programs in Comparison with ORACLE Export/Import:

Faster export/import of objects.

The data is automatically unloaded as sorted according to the primary index and correspondingly reloaded. The option NOSORT can be used when regenerating the index, so shortening the index generation phase. Instead of using the index chosen automatically by SAPDBA when recovering an index with NOSORT, you can choose an index manually for each table (also NONUNIQUE). A prerequisite is that all index columns are NOT NULL columns. To do this, set the option Unload/load in the menu Transfer method for table data to YES, and the option Index for sorted unload to MANUALLY. The performance of some accesses to table data can also improve because the data is sorted.

If the data loading procedure with SAP load or ORACLE SQL*Loader is terminated it is not necessary to repeat the whole import procedure when restarting (as with ORACLE import). You can restart with the table where the termination occurred.

Simplified reorganization of tables with fields of type LONG RAW.

The DBA frequently had to take manual action (define large rollback segments, edit parameter COMMIT in script imp<TSP>.sh, see for example the SAPDBA online help) in order to reorganize tablespaces containing tables with LONG RAW fields (e.g. PSAPPOOLD). This is no longer required when using SAP unload/SAP load or SAP unload/SQL*Loader if the menu option commit=Y is selected (corresponds to default value).

SAP unload identifies which tables have to be exported using the entries in script ldr<TSP>.par. If you start SAP unload, you can see on the screen how large the individual objects are and how fast they can be exported. SAPDBA determines the average rate of unloading for the objects in the corresponding script ldr<TSP>.par. This is a simple way to collect reference values for the times required for the export/import.

You do not have the Chop option for SAPDBA unload/SAP load or SQL*Loader, as you do for Export/Import. This means that you can unload/load only those tables whose data after unloading is less than the maximum permitted file size in the operating system. More and more operating systems allow files > 2 GB (previously the usual limit).

Scripts/Logs

For a description of the scripts or logs when using Oracle export/import, see SAPDBA Scripts and Files or Log Files and Their Contents. The following contains some notes on the differences in the scripts when you use SAP unload/SAP load or SAP unload/SQL*Loader.

Comparison of the Scripts/Logs when Using:

ORACLE export/import	*SAP unload/SAP load or SAP unload/SQLLoader**
exp/imp<TSP>.sh	unl/ldr<TSP>.sh (Pseudo shell scripts)
exp<TSP>.par	ldr<TSP>.par
exc/imc<TSP>.sh	Do not exist, as compression and R3chop is not yet supported when using SAP unload
exp<TSP>.dmp (File)	ldr<TSP>.dmp (subdirectory)
exp/imp<TSP>.log (temporary logs, if	ldr<TSP>.log parallel processing)
imx<TSP>.sh inx<TSP>.sql	do not exist (no check option has yet been implemented for SAP unload)

The following files are also created in subdirectory ldr<TSP>.dmp for every table to be reorganized:

<OWNER>_<TABLENAME>.dat	Unload dump file
<OWNER>_<TABLENAME>.ctl	Control file for SAP load/SQL*Loader
<OWNER>_<TABLENAME>.log	Detailed log of SAP unload/SAP load
<OWNER>_<TABLENAME>.ldr	Log of the SQL*Loader
<OWNER>_<TABLENAME>.bad	For SQL*Loader errors: data file with the first faulty record
<OWNER>_<TABLENAME>.dsc	For SQL*Loader errors: data file with the first rejected record

The programs SAP unload/SAP load and SAP unload/SQL*Loader recognize which objects should be exported with SAP unload and imported with SAP load or SQL*Loader using the information in ldr<TSP>.par.

Unload Phase

The process of unloading with SAP unload is selected in the restart file with the pseudo-script unl<TSP>.sh. There are no explicit call commands in this script (in contrast to exp<TSP>.sh) because the commands for unloading are generated by SAPDBA. The script unl<TSP>.sh has indicator N in the corresponding restart file until all the objects in ldr<TSP>.par have been successfully processed, that is, all the tables were assigned indicator Y.

This indicator in ldr<TSP>.par in front of the table name specifies whether or not the object was successfully exported with SAP unload.

N	Table was not edited or could not be successfully exported.
Y	Table was successfully exported with SAP unload.

Script unl<TSP>.sh is assigned indicator Y and the indicators in ldr<TSP>.par are reset to N for all the tables when all the tables have been successfully edited with SAP unload.

Load Phase

The load process with SAP load or SQL*Loader is selected in the restart file with the pseudo-script ldr<TSP>.sh. There are no explicit call commands in this script (in contrast to imp<TSP>.sh) because the commands for loading are generated by SAPDBA. Script ldr<TSP>.sh contains indicator S in the corresponding restart file when the load process is started. The indicator for ldr<TSP>.sh is only set to Y once all the objects in ldr<TSP>.par have been successfully edited (that is, all the tables have indicator Y).

Y/N/S

This indicator in ldr<TSP>.par in front of the table name specifies whether or not the object has been successfully imported with SAP load/SQL*Loader.

N	Table was not yet edited or could not be successfully loaded.
S	Editing of this table was started. However, the table could not be loaded successfully.
Y	Table was successfully imported with SAP load/SQL*Loader.

Script ldr<TSP>.sh is assigned indicator Y in the restart schedule once all the tables have been successfully edited with SAP load/SQL*Loader.

This indicator in ldr<TSP>.par specifies the tool to be used for loading. You can edit this indicator manually before a restart

*	SQL*Loader
-	SAP load

For information about restarting a reorganization, see Restarting a Reorganization.