Netezza - Replace table - Order of Columns not preserved

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
Ranjana
Premium Member
Premium Member
Posts: 6
Joined: Fri Oct 13, 2006 8:31 am
Location: Chicago

Netezza - Replace table - Order of Columns not preserved

Post by Ranjana »

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: Select all

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
Premium Member
Premium Member
Posts: 46
Joined: Mon Dec 24, 2007 2:16 pm

Post by sharma »

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
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
Ranjana
Premium Member
Premium Member
Posts: 6
Joined: Fri Oct 13, 2006 8:31 am
Location: Chicago

Re: Netezza - Replace table - Order of Columns not preserve

Post by Ranjana »

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]
Ranjana
Premium Member
Premium Member
Posts: 6
Joined: Fri Oct 13, 2006 8:31 am
Location: Chicago

Re: Netezza - Replace table - Order of Columns not preserve

Post by Ranjana »

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.
Post Reply