Routine DB2 database management

1. How to quickly obtain the size of the storage space occupied by the database (zz) select tbspace, sum (aa. total_pages * bb. pagesize)/1024/1024 "size (m)" from table (snapshot_container ('sdclr ',-1) as aa, syscat. tablespaces bbwhere aa. tablespace_id = bb. tbspaceidgroup by tbspace; db2pd-d sdclr-tablespace: Number of pending free pages = 781248 after the emergence of db2 force application all run db2_all "db2 connect to sdclr; db2 list tablespaces show detail; db2 connect reset "--- old: db2_all"; db2 update db cfg for sdclr using LOGFILSIZ 51200 LOGPRIMARY 50 LOGSECOND 40 "db2_all "; db2 update db cfg for sdclr using LOGFILSIZ 65536 LOGPRIMARY 80 LOGSECOND 16"
2. view the lock status SELECT * from sysibmadm. LOCKS_HELDSelect. DB_NAME,. AGENT_ID,. APPL_NAME, B. APPL_ID, B. APPL_STATUS,. LOCK_MODE,. LOCK_STATUS,. TABSCHEMA,. TABNAME,. DBPARTITIONNUMfrom sysibmadm. locks_held a, sysibmadm. applications bwhere. agent_id = B. agent_id; 3. Create a tool directory database: DB2? Create tools catalog delete: db2 "drop tools catalog toolscat in database sdclr" 4. Clear data import from/dev/null of del replace into table_name alter table tblname activate not logged initially with empty table5. pay attention to the selection of the partition key (month is not recommended as the partition key) the main consideration is the even distribution of data. In the future, hash jion should minimize the configuration files of TQ6 and DB2 cataloguing \ SQLLIB \ DB2 Directory: SQLNODIR (node directory) and SQLDBDIR (Database directory) 7. Check the deadlock of the database table. The locksize parameter and maxlocks parameter are related to the lock generation. View the lock using db2 list indoubt transactions unlock using db2 list indoubt transactions with prompting db2pd-locks-transactions-agents-alldbs db2pd-db sdclr-lock-transactions-agents-applications-file lock_info.txt -- find the lock on the node and wait for db2pd-alldbp-db sdclr-locks showlocks wait -- query the lock-related information of the specified node db2pd-dbpartitionnum 0, 1-db sdclr-locks-transactions- applications-dynamic-file locklog first, turn the monitoring switch on db2 update Dbm cfg using dft_mon_lock on db2 update dbm cfg using DFT_MON_TABLE on snapshot db2 get snapshot for Locks on sdclr> snap. log db2 get snapshot for tables on sdclr> snap. log db2 get snapshot for bufferpools on sdclr> snap. log db2 get snapshot for tablespaces on sdclr> snap. log db2 get snapshot for database on sdclr> snap. log and then look at snap. log content. You can view the locks of each Application based on Application handle. After monitoring, do not forget to turn off the monitor db2 update dbm cfg using dft_mon_lock off select * from table (snapshot_lock ('sdfqs',-1 )) snapshot_lock ------------------------------ determine that the lock is waiting for db2 update monitor switches using lock on db2 get snapshot for locks on sdclr> snap. log db2 update monitor switches using lock off ---- You can also get the SQL statement of the lock table without locking information: 1. run: db2pd-db sdclr-lock-transactions-reopt-applications-file c:/lock_info.log2. document Find the SQL statement that causes the lock by associating fields in the file: Use Locks (TranHdl) ---> Transactions (AppHandl) ---> Applications (C-AnchID, C-StmtUID) ---> dynamic statement ---> SQL 8. select tbsp_name, sum (tbsp_total_pages) as "total", sum (tbsp_used_pages) as "use pages", sum (tbsp_free_pages) as "free pages", sum (tbsp_used_pages) * 1.0/sum (tbsp_total_pages) * 100 as "use precent" from "SYSIBMADM ". "TBSP_UTILIZATION" where tbsp_total_pages> 0 group By tbsp_name9, check whether the data distribution is even (by tablespace) nohup sh CheckPartitionnum. sh USR_TBSP_MINING> check. log & 10. Check the SQL statement SELECT elapsed_time_min, SUBSTR (AUTHID, 1, 10) AS auth_id, agent_id, appl_status, SUBSTR (stmt_text, 1, 60) AS SQL _text FROM sysibmadm. long_running_ SQL WHERE elapsed_time_min> 0 ORDER BY elapsed_time_min DESC11, SQL0668N due to table "ATOM. the cause code on T_ST_USAGE_DAY is "3", so you are not allowed to operate db2 "load from/dev/null of del term Inate into atom. t_st_usage_day partitioned db config OUTPUT_DBPARTNUMS (, 22, 32, 34, 35) "; 12. recursion of dimension table retrieved: with n (level, schemaname, tname) as (select distinct 1, REFTABSCHEMA, REFTABNAME from SYSCAT. REFERENCES where REFTABSCHEMA not in ('db2info', 'revoke ools') union all select d. level + 1, c. refTABSCHEMA, c. refTABNAME from SYSCAT. REFERENCES as c, n as d where c. tabname = d. tname and d. leve L <10 --- restricted operation for 10 times) select schemaname, tname, max (level) flag from n group by schemaname, tname order by flag desc 13. archive log processing db2 connect to DBNAMEdb2 get db cfg | awk-F = '$1 ~ /First active log file/{print $2} '| read firstlogdb2 prune logfile prior to $ firstlogdb2 terminate 14. EXPORT the Stored Procedure SELECT 'db2 "export to' | rtrim (procschema) | '. '| rtrim (procname) | '. SQL of del MODIFIED BY LOBSINFILE SELECT ''set CURRENT scheme' | rtrim (procschema) | '@ ''| chr (10) | ''set current path = SYSIBM, SYSFUN, SYSPROC, '| rtrim (procschema) |' @ ''| chr (10) | '| 'text' |' | chr (10) | ''@'' | chr (10) From syscat. procedures where procschema = ''' | rtrim (procschema) | ''' and procname = ''' | rtrim (procname) | ''' "'from syscat. PROCEDURES where procschema not in ('db2info', 'idmmx', 'sqlj', 'sysfun ', 'sysibm', 'sysproc ', 'sysibmadm ') 15. Find the illegal Stored Procedure select rtrim (r. routineschema) | '. '| RTRIM (r. routinename) AS spname, '(' | RTRIM (r. routineschema) | '. '| 'P' | SUBSTR (CHAR (r. lib_id + 10000000), 2) | ')' FROM SYSCAT. routines r WHERE r. routinetype = 'p' AND (r. origin = 'Q' AND r. valid! = 'Y') or exists (SELECT 1 FROM syscat. packages WHERE pkgschema = r. routineschema AND pkgname = 'P' | SUBSTR (CHAR (r. lib_id + 10000000), 2) AND valid! = 'Y') order by spname; 16. The transaction log is full. 1. on TOAD, use the following sentence to query which node of the transaction is full of select int (total_log_used/1024/1024) as "Log Used (Meg)", int (total_log_available/1024/1024) as "Log Space Free (Meg)", int (tot_log_used_top/1024/1024) as "Max Log Used (Meg)", int (sec_log_used_top/1024/1024) as "Max Sec. used (Meg) ", int (sec_logs_allocated) as" Secondaries ", int (float (total_log_used)/float (total_log_used + interval) * 100) as" Pct Used ", t. DB_NAME, t. DB_PATHfrom sysibmadm. snapdb torder by int (float (total_log_used)/float (total_log_used + total_log_available) * 100) descwith ur 2. log onto db2_dw_5 and run db2 terminate export DB2NODE = 13 pid = 'db2 get snapshot for database on hebdw | grep-p log | grep oldest | cut-d =-f2 'echo $ pid db2" force application ($ pid) "author myamor