IBM Security Verify

 View Only

Clearing the historical data of connectors, schedulers, reports and bulk tools from IGI

By Nishant Singhai posted Wed May 06, 2020 12:42 AM

  
IAM.jpg
Identity Governance and Intelligence (IGI) connectors and schedulers are always running or scheduled to run at given frequency. For the connectors/schedulers which run at higher frequency we can disable the connector history but sometimes we may need to keep the history enabled for some of the connectors/schedulers. For such connectors/schedulers the records are getting inserted into database tables quite frequently and tables may grow large, eventually we see the performance impact while accessing the history of a connector/scheduler. Same is true for the bulk tools and reports as well.

In the report designer module of IGI, we can design any custom report or use existing out of box reports, configure them and assign it to respective roles. Multiple reports are getting generated periodically. Some reports like IDEAS audit report holds large number of records even when we run with specific data range filters.  When a report is generated from IGI console, internally temporary files are created in folder /opt/isig/IDEASPlatformEnv/report_temp/ and once the report generation ends, the temporary file is removed from filesystem and present only om the DB. So finally igi database may hold the report data which keeps growing with generation of reports. It is important to periodically clean the generated reports from database to keep the database size under control. The reports which are already downloaded by the users and holding old data which may not be required to be in live database.

Using the bulk tools we can import users, accounts and entitlements and over the period of time the data uploaded to igi may grow to significant size and hence cleanup will be required.

Here is the information which can help to clean the historical data of connectors, schedulers and reports.

1.      First get the information about the top 10 large tables in IGI database using below command:

SELECT SUBSTR(TABSCHEMA,1,18) TABSCHEMA,SUBSTR(TABNAME,1,30) TABNAME,(DATA_OBJECT_P_SIZE + INDEX_OBJECT_P_SIZE + LONG_OBJECT_P_SIZE + LOB_OBJECT_P_SIZE + XML_OBJECT_P_SIZE) AS TOTAL_SIZE_IN_KB,(DATA_OBJECT_P_SIZE + INDEX_OBJECT_P_SIZE + LONG_OBJECT_P_SIZE + LOB_OBJECT_P_SIZE + XML_OBJECT_P_SIZE)/1024 AS TOTAL_SIZE_IN_MB, (DATA_OBJECT_P_SIZE +INDEX_OBJECT_P_SIZE + LONG_OBJECT_P_SIZE + LOB_OBJECT_P_SIZE + XML_OBJECT_P_SIZE) / (1024*1024) AS TOTAL_SIZE_IN_GB FROM SYSIBMADM.ADMINTABINFO WHERE TABSCHEMA NOT LIKE 'SYS%' order by TOTAL_SIZE_IN_GB desc fetch first 10 rows only

 
Example:



2.      Take a decision which connectors and schedulers should have history enabled.
Decide how long the historical data needs to be kept for connector and schedulers.

3.      Once we decide then we can use below query to check the total records in the scheduler history which are older than 3 months:


select count(*) from IGASERV.TIMER_HISTORY where ID in (select ID from IGASERV.TIMER_HISTORY where  LAST_MOD_TIME <(SELECT CURRENT_TIMESTAMP-3 MONTHS FROM sysibm.sysdummy1 ))


Similarly you can check for older than 15 days etc. For example:


select count(*) from IGASERV.TIMER_HISTORY where ID in (select ID from IGASERV.TIMER_HISTORY where  LAST_MOD_TIME <(SELECT CURRENT_TIMESTAMP-15 DAYS FROM sysibm.sysdummy1 ))

 

To clean these records you can use the delete query below:

delete from IGASERV.TIMER_HISTORY where ID in (select ID from IGASERV.TIMER_HISTORY where  LAST_MOD_TIME <(SELECT CURRENT_TIMESTAMP-3 MONTHS FROM sysibm.sysdummy1 )

 

4.      To check the connector history recorder older than 3 months, we can use below query:

select count(*) from IGACORE.CONNECTOR_HISTORY where ID in (select ID from IGACORE.CONNECTOR_HISTORY where  start_date <(SELECT CURRENT_TIMESTAMP-3 MONTHS FROM sysibm.sysdummy1 ))

To clean these records you can use the delete query below:

delete from IGACORE.CONNECTOR_HISTORY where ID in (select ID from IGACORE.CONNECTOR_HISTORY where  start_date <(SELECT CURRENT_TIMESTAMP-3 MONTHS FROM sysibm.sysdummy1 )

 

5.      To check the reports which are generated older than 3 months, we can use below query:

select count(*) from REPCORE.JOB where ID in (select JOB from REPCORE.JOB_BLOB where LAST_MOD_DATE <(SELECT CURRENT_TIMESTAMP-3 MONTHS FROM sysibm.sysdummy1 ))

To clean these records you can use the delete query below:

delete from REPCORE.JOB where ID in (select JOB from REPCORE.JOB_BLOB where LAST_MOD_DATE <(SELECT CURRENT_TIMESTAMP-3 MONTHS FROM sysibm.sysdummy1 ))

The actual report data is stored in the REPCORE.JOB_BLOB table, once we remove the record from REPCORE.JOB table its associated entry also gets removed from the REPCORE.JOB_BLOB table.

6.      To check the historical data of bulk tool related files which are older than 3 months, we can use below query:

select count(*)  from IGACORE.JOB where ID in (select JOB from IGACORE.JOB_BLOB where CREATION_DATE <(SELECT CURRENT_TIMESTAMP-3 MONTHS FROM sysibm.sysdummy1 ))

To clean these records you can use the delete query below:

delete from IGACORE.JOB where ID in (select JOB from IGACORE.JOB_BLOB where CREATION_DATE <(SELECT CURRENT_TIMESTAMP-3 MONTHS FROM sysibm.sysdummy1 ))

 

Important Note: But be careful while executing this query directly because if the number of records are higher in the database then you may end up with transaction errors. So, depending on the total number of count returned from the first query you may choose to remove a specific number of records at a time.

For example, using below query we remove only 9,000 records at a time:

delete from IGASERV.TIMER_HISTORY where ID in (select ID from IGASERV.TIMER_HISTORY where  LAST_MOD_TIME <(SELECT CURRENT_TIMESTAMP-3 MONTHS FROM sysibm.sysdummy1 ) FETCH FIRST 9000 ROWS ONLY)

We may write a script to automate this process where we only remove ‘x’ records at a time.
0 comments
12 views

Permalink