Db/2 – SQL Error [42997] :- Function not supported (Reason code = “89”)

Background

Playing around with DB/2 LUW and ran into an error that deserves a bit of thought.

Error

Error – Image

42990_20180924_0909AM.PNG

Error – Text


SQL Error [42997]: Function not supported (Reason code = "89").. SQLCODE=-270, SQLSTATE=42997, DRIVER=4.21.29 

Reproduction

Here is the SQL statement that triggered the error


CALL SYSPROC.ADMIN_CMD('REORG INDEX "Fact"."INDX_UNIQUE_SALELARGE_INVOICEDATEKEYYEAR_PROFIT_SALEKEY" FOR TABLE "Fact"."SaleLarge<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;"></span>"     ') 

Troubleshooting

The basis for the error is that Index re-org can not be targeted against specific Indexes for un-partitioned tables.

Re-Org

Table – Reorg all Indexes

SQL

SQL – Syntax

CALL SYSPROC.ADMIN_CMD('REORG INDEXES ALL FOR TABLE "table-schema"."table"     ')

SQL – Sample

CALL SYSPROC.ADMIN_CMD('REORG INDEXES ALL FOR TABLE "Fact"."SaleLargeRowStoreClustered"     ')

Runstats

Runstats – Specific Index

SQL – Syntax


CALL SYSPROC.ADMIN_CMD('RUNSTATS ON TABLE "table-schema"."table" FOR INDEX "index-schema"."index-name" ') 

SQL – Sample


CALL SYSPROC.ADMIN_CMD('RUNSTATS ON TABLE "Fact"."SaleLarge" FOR INDEX "Fact"."INDX_UNIQUE_SALELARGE_INVOICEDATEKEYYEAR_PROFIT_SALEKEY" ') 

Runstats – All Indexes

SQL – Syntax


CALL SYSPROC.ADMIN_CMD('RUNSTATS ON TABLE "table-schema"."table" ON ALL COLUMNS WITH DISTRIBUTION ON ALL COLUMNS AND INDEXES ALL<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;"></span>') 

SQL – Sample


CALL SYSPROC.ADMIN_CMD('RUNSTATS ON TABLE "Fact"."SaleLarge" ON ALL COLUMNS WITH DISTRIBUTION ON ALL COLUMNS AND INDEXES ALL')

Metadata

Is table/index partitioned?

Table

  1. sysproc.admin_get_tab_info_v95
    • Partition
      • If not partitioned, Single Row for the table
      • If partitioned, Multiple Rows
    • Identifiers
      • Columns
        • DATA_PARTITION_ID
          • If not partitioned
            • DATA_PARTITION_ID = 0
          • If partitioned
            • Multiple entries for each partition
  2. syscat.datapartitionexpression
    • Column
      • datapartitionexpression
        • Partition Column / Expression
  3.  syscat.datapartitions
    • Granularity
      • Data Granularity, not Index
    • Table Space ID
      • TBSPACEID
        • Table Space ID – Data
      • INDEX_TBSPACEID
        • Tablespace ID – Index
      • Partition Info
        • LOWVALUE
        • HIGHVALUE
      • Pages used
        • NPAGES
        • FPAGES

Index

  1. sysproc
    • sysproc.admin_get_index_info
      • Partition
        • If not partitioned, Single Row for the table
        • If partitioned, Multiple Rows
      • Identifiers
        • Columns
          • DATA_PARTITION_ID
            • If not partitioned
              • DATA_PARTITION_ID = 0
            • If partitioned
              • Multiple entries for each partition
  2. syscat
    • syscat.indexpartitions
      • Only has records for partitioned Indexes
      • Columns
        • IID
          • Index ID
        •  INDPARTITIONTBSPACEID
          • Table Space
        • DATAPARTITIONID
          • Data Partition
        • Nleaf
          • Number of leaves
        • NumRIDS
          • Number of Records

Summary

