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 !!! 

Select and Delete a Row in single DML- Better job control

Can we Select a row and delete the row in a single SQL?

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

---- Select and delete in the same statement---

SELECT AND CONSUME TOP 1 * FROM TA_62917_QRMDB.VERSION_QRM;

How it helps?

1. It becomes an efficient way of maintaining the latest records in the Journal/Control tables of a Batch.

2. Control tables that contain the aggregation job-names, will be processed one by one. So, it helps in dependency control.

RunAggregate.ksh <Aggregate_Name>

where <Aggregate_Name> is taken from

1	TAG_MICR_PAIE
2	TAG_SER_BILLING
3	TAG_MON_BILLING

3. Environments synchronisation can be done using the above query.

Example: If a file is transferred from Mainframe server to UNIX server.

Command : Sendfile.ksh <Filename>

Where <filename> is the output of

SELECT AND CONSUME TOP 1 * FROM TA_62917_QRMDB.VERSION_QRM;

Thus once the file is sent, chances of sending the file again is NIL.

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