Passing password parameter to DB2 Stage via Sequence

This forum contains ProfileStage posts and now focuses at newer versions Infosphere Information Analyzer.

Moderators: chulett, rschirm

Post Reply
horserider
Participant
Posts: 71
Joined: Mon Jul 09, 2007 1:12 pm

Passing password parameter to DB2 Stage via Sequence

Post by horserider »

My DataStage Job works fine when I read from an DB2 source and having the password hardcoded. When I call the same job via a sequence and passing password through the sequencer the same JOB ABORTS.

Eample: I defined a parameter in Sequence called db2password. I also defined the same parameter in my JOB as well. When calling JOB from Sequence I password db2password=db2password. So JOB should be getting the password from Sequence. In the DB2 Stage I enter #db2password# in password input.

When I hardcode the password the ETL works but I use #db2password# ETL breaks. Error is below

[IBM][CLI Driver] SQL30082N Security processing failed with reason "24" ("USERNAME AND/OR PASSWORD INVALID"). SQLSTATE=08001
Unable to connect to DB2 server 'DBCOMPDATA'.


I tried to peek the password being passed to Job and it is proper. What baffles me is what could be the issue when it is passed properly to JOB via Sequence?

Any ideas?
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Does the job work with the password parameter if you don't call it from a Sequence but just run it from the Director?
-craig

"You can never have too many knives" -- Logan Nine Fingers
andrewn
Premium Member
Premium Member
Posts: 14
Joined: Tue Jul 10, 2007 3:19 am
Location: UK

Post by andrewn »

If you set the OSH_ECHO environment variable to true you will get a message in the Director log that shows the Orchestrate script for your parallel job.

The password for your DB2 stages will appear as plain text (at least it does for me), which might help work out what is being passed when the job is called from a sequence.
horserider
Participant
Posts: 71
Joined: Mon Jul 09, 2007 1:12 pm

Post by horserider »

Ok here is what I have found so far. The job works fine in DEV/TEST environment. Encrypted DB2 password is passed from Master Sequence to Another Sequence and then to JOB and it works fine. It's only in PROD that we have issues with the password parameter.

I debugged it further and changed password parameter to STRING in all 2 Sequence and 1 Job and still job aborts. Hardcoding works though.

I am calling the Master Sequence through a shell script using below script. When I hard code the password the job works

bin/dsjob -run -mode NORMAL \
-param 'db2id'=$db2id
-param 'db2pass'='blackdog918' \

But when I pass the encrypted password it aborts (although works in DEV/TEST)

bin/dsjob -run -mode NORMAL \
-param 'db2id'=$db2id
-param 'db2pass'=$db2password \


So that means something is happening with the password file on UNIX box that I am using to pass but strangely when I view the file the password looks perfect!!!
shamshad
Premium Member
Premium Member
Posts: 147
Joined: Wed Aug 25, 2004 1:39 pm
Location: Detroit,MI

Post by shamshad »

Since you are mentioning that when hardcoding from UNIX works, I bet you it's the password file or the parameter file that you have created for DB2 password. Even though the password looks fine, it might not be !!!

I suggest delete the parameter file/ password file and then run your Shell Script with password file (remove the hardcoded password). I am almost positive it will work.

Good Luck !
Datawarehouse Consultant
horserider
Participant
Posts: 71
Joined: Mon Jul 09, 2007 1:12 pm

Post by horserider »

Shamshad,

Thanks for great input. We just removed the password file and recreated the file and there you go....Jobs in production ran exactly as in QA.

Thank You All once again.
prasanna_anbu
Participant
Posts: 42
Joined: Thu Dec 28, 2006 1:39 am

Post by prasanna_anbu »

horserider wrote:Shamshad,

Thanks for great input. We just removed the password file and recreated the file and there you go....Jobs in production ran exactly as in QA.

Thank You All once again.
I have faced the same problem, could you please explain me what is the password file? since i have to ask my administrator to recreate I need this information.

Thanks
Prasanna.
shamshad
Premium Member
Premium Member
Posts: 147
Joined: Wed Aug 25, 2004 1:39 pm
Location: Detroit,MI

Post by shamshad »

Password file is just a plain text file that contains the password. It is stored on the ETL Server. If Datastage has access to a particular directory you can create it yourself.
Datawarehouse Consultant
synsog
Premium Member
Premium Member
Posts: 232
Joined: Sun Aug 01, 2010 11:01 pm
Location: Pune

Post by synsog »

prasanna_anbu wrote:
horserider wrote:Shamshad,

Thanks for great input. We just removed the password file and recreated the file and there you go....Jobs in production ran exactly as in QA.

Thank You All once again.
I have faced the same problem, could you please explain me what is the password file? since i have to ask my administrator to recreate I need this information.

Thanks
Prasanna.
Hi,

Rather of usinf Password file i would recommend to correct the password from Administrator through Password parameter.
Post Reply