In closing , here are some quick points:

  1. Re-org
    • To be able to run targeted re-org against specific indexes, it does not matter whether the Index is itself partitioned.
    • The lone requirement is for the table to be partitioned
  2. Naming Constructs
    • INDEX SCHEMA
      • Please keep in mind that unlike some other databases i.e. Microsoft SQL Server, INDEX SCHEMA is based on the person who created the Index;  Unless specific otherwise

 

 

Db/2 – Query – Get Top N Rows for each group ( Stored Procedure )

Background

In our last post we tackled a nominal TOP N Query using a Function.

In this post we will do same using a Stored Procedure.

BTW, here is the link to the original post.

SQL

Stored Procedure

queryMaxByYearCorrelatedJoin_GroupByActualColumn

Outline

  1. Stored Procedure Declaration
    • Parameters
      • Year Begin
      • Year End
      • Number of records for each year
    • Number of Dynamic Results :- 1
    • Language :- SQL
  2. Temporary Table Creation
    • Declare Temporary Table
      • On Commit
        • Preserve
        • Or Discard Rows
      • With Replace
        • If table exists, please replace
      • Not Logged
  3. Create Index on Temporary Table
  4. Populate Temporary Table
  5. Cursor
    • Declare Cursor against result set that we will returning to client
      • WITH RETURN TO CLIENT
    • Open Cursor

Code


 -- DROP PROCEDURE queryMaxByYearCorrelatedJoin_GroupByActualColumn
-- //

CREATE OR REPLACE PROCEDURE queryMaxByYearCorrelatedJoin_GroupByActualColumn
(
	  @yearBegin INT  DEFAULT 2015
	, @yearEnd   INT  DEFAULT 9999
	, @seqNbr    int default 3

)
DYNAMIC RESULT SETS 1

LANGUAGE SQL

BEGIN 

	DECLARE @id        int;

	DECLARE @idMax     int;

	DECLARE @iYearBase int;
	DECLARE @iYear     int;
	DECLARE @iYearCurrent int;

    DECLARE @dtCurrent DATE;

	DECLARE @dtDate    DATE;

	DECLARE GLOBAL TEMPORARY TABLE SESSION.YEAR
	(

	 	  id   		 int not null 

			GENERATED BY DEFAULT
			AS IDENTITY
	          (
	          	  START WITH 1
	          	, INCREMENT BY 1
	      	  )

		, fiscalYear int not NULL 

	)
	ON COMMIT PRESERVE ROWS
	WITH REPLACE
    NOT LOGGED
    ;	

    CREATE UNIQUE INDEX IDX_YEAR
    ON SESSION.YEAR
    (
    	fiscalYear
    )
    ;

	/*
		Populate Temp TABLE
	*/
    SET @id = 1;

    SET @iYear = @yearBegin;

    WHILE (@iYear <= @yearEnd) DO

	    insert into SESSION.YEAR
		(
			fiscalYear
		)
		SELECT *

		FROM  (
					VALUES
					(
						@iYear
					)
			  ) t1
			  (			

			      fiscalYeal
			  )
		;

		SET @id = @id + 1;

		SET @iYear = @iYear + 1;

	END WHILE;

	/*
		data to return to client will be in cursor
	*/
 	BEGIN

		/*
			Declare cursor
		*/
		DECLARE cur CURSOR

		WITH RETURN TO CLIENT FOR

		    WITH cteYearCustomer
		    (
		          InvoiceDateKeyYear
		        , SaleKey
		        , CustomerKey
		        , Profit
		        , sequenceNbr
		    )
		    as
		    (
		        select  

		              tblFS.InvoiceDateKeyYear AS InvoiceDateKeyYear

		            , tblFS.SaleKey AS SaleKey

		            , tblFS.CustomerKey AS CustomerKey

		            , tblFS.PROFIT AS PROFIT

		            , ROW_NUMBER()
		                OVER
		                (
		                    PARTITION BY
		                          tblFS.InvoiceDateKeyYear

		                    ORDER BY
		                            tblFS.PROFIT DESC
		                )                   

		        from   "Fact"."SaleLargeRowStoreClusteredPartition"  tblFS

		        INNER JOIN SESSION.YEAR tblYear

		        		ON tblFS.InvoiceDateKeyYear = tblYear.fiscalYear
		    )    

		    SELECT

		          tblFS.INVOICEDATEKEYYEAR

		        , tblFS.SaleKey

		        , tblFS.CustomerKey

		        , tblFS.PROFIT

		        , tblFS.sequenceNbr     

		    from cteYearCustomer tblFS

		    WHERE tblFS.sequenceNbr <= @seqNbr

		    ;

		/*
			Open Cursor
		*/
		OPEN cur;	

	END
	;

