0

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!

user863952
  • 103
  • 2
  • 5
  • 15
  • If you can identify the failure occurred during acquisition phase, I'm sure you can script the release. The problem is if you release the MuliLoad during application phase leaving you with an inconsistent table that must be dropped and reloaded from backup or source files. – Rob Paller Dec 01 '15 at 18:34
  • Thanks for comment Rob. I am not sure how can i identify failure occurred during acquisition phase. What we do is try to load data into teradata table from informatica. And as target table is locked (or any other failure related to mload) so session fails. Now next step I want to do is automate this error handling. Is there anyway that informatica can trigger some queries which will find out what was the error and on basis of that I can code some standard resolutions. Trigger the same session again. This could be both informatica & teradata question so adding that tag. – user863952 Dec 01 '15 at 18:55
  • If I can create a generic reusable session in informatica which will be called on failure, take error message from last session, table name, do resolution and call previous session again. Then that can be used with any session and should help in this scenario. Please advise. – user863952 Dec 01 '15 at 18:57

2 Answers2

0

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.

Rob Paller
  • 7,736
  • 29
  • 26
  • Hi Rob, thinking of approach from ETL to load into Teradata. we read data from source and load into a empty table in teradata then merge that table to original table – user863952 Dec 01 '15 at 21:25
  • Hi Rob, Thinking of approach from ETL to load into Teradata. We read data from source and load into a empty table in teradata then merge that table to original table. That being said I won't have issue of table in an inconsistent state. So its okay for me to run release mload command on table just before starting load. Because even if it was failed at application phase I will anyway truncate table and load full data again. But I am yet not sure how to check if table is being loaded or previous attempt was failed. in other words how can i make sure that release mload command will not fail. – user863952 Dec 01 '15 at 21:31
  • You will receive a `2580 MLoad not active on table %TVMId` error that you will have to ignore or trap. – Rob Paller Dec 01 '15 at 21:52
  • Rob, I know we can create a Continue Error Handler in a stored procedure. Can you please guide how Can we do same for SQL statements. I believe we can not have release mload inside a stored procedure. If I include release mload in a stored procedure it report error "expected something between release and mload keyword" – user863952 Dec 01 '15 at 23:24
  • I think you can use a BTEQ script to accomplish this task with SQL. Then Informatica can call the BTEQ script to do the work. Look at `SET ERRORLEVEL`, I think this lets you control the impact of an error code. – Rob Paller Dec 02 '15 at 03:35
0

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;
IvelinaMarinova
  • 61
  • 4