- Table
Merge(http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.admin.doc/doc/r0010873.htm)db2look -d <database> -e -t <schema>.<table_name> -o tmp.sql db2 describe table SYSIBMADM.ENV_INST_INFO Altter column size ALTER TABLE ${schema}.${table} ALTER COLUMN ${column} SET DATA TYPE VARCHAR(128);
MERGE INTO employee_file AS e
USING (SELECT empid, phone, office
FROM (SELECT empid, phone, office,
ROW_NUMBER() OVER (PARTITION BY empid
ORDER BY transaction_time DESC) rn
FROM transaction_log) AS nt
WHERE rn = 1) AS t
ON e.empid = t.empid
WHEN MATCHED THEN
UPDATE SET
(phone, office) =
(t.phone, t.office)
WHEN NOT MATCHED THEN
INSERT
(empid, phone, office)
VALUES (t.empid, t.phone, t.office)
查看表占用的磁盘空间(pages * page_per_size)
select char(date(t.stats_time))||' '||char(time(t.stats_time)) as statstime
,trim(t.tabschema)||'.'||t.tabname as tabname
, card as rows_per_table
, t.TBSPACE as "TABLESPACE"
, decimal(float(t.npages) * b.pagesize / 1024 / 1024,9,2) as used_mb
, decimal(float(t.fpages) * b.pagesize / 1024 / 1024,9,2) as allocated_mb
from syscat.tables t, syscat.tablespaces b
where t.tbspace=b.tbspace
and t.tbspace not in ('SYSCATSPACE')
order by t.TBSPACE,used_mb desc
with ur;
SELECT rtrim(substr(i.TABSCHEMA,1,8))||'.'||rtrim(substr( i.TABNAME, 1,24)) as TABLE_NAME
,b.TBSPACE AS TABLE_SPACE
, decimal((sum(i.NLEAF) * b.PAGESIZE / 1024 / 1024),12,2) as INDX_USED_PERTABLE
FROM SYSCAT.INDEXES i, SYSCAT.TABLESPACES b
WHERE i.TABSCHEMA IS NOT NULL
AND i.tbspaceid=b.TBSPACEID
GROUP BY i.TABNAME,i.TABSCHEMA, b.TBSPACE, b.PAGESIZE
ORDER BY TABLE_SPACE, INDX_USED_PERTABLE DESC WITH UR;
SELECT
decimal((i.NLEAF * b.PAGESIZE / 1024 / 1024),12,2) as INDX_USED_MB
FROM SYSCAT.INDEXES i, SYSCAT.TABLESPACES b
WHERE i.TABSCHEMA IS NOT NULL
AND i.tbspaceid=b.TBSPACEID
and i.INDNAME = '${index_name}'
WITH UR;
- Processes(applications)
SELECT * FROM SYSIBMADM.APPLICATIONS with ur;
- Check dead lock
SELECT lockwaits.lock_wait_start_time,
SUBSTR (locker.auth_id, 1, 10) AS locker_auth_id,
SUBSTR (locker.appl_name, 1, 20) AS locker_appl_name,
SUBSTR (locked.auth_id, 1, 10) AS locked_auth_id,
SUBSTR (locked.appl_name, 1, 20) AS locked_appl_name
FROM TABLE (SNAPSHOT_LOCKWAIT ('', - 1)) AS lockwaits,
TABLE (SNAPSHOT_APPL_INFO ('', - 1)) AS locker,
TABLE (SNAPSHOT_APPL_INFO ('', - 1)) AS locked
WHERE lockwaits.agent_id = locker.agent_id AND
lockwaits.agent_id_holding_lk = locked.agent_id
WITH UR; -
- Sequence
values previous value for $schema.$seq_name
values next value for $schema.$seq_name
SELECT LASTASSIGNEDVAL
FROM sysibm.SYSSEQUENCES
WHERE seqname = '$seq_name' and seqschema = '$schema'
WITH UR;
db2 "ALTER SEQUENCE $schema.$seq_name RESTART WITH $seq_num"
db2ts restart sequence -dbname $database -schema $schema -value $seq_num -name $sequence -mailto $mail_addr
- Instance
SELECT * FROM SYSIBMADM.ENV_INST_INFO with ur;
- Load Query
db2 load query table ${schema.name} [ to ${log_file_path} ]
- Terminate table loading pending status(will purge all existing data)
load from /dev/null of del terminate into ${schema.name}
- db2 get db cfg (check db2 configure parameters)
- db2 list command options
- Check table status
SELECT t.status, t.access_mode, t.TBSPACEID, t.TBSPACE, t.INDEX_TBSPACE, t.* FROM syscat.tables t WHERE tabschema='${table_schema}' AND tabname = '${table_name}' WITH UR
select t.status, t.access_mode FROM syscat.tables t WHERE t.TABNAME = ${table_name} with urSELECT 'set integrity for '||tabschema||'.'||tabname||' immediate checked ;' FROM syscat.tables WHERE tabschema='${table_schema}' and not (status='N' and access_mode='F');
- Frequetly used Date format
http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.sql.ref.doc%2Fdoc%2Fr0007107.htmlSELECT VARCHAR_FORMAT(TIMESTAMP_ISO(current date - 1 days ), 'YYYY-MM-DD') || ' 00:00:00' FROM SYSIBMADM.ENV_INST_INFO SELECT VARCHAR_FORMAT(TIMESTAMP_ISO(current timestamp - 2 days ), 'YYYY-MM-DD') || ' 00:00:00' FROM SYSIBMADM.ENV_INST_INFO values current timestamp select date(current timestamp) FROM SYSIBMADM.ENV_INST_INFO with ur
- Reorg/Run stats
below SQL is for check if a table pending for reorg
select TABSCHEMA, TABNAME, REORG_PENDING from SYSIBMADM.ADMINTABINFO a where a.REORG_PENDING = 'Y' and a.TABSCHEMA = ${schema_name} and a.TABNAME = ${table_name} with ur
(http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/core/r0001971.htm)
db2 reorgchk update statistics on table system
db2 -v reorgchk on table ${schema_name}.${table_name}
db2ts reorg table -dbname ${database} -schema ${schema_name} -name ${table_name} -mailto ${mail_group} -dictionary reset -force
run statistics and rebind package(especially for stored procedure)module load ibmdb2/db2ts
db2ts update table statistics -dbname ${database} -schema ${table_sckema} -name ${table_name} -mailto ${mail_group} -
Check Re-org table status
select *
from sysibmadm.snaptab_reorg r
where r.TABNAME = ${table_name}
order by r.REORG_START desc
with ur - Stored Procedure
query stored procedure and its related infor(like package, view and table etc.)
select r.* from syscat.ROUTINES r where r.ROUTINENAME = ${table_name} with ur
select d.* from syscat.routinedep d where d.routinename = ${table_name} and d.btype='K' with ur
Note:d.btype='K' stand for package
- DB2 SQL应用调优——解释工具db2expln的使用及实例分析
db2expln -database sample -t -g -f "sql query
db2expln -d ${database} -f imrs_fx.sql -g -t -z @ > imrs_fx.out
run_db2exfmt -d ${database} -f ${sql file}
-
db2数据库的启动和关闭
http://space.itpub.net/785478/viewspace-571147
db2 list database directory
db2 list node directory
Get snapshot
attach to ${database}
get snapshot for application agentid 24568
module load ibmdb2/client/9.5.7 |
db2 attach to ${database_name} Note : Not alias |
db2 get snapshot for application agentid 24568 |
stored procedure pre-compile options
http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=%2Fcom.ibm.db2.luw.apdv.routines.doc%2Fdoc%2Ft0006584.html
http://pic.dhe.ibm.com/infocenter/db2luw/v9r8/index.jsp?topic=%2Fcom.ibm.db2.luw.admin.perf.doc%2Fdoc%2Fc0055082.html
db2 "CALL SET_ROUTINE_OPTS(GET_ROUTINE_OPTS() || ' ISOLATION UR')"
db2 -td@ -vf maxamount.db2
保存修改
Get Dynamic SQL
SELECT * FROM SYSIBMADM.SNAPDYN_SQL x where x.STMT_TEXT like '%CASH_BLOCK_TRADE_DETAIL%';