All Forums Tools
Dominiq 11 posts Joined 12/13
25 Mar 2014
Fastload Utility Locks due to abort- .FILLER command- possible solution?

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.

Swapnil
krishaneesh 140 posts Joined 04/13
25 Mar 2014

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:
.logon TDPID/username,password;
begin loading databasename.targetname errorfiles dbname.err_table1,dbname.errtable2
endloading;
.logoff;
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;

Dominiq 11 posts Joined 12/13
31 Mar 2014

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 

  1. i recreate table or
  2. i run same process again which was failed, but only with an empty file.without any data.

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.

Swapnil

feinholz 1234 posts Joined 05/08
31 Mar 2014

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:

.logon
begin loading ....;
end loading ....;
.quit

By doing the following:

APPLY
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).

--SteveF

Dominiq 11 posts Joined 12/13
03 Apr 2014

Thanks Steven for your brief explaination. :-)

Swapnil

You must sign in to leave a comment.