END

//

Invoke


CALL queryMaxByYearCorrelatedJoin_GroupByActualColumn(2012, 2013, 4)

Output

Query_Output_20180922_0641PM

Troubleshooting

There is quite a bit of errors that can be encountered when developing programmable objects in DB/2 LUW.

Here are some of them.

SQL Error [42710]: The name of the object to be created is identical to the existing name

Error Image

error_42710.PNG

Error Text


Query execution failed

Reason:
SQL Error [42710]: The name of the object to be created is identical to the existing name "SESSION.YEAR" of type "DECLARED TEMPORARY TABLE".. SQLCODE=-601, SQLSTATE=42710, DRIVER=4.21.29

Remediation

Outline

  1. Add the “WITH REPLACE” clause to your Temporary table definition

SQL

	DECLARE GLOBAL TEMPORARY TABLE SESSION.YEAR
	(

	 	  id   		 int not null 

			GENERATED BY DEFAULT
			AS IDENTITY
	          (
	          	  START WITH 1
	          	, INCREMENT BY 1
	      	  )

		, fiscalYear int not NULL 

	)
	ON COMMIT PRESERVE ROWS
	-- WITH REPLACE
    NOT LOGGED
    ;	

with


	DECLARE GLOBAL TEMPORARY TABLE SESSION.YEAR
	(

	 	  id   		 int not null 

			GENERATED BY DEFAULT
			AS IDENTITY
	          (
	          	  START WITH 1
	          	, INCREMENT BY 1
	      	  )

		, fiscalYear int not NULL 

	)
	ON COMMIT PRESERVE ROWS
	WITH REPLACE
    NOT LOGGED
    ;	

 

References

  1. Temporary Table
    • Micro focus
      • Define Global Temporary Table
        Link

Db/2 – Query – Get Top N Rows for each group ( Function )

Background

Wanted to start querying our WideWorld database on Db/2 LUW.

Code

Outline

  1. Create Procedure
    • Parameters
      • Nth
    • ResultSet
      •  fiscalYear
      • SaleKey
      • profit
      • sequenceNbr
    • Not Deterministic
    • Ability to read SQL Data
  2. Common Table Expression ( CTE )
    • Windowing Function
      • Partition by Year

SQL


DROP FUNCTION func_queryMaxByYearCorrelatedJoin_GroupByActualColumn_WF
//

CREATE OR REPLACE FUNCTION func_queryMaxByYearCorrelatedJoin_GroupByActualColumn_WF
(
    @seqNbr int default 3
)
RETURNS TABLE
(

       fiscalYear       int
     , SaleKey          bigint
     , CustomerKey      integer
     , profit           decimal(18, 2)
     , sequenceNbr      int

)
LANGUAGE SQL

-- CARDINALITY 50

NOT DETERMINISTIC

READS SQL DATA 

