How to get Data and index hit ration

with bpsnap as
(
select substr(bp_name,1,20) as bp_name,
float(pool_data_p_reads) as dp_read,
float(pool_data_l_reads) as dl_read,
float(pool_index_p_reads) as ip_read,
float(pool_index_l_reads) as il_read
from sysibmadm.snapbp
)
select bp_name,
dec((1 -(dp_read / dl_read))*100,4,1) as "Data Hit Ratio",
dec((1 -(ip_read / il_read))*100,4,1) as "Index Hit Ratio"
from bpsnap
where dl_read > 0 and il_read > 0
;

How to get status of any utility ( reorg,backup,load) running on DB2.

How to get status of any utility ( reorg,backup,load) running on DB2.

SELECT
          substr(char(UTILITY_START_TIME),1,19) as start_time /*==> TIMESTAMP 10,0 */
          ,u.DBPARTITIONNUM as dp#
          ,substr(u.UTILITY_TYPE,1,12) as UTILITY_TYPE
          ,u.UTILITY_STATE /*==> VARCHAR 16,0 */
          ,char(PROGRESS_DESCRIPTION,10) as prog /*==> VARCHAR 2048,0 */
          ,PROGRESS_TOTAL_UNITS  as tot_units /*==> BIGINT 8,0 */
          ,PROGRESS_COMPLETED_UNITS as tot_comp_units /*==> BIGINT 8,0 */
          ,TIMESTAMPDIFF(4,CHAR(TIMESTAMP(current timestamp)-TIMESTAMP(PROGRESS_START_TIME))) AS elp_min
          ,substr(UTILITY_DESCRIPTION,1,120) as desc
        FROM SYSIBMADM.SNAPUTIL u
        LEFT OUTER JOIN SYSIBMADM.SNAPUTIL_PROGRESS up
        on u.UTILITY_ID = up.UTILITY_ID
        and u.DBPARTITIONNUM = up.DBPARTITIONNUM
        order by u.DBPARTITIONNUM, u.UTILITY_TYPE, u.UTILITY_STATE
        for fetch only with ur

Reorg History

How to get history of reorg on a particular table

SELECT
        smallint(DBPARTITIONNUM) as PARTITION,
        CASE OPERATIONTYPE
         WHEN 'F' THEN 'OFFLINE'
         WHEN 'N' THEN 'ONLINE'
        END as REORG_TYPE,
        substr(char(TIMESTAMP(START_TIME)),1,19) AS START_TIME,
        substr(char(TIMESTAMP(END_TIME)),1,19) AS END_TIME,
        TIMESTAMPDIFF(4,CHAR(TIMESTAMP(END_TIME)-TIMESTAMP(START_TIME))) as "ELAPSED(MIN)",
        coalesce(SQLCODE,0) as SQLCODE,
        CASE WHEN SQLCODE < 0  THEN 'FAILED' ELSE 'SUCCEEDED' END as STATUS
        FROM
          sysibmadm.db_history as a
          WHERE OPERATION = 'G'
            AND (TIMESTAMP(START_TIME) + 5 DAYS) >
                CURRENT TIMESTAMP
            AND TABSCHEMA = 'SCHEMA'
            AND TABNAME = 'TAB'
          ORDER BY START_TIME, PARTITION
        for fetch only with ur;

How to reducing the number of partitions in DB2

Steps to be followed for reducing the number of partitions :

Step 1 ) ALTER DATABASE PARTITION GROUP

When a database partition is dropped from a database partition group, the catalog entry for the database partition (see SYSCAT.DBPARTITIONGROUPDEF) is updated. If there are no tables defined in the table spaces defined in the database partition group, the distribution map is changed immediately to exclude the dropped database partition and the entry for the database partition in the database partition group is dropped. If tables exist, the distribution map is not changed and the indicator (IN_USE) is set to indicate that the database partition is waiting to be dropped. The REDISTRIBUTE DATABASE PARTITION GROUP command must be used to redistribute the data and drop the entry for the database partition from the database partition group.

USAGE:

ALTER DATABASE PARTITION GROUP db-partition-name DROP DBPARTITIONNUMS (9,10,11,12,13,14,15,16);

Step 2 ) REDISTRIBUTE DATABASE PARTITION GROUP

Redistributes data across the database partitions in a database partition group. The current data distribution, whether it is uniform or skewed, can be specified. The redistribution algorithm selects the partitions to be moved based on the current data distribution.

Parameters for redistribute DB partition group command :

DATABASE PARTITION GROUP database partition group

The name of the database partition group. This one-part name identifies a database partition group described in the SYSCAT.DBPARTITIONGROUPS catalog table. The database partition group cannot currently be undergoing redistribution.

Note:

Tables in the IBMCATGROUP and the IBMTEMPGROUP database partition groups cannot be redistributed.

UNIFORM

