Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ DB2 commonly used scripting sort out     - How to configure FirewallD in RHEL / CentOS 7 and Fedora in (Linux)

- C ++ based foundation: the difference between C and C ++ (Programming)

- VMware Workstation virtual machine startup error: Could not open / dev / vmmon in CentOS 6 (Linux)

- Upgrade to Node V4 seven reasons (Programming)

- Linux iptables port mapping settings (Server)

- Linux System Getting Started Tutorial: how to find information on Linux-embedded module (Linux)

- CentOS6.5 installation Docker (Linux)

- Difference in MySQL VARCHAR and CHAR data format (Database)

- Linux security configuration (Linux)

- Use Python automatically cleared Android Engineering excess resources (Programming)

- Linux Open coredump (Linux)

- Hadoop connection failed or stuck processing (Server)

- Oracle 11g new features of the collection of multi-column statistics (Database)

- Shared directory settings between Linux and Linux (Linux)

- S5PV210 development board for embedded development environment to build under Ubuntu (Linux)

- Installation Yarock 1.1.4 Music Player in Ubuntu (Linux)

- Let VMware ESXi virtual switch support VLAN (Linux)

- RHEL7 system making use of OpenStack mirror (Linux)

- C ++ runtime environment built on CentOS 6.6 and Oracle database connection (Database)

- GO five stages of language learning (Programming)

 
         
  DB2 commonly used scripting sort out
     
  Add Date : 2018-11-21      
         
         
         
  - 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"
     
         
         
         
  More:      
 
- Linux command in the dialog whiptail (Linux)
- To execute the cp command prompt type skip folder under CentOS (Linux)
- Linux loopback adapter Driven Design (Programming)
- Piostat - Monitoring and Statistics Linux process (Linux)
- Articles do not resolve after opening under Ubuntu WordPress setting a fixed link (Server)
- How to create a binary distribution with Bash (Linux)
- Ubuntu use three methods to install Ruby (Linux)
- The difference between Linux su and sudo commands (Linux)
- Figlet use (Linux)
- To install the latest version of the EPEL on CentOS 5.x or 6.x (Linux)
- Two classic macro definition under Linux (Linux)
- Oracle Automatic Diagnostic Repository (Automatic Diagnostic Repository, ADR) (Database)
- Linux environment password security settings (Linux)
- FileZilla FTP && TLS connection settings of (Linux)
- MongoDB common optimization settings in Linux (Database)
- MySQL Statistics (Database)
- Java interface and inheritance (Programming)
- Gnu Linux - Ubuntu System Clean-term consolidation (Linux)
- CentOS 6.5 installation configuration DRBD (Server)
- Tip: Use Cryptsetup U disk encryption (Linux)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.