Scheduled job that has a TimeStamp as a parameter

peterbaun · Post by **peterbaun** » Mon May 22, 2006 7:11 am

Hi -

Anyone got a solution to the following :

I have a job with a parameter that is a timestamp. This timestamp is written to a DB2/AS400 database. When running this job from the director entering eg 2006-05-21 15:00:00 it works fine. Note - no quotes around this.

When I schedule the same job and enter the same datetime at the schedule time then quotes are put around the datetime at runtime which causes an error when trying to insert it into the database.

From the log :
Starting Job TimeStampTest.
P = "2006-05-21 14:00:00"

TimeStampTest..DB2_UDB_API_1: [IBM][CLI Driver][AS] SQL0180N The syntax of the string representation of a datetime value is incorrect. SQLSTATE=22007

TimeStampTest..DB2_UDB_API_1: xx = 1 tt = 7716-62122-488 33904:62448:0.0

Anyone has a suggestion to how to get the timestamp parameter to work - without changing the job (or making a calling job) ?

I can pretty easily get it to work when doing this. But since it is a sequence that I schedule then it will require changes in a lot of jobs.

Thanks

Regards
Peter

DSguru2B · Post by **DSguru2B** » Mon May 22, 2006 7:18 am

What mechanism are you using to pass the parameter?
How exactly are you scheduling the job?
Maybe during runtime, when you dynamically pass the parameter, it picks the parameter up (if from a file) with quotes.

chulett · Post by **chulett** » Mon May 22, 2006 7:31 am

What exact 7.x version do you have, Peter? In the 7.5.x versions the parameter value in a Sequence supports expressions, so you wouldn't have to touch any jobs, just the calling sequence. Meaning, you should be able to strip the quotes the scheduler needs as it is passed to the job(s) that needs the timestamp.

peterbaun · Post by **peterbaun** » Mon May 22, 2006 3:02 pm

Jules -
For testing purposes I created a very simple job with a parameter named P :
Transformer --> DB2
Where I generated one row to be inserted.

At runtime when it asks for the parameter value I simply enter : 2005-05-22 14:00:00. No quotes.
This works fine - the entered date is inserted in the database.

When scheduling the job it also asks for the parameter and I enter exactly the same - again with no quotes. But when the job is run it puts quotes around it causing the job to fail.

The annoying thing here is that you seem to get two different results/values of the parameter depending on whether you just run the job or whether it is scheduled. This will not just happen with datetime values - it will happen whenever you have a text with spaces in it.

Craig -
It is DS version 7.5.1.
I see what you mean and I guess that this is the way to get this to work. Normally this is not a problem since the sequence is launched by a job-control job. But for testing purposes I have a situation where I need to omit the job-control job. So I think I will either follow your suggestion or create a temporary job-control job.

Thanks for the input

Regards
Peter

kumar_s · Post by **kumar_s** » Mon May 22, 2006 11:38 pm

What is the type of parameter in the job and Jobsequence for the timestamp? Is it string or date or time?
Just try to write the same to a sequential file.

peterbaun · Post by **peterbaun** » Tue May 23, 2006 4:49 am

Kumar -

Sorry for not specifying that. It is a string. I already did this and the output is different in the two cases.

When running the job directly the output is
2006-05-22 14:00:00

When I use the DS schedular to run the job the output is
"2006-05-22 14:00:00"

DSguru2B · Post by **DSguru2B** » Tue May 23, 2006 6:48 am

As you said, this problem is arising because of the space in between.
As Craig mentioned, apply logic to remove the quotes or send in the timestamp with an underscore or some other character between the date and time part, in place of space and handle that before loading the table.
My 2 cents.
Regards,