Environment variables in Parallel jobs

sairamkrish · Post by **sairamkrish** » Tue May 30, 2006 2:42 am

Hi,

Have a job which extracts records from db2 tables on mainframes using Db2 API stage and loads into a txt file.The DSN/UserId/Pwd are parameterised as environment variable in the Db2 API stage. Job aborts when run with the following message.

f,0: Fatal Error: Fatal: [IBM][CLI Driver] CLI0124E Invalid argument value. SQLSTATE=HY009
Unable to connect to DB2 server '#DSCAPIOP__DSN#'.

It is clearly evident that the parameters are not taken when declared as environment variable. Also the sqlstate=HY009 corresponds to null arguments in SQLBindParameter() function.The same job works fine when the DSN/UserId/Pwd are declared as job parameter variables.

Would like to know how to use environment variable in Parallel jobs.

Thanks
Krishna

ashwin141 · Post by **ashwin141** » Tue May 30, 2006 4:56 am

Are you specifying the schema name with the table name?
Did you check for APT_DBNAME?

kumar_s · Post by **kumar_s** » Tue May 30, 2006 5:08 am

Add $DSCAPIOP__DSN in your job properties.

sairamkrish · Post by **sairamkrish** » Tue May 30, 2006 5:38 am

kumar_s wrote:Add $DSCAPIOP__DSN in your job properties.

Hi,

How do i add that $DSCAPIOP__DSN to job properties?. I dont find that in the list of Environment variables.

Thanks
Krishna

kumar_s · Post by **kumar_s** » Tue May 30, 2006 6:08 am

May I know what is the version of DS you use. If you go to Job properties under parameters tab, you can find 'Add Enviromental variable' button. If you click on it you can find list of all available environmental variables. You can go to the last part, User Defined. And select the variable you have defined there. (Make sure you have defined in the Adminstrator client prior to this).

sairamkrish · Post by **sairamkrish** » Tue May 30, 2006 7:08 am

kumar_s wrote:May I know what is the version of DS you use. If you go to Job properties under parameters tab, you can find 'Add Enviromental variable' button. If you click on it you can find list of all available environmental variables. You can go to the last part, User Defined. And select the variable you have defined there. (Make sure you have defined in the Adminstrator client prior to this).

Hi,

Version is DS 7.5.2.Can u tel me why this variable #DSCAPIOP__DSN# needs to be added in job properties. I already have environment variables for DSN/USER/PWD set in in administrator.

Thanks
Krishna

kumar_s · Post by **kumar_s** » Tue May 30, 2006 11:04 pm

This is to set the environmetal variable at run time.
If you need to use the existing variable, you may include $ENV or $PROJDEF in you Job property.
$ENV will Instructs DataStage to use the current setting for the environment variable.
$PROJDEF - Will reflect the change in the value even after compilation or runtime.
Search on this term you may find lot of post. Also look into FAQ forum you may find a post witten by Vincent.

sairamkrish · Post by **sairamkrish** » Thu Jun 01, 2006 11:28 pm

kumar_s wrote:This is to set the environmetal variable at run time.
If you need to use the existing variable, you may include $ENV or $PROJDEF in you Job property.
$ENV will Instructs DataStage to use the current setting for the environment variable.
$PROJDEF - Will reflect the change in the value even after compilation or runtime.
Search on this term you may find lot of post. Also look into FAQ forum you may find a post witten by Vincent.

Am using $PROJDEF for all the three environment variable $DSN,$USR and $ PWD.. but i get the same error as above.

Thanks
Krishna