To run a job with job parameters...........

[vkumar]

Hello Folks!!

I was not able to decide the name of the topic....with this kind of a problem..

Here it is....
I designed a job which has 3 Oracle tables... T1, T2, T3 and a shared container SC1.
T1 is in database O1
T2 and T3 are in database O2 and
The result table of the shared container SC1 is in database O3
I am supposed to get the data in T1, with some lookups in T2 and SC1, transferred to table T3.
I used job parameters for all the usernames, passwords for all the data bases used.....

When I try to run this job....it ABORTS and the log in the Director is,
Error1: -"T1...ORA-01017: invalid username/password; logon denied"-
I retyped the same username, password in the parameters.....and I ran the program for the second time.......Still It ABORTS
The log in the Director is

Error2: -" SC1....ORA-01017: invalid username/password; logon denied"-
I tried changing the information in the parameters....and ran it for the third time....The first error appears....and
These errors repeat ....
As many number of times I try to run the job with the parameter changes it continues....but
The job ran succesfully when I hard coded the values in all the tables and Shared containers used in this job....I am not able to understand what exactly is the problem....


Can any one suggest me a solution.......

[chulett]

Welcome aboard!

Hard to say what your problem might be without seeing the actual job. At first blush, the problem would seem to be that you might have the parameters mixed up, sending the wrong values to the wrong stages. That would be the first thing I would triple-check, especially the passwords as it's impossible to tell from looking at them (if encrypted) which parameter name was used where.

There is a known bug that crops up once in a great while with the encypted parameters typically used for passwords. But it gives a different error ('null password given') and is usually associated with Sequencer jobs passing parameters to child jobs, which you aren't using.

The Oracle stages require three parameters to parameterize everything and you've only mentioned two - user name and password. Do you have a parameter for the DSN (i.e. - the instance) to connect to as well? Could that be part of the problem?

I'd suggest that you reset all of the Oracle stages to use the proper parameters. Do this by right-clicking on the stages and selecting 'Grid Style' this time. On the General / Properties tab, use the Insert Job Parameter button to assign the correct job parameter to the DSN, User Id and Password fields rather than by typing them in by hand. See how that works and let us know.

[pramod_au]

Hi

Please Check your user id and password for DB.

If ur using ODBC stage to connect to DB, click Get SQL Info after providing the DSN, user id and password (in parameter form).
Please Check whether the parameter has been typed correctly in the stage?
#DSN#
#USERID#
#password#

[vmcburney]

A shortcut to checking database parameters is to try the "view data" option from within the job for each database stage. If you have use job parameters for login details the view data will take you to a job parameter screen where you can change parameters or accept defaults. You should see all three parameters being offered to you, database, login and password. If any of the three are missing then you have a mispelt parameter name. If all three appear then you can choose okay and it will attempt to retrieve the data.

[chulett]

Very true and excellent advice, especially the part about recognizing if all parameters are being prompted for. Only problem is with the stages in the Shared Container. From what I recall, they won't have any 'default' values in their parameter fields, so you won't know about mismatches there until runtime.

[vkumar]

I tried hard coding the shared container and used parameters for the rest ....It works...I think the problem is with the shared containers' parameters....But the same shared container is used in other jobs in the same object which are working without any problems....
I tried "view data" option....It gives me an error alternatively...for example..if the director log shows that the shared container has login problem, then I can view the data from the tables......But when the log shows the login problem is in any table as I specified like in Error2....then the view data doesnt work....
Here I mean to say is the that the parameter values are changing in random....I dont know how to control that.....When the same shared container, the same tables and the same parameters work good in other job...What might be the problem?....
I am very thankful for the suggestions....I am still working on this issue.....

A shortcut to checking database parameters is to try the "view data" option from within the job for each database stage. If you have use job parameters for login details the view data will take you to a job parameter screen where you can change parameters or accept defaults. You should see all three parameters being offered to you, database, login and password. If any of the three are missing then you have a mispelt parameter name. If all three appear then you can choose okay and it will attempt to retrieve the data.

[vkumar]

Folks.......Thanx for the suggestions....I am not sure how far is this the solution but....

I tried these following steps...which finally worked.....

1) I changed all the database names, userids, passwords into Upper Case in the parameters....

2) In the Shared Container stage, it asks for parameters which are the database name, userid and passwd...When I tried to enter the parameter for DB password in the Shared container it is not in "encrypted" format but in the parameters it is encrypted....So I tried changing the format in the parameteres to string...then the job works.....
(This might be ridiculous).....Later I changed the format of the password in the "Job parameters" back to "encrypted" and the job executed succesfully.....

I tried running the job for a couple of times with the above changes(after these changes the job has the code which was initially in it where the problem started) and it ran successfully ...I am not sure how far is it going to work....

[Athorne]

On the topic of job parameters, I have a situation I hope someone has an answer for. We have global job parameters setup in the project so that our developers don't have to know the fsadm password to our databases. Those are fine and working well, but in our Development phase we want the developers to use their own ID instead of fsadm, for obvious potential destructive reasons while they unit test their changes. To accomplish this I don't want to have to change all the jobs current parameters from the #$TGTUserName# parameter setup for the project.

At run time I see that in the list of variables that show up in the environment there is our Unix logon variable LOGNAME and it shows the person who is logged in. Is it possible, and if so how could I put the value of $TGTUserName to be a reference to the $LOGNAME variable that the system is picking up. That way all jobs have the parameter in it already I don't have to change the parameter but it will automaticaly change the value to be the person that logged in. Maybe I'm asking too much, but hopefully not. I know I can't do the password the same way, so I had planned on making that a prompt value.

[Athorne]

Well, I've almost answered my own question and it won't work the way I had planned it to. In our current version of Ascential 7.1r1 we have found that you can't change a project parameter value and have a job pickup that change without removing the parameter from the job and re-adding it. This presents an obvious issue for me, so I changed my gameplan a bit and settled on making a common ID like fsadm but not with as much power and just using that for the Development project.

Only problem with that route is that I still have to change the value for the project variable and I still have to remove and re-add the parameter to every job that references it (about 1053 jobs). I know someone here has the answer, how can I do an automated process that removes a specific parameter and re-adds it to every job? I know it's possible because I have a utility that PeopleSoft provided to me to do the exact thing I want to do with the userid, but it does it to the encrypted password variables. There must be a way to do it via the universe command line interface I would guess. ::Wills Ray to answer this post and grant enlightenment::

[ray.wurlod]

Probably easier to do it via editing an export file than trying to get at parameter values through the Repository tables themselves.

Remember there are three sets of parameter values if you do it the latter way; the design time default values, the run time default values (set in Director), and the actual values (set when the job runs).

Editing an export file then recompiling is probably much quicker than trying to nut out how to update nested tables for which no metadata exist.

[dstechdev]

We had a situation where we had to add an environmental variable created in Administrator to approx 60 jobs. We first created the variable with a default value. When we went to add the variable to each job, the default value was being populated. Realizing that this value could change, I found that using $PROJDEF as the default value in the job would pick up the value set in administrator. We first set the default value in administrator to $PROJDEF and when the variable was added to each job, the default was set to $PROJDEF. After compiling the jobs, we reset the value of the variable in administrator to the value we needed and all of the jobs picked up the new value. Using Rel 7.1

[Athorne]

We just came across the exact same solution, thank you for the response. I have no idea how it lost the references to $PROJDEF but now I'm concerned going forward that we could be missing those $PROJDEF references. Like Ray suggested, I will probably put a little tool together to verify our xml exports have $PROJDEF values for the Environment variables before we import them into our test and prod projects.