linux下db2建庫指令碼pagesize>4k及常用監控

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