3.01 Optimize Domain and Repository Size and Performance

Informatica Admin team will receive
concerns from developers saying that development repository performance is too
bad and have issues to work on their regular tasks on daily wise, so as
administrator its admin responsibly to review issues and fix, below are some of
my findings where this could help in improving of performance.

Also please note below steps will clean up
all history or logs etls and you cannot fetch history runs of your jobs, and
note if the maintenance of the repository is not performed regularly, command
line (pmrep truncatelog) might not work or takes long time to truncate the
tables.

NOTE: Make sure to discuss internally with
your team /client before implementing these steps, also you understand logic
behind these steps.

1.1
Cleanup
Unused ETL Objects:

·
Clean
up unwanted / unused folders in repository

(select * from opb_subject)

·
Clean
up all Backup Folders

·
Identify
unwanted/unused workflows in the repository (Check below query)

select * from(

select subj_name,
workflow_name,server_name, trunc(start_time) as start_time,

rank() over (partition by subject_id,
workflow_name order by start_time desc ) rnk

from
OPB_WFLOW_RUN,opb_subject

where OPB_WFLOW_RUN.subject_id=opb_subject.subj_id

) where rnk=1

select server_name,count(1) from(

select subject_id,
workflow_name,server_name, trunc(start_time) as start_time,

rank() over (partition by subject_id,
workflow_name order by start_time desc ) rnk

from
OPB_WFLOW_RUN

) where rnk=1

group by server_name

–order by 1,2,3

·
Identify
in any un used connections can be cleaned up

Select * from opb_cnx

1.2
Update
Stats of Repository Schema

·
After
cleanup of unwanted Folders or ETL’s make sure execute stats job on Repository database.

1.3
Steps
to complete before running the Cleanup queries

1.
Stop
the repository service

2.
Take
the backup of repository (skip workflow and session log, skip deployment
group history, skip MX data) –
this is an optional step.

Ideally – the below operations will not cause any
impact on the repository service, but if required you can take the backup.

Note: Please
do not make this as a script. Perform manually.

1.4
Steps
to cleanup – Queries

1.
Take Backup of below OPB tables.

2.
To truncate all data from the OPB
tables which stores the run history information

TRUNCATE TABLE OPB_SESS_TASK_LOG;

TRUNCATE TABLE OPB_SWIDGINST_LOG;

TRUNCATE TABLE OPB_DTL_SWIDG_LOG;

TRUNCATE TABLE OPB_TASK_INST_RUN;

TRUNCATE TABLE OPB_WFLOW_VAR_RUN;

TRUNCATE TABLE OPB_WFLOW_DEP_RUN;

TRUNCATE TABLE OPB_WFLOW_CACHE;

TRUNCATE TABLE OPB_WFLOW_RUN;

TRUNCATE TABLE OPB_PERF_COUNT;

3.
To backup and retain last 15 days run history (if you have any)

DROP TABLE OPB_SESS_TASK_LOG_BACKUP;

DROP TABLE OPB_SWIDGINST_LOG_BACKUP;

DROP
TABLE OPB_DTL_SWIDG_LOG_BACKUP;

DROP
TABLE OPB_TASK_INST_RUN_BACKUP;

DROP TABLE OPB_WFLOW_VAR_RUN_BACKUP;

DROP TABLE OPB_WFLOW_DEP_RUN_BACKUP;

DROP TABLE OPB_WFLOW_CACHE_BACKUP;

DROP TABLE OPB_WFLOW_RUN_BACKUP;

DROP TABLE OPB_PERF_COUNT_BACKUP;

CREATE
TABLE OPB_SESS_TASK_LOG_BACKUP AS SELECT A.* FROM OPB_SESS_TASK_LOG A,OPB_WFLOW_RUN
B WHERE A.WORKFLOW_RUN_ID=B.WORKFLOW_RUN_ID AND