Oracle Support environment variables in DataStage
These environment variables are concerned with the interaction between DataStage® and Oracle databases.
APT_ORACLE_LOAD_OPTIONS environment variable in DataStage
Set the APT_ORACLE_LOAD_OPTIONS environment variable to control the options that are included in the Oracle load control file.
You can load a table with indexes without using the Index Mode or Disable Constraints properties by setting the APT_ORACLE_LOAD_OPTIONS environment variable. You need to set the Direct option or the PARALLEL option to FALSE, for example:
APT_ORACLE_LOAD_OPTIONS='OPTIONS(DIRECT=FALSE,PARALLEL=TRUE)'
In this example, the stage would still run in parallel. However, since DIRECT is set to FALSE, the conventional path mode rather than the direct path mode would be used.
When loading index organized tables (IOTs), do not set both DIRECT and PARALLEL to true as direct parallel path load is not allowed for IOTs.
APT_ORACLE_PRESERVE_BLANKS environment variable in DataStage
Set the APT_ORACLE_PRESERVE_BLANKS environment variable to set the PRESERVE BLANKS option in the control file. This preserves leading and trailing spaces.
When PRESERVE BLANKS is not set Oracle removes the spaces and considers fields with only spaces to be NULL values.
APT_ORA_IGNORE_CONFIG_FILE_PARALLELISM environment variable in DataStage
Set the APT_ORA_IGNORE_CONFIG_FILE_PARALLELISM environment variable to use the number of data files in the destination table's table space to determine the number of processing nodes available for a parallel write to Oracle.
By default DataStage determines the number of processing nodes available for a parallel write to Oracle from the configuration file.
APT_ORA_WRITE_FILES environment variable in DataStage
Set the APT_ORA_WRITE_FILES environment variable to prevent the invocation of the Oracle loader when write mode is selected on an Oracle Enterprise destination stage.
Instead, the sqlldr commands are written to a file, the name of which is specified by this environment variable. The file can be used after the job finishes to run the loaders sequentially to help track down export and pipe-safety issues that are related to the loader.
APT_ORAUPSERT_COMMIT_ROW_INTERVAL and APT_ORAUPSERT_COMMIT_TIME_INTERVAL environment variables in DataStage
Set these two environment variables to control how often target rows are committed when using the Upsert method to write to Oracle.
Commits are made whenever the time interval period passes or the row interval is reached, whichever comes first. By default, commits are made every 2 seconds or 5000 rows.
APT_ORACLE_LOAD_DELIMITED environment variable in DataStage
It creates delimited records while loading into Oracle's sqlldr, when write is selected on destination Oracle stage. The value of this variable is used as the delimiter. This variable has String type.