Repository cleanup

The Idea

Inpired by a post on Stackoverflow I've decided to come up with some solution to find unused objects in PowerCenter Repository.

So, the idea is to find all unused objects in Repository, like for example sources and targets not used by any mapping or mappings not used by any session or sessions that do not exist in any workflows. Doing that manually would require a lot of work and even more time. Doing it using Reository Queries is highly complex - if feasible at all.

Therefore I've created a simple app that uses pmrep tool to find all objects and check their dependencies. It is still time consuming, but once configured requires no manual effort at all.

Download link is available at the bottom.

Setup

All you need to do to run the tool after downloading is edit the config file. The properties are quite simple

#Common parameters

[Common]

infaDir = C:\Informatica\9.0.1

Repository = RepositoryName

#Specify Domain OR Host and Port. By default Domain will be used if specified.

Domain = Domain_Name

Host = your.host

Port = 6005

Folder = InformaticaFolderName

User = UserName

#User security domain. By default Native is used.

UserSecurityDomain = 

#Object list - sample below 

#objectTypeList = mapplet, mapping, session, source, target, worklet

objectTypeList = mapplet, mapping, session, source, target, worklet 

The last one - objectTypeList - can contain any number of object types that should be checked. 

Please keep in mind that looking for all possible object types might be really time consuming!

Running

Once set up, you just need to run the executable. It will prompt for repository password (note: the password is not stored anywhere and must therefore be provided on each run).

During runtime the progress is indicated by listing all steps and the number of objects of each type. Here's a sample:

Finally you'll find a folder named UnusedObjectsReport with the output:

• ListOfUnusedObjects.txt - file containing list of all the unused objects found in the repository
• DeleteUnusedObjects.bat - prepared batch to remove all the unused objects. For safety reasons all the lines in the batch are commended with 'rem ' prefix. You should review and choose which objects to remove. Once executed, all the objects should be removed. Note: this works only with unversioned repositories. 

In addition there will be two files per each object type defined in the list, e.g.:

• mapping.txt - list of all the mappings found in the repository
• mapping_dep - list of all dependencies found for each mapping

But these are just temporary files not intended for any further use. Feel free to inspect them if you like.

The tool requires no installation - simply download, unzip, setup and run. 

It can be downloaded from the following download page.