Teradata Procedure Locks a Log Table

Question

I am trying to get around a problem where the following SP locks the log table DWH_METADATA.LOG_TABLE and the lock is released only when the DELETE ALL and INSERT-SELECT statements are executed. This procedure chokes the whole ETL process. Moreover, this procedure is called via IBM Data Stage Sequence.

Below is the procedure code:

REPLACE PROCEDURE DWH.HIST_PROC (OUT RETURN_MSG VARCHAR(100))
 
BEGIN
/**********************************
* DECALRE VARIABLES   *
**********************************/
DECLARE PJ_RID INTEGER;
DECLARE MS_Name VARCHAR(100); 
DECLARE JS_Name VARCHAR(100); 
DECLARE JT_Name VARCHAR(100);
DECLARE PJ_Domain VARCHAR(100);
DECLARE JS_Timestamp TIMESTAMP(0);
DECLARE Script_Name VARCHAR(500);
DECLARE Procedure_Name VARCHAR(100);
DECLARE JS_No INTEGER;
DECLARE SS_Ts TIMESTAMP(0);
DECLARE SR_Processed INTEGER;

/***********************************
* EXCEPTION HANDLING *
***********************************/
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN 
UPDATE DWH_METADATA.LOG_TABLE
SET 
JOB_STATUS = SQLCODE,
STEP_END_TS = CURRENT_TIMESTAMP(0),
JOB_END_TIMESTAMP = CURRENT_TIMESTAMP(0)
WHERE JOB_RUN_ID = PJ_RID
AND STEP_NO = JS_No
AND JOB_NAME = JS_Name;

END;    

/**********************************
* SET VARIABLES            *
**********************************/
SET MS_Name = 'A Value';
SET JS_Name='A Value';
SET JT_Name = 'A Value';
SET PJ_Domain  = 'A Value';
SET JS_Timestamp = CURRENT_TIMESTAMP(0);
SET Script_Name = 'A Value';
SET Procedure_Name  = 'A Value';
SET SS_Ts   = CURRENT_TIMESTAMP(0);
SET JS_No   = 0;
SET RETURN_MSG  = 'Failure';

--------------------------------------------------------

--Insert log for step 0
INSERT INTO DWH_METADATA.LOG_TABLE
(
DWH_METADATA.LOG_TABLE.JOB_RUN_ID,                   
DWH_METADATA.LOG_TABLE.STEP_NO,                     
DWH_METADATA.LOG_TABLE.MASTER_SEQ_NAME ,              
DWH_METADATA.LOG_TABLE.JOB_NAME  ,                  
DWH_METADATA.LOG_TABLE.TABLE_NAME,                    
DWH_METADATA.LOG_TABLE.JS_Timestamp   ,        
DWH_METADATA.LOG_TABLE.JOB_END_TIMESTAMP          ,   
DWH_METADATA.LOG_TABLE.SS_Ts                 ,
DWH_METADATA.LOG_TABLE.STEP_END_TS                   ,
DWH_METADATA.LOG_TABLE.JOB_STATUS                    ,
DWH_METADATA.LOG_TABLE.JOB_DOMAIN                    ,
DWH_METADATA.LOG_TABLE.SCRIPT_NAME                  , 
DWH_METADATA.LOG_TABLE.PROCEDURE_NAME       ,         
DWH_METADATA.LOG_TABLE.RECORDS_PROCESSED             
)
VALUES
(:PJ_RID, :JS_No, :MS_Name, :JS_Name, :JT_Name,
:JS_Timestamp, NULL, :SS_Ts, NULL, NULL, :PJ_Domain, :Script_Name, :Procedure_Name, NULL
);

-----------------------------------------------------------------------------------------------------------------
LOCKING ROW FOR ACCESS
SEL COALESCE(MAX(JOB_RUN_ID),0) + 1
FROM DWH_METADATA.LOG_TABLE
WHERE JOB_NAME = :JS_Name
AND Job_Domain = :PJ_Domain
INTO :PJ_RID;


/**********************************  DELETE/INSERT RTD_DATA *************************************************/
--Insert log for step 1
SET JS_No = JS_No + 1 ;
SET JT_Name = 'DWH.HISTORY_TABLE ';
SET SS_Ts = CURRENT_TIMESTAMP(0);
INSERT INTO DWH_METADATA.LOG_TABLE
VALUES(    :PJ_RID,    :JS_No,    :MS_Name,    :JS_Name,    :JT_Name,    :JS_Timestamp,
                        NULL,    :SS_Ts,    NULL,    NULL,    :PJ_Domain,    :Script_Name,    :Procedure_Name,    NULL);

DELETE FROM DWH.HISTORY_TABLE ALL;
INSERT INTO DWH.HISTORY_TABLE
( 
COLUMNLIST       
)
SELECT 
SI.COLUMNS,
S2.COLUMNS,
.
.
.
    
FROM SOMETABLE1 S1
INNER JOIN SOMETABLE2 S2
ON S1.ID = S2.ID
.
.
.
.;


--Update log for step 1
SET SR_Processed = ACTIVITY_COUNT;

UPDATE DWH_METADATA.LOG_TABLE
SET 
JOB_STATUS = SQLCODE,
STEP_END_TS = CURRENT_TIMESTAMP(0),
JOB_END_TIMESTAMP = CURRENT_TIMESTAMP(0),
RECORDS_PROCESSED = :SR_Processed
WHERE JOB_RUN_ID = PJ_RID
AND STEP_NO = JS_No
AND JOB_DOMAIN = PJ_Domain
AND PROCEDURE_NAME = Procedure_Name
AND "TABLE_NAME"=JT_Name;   

---------------------------------------------------------------------------------------
SET RETURN_MSG = 'Success' ;



END;

Based on same logic, many other stored procedures are executing on daily basis without any such issue. I am unable to understand, why this procedure locks the log table although both of its' insert statements have been executed.

I have tried to execute the individual DML statements in BT and ET, and doing such did not have any impact and the issue persisted. Moreover, I have optimized the underlying INSERT-SELECT. It has reduced the exeution time significantly but the locking is still occurring.

Anyone please, help me pinpoint why this issue is ocurring.

EDIT: Structure of log table.

CREATE MULTISET TABLE DWH_METADATA.LOG_TABLE ,FALLBACK ,
         NO BEFORE JOURNAL,
         NO AFTER JOURNAL,
         CHECKSUM = DEFAULT,
         DEFAULT MERGEBLOCKRATIO,
         MAP = TD_MAP1
         (
          JOB_RUN_ID INTEGER,
          STEP_NO INTEGER,
          MASTER_SEQ_NAME VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
          JOB_NAME VARCHAR(200) CHARACTER SET LATIN NOT CASESPECIFIC,
          TABLE_NAME VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
          JOB_START_TIMESTAMP TIMESTAMP(6),
          JOB_END_TIMESTAMP TIMESTAMP(6),
          STEP_START_TS TIMESTAMP(0),
          STEP_END_TS TIMESTAMP(0),
          JOB_STATUS INTEGER,
          JOB_DOMAIN VARCHAR(200) CHARACTER SET LATIN NOT CASESPECIFIC,
          SCRIPT_NAME VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,
          PROCEDURE_NAME VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,
          RECORDS_PROCESSED DECIMAL(18,0))
    PRIMARY INDEX ( JOB_NAME ,TABLE_NAME ,JOB_START_TIMESTAMP );