How to test a table for a Loader Lock

Database
Teradata Employee

How to test a table for a Loader Lock

Hi.

I'm trying to create a store proc that counts how many rows are in the ET table if it exists.

I can trap for table not exists but I can't see to trap for when the table is 'loader locked'.

The error code appears to be 2652
ie CALL Failed. [2652] TPT_GET_TABLE_COUNT:Operation not allowed: TEST.ET_TTU_TEST_DQA is being Loaded.

Below is the temporary table I use, the proc and the call.

FYI. You have to lock the table via a load to be able to test for the error above.

Note: I'm using TD 13.0

CREATE SET GLOBAL TEMPORARY TABLE ERROR_TABLE_COUNT ,FALLBACK ,
CHECKSUM = DEFAULT,
LOG
(
TABLE_NAME VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
TABLE_ROW_COUNT INTEGER)
PRIMARY INDEX ( TABLE_NAME )
ON COMMIT DELETE ROWS;

REPLACE PROCEDURE CTLFW_GET_TABLE_COUNT(
IN p_DATABASE_NAME VARCHAR(30) -- DATABASE TABLES ARE IN
, IN p_TABLE_NAME VARCHAR(30) -- TABLE
,OUT p_TABLE_CNT INTEGER --count of records in table
)
--PROC LEVEL LABEL
P1:BEGIN

--variables
DECLARE v_SQL_STMT CHAR(2000) ;
DECLARE v_CNT INTEGER;
DECLARE v_TABLE_NAME VARCHAR(30) ;
DECLARE v_DATABASE_NAME VARCHAR(30) ;
DECLARE v_SQL_VALUE VARCHAR(4096) DEFAULT '';

SET v_TABLE_NAME = TRIM(p_TABLE_NAME);
SET v_DATABASE_NAME = TRIM(p_DATABASE_NAME);

--create statnent to run
SET v_SQL_STMT = 'INSERT INTO ERROR_TABLE_COUNT SELECT ' || '''' || v_TABLE_NAME || '''' || ' AS TABLE_NAME, COUNT(1) FROM ' || v_DATABASE_NAME || '.' || v_TABLE_NAME || ';' ;

--CREATE BLOCK for ERROR HANDLER
ERROR_BLOCK:BEGIN

--declare error for missing table
DECLARE CONTINUE HANDLER
FOR SQLSTATE '42000'
SET v_SQL_VALUE = SQLSTATE;

DECLARE CONTINUE HANDLER
FOR SQLSTATE 'T2652'
SET v_SQL_VALUE = SQLSTATE;

--start transaction
BT;

--execute the statement
CALL DBC.SYSEXECSQL(:v_SQL_STMT );

--check to see if table is not found
IF
v_SQL_VALUE = '42000' OR
v_SQL_VALUE = 'T2652' THEN

--set default to be zero
SET p_TABLE_CNT=0;

ELSE

--get the count from the global temporary table
SELECT TABLE_ROW_COUNT
INTO p_TABLE_CNT
FROM ERROR_TABLE_COUNT
WHERE TABLE_NAME = v_TABLE_NAME;
--end transcation
ET;

END IF;
END ERROR_BLOCK;
END P1;

CALL TPT_GET_TABLE_COUNT ('CTLFW3','CTLFW_T_PROCESS_RUN',p_OUT)

Tags (1)
2 REPLIES
Teradata Employee

Re: How to test a table for a Loader Lock

By the way, meant to say, anyone got any ideas on how to trap the error successfully?
Enthusiast

Re: How to test a table for a Loader Lock

Hi Chris,

I have the same requirement. Did you ever manage to solve your issue?

Sven