linux db2建库脚本,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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11419868/viewspace-1017987/，如需转载，请注明出处，否则将追究法律责任。