Location of Back-End Programs (Procedures, Function and Packages) in Informatica

There are four databases where we may keep Back-end Programs used as Stored Procedures within Informatica:

1.  Source Database

2.  Target Database

3.  Repository Database

4.  External Database

Generally Source Database is opened by the Client only for Query purpose since any type of mistake during Data Manipulation or Data Definition may damage their database, so client never takes risk for Source Database. So Source Database is not the place for Stored Procedures.

Repository Database is generally for Informatica's internal use and any type of mistake may damage the entire Repository. So we should avoid using Repository Database for our use. So this database also is not for stored procedure.

Target Database is available to Data Warehouse Team for all kind of process including Query, Manipulation and Structure Definition. So this can be one location for Stored Procedures. If numbers of stored procedures are very high and they are used frequently then we should not keep under Target Database since data loading will be affected. In such cases it is better to keep Stored Procedures under External Databases.