How to dynamically change schema name in Framework Manager

Check list of all posts

Context:
This is an issue from my reporting project. Cognos directly access operation database.  The system (called SEM) has DEV, SIT, BAT, PAT and Prod environments. For the sake of explanation, I simplify the setup as below
DEV: Database name: SEMREC_D; schema: SEM_DEV
PAT: Database name: SEM_PAT;     schema: SEMPAT

Problem:
As there are two different schemas, you need to deploy different packages for different environment. In other word, you have to deploy package for DEV with SEM_DEV, deploy package for PAT with SEMPAT. This is a very bad deployment solution, as you need to switch package every time when you change data source.

Analysis:

1. There is NO WAY we can ask for  making schema consistent.
2. Explore whether we can make catalogue with default schema in Cognos server, we think this solution is NOT going to work, as scheme is based on setup in framework manger
3. Use parameter maps in framework manager

SEMREC_D; schema: SEM_DEV

SEM_PAT;     schema: SEMPAT

Then we can use macro to make the schema dynamic. However, I don’t find a way to get database connection name with macro.

Solution:
Please download the attached document Dynamically Determine DB2 Library in Cognos Framework Managerfor the basic idea.
However, it won’t work to simply follow this document; there are many tricks below:

1. Two data sources ( SEM and SEM0 ) are needed to be created, otherwise FM model won’t work as this is loop query ( chick in the eggs)

 

2. The query to get database connection info below should be attached to SEM0

SELECT

'CURRENT_ENVIRONMENT' as CURRENT_ENVIRONMENT,

case CURRENT SERVER

when 'SEMREC_D' then 'SEM_DEV'

when 'SEM_PAT' then 'SEMPAT'

…………………………… (more)

else 'SEMPAT'

end as LIBRARY_NAME

FROM SYSIBM.SYSDUMMY1

3. The dynamic_Library query must use native SQL therefore query processing has to be defined as “Limited local”

4. The query MUST be published as well. Otherwise, the schema will be always the default one.