DB2 Table Elimination

-- Test run on DB2 9.5.2 (2009-05-04)

DROP TABLE AN_Anchor;

CREATE TABLE AN_Anchor (
    AN_ID INT NOT NULL,
    PRIMARY KEY(AN_ID)
);

DROP TABLE ANAT1_AnchorAttribute1;

CREATE TABLE ANAT1_AnchorAttribute1 (
    AN_ID INT NOT NULL,
    ANAT1_AnchorAttribute1Value CHAR(10) NOT NULL,
    PRIMARY KEY(AN_ID)
);

DROP TABLE ANAT2_AnchorAttribute2;

CREATE TABLE ANAT2_AnchorAttribute2 (
    AN_ID INT NOT NULL,
    ANAT2_AnchorAttribute2Value CHAR(10) NOT NULL,
    ANAT2_FromDate DATE NOT NULL,
    PRIMARY KEY(AN_ID, ANAT2_FromDate)
);

DROP VIEW lAN_Anchor;

CREATE VIEW lAN_Anchor
AS
SELECT
    AN.AN_ID,
    (
        SELECT
            ANAT1.ANAT1_AnchorAttribute1Value
        FROM
            ANAT1_AnchorAttribute1 ANAT1
        WHERE
            ANAT1.AN_ID = AN.AN_ID
    ) ANAT1_AnchorAttribute1Value,
    (
        SELECT
            ANAT2.ANAT2_AnchorAttribute2Value
        FROM
            ANAT2_AnchorAttribute2 ANAT2
        WHERE
            ANAT2.AN_ID = AN.AN_ID
        AND
            ANAT2.ANAT2_FromDate = (
                SELECT
                    MAX(sub.ANAT2_FromDate)
                FROM
                    ANAT2_AnchorAttribute2 sub
                WHERE
                    sub.AN_ID = AN.AN_ID
            )
    ) ANAT2_AnchorAttribute2Value
FROM
    AN_Anchor AN;

INSERT INTO AN_Anchor (
    AN_ID
)
VALUES
(1),
(2),
(3);

RUNSTATS ON TABLE lro2.AN_Anchor
ON KEY COLUMNS AND INDEXES ALL;

INSERT INTO ANAT1_AnchorAttribute1 (
    AN_ID,
    ANAT1_AnchorAttribute1Value
)
VALUES
(1, 'Green'),
(2, 'Blue'),
(3, 'Red');

RUNSTATS ON TABLE lro2.ANAT1_AnchorAttribute1
ON KEY COLUMNS AND INDEXES ALL;

INSERT INTO ANAT2_AnchorAttribute2 (
    AN_ID,
    ANAT2_AnchorAttribute2Value,
    ANAT2_FromDate
)
VALUES
(1, 'Car', '2008-01-01'),
(1, 'Bus', '2008-12-12'),
(2, 'Train', '2008-01-01'),
(2, 'Car', '2008-12-12'),
(3, 'Train', '2008-01-01'),
(3, 'Bus', '2008-12-12');

RUNSTATS ON TABLE lro2.ANAT2_AnchorAttribute2
ON KEY COLUMNS AND INDEXES ALL;

SELECT * FROM lAN_Anchor;

SELECT ANAT1_AnchorAttribute1Value FROM lAN_Anchor;

SELECT ANAT2_AnchorAttribute2Value FROM lAN_Anchor;