RETURN

    WITH cteYearCustomer
    (
          InvoiceDateKeyYear
        , SaleKey
        , CustomerKey
        , Profit
        , sequenceNbr
    )
    as
    (
        select  

              tblFS.InvoiceDateKeyYear AS InvoiceDateKeyYear

            , tblFS.SaleKey AS SaleKey

            , tblFS.CustomerKey AS CustomerKey

            , tblFS.PROFIT AS PROFIT

            , ROW_NUMBER()
                OVER
                (
                    PARTITION BY
                          tblFS.InvoiceDateKeyYear

                    ORDER BY
                            tblFS.PROFIT DESC
                )                   

        from   "Fact"."Sale" tblFS

    )    

    SELECT

          tblFS.INVOICEDATEKEYYEAR

        , tblFS.SaleKey

        , tblFS.CustomerKey

        , tblFS.PROFIT

        , tblFS.sequenceNbr     

    from cteYearCustomer tblFS

    WHERE tblFS.sequenceNbr <= @seqNbr

//

Invoke


SELECT *

FROM   TABLE
            (
                func_queryMaxByYearCorrelatedJoin_GroupByActualColumn_WF
                (
                    3
                )
            ) tblP

Output

queryPartitionReturnNth.PNG

Db/2 – LUW – Managing Transaction Log Backup

Background

Wanted to document some of the steps to take when wanting to implement Transaction Log backup in DB/2 LUW.

 

Outline

  1. Configuration
    • Review & Apply Settings
      • LOGRETAIN
        • Deprecated
      •  LOGARCHMETH1
        • Set folder to place LOG backups
  2. Stop Database
  3. Restart Database
  4. Take full database backup
  5. Review
    • Review File System Folder
      • Active Logs
      • Archive Logs

 

Configuration

 

Outline

Here are the entries that need to be reviewed, set, and tweaked for Db/2 Log Backup.

Items

  1. Path to log files
    • Path where active log files are kept
    • Current :- M:\DB2\NODE0000\SQL00002\LOGSTREAM0000\
  2. LogRetain
    • Text :- Log retain for recovery status
    •  Setting :- NO
    • References
      • Stackoverflow
        • Need to properly set USEREXIT and LOGARCHMETH1
          Link
  3. Primary
    • LOGARCHMETH1 ( Change )
      • Text :- First log archive method (LOGARCHMETH1)
      • Current :- DISK:M:\DB2\NODE0000\Backup\Log\WideWrld\
    • LOGPRIMARY
      • Text :- Number of primary log files (LOGPRIMARY)
      • Current :- 13
    • LOGARCHCOMPR1
      • Text :- Archive compression for logarchmeth1 (LOGARCHCOMPR1)
      • Current :- OFF
  4. Secondary
    • LOGARCHMETH2 ( Change )
      • Text :- First log archive method (LOGARCHMETH2)
      • Current :- DISK:M:\DB2\NODE0000\Backup\Log\WideWrld\
    • LOGSECOND
      • Text :- Number of primary log files (LOGPRIMARY)
      • Current :- 13
    • LOGARCHCOMPR2
      • Text :- Archive compression for logarchmeth2 (LOGARCHCOMPR2)
      • Current :- OFF

 

Settings – Set

Code


setlocal

set "_folder=M:\DB2\NODE0000\Backup\Log\WideWrld"

set "_database=WideWrld"

set "_logRetain=Recovery"

set "_logfileSize=1024"

if not exist %_folder%  mkdir %_folder%

db2 connect to %_database%

db2 update db cfg for %_database% using LOGARCHMETH1 "DISK:%_folder%"

db2 update db cfg for %_database% using LOGFILSIZ %_logfileSize%

db2 CONNECT RESET

Images

 

Settings – Get

Outline

Using “db2 get database configuration” highlight pertinent entries.

Textual


setlocal

set _database=WideWrld

db2 connect to %_database%

db2 get database configuration | findstr /I /C:LOGFILSIZ /C:LOGPRIMARY /C:LOGSECOND /C:LOGARCHMETH /C:"Log retain for recovery status"  /C:"Path to log files" 

db2 CONNECT RESET

endlocal

Images

 

 

Database Backup

Outline

Syntax

Code


db2 backup database {database} ONLINE to {folder} INCLUDE LOGS

Sample

Take full database backup

Code

@echo on

set "_database=WideWrld"

set "_folder=M:\DB2\NODE0000\Backup\Full\WideWrld"

