Unwanted quotes added to parameter

ianm · Post by **ianm** » Wed Feb 08, 2006 8:39 am

I am trying to schedule a job which is reading back from a production Oracle Database to a development environment (still Oracle), but I do not want any data that is added after a certain date.

So after I have bulk copied the data I delete what I don't need using a before SQL command in an Oracle stage.

DELETE from CONSORT_ORDERBNK WHERE DATE_ADDED > TO_DATE('#parmDateTime#','YYYY-MM-DD HH24:MI:SS')

The above SQL works fine when I run the job manually while I'm logged on.
Log Info :- REMOVE_FROM_DATE..delete_from_orderbnk: DELETE from CONSORT_ORDERBNK WHERE DATE_ADDED > TO_DATE('2006-02-03 23:59:59','YYYY-MM-DD HH24:MI:SS')

but when I schedule the job a couple of quotes get added in that makes it fall over

Log Info :- REMOVE_FROM_DATE..delete_from_orderbnk: DELETE from CONSORT_ORDERBNK WHERE DATE_ADDED > TO_DATE('"2006-02-03 23:59:59"','YYYY-MM-DD HH24:MI:SS')

Warning :- REMOVE_FROM_DATE..delete_from_orderbnk: ORA-01841: (full) year must be between -4713 and +9999, and not be 0

How do I stop datastage adding in the quotes ?

Regards,
Ian

ArndW · Post by **ArndW** » Wed Feb 08, 2006 8:47 am

Does the paramater value for #parmDateTime# already contain the extra double-quotes or are they added in the before-stage command?

ianm · Post by **ianm** » Wed Feb 08, 2006 8:57 am

This is the Before-Stage command :-

DELETE from CONSORT_AUDITS WHERE DATE_ADDED > TO_DATE('#parmDateTime#','YYYY-MM-DD HH24:MI:SS')

I don't know where the double quotes are coming from.
As I say when I run in real time it work fine but when I schedule the job and log off, datastage adds the double quotes in.

ArndW · Post by **ArndW** » Wed Feb 08, 2006 9:05 am

When you look at your log file entries in the director you can go through them to find the one which lists your incoming parameters. You need to know where those extraneous quotes are coming from in order to get rid of them, be it in the job or in the program which sets & passes the parameter value.

ianm · Post by **ianm** » Wed Feb 08, 2006 9:23 am

This is the Log Info from when the job has been kicked off by the scheduler :-
Starting Job REMOVE_FROM_DATE.
parmSourceUserId = ds_account
parmSourcePassword = ********
parmDestinationSource = dcsr1
parmDestinationUserId = ds_account
parmDestinationPassword = ********
parmDateTime = "2006-02-03 23:59:59"

and this is the Log Info when I run the job in real time.
Starting Job REMOVE_FROM_DATE.
parmSourceUserId = ds_account
parmSourcePassword = ********
parmDestinationSource = dcsr1
parmDestinationUserId = ds_account
parmDestinationPassword = ********
parmDateTime = 2006-02-03 23:59:59

I haven't changed anything.

ArndW · Post by **ArndW** » Wed Feb 08, 2006 9:32 am

So the extra quotes are being added when you run through the scheduler... How is the scheduler getting/setting that value?

ianm · Post by **ianm** » Wed Feb 08, 2006 9:37 am

parmDateTime is a job parameter and while I'm trying to solve this problem it has a default value of 2006-02-03 23:59:59 (no quotes).

ArndW · Post by **ArndW** » Wed Feb 08, 2006 9:49 am

ian,

it has a default value without quotes, since that is the value it is using when you start your job manually. But when called through your scheduled process it is getting the extra double-quotes from somewhere; this is what you need to analyze. Most likely the double-quotes were added to the parameter values in the scheduler. If you used the DS scheduling you can view that in the Director -> View -> Schedule.

ianm · Post by **ianm** » Wed Feb 08, 2006 10:01 am

I've just added the job to the schedule then looked at the schedule details as you suggest :-

Job Schedule Detail
Project
BI_Layer(gal04019)
Job name
REMOVE_FROM_DATE
Schedule #:
433
Run time:
17:00
Occurrences:
1
Run date:
Today

Job parameters:
parmSourceUserId=ds_account
parmSourcePassword=LL9@9HVA>9;M07FIDIN<AOI7
parmDestinationSource=dcsr1
parmDestinationUserId=ds_account
parmDestinationPassword=LDI@1;VH?9:L0G7I=8N<2OI7
parmDateTime=2006-02-03 23:59:59

Still no quotes.
I've dug around but I can't find anything to do with adding quotes to parameters ?
I think I'm going to go home now !