You can configure a session to use Sybase IQ, Teradata and Oracle external loaders to load session target files into the respective databases.
The External Loader option can increase session performance since these databases can load information directly from files faster than they can the SQL commands to insert the same data into the database.
When a session used External loader, the session creates a control file and target flat file. The control file contains information about the target flat file, such as data format and loading instruction for the External Loader. The control file has an extension of “*.ctl “ and you can view the file in $PmtargetFilesDir.
For using an External Loader:
The following must be done:
- Configure an external loader connection in the server manager
- Configure the session to write to a target flat file local to the server.
- Choose an external loader connection for each target file in session property sheet.
Issues with External Loader:
- Disable constraints
- Performance issues
- Increase commit intervals
- Turn off database logging
- Code page requirements
- The server can use multiple External Loader within one session (Ex: you are having a session with the two target files. One with Oracle External Loader and another with Sybase External Loader)
- The External Loader performance depends upon the platform of the server
- The server loads data at different stages of the session
- The serve writes External Loader initialization and completing messaging in the session log. However, details about EL performance, it is generated at EL log, which is getting stored as same target directory.
- If the session contains errors, the server continues the EL process. If the session fails, the server loads partial target data using EL.
- The EL creates a reject file for data rejected by the database. The reject file has an extension of “*.ldr” reject.
- The EL saves the reject file in the target file directory
- You can load corrected data from the file, using database reject loader, and not through Informatica reject load utility (For EL reject file only)
Configuring EL in session
- In the server manager, open the session property sheet
- Select File target, and then click flat file options