Specifies that the data is uniformly distributed across hash partitions (that is, every hash partition is assumed to have the same number of rows), but the same number of hash partitions do not map to each database partition. After redistribution, all database partitions in the database partition group have approximately the same number of hash partitions

CONTINUE

Continues a previously failed REDISTRIBUTE DATABASE PARTITION GROUP operation. If none occurred, an error is returned.

ROLLBACK

Rolls back a previously failed REDISTRIBUTE DATABASE PARTITION GROUP operation. If none occurred, an error is returned.

When a redistribution operation is done, a message file is written to:

·         The /sqllib/redist directory on UNIX based systems, using the following format for subdirectories and file name: database-name.database-partition-group-name.timestamp.

This utility performs intermittent COMMITs during processing. This can cause type 2 connections to receive an SQL30090N error.

All packages having a dependency on a table that has undergone redistribution are invalidated. It is recommended to explicitly rebind such packages after the redistribute database partition group operation has completed. Explicit rebinding eliminates the initial delay in the execution of the first SQL request for the invalid package. The redistribute message file contains a list of all the tables that have undergone redistribution.

It is also recommended to update statistics by issuing RUNSTATS after the redistribute database partition group operation has completed.

Before starting a redistribute operation, ensure there are no tables in the Load Pending state. Table states can be checked by using the LOAD QUERY command. If you discover data in the wrong database partition as a result of a redistribute operation, there are two options. You can:

unload the table, drop it and then reload the table, or

use a new target map to redistribute the database partition group again.

USAGE:

REDISTRIBUTE DATABASE PARTITION GROUP UNIFORM;

Step 3) db2 force application all

Step 4) export db2node=<partition number to drop>

Step 5 ) DROP DBPARTITIONNUM VERIFY

Verifies if a database partition exists in the database partition groups of any databases, and if an event monitor is defined on the database partition. This command should be used prior to dropping a database partition from a partitioned database system.

This command only affects the database partition on which it is issued.

If a message is returned, indicating that the database partition is not in use, use the STOP DATABASE MANAGER command with DROP DBPARTITIONNUM to remove the entry for the database partition from the db2nodes.cfg file, which removes the database partition from the database system.

If you recieve RC=SQL6034W (Node not used in any database),

Step 6)  Unset DB2NODE

Step 7 ) STOP DATABASE MANAGER

On partitioned database system, this command stops the current database manager instance on a database partition or on all database partitions. When it stops the database manager on all database partitions, it uses the db2nodes.cfg configuration file to obtain information about each database partition.

This command can also be used to drop a database partition from the db2nodes.cfg file (partitioned database systems only).

By default, and in a partitioned database environment, this command affects all database partitions that are listed in the db2nodes.cfg file.

DROP DBPARTITIONNUM db-partition-number

partitioned database systems only. Specifies the database partition to be dropped from the db2nodes.cfg file.

Before using this parameter, run the DROP DBPARTITIONNUM VERIFY command to ensure that there is no user data on this database partition.

When this option is specified, all database partitions in the db2nodes.cfg file are stopped.

If you recieve RC=SQL6035W (Node in use by database)

1. Redistribute the data again ( Perform Step 2 again)

2. Drop any associated event monitors

3. Repeat the steps to drop the partition  ( Repeat Step 3 to 7 ).

IBM DB2 HADR Example

IBM DB2 HADR Example

Here is an HADR configuration example that I did when preparing for my DBA certification. It has being awhile when I performed these steps, so bear with me if there are any mistakes or errors in the description.



DB2(R) high availability disaster recovery (HADR) is a data replication feature that provides a high availability solution for both partial and complete site failures. HADR protects against data loss by replicating data changes from a source database, called the primary, to a target database, called the standby.



This practice assumes two DB2 instances, named DB2INST1 and DB2INST2 resides in the same physical machine.

For configuring HADR, you will need few pieces of information.

Update DB2 Registry Profile
DB2 Communication Protocol (DB2COMM)

Update Each Instance DBM Configuration
DB2 TCPIP Listening Port (SVCENAME)

Update Primary DB Archical Logging Configuration
Log Pages During Index Rebuild (LOGINDEXBUILD)
Index Creation Time (INDEXREC)
Retain Log (LOGRETAIN)

Update Each DB HADR Configuration
Local IP Address (HADR_LOCAL_HOST)
Local Service Name/Port (HADR_LOCAL_SVC)
Remote IP Address (HADR_REMOTE_HOST)
Remote Service Name/Port (HADR_REMOTE_SVC)
Remote DB2 Instance Name (HADR_REMOTE_INST)
Synchronization Mode (HADR_SYNCMODE)


--------------------------------------------------------------------------------


For the intent of all purposes, I installed a fresh copy of DB2 ESE version 9 and chooose NOT to create the default DB2 instance. Windows environment is assumed.


Step 1: Create 2 DB2 Instances

Open up an instance of DB2 Command Window by typing "db2cmd" in Run dialog.

