Using environment variables at run time with a DB2 stage

Offshored2002 · Post by **Offshored2002** » Wed Jul 23, 2008 12:20 pm

In DS Admin, I have setup a series of parameters, some of which will be passed to a DB2 stage to perform queries or loads.

In my job sequence, I bring in all the environment parms I need, and set them all to $PROJDEF, to fill in the value at run time for easier maintenance when changing things in Admin (we are on 7.5.2, so parameter sets cannot be used). An example here would be the $DB2_Owner (DB2 table owner used in queries, which for this example will be set to DAVE). There are similar variables for DB2 server name, userid and password.

For each job that is called, I set a parm to a name without the $ (eg: DB2_Owner) and in the job's properties/parameter tab, leave the field blank. This allows me to use #DB2_Owner# within the SQL statements vs. #$DB2_Owner# which does not work (various IBM CLI errors).

In the job sequence, the job activity/job tab lists the above field, and I set it to $DB2_Owner. I tried "$PROJDEF" and $PROJDEF here with no luck.

Is my solution to use the $ variables on the job activity screen ok or does anyone have a better idea? As a side note, why can't a #$<parm># be used in a DB2 stage like it can in other stages like sequental file?

dsnovice · Post by **dsnovice** » Wed Jul 23, 2008 1:54 pm

Please post the error messages for better speculation.

thanks,

a novice

ray.wurlod · Post by **ray.wurlod** » Wed Jul 23, 2008 2:57 pm

Not sure WHERE you're doing these things. You use $PROJDEF only in the Job Parameters grid, as a default value token for an environment variable that you have added to the job parameters grid.

Within activities, click the Add Job Parameter button to add parameters; they will be added in a way that is syntactically correct.

Offshored2002 · Post by **Offshored2002** » Thu Jul 24, 2008 1:46 pm

dsNovice:
The typical error that comes back with improperly using a system variable in a DB2 stage is:

Code: Select all

<job name>,0: Fatal Error: Fatal: [IBM][CLI Driver] CLI0124E  Invalid argument value. SQLSTATE=HY009
Unable to connect to DB2 server '#DSCAPIOP__DB2_Server#

Where we tried to use #$DB2_Server# as the DB2 Server name in the DB2DSPX stage. I have resolved this by using #DB2_Server#

Ray, will try and clear things up here:
1. In my job sequence, I clicked on job properties, then the Parameters tab. I then did a "Add Environment Variable" to bring in the needed field that was defined in DS Admin. I set the Default value there to $PROJDEF
(for example: $DB2_Server which in Admin is set to DB2D - the subsystem id for DB2 mainframe to connect to)

This is going to be more of the question here... and since I have been playing around a bit, here is the latest information:
2. On the palette for the job sequence, I pulled up the specific job activity properties, and on the job tab there, which lists all the job's specific parms,
I set DB2_Server, the name of the parm in the job itself, with no $prefixed to a value expression of $DB2_Server (via the Insert Parameter Value Dialog box).

3. Within the parallel job itself, I have not set the DB2_Server parameter.

The above is working. I have made changes to the DB2_Server name in DS Admin and rerun the job, and the data is being pulled from the correct subsystem on the mainframe. As far as the experienced DS folks are concerned, is this ok?

Thanks for everyone's help

ray.wurlod · Post by **ray.wurlod** » Thu Jul 24, 2008 2:57 pm

Your longer description is the correct way to do it, as evidenced by the fact that it works. Please mark the thread as Resolved.

throbinson · Post by **throbinson** » Fri Jul 25, 2008 4:44 am

It may be asking a lot to be able to use a Project environment variable in any stage but it is most certainly NOT asking a lot to be able to use them for connectivity to a data source like DB2. If you needed validation that you aren't asking for a lot, you've got it. I completely agree. To me, this is clearly a DataStage bug or at the very least an enhancement request. I've seen this behavior in other DB stages as well. My workaround is the same as yours; don't use them. This is not a resolution, it is a bad solution since it severely limits the utility of Project environment variables. Unfortunately, it is also the only soution.

Offshored2002 · Post by **Offshored2002** » Fri Jul 25, 2008 5:58 am

Thank you - thread is resolved.

deanwalker · Post by **deanwalker** » Fri Jul 25, 2008 6:42 am

There are several issues with environment variable and/or Parameter Set support in DataBase stages logged with IBM, contact your support provider to establish if a patch is available.