Db/2 – Locking – DBeaver

Background

Let us see how to stimulate a database blocking situation via DBeaver.

Steps

Outline

  1. Query 1
    • Start a new query window
    • Set commit mode to Manual Commit ( primary )
      • Manual or Auto-Commit
        • Ensure that Mode is set for Manual Commit
      • Issue Database Statement
        • Make sure that statement is not committed/rollback via “Explicit” reference
  2. Query 2
    • Start a new query window ( secondary )
    • Set commit mode to Manual Commit
      • Manual or Auto-Commit
        • Ensure that Mode is set for Manual Commit
      • Issue Database Statement
        • Make sure that statement is not committed/rollback via “Explicit” reference
  3. Review Transaction Log
    • List uncommitted queries

 

Steps

  1. Review Commit Mode ( Manual and Auto-Commit Mode ? )
    • Issue a new query window
    • From the tool bar confirm transaction commit mode
    • Make sure is is set to manual commit
  2. Query Window – 01
    • Create new query window
    • Enter Query
    • Execute Query
    • Make sure query does not contain explicit commit nor rollback clause
  3. Query Window – 02
    • Create new query window
    • Enter Query
    • Execute Query
    • Make sure query does not contain explicit commit nor rollback clause
  4. Review Transaction Log
    • Invoke menu “Database/Transaction Log”
    • Transaction Log
      • The “Transaction Log” window opens
      • Review list of uncommitted transactions

 

Images

Manual/Auto Commit Mode

Auto-Commit Mode

transactionCommitMode.Auto.20181024.1029AM.PNG

Changing Mode

transactionCommitMode.Reviewing.20181024.1030AM.PNGl

Mode Changed to Manual

transactionCommitMode.Manual.20181024.1031AM.PNG

Query 1

Toolbar

toolbar.01.20181024.0953AM.PNG

Query Window – 01

Query Entered into Query Window 01

toolbar.02.setMode.20181024.0957AM.PNG

Query Executed against Query Window 01

toolbar.03.queryExecuted.20181024.0958AM.PNG

Query Window – 02

Query Entered into Query Window 02
toolbar.03.query02.BeforeExecution.20181024.0959AM.PNG
Query Executed against Query Window 02

toolbar.03.query02.AfterExecution.20181024.1000AM.PNG

Transaction Log

Review Transaction Log

transactionLog.02.20181024.1001AM.PNG

Db/2 LUW & DBeaver – Error – An error occurred during implicit system action type “2”

Background

Trying to run Explain on a Db/2 query, but running into an error.

Error

Error Image

SQLCode-104AndSQLCode-727.20181005.1124AM.PNG

Error Text

 An error occurred during implicit system action type "2". Information returned for the error includes SQLCODE "-104", SQLSTATE "42601" and message tokens "END-OF-STATEMENT|153875 FOR ORDER BY|JOIN ".. SQLCODE=-727, SQLSTATE=56098, DRIVER=4.21.29

Troubleshooting

Unselected Text

When we try to explain the Query without selecting the text we get the error pasted below:

Image

unselectedQuery.20181005.1133AM

Text


Empty query string

Remediation

Select Text

Please select the text you want explained and click the “Explain Execution Plan“.

queryEditor.selected.20181005.1130AM.PNG

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
      • Added in version v 9.7
      • 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 – 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

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

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

Background

On MS Windows, the first databases were simple database files such as DBase, Clipper, Paradox, Foxpro, Access, etc.

With the advent of the Client Servers architecture,  databases that started in the Unix area started moving down stream.

Prudent

It is prudent to take time and better understand how each database management system prefers to be shut-down.

In this exercise we will start tackling IBM’s DB2 LUW on MS Windows.

Outline

  1. List Db2 Instances
    • db2ilist
  2. List / Set Environment Variable
    • set db2
  3. List Connected Applications
    • CLI
      • db2 list applications
    • metadata
      • SYSIBMADM.APPLICATIONS
  4. Terminate Database Applications
    • db2 force application all
  5. DB2 Terminate
    • db2 terminate
  6. Stop License Daemon
    • db2licd -end
  7. Issue db2stop
    • db2stop
  8. db2diag
    • Review db2 log files

Steps

List Db2 Instances ( db2ilist )

Syntax


db2ilist

Sample


db2ilist

Output

db2ilist_20180912_0238PM.PNG

List / Set Environment Variable ( set db2 )

Syntax


set [environment-variable]

Sample


set db2

Output

envvar_set_20180912_0422PM.PNG

List Applications

CLI

db2 list applications
Syntax

db2 list applications

Sample

db2 list applications

Output
Output – “No Data Was Returned”
Image

db2ListApplications_20180913_0648AM.PNG

Textual

SQL1611W No data was returned by Database System Monitor.

Output – “List Applications #1”

Image
Textual

listApplications.20180913.0822AM.PNG


Auth Id Application Appl. Application Id DB # of
Name Handle Name Agents
-------- -------------- ---------- -------------------------------------------------------------- -------- -----
DADENIJI db2jcc_applica 462 10.11.203.114.63024.180913144347 SAMPLE 1

