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

Db2 Inspect

Background

As we discussed in an earlier post, one has to be careful when restarting a box that houses a database server.

The post that discusses the proper steps are here :-

DB/2 LUW – Stopping DB2 Subsystem ( On MS Windows )
Link

Db2 Inspect

Introduction

There are a couple of options for validating Db2 databases.

Our options include :-

  1. Db2 Inspect
  2. db2dart

Outline

  1. Preparation
    • Get diag folder
  2. Inspect Database
    • db2 inspect
  3. Convent binary file from db2 inspect to text file
    • db2inspf
  4. Review prepared text file

Preparation

Outline

  1. Get diag folder

Get Diag Folder

Linux

db2 get dbm configuration | grep "DIAGPATH"

Windows

db2 get dbm configuration | find /i "DIAGPATH"

db2 inspect

Outline

The steps to take are :

  1. Launch db2cmd window by issuing db2cmd
  2. connect to the database ( connect database [database] )
  3. Issue db2 inspect command

Check Database

Sample


db2cmd
db2 connect to sample
db2 "inspect check database RESULTS KEEP db2_inspect_database_20180915_0810AM.txt ON DBPARTITIONNUM (1)"

Output


DB20000I  The INSPECT command completed successfully.

Check Table

Sample


db2cmd
db2 connect to sample
db2 "inspect check table NAME ACT SCHEMA DADENIJI RESULTS KEEP db2_inspect_table_act.txt ON DBPARTITIONNUM (1)"

Output


DB20000I  The INSPECT command completed successfully.

db2inspf

db2inspf ( No Parameters )

Sample


C:\>db2inspf db2_database_sample_20180915_0807AM.txt db2_database_sample_clear_20180915_0807AM.txt

Trouble Shooting

db2 inspect

SQL1143N The operation cannot complete because a file error occurred for the file “e:\db2temp\db2_inspect.txt”

Error

Error Image

SQL1143N_20180915_0851AM

Error Text

SQL1143N  The operation cannot complete because a file error occurred for the
file "e:\db2temp\db2_inspect.txt".
Remediation

One does not need to use the full file path.

The file is placed in the db2 dump directory.

BTW, issue “db2 get dbm configuration” to get database configuration and deduce the db2diag folder.

File Is Already In use

Error

Error Image

fileIsAlreadyInUse_20180915_0841AM.PNG

Error Text

SQL1142N  The operation cannot complete because the file "db2_database_sample.txt" is already in use.

Remediation

Please use a new file name