1. 日志查看

# 查看DB2diag日志的路径（一般路径为：/home/db2inst1/sqllib/db2dump）
# 方式1
db2 get dbm cfg show detail|grep -i diag
# 方式2：看最后的输出就知道在哪里了
db2diag

2. 连接数设置

2.1. 数据库管理器配置

# 数据库管理器配置
# 查看最大连接数
db2 get dbm cfg
# Max number of coordinating agents     (MAX_COORDAGENTS) = AUTOMATIC(200)
# Max number of client connections      (MAX_CONNECTIONS) = AUTOMATIC(MAX_COORDAGENTS)
# 设置最大连接数
db2 update dbm cfg using <parameter_name> <value>
db2 update dbm cfg using max_coordagents 1000 automatic max_connections -1 automatic

2.2. 数据库配置

# 数据库配置
# 查看最大连接数
db2 get db cfg for deskdb
db2 get db cfg for deskdb | grep -i maxappls
# Max number of active applications            (MAXAPPLS) = AUTOMATIC(40)
# Average number of active applications       (AVG_APPLS) = AUTOMATIC(1)
# 设置最大连接数
db2 update db cfg for <db-name> using maxappls <new number>
db2 update db cfg for deskdb using MAXAPPLS 200
# 恢复默认设置命令
db2 reset db cfg

2.3. 实例查看

# 查看当前连接数
db2 list applications for db deskdb
db2 list applications for db deskdb | wc -l
db2 list applications for db deskdb show detail

2.4. 活跃连接数查看

-- 方式1
SELECT COUNT(*) as num FROM SYSIBMADM.APPL_PERFORMANCE;
-- 方式2
SELECT COUNT(*) as num FROM TABLE(SYSPROC.SNAP_GET_APPL('BDAPDB',0));
SELECT COUNT(*) as num FROM TABLE(SYSPROC.SNAP_GET_APPL(NULL,0));
SELECT COUNT(*) as num FROM TABLE(SYSPROC.SNAP_GET_APPL_INFO('BDAPDB',-2));

3. 表空间操作

3.1. 表空间使用率

db2 "select substr(a.tbsp_name,1,18) as name,substr(a.tbsp_type,1,10) as 
tbstype,a.TBSP_USING_AUTO_STORAGE as AUTO_STORAGE,substr(a.tbsp_state,1,8) as 
state,a.tbsp_total_size_kb/1024 as TotalMB ,a.TBSP_PAGE_TOP*a.TBSP_PAGE_SIZE/1024/1024 as 
top_size_mb,a.tbsp_used_size_kb/1024 as UsedMB, a.TBSP_UTILIZATION_PERCENT as 
UsedPer ,b.CONTAINER_NAME from sysibmadm.tbsp_utilization a, sysibmadm.SNAPCONTAINER 
b where tbsp_type='DMS' and a.TBSP_ID=b.TBSP_ID order by AUTO_STORAGE,UsedPer desc"

3.2. 创建表空间

su - db2inst1
db2 connect to deskdb
# 创建表空间
db2 create tablespace TBS_OTH_DAT managed by automatic storage
db2 create tablespace TBS_OTH_IDX managed by automatic storage
# 删除表空间
db2 drop tablespace TBS_OTH_DAT
db2 drop tablespace TBS_OTH_IDX
# 创建缓冲池（size表示页数，pagesize表示页大小，size*pagesize就是缓冲池的内存大小）
db2 create bufferpool bp16k size 1000 pagesize 16k
# 先删除之前创建的表空间（注意：确保目录为空）
rm -rf /data/db2/TBS_OTH_DAT
rm -rf /data/db2/TBS_OTH_IDX
rm -rf /data/db2/TBS_TEMP
chown -R db2inst1:db2iadm1 /data/db2
db2 "create tablespace TBS_OTH_DAT pagesize 16k managed by database using(file '/data/db2/TBS_OTH_DAT' 10G) bufferpool bp16k"
db2 "create tablespace TBS_OTH_IDX pagesize 16k managed by database using(file '/data/db2/TBS_OTH_IDX' 1G) bufferpool bp16k"
db2 "create TEMPORARY tablespace TBS_TEMP pagesize 16k managed by database using(file '/data/db2/TBS_TEMP' 1G) bufferpool bp16k"

3.3. 扩容

db2 "alter tablespace <tablespace_name> extend (all <page_numbers>)"
db2 alter "tablespace IBMDB2SAMPLEREL extend (all 1000)"
db2 "alter tablespace <tablespace_name> add (FILE '<new_container_file>' <size>)"
alter tablespace nnc_index01 add (file '\db2\nnc_index01_2' 204800)

4. 导入导出操作

4.1. CSV导出

# 1. 生成列标题
db2 "EXPORT TO /tmp/header.csv of del  
SELECT 
SUBSTR(REPLACE(REPLACE(XMLSERIALIZE(CONTENT XMLAGG(XMLELEMENT(NAME c,colname) 
ORDER BY colno) AS VARCHAR(1500)),'<C>',', '),'</C>',''),3) 
FROM syscat.columns WHERE tabschema=${SCHEMA} and tabname=${TABLE}"
# 2. 查询主体
db2 "EXPORT TO /tmp/body.csv of del SELECT * FROM ${SCHEMA}.${TABLE}"
# 3. 合并
cat /tmp/header.csv /tmp/body.csv > ${TABLE}.csv

4.2. 管道方式导入导出

