Thursday, 4 December 2014

Parameterizing Schema's in Informatica

In database level its possible to restrict the access for particular user on all database objects , for example in Oracle database a user named "sh" is having select all tables privilege , if the same user wants to read the tables of other schema("hr") then 
the syntax of query will be as follows:

select * from hr.tablename. 

In some point , we are migrating the Informatica workflows from one environment to another environment where "hr" schema may present "hrr" and at this point we need to change the query associated with "hr" schema. To avoid this we can parameterize the schema name .

Parameterizing in Informatica:

You have read only relational connection on source(say "sh") and you want to access the tables of other schema and also sql override in SQ. In this situation  instead of directly embedding the schema name in query we can parameterize it by declaring the parameter and calling it as follows:

select * from $$schemaname.table2 (not select * from hr.table2) .

In the parameter file , declare the values for schema name like:

File.prm
[global]
$$schemaname=hr

or 

File.prm
[global]
$$schemaname=hrr

 

No comments:

Post a Comment