Db/2 – SQL – Compound Statements Using Atomic Keyword

Background

Wanted to provide sample code on supporting so called “Compound Statements” in Db/2 LUW.

Definition

Here is IBM’s Definition:

A compound statement groups other statements together in an SQL routine. A compound statement allows the declaration of SQL variables, cursors, and condition handlers.

Outline

  1. Housekeeping
    • Db2 CLP
      • Revert Statement Terminator to ;
      • Turn off auto-commit
      • Enable Server Ouput
      • Change Statement Terminator
        • In our case we changed it to //
  2. Actual Code
    • Begin Atomic Block
      • Begin Atomic
    • Declare Variables
    • SQL Processing
    • End Atomic Block
      • Provide Statement Terminator
        • In our case //
  3. Environment Clean Up
    • DB2CLP
      • Revert Statement Terminator to ;
        • In our case we changed it to //
      • Disable Server Output

Code


--Set Terminator
--#SET TERMINATOR ;

-- Disable autocommit
UPDATE COMMAND OPTIONS USING C OFF;

--Set Server Output ON
SET SERVEROUTPUT ON;

--Change Terminator
--#SET TERMINATOR //

--Compound STATEMENT Begin
--Begin
BEGIN ATOMIC

    DECLARE lNumberofRecords                int;
    DECLARE lNumberofRecordsPostOperation   int;
    DECLARE lNumberofRecordsAffected        int;
    DECLARE strLog                          varchar(600);

    set lNumberofRecords = -1;
    set lNumberofRecordsAffected = -1;
    set lNumberofRecordsPostOperation  =-1;

    /*
        Get Current Number of Records
    */
    SET lNumberofRecords =
            (
                select count(*) 

                FROM    "bank"."balance"

            )           

    ;

    /*
        Display Number of Records
    */
    SET strLog =
                    TO_CHAR
                    (
                        coalesce
                        (
                            lNumberofRecords
                            , -1
                        )
                    )
                    CONCAT ' records are in table ( Original Count)'
                    ;

    CALL DBMS_OUTPUT.PUT_LINE(strLog);

    CALL DBMS_OUTPUT.NEW_LINE;  

    /*
        Remove All Records
    */
    DELETE "bank"."balance"
    ;   

    /*
        Get Number of Records by last operation
    */
    GET DIAGNOSTICS
        lNumberofRecordsAffected = ROW_COUNT
        ;

    SET strLog =
                    TO_CHAR(lNumberofRecordsAffected)
                    CONCAT ' record(s) pruned'
                    ;

    CALL DBMS_OUTPUT.PUT_LINE(strLog);

    CALL DBMS_OUTPUT.NEW_LINE;  

    /*
        Count Records
    */
    SET lNumberofRecordsPostOperation
            = (
                    select count(*)
                    FROM    "bank"."balance"
              )                 

    ;

    SET strLog =
                    TO_CHAR
                    (
                        coalesce
                        (
                              lNumberofRecordsPostOperation
                            , -1
                        )
                    )
                    CONCAT ' records are in table ( Post Operation )'
                    ;

    CALL DBMS_OUTPUT.PUT_LINE(strLog);

    CALL DBMS_OUTPUT.NEW_LINE;          

END

--Compound STATEMENT - Closing
//

--#SET TERMINATOR ;

SET SERVEROUTPUT OFF

 

Summary

Each SQL GUI ( IBM Db/2 CLP & IBM Data Studio, Dbeaver, DbVis, WinSQL ) will be slightly different in terms of customization.

But, wanted to provide a rough draft.

References

  1. IBM
    • DB/2
      • Home > Db2 for z/OS 10.0.0 > DB2 > SQL > SQL control statements for SQL routines
        • Compound-statement
          • Compound-statement
            Link

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s