CREATE DATABASE std100 AUTOMATIC STORAGE YES USING CODESET GBK TERRITORY CN COLLATE USING SYSTEM PAGESIZE 16384;[@more@]
--无日志清空表
alter table ORI_AAC01 activate not logged initially with empty table;
以下语句相当于oracle中的truncate
'IMPORT FROM /dev/null OF DEL REPLACE INTO '||full_name;
--创建统计表信息脚本
db2 connect to db
db2 "select 'RUNSTATS ON TABLE BASEINFO.'||TABNAME||';' as aaa FROM SYSCAT.TABLES WHERE TABSCHEMA='BASEINFO'" >d:runstats2.sql
db2 "select 'RUNSTATS ON TABLE BASEINFO.'||TABLE_NAME||';' as a FROM sysibm.tables WHERE TABLE_SCHEMA='BASEINFO' AND table_type='BASE TABLE'" >D:runstats3.sql
--db2 load from db2
db2 => declare c1 cursor database stdnew user baseinfo using baseinfo for select * from paz36
DB20000I SQL 命令成功完成。
db2 => load from c1 of cursor messages d:load_paz36.msg insert into baseinfo.paz36
--监控死锁及解除死锁
db2 UPDATE MONITOR SWITCHES USING LOCK ON
db2 get snapshot for locks on db
db2 "force applications(handle) "
--force applications all
db2 UPDATE MONITOR SWITCHES USING LOCK OFF
--db2备份数据库
db2move stdnew export -u DRM_U2 -p drm_u2 -sn DRM_U2,DB2ADMIN,U2
--db2授权
CONNECT TO STANDARD;
GRANT DBADM,CREATETAB,
BINDADD,CONNECT,
CREATE_NOT_FENCED_ROUTINE,IMPLICIT_SCHEMA,
LOAD,CREATE_EXTERNAL_ROUTINE,
QUIESCE_CONNECT,SECADM
ON DATABASE TO USER DRM_U2;
CONNECT RESET;
用SQL语句实现DB2主要指标的监控
--Database manager:To capture a snapshot of database manager information:
SELECT * FROM TABLE( SNAPSHOT_DBM(-1)) as SNAPSHOT_DBM
--To capture a snapshot of database manager information specifically regarding the fast communication manager (FCM):
SELECT * FROM TABLE( SNAPSHOT_FCM(-1)) as SNAPSHOT_FCM
--To capture a snapshot of database manager information for a partition specifically regarding the fast communication manager(FCM):
SELECT * FROM TABLE( SNAPSHOT_FCMPARTITION(-1)) as SNAPSHOT_FCMPARTITION
--To capture the database manager's monitor switch settings:
SELECT * FROM TABLE( SNAPSHOT_SWITCHES(-1)) as SNAPSHOT_SWITCHES
--Database: To capture a snapshot of database information:
SELECT * FROM TABLE( SNAPSHOT_DATABASE( 'SAMPLE', -1 )) as SNAPSHOT_DATABASE
--Application:To capture a snapshot of application information:
SELECT * FROM TABLE( SNAPSHOT_APPL( 'SAMPLE', -1 )) as SNAPSHOT_APPL
--To capture a snapshot of application identification information:Chapter 3. Using the Snapshot Monitor 23
SELECT * FROM TABLE( SNAPSHOT_APPL_INFO( 'SAMPLE', -1 )) as SNAPSHOT_APPL_INFO
--To capture a snapshot of lock wait information:
SELECT * FROM TABLE( SNAPSHOT_LOCKWAIT('SAMPLE', -1 )) as SNAPSHOT_LOCKWAIT
--To capture a snapshot of statement information:
SELECT * FROM TABLE( SNAPSHOT_STATEMENT( 'SAMPLE', -1 )) as SNAPSHOT_STATEMENT
--To capture a snapshot of agent information:
SELECT * FROM TABLE( SNAPSHOT_AGENT( 'SAMPLE', -1 )) as SNAPSHOT_AGENT
--To capture a snapshot of subsection information:
SELECT * FROM TABLE( SNAPSHOT_SUBSECT( 'SAMPLE', -1 )) as SNAPSHOT_SUBSECT
--Buffer pool: To capture a snapshot of buffer pool information:
SELECT * FROM TABLE( SNAPSHOT_BP( 'SAMPLE', -1 )) as SNAPSHOT_BP
--Table space:To capture a snapshot of table space information:
SELECT * FROM TABLE( SNAPSHOT_TBS( 'SAMPLE', -1 )) as SNAPSHOT_TBS
--To capture a snapshot of table space configuration information:
SELECT * FROM TABLE( SNAPSHOT_TBS_CFG( 'SAMPLE', -1 )) as SNAPSHOT_TBS_CFG
--To capture a snapshot of table space quiescer information:
SELECT * FROM TABLE( SNAPSHOT_QUIESCER( 'SAMPLE', -1 )) as SNAPSHOT_QUIESCER
--To capture a snapshot of table space container configuration information:
SELECT * FROM TABLE( SNAPSHOT_CONTAINER( 'SAMPLE', -1 )) as SNAPSHOT_CONTAINER
--To capture a snapshot of the ranges for a table space map:
SELECT * FROM TABLE( SNAPSHOT_RANGES( 'SAMPLE', -1 )) as SNAPSHOT_RANGES
--Table: To capture a snapshot of table information:
SELECT * FROM TABLE( SNAPSHOT_TABLE( 'SAMPLE', -1 )) as SNAPSHOT_TABLE
--Lock: To capture a snapshot of lock information:
SELECT * FROM TABLE( SNAPSHOT_LOCK( 'standard', -1 )) as SNAPSHOT_LOCK
--Dynamic SQL cache: To capture a snapshot of dynamic SQL statement cache information:Snapshot monitor 24 System Monitor Guide and Reference
SELECT * FROM TABLE( SNAPSHOT_DYN_SQL( 'SAMPLE', -1 )) as SNAPSHOT_DYN_SQL
--db2中的minus(集合差集)
except
--db2中执行文件
db2 -vf filename
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/11419868/viewspace-1017987/,如需转载,请注明出处,否则将追究法律责任。