Queue Table implementation - Job Control with Sample Script

Queue Tables were introduced from TD V2R6 onwards. It helps in job-control by using the FIFO algorithm.

A Job that has waited the longest moves up, and will be executed first.

Since Queue tables are implemented in Teradata using CREATE TABLE SQL, all A-C-I-D rules of RDBMS apply.

Hence, if the job is aborted, the transaction is rolled-back.

The first column in a QITS or Queue table is always the QITS column ( Query Insertion Time Stamp).

Below is a sample implementation of Queue Tables:


CREATE  TABLE DB_LOAD_TBLS.LAYWY_CONFIG
, QUEUE
(
QITS_JOB_START TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
,JOB_ID INTEGER)
;

INSERT INTO DB_LOAD_TBLS.LAYWY_CONFIG
VALUES
(CURRENT_TIMESTAMP(6)
, 1
);

SELECT * FROM LCI_LOAD_TBLS.LAYWY_CONFIG;

SELECT AND CONSUME TOP 1 * FROM LCI_LOAD_TBLS.LAYWY_CONFIG;

The SELECT AND CONSUME statement helps you to simulate the FIFO Pop function.
So, the moment the job is executed successfully, the entry will be deleted. Thus, this also helps in removing re-runs.

Feel free to add comments.  And If you feel Helped, Please Donate !!!