I want to know one thing. I submit one TPT Load job. If the job fails the target table will be locked (similar to fastload).
Both are valid, but what is the better remedy?
use Standalone TPT Load job to apply and release the lock
using ddl drop and recreate the table every time.
I suppose it might depend on how much data is loaded and how much time you spent on the original job.
If the original job loaded 90% of the data, you might be better off unlocking the table and using the Update operator (MultiLoad protocol) to add the rest of the data to the table.
If you were not even halfway through the load, it might be beneficial to drop the table and start over.
Like checkpoints, it depends on how much time you are willing to lose.
Thanks for your reply. I can understand what you are saying.
But I was thinking, if I go for drop and recreate of table every time, this will take Exclusive lock across all amps. If I am in production environment, this does not seem a good option. However, as you have explained it depends on what I am willing to achieve.
Please let me know your response.
Please explain your concern about the locks on the AMPs for that table.
If you are concerned about the amount of time the locks remain on the table, then the FastLoad or MultiLoad protocol should not be used to load the table. You should use the Stream or Inserter operator.
Most customers realize that the FastLoad protocol is the quickest way to load a table and are willing to accept the locks.
(I also think that most customers just drop the table and recreate it from the beginning, but that solution may not be the best approach in every scenario for every customer.)
Do you use checkpoints?
For this table in question, how long does the loading of the table usually take?
Maybe DBA needs to cut you a staging database on production box. Load to a table there with TPT LOAD, then apply it with a MERGE to production table - a single transaction
Hi Steve and Q
Thanks for your reply.
Actually I have a scenario and I want to know the best possible solution. We have many stage tables where truncate load is happening everyday with almost 1M data through TPT LOAD jobs. Now there is possibility that any TPT LOAD job may fail due to some issue. I do not know at which point it is failing. Checkpoint is used as 1000 for all. What would be the best possible solution if any TPT Load job fails?
1. Use standalone TPT Load job, say within a shell script to release lock (like fast load empty script)
2. Using DDL of those stage tables drop and recreate every time it fails
Since the stage tables are empty so drop and recreate should not be a problem. However, I was thinking whether standalone TPT Load script is better option compared to the other?
I would like to advise you use Update operator, or Multiload to load load ready tables, and then use merge to apply data to target.
In some cases, if the target table has been locked by update operator during apply step, you can use the following command to release the table.
|release mload <Table_Name> in Apply|
If the table is locked before apply step, you can use the following command:
release mload <Table_Name>
In this way your production tables will never go into invalid state.
If you are using the Load operator, the only way to release a lock is to complete the Application Phase.
The only other option is to drop the target table (and error tables) and start the job from the beginning.
Are you using the Load operator or Update operator?