We are getting same error.
We are using MLOAD on a table which is PARTITIONED on column ORDER_TYPE and NUPI on another column ROW_WID.
Here we are using MLOAD UPSERT where it is trying to update partition column also.
While having a talk with teradata support they recommended not to use MLOAD on partitioned table s it doesnt support so.
Please find the loader below.
Well, you answered your own question:
"While having a talk with teradata support they recommended not to use MLOAD on partitioned table s it doesnt support so."
You cannot use MultiLoad on a column partitioned table.
If you do not want to use TPump then you probably have to resort to loading into a (non-column-partitioned) staging table, and then performing an INSERT-SELECT (or MERGE-INTO) into the target table.
We also thought of the same alternative but it was not performence gainer.
So finally we confirmed the business logic behind that table and decided to go with UPI on composite column including partitioned column.
We are facing the same problem. We have addedd a PPI on a column in a table and trying to update using mload. We get RDBMS failure, 3538: A MultiLoad UPDATE Statement is Invalid.
Looking are the discussion above, i have few questions :
1. Does the mload doesn't support any table upadte having PPI ?, even when you are not updating the PPI column involed. We have a PI and PPI is not part of that PI.
2. Ashish : When you made it part of PI i.e making it UPI, then did the mload work ?
Please refer to the MultiLoad Reference Manual.
There are a lot of sections that talk about what is supported and what is not supported in MultiLoad.
For an example, here is an exerpt:
PPI Table Considerations
Load utilities, including Teradata MultiLoad, are supported on PPI tables with the following
restrictions and advisories:
• Teradata MultiLoad does not support tables with Unique Secondary Indexes (USI). Many
PPI tables have USIs.
• Teradata MultiLoad IMPORT tasks require all values of the primary index column set and
all values of the partitioning column set for deletes and updates.
• Teradata MultiLoad IMPORT tasks do not support updates of the partitioning column set.
• Teradata MultiLoad IMPORT tasks do not support primary index updates.
• Teradata MultiLoad does not support hash and join indexes.