• 示例1（导出，已验证） ```sql mkfifo aa.fifo

db2 “export to aa.fifo of del select * from xxx”

db2 “import from aa.fifo of del insert into xxx”

- **示例2（导出，未测试）**
```bash
mkfifo ${target_file_tmp}
(( $? != 0 )) && print "\nERROR: failed to create named pipe ${target_file_tmp}" && exit 1
db2 -v "EXPORT TO ${target_file_header} of del SELECT 'COL1', 'COL2', 'COL3' FROM sysibm.sysdummy1 "
cat ${target_file_header} ${target_file_tmp} >> ${target_file} & 
(( $? > 0 )) && print "Failed to append ${target_file} . Check permissions and free space" && exit 1
db2 -v "EXPORT TO ${target_file_tmp} of del SELECT  COL1, COL2, COL3 FROM myTable ORDER BY 1 "
rc=$?
(( rc == 1 )) && print "Export found no rows matching the query" && exit 1
(( rc == 2 )) && print "Export completed with warnings, your data might not be what you expect" && exit 1
(( rc > 2 )) && print "Export failed. Check the messages from export" && exit 1

• 示例3（导入，未测试） ```sql — 创建表并启动 LOAD 的示例 DB2 脚本 CONNECT TO SAMPLE;

CREATE TABLE PIPE_TABLE ( “ID” NUMBER(10) NOT NULL , “NAME” VARCHAR2(35) , “LOC_ID” NUMBER(4)
);

LOAD FROM “\.\pipe\mynamedpipe” OF DEL MODIFIED BY CODEPAGE=1208 COLDEL~ ANYORDER
USEDEFAULTS CHARDEL”” DELPRIORITYCHAR NOROWWARNINGS METHOD P (1,2,3) MESSAGES “db2tabledata.txt” REPLACE INTO PIPE_TABLE ( “ID”, “NAME”, “LOC_ID” ) NONRECOVERABLE INDEXING MODE AUTOSELECT ;

TERMINATE;

<a name="JtakJ"></a>
## 4.3. 游标方式导入
1. 远程数据库导入。
```bash
-- 1. 编目
CATALOG TCPIP NODE TargetND REMOTE xxx.xxx.xxx.xxx SERVER 50001;
CATALOG DATABASE targetdb AT NODE TargetND;
db2 connect to targetdb
db2 "declare mycursor cursor database localdb user db2page using D4rkh4wk for select * from $SCHEMA.$TABLE"
db2 "load from mycursor of cursor insert into $SCHEMA.$TABLE nonrecoverable"
db2 connect reset

1. 本地数据库导入。

   db2 connect to localdb
   db2 "declare mycursor cursor for select * from tabschema.tabname"
   db2 "load from mycursor of cursor insert into tabschema.tabname nonrecoverable"
   db2 connect reset

   5. 重建操作

   5.1. 重建表空间

   select 'reorg table ' || tabschema||'.'||tabname|| ' use tempspace1;' from syscat.tables where tbspaceid=3

   6. 表查询

   6.1. 行列转置

   6.1.1. 行转列

• 样例数据 ```sql YEAR QUARTER RESULTS

2004 1 20 2004 2 30 2004 3 15 2004 4 10 2005 1 18 2005 2 40 2005 3 12 2005 4 27

- **建表语句**
```sql
CREATE TABLE Sales (Year INT, Quarter INT, Results INT);

• SQL语句

  SELECT 
       Year,
       MAX(CASE WHEN Quarter = 1 THEN Results END) AS Q1,
       MAX(CASE WHEN Quarter = 2 THEN Results END) AS Q2,
       MAX(CASE WHEN Quarter = 3 THEN Results END) AS Q3,
       MAX(CASE WHEN Quarter = 4 THEN Results END) AS Q4
  FROM Sales
  GROUP BY Year

• 效果

  • 正常SQL效果如下： ```sql YEAR Q1 Q2 Q3 Q4

   2004          20          30          15          10
   2005          18          40          12          27

   - **不加`MAX()`效果如下：**
```sql
       YEAR          Q1          Q2          Q3          Q4
----------- ----------- ----------- ----------- -----------
       2004          20           -           -           -
       2004           -          30           -           -
       2004           -           -          15           -
       2004           -           -           -          10
       2005          18           -           -           -
       2005           -          40           -           -
       2005           -           -          12           -
       2005           -           -           -          27

6.1.2. 列转行

• 样例数据 ```sql YEAR Q1 Q2 Q3 Q4

2004 20 30 15 10 2005 18 40 12 27

- **建表语句**
```sql
CREATE TABLE SalesAgg(  
 year INTEGER,
   q1 INTEGER,
   q2 INTEGER,
   q3 INTEGER,
   q4 INTEGER 
);

• SQL语句

  -- 用table函数创建虚表，跟from子句中的表产生了关系，取出表中的一列作为数据
  SELECT 
    S.Year, 
    Q.Quarter, 
    Q.Results
  FROM SalesAgg AS S,
  TABLE(VALUES(1, S.q1),(2, S.q2),(3, S.q3),(4, S.q4)) AS Q(Quarter, Results);

• 效果 ```sql

   YEAR     QUARTER     RESULTS

   2004           1          20
   2004           2          30
   2004           3          15
   2004           4          10
   2005           1          18
   2005           2          40
   2005           3          12
   2005           4          27

- **补充**
```sql
-- 示例：用values实现多行表（虚表）
select * from (values (1,2),(2,3)) as t1(col1,col2);
-- 执行结果如下：
       COL1        COL2
----------- -----------
          1           2
          2           3

参考

ChinaUnix：DB2数据仓库高效迁移数据的5个方法
http://blog.chinaunix.net/uid-25723371-id-5046698.html