DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
This topic has been marked "Resolved."
Author Message
Ranjana



Group memberships:
Premium Members

Joined: 13 Oct 2006
Posts: 6
Location: Chicago
Points: 63

Post Posted: Thu Jun 25, 2009 10:27 am Reply with quote    Back to top    

DataStage® Release: 8x
Job Type: Parallel
OS: Windows
Hello,

I am using the Netezza Enterprise Stage to load data into a Netezza table. I am reading data using an ODBC Enterprise Stage. This is how my design looks like
Code:

ODBC Enterprise Stage => Transformer => Netezza Enterprise Stage


The order of fields in my input data is as follows
field1
field2
field3
field4

In my transformer I am doing a NullToEmpty on field3 and I am preserving the order of my fields on the output Netezza stage i.e.

field1
field2
field3
field4

My Netezza stage has the following options :
Load Method = nzload and Write Mode = Replace

Since Replace has to drop and recreate my table each time, I expect it to recreate the table with fields in the order I specified in my Transformer i.e.

field1
field2
field3
field4

But it does not do that. In fact it re-orders the fields to have the field with the derivation on top

field3
field1
field2
field4

Has anyone come across this issue? I have seen this with only with the Netezza stage. This happens only when I do some sort of derivation in the Transformer. The field with the derivation is pushed to the top regardless of the order that I specify. This does not happen with any other stage like Filter. The transformer with the derivation is throwing the order of.

Thanks in advance,
Ranjana
sharma



Group memberships:
Premium Members

Joined: 24 Dec 2007
Posts: 46

Points: 428

Post Posted: Thu Jun 25, 2009 12:21 pm Reply with quote    Back to top    

Hi,

When you use 'Replace' as the write mode in Netezza Stage, then it only takes the column information defined in the Netezza stage and simply create the table in Netezza without bothering much about the sequence of the column.

So it happens in Netezza Stage.

I guess, to retain the column sequence, you first create the table in Netezza and use "Truncate" or "Create" depending on your requirements in write mode in Netezza Stage.

This will resolve your problem.

Regards
~Nirmal

_________________
Nirmal Sharma
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 39137
Location: Denver, CO
Points: 199845

Post Posted: Thu Jun 25, 2009 1:10 pm Reply with quote    Back to top    

Have no way to check this myself, but... any chance that field3 is marked as a Key field?

_________________
-craig

If you try and take a cat apart to see how it works, the first thing you have on your hands is a non-working cat. -- Douglas Adams
Rate this response:  
Not yet rated
Ranjana



Group memberships:
Premium Members

Joined: 13 Oct 2006
Posts: 6
Location: Chicago
Points: 63

Post Posted: Thu Jun 25, 2009 2:04 pm Reply with quote    Back to top    

Thanks a lot for the replies

Craig,

Field3 is not marked as a key field.

Nirmal,

Does that mean that whatever order I have in my column specs in the Netezza stage, If I use the Replace option, the column that has a derivation will come on top when creating the table.

Ranjana.[/code]
Rate this response:  
Not yet rated
Ranjana



Group memberships:
Premium Members

Joined: 13 Oct 2006
Posts: 6
Location: Chicago
Points: 63

Post Posted: Thu Jun 25, 2009 4:20 pm Reply with quote    Back to top    

Looks like the only way I can use the Replace option , have a derivation in my transformer and still keep the order is if I specify a Create statement in the Netezza stage.

Thanks Nirmal for guiding me in the right direction.

Ranjana.
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours