Troubleshooting
Problem
Customer uses the "imports data from staging tables" feature of Controller. As part of this, they periodically place new data inside the Controller database table 'xstagefact', and then send a command to import that data into the system.
Over time, the database table 'xstagefact' increases in size.
=> Is there a method to manually delete that old/historic data (from the staging area)?
Symptom
Controller 10.4.1 (and earlier)
In older versions of Controller, inside 'Maintain - Configuration- General' - 'General 3' there is a configuration option "Number of Days Before Records in the Staging Tables are Deleted":
This menu item gives the impression that periodically (example every 31 days) the data inside 'xstagefact' is automatically deleted.
- However, this does not happen. In other words, the system never automatically deletes records/data inside the xstagefact table. Therefore (over time), the amount of data (in that database table 'xstagefact') is growing.
Cause
In Controller 10.4.1 (and earlier versions) the setting 'Import from Staging Tables - Number of Days Before Records in the Staging Tables are Deleted' does not perform any function.
- This is caused by a defect (references APAR PH01895 & also APAR PI44373 )
Resolving The Problem
Fix:
Ensure that you are using Controller 10.4.2 (or later). Then:
- Click 'Maintain - Configuration- General' - 'General 3'
- Ensure that the number of days value is appropriate for your needs (for example 31)
- Click on the button: Delete Staging Tables Records
Workaround:
Manually delete the data (from xstagefact table) outside of the Controller client GUI.
Steps:
There are several methods to manually delete data (from xstagefact) outside of the Controller client GUI:
Method #1 - (easiest for small numbers of job deletions) Delete the batch jobs from Transfer/External Data/Import From Staging Table
This will remove the corresponding data from xstagefact too.
Steps:
As a precaution, before making any changes:
- Create a backup of the database
- Ideally, first try inside a 'test' (or 'development') database first (to make sure you understand/test the procedure).
1. Inside the Controller application, click "Transfer - External Data - Import From Staging Table"
2. Highlight the relevant old jobs (for example any job older than 1 month)
3. Click the 'delete' icon.
Method #2 - (easiest for large number of row deletions) Run a 'delete' (or 'truncate') SQL query.
This method is typically quickest/easiest for many customers, because they typically have many (for example several thousand!) batch jobs.
- However, care should be taken to make sure that your SQL Query only deletes data that you no longer need.
- Customers should talk with their I.T. department's database server administrator ('DBA') for assistance with how to delete old data.
Example:
In one real-life customer example:
- database hosted on DB2
- database schema owner (user) = fastnet
- Customer wanted to delete all the entries inside xstagefact
Related Information
Was this topic helpful?
Document Information
Modified date:
24 January 2020
UID
swg21458934