if not exist %_folder%  mkdir %_folder%

rem terminate current connection
db2 terminate

rem deactivate database
db2 deactivate database %_database%

rem backup database
db2 backup database %_database% ONLINE to %_folder% INCLUDE LOGS

Review

Review File System

Active Logs

File Explorer

Image

 

Archive Logs

File Explorer

Image

 

Transaction Log Utilization

CLP

DB2 GET SNAPSHOT FOR DATABASE

Code
Syntax

DB2 GET SNAPSHOT FOR DATABASE ON {database}

Sample

DB2 GET SNAPSHOT FOR DATABASE ON WideWrld   | findstr /I /C:"log space used" /C:"active"

 

Output – Image

Output – Textual
Log space used by the database (Bytes) = 2114776
Maximum secondary log space used (Bytes) = 3577958
Maximum total log space used (Bytes) = 57837670
File number of first active log = 2060
File number of last active log = 2072
File number of current active log = 2072

Explanation
  1. Log space used by the database (Bytes)
  2. Maximum secondary log space used (Bytes)
  3. Maximum total log space used (Bytes)
  4. File number of first active log
  5. File number of last active log
  6. File number of current active log

Db/2 LUW – Loading SQL Server / WorldWideImporters

Background

Unfortunately DB/2 LUW Sample database is very small.

Let us copy data from SQL Server’s sample database, WideWorldImportersDW, over to our DB/2 instance.

Outline

  1. In SQL Server
    • Retrieve Data
  2. In DB2/LUW
    • Create database
    • Create Schema
    • Create Tables
    • Remove existing data
    • Load new data

SQL Server

Retrieve Data

Outline

Using SQL Server’s BCP retrieve data from SQL Server.

Dimension.Date

Code


setlocal

set "_sqlInstance=localhost"

set "_database=WideWorldImportersDW"

set "_columnList=*"

set "_datafileFolder=datafile"

set "_option= -c -T -t"," "

if not exist %_datafileFolder% mkdir %_datafileFolder%

bcp "select %_columnList% from [%_database%].[dimension].[date]" queryout %_datafileFolder%\dimension.date.txt  -S %_sqlInstance% %_option%

endlocal

Db/2 LUW

Database

Create

We provided a sample script for creating a new DB/2 LUW database here.

Schema

Create Schema

Dimension

Code

 CREATE SCHEMA "Dimension"

Table

Create Table

Dimension.Date

Code

DROP TABLE "Dimension"."Date"
//

CREATE TABLE "Dimension"."Date"
(
    "Date" date NOT NULL,
    DayNumber int NOT NULL,
    "DAY" varchar(10) NOT NULL,
    "MONTH" varchar(10) NOT NULL,
    ShortMonth varchar(3) NOT NULL,
    CalendarMonthNumber int NOT NULL,
    CalendarMonthLabel varchar(20) NOT NULL,
    CalendarYear int NOT NULL,
    CalendarYearLabel varchar(10) NOT NULL,
    FiscalMonthNumber int NOT NULL,
    FiscalMonthLabel varchar(20) NOT NULL,
    FiscalYear int NOT NULL,
    FiscalYearLabel varchar(10) NOT NULL,
    ISOWeekNumber int NOT NULL

)
IN TS_WIDEWRLD_REG04
//

ALTER TABLE "Dimension"."Date"
      ADD CONSTRAINT "PK_Dimension_Date"
      PRIMARY KEY
      (
        "Date"
      )
//        

Load data into DB/2

Outline

Using Db2 Import utility let us copy our comma delimited file into DB/2 LUW.

Dimension.Date

Code


setlocal

set "_datafile=..\bcp\datafile"

db2 connect to WideWrld

set "_table=\"Dimension\".\"Date\""

rem db2 import from /dev/null of del replace into  %_table% 

db2 "truncate table %_table% reuse storage ignore delete triggers immediate"

db2 commit

