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

AWS/RDS – Monitoring

Background

Wanted to take a quick look at our AWS/RDS SQL Server Instance.

The look will be solely focused on Performance Metrics.

Performance

Outline

  1. CloudWatch
  2. Enhanced Monitoring
  3. OS Process List

CloudWatch

Images

Image – CloudWatch – CPU utilization & DB Connections

cloudWatch_01_20180917_0240PM.PNG

Image – CloudWatch – Free Storage Space, Free-able Memory, IOPs

cloudWatch_02_20180917_0244PM.PNG

Explanation

CPU Utilization

  1. CPU Utilization is around 2 to 3 percentile

DB Connections

  1. 20 to 30 concurrent DB Connections

Free Storage Space  (MB )

  1. Free Storage is 200,000 MB
  2. Or 200 GB

Freeable Memory  (MB )

  1. Freeable Memory is 200 MB
Code
sys.dm_os_sys_memory – Code

 select
		  tblOSMem.total_physical_memory_kb
			 as [physicalMemKB]

		, ( tblOSMem.total_physical_memory_kb )
				/
		  (1000 * 1000)
			 as [physicalMemGB]

	    , tblOSMem.[available_physical_memory_kb]
			 as [availableMemKB]

		, ( tblOSMem.available_physical_memory_kb )
				/
		  (1000)
			 as [avialableMemMB]

		, tblOSMem.[system_memory_state_desc]

		--, tblOSMem.*

from   sys.dm_os_sys_memory  tblOSMem

 

sys.dm_os_sys_memory – Image

sys.dm_os_sys_memory_20180917_0345PM

 

Enhanced Monitoring

Images

Image – Enhanced – Memory

enhancedMonitoring_01_20180917_0247PM.PNG

Image – Enhanced – CPU, Available Disk Space, Total Disk Space

enhancedMonitoring_02_20180917_0247PM.PNG

 

OS Process List

Images

Image – Process List

OSMonitoring_ProcessList_20180917_0236PM.PNG

Explanation

  1. OS Processes :- 1.4 GB
  2. RDS Processes
    • SQLAgent.exe
    • fdlauncher.exe
    • sqlserver.exe

 

 

Joe Amaral :- Jesus Writing in the Soil

Background

Listening in one of Joe Amaral’s interview.

In this particular segment he offers his take on why Jesus wrote in the sand.

Video

Here is a link to the video :-

Understanding Why Jesus Wrote in the Sand
Crossroads CEO Dr. John Hull talks to Joe Amaral about how Jesus did some unusual things but if you understand the culture it makes sense and has deep meaning.
Link

Bible Verses

Woman & the Woman Caught in Adultery

John 8:3-11 King James Version (KJV)
Link

  1. They say unto him, Master, this woman was taken in adultery, in the very act.
  2. Now Moses in the law commanded us, that such should be stoned: but what sayest thou?
  3. This they said, tempting him, that they might have to accuse him. But Jesus stooped down, and with his finger wrote on the ground, as though he heard them not.
  4. So when they continued asking him, he lifted up himself, and said unto them, He that is without sin among you, let him first cast a stone at her.
  5. And again he stooped down, and wrote on the ground.
  6. And they which heard it, being convicted by their own conscience, went out one by one, beginning at the eldest, even unto the last: and Jesus was left alone, and the woman standing in the midst.

 

They that depart me from me shall be written in the earth

Jeremiah 17:13 King James Version (KJV)
Link

13 Lord, the hope of Israel, all that forsake thee shall be ashamed, and they that depart from me shall be written in the earth, because they have forsaken the Lord, the fountain of living waters.

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

Forensic Files

