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
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.
- There is NO WAY we can ask for making schema consistent.
- 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
- 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.
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:
- 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)
- The query to get database connection info below should be attached to SEM0
'CURRENT_ENVIRONMENT' as CURRENT_ENVIRONMENT,
case CURRENT SERVER
when 'SEMREC_D' then 'SEM_DEV'
when 'SEM_PAT' then 'SEMPAT'
end as LIBRARY_NAME
- The dynamic_Library query must use native SQL therefore query processing has to be defined as “Limited local”
- The query MUST be published as well. Otherwise, the schema will be always the default one.