Db/2 LUW – Conditional Schema Creation

Background

Wanted to explore what it will take to conditional create a schema in DB/2 LUW.

Transact SQL

In Transact SQL, it is straight forward.


if schema_id('hr') is null
begin

   exec('create schema [hr] authorization [dbo]');

end

DB2

Stored Procedure

ddlCreateSchema

Outline

  1. Declaration
    • Language :- SQL
    • Atomic
  2. Code
    • Find matching Schema in SYSCAT.SCHEMATA
    • If none found, then create schema
      • sql is ‘CREATE SCHEMA ‘ || v_SchemaQuoted
      • Prepare SQL String
        • Convert String to Prepared Statement
      • Execute Prepared Statement

Code

CREATE OR REPLACE PROCEDURE ddlCreateSchema
(
    vSchema VARCHAR(128)
)
SPECIFIC ddlCreateSchema
LANGUAGE SQL
BEGIN ATOMIC

    DECLARE v_count         INT;
    DECLARE v_buffer        VARCHAR(600);
    DECLARE v_SQLProcessed  BOOLEAN;

    DECLARE v_sql           varchar(4000);

    DECLARE v_SchemaQuoted   varchar(128);

    DECLARE CHAR_SINGLE_QUOTE  char(1);
    DECLARE CHAR_DOUBLE_QUOTE  char(1);

    DECLARE v_statement     statement;

    SET CHAR_SINGLE_QUOTE = '''';
    SET CHAR_DOUBLE_QUOTE = '"';

    SET v_SQLProcessed = FALSE;

    /*
     * Add Double Quotes to ensure case kept
    */
    SET v_SchemaQuoted = CHAR_DOUBLE_QUOTE
                            || vSchema
                            || CHAR_DOUBLE_QUOTE
                            ;

    /*
     * Get Number of Matching Schemas
     */
    SET v_count =  (
                        SELECT COUNT(*)
                        FROM   SYSCAT.SCHEMATA
                        WHERE  SCHEMANAME = vSchema
                   )
                    ;   

    SET v_buffer = 'Number of matching schema ';
    SET v_buffer = v_buffer || cast( v_count AS varchar(3));

    CALL DBMS_OUTPUT.PUT_LINE(v_buffer);

    /* If count is 0, then create schema
    */
    IF (v_Count = 0) THEN

        -- EXECUTE IMMEDIATE(v_sql);
        SET v_sql = 'CREATE SCHEMA ' || v_SchemaQuoted;

        PREPARE v_statement FROM v_sql;

        EXECUTE v_statement;

        SET v_SQLProcessed = TRUE;

    END IF;

END

//

Invoke


BEGIN ATOMIC

    DECLARE vSchema varchar(128);

    SET vSchema = 'hr';
    --SET vSchema = 'hr2';

    CALL DDLCREATESCHEMA(vSchema);

END

//

Summary

Noticed the following

  1. In Oracle & Db/2
    • Object Names are converted to upper-case unless quoted in double-quotes
  2. The following are only available within programmable objects
    • Datatype
      • Boolean
    • Statements
      • Prepare
      • Execute

References

  1. IBM Knowledge Center
    • Home > DB2 for Linux UNIX and Windows 9.7.0 > Product overviews > DB2 Database for Linux, UNIX, and Windows > What’s New overview > New features and functionality > SQL Procedural Language (SQL PL) enhancements > New data types are supported
  2. Serge Rielau
    • Developer Works
      • Using dynamic SQL inside SQL PL
        Link
  3. Antonio Maranhao
    • Developer Works
      • Execute Immediate
        Link
  4. Stack Overflow
    • How to execute an SQL string in DB2
      Link
    • Create table if not exists syntax db2
      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