Page last modified 15:13, 15 Dec 2017 by alang

DBA 管理技巧

內容表格

1. Tables 管理
2. Tablespaces 管理
1. 2.1. 新增多個 auto resized Tablespace
3. 用戶端連線設定(Catalog)的匯出/匯入

版本為 16:18, 6 Oct 2024

返回到版本存檔.

查閱目前版本

DB2 之 Linux/UNIX 檔案系統規劃

Tables 管理

執行 SQL 的顯示開始與結束時間

reference to undefined name 'syntax' Exception of type 'MindTouch.Deki.Script.Runtime.DekiScriptUndefinedNameException' was thrown. (click for details)

Callstack:
    at User:Alang/IT_專案工作/IBM_DB2/DBA_管理技巧

MindTouch.Deki.Script.Runtime.DekiScriptUndefinedNameException: reference to undefined name 'syntax' Exception of type 'MindTouch.Deki.Script.Runtime.DekiScriptUndefinedNameException' was thrown.
  at MindTouch.Deki.Script.Compiler.DekiScriptExpressionEvaluation.Visit (MindTouch.Deki.Script.Expr.DekiScriptVar expr, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0 
  at MindTouch.Deki.Script.Expr.DekiScriptVar.VisitWith[DekiScriptExpressionEvaluationState,Range] (IDekiScriptExpressionVisitor`2 visitor, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0 
  at MindTouch.Deki.Script.Compiler.DekiScriptExpressionEvaluation.Evaluate (MindTouch.Deki.Script.Expr.DekiScriptAccess expr, DekiScriptExpressionEvaluationState state, Boolean evaluateProperties) [0x00000] in <filename unknown>:0 
  at MindTouch.Deki.Script.Compiler.DekiScriptExpressionEvaluation.Visit (MindTouch.Deki.Script.Expr.DekiScriptAccess expr, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0 
  at MindTouch.Deki.Script.Expr.DekiScriptAccess.VisitWith[DekiScriptExpressionEvaluationState,Range] (IDekiScriptExpressionVisitor`2 visitor, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0 
  at MindTouch.Deki.Script.Compiler.DekiScriptExpressionEvaluation.Visit (MindTouch.Deki.Script.Expr.DekiScriptCall expr, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0 
  at MindTouch.Deki.Script.Expr.DekiScriptCall.VisitWith[DekiScriptExpressionEvaluationState,Range] (IDekiScriptExpressionVisitor`2 visitor, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0 
  at MindTouch.Deki.Script.Compiler.DekiScriptExpressionEvaluation.Visit (MindTouch.Deki.Script.Expr.DekiScriptSequence expr, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0 
  at MindTouch.Deki.Script.Expr.DekiScriptSequence.VisitWith[DekiScriptExpressionEvaluationState,Range] (IDekiScriptExpressionVisitor`2 visitor, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0 
  at MindTouch.Deki.Script.Compiler.DekiScriptExpressionEvaluation.Visit (MindTouch.Deki.Script.Expr.DekiScriptReturnScope expr, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0

快速產生所有資料表的 runstats 腳本

db2 -x "select 'runstats on table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,35),'with distribution and detailed indexes all allow write access;'from syscat.tables where type = 'T' and tabschema not in ('SYSIBM','ASN')" > runstats_all.sql

大量匯出/匯入資料表

Tablespace = TB1

-- db2 -txf gen_export_bulk.sql > export_bulk_tables.sql
-- 
select ' export to '||rtrim(tabschema)||'.'||rtrim(tabname)||'.ixf of ixf messages export_'||rtrim(tabschema)||'.'||rtrim(tabname)||'.msg select * from '||rtrim(tabschema)||'.'||ltrim(tabname)||';'
from syscat.tables
where tbspace= 'TB1' and type = 'T';

-- db2 -txf gen_import_bulk.sql > import_bulk_tables.sql
--
select ' import from '||rtrim(tabschema)||'.'||rtrim(tabname)||'.ixf of ixf commitcount 2000 messages import_'||rtrim(tabschema)||'.'||rtrim(tabname)||'.msg insert_update into '||rtrim(tabschema)||'.'||ltrim(tabname)||';'
from syscat.tables
where tbspace= 'TB1' and type = 'T';

取得所有 Tables Name

-- db2 -txf get_all_tables_name.sql > all_tables_name.lst
--
select substr(tabschema,1,8) || ' ' || substr(tabname,1,24)
from SYSCAT.TABLES
where tabschema not in ('SYSIBM','ASN','SYSTOOLS')
and type='T'
order by tabschema;

TIP:

已排除系統相關的 Tables。

統計 Table 的使用容量

SELECT char(date(t.stats_time))||' '||char(time(t.stats_time)) as statstime
, substr(t.tabschema,1,8)||'.'||substr(t.tabname,1,24) as tabname
, card as rows_per_table
, decimal(float(t.npages)/ ( 1024 / (b.pagesize/1024)),9,2) as used_mb
, decimal(float(t.fpages)/ ( 1024 / (b.pagesize/1024)),9,2) as allocated_mb
FROM syscat.tables t
, syscat.tablespaces b
WHERE t.tbspace=b.tbspace
AND t.tabschema NOT IN ('SYSIBM','SYSTOOLS','ASN')
WITH ur;

TIP:

Table 必須先經過 runstats 後，統計後的數值才會正確。

已排除系統用的 Table。

Tablespaces 管理

新增多個 auto resized Tablespace

CREATE REGULAR TABLESPACE RPTTBS9 IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 8192 MANAGED BY DATABASE
         USING (FILE '/home/STORAGE/devrpt/DEVRPTDB/rpttbs9-c1' 1024 M,
                FILE '/home/STORAGE/devrpt/DEVRPTDB/rpttbs9-c2' 1024 M)
         AUTORESIZE YES MAXSIZE 51200 M
         EXTENTSIZE 32
         PREFETCHSIZE AUTOMATIC
         BUFFERPOOL RPT8KBP1
         OVERHEAD 12.670000
         TRANSFERRATE 0.180000
         FILE SYSTEM CACHING
         DROPPED TABLE RECOVERY ON;

TIP:

AUTORESIZE YES MAXSIZE 51200 M ，每個 data file 可以自動擴充至 50GB

用戶端連線設定(Catalog)的匯出/匯入

db2cfexp 匯出
db2cfimp 匯入