DSXchange

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 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

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

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

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]

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.