Perserving column order to target Teradata Enterprise Stage

[BradJoss]

Hi,

Firstly, I am realtively new to DataStage and have found a wealth of information on this site, so Thanks to everyone that I've gleened information from.

Now for my question: I'm using the Teradata Enterprise stage as a target stage to write my output to a table, but the column order is not perserved as what I've indicated in my metadata. Is there some option that I'm missing?

The Write Mode I'm using is replace. I've also used Create with the same results. The only option I can think of is to define a OPEN COMMAND and write the DROP and CREATE TABLE SQL to explicitly define the column order I want. I'm hoping there is a simpler solution.

Thanks in advance!!

[chulett]

Interesting... we just recently had the same problem crop up with Neteeza, I wonder if this information helps at all:

viewtopic.php?t=127940

[BradJoss]

That's exactly what's happening. And looking at the output more closely this is what I'm seeing... (First a little background)

I've got 2 sources 1 UDB and 1 Teradata which I'm joining together with a Join Stage. I'm using a Transformer inbetween the UDB and the Join to conform the columns. In the Transformer I'm setting a derivation on 2 columns. I'm then grabbing supporting data from another Teradata source and joining this to the result which I'm then writing out to Teradata again.

Now here's the funny part. The order in which the create statement is being generated is the two columns in which I performed the derivation followed by the columns (in order) in which I extracted the source from the UDB database followed by the columns (again in order) from the first Teradata source then lastly then 2nd Teradata source.

I've disabled the perserve sort order in the transformer and I've also trid running the job by placing a hash on the partition of the table key and that still didn't correct the issue.

I guess I still have to go with the OPEN COMMAND solution.

[chulett]

Seems like it. Odd that they work that way, even if there are being consistent. Would be interesting to ping IBM or your official support provider for an explanation, see what (if any) reasoning is behind that decision.

[BradJoss]

Update: OPEN COMMAND solution didn't work

I tried explicitly writing out the DROP and CREATE TABLE commands, which you would need to ensure your Write Mode is set to either Append or Truncate. The trouble comes when DataStage performs a pre-check on the existance of the target table, which is a condition when setting these two write mode types, and doesn't find an existing table. The whole job aborts before even running.

I'm still waiting to hear back from our IBM rep so if anyone has any other ideas I'd love to hear them!

Thanks,

[miwinter]

Hi Brad,

Just to tie in with this... check my latest findings out as well. I think we can safely say it's something transformer-related somewhere along the line.

viewtopic.php?t=129054

[BradJoss]

Hi Brad,

Just to tie in with this... check my latest findings out as well. I think we can safely say it's something transformer-related somewhere along the line.

viewtopic.php?t=129054

Hi Miwinter,

Yes, after placing a PMR with IBM they came back and noted the order issue is Transformer related. It is a known "Bug" and there is no ETA on a fix. The work arounds that we've done is to break up the DataStage jobs to write to an intermediate Dataset then in a subsequent job extract the data mapping to the order you want.... For my particular issue as long as the Target table is already defined and only an append or update action is taken then the columns mapped ok. The trouble occurred when we created a new table or did a replace table. Then the columns were placed in order of derivation first then other data sources in the order they were originally extracted.

Appologies for not posting this update sooner.

Brad

[miwinter]

Brad,

No worries and thanks for the update - always nice to tie similar findings into one. I noted on your post you appear to be running v8. I've seen this issue in build v7.5.1A so it seems this hangover has continued on into v8 too . I'm amazed a prior PMR to IBM (I'd assume it's likely one would have been raised before now) hasn't caused this to be addressed in v8 onwards.

[miwinter]

Brad,

Just one more thing... (as Columbo said to the prime suspect) could you post up your PMR ID please? It would be nice in submitting my own (which I'm collating now) to cross-reference your existing one.

Thanks in advance