db2 import from %_datafile%\dimension.date.txt OF DEL modified by coldel, insert into %_table%

db2 CONNECT RESET

endlocal
Output

dm2Import_20180918_0405PM

Db/2 – Database – Create – Sample Script

Background

Wanted to provide and give a brief introduction on how to create a small footprint database on DB/2 LUW.

Basis

We will use a sample script provided by VMWare.

Here are the links :-

  1. vSphere 4.1 – ESX and vCenter > ESX and vCenter Server Installation Guide > vCenter Server Databases > Configure DB2 Databases
    • Use a Script to Create a DB2 Database
      Link

Script

WideWlrd

Outline

  1. Database
    • Create Database WideWrld
    • Code Set :- UTF-8
    • Page Size :- 4K
  2. Buffer Pools
    • 4K ( WideWrld_04KBP )
    • 8K ( WideWrld_08KBP )
    • 16K ( WideWrld_16KBP )
    • 32K ( WideWrld_32KBP )
  3. Table Spaces
    • Temporary
      • User
        • WideWrld_TMP_USER ( Page Size 32K )
    • Data
      • User
        • TS_WideWrld_REG04 ( Page Size 4K )
        • TS_WideWrld_REG08 ( Page Size 8K )
        • TS_WideWrld_REG16 ( Page Size 16K )

db2cmd Script


CREATE DATABASE WideWrld
AUTOMATIC STORAGE YES 
USING CODESET UTF-8 
TERRITORY DEFAULT
COLLATE USING SYSTEM PAGESIZE 4096;

CONNECT TO WideWrld;

CREATE BUFFERPOOL WideWrld_04KBP 
	IMMEDIATE SIZE 
	AUTOMATIC PAGESIZE 4K
	;

CREATE BUFFERPOOL WideWrld_08KBP 
	IMMEDIATE SIZE 
	AUTOMATIC PAGESIZE 8K
	;

CREATE BUFFERPOOL WideWrld_16KBP 
	IMMEDIATE SIZE 
	AUTOMATIC PAGESIZE 16K;

CREATE BUFFERPOOL WideWrld_32KBP 
	IMMEDIATE SIZE 
	AUTOMATIC PAGESIZE 32K;
	
CONNECT RESET;


CONNECT TO WideWrld;

CREATE  USER TEMPORARY  TABLESPACE WideWrld_TMP_USER
	PAGESIZE 32K  
	BUFFERPOOL  WideWrld_32KBP
	; 

CREATE REGULAR TABLESPACE TS_WideWrld_REG04 
	PAGESIZE 4K   
	BUFFERPOOL  WideWrld_04KBP 
	PREFETCHSIZE AUTOMATIC
	;
 
CREATE REGULAR TABLESPACE TS_WideWrld_REG08 
	PAGESIZE 8K   
	BUFFERPOOL  WideWrld_08KBP 
	PREFETCHSIZE AUTOMATIC
	;

CREATE REGULAR TABLESPACE TS_WideWrld_REG16  
	PAGESIZE 16K  
	BUFFERPOOL  
	WideWrld_16KBP 
	PREFETCHSIZE AUTOMATIC
	;

CREATE LARGE TABLESPACE TS_WideWrld_REG32  
	PAGESIZE 32K  
	BUFFERPOOL  
	WideWrld_32KBP 
	PREFETCHSIZE AUTOMATIC
	;
	
CONNECT RESET;

Batch file


setlocal

set "_file=WideWorldImportersDW.sql"

db2 -svtf %_file%

endlocal

Output

Image

WideWorldImportersDW_20180918_1222PM.

Drop Database

Preface

Once you are good with your test, please feel free to drop using steps that look like the one below.

Script


FORCE APPLICATION ALL;

DROP DATABASE WideWrld;

CONNECT RESET;

Summary

Standards

There are some standards that have to be followed.

Here are some of them:

  1. Database names
    • 8 Characters of less
      Link
  2. Other database Objects
    • 16 Characters of less
      SQL and XML Limits
      Link

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