- From the respective repository DBMS, retrieve a list of all views in the repository schema as a text file. Under Oracle, you can get a list of all these views via a query like SELECT view_name FROM user_views (assuming you're logged on to Oracle as the repository DB user); under DB2, you can use a query like SELECT name FROM sysibm.sysviews WHERE creator = 'PCREPO' (this sample assumes that the repository DB owner is named PCREPO).
- Edit this text file such that every line finally reads like in this example (Oracle syntax):
CREATE PUBLIC SYNONYM mxrep_all_tasks FOR rep_all_tasks;
- Of course you can build a small mapping for step #2, you don't have to do that in a text editor.
- Add statements to this text file which grant SELECT permission to all these public synonyms to a DB user ID which you are using to import source definitions in the Source Analyzer of the PowerCenter Designer.
Under DB2, do not forget to add the command COMMIT WORK; at the end of the text file.
Execute this text file as a DDL script in the repository database.
Open the PowerCenter Designer and a (preferrably shared) folder in the Source Analyzer.
- In the Source Analyzer, start an import from the repository database. The views named like MX* should all be visible. Import them all or import as many as you need for your purposes.
- If you have imported e.g. 10 MX views, you can now simply edit them in the Source Analyzer and remove the prefix MX from their names.
- If you have imported many or all of the synonyms for MX views, a faster way to correct their names would be to export them all to XML files; edit these XML files (e.g. using a PowerCenter mapping with a flat file source and a flat file target) and remove the prefix MX from all the view names; and import the changed XML files.
Now remove the source definitions with names MX* from the repository.
Finally remove the synonyms from the repository database.