DSXchange

Does anyone else have an issue where the metadata for the output table that is to be created by a DS job has the columns in a particular order, yet the table created by running the job always mixes up the order of the columns?

Not a huge drama, but annoying if I have 100 columns in a table organised logically for ease of viewing and then the job just pulls the order out of a hat...

Is this just something I have to live with, or is there a solution to this?

What stage? Using the OCI stage on the Server side, the order always matches the column order if the stage is generating the DDL, but it sounds like that may not be the case on the PX side.

It's the Oracle Enterprise stage in a parallel job.

I have a job where I might have some Transformer stages and I've specifically ordered the output columns. The destination Oracle Enterprise stage shows the columns ordered correctly, but when I run it for the first time and it creates the table, the columns are in an apparently random order.

@Craig

It should not be the case with PX jobs too.

I am creating the tables with oracle enterprise stage and the colums are created perfectly ordered.
It might be exceptional case but I am getting ordered columns.

@stuartjvnorton

Check RCP settings and try to run a test job without RCP to verify the column order.

When you check the DDL in the OE stage, the CREATE TABLE statement it will use, does it show them in the 'proper' order there? How are you checking the actual table itself? I ask because tools like Toad can show you the columns in different orders, so just want to make sure it's not a 'viewer' issue.

Actually I have found the same issue.
I have noticed that the columns seem to be determined by the preceding transform stage. Those columns which have some sort of derivation logic seem to be created first and in the order that they would appear in the transform. Those columns which are just 'passing through' or have no transformation attached to them are tacked onto the end.

It can be an issue sometime if you write update statements based on position or extract a table using 'select * from ...' but how often would do that anyway.

I eye-balled the same when DDL-ing a Teradata table in EE. I had the newbie hope my first column would be the P.I. if none was explicit-ized.
By bad as the stage Hal2000'd a column of it's own choosing causing major skew and hot ampin' like a Moe foe.
I'll circle the wagons to see if the derivations were the smoking gun that reordered the columns.
thx for the 411.

PI, HAL, MoFo and 411 all in the same post, not to mention 'eye-balled', 'smoking gun', 'hot ampin' and 'circle the wagons'. This amused me.

(HAL 9000, btw, unless you meant a much earlier model)

throbinson wrote:I eye-balled the same when DDL-ing a Teradata table in EE. I had the newbie hope my first column would be the P.I. if none was explicit-ized.
By bad as the stage Hal2000'd a column of it's own choosing causing major skew and hot ampin' like a Moe foe.
I'll circle the wagons to see if the derivations were the smoking gun that reordered the columns.
thx for the 411.

Word, dog.

priyadarshikunal wrote: @stuartjvnorton

Check RCP settings and try to run a test job without RCP to verify the column order.

Sorry priyadarshikunal, I'm not sure what RCP stands for. ?

@chulett:

I've looked in Toad and Oracle SQL Developer, and the metadata for the table has the columns in the same order as I view the data and the same order as specified in the CREATE TABLE call in Oracle (watched it in the session viewer in Toad).
It seems like the columns are grouped. The fields from the source table are in 1 group and the others I have added (I'm doing an audit table with befores and afters), and the fields I have added are in a random order, followed by the fields from the original source in their correct order.

"RCP" is Runtime Column Propagation where columns not specifically mentioned are magically added at runtime and passed through the job. I assume that is not the case here and that you have specified a distinct set of columns to work with.

So, the table matches the DDL used, it just seems a little mysterious as to the column order it picked... or can you see the method of its madness now? Perhaps opening a ticket with your official support provider can get us an 'official' explanation of how this works.

Bottom line, though, column order doesn't make any difference in how the table 'performs' or functions, it is strictly an aesthetic / cosmetic consideration for the wetware side of the equation. For whatever that is worth.

With Seinfeld and the swirl as with Teradata Enterprise Stage and the PI; the order can make a significant difference.

If you mean the choice of the PI column, then yes, of course. What I mean is when the proper column is picked, it doesn't matter if that column is column number 1 or number 42, it doesn't affect how it functions. All this under the umbrella of Oracle, of course, which is the subject of this post.

Are you saying that the actual column number / order in Teradata can be an issue?

This is the last high-jacking of this post, I promise. That is exactly what I am saying. When creating a teradata table in EE, if a PI is not specified, the first column becomes the PI.
Primary Index Property Help;

Comma-separated list of field names that will become the primary index for tables, if not the first column.

Meaning the first column could be selected through some weird EE internal logic and cause trouble.

Ah... thanks for that.

We now return you to your reguarly scheduled problem...