Perserving column order to target Teradata Enterprise Stage
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 10
- Joined: Thu Jun 04, 2009 9:37 am
- Location: Winnipeg, Manitoba, Canada
Perserving column order to target Teradata Enterprise Stage
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!!
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!!
Interesting... we just recently had the same problem crop up with Neteeza, I wonder if this information helps at all:
viewtopic.php?t=127940
viewtopic.php?t=127940
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 10
- Joined: Thu Jun 04, 2009 9:37 am
- Location: Winnipeg, Manitoba, Canada
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.
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.
-
- Premium Member
- Posts: 10
- Joined: Thu Jun 04, 2009 9:37 am
- Location: Winnipeg, Manitoba, Canada
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,
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,
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
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
Mark Winter
<i>Nothing appeases a troubled mind more than <b>good</b> music</i>
<i>Nothing appeases a troubled mind more than <b>good</b> music</i>
-
- Premium Member
- Posts: 10
- Joined: Thu Jun 04, 2009 9:37 am
- Location: Winnipeg, Manitoba, Canada
Tranformer is the issue
Hi Miwinter,miwinter wrote: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
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
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.
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.
Mark Winter
<i>Nothing appeases a troubled mind more than <b>good</b> music</i>
<i>Nothing appeases a troubled mind more than <b>good</b> music</i>
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
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
Mark Winter
<i>Nothing appeases a troubled mind more than <b>good</b> music</i>
<i>Nothing appeases a troubled mind more than <b>good</b> music</i>