I am trying to write to a target table on Teradata with the Ent stage. Mode is truncate. The incoming link has 25 columns in it, but we are only writing 20 of them to the table. We are using an explicit select list. On DB2, that works great. I don't have to use any other options to drop the extra fields. On Teradata, the job fails unless I also specify the 'siliently drop fields not in the table" option set to true.
Has anyone else run into this? Is this a bug or 'feature'?
Brad.
Problems with select list in Teradata Ent stage on write
Moderators: chulett, rschirm, roy
Problems with select list in Teradata Ent stage on write
It is not that I am addicted to coffee, it's just that I need it to survive.
I modifed the job to do a 'replace' instead of 'truncate' and the select list is completely ignored - all fields are written and in the order of the input dataset, not even close to the select list order.
The same issue exists in v7, but we had hoped this would be resolved in v8. Are there APARs that we should be looking at? Patches?
Related link: Strange Behaviour with Teradata Load
Brad.
The same issue exists in v7, but we had hoped this would be resolved in v8. Are there APARs that we should be looking at? Patches?
Related link: Strange Behaviour with Teradata Load
Brad.
It is not that I am addicted to coffee, it's just that I need it to survive.
That is normal behavior for Teradata Enterprise. You must specify the drop option if you have input link columns that are not to be loaded into the target table.
When you use the replace option, the schema of the target table is determined by the input link columns and the schema of whatever table existed previously is irrelevant.
When you use the replace option, the schema of the target table is determined by the input link columns and the schema of whatever table existed previously is irrelevant.
Isn't that kind of like saying about your new car's rattling muffler "don't worry, it is supposed to sound like that"?
The purpose of the select list is to limit your select list. So I take it this is a bug and not a 'feature' and IBM never got around to actually fixing it.
Okay, I'll get off my soap box. It is good to know that this is a known issue. Thanks!
Brad.
The purpose of the select list is to limit your select list. So I take it this is a bug and not a 'feature' and IBM never got around to actually fixing it.
Okay, I'll get off my soap box. It is good to know that this is a known issue. Thanks!
Brad.
It is not that I am addicted to coffee, it's just that I need it to survive.
By default Teradata Enterprise assumes that you intended every input link column to get loaded into the target table. If there is a descrepancy between your input link columns and your target schema (whether you use the table option or select list), the default behavior is to abort. The whole purpose of the drop option is to allow the user to specify that they realize not all of the input columns exist in the target schema and that it is ok to ignore those. It is not a bug. That is the whole purpose of the drop option.
I'm not talking about the drop option. I am talking about the select list. The purpose of the select list is to explicitly list the fields to apply to your target table, regardless of mode (append, truncate, replace, etc.), and to explicitly list the field order. The field order in your input stream may not match your target table's field order.
Brad.
Brad.
It is not that I am addicted to coffee, it's just that I need it to survive.
The select list specifies what columns to load in the target table, not what columns to use from the input link. If you use a select list to specify a subset of target columns, you'd need to have defined the other columns with a DEFAULT clause.
Specifying a select list does not imply the drop option. You still must specify the drop option if you are aware that your input link contains columns that are not in the target select list and can be ignored.
Specifying a select list does not imply the drop option. You still must specify the drop option if you are aware that your input link contains columns that are not in the target select list and can be ignored.
Put it this way. That is how it works in other database stages. It obviously behaves differently in Teradata.
Also, I am not talking about using the select list to insert fewer columns than exist in the target table (inserting 5 columns into a table that has 10 columns). Rather, it is the other way around.
In the DB2 Ent Stage, I can have an input stream of 100 fields and a target table with 25 columns. I can use a select list to specify only the 25 required columns and never touch the drop option. We do this all the time. In fact, that is our standard here - always explicitly list the columns in the select list and do not use the drop option. We want 100% control over which columns are used in an insert or load. No silently dropping fields.
This was our expectation in the Teradata Ent Stage, considering DataStage at least attempts to make things work similarly between RDBMS's. At a minimum, it is an inconsistency in the tool. You use whatever terms you want, I personally would consider it a bug.
Brad.
Also, I am not talking about using the select list to insert fewer columns than exist in the target table (inserting 5 columns into a table that has 10 columns). Rather, it is the other way around.
In the DB2 Ent Stage, I can have an input stream of 100 fields and a target table with 25 columns. I can use a select list to specify only the 25 required columns and never touch the drop option. We do this all the time. In fact, that is our standard here - always explicitly list the columns in the select list and do not use the drop option. We want 100% control over which columns are used in an insert or load. No silently dropping fields.
This was our expectation in the Teradata Ent Stage, considering DataStage at least attempts to make things work similarly between RDBMS's. At a minimum, it is an inconsistency in the tool. You use whatever terms you want, I personally would consider it a bug.
Brad.
It is not that I am addicted to coffee, it's just that I need it to survive.