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: