4.2.1.4.8 Gather Stats of Database Tables

The PBSMCS Processing Tables – Gather Statistics utility gathers and updates statistics for database tables (such as FSI_D_ASSET, FSI_D_LIABILITY, etc.).

If the target table is partitioned by As_of_Date, then statistics are gathered only for that specific partition. If the partition contains sub-partitions, then utility will also gather statistics for all relevant sub-partitions.

This utility is useful after data reload operations. In scenarios where a delete file operation is performed using the File Upload and Download utility, it is recommended to schedule the Gather Statistics utility.

To Gather Stats of database tables, following these steps:

  1. Navigate to Operations and Processes, select Scheduler, and then select Define Batch.
  2. Create a new batch by entering Code, Name and Description. Save the batch.
  3. Navigate to Define Task.
  4. Select the Batch created above from the Batch drop-down list.
  5. Click the Add button from Actions drop-down.
  6. Enter the following details in the Task Details Section:
    • Task Code: Enter a Code for the task.
    • Task Name: Enter Name for the task. The Task Name must be alphanumeric and must not start with a number. The Task Name must not exceed 60 characters in length. The Task Name must not contain any special characters except underscore (_).
    • Description: Enter the Task Description. Special characters are not allowed in Task Description. Words like Select From or Delete From (identified as potential SQL injection vulnerable strings) should not be entered in the Description.
  7. Select Component as Transform Data.
  8. Select Batch Service URL as DMT_Service.
  9. Select “PBSMCS Processing tables gather statistics” from Name drop-down list.
  10. In the Extra Parameter field, enter the table name on which the gather stats to be done. You can enter only one table at a time. Individual tables need to be added in task and executed. Wildcard characters are not supported in Table Name.

    Note:

    Do not select any value in Use Effective Date field.

    Figure 4-16 Create Task


    Create Task

  11. From the LHS menu, navigate to Operations and Processes, select Scheduler, and then select Execute Batch.
  12. From the LHS menu, navigate to Operations and Processes, select Scheduler, and then select Monitor Batch.

    The UI displays the status of the batch.

    For more details about Scheduler processes, see the Scheduler Services.