DSXchange

I know that I can set up a transform stage variable and I know that the job has prompts, but I need a global Variable that I can use in multiple transfrom stages. It dosent need to be a prompt, and prompts dont apear to be able to handle what I need I need a date/time stamp, and prompts seem to only handle date or time and dont like the function timedate(). How do I create a global variable?
Jim

Just create a job parameter (what you're calling a prompt) with a datatype of STRING.

kcbland wrote:Just create a job parameter (what you're calling a prompt) with a datatype of STRING.

I knew there had to be an easy way to do that. Thanx
Jim

JDionne wrote:

kcbland wrote:Just create a job parameter (what you're calling a prompt) with a datatype of STRING.

I knew there had to be an easy way to do that. Thanx
Jim

The string command seems to handle the command timedate() as a string.
its not returning the actual date.
how do I handle that?
Jim

Hi,
I think there are systemvariables available in transformer level that might give you what you need and even be consistant thruout transformer in the same job, just look in any derivation (right click and click on systemvariable).

JDionne wrote:
The string command seems to handle the command timedate() as a string.
its not returning the actual date.
how do I handle that?
Jim

How are you using the job parameter and where? Derive its value and set the parameter at runtime. Since it's STRING, you can put anything into it.

If you're attempting to do date arithmetic, then you have to use the appropriate functions to do the work.

kcbland wrote:

JDionne wrote:
The string command seems to handle the command timedate() as a string.
its not returning the actual date.
how do I handle that?
Jim

How are you using the job parameter and where? Derive its value and set the parameter at runtime. Since it's STRING, you can put anything into it.

If you're attempting to do date arithmetic, then you have to use the appropriate functions to do the work.

I have successfully in the past set up a stage variable with the dirivation of timedate() and used that as the load date. I didnt want to create that in every transformation. I dont want to set the varaible at run time i want it to be dynamic.
hope that helps explane my trouble.
Jim

If your intent is to derive a value in the first transformer of a chain of transformers:
then you can setup a stage variable in xfm1 to derive anything you want and add an extra column to the output link with a derivation of that stage variable (or whatever. That column should travel the length of the transformers. It's "visible" because it's part of the data stream.

If your value is static, but derived once, you're better off using a job parameter as it's a fixed value. The alternative is to derive it once in a stage variable in xfm1 and then leave it alone, but propagate it down the chain of transformers as I suggested.


I'm not really understanding what you're trying to do. You mention setting a load date value. Are you attempting to stamp all of the rows with a timestamp, like a last_update_dt column? Do you want each row stamped with the instantenous clock time or do you want all rows stamped with the initial start time of the job so that all rows have the exact same value? A clock call is a wait of time and cpu cycles, as it is not the same as the time the row is loaded into the database (this can be minutes of discrepancy depending on how long between derivation and commit). If you want all rows stamped with the same processing timestamp, your best bet is to derive once (one call to the clock) and stamp all.

If you have to stamp in multiple output links, then the propagation technique I described is simple enough. The complexity of setting up custom global variables (labeled COMMON) to which all transformers will have visibility has limitations on inter-process and buffering. I don't think you want that.

Jim, if you wrap your server job inside a sequence job you should find the setting of job parameters easier. In the job stage you can write some BASIC code to set the timestamp in the Parameters Value Expression field of the job stage. You've got more flexibility setting a parameter here than you have in the run job screen.

You can also set a parameter in a routine and call this routine from the sequence job, it can then be passed to server jobs as a parameter.

vmcburney wrote:Jim, if you wrap your server job inside a sequence job you should find the setting of job parameters easier. In the job stage you can write some BASIC code to set the timestamp in the Parameters Value Expression field of the job stage. You've got more flexibility setting a parameter here than you have in the run job screen.

You can also set a parameter in a routine and call this routine from the sequence job, it can then be passed to server jobs as a parameter.

....Im gona have to open the training manual to figure that one out. Ill give it a go.
Jim

Search the forum over the manual. We've been discussing job control for years, there's lots of samples.

kcbland wrote:Search the forum over the manual. We've been discussing job control for years, there's lots of samples.

Thanx Ill do it
Jim