We are using Mainframe teradata utility to load and unload the data from external source systems. We are facing issues when 3 mload jobs trying to load the data from different files to same table at same time. So job aborted stating 2574, operation not allowed. table_1 is is being MLoaded.
Its a dump load only. Is there any way to sort out this issue using different utility or releasing the lock and process the data in the batch(like any key word) so that we have it before load phase starts.
Solved! Go to Solution.
The load utilities do not allow you to run more than one load against a table at the same time. There is no way to release a lock and to try and get around this. If the volumes are not that large, you could look at using TPUMP / TPT STREAM. Or load into three different staging tables (NOPI) and then do a copy from the staging table to the target table, but those copies can also only be run one at a time, but they will at least block and not be rejected with an error.
I think that TPT is best option and even faster.
For mload there is only option to use 'inmod' (to load more files/different datalines to one table), but that needs some programming skills in "C".
I am assuming that files are not same, but very similar (so you can't easily merge them using .python/.perl/.shell scripts), but you want sub-set which is same (or at least have same structure-datatypes).
If you have to stick with mload (no way to use preparefile script), instead of directly loading to stage, introduce pre-stage :)
In such approach you can easily load everything to pre-stage as generic strings (so no extra work for casting, formating stuff). So you can make one generic mload script parametrized via some environment file. Later on you can call (possibly some shell script with handling error/return codes) several mload jobs to feed prestage and normal bteq script which will prepare stage and loads data to target.
Personally, go for TPT. It is faster(sometimes 10times!!! with same data), has more options, more flexible(you can make generic template and just tailor configuration files per-each interface file and call it ... and with on-fly select generation tpt scripts are really small ..., with easyloader almost without any extra work ) .... when we have chance to migrate mload/fload to tpt we do so :-).
Run below SQLs to release lock from Mloaded table:
Error: Mload - 2574, Operation not allowed. Table is being MLoaded
RELEASE LOCK DB.TableName;
RELEASE LOCK DB.TableName,OVERRIDE;
RELEASE MLOAD DB.TableName IN APPLY;
RELEASE LOCK applies to HUT (archive) locks.
RELEASE MLOAD (without the IN APPLY option) might be appropriate, if the MLOAD failed prior to the APPLY phase.
Only use IN APPLY in exceptional circumstances, after reading the documentation and being sure you understand all the implications.