Write Default Row to Dimension Table

[admin]

I am using DataStage 5.2 on Win2000

I need to write a default row to a hash table before I load in the operational data. The target has two columns - Code and Description.
I selected a transform stage and a hash stage as output. I put the constants into the derivation areas in the transform stage. Code =9 and Description=UNKNOWN.
Everything compiles great, but when executed the job just keeps running.

Why doesn't it just put in one row?

What is the easiest way to put one default row into a target hash or target Oracle table?

Daniel A. Sutton Associate Technical Fellow, Boeing Navigator
Hierarchical Databases, Data Warehouse, and Migration Control Group

To be in a dialogue, we must listen from where the other person speaks.
Otherwise, we only have two monologues

Boeing Philadelphia IS/Data Management
610-591-7124 610-591-7524(fax) 800-946-4646 pwd 1499533(beeper)

email: daniel.a.sutton@boeing.com

Regular Mail Address:
Boeing
PO Box 16858 MS-P29-29
Philadelphia PA 19142-0858

[admin]

Well seeing as you mention that you are using Oracle, you could always use
an OCI input stage that reads from DUAL. That would give you one row. I do
this quite often.


-----Original Message-----
From: Sutton, Daniel A [mailto:daniel.a.sutton@boeing.com]
Sent: Thursday, 13 November 2003 6:18 AM
To: datastage-users@oliver.com
Subject: Write Default Row to Dimension Table

I am using DataStage 5.2 on Win2000

I need to write a default row to a hash table before I load in the
operational data. The target has two columns - Code and Description.
I selected a transform stage and a hash stage as output. I put the
constants into the derivation areas in the transform stage. Code =9 and
Description=UNKNOWN.
Everything compiles great, but when executed the job just keeps running.

Why doesn't it just put in one row?

What is the easiest way to put one default row into a target hash or target
Oracle table?

Daniel A. Sutton Associate Technical Fellow, Boeing Navigator
Hierarchical Databases, Data Warehouse, and Migration Control Group

To be in a dialogue, we must listen from where the other person speaks.
Otherwise, we only have two monologues

Boeing Philadelphia IS/Data Management
610-591-7124 610-591-7524(fax) 800-946-4646 pwd 1499533(beeper)

email: daniel.a.sutton@boeing.com

Regular Mail Address:
Boeing
PO Box 16858 MS-P29-29
Philadelphia PA 19142-0858

[admin]

You must ALWAYS process data from a passive stage.
This means a data source such as a file, table, or
virtual file such as an FTP session or pipe. You do
not have to have any output, but that seems kind of
silly.

So, to do what you desire, I would first recommend
creating an operational table in your warehouse to
contain your default row. Another alternative would
be to create a file that contains the data. Your
effort is simple at that point to just read/select the
data and transform and load.

-Ken

--- "Sutton, Daniel A"
wrote:
> I am using DataStage 5.2 on Win2000
>
> I need to write a default row to a hash table before
> I load in the operational data. The target has two
> columns - Code and Description.
> I selected a transform stage and a hash stage as
> output. I put the constants into the derivation
> areas in the transform stage. Code =9 and
> Description=UNKNOWN.
> Everything compiles great, but when executed the job
> just keeps running.
>
> Why doesn't it just put in one row?
>
> What is the easiest way to put one default row into
> a target hash or target Oracle table?
>
> Daniel A. Sutton Associate Technical Fellow, Boeing
> Navigator
> Hierarchical Databases, Data Warehouse, and
> Migration Control Group
>
> To be in a dialogue, we must listen from where the
> other person speaks.
> Otherwise, we only have two monologues
>
> Boeing Philadelphia IS/Data Management
> 610-591-7124 610-591-7524(fax) 800-946-4646
> pwd 1499533(beeper)
>
> email: daniel.a.sutton@boeing.com
>
> Regular Mail Address:
> Boeing
> PO Box 16858 MS-P29-29
> Philadelphia PA 19142-0858
>
>
>


__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree

[admin]

Create a constraint "@OUTROWNUM

[admin]

That won't help if there's no input!

