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
Scheduled job that has a TimeStamp as a parameter
Moderators: chulett, rschirm, roy
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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
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
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,
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,
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.