Stored Procedure Stage - Compilation issue

Sudhindra_ps · Post by **Sudhindra_ps** » Fri Jan 04, 2008 8:24 pm

hi All,

Am using following job design.
ExternalSourceStage ->TransformerStage->StoredProcedureStage->SQFileStage

But this works fine with my ID. When I compile this job using different UserID and try to execute it gives Oracle-12154 TNS error on Stored Procedure stage. When I checked OSH script when compiled using my ID against Other ID's. It looks like OSH script is different for Stored Procedure stage in the ETL job. I guess, environmental parameters to connect to database are not getting recognised correctly when compiled with other UserID's. As I can see variable values being appended with keyword called "notLikely" in the OSH script. Please see below the OSH script attached for Variables in Stored Procedure stage when compiled with other UserID's.
-- OSH Script STP Dtage(When Job Compiled with other Datastage USerID's)---
DATASRC=\'NOTLIKELYDSCAPIOP_\$PPT_SERVER_NAMENOTLIKELY\',
USERID=\'NOTLIKELYDSCAPIOP_\$PPT_SCHEMA_USRNOTLIKELY\',
PASSWORD=\'[&"DSCAPIOP_$PPT_SCHEMA_PWD"]\',
DATABASE=\'NOTLIKELYDSCAPIOP_\$PPT_SERVER_NAMENOTLIKELY\',
------------------------------------------------------------------------------------

----- OSH Script STP Stage (When Job Compiled with my Datastage USerID)------
DATASRC=\'[&"DSCAPIOP_$PPT_SERVER_NAME"]\',
USERID=\'[&"DSCAPIOP_$PPT_SCHEMA_USR"]\',
PASSWORD=\'[&"DSCAPIOP_$PPT_SCHEMA_PWD"]\',
DATABASE=\'[&"DSCAPIOP_$PPT_SERVER_NAME"]\',
---------------------------------------------------------------------------------
Job works fine when compiled using my UserID i.e. it don't have keyword "NOT LIKELY" appended to "DSCAPIOP".
As far as privileges and roles are considered it is all same for my userID and that of others.
Can any body suggest me what could be the reason in getting this OSH script corrupted when compiled with other UserID's. Any suggestions is highly appreciated.

Thanks & regards
Sudhindra P S

ray.wurlod · Post by **ray.wurlod** » Fri Jan 04, 2008 10:17 pm

What's different about your ID? Do you, for example, have read permission to tnsnames.ora and the other ID does not?

Sudhindra_ps · Post by **Sudhindra_ps** » Sat Jan 05, 2008 2:32 am

hi Ray,

I don't see any difference about my User ID against other User ID's. All our UserID's belong to same group on ETL box and privileges set are also the same. I was browsing through couple of tech forums and found that this is potentially an bug in Datastage and there is an patch readily available for this with IBM. Anyhow, I have escalated this to IBM and let me see what they suggest so, that I could post the same here.

Thanks & regards
Sudhindra P S