automatic release mload in teradata

Question

We are implementing data warehouse in teradata and has our stage process to load data from source to teradata using mload utility. Problem we are having is if due to some reason mload was active on the table session fail to load data. Then 99% we go back and run command release load table name. This always work and we never had any data loss.

My question is can we automate this process. I have around 50 tables so can i create a script which check if mload is active of these table and release mload if active. before actually starting the wf itself. I can have those scripts as IF load active then release or pass. I am not able to find how to check if mload is active on table.

Is there any system table or query which can tell if mload is active on table. Please advise. Thanks!

Answer 1

If your attempt to access the table encounters the error number 2652, Operation not allowed: %DBID.%TVMID is being Loaded, this signifies the table is being loaded or that a previous attempt to load the table failed and has not been resolved.

With careful planning, you may be able to leverage the presence of the acquisition and application error tables to determine if a MultiLoad lock is present or that a previous MultiLoad had job finished with a non-zero return code. The acquisition error table, which is specified as the third table in BEGIN MLOAD command (et_tname1), traps errors that occur during the acquisition phase. The application table, which is specified as the fourth table in the BEGIN MLOAD command (uv_tname1), traps errors encountered during the application phase.

If the UV table is present but contains no records, the multiload likely failed in during acquisition phase. You can confirm this by checking for the presence of rows in the 'ET` table. In this state, the MultiLoad lock could be released and the error tables removed. This would allow the table to be accessed and/or the load step restarted.

If the 'UV' table is present and contains rows, the MultiLoad likely failed during the application phase. In this state, the MultiLoad lock should not be released nor the error tables removed. You should determine the reason for the failure and restart the failed job from the point of failure. If you release the MultiLoad lock and/or remove the error tables you may end up with a table in an inconsistent state. In this state, the table will likely have to be dropped, re-created, and reloaded from source files or backups.

Typically, unless the table was empty to begin with, it is desirable to resolve the failed load job.

Have you considered using the ANSI MERGE statement in your Informatica workflow to avoid this issue in the first place? You gain some of the efficiencies of the MultiLoad utility but your recoverability doesn't leave the table in a locked state like the utility. It may require redesigning your approach in your ETL but something to consider going forward.

Answer 2

You can use .LABLES and ACTIVITYCOUNT in order to find if the tables are locked or the help tables exist. Add this at the end/start of your Mload script.

ACTIVITYCOUNT returns the number of records selected/impacted by the previous SQL query.

LABEL − Assigns a label to a set of SQL commands.

-- DECLARE RULES OF THE LABES
SELECT TOP 1 * FROM <tablename>;
.IF ACTIVITYCOUNT <> 0 THEN .GOTO RELEASE_MLOAD;

SELECT TOP 1 * FROM UV_<tablename>;
.IF ACTIVITYCOUNT >= 0 THEN .GOTO DROP_UV;

SELECT TOP 1 * FROM ET_<tablename>;
.IF ACTIVITYCOUNT >= 0 THEN .GOTO DROP_ET;

SELECT TOP 1 * FROM WT_<tablename>;
.IF ACTIVITYCOUNT >= 0 THEN .GOTO DROP_WT;

SELECT TOP 1 * FROM LOG_<tablename>;
.IF ACTIVITYCOUNT >= 0 THEN .GOTO DROP_LOG;

-- DECLARE THE LABELS
.LABEL RELEASE_MLOAD
RELEASE MLOAD <tablename>;
.IF ERRORCODE <> 0 THEN .EXIT ERRORCODE;

.LABEL DROP_UV
DROP TABLE UV_<tablename>;
.IF ERRORCODE <> 0 THEN .EXIT ERRORCODE;

.LABEL DROP_ET
DROP TABLE ET_<tablename>;
.IF ERRORCODE <> 0 THEN .EXIT ERRORCODE;

.LABEL DROP_WT
DROP TABLE WT_<tablename>;
.IF ERRORCODE <> 0 THEN .EXIT ERRORCODE;

.LABEL DROP_LOG
DROP TABLE LOG_<tablename>;
.IF ERRORCODE <> 0 THEN .EXIT ERRORCODE;