Staging table management
Where you specify the bulk loading option, InfoSphere® DataStage® Balanced Optimization uses a staging table in the target database, unless the Push all processing into the database option is selected.
By default the staging table is created in the same database instance or table space as the target table, but you can use the Staging table database instance name (Teradata) or Staging tablespace (IBM® DB2®, and Oracle) properties to specify the name of a different database instance or table space in which to create the staging table.
If you do not specify a Staging table name, the Balanced Optimization tool generates a unique staging table name. Target connector properties in the optimized job are set to create the staging table, bulk load it in parallel, post-process its contents into the real target table, and then drop the staging table. Constraints, such as not-null, are not used in creating the staging table, to minimize performance loss during bulk loading, and to accommodate mis-marked column definitions in DataStage job links.
For detailed information about staging table management specific to the Netezza connector, see Netezza connector specific considerations.
After optimization, you can modify these connector properties if required (for example, you can retain the staging table to examine its contents). If you want to use a statically predefined staging table, you can use the generated CREATE TABLE statement in the TableAction section of the connector to create the staging table. Be aware, however, that subsequent reoptimizations of the root job with different options, or after changes in the root job design, might change the required schema, and you will have to respecify the CREATE TABLE statement.
The following table gives the parallel synchronization properties that are set in a target Teradata connector when a job has been optimized to bulk load a table:
| Property | Setting |
|---|---|
| Sync table | Generated name for a private synchronization table for the optimized job |
| Sync server | Blank (defaults to Server property value) |
| Sync user | Blank (defaults to User name property value) |
| Sync password | Blank (defaults to Password property value) |
| Sync database | Set the same as the staging table database instance name optimization property. User must have CREATE TABLE permission in this database |
| Sync ID | Set to a unique value for the specific optimized job. If you plan on running the same optimized job concurrently on different InfoSphere Information Server servers, you need to set this value differently on each such job instance. |
| Sync table action | Create (synchronization table will be created if it does not exist) |
| Sync table cleanup | Keep (synchronization table will be kept after the job runs) |
| Sync write mode | Insert (new rows are added with each execution of this job) |
| Sync poll | 0 (default) |
| Sync timeout | 0 (default) |