Work with staging databases by specifying staging at the following levels:
- Database and table levels
- Secondary staging database
- System level
- Job level
- Database level
- Object level
Target staging tables are created as NOPI tables unless TPTAPI_UPDATE is being used to load data to the staging table. TPTAPI_UPDATE does not support loading to a NOPI staging table. In this case, if the table is a time series table, the target staging table is created with the default Primary Index; otherwise, the target staging table is created with the same Primary Index as the source table. Source staging tables for a table object are created using the original source table ddl. Source staging tables for a view object are created the same way as the target staging table.
Specified staging databases are only used when the job needs to create temporary objects. The following table lists when a staging database is used, based on the force utility selected.
| Force Utility | When Target Staging Database is Used |
|---|---|
| DSA | Table is a child on the source database and does not exist on the target database. |
| Table exists on the target database and has either a parent or child on the target database. | |
| Job is a partial copy, source staging is used, and the target table is not empty. | |
| Target table exists and force_target_staging_table is true. | |
| Job is a partial copy of view data directly to a target view when source staging is used. | |
| Table has a trigger with a before action. | |
| JDBC | Target table exists and is not empty. |
| Target table is an RI parent. | |
| Target table is an RI child. | |
| Target table has indices. | |
| Table is a temporal table. | |
| Target table has secondary indices. | |
| Job contains a trigger with action time before. | |
| Target table has a trigger. | |
| Target table exists and is empty, but force_target_staging_table is true. | |
| Job is copying view data to target view. | |
| T2T | Target table exists and is not empty. |
| Target table is an RI parent. | |
| Target table is an RI child. | |
| Target table has indices. | |
| Table is a temporal table. | |
| Target table has secondary indices. | |
| Job contains a trigger with action time before. | |
| Target table has a trigger. | |
| Target table exists and is empty, but force_target_staging_table is true. | |
| Job is copying view data to target view. | |
| TPT | Target table is not empty and is not performing an upsert. |
| Target table is an RI child and is not performing an upsert. | |
| Target table has indices and not performing an upsert. | |
| Table is a temporal table and is performing an upsert. | |
| Target table has secondary indices and is not performing an upsert. | |
| Job contains a trigger with action time before and is not performing an upsert. | |
| Target table contains a trigger and is not performing an upsert. | |
| Target table exists and is empty, but force_target_staging_table is true. | |
| Job is copying view data to target view. |