Videos

  1. Shattered Shield
    • Videos
      • YouTube
        • The FBI Files: Season 2 – Episode 8 “Shattered Shield”
          Published On :- 2014-Nov-27th
          Link
    • Persons
      • Police
        • Richard Pennington, New Orleans Police Chief
        • Sammy Williams ( Police )
        • Len Davis ( Police )
      • Community
        • Kim Groves
    • Location :- New Orleans, Louisiana
    • Stories
      • The New Orleans Advocate
        • A murder 20 years ago marked low point for NOPD
          Link
      • NOLA
        • Len Davis, eight other New Orleans police officers, charged in drug sting
          Link
    • Videos
      • City Confidential-New Orleans: Betrayal in the Big Easy
        Link
      • Woman who lost mother to NOPD murder for hire ‘We need to get better’ wwltv com New Orleans
        Link
  2. Sign Here
    • Season 8, Episode 4
    • Profile
      • A mother of two disappears after a shopping trip. Her body is found a month later. Witnesses saw the victim being forced into a car by an unknown person. Police learn that the car had been rented, but the signature on the rental agreement does not match that of their number one suspect. A forensic handwriting expert points to the murderer. Originally aired as Season 8, Episode 4.
    • Videos
      • YouTube
        • Forensic Files – Season 8, Ep 4: Sign Here
          Published On :- 2015-March-7th
          Link
    • Participants
      • Dally
        • Sharri Dally
        • Michael Dally
      • Diana Haun
    • Location :- Los Angeles
  3. Who is your Daddy?
    • Season 7, Episode 4
    •  Profile
      • In 1988, the body of a young woman was discovered in an Ohio river under ice. Most of the evidence found at the crime scene — like hairs, fibers and fingerprints — had been washed away. But the victim’s six year-old son unwittingly told investigators all they needed to know about the killer without saying single a word. The clue was in his genes. Originally aired as Season 7, Episode 4.
    •  Videos
      • YouTube
        • Forensic Files – Season 7, Ep 4: Who’s Your Daddy?
          Published On :- 2015-March-7th
          Link
    • Participants
      • Margie
        • Brandon
    • Location :-Butler, OHIO
  4. Linda Sobek
    • In 1995, California model Linda Sobek goes missing. Some vital pieces of information are found in a dumpster, which eventually led investigators to professional photographer Charles Rathbun. Rathbun claims Sobek died during a consensual sexual encounter gone wrong, but Sobek’s corpse and some high tech digital imagery tell a more sinister story. Originally aired as Season 6, Episode 11.
    •  Videos
      • YouTube
        • Forensic Files – Season 6, Ep 11: Photo Finish
          Published On :- 2016-July-8th
          Channel :- FilmRise
          Link
    • Participants
      • Linda Sobek
      • Charles Rathbun
    • Location :- Southern California ( SoCAL)
  5. Olamide Adeyoye
    • Videos
      • YouTube
        • Forensic Files 13×35 Covet Thy Neighbor
          Published On :- 2015-March-7th
          Link
    • Location :- Illinois
  6. Lucious Boyd
    • Videos
      • YouTube
        • Listing :- Season 13, Episode 18: Church Dis-service
        • Profile
          • A young woman attends evening church services… then disappears. When her abandoned car is found, the tank is empty and a gas can she kept in her trunk for emergencies is missing. Eyewitnesses place her at a nearby gas station, getting into a van, but their descriptions of the vehicle don’t match. Three days later when her body is discovered, the search for the driver of that van intensifies. Originally aired as Season 13, Episode 18.
        • Videos
          • Forensic Files in HD – Season 13, Ep 18: Church Dis-service
            Channel :- Forensic Files
            Published On :- 2014-Oct-16th
            Link
  7. Forensic Files – Season 5, Episode 17 : Journey to Justice
    Link
  8. Telltale Tracks
    • Forensic Files – Season 7, Ep 20: Telltale Tracks
    • Season 7, Episode 20
    •  Videos
      • YouTube
    • Participants
      • Arthur Boman
      • Aimee Willard
      • Dale Yeager ( Criminal Analyst )
    • Location
      • Philadelphia, Pennsylvania
  9. Badge of Betrayal
    • Profile
      • Forensic Files – Season 9, Episode 18: Badge of Betrayal
      • Season 9, Episode 18
    •  Videos
      • YouTube
  10. Summer Obsession
    • Profile
      • Forensic Files – Season 9, Episode 18: Badge of Betrayal
      • Season 10, Episode 27
    •  Videos
      • YouTube
  11. Elemental Clue
    • Profile
      • Forensic Files – Season 10, Episode 28: Elemental Clue
      • Season 10, Episode 28
    •  Videos
      • YouTube
    • Participants
      • Jensen
        • Arlene Jensen
        • Julie Jensen
      • Gary Ackley
  12. Moss, Not Grass
    • Profile
      • Forensic Files – Season 10, Ep 29: Moss, Not Grass
      • Season 10, Episode 29
    •  Videos
      • YouTube
    • Participants
      • Samantha Forbes
      • Marjorie
      • Keith Lockmore
      • Dominique Moss
    • Stories
      • The Nassau Guardian
        • Convicted killer released from prison
          Link
  13. House Call
    • Profile
      • Forensic Files – Season 7, Ep 38: House Call
      • Season 7, Episode 38
      • A murder investigation in St. Petersburg, Florida, crosses jurisdictions from New York and Jamaica. The police rely heavily on cell phone mapping, wiretapping and a host of forensic evidence to reveal a suspect to the crime. Originally aired as Season 7, Episode 38.
    •  Videos
      • YouTube
    • Participants
      • Davidson
        • Dr. Davidson
        • Denise Davidson
          • Pauline White
      • Robert Gordon
      • Meryl McDonald
      • Leo Cisneros
  14. Watchful Eye
    • Profile
      • Forensic Files in HD – Season 13 Ep 45: Watchful Eye
      • Season 13, Episode 45
      • A murder investigation in St. Petersburg, Florida, crosses jurisdictions from New York and Jamaica. The police rely heavily on cell phone mapping, wiretapping and a host of forensic evidence to reveal a suspect to the crime. Originally aired as Season 7, Episode 38.
    •  Videos
      • YouTube
  15. All that Glitters is Gold
    • Profile
      • Forensic Files – Season 13, Ep 32: All That Glitters is Gold
      • Season 13, Episode 32
      • A bullet-riddled car, a missing driver and no witnesses. Was this an ambush or a random attack? Had the victim been taken or was she dead? The answers lay in one special clue, so tiny it was measured in millionths of a meter. Originally aired as Season 13, Episode 32.
    •  Videos
      • YouTube
    • Participants
      • Megan Barroso
      • Vincent Sanchez, Simi Valley Rapist
    • Stories
      • LA Times
        • Simi Valley Rapist Is Convicted of Kidnapping, First-Degree Murder
          Link
  16. The Day the Music Died
    • Profile
      • Forensic Files – Season 12, Episode 7 – The Day the Music Died
      • Season 12, Episode 7
      • Seattle police had no suspects in the violent murder of a popular rock singer. More than a decade would pass before the evidence collected by an extraordinarily prescient medical examiner could be used by forensic scientists to identify the killer. Originally aired as Season 12, Episode 7.
    •  Videos
      • YouTube
  17. Quite a spectacle
    • Profile
      • Forensic Files – Season 12, Episode 5 – Quite a spectacle
      • Season 12, Episode 5
      • Police in Canada received a chilling 911 call from a woman who was just attacked in her apartment, but by the time they arrived she was dead. Little evidence remained at the scene, except for a pair of eyeglasses and a shoeprint in a squished tomato. With the help of Canada’s only forensic optometrist, police put away a killer who is larger than life. Originally aired as Season 12, Episode 5.
    •  Videos
      • YouTube
  18. Skeleton Key
    • Profile
      • Forensic Files – Season 14, Episode 19 – Skeleton Key
      • Season 14, Episode 19
      • In 2004, nursing student Tamika Huston went missing from her Spartanburg, South Carolina home. A tip guides investigators to her car where they find an unknown house key that could help solve the case. Originally aired as Season 14, Episode 19.
    •  Videos
      • YouTube
    • Participants
      • Tamika Huston
  19. A welcome intrusion
    • Profile
      • Forensic Files – Season 8, Episode 14,  A Welcome Intrusion
      • Season 8, Episode 14
      • A man tells police he shot an intruder who had attacked and murdered his wife. The husband paints a tragic picture of harassment, stalking and revenge, and is dubbed a hero for his valiant attempt to save his wife. Four years later, new forensic evidence leads police to re-examine the motives of this so-called hero. Originally aired as Season 8, Episode 14.
    •  Videos
      • YouTube
    • Participants
      • Mark Winger
      • DeeAnn Shultz
    • Comments
      • The story of Mark Winger, doesn’t end there, he got an additional 35 years in 2007, for trying to solicit another inmate to hire a hitman to kill DeeAnn Shultz (the main witness against him) along with his boyhood friend Jeffrey Gelman (who refused to post his bail following his arrest in 01). He even asked for one more if possible. “Oh by the way, if there’s any money left over, kill Ira Dreschser (Donnah’s stepfather who kept writing him letters in prison) also because he’s a son of a gun of a father in law that I dislike.”
  20. Three’s a Crowd
    • Profile
      • Forensic Files in HD – Season 14 Ep 4: Three’s a Crowd
      • Season 14, Episode 4
      • As she left choir practice, the woman was gunned down in the church parking lot. Her husband became the prime suspect — particularly when police learned he found out just a month earlier that his wife had been cheating on him for three years. Originally aired as Season 14, Episode 4.
    •  Videos
      • YouTube
    • Participants
      •  Fassett
        • Susan Fassett
        • Jeff Fassett
        • Jason Fassett
      • Fred Andros
      • Dawn Silvernail
      • William Phelps
    • Stories
      • John Sessoms
        • Missing from the episode is the back story. Fred Andros was being investigated for kickbacks & bribery by the FBI; part of a larger pay-to-play corruption investigation in Dutchess County, NY aimed at the local political boss. Andros was the political boss’s “bag man”. Andros was negotiating a plea deal to testify against the local political boss in return for a reduced sentence.
          Another potential witness against the boss turned up dead in the Hudson River as an apparent “suicide” after being approached by the FBI. Susan Fassett ran the town of Poughkeepsie’s personnel department & was a potential witness against Andros who could blow his plea deal by revealing his greater culpability in the corruption scandal.The political boss later got off with a $24,000 fine & 18 months in prison. Dawn Silvernail will be eligible for parole in 2017.
  21. Runaway Love
    • Profile
      • Forensic Files in HD – Season 13 Episode 43
      • Season 13, Episode 43
      • It was classic “overkill.” The woman floating in the harbor of an upscale yacht club had been stabbed more than 50 times. Her ex-husband and daughter are missing, and investigators turn to forensic science to determine if they’re searching for victims… or perpetrators. In the end, Rachael Mullenix, 19, is convicted of stabbing her mother, Barbara Mullenix, on Sept. 13, 2006, and then packing her bloodied corpse in a cardboard box before dumping it in Newport Bay on the California coast. Originally aired as Season 13, Episode 43.
    •  Videos
      • YouTube
    • Participants
      • Mullenix
        • Barbara
        • Bruce
        • Rachael
  22. Deadly Knowledge
    • Profile
      • Forensic Files in HD – Season 5, Episode 19
      • Season 5, Episode 19
      • The investigation into a missing college coed leads police to discover that, unbeknownst to her friends, she lived a bizarre double life, attending school by day and working as a call-girl by night. When her body is found, investigators gather evidence that implicates her killer. Originally aired as Season 5, Episode 19.
    •  Videos
      • YouTube
    • Participants
      • Tina
      • Todd

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