Stored Procedure and Environment
Moderators: chulett, rschirm, roy
Stored Procedure and Environment
Hi,
I have a Stored Procedure stage for DB2. In this stage I have to pass Data Source which can be Unit Test environment [XXXX] or System Test environment[YYYY] or Production environment[ZZZZ]
These XXXX/YYYY/ZZZZ are hard-coded values now.
When I move this Job from one environment say from one Test Environemnt [YYYY] to Production environment[ZZZZ] How would I go about chaning this...
Any direction/help on this is highly appreciated.
Thanks,
Vijay
I have a Stored Procedure stage for DB2. In this stage I have to pass Data Source which can be Unit Test environment [XXXX] or System Test environment[YYYY] or Production environment[ZZZZ]
These XXXX/YYYY/ZZZZ are hard-coded values now.
When I move this Job from one environment say from one Test Environemnt [YYYY] to Production environment[ZZZZ] How would I go about chaning this...
Any direction/help on this is highly appreciated.
Thanks,
Vijay
I have an User defined Environemnt variable [$RUNMODE] which is referenced as Job Parameter, and I'm using $RUNMODE in Data Source tab, but it's not resolving giving me the following:-DSguru2B wrote:Parametrize it. Pass it as a parameter to your stored procedure.
SP_CDDM,0: Error: [IBM][CLI Driver] SQL1013N The database alias name or database name "$RUNMODE" could not be found. SQLSTATE=42705
I have given $RUNMODE = DDB3 in the Job Properties and this value [DDB3] is what I want in the tab Data Source
Thanks again for replying on this.
-
- Participant
- Posts: 1
- Joined: Mon Aug 21, 2006 12:49 pm
This is how we have done it with ORACLE..
1. we have created parameter text file which contains oracle userid/password (encrypted) and ServerID (connection string)
2. a unix script is used to extract this 3 data elements from parameter file and pass as parameters to the datastage job
3. in datastage, 3 parameters are used to connect to the database. If we need to run it from designer, then we need to enter the userid/password/SID at run time in parameters box... (we can also set it as default parameters).
In DS, the parameters will be regular parameters e.g. ORA_USER, ORA_PASS, ORA_SID..
So when we move from DEV to QA or PROD, we simple copy the parameter file and modify it for database parameters accordingly.
hth,
kiran
1. we have created parameter text file which contains oracle userid/password (encrypted) and ServerID (connection string)
2. a unix script is used to extract this 3 data elements from parameter file and pass as parameters to the datastage job
3. in datastage, 3 parameters are used to connect to the database. If we need to run it from designer, then we need to enter the userid/password/SID at run time in parameters box... (we can also set it as default parameters).
In DS, the parameters will be regular parameters e.g. ORA_USER, ORA_PASS, ORA_SID..
So when we move from DEV to QA or PROD, we simple copy the parameter file and modify it for database parameters accordingly.
hth,
kiran
You should be able to use the parameters. I am not a big fan of making all parameters as environment variables, but I test it out in a dummy job and it works here. How did you define the environment variable in the first place?
Go to the job properties, click on "Add environment variables", hit on new and define the variable. It will work, works for many of us.
Go to the job properties, click on "Add environment variables", hit on new and define the variable. It will work, works for many of us.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
If the User Defined environment variable is $RUNMODE how do I use it in DataSource tab to take the values of $RUNMODEDSguru2B wrote:You should be able to use the parameters. I am not a big fan of making all parameters as environment variables, but I test it out in a dummy job and it works here. How did you define the environment variable in the first place?
Go to the job properties, click on "Add environment variables", hit on new and define the variable. It will work, works for many of us.
Tried the following..
#$RUNMODE#
#RUNMODE#
Doesn't work...Does that mean someother variable @ project level has to be set...
I get this msg: Invalid argument value. SQLSTATE=HY009
Thanks a ton DSguru2B. You are a DSguru already to meDSguru2B wrote:First option for environment variable and second for just the job parameter. Try this, just specify a job parameter and see if that works. ...
![Smile :)](./images/smilies/icon_smile.gif)
It worked atlast using Job Parameter . Thanks again
Defined Env variable, and assigning to Job Parameter and using Job Parameter in the stage and this works...Thanks