Second Teradata Article Published

My second Teradata article has been published: Exploring Teradata 13's PERIOD Functions.

It is the follow-up to Exploring Teradata 13's PERIOD Data Type.

The article is quite long (12 printed pages) and took a long time to write, but I'm pleased with the outcome.

The "Other" Persistence of Vision

Today marks the opening of my blog at Teradata Developer Exchange. I've named it Persistence of Vision just like this one, but now I will keep this one focused on SQL Server and put anything specific to Teradata in the other blog.

My first post is Detecting a Broken Prior Value/New Value Chain, which I plan to post here also but with SQL Server code.

Transactions in Teradata

Here is a quick test to show how transactions work in Teradata.

CREATE TABLE t1 (
    Col1    INT 
);
 
INSERT INTO t1 VALUES (1); 
INSERT INTO t1 VALUES (2);
INSERT INTO t1 VALUES (3);
 
SELECT Col1 AS AfterInsert FROM t1;

BEGIN TRANSACTION;

UPDATE t1 SET Col1 = Col1 * 10;

SELECT Col1 AS AfterUpdate FROM t1;

ROLLBACK;
--ABORT;

SELECT Col1 AS AfterRollback FROM t1;

Running this query in Teradata SQL Assistant creates two results panes and highlights the ROLLBACK. The message at the bottom states, "ROLLBACK Failed. 3514: User-generated transaction ABORT."

Running this same query in Nexus creates three results panes.

AfterInsert
-----------
1
2
3

Nexus adds a tab here with no results. It appears to correspond to the ROLLBACK.

AfterUpdate
-----------
20
30
10

Neither returns results for the "AfterRollback" query.

Running that query after running the whole script returns the expected (rolled back) results.

AfterInsert
-----------
1
2
3

There is clearly more to understand here. I will update this post as I learn more.

Update 1 (2011-01-07)

Our system uses Teradata transaction semantics. Look here for a quick explanation of transaction semantics.

To see which mode you are running in a session, use HELP SESSION and look for the column "Transaction Semantics".

(Many thanks to my friend and colleague Joshua Skarphol for the details.)

Teradata Recursive View

Here is a complete example of building a recursive view in Teradata.

The table stores records that show the current [slightly simplified] hierarchy of two top-level automobile manufacturers.

Table Create

CREATE MULTISET TABLE AutoMarque (
    MarqueName        VARCHAR(200)  NULL,
    ParentMarqueName  VARCHAR(200)  NULL
) UNIQUE PRIMARY INDEX AutoMarque_UPI (MarqueName);


Inserts

INSERT INTO AutoMarque (MarqueName, ParentMarqueName) VALUES ('Volkswagen AG',                  'Volkswagen AG');
INSERT INTO AutoMarque (MarqueName, ParentMarqueName) VALUES ('AUDI AG',                        'Volkswagen AG'); 
INSERT INTO AutoMarque (MarqueName, ParentMarqueName) VALUES ('Bentley Motors Limited',         'Volkswagen AG'); 
INSERT INTO AutoMarque (MarqueName, ParentMarqueName) VALUES ('Bugatti Automobiles S.A.S.',     'Volkswagen AG'); 
INSERT INTO AutoMarque (MarqueName, ParentMarqueName) VALUES ('SEAT, S.A.',                     'Volkswagen AG'); 
INSERT INTO AutoMarque (MarqueName, ParentMarqueName) VALUES ('Skoda automobilova a.s.',        'Volkswagen AG'); 
INSERT INTO AutoMarque (MarqueName, ParentMarqueName) VALUES ('Volkswagen Passenger Cars',      'Volkswagen AG'); 
INSERT INTO AutoMarque (MarqueName, ParentMarqueName) VALUES ('NSU Motorenwerke AG',            'AUDI AG'); 
INSERT INTO AutoMarque (MarqueName, ParentMarqueName) VALUES ('Automobili Lamborghini S.p.A.',  'AUDI AG'); 
INSERT INTO AutoMarque (MarqueName, ParentMarqueName) VALUES ('Tata Motors',                    'Tata Motors'); 
INSERT INTO AutoMarque (MarqueName, ParentMarqueName) VALUES ('Jaguar',                         'Tata Motors'); 
INSERT INTO AutoMarque (MarqueName, ParentMarqueName) VALUES ('Land Rover',                     'Tata Motors');

Recursive View Definition

REPLACE RECURSIVE VIEW AutoMarqueRecurse (
    MarqueName,
    ParentMarqueName,
    TopLevelMarqueName,
    RecurseLevel
) AS LOCKING ROW FOR ACCESS
SELECT
    MarqueName,
    ParentMarqueName,
    MarqueName,  -- TopLevelMarqueName
    1
FROM AutoMarque
WHERE ParentMarqueName = MarqueName

UNION ALL

SELECT
    m.MarqueName,
    m.ParentMarqueName,
    r.TopLevelMarqueName,
    r.RecurseLevel + 1 
FROM AutoMarqueRecurse AS r
JOIN AutoMarque        AS m
  ON m.ParentMarqueName = r.MarqueName 
WHERE m.ParentMarqueName <> m.MarqueName;

SELECT All Rows From Base Table

MarqueName                    ParentMarqueName
----------------------------- ---------------- 
Automobili Lamborghini S.p.A. AUDI AG
NSU Motorenwerke AG           AUDI AG
Jaguar                        Tata Motors
Land Rover                    Tata Motors
Tata Motors                   Tata Motors
AUDI AG                       Volkswagen AG
Bentley Motors Limited        Volkswagen AG
Bugatti Automobiles S.A.S.    Volkswagen AG
SEAT, S.A.                    Volkswagen AG
Skoda automobilova a.s.       Volkswagen AG
Volkswagen AG                 Volkswagen AG
Volkswagen Passenger Cars     Volkswagen AG

SELECT All Rows From Recursive View

MarqueName                    ParentMarqueName TopLevelMarqueName RecurseLevel 
----------------------------- ---------------- ------------------ --------------
Jaguar                        Tata Motors      Tata Motors        2
Land Rover                    Tata Motors      Tata Motors        2
Tata Motors                   Tata Motors      Tata Motors        1
Automobili Lamborghini S.p.A. AUDI AG          Volkswagen AG      3
NSU Motorenwerke AG           AUDI AG          Volkswagen AG      3
AUDI AG                       Volkswagen AG    Volkswagen AG      2
Bentley Motors Limited        Volkswagen AG    Volkswagen AG      2
Bugatti Automobiles S.A.S.    Volkswagen AG    Volkswagen AG      2
SEAT, S.A.                    Volkswagen AG    Volkswagen AG      2
Skoda automobilova a.s.       Volkswagen AG    Volkswagen AG      2
Volkswagen AG                 Volkswagen AG    Volkswagen AG      1
Volkswagen Passenger Cars     Volkswagen AG    Volkswagen AG      2