While using utilities like Multiload, when any job ends abnormally, Teradata still holds locks and we get error 'Table is being mLoaded'.
While in case of FastLoad/TPT end, we get error 'Table is being loaded'. Even after dropping error/log/work tables, we still get same error. What I do now is just drop these tables, and recreate them.
Possible solution(read on forum) is to run same process again with empty file. So utility puts lock, and releases after successful completion.
Instead of that, can we use .FILLER command in fastload script so process will run without empty file, but with all empty columns and will release lock later once finished successfully?FILLER will exclude columns from loading and it should release locks.
The filler command is used to when you do not want to load a particular field but why do you want to use the columns and complicate the script. Theoretically speaking it might be possible but a much simpler approach would be to build another script as:
begin loading databasename.targetname errorfiles dbname.err_table1,dbname.errtable2
Note that the error tables in your original script and this one should be the same. for MLOAD failiure depending on the phase it failed, you can use the statements:
release mload on dbname.tablename; or release mload on dbname.tablename in apply;
Thanks Krishna for your thoughts. But i do know about releasing mload locks.
What my question was regarding FLOAD locks, and TPT locks if these job end abnormally.
In such cases, I can not release locks by 'release lock dbname.tablenm' or even with 'in apply'. Whether i do anything, I still get an error 'Table is being loaded'. (Table is being loaded and not mloaded). I can release mload locks. Thats not a problem at all.
The only two options available (read on forum) are
And i think,if you dont have empty file, you can skip all columns to make an empty file.
I hope this clarifies my problem.
Yes, there are 2 choices.
But for #2, you do not need to try to load an empty file.
You can do the equivalent of FastLoad's:
begin loading ....;
end loading ....;
By doing the following:
TO OPERATOR (LOAD_OPERATOR_NAME);
By not supplying any DML statements, we will interpret that as a standalone type of operation and it will force the Load operator into performing the BEGIN LOADING and END LOADING without trying to go into the acquisition phase and loading data.
This will unlock the table, but the table will be in a state where you will not be able to use the Load operator (FastLoad protocol) to load any more data to that table (it will be a populated table).