DRS Stage- Environmenta Variable

parameswar · Post by **parameswar** » Fri Oct 12, 2007 9:04 am

Hi,

My job is aborting with below fatal error if I am using Connection details as parameter in DRS stage.
Fatal: ORA-12154: TNS:could not resolve the connect identifier specified.
But if I will hardcode the log in details in DRS stage it is working fine.

Is there any solution to pass user id/Password .. as parameter to DRS stage

Thanks,
Parameswar

DSguru2B · Post by **DSguru2B** » Fri Oct 12, 2007 9:45 am

Parameters work. How are you passing the parameters. They should precede and end with a hash symbol (#).

ray.wurlod · Post by **ray.wurlod** » Fri Oct 12, 2007 2:13 pm

Do some debugging. Find out what was actually passed. Job parameter names must be correctly spelled and correctly cased in order to work properly. Further, they must be enclosed in a pair of "#" characters when used in passive stage types. Did you type the job parameter reference in your design, or use the "Insert Job Parameter" tool?

parameswar · Post by **parameswar** » Sat Oct 13, 2007 4:55 am

ray.wurlod wrote:Do some debugging. Find out what was actually passed. Job parameter names must be correctly spelled and correctly cased in order to work properly. Further, they must be enclosed in a pair of "#" characters when used in passive stage types. Did you type the job parameter reference in your design, or use the "Insert Job Parameter" tool?

Hi,
We are not getting any option "Insert Job parameter" in DRS stage. we are typeing it as #$OFDM_DSN# which is environmental variable. Similarly #$OFDM_PWD#, #$OFDM_UID# for userid and password.

In director log we are getting the correct value of these parameters. But job is aborting with TNS error. Job is working fine when we will type value instead of parameter. I am not getting any clue to fix it

Thanks,
Parameswar

chulett · Post by **chulett** » Sat Oct 13, 2007 7:39 am

Here's a "trick" so that you can use the "Insert Job Parameter" helper. Right-click on the DRS stage and select Grid Style which is the old way you worked with the stages. Switch to the Properties tab and then use the Insert Job Parameter tool to populate your connection information. See if that helps.

I do this all the time to avoid having to hand-type them. Suggest that if you do this, that this is all you use this mode for - set the parameters and then close the stage. Use the normal GUI for everything else.

This works on all database stages, from what I recall.

If this still doesn't work, then I'd suggest you open an official case with support to see if this is a known issue with the DRS stage and if so, if there is a patch available for it.

parameswar · Post by **parameswar** » Sun Oct 14, 2007 10:05 pm

chulett wrote: Here's a "trick" so that you can use the "Insert Job Parameter" helper. Right-click on the DRS stage and select Grid Style which is the old way you worked with the stages. Switch to the Properties tab and then use the Insert Job Parameter tool to populate your connection information. See if that helps.

I do this all the time to avoid having to hand-type them. Suggest that if you do this, that this is all you use this mode for - set the parameters and then close the stage. Use the normal GUI for everything else.

This works on all database stages, from what I recall.

If this still doesn't work, then I'd suggest you open an official case with support to see if this is a known issue with the DRS stage and if so, if there is a patch available for it.

Thanks Chulett. I got your trick to insert job parameter instead of typing it. But it also did not solve the problem as specified below:
Fatal Error: Fatal: ORA-12154: TNS:could not resolve the connect identifier specified . Is this a normal problem with DRS stage or I missed something during installation.

Thanks,
Parameswar

ray.wurlod · Post by **ray.wurlod** » Sun Oct 14, 2007 10:44 pm

Does this connection string include a correct reference to an entry in the tnsnames.ora file?

parameswar · Post by **parameswar** » Wed Oct 17, 2007 2:01 am

ray.wurlod wrote:Does this connection string include a correct reference to an entry in the tnsnames.ora file?

Hi,
Connection string is correct. It is working fine if we will pass value, it is working. The same parameter is working in Oracle stage aslo. But it is a surprise taht DRS stage is not taking this parameter.

Thanks,
Parameswar

pneumalin · Post by **pneumalin** » Wed Oct 17, 2007 1:54 pm

I suspect there is a bug in DRS since I just got the same problem, and I trid it in ODBC Enterprise and it works fine to take the Environment Variables. I can see the failure message of expanding EV in director log as such:
Offering_Selection,0: Fatal Error: Fatal: Unable to connect to database server '#DSCAPIOP__ODBC_DB1#'.

My case is even wiere! I can view data using EV, but it failed during runtime. Will open a case with IBM and let you know the result.

pneumalin · Post by **pneumalin** » Thu Oct 25, 2007 1:01 pm

It's a bug in DRS stage in 7.5.2.
A patch number 704210 has been created for testing. You can ask IBM for it and close this thread if it resolves your problem.

rjhcc · Post by **rjhcc** » Thu Nov 01, 2007 2:00 pm

pneumalin wrote:It's a bug in DRS stage in 7.5.2.
A patch number 704210 has been created for testing. You can ask IBM for it and close this thread if it resolves your problem.

In our environment, the job level parameters are not updating to the defaults set for env varaibles in admin. This is supposed to happen at run time or when viewing data. Once the ENV variable has been pulled in for use as a param in a job, the only way to get the job to see a new default value enteres in ADMIN is to delete it and reload it in the jobs param screen. Will this patch fix that as well?

DSXchange

DRS Stage- Environmenta Variable

DRS Stage- Environmenta Variable

ENV Variables used for Params