| |
- Table space expansion
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
- Table space expansion
ALTER TABLESPACE BAS_MK_APP
ADD (Device '/ dev / n0datavg6 / rmkapplv_100g_0_01 /' 3276800) ON DBPARTITIONNUM (0)
ADD (Device '/ dev / n1datavg6 / rmkapplv_100g_1_02 /' 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);
- See table space usage
db2 "select substr (tbsp_name, 1,20) tbsp_name, tbsp_type, substr (tbsp_state, 1,10) tbsp_state, tbsp_total_size_kb / 1024 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 table space state
db2 "select tbsp_id, substr (tbsp_name, 1,30) tbsp_name, substr (tbsp_state, 1,10) tbsp_state, tbsp_utilization_percent, dbpartitionnum from sysibmadm.tbsp_utilization with ur"
db2 list tablespaces show detail - view the table space on a single partition state, the normal return 0x0000
db2_all "db2 list tablespaces show detail" - to view the table space on all partitions status
db2tbst 0x0000
State = Normal
- See table space information
db2 list tablespaces
show detail
db2 list tablespace containers for 0
- To check if there is available space in the table on page DMS
$ Db2 list tablespaces show detail - the table space on a single partition is also available page
$ Db2_all "; db2 connect to qhbidb; db2 list tablespaces show detail" - to view the table space on all partitions if there are free pages
- Get table space high-water information
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-'
'------------------------------------------------- --------------------------- '
It shows two clauses and their syntax and memory reclaim space associated with the table. Wherein the MAX parameter to specify the maximum to reduce the high water mark to free up space. After running REDUCE command, during the movement of the data block,
You can also use STOP to stop parameter table space to move. If the DMS table space, you need to run the LOWER HIGH WATER MARK clause to reduce the high water mark, and then run the REDUCE clause to release the table space.
If it is enabled for automatic storage table spaces, you can run directly REDUCE clause.
Listing 7. The automatic storage table spaces
ALTER TABLESPACE REDUCE 10 M Listing 8. DMS table space
ALTER TABLESPACE LOWER HIGH WATER MARK
ALTER TABLESPACE REDUCE (ALL CONTAINERS 10 M)
- Database Size
db2 "call get_dbsize_info (,,, -??? 1)"
- Database load history information file cleanup
- It is recommended to clean regularly /dbhome/qhbiinst/NODE0000/SQL00001/db2rhist.asc, the file is too large, the LOAD operating performance problems.
- For example: remove node 1 October 2014 prior history file records
export DB2NODE = 1
db2 terminate
db2 connect to qhbidb
db2 prune history 201410
- Fault log Crawl
cd / db2diaglog / db2dump
- The entire contents of the folder will FODC_Trap_YYYY_MM_DD-HH.MI.SS.MS take down
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"
- Check load history file size
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"}'
- Verify the operating status of each point of the database
$ Db2_all "db2pd -db qhbidb -"
- If some node status is not normal, you can adopt the following ways to start the database:
$ Db2start dbpartitionnum nodenum
- View the status of a table
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 database name ------------- deactivation database
db2 activate database database name ------------- activate database
connect reset ------------- disconnect your current database connection
db2 restart database databasename ------------- restart the database
- Two kinds of ways you can back up
--- Offline database backup, you need to an exclusive connection to the database, it backs up the database of all the table space;
backup db sample to c: \ backup with 3 buffers buffer 1000 without prompting
--- Online database backup, you can back up the entire database or individual tablespaces.
backup db sample tablespace (userspace1) online to / dev / rmt0 without prompting
- To perform an online backup, make sure the database parameters logretain = on or set logarchmetd1 support online backup
- Check warehouse transaction
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"
- By the use of db2 activate db qhbidb, then use db2_all "db2 connect to qhbidb" See if you can query the table.
- View the current node rollback situation
db2pd -d qhbidb -reco
- View current host rollback of all partitions
db2pd -d qhbidb -reco -alldbp
db2_all "db2 list utilities show detail"
- Check doubt transactions
export DB2NODE = 0
db2 terminate
db2 connect to qhbidb
db2 values current dbpartitionnum
db2 list indoubt transactions
- Recombinant table check
db2 "call reorgchk_tb_stats ( 'T', 'ALL')"
- Recombinant index check
db2 "call reorgchk_ix_stats ( 'T', 'ALL')"
- Check the 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 escalation, lock waits
db2 "select lock_waits, deadlocks, lock_escals, lock_timeouts, dbpartitionnum from sysibmadm.snapdb"
- Sort overflow
db2 "select total_sorts, sort_overflows, dbpartitionnum from sysibmadm.snapdb"
--SQL Statement analysis
db2 "select * from sysibmadm.snapdyn_sql"
- Check the status of the table
db2 "select substr (tabschema, 1,10) tabschema, substr (tabname, 1,30) tabname, status, type from syscat.tables where status <> 'N'"
- Valid index reading
db2 "select rows_read / (rows_selected + 1), dbpartitionnum as IREF from sysibmadm.snapdb"
- The average size of the result set
db2 "select rows_selected / (select_sql_stmts + 1) as avg_result_set, dbpartitionnum from sysibmadm.snapdb"
- Synchronous read proportion
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 = 'ODSDB'"
- Dirty page steal
db2 "select pool_dirty_pg_steal_clns from sysibmadm.snapdb"
- Buffer write IO response time
db2 "select tbsp_name, (pool_read_time / (pool_data_p_reads + pool_index_p_reads + pool_temp_data_p_reads + pool_temp_index_p_reads + 1)) as tsorms from sysibmadm.snaptbsp
order by tsorms desc fetch first 10 rows only "
- The average number of transactions per sort
db2 "select total_sorts / (commit_sql_stmts + rollback_sql_stmts), dbpartitionnum from sysibmadm.snapdb"
- The total number of transactions
db2 "select commit_sql_stmts + rollback_sql_stmts, dbpartitionnum from sysibmadm.snapdb"
- Each transaction including the number of SQL queries
db2 "select select_sql_stmts, (commit_sql_stmts + rollback_sql_stmts), dbpartitionnum from sysibmadm.snapdb"
- Each transaction includes a number of additions and deletions to the statement
db2 "select uid_sql_stmts, (commit_sql_stmts + rollback_sql_stmts), dbpartitionnum from sysibmadm.snapdb"
- The result set number of rows returned for each transaction
db2 "select row_selected, (commit_sql_stmts + rollback_sql_stmts), dbpartitionnum from sysibmadm.snapdb"
- The number of rows returned for each transaction to read
db2 "select rows_read, (commit_sql_stmts + rollback_sql_stmts), dbpartitionnum from sysibmadm.snapdb"
- Check 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_AGG'
group by substr (a.tabname, 1,50) having count (*) = 1;
- See the table belongs table space
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 stored procedure that corresponds to the package
SELECT
CHAR (PROCSCHEMA, 20) AS PROCSCHEMA, - model name
CHAR (PROCNAME, 20) AS PROCNAME, - the name of the stored procedure
CHAR (B.BNAME, 20) AS PKGNAME - the bundle name
FROM SYSCAT.PROCEDURES A
JOIN SYSCAT.ROUTINEDEP B ON A.SPECIFICNAME = B.ROUTINENAME
WHERE PROCSCHEMA = 'KF2' AND PROCNAME = 'EXPLAINPLAN_TEST' WITH UR;
- Related information View table
db2 reorgchk update statistics on table NWH.CUST_BLACK
db2_all "; db2 connect to qhbidb; db2 reorg table NWH.CUST_BLACK" |
|
|
|