Parameters are quoted by DS when passed in when scheduled

RodBarnes · Post by **RodBarnes** » Fri Apr 06, 2007 4:14 pm

I have a similar, but slightly different problem than I found in the thread "scheduled job picks wrong value of parameter" (viewtopic.php?t=91345). Since it seemed the opposite problem I felt to post a new topic.

Both of these issues occur only when scheduled via Director (using AT). They did not begin appearing until after I changed the job defaults [Job -> Set defaults...]. I have tried re-scheduling the sequence and entering all of the values (overriding the defaults) but it did not correct it. But if I run them manually -- and enter the parameter values -- the problems do not occur.

[I have had a case open for months with support and they are unable to duplicate it though I sent them an export of the project.]

1. In one sequence, a string parameter has a default value of -- 2005-01-01 00:00:00. But when it gets passed in, it is getting quoted -- "2005-01-01 00:00:00". I can see this by examining the "Starting job..." entry in the log.

2. In the same sequence, several encrypted parameters (passwords) are getting quoted when passed in. This results in an error that the password was invalid. The quoted value is unencrypted to a null and so it fails.

In the referenced thread, Craig Chulett states that there is a known problem using quoted values in parameters and that they should be passed in without quotes and the parameter used with quotes around it. However, my problem is the opposite: quotes are added, not removed.

I was wondering if there is a way to revert back to the original defaults some how. I tried re-promoting the sequence but that did not fix the behavior.

ray.wurlod · Post by **ray.wurlod** » Sat Apr 07, 2007 4:08 am

Have you tried revisiting Set Defaults in the Director's Job menu and clicking on "Set to Default" or "All to Default"?

ivannavi · Post by **ivannavi** » Tue Apr 10, 2007 9:54 am

You have spaces in your parameter => It gets double quotes (only when scheduled). This is exactly how it works!
Is this a feature or a bug? I don't know, but you have to loose the spaces.

RodBarnes · Post by **RodBarnes** » Tue Apr 10, 2007 9:57 am

I have other parameters with spaces and they do NOT get quotes around them. Though the values that "work" are read in from a file as opposed to being defaults or entered.

Maybe that is the solution: read the rest of the defaults from a file. Seems an unnecessary work-around.

And if it is the spaces, why does it not put spaces around the value when I run it manually? Is this just something it does when it uses AT?

ivannavi wrote:You have spaces in your parameter => It gets double quotes (only when scheduled). This is exactly how it works!
Is this a feature or a bug? I don't know, but you have to loose the spaces.

RodBarnes · Post by **RodBarnes** » Tue Apr 10, 2007 9:59 am

ray.wurlod wrote:Have you tried revisiting Set Defaults in the Director's Job menu and clicking on "Set to Default" or "All to Default"?

Yes, I tried the "Reset All" button. I tried deleting the scheduled job and recreating it. It always quotes the parameters. But not all of them, just particular ones --- the one date parameter, and a couple password parameters.

chulett · Post by **chulett** » Tue Apr 10, 2007 10:05 am

RodBarnes wrote:Is this just something it does when it uses AT?

Yes. If you don't want to go the file route, split your timestamp in two - send each portion separately.

RodBarnes · Post by **RodBarnes** » Tue Apr 10, 2007 10:20 am

chulett wrote:Yes. If you don't want to go the file route, split your timestamp in two - send each portion separately.

Ok, I am willing to accept this -- except: I have this exact same ETL running in a test environment and on production (both promoted to the enviroment from the same source in version control). It works fine on production -- never quotes the value -- but only recently began doing it on test with the most recent promotion. Previously, it worked fine in both environments.

Makes no sense to me.

ray.wurlod · Post by **ray.wurlod** » Tue Apr 10, 2007 1:15 pm

What's changed?

Involve your support provider. You need to determine whether the quotes have been stored in the job design.

A quick test would be to create a trivial job that uses parameters of the same kind and see whether its parameters are quoted in the various environments. Check the job log for "starting job" events to show the parameter values actually used.

We can go beyond that and list out the parameter records from the Repository table, but let's first get a handle on where the problem originates.

RodBarnes · Post by **RodBarnes** » Fri Apr 13, 2007 12:00 pm

Update: IBM tells me that this is a known issue and there is a fix in 7.5.1A and 7.5.2 so we're looking into that upgrade. In the meantime, I removed the time from the value. I expect it will work tomorrow morning. We'll see...

I tried creating a small sequence that used the same model and it had the same problem. It is easy enough to see the difference when comparing the parameter list between when run manually and scheduled: one has the quotes, one doesn't.

Yet I still don't understand why it works in one project and not another. I even moved copies of the sequences and jobs from the working project to the non-working project and it still exhibits the same behavior. Very weird.

ray.wurlod wrote:What's changed?

Involve your support provider. You need to determine whether the quotes have been stored in the job design.

A quick test would be to create a trivial job that uses parameters of the same kind and see whether its parameters are quoted in the various environments. Check the job log for "starting job" events to show the parameter values actually used.

We can go beyond that and list out the parameter records from the Repository table, but let's first get a handle on where the problem originates.

ray.wurlod · Post by **ray.wurlod** » Fri Apr 13, 2007 3:30 pm

Were the working ones compiled in an earlier version of DataStage and have not been recompiled since?

RodBarnes · Post by **RodBarnes** » Fri Apr 13, 2007 3:49 pm

ray.wurlod wrote:Were the working ones compiled in an earlier version of DataStage and have not been recompiled since?

No. The same modules were pushed to the two projects using Version Control and compiled upon delivery. Both projects are on the same server using the same datastage engine.

But I understand what you are getting at. There must be something different about one project vs. the other. I just cannot figure out what it is.

At least I have a work-around for now.