Modify Stage - System Date to Timestamp conversion??

catmans1 · Post by **catmans1** » Wed Nov 08, 2006 1:48 pm

Are we able to create a new column in the modify stage without a source from the input side?

I'm trying to put a load timestamp into my target table (UDB/DB2) in which I just want the system date and time to be loaded into this new field.

I've tried the following

Specification =
DW_LOAD_TIME = timestamp_from_date(tdatetime)
(new column)

Warning Message I receive = Modify_48: When checking operator: The modify operator has a binding for the non-existent output field "tdatetime".

(The data loads for the other fields but the DW_LOAD_TIME field is blank)

I'm not sure if tdatetime even returns the system timestamp but I've read it somewhere so I thought I'd try.

Any help with my problem for Modify stage to this usually simple task in Server version would be appreciated.

Thank you,
Clarence

ray.wurlod · Post by **ray.wurlod** » Wed Nov 08, 2006 4:10 pm

At a quick glance that looks OK, provided that there is a field called tdatetime in the input stream. Are the fields on both sides nullable?

How and where is tdatetime derived?

catmans1 · Post by **catmans1** » Wed Nov 08, 2006 4:29 pm

Ray,

That's where I'm confused I guess. Does the Specification parameter in the Modify Stage require a mapping of a Input to a output?

I don't have an input date for my output column, I want to load the Current Timestamp to the output column......can this be done in the Modify Stage?

A truly EE newbie question but I can't find any info in the Online Manuals on this.

Thanks for any input.

Clarence

ray.wurlod · Post by **ray.wurlod** » Wed Nov 08, 2006 4:34 pm

The Parallel Job Developer's Guide chapter on Modify stage gives full information about constructing specifications, though it has some of the functions incorrect (for example NullToValue should be handle_null).

Ask your support provider to obtain the Orchestrate Operators manual (and the other Orchestrate manuals) for a more definitive discussion.

Essentially the specification, in full, is

Code: Select all

outputfield:nullable datatype=function[options](inputfield)

If you're not changing nullability, omit nullable. If you're not changing data type, omit the datatype.

trobinson · Post by **trobinson** » Thu Nov 09, 2006 6:30 am

My two cents is that the only way to get the CurrentTimeStamp, for a given row being processed, is via the Transformer stage. It can't be done with the Modify stage or any other standard (non-custom, generic) EE stage. I'm throwing this bold assertion out there in the hopes of it being disproved.

ray.wurlod · Post by **ray.wurlod** » Thu Nov 09, 2006 1:05 pm

I agree. To assemble a timestamp from date and time is not possible in Modify, whose functions accept only one argument. Nor can any function in the Modify stage generate new data (other than handle_null).

my_stm · Post by **my_stm** » Wed Apr 18, 2007 11:05 pm

May i know that Orchestrate Operators manual that you all mentioned, is it the link as this below?

http://www.ascentialsoftware.com/servic ... vGuide.pdf

I asked my support provider and they gave me with this .pdf file. Is it this is the one?

Thank you

ray.wurlod · Post by **ray.wurlod** » Wed Apr 18, 2007 11:37 pm

That is the one if you're on version 7.0. There are more recent Orchestrate manuals for version 7.5. I guess you could try substituting orch75 for orch70 in the pathname, but no guarantees. Hassle your support provider some more.