Sort common DB2 scripts

Sort common DB2 scripts

-- Tablespace resizing

Alter tablespace BAS_DW_ENT

ADD (Device '/dev/vx/rdsk/n01datadg3/dwentlv_100g_01_01' 3276800) on dbpartitionnum (1)

ADD (Device '/dev/vx/rdsk/n02datadg3/dwentlv_100g_02_02' 3276800) on dbpartitionnum (2)

ADD (Device '/dev/vx/rdsk/n03datadg3/dwentlv_100g_03_03' 3276800) on dbpartitionnum (3)

ADD (Device '/dev/vx/rdsk/n04datadg3/dwentlv_100g_04_04 '3276800) on dbpartitionnum (4)

ADD (Device '/dev/vx/rdsk/n05datadg3/dwentlv_100g_05_05' 3276800) on dbpartitionnum (5)

ADD (Device '/dev/vx/rdsk/n06datadg3/dwentlv_100g_06_06' 3276800) on dbpartitionnum (6)

ADD (Device '/dev/vx/rdsk/n07datadg3/dwentlv_100g_07_07' 3276800) on dbpartitionnum (7)

ADD (Device '/dev/vx/rdsk/n08datadg3/dwentlv_100g_08_08' 3276800) on dbpartitionnum (8)

ADD (Device '/dev/vx/rdsk/n09datadg3/dwentlv_100g_09_09' 3276800) on dbpartitionnum (9)

ADD (Device '/dev/vx/rdsk/n10datadg3/dwentlv_100g_10_10' 3276800) on dbpartitionnum (10)

ADD (Device '/dev/vx/rdsk/n11datadg3/dwentlv_100g_11_11' 3276800) on dbpartitionnum (11)

ADD (Device '/dev/vx/rdsk/n12datadg3/dwentlv_100g_12_12' 3276800) on dbpartitionnum (12)

ADD (Device '/dev/vx/rdsk/n13datadg3/dwentlv_100g_13_13' 3276800) on dbpartitionnum (13)

ADD (Device '/dev/vx/rdsk/n14datadg3/dwentlv_100g_14_14' 3276800) on dbpartitionnum (14 );

Db2 list tablespaces show detail;

Db2 list tablespace containers for 42

-- Tablespace resizing

Alter tablespace BAS_MK_APP

ADD (Device '/dev/n0datavg6/rmkapplv_100g_0_01/' 3276800) on dbpartitionnum (0)

ADD (Device '/dev/n1datavg6/rmkapplv_100g_000002/' 3276800) on dbpartitionnum (1)

ADD (Device '/dev/n2datavg6/rmkapplv_100g_2_03/' 3276800) on dbpartitionnum (2)

ADD (Device '/dev/n3datavg6/rmkapplv_100g_3_04/' 3276800) on dbpartitionnum (3 );

-- View table space usage

Db2 "select substr (tbsp_name, 1024) tbsp_name, tbsp_type, substr (tbsp_state,) tbsp_state, tbsp_total_size_kb/tot_size,

Tbsp_free_size_kb/1024 free_size, tbsp_utilization_percent used_pect, TBSP_USING_AUTO_STORAGE, TBSP_PAGE_SIZE/1024 PGSIZE, DBPARTITIONNUM

From sysibmadm. tbsp_utilization where tbsp_total_size_kb/1024> 1024 and tbsp_utilization_percent> 90 order by DBPARTITIONNUM with ur"

-- Check the space status

Db2 "select tbsp_id, substr (tbsp_name,) tbsp_name, substr (tbsp_state,) tbsp_state, tbsp_utilization_percent, dbpartitionnum from sysibmadm. tbsp_utilization with ur"

Db2 list tablespaces show detail -- check the status of the tablespace on a single partition. Normally, 0x0000 is returned.

Db2_all "db2 list tablespaces show detail" -- view the tablespace status on all partitions

Db2tbst 0x0000

State = Normal

-- View tablespace Information

Db2 list tablespaces

Show detail

Db2 list tablespace containers for 0

-- Check whether there are available pages in the DMS tablespace.

$ Db2 list tablespaces show detail -- check whether there are available pages in the tablespace on a single Partition

$ Db2_all "; db2 connect to qhbidb; db2 list tablespaces show detail" -- check whether the tablespace has available pages on all partitions.

-- Obtains the high watermark information of a tablespace.

Db2 "SELECT varchar (tbsp_name, 16) as tbsp_name, RECLAIMABLE_SPACE_ENABLED, TBSP_USED_PAGES,