List Applications ( Using SYSIBMADM.APPLICATIONS )

SYSIBMADM.APPLICATIONS

Sample

SELECT 

          tblApp.AGENT_ID           AS "agentID"
        , tblApp.AUTHID             AS "authID"
        , tblApp.TPMON_CLIENT_APP   AS "clientApp"
        , tblApp.APPL_NAME          AS "Application"
        , tblApp.DB_NAME            AS "database"
        , tblApp.APPL_STATUS        AS "appStatus"
        , tblApp.CLIENT_PROTOCOL    AS "clientProtocol"
        , tblApp.CLIENT_NNAME       AS "clientName"
        , tblApp.NUM_ASSOC_AGENTS   AS "#ofAgents"
--      , tblApp.*

FROM   SYSIBMADM.APPLICATIONS tblApp

WHERE
        (

            ( tblApp.TPMON_CLIENT_APP IS NOT NULL )

        )

Output

SYSIBMADM.APPLICATIONS.20180913.1123AM..PNG

Terminate Application

Syntax


db2 force application ( appl-id-1, appl-id-2, appl-id-3, ...., appl-id=n)

Sample

Sample – Specific Application ID

db2 force application ( 462 )

Output

db2forceapplication_01.20180913.1134AM.PNG

Sample – All Applications

db2 force application all

DB2 Terminate

Syntax


db2 terminate

Sample


db2 terminate

Output

db2terminate_20180913_1137AM.PNG

db2stop

Syntax


db2stop

Sample


db2stop

Output

db2stop.20180913.1139AM

db2licd

Syntax


db2licd

Sample


db2licd -end

Output

db2licd.stop.20180913.1143AM.PNG

db2diag

Syntax


db2diag

Sample


db2diag

Output

Output – 01

db2diag_20180912_0432PM.PNG

Output – 02

db2diag_20180913_1146AM.PNG

References

  1. IBM
    • IBM Knowledge Center
      •  Home > Db2 11.1 > Database fundamentals > Applying fix packs > Applying fix packs in Db2 database environments > Preparing to install a fix pack
        • Stopping all Db2 processes (Linux and UNIX)
          Link
      • Home > Tivoli Storage Productivity Center 5.2.6 > Administering > Administering DB2
        • Manually stopping DB2 on Windows
          Link

DB/2 – LUW :- DDL & DML – Schema, Table & Adding Data

Background

Now that we have a sample database, let us start creating database objects and manipulating data.

Data Definition Language ( DDL )

Schema

government

syntax


CREATE SCHEMA [schema];

Sample


CREATE SCHEMA "government";

Table

government.city

Sample


DROP TABLE government.city;

create table "government"."city"
(

	  "state"  varchar(60) not null
	, "city"   varchar(60) not null

	, constraint "PK_Government_City"
	  primary key
	  (
		  "state"
		, "city"
	  )

)
;

Data Manipulation Language ( DML )

Table

Add data

Add data using INSERT/VALUES

Sample

/*
	DELETE
	FROM government.city
	WHERE 1=1
	;

*/
TRUNCATE TABLE "government"."city"
	REUSE STORAGE
    IGNORE DELETE TRIGGERS
    IMMEDIATE
    ;

insert into "government"."city"

( "state", "city")

VALUES
	  ( 'CA', 'San Francisco' )
	, ( 'CA', 'Sacramento' )
	, ( 'CA', 'Santa Clarita' )

	, ( 'GA', 'Augusta' )
	, ( 'GA', 'Atlanta' )
	, ( 'GA', 'Macon' )
	, ( 'GA', 'Savanna' )

	, ( 'NY', 'New York' )
	, ( 'NY', 'Sacramento' )

	, ( 'TX', 'Austin' )
	, ( 'TX', 'Dallas' )
	, ( 'TX', 'Houston' )
	, ( 'TX', 'San Antonio' )				


Add data using Table Function

Sample
/*

	DELETE
	FROM "government"."city"
	WHERE 1=1;

*/

TRUNCATE TABLE "government"."city"
    REUSE STORAGE
    IGNORE DELETE TRIGGERS
    IMMEDIATE
    ;

insert into "government"."city"
( "state", "city")

SELECT *

FROM TABLE
(

      VALUES
          ( 'CA', 'San Francisco' )
        , ( 'CA', 'Sacramento' )
        , ( 'CA', 'Santa Clarita' )

        , ( 'GA', 'Augusta' )
        , ( 'GA', 'Atlanta' )
        , ( 'GA', 'Macon' )
        , ( 'GA', 'Savanna' )

        , ( 'NY', 'New York' )
        , ( 'NY', 'Sacramento' )

        , ( 'TX', 'Austin' )
        , ( 'TX', 'Dallas' )
        , ( 'TX', 'Houston' )
        , ( 'TX', 'San Antonio' )               

)
;

Crediting

Insert multiple rows into DB2 database
Crediting Hogan & Esperento57
Link