Tables Cleanup and Index Maintenance

The SQL Loader is running in parallel mode in order to maximize the performance. Therefore the target tables are not emptied prior to the data insertion and it has to be done implicitly, by running corresponding batch jobs.

Indexes are disabled during the data upload and then need to be re-built afterwards.. The product provides batch processes that support index disabling and re-building indexes and gathering table statistics.

Most of the larger volume tables in the application are partitioned. The partitioned indexes cannot be disabled implicitly by the data upload process and should be disabled implicitly before running the upload.

  • User batch control K1-SCLTB to truncate tables in Staging area and batch control K1-CLNTB to truncate tables in Production

  • Use batch control K1-SDSIN to disable indexes in Staging area and batch control   K1-DRPIN to disable indexes in Production

  • Use batch control K1–RIUSS to rebuild indexes and/or update table statictics in Staging and K1–RIUSP to perform the same in Production

  • Use batch control K1-CLNKY to truncate Key Reference tables in Staging area

  • Use batch control K1-CLNRT to truncate XML Resolution tables in Staging Area

  • Use batch process K1–SDSTG to disable Triggers in Staging and batch control K1–PDSTG to disable triggers in Production

  • Use batch process K1–SENTG to enable Triggers in Staging and batch control K1–PENTG to enable triggers in Production

The supported scenarios are:

  • Truncate Specific Table or Maintenance Object: In order to truncate the specific table, submit the corresponding batch job, specifying table parameter. In order to truncate the data that stored in a specific Maintenance Object, submit the corresponding batch job, specifying maintenanceObject parameter. The process will also disable indexes in the truncated tables.

    Note: If the truncated table’s metadata defines a Key table, the Key table gets truncated too.

  • Truncate all Converted Tables: In order to truncate all converted tables run the corresponding batch job without specifying table or maintenanceObject parameters. The process will disable indexes and truncate all tables marked for conversion in the metadata.

  • Truncate Key Reference Tables or XML Resolution Tables for Specific Table or Maintenance Object: In order to truncate Key Reference table linked to a staging table, submit the corresponding batch job, specifying table parameter. In order to truncate Key Reference tables for a specific Maintenance Object, submit the corresponding batch job, specifying maintenanceObject parameter.

  • Truncate all Key Reference Tables or XML Resolution Tables: Submit the corresponding batch job without specifying table or maintenanceObject parameters to truncate all Key Reference tables.

  • Disable Indexes for Specific Table or Maintenance Object: In order to disable indexes for the specific table, submit the corresponding batch job, specifying table parameter. In order to disable indexes for the specific Maintenance Object’s tables, submit the corresponding batch job, specifying maintenanceObject parameter.

  • Disable Indexes for all converted Tables: In order to disable indexes on all converted tables run the corresponding batch job without specifying table or maintenanceObject parameters. The process will disable indexes and truncate all tables marked for conversion in the metadata.