Create Instance 1 by typing "db2icrt DB2INST1"

Create Instance 1 by typing "db2icrt DB2INST2"


Step 2: Configure Instance 1

Type the following commands:

SET DB2INSTANCE=DB2INST1
db2set DB2COMM=tcpip
db2 update dbm cfg using SVCENAME 41001
db2start


Note: The TCPIP service port for this instance is 41001

Step 3: Configure Instance 2

Type the following commands:

SET DB2INSTANCE=DB2INST2
db2set DB2COMM=tcpip
db2 update dbm cfg using SVCENAME 42001
db2start

Note: The TCPIP service port for this instance is 42001


Step 4: Create the Primary DB in Instance 1

Type the following commands:

SET DB2INSTANCE=DB2INST1
db2 create db BANKFRS
db2 update db cfg for BANKFRS using LOGINDEXBUILD on
db2 update db cfg for BANKFRS using INDEXREC RESTART
db2 update db cfg for BANKFRS using LOGRETAIN on

Note: This step will turn the DB into archival logging mode


Step 5: Create a backup of Primary DB

Type the following commands:

db2 BACKUP DB BANKFRS TO C:\TEMP

Note: This step will backup the BANKFRS db into C:\TEMP


Step 6: Restore the backup BANKFRS as Standby database

Type the following commands:

SET DB2INSTANCE=DB2INST2
db2 RESTORE DB BANKFRS FROM C:\TEMP

Note: This step will restore the database as BANKFRS in the second DB2 instance and place it into roll forward pending state.

Note: If you roll forward the BANKFRS now, you will not be able to initialize it as the standby database.


Step 7: Configure the HADR settings for the Primary DB in DB2INST1

Type the following commands:

SET DB2INSTANCE=DB2INST1
db2 update alternate server for database BANKFRS using hostname localhost port 42001
db2 update db cfg for BANKFRS using hadr_local_host localhost
db2 update db cfg for BANKFRS using hadr_local_svc 41005
db2 update db cfg for BANKFRS using hadr_remote_host localhost
db2 update db cfg for BANKFRS using hadr_remote_svc 42005
db2 update db cfg for BANKFRS using hadr_remote_inst DB2INST2
db2 update db cfg for BANKFRS using hadr_syncmode SYNC
db2 update db cfg for BANKFRS using hadr_timeout 120

Note: Primary BANKFRS HADR service port will be 41005.
Note: Standby BANKFRS HADR service port will be 42005.
Warning: You cannot use the Instance TCPIP port (SVCENAME) or next port number (SVCENAME+1) as the HADR service port. This is by design.


Step 7: Configure the HADR settings for the Primary DB in DB2INST1

Type the following commands:

SET DB2INSTANCE=DB2INST1
db2 update alternate server for database BANKFRS using hostname localhost port 41001
db2 update db cfg for BANKFRS using hadr_local_host localhost
db2 update db cfg for BANKFRS using hadr_local_svc 42005
db2 update db cfg for BANKFRS using hadr_remote_host localhost
db2 update db cfg for BANKFRS using hadr_remote_svc 41005
db2 update db cfg for BANKFRS using hadr_remote_inst DB2INST1
db2 update db cfg for BANKFRS using hadr_syncmode SYNC
db2 update db cfg for vusing hadr_timeout 120

Note: Primary BANKFRS HADR service port will be 41005.
Note: Standby BANKFRS HADR service port will be 42005.
Warning: You cannot use the Instance TCPIP port (SVCENAME) or next port number (SVCENAME+1) as the HADR service port. This is by design.



--------------------------------------------------------------------------------


Now, you are ready to test the configuration.


Step 8: Start HADR on Standby DB IN DB2INST2

SET DB2INSTANCE=DB2INST2
db2 START HADR ON DB BANKFRS AS STANDBY


Step 9: Start HADR on Primary DB IN DB2INST1

SET DB2INSTANCE=DB2INST1
db2 START HADR ON DB BANKFRS AS PRIMARY


Step 10: Take over the Primary Role to Standby Instance

SET DB2INSTANCE=DB2INST2
db2 TAKEOVER HADR ON DB BANKFRS


Step 11: Take back the Primary Role from Standby Instance

SET DB2INSTANCE=DB2INST1
db2 TAKEOVER HADR ON DB BANKFRS

Step 12: Stop the HADR service on Primary DB

SET DB2INSTANCE=DB2INST1
db2 STOP HADR ON DB BANKFRS


Step 13: Stop the HADR service on Standby DB

SET DB2INSTANCE=DB2INST2
db2 deactivate db BANKFRS
db2 STOP HADR ON DN BANKFRS



General Notes:

You can invoke the HADR wizard from the DB2 Control Center by right clicking on the specific database and select "High Availability and Disaster Recovery" -> Setup

You can monitor the HADR status on the database by using the database snapshot monitor. "db2 get snapshot for database on BANKFRS"