TBSP_FREE_PAGES, TBSP_PAGE_TOP from TABLE (MON_GET_TABLESPACE ('',-2) AS t

Where t. TBSP_PAGE_TOP> t. TBSP_USED_PAGES"

>-Alter tablespace -- tablespace-name ---------------------------->

> ---- +-REDUCE -- + ------------------------------- + -- + ----------------------------- +-+

+-

Database-container-clause

-+ '-

On-db-partitions-clause

-'

+-

All-containers-clause

----- +

+-MAX --------------------------- +

+-STOP -------------------------- +

'-Integer -- + --------- + ----------'

+-K ------- +

+-M ------- +

+-G ------- +

'-PERCENT -'

+-Lower high water mark -- + ------ + ---------------------------------------------- +

'-STOP -'

'----------------------------------------------------------------------------'

This section describes two clauses related to the recovery of tablespace storage and Their syntax. The MAX parameter can be used to specify the mark for minimizing the high level to free up space. After running the REDUCE command, when the data block is moved,

You can also use the STOP parameter to STOP table space movement. For DMS tablespaces, run the lower high water mark clause to LOWER the high water mark, and then run the REDUCE clause to release the tablespace.

If the Automatic Storage tablespace is enabled, run the REDUCE clause directly.

Listing 7. Automatic Storage of tablespaces

Alter tablespace reduce 10 M List 8. DMS TABLESPACE

ALTER TABLESPACE LOWER HIGH WATER MARK

Alter tablespace reduce (all containers 10 M)

-- Database size

Db2 "call get_dbsize_info (?,?,?, -1 )"

-- Database load history file cleanup

-- We recommend that you regularly clean/dbhome/qhbiinst/NODE0000/SQL00001/db2rhist. asc. This file is too large to cause performance problems in the LOAD operation.

-- For example, clear historical file records of nodes before January 1, October.

Export DB2NODE = 1

Db2 terminate

Db2 connect to qhbidb

Db2 prune history 201410

-- Fault Log Capture

Cd/db2diaglog/db2dump

-- Take down all the contents of the FODC_Trap_YYYY_MM_DD-HH.MI.SS.MS folder

Db2diag-t 2014-11-30-23.00.00: 2014-12-01-03.00.00> diag_201441201.log

Db2diag-t 2015-8-18-level "Severe, Error"

-- View the size of the historical load File

Du-sm/db2data/qhbiinst/NODE0000/SQL00001/db2rhist *

Awk '{a + = $0} END {print a "MB "}'

Du-sm/db2data/qhbiinst/NODE000 [1-8]/SQL00001/db2rhist *

Awk '{a + = $0} END {print a "MB "}'

Du-sm/db2data/qhbiinst/NODE000 [9-16]/SQL00001/db2rhist *

Awk '{a + = $0} END {print a "MB "}'

Du-sm/db2data/qhbiinst/NODE000 [17-24]/SQL00001/db2rhist *

Awk '{a + = $0} END {print a "MB "}'

Du-sm/db2data/qhbiinst/NODE000 [1-8]/SQL00001/db2rhist *

Awk '{a + = $0} END {print a "MB "}'

Du-sm/db2data/qhbiinst/NODE000 [1-8]/SQL00001/db2rhist *

Awk '{a + = $0} END {print a "MB "}'

-- Check the database running status of each node

$ Db2_all "db2pd-db qhbidb -"

-- If the status of some nodes is abnormal, you can start the database as follows:

$ Db2start dbpartitionnum nodenum

-- View the table status

Db2 "select tabname, colcount, status from syscat. tables where tabschema not like 'sys % 'order by tabname"

Db2 load query table test

-- DB2 Status Monitoring

$ Db2_ps

$ Db2gcf-u-p 0-I qhbiinst

Instance: qhbiinst

DB2 Start: Success

Partition 0: Success

Db2 deactivate database name ----------- activate the database

Db2 activate database name ----------- activate database

Connect reset ------------- disconnect the current database

Db2 restart database databasename ------------- restart the database

-- Two backup methods

--- Offline database backup requires an exclusive connection to the database, which backs up all tablespaces in the database;

Backup db sample to c: \ backup with 3 buffers buffer 1000 without prompting

--- Online database backup: You can back up the entire database or a single tablespace.

Backup db sample tablespace (userspace1) online to/dev/rmt0 without prompting

-- Online backup is required to ensure that the database parameter logretain = on or logarchmetd1 is set to support online backup.

-- View the transaction of the Repository

Db2pd-d qhbidb-transactions

Db2pd-d qhbidb-alldbp-apinfo 59898 (applid)

Ps-ef

Grep "Application PID"

-- Stop the database

Db2 force application all

Db2 terminate

Db2stop

Db2start

Db2_all "db2 connect to qhbidb"

-- Use db2 activate db qhbidb, and then use db2_all "db2 connect to qhbidb" to check whether the table can be queried.

-- View the rollback status of the current node

Db2pd-d qhbidb-reco

-- View the rollback of all partitions of the current host

Db2pd-d qhbidb-reco-alldbp

Db2_all "db2 list utilities show detail"

-- View uncertain transactions

Export DB2NODE = 0

Db2 terminate

Db2 connect to qhbidb

Db2 values current dbpartitionnum

Db2 list indoubt transactions

-- Reorganize table check

Db2 "call reorgchk_tb_stats ('T', 'all ')"

-- Re-Indexing

Db2 "call reorgchk_ix_stats ('T', 'all ')"

-- Check statistics

Db2 "select date (STATS_TIME), count (1) from syscat. tables where type = 't'group by date (STATS_TIME) with ur"

-- Buffer pool hit rate

Db2 "select * from sysibmadm. bp_hitratio"

Db2 get snapshot for bufferpools on qhbidb global

-- Lock upgrade and lock wait

Db2 "select lock_waits, deadlocks, lock_escals, lock_timeouts, dbpartitionnum from sysibmadm. snapdb"

-- Sorting Overflow

Db2 "select total_sorts, sort_overflows, dbpartitionnum from sysibmadm. snapdb"

-- SQL Statement Analysis

Db2 "select * from sysibmadm. snapdyn_ SQL"

-- Table status check

Db2 "select substr (tabschema, 1, 10) tabschema, substr (tabname, 1, 30) tabname, status, type from syscat. tables where status <> 'n '"

-- Read valid Indexes

Db2 "select rows_read/(rows_selected + 1), dbpartitionnum as IREF from sysibmadm. snapdb"

-- The average result set is large or small.

Db2 "select rows_selected/(select_ SQL _effects + 1) as avg_result_set, dbpartitionnum from sysibmadm. snapdb"

-- Synchronous read Ratio

Db2 "select 100-(pool_async_data_reads + pool_async_index_reads * 100)/(pool_index_p_reads + 1) as SRP from sysibmadm. snapdb where DB_NAME = 'osdb '"

-- Dirty page stealing

Db2 "select pool_dirty_pg_steal_clns from sysibmadm. snapdb"

-- Buffer read/write IO Response Time

Db2 "select tbsp_name, (pool_read_time/(pool_data_p_reads + pool_index_p_reads + bytes + pool_temp_index_p_reads + 1) as tsorms from sysibmadm. snaptbsp

Order by tsorms desc fetch first 10 rows only"

-- Average sorting times of each transaction

Db2 "select total_sorts/(commit_ SQL _stmts + rollback_ SQL _stmts), dbpartitionnum from sysibmadm. snapdb"

-- Total number of transactions

Db2 "select commit_ SQL _effects + rollback_ SQL _effects, dbpartitionnum from sysibmadm. snapdb"

-- Each transaction includes the number of SQL queries

Db2 "select select_ SQL _stmts, (commit_ SQL _stmts + rollback_ SQL _stmts), dbpartitionnum from sysibmadm. snapdb"

-- The number of statements added, deleted, and modified for each transaction

Db2 "select uid_ SQL _stmts, (commit_ SQL _stmts + rollback_ SQL _stmts), dbpartitionnum from sysibmadm. snapdb"

-- Number of rows returned by each transaction

Db2 "select row_selected, (commit_ SQL _effects + rollback_ SQL _effects), dbpartitionnum from sysibmadm. snapdb"

-- Number of rows returned for each transaction

Db2 "select rows_read, (commit_ SQL _cmdts + rollback_ SQL _cmdts), dbpartitionnum from sysibmadm. snapdb"

-- Check the Package status

Db2 "select valid, count (1) from syscat. packages group by valid with ur"

-- Monitor table usage

Select substr (tabname, 1, 50) tabname, count (*)

From sysibmadm. snaptab a, syscat. tables B

Where a. tabschema = B. tabschema

And a. tabname = B. tabname

And B. tbspace like 'tbs _ blank'

Group by substr (a. tabname, 1, 50) having count (*) = 1;

-- View the tablespace to which the table belongs

Db2 "select tables. TABSCHEMA, TABLES. TABNAME, TBSPACES. tbspace from syscat. tables as tables, SYSCAT. TABLESPACES AS TBSPACES

Where tables. TBSPACEID = TBSPACES. tbspaceid and tabname = 'sales '"

-- Find the name of the package corresponding to the stored procedure

SELECT

CHAR (PROCSCHEMA, 20) as procschema, -- schema name

CHAR (PROCNAME, 20) as procname, -- stored procedure name

CHAR (B. BNAME, 20) AS PKGNAME -- Bind package name

From syscat. PROCEDURES

Join syscat. routinedep B on a. SPECIFICNAME = B. ROUTINENAME

Where procschema = 'kf2' and procname = 'explainplan _ test' with ur;

-- View table information

Db2 reorgchk update statistics on table NWH. CUST_BLACK

Db2_all "; db2 connect to qhbidb; db2 reorg table NWH. CUST_BLACK"

This article permanently updates the link address: