Page 1 of 1
Environment variables in Parallel jobs
Posted: Tue May 30, 2006 2:42 am
by sairamkrish
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
Environment variables in Parallel jobs
Posted: Tue May 30, 2006 4:56 am
by ashwin141
Are you specifying the schema name with the table name?
Did you check for APT_DBNAME?
Posted: Tue May 30, 2006 5:08 am
by kumar_s
Add $DSCAPIOP__DSN in your job properties.
Posted: Tue May 30, 2006 5:38 am
by sairamkrish
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
Posted: Tue May 30, 2006 6:08 am
by kumar_s
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).
Posted: Tue May 30, 2006 7:08 am
by sairamkrish
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
Posted: Tue May 30, 2006 11:04 pm
by kumar_s
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.
Posted: Thu Jun 01, 2006 11:28 pm
by sairamkrish
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