To create a one line file, perhaps containing the data you want, in a text file that you can process with a SequentialFile stage, use ExecSH as a before-stage or before-job routine and put the command
echo 9,"UNKNOWN" > #filename#
into the Input Value field. Then use the same job parameter (#filename#) in your Sequential File stage.

----- Original Message -----
From: "Craig Rouse"
Date: Wed, 12 Nov 2003 20:40:50 -0700
To:
Subject: RE: Write Default Row to Dimension Table

> Create a constraint "@OUTROWNUM
> -Craig Rouse
> Griffin Resources
> griffin.resources@att.net
>
> -----Original Message-----
> From: Sutton, Daniel A [mailto:daniel.a.sutton@boeing.com]
> Sent: Wednesday, November 12, 2003 1:18 PM
> To: datastage-users@oliver.com
> Subject: Write Default Row to Dimension Table
>
>
> I am using DataStage 5.2 on Win2000
>
> I need to write a default row to a hash table before I load in the
> operational data. The target has two columns - Code and Description. I
> selected a transform stage and a hash stage as output. I put the constants
> into the derivation areas in the transform stage. Code =9 and
> Description=UNKNOWN. Everything compiles great, but when executed the job
> just keeps running.
>
> Why doesn't it just put in one row?
>
> What is the easiest way to put one default row into a target hash or target
> Oracle table?
>
> Daniel A. Sutton Associate Technical Fellow, Boeing Navigator Hierarchical
> Databases, Data Warehouse, and Migration Control Group
>
> To be in a dialogue, we must listen from where the other person speaks.
> Otherwise, we only have two monologues
>
> Boeing Philadelphia IS/Data Management
> 610-591-7124 610-591-7524(fax) 800-946-4646 pwd 1499533(beeper)
>
> email: daniel.a.sutton@boeing.com
>
> Regular Mail Address:
> Boeing
> PO Box 16858 MS-P29-29
> Philadelphia PA 19142-0858
>
>
>

[admin]

It sounds like he already has it kicking out rows, so the assumption is he
has already created at least one stage variable in the transform stage.

You don't need an input. You can have just a Transform Stage to an output
stage like Seq, Hash, OCI, whatever. Then, you only need to define a stage
variable (don't even need a derivation for the stage variable). Without a
constraint, it will write records infinitely. It you want to write one row,
as he needs to, set the constraint as described.



-----Original Message-----
From: Ray Wurlod [mailto:rayw@mindless.com]
Sent: Wednesday, November 12, 2003 9:57 PM
To: datastage-users@oliver.com
Subject: RE: Write Default Row to Dimension Table


That won't help if there's no input!

To create a one line file, perhaps containing the data you want, in a text
file that you can process with a SequentialFile stage, use ExecSH as a
before-stage or before-job routine and put the command echo 9,"UNKNOWN" >
#filename# into the Input Value field. Then use the same job parameter
(#filename#) in your Sequential File stage.

----- Original Message -----
From: "Craig Rouse"
Date: Wed, 12 Nov 2003 20:40:50 -0700
To:
Subject: RE: Write Default Row to Dimension Table

> Create a constraint "@OUTROWNUM file.
>
> -Craig Rouse
> Griffin Resources
> griffin.resources@att.net
>
> -----Original Message-----
> From: Sutton, Daniel A [mailto:daniel.a.sutton@boeing.com]
> Sent: Wednesday, November 12, 2003 1:18 PM
> To: datastage-users@oliver.com
> Subject: Write Default Row to Dimension Table
>
>
> I am using DataStage 5.2 on Win2000
>
> I need to write a default row to a hash table before I load in the
> operational data. The target has two columns - Code and Description.
> I selected a transform stage and a hash stage as output. I put the
> constants into the derivation areas in the transform stage. Code =9
> and Description=UNKNOWN. Everything compiles great, but when executed
> the job just keeps running.
>
> Why doesn't it just put in one row?
>
> What is the easiest way to put one default row into a target hash or
> target Oracle table?
>
> Daniel A. Sutton Associate Technical Fellow, Boeing Navigator
> Hierarchical Databases, Data Warehouse, and Migration Control Group
>
> To be in a dialogue, we must listen from where the other person
> speaks. Otherwise, we only have two monologues
>
> Boeing Philadelphia IS/Data Management
> 610-591-7124 610-591-7524(fax) 800-946-4646 pwd 1499533(beeper)
>
> email: daniel.a.sutton@boeing.com
>
> Regular Mail Address:
> Boeing
> PO Box 16858 MS-P29-29
> Philadelphia PA 19142-0858
>
>
>

[admin]

I apologize if people are reading my response on DSXchange.com...
The description of the output link constraint was clipped for some reason in
DSXchange.

In pseudo-code it would read "@OUTROWNUM less than 2"
The "less than" sign and the 2 were clipped.

BTW, it is a not-so-well-known fact that you can create a DS job without an
input stage.
All you need is a Transformer stage at the beginning of your job that has at
least one stage variable declared (but no need to assign a value). And of
course, you need an output link from that Transformer.

Using DS this way is a very intriguing method of "creating new data". It
would be very useful in creating test data. Create several hash files of
first names, last names, street addresses, towns, states, etc and keyed by a
sequential number, say from 1 to 1000. Then, use DS random routines
(randomize or rnd) to create a lookup key and you can start creating unique
names and addresses and such to load databases with... You could do the same
with Order numbers, item numbers, product ids and the like.

-Craig

-----Original Message-----
From: Kenneth Bland [mailto:kcbland_2000@yahoo.com]
Sent: Wednesday, November 12, 2003 8:18 PM
To: datastage-users@oliver.com
Subject: Re: Write Default Row to Dimension Table


You must ALWAYS process data from a passive stage.
This means a data source such as a file, table, or
virtual file such as an FTP session or pipe. You do
not have to have any output, but that seems kind of
silly.

So, to do what you desire, I would first recommend
creating an operational table in your warehouse to
contain your default row. Another alternative would
be to create a file that contains the data. Your
effort is simple at that point to just read/select the
data and transform and load.

-Ken

--- "Sutton, Daniel A"
wrote:
> I am using DataStage 5.2 on Win2000
>
> I need to write a default row to a hash table before
> I load in the operational data. The target has two
> columns - Code and Description.
> I selected a transform stage and a hash stage as
> output. I put the constants into the derivation
> areas in the transform stage. Code =9 and Description=UNKNOWN.
> Everything compiles great, but when executed the job
> just keeps running.
>
> Why doesn't it just put in one row?
>
> What is the easiest way to put one default row into
> a target hash or target Oracle table?
>
> Daniel A. Sutton Associate Technical Fellow, Boeing Navigator
> Hierarchical Databases, Data Warehouse, and
> Migration Control Group
>
> To be in a dialogue, we must listen from where the
> other person speaks.
> Otherwise, we only have two monologues
>
> Boeing Philadelphia IS/Data Management
> 610-591-7124 610-591-7524(fax) 800-946-4646
> pwd 1499533(beeper)
>
> email: daniel.a.sutton@boeing.com
>
> Regular Mail Address:
> Boeing
> PO Box 16858 MS-P29-29
> Philadelphia PA 19142-0858
>
>
>


__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree