DB2 10.5 for Linux, UNIX, and Windows
DB2 10.5 for Linux, UNIX, and Windows
A compound SQL (inlined) statement is a compound SQL statement that is inlined at run time within another SQL statement. Compound SQL (inlined) statements have the property of being atomically executed; if the execution of any of the statements raises an error, the full statement is rolled back.
This statement can be embedded in a trigger, SQL function, or SQL method, or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared.
The privileges held by the authorization ID of the statement must also include all of the privileges necessary to invoke the SQL statements that are specified in the compound statement.
>>-+------------+--BEGIN ATOMIC---------------------------------> | (1) | '-label:-----' >--+-----------------------------------------+------------------> | .-------------------------------------. | | V | | '---+-| SQL-variable-declaration |-+--;-+-' '-| condition-declaration |----' >--+--------------------------+--END--+-------+---------------->< | .-,--------------------. | '-label-' | V | | '---| SQL-statement |--;-+-' SQL-variable-declaration .-,-----------------. V | |--DECLARE----SQL-variable-name-+--data-type--------------------> .-DEFAULT NULL------. >--+-------------------+----------------------------------------| '-DEFAULT--constant-' condition-declaration |--DECLARE--condition-name--CONDITION--FOR----------------------> .-VALUE-. .-SQLSTATE--+-------+-. >--+---------------------+--string-constant---------------------| SQL-statement |--+-CALL----------------------------------------------+--------| +-FOR-----------------------------------------------+ +-+-----------------------------------+--fullselect-+ | | .-,-----------------------. | | | | V | | | | '-WITH----common-table-expression-+-' | +-GET DIAGNOSTICS-----------------------------------+ +-IF------------------------------------------------+ +-INSERT--------------------------------------------+ +-ITERATE-------------------------------------------+ +-LEAVE---------------------------------------------+ +-MERGE --------------------------------------------+ +-RETURN--------------------------------------------+ +-searched-delete-----------------------------------+ +-searched-update-----------------------------------+ +-SET Variable--------------------------------------+ +-SIGNAL--------------------------------------------+ '-WHILE---------------------------------------------'
If the ATOMIC keyword is specified in an SQL function in a module or an SQL procedure, the compound statement is processed as a compound SQL (compiled) statement.
When XML values are passed by reference, any input node trees are used directly. This direct usage preserves all properties, including document order, the original node identities, and all parent properties.
This example illustrates how inline SQL PL can be used in a data warehousing scenario for data cleansing.
The example introduces three tables. The TARGET table contains the cleansed data. The EXCEPT table stores rows that cannot be cleansed (exceptions) and the SOURCE table contains the raw data to be cleansed.
A simple SQL function called DISCRETIZE is used to classify and modify the data. It returns the null value for all bad data. The compound SQL (inlined) statement then cleanses the data. It walks all rows of the SOURCE table in a FOR-loop and decides whether the current row gets inserted into the TARGET or the EXCEPT table, depending on the result of the DISCRETIZE function. More elaborate mechanisms (multistage cleansing) are possible with this technique.
The same code can be written using an SQL Procedure or any other procedure or application in a host language. However, the compound SQL (inlined) statement offers a unique advantage in that the FOR-loop does not open a cursor and the single row inserts are not really single row inserts. In fact, the logic is effectively a multi-table insert from a shared select.
This is achieved by compilation of the compound SQL (inlined) statement as a single statement. Similar to a view whose body is integrated into the query that uses it and then is compiled and optimized as a whole within the query context, the database optimizer compiles and optimizes both the control and data flow together. The whole logic is therefore executed within the runtime environment of the database. No data is moved outside of the core database engine, as would be done for a procedure.
CREATE TABLE TARGET
(PK INTEGER NOT NULL
PRIMARY KEY, C1 INTEGER)
CREATE TABLE EXCEPT
(PK INTEGER NOT NULL
PRIMARY KEY, C1 INTEGER) CREATE TABLE SOURCE
(PK INTEGER NOT NULL
PRIMARY KEY, C1 INTEGER) CREATE FUNCTION DISCRETIZE(RAW INTEGER) RETURNS INTEGER
RETURN CASE
WHEN RAW < 0 THEN CAST(NULL AS INTEGER)
WHEN RAW > 1000 THEN NULL
ELSE ((RAW / 10) * 10) + 5
END INSERT INTO SOURCE (PK, C1)
VALUES (1, -5),
(2, NULL),
(3, 1200),
(4, 23),
(5, 10),
(6, 876) BEGIN ATOMIC
FOR ROW AS
SELECT PK, C1, DISCRETIZE(C1) AS D FROM SOURCE
DO
IF ROW.D IS NULL THEN
INSERT INTO EXCEPT VALUES(ROW.PK, ROW.C1);
ELSE
INSERT INTO TARGET VALUES(ROW.PK, ROW.D);
END IF;
END FOR;
END
SELECT * FROM EXCEPT ORDER BY 1
PK C1
----------- -----------
1 -5
2 -
3 1200
3 record(s) selected.
SELECT * FROM TARGET ORDER BY 1
PK C1
----------- -----------
4 25
5 15
6 875
3 record(s) selected. DROP FUNCTION DISCRETIZE
DROP TABLE SOURCE
DROP TABLE TARGET
DROP TABLE EXCEPT