Tuesday, 19 February 2013

Queue tables basics

Teradata Database queue tables are implemented as a Teradata extension to ANSI SQL at the database/ table level.

Queue tables have a user-defined insertion timestamp (QITS) as the first column of the table.
The QITS contains the time the row was inserted into the queue table as the means for approximate FIFO ordering.

Teradata Database queue tables support asynchronous first-in-first-out (FIFO) push, pop, and peek queue operations:

1) The FIFO push operation is defined as a SQL INSERT operation to store rows into a queue table.
2) The FIFO peek operation is defined as an SQL SELECT operation to retrieve rows from a queue table without deleting them.
This is also referred to as browse mode.
3) The FIFO pop operation is defined as an SQL SELECT AND CONSUME operation to retrieve a row from a queue table and delete that selected row upon completion of the read.
This is also referred to as consume mode.
Consumed rows are rolled back when a transaction containing a SELECT AND CONSUME does not complete successfully.

What if we do SELECT -CONSUME and table contains no data?
A consume mode request goes into a delayed state when a SELECT AND CONSUME finds no rows in the queue table.
SELECT AND CONSUME request will wait for the table to be populated.
The request remains idle until an INSERT to that queue table awakens the request; that row is returned, and then it's deleted.

Can we change the default FIFO orderring?
The default FIFO ordering of a queue table may be altered before consumption by altering the QITS. This is done with the SQL UPDATE, UPSERT form of UPDATE, or MERGE statements.

Is SELECT .. CONSUME only way to delete data from Queue tables?
Rows may also be removed from a queue table with the SQL DELETE.

QUEUE tables can be used for event processing:
Queue tables can be used to know the order in which events occured.
Or  it could contain messages generated by a application. Other aplication reads this messages from this table in the same
sequence as the sending application has posted.

Thursday, 14 February 2013

Implementing BTEQ restart Logic using OS command

The logic involved maintaining a file (say bteq_restart_step.txt) which will have the name of the step from where the BTEQ has to be restarted.

Every Time a step completes the label name for the next step is written into the file.
So next time to submit the BTEQ it skips the step that were already executed and starts running from the next one.

At the end of  the BTEQ we delete that file.

Note that for writing and deleting the file we are using the BTEQ OS command.

.run file bteq_restart_step.txt;

.label sql1;
     DELETE FROM ${PQA_GSC_DCM_DB}.table1; ---- sq11
     .if errorcode <> 0 then .goto EXIT_ERROR;
     .if errorcode = 0 then .os echo  '.goto sql2' > bteq_restart_step.txt;

.label sql2;
     DELETE FROM ${PQA_GSC_DCM_DB}.table2; ---- sql2
     .if errorcode <> 0 then .goto EXIT_ERROR;
     .if errorcode = 0 then .os echo  '.goto sql3' > bteq_restart_step.txt;

.label sql3;
     DELETE FROM ${PQA_GSC_STG_DB}.table3; ---- sql3
     .if errorcode <> 0 then .goto EXIT_ERROR;
     .if errorcode = 0 then .os echo  '.goto sql4' > bteq_restart_step.txt;

.label sql4;
     DELETE FROM ${PQA_GSC_STG_DB}.table4; ---- sql4
     .if errorcode <> 0 then .goto EXIT_ERROR;
     .if errorcode = 0 then .os echo  '.goto sql5' > bteq_restart_step.txt;

.label sql5;
DELETE FROM ${PQA_GSC_STG_DB}.table5; ---- sql5

.os rm -f bteq_restart_step.txt;
.quit 0;

.label EXIT_ERROR;
     .quit 99;

(courtesy: Teradata forum)