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

Matthew Thomas, A Teacher And An Author

Videos

  1. Bergin OMalley
    • Launch of Matthew Thomas “We Are Not Ourselves”
      Link
  2. Simon & Schuster Books
    • Author Matthew Thomas on starting ‘We Are Not Ourselves’
      • Profile
        • Author Matthew Thomas discusses how he started writing his critically-acclaimed bestseller ‘We Are Not Ourselves’ with ‘Supernatural’ actor Misha Collins.
      • Videos
        • Video #1
          Channel :- Simon & Schuster Books
          Published On :- 2015-July-7th
          Link
    • Author Matthew Thomas Reflects on His Father’s Alzheimer’s Disease
      • Profile
        • Learn more about We Are Not Ourselves at http://books.simonandschuster.com/We-&#8230; In his acclaimed debut novel, We Are Not Ourselves, Matthew Thomas shares his deeply personal experience of his father’s battle with Alzheimer’s disease. “He took the news like a champ,” the author recalls. His novel, inspired by his father’s own illness, took a decade to complete and was an instant New York Times bestseller.
      • Videos
    • Behind the Book: We Are Not Ourselves
      • Profile
        • Learn more about We Are Not Ourselves at http://books.simonandschuster.com/We-&#8230; Simon & Schuster’s Editor-in-Chief, Marysue Rucci, reveals the incredible affect that reading We Are Not Ourselves had on her, and the decade long process that author Matthew Thomas went through to write the novel.
      • Videos
  3. Author Matthew Thomas in discussion with Misha Collins about Matthew’s first novel, “We Are Not Ourselves”. Event held on Sunday, 28 Sep at Barnes & Noble, The Grove at Farmers Market, L.A.
    • Videos
      • Video #1
        Channel :- luvjackson1
        Published On :- 2014-Sept-28th
        Link
  4. Matthew Thomas – We Are Not Ourselves
    • Profile
      • Authors Revealed 2014
    • Host :- Becky Anderson
    • Channel :- nctv17
    • Videos
  5. Matthew Thomas – We Are Not Ourselves
    • Profile
      • Author Matthew Thomas (We Are Not Ourselves) came to St. Francis College March 9, 2015 to help celebrate St. Patrick’s Day with a reading and discussion of his New York Times Bestselling book. The novel, about an Irish family in Queens, has a strong connection to St. Francis College, as Matthew’s family member Ronald Thomas ’64 is an alumnus of the College and several scenes in the book may have been inspired by Ronald’s time at St. Francis. About We Are Not Ourselves Born in 1941, Eileen Tumulty is raised by her Irish immigrant parents in Woodside, Queens, in an apartment where the mood swings between heartbreak and hilarity, depending on whether guests are over and how much alcohol has been consumed. When Eileen meets Ed Leary, a scientist whose bearing is nothing like those of the men she grew up with, she thinks she’s found the perfect partner to deliver her to the cosmopolitan world she longs to inhabit. They marry, and Eileen quickly discovers Ed doesn’t aspire to the same, ever bigger, stakes in the American Dream.
    • Channel :- St. Francis College
    • Videos
      • Video #1
        Published On :- 2015-March-23rd
        Link

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

Tom Holland – Critique of Religion

 

Videos

  1. Did the Quran come from God?
    • Profile :-
      • Historian Tom Holland interview
        Historian Tom Holland Author, ‘In the Shadow of the Sword’.
    • Channel :- Moneer Cherie
    • Date Published :- 2017-Nov-29th
    • Link
      Link
  2. Origins of Islam
    • Profile
      • Tom Holland Where did Islam come from? The story of how it came to be established across a vast empire stretching from the Atlantic to the frontiers of China is conventionally traced back to the charisma and inspiration of a single man: Muhammad. But his story was not written until 200 years later. Join historian Tom Holland who has received death threats for challenging the long-held origins of Islam.
    • Channel :- Rancho Mirage Writers Festival
    • Date Published :- 2017-Feb-17th
    • Link
      Link

 

In-depth

Did the Quran come from God?

  1. Source
    • God or Culture
      • Persian and Roman in 7th Century
      • Has stories borrowed from Roman, Christian folk tales
      • Christian
        • Moses
        • Virgin Mary
      • Alexander the Great
    • Why it matters
      • If not from God, then one has to understand it in human terms
      • Historian of Antiquity
      • Seem Rigor
      • If not becoming a Muslim and dealing in Theology and not in History
  2. Needs
    • The people of the time needed a backdrop of religion to bind together what was happening
    • Binding Philosophy
    • Anything that happens is seen as coming from God
    • Arabs said too themselves “What is going on
    • The process the writing got shaped into religion
  3. Christianity & Islam
    • Born out of same melting point
  4. In the West
    • 1970 and 1980s

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