Stored Procedure and Environment

[vijayrc]

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

[DSguru2B]

Parametrize it. Pass it as a parameter to your stored procedure.

[vijayrc]

Parametrize it. Pass it as a parameter to your stored procedure.

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:-

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.

[DSguru2B]

How are you parametrizing it. Make sure you surround it with hashes. Plus you need to give the instance name for Data Source.

[vijayrc]

How are you parametrizing it. Make sure you surround it with hashes. Plus you need to give the instance name for Data Source. ...

Tried using #$RUNMODE#; #RUNMODE# and other options, but no good

[DSguru2B]

If you hardcode the value. Does it work fine?

[vijayrc]

If you hardcode the value. Does it work fine?

Yes...Hardcoding works...But I'm trying to find a way to make it work when it moves from one environment to another...say from TEST to PROD.

[kiranmarathe]

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

[DSguru2B]

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.

[vijayrc]

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.

If the User Defined environment variable is $RUNMODE how do I use it in DataSource tab to take the values of $RUNMODE
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

[DSguru2B]

First option for environment variable and second for just the job parameter. Try this, just specify a job parameter and see if that works.

[vijayrc]

First option for environment variable and second for just the job parameter. Try this, just specify a job parameter and see if that works. ...

Thanks a ton DSguru2B. You are a DSguru already to me
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