# 查看现有临时表空间
SELECT
TBSP_NAME,
TBSP_TYPE,
TBSP_CONTENT_TYPE,
TBSP_PAGE_SIZE,
TBSP_TOTAL_PAGES,
TBSP_USED_PAGES
FROM SYSIBMADM.TBSP_UTILIZATION
WHERE TBSP_CONTENT_TYPE IN (‘USR_TEMP’, ‘SYS_TMP’);

# 创建系统临时表空间
CREATE SYSTEM TEMPORARY TABLESPACE TEMP_SYS_8K
PAGESIZE 8K
MANAGED BY DATABASE
USING (FILE ‘/db2data/temp/temp_sys_8k.dms’ 5G)
EXTENTSIZE 32
PREFETCHSIZE 128
BUFFERPOOL BP8K;

CREATE SYSTEM TEMPORARY TABLESPACE TEMP_SYS_16K
PAGESIZE 16K
MANAGED BY DATABASE
USING (FILE ‘/db2data/temp/temp_sys_16k.dms’ 5G)
EXTENTSIZE 32
PREFETCHSIZE 128
BUFFERPOOL BP16K;

CREATE SYSTEM TEMPORARY TABLESPACE TEMP_SYS_32K
PAGESIZE 32K
MANAGED BY DATABASE
USING (FILE ‘/db2data/temp/temp_sys_32k.dms’ 5G)
EXTENTSIZE 32
PREFETCHSIZE 128
BUFFERPOOL BP32K;

# 创建用户临时表空间
CREATE USER TEMPORARY TABLESPACE TEMP_USR_8K
PAGESIZE 8K
MANAGED BY DATABASE
USING (FILE ‘/db2data/temp/temp_usr_8k.dms’ 2G)
EXTENTSIZE 32
PREFETCHSIZE 128
BUFFERPOOL BP8K;

# 创建缓冲池
CREATE BUFFERPOOL BP8K SIZE 50000 PAGESIZE 8K;
CREATE BUFFERPOOL BP16K SIZE 25000 PAGESIZE 16K;
CREATE BUFFERPOOL BP32K SIZE 12500 PAGESIZE 32K;

# 配置排序堆大小
UPDATE DATABASE CONFIGURATION USING SORTHEAP 8192;

# 配置排序堆阈值
UPDATE DATABASE CONFIGURATION USING SHEAPTHRES 65536;
UPDATE DATABASE CONFIGURATION USING SHEAPTHRES_SHR 65536;

# 查看排序配置
GET DATABASE CONFIGURATION;

# 查看排序使用情况
SELECT
TOTAL_SORTS,
TOTAL_SORT_OVERFLOWS,
CASE
WHEN TOTAL_SORTS > 0
THEN ROUND(TOTAL_SORT_OVERFLOWS * 100.0 / TOTAL_SORTS, 2)
ELSE 0
END AS SORT_OVERFLOW_PERCENT
FROM SYSIBMADM.SNAPDB;

# 监控排序性能
SELECT
SUBSTR(APPL_NAME, 1, 30) AS APPL_NAME,
TOTAL_SORTS,
TOTAL_SORT_TIME,
SORT_OVERFLOWS
FROM SYSIBMADM.APPLICATIONS
WHERE TOTAL_SORTS > 0
ORDER BY TOTAL_SORT_TIME DESC;

# 创建声明全局临时表
DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_ORDER (
ORDER_ID VARCHAR(32),
CUSTOMER_ID VARCHAR(32),
TOTAL_AMOUNT DECIMAL(18, 2),
ORDER_STATUS VARCHAR(20)
) ON COMMIT PRESERVE ROWS
NOT LOGGED
IN TEMP_USR_8K;

# 插入数据
INSERT INTO SESSION.TEMP_ORDER
SELECT
ORDER_ID,
CUSTOMER_ID,
TOTAL_AMOUNT,
ORDER_STATUS
FROM ORDERS
WHERE CREATE_TIME BETWEEN ‘2026-04-01’ AND ‘2026-04-30’;

# 查询临时表
SELECT * FROM SESSION.TEMP_ORDER;

# 创建带索引的临时表
DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_CUSTOMER (
CUSTOMER_ID VARCHAR(32),
CUSTOMER_NAME VARCHAR(200),
TOTAL_ORDERS INTEGER,
TOTAL_AMOUNT DECIMAL(18, 2)
) ON COMMIT PRESERVE ROWS
NOT LOGGED
IN TEMP_USR_8K;

CREATE INDEX SESSION.IDX_TEMP_CUSTOMER ON SESSION.TEMP_CUSTOMER(CUSTOMER_ID);

# 删除临时表
DROP TABLE SESSION.TEMP_ORDER;

# 清空临时表
DELETE FROM SESSION.TEMP_ORDER;

# 使用ON COMMIT PRESERVE ROWS保持数据
DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_DATA (
ID INTEGER,
NAME VARCHAR(100)
) ON COMMIT PRESERVE ROWS
NOT LOGGED;

# 使用ON COMMIT DELETE ROWS自动清空
DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_DATA2 (
ID INTEGER,
NAME VARCHAR(100)
) ON COMMIT DELETE ROWS
NOT LOGGED;

# 使用NOT LOGGED提高性能
DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_LARGE (
ID INTEGER,
DATA VARCHAR(1000)
) ON COMMIT PRESERVE ROWS
NOT LOGGED;

# 批量处理使用临时表
# 1. 创建临时表
DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_PROCESS (
ORDER_ID VARCHAR(32),
PROCESS_STATUS VARCHAR(20)
) ON COMMIT PRESERVE ROWS
NOT LOGGED;

# 2. 插入待处理数据
INSERT INTO SESSION.TEMP_PROCESS
SELECT ORDER_ID, ‘PENDING’
FROM ORDERS
WHERE ORDER_STATUS = ‘NEW’;

# 3. 处理数据
UPDATE ORDERS o
SET ORDER_STATUS = ‘PROCESSING’
WHERE EXISTS (
SELECT 1 FROM SESSION.TEMP_PROCESS t
WHERE t.ORDER_ID = o.ORDER_ID
);

# 4. 更新临时表状态
UPDATE SESSION.TEMP_PROCESS
SET PROCESS_STATUS = ‘DONE’;

# 5. 清理
DROP TABLE SESSION.TEMP_PROCESS;

# 查看临时表空间使用情况
SELECT
TBSP_NAME,
TBSP_TYPE,
TBSP_CONTENT_TYPE,
TBSP_PAGE_SIZE,
TBSP_USED_PAGES,
TBSP_FREE_PAGES,
ROUND(TBSP_USED_PAGES * 100.0 / TBSP_TOTAL_PAGES, 2) AS USAGE_PERCENT
FROM SYSIBMADM.TBSP_UTILIZATION
WHERE TBSP_CONTENT_TYPE IN (‘USR_TEMP’, ‘SYS_TMP’)
ORDER BY TBSP_NAME;

# 查看临时表空间活动
SELECT
TBSP_NAME,
TBSP_READS,
TBSP_WRITES,
TBSP_READ_TIME,
TBSP_WRITE_TIME
FROM SYSIBMADM.TBSP_UTILIZATION
WHERE TBSP_CONTENT_TYPE IN (‘USR_TEMP’, ‘SYS_TMP’);

# 查看使用临时表空间的SQL
SELECT
SUBSTR(STMT_TEXT, 1, 100) AS STMT_TEXT,
TOTAL_SORTS,
TOTAL_SORT_OVERFLOWS,
TOTAL_SORT_TIME
FROM SYSIBMADM.TOP_DYNAMIC_SQL
WHERE TOTAL_SORT_OVERFLOWS > 0
ORDER BY TOTAL_SORT_OVERFLOWS DESC;

# 监控排序溢出
SELECT
TOTAL_SORTS,
TOTAL_SORT_OVERFLOWS,
POST_THRESHOLD_SORTS,
POST_SHRTHRESHOLD_SORTS
FROM SYSIBMADM.SNAPDB;

# 1. 增大SORTHEAP
UPDATE DATABASE CONFIGURATION USING SORTHEAP 16384;

# 2. 增大临时表空间
ALTER TABLESPACE TEMP_SYS_8K
RESIZE (FILE ‘/db2data/temp/temp_sys_8k.dms’ 10G);

# 3. 优化SQL减少排序
# 添加索引避免排序
CREATE INDEX IDX_ORDER_DATE ON ORDERS(CREATE_TIME DESC);

# 使用索引扫描代替排序
SELECT * FROM ORDERS
WHERE CREATE_TIME > ‘2026-04-01’
ORDER BY CREATE_TIME;

# 4. 分区减少排序数据量
SELECT * FROM ORDERS
WHERE CREATE_TIME BETWEEN ‘2026-04-01’ AND ‘2026-04-30’
ORDER BY CREATE_TIME;

# 5. 使用MQT预计算
CREATE TABLE MQT_ORDER_SUMMARY AS (
SELECT
DATE(CREATE_TIME) AS ORDER_DATE,
COUNT(*) AS ORDER_COUNT,
SUM(TOTAL_AMOUNT) AS TOTAL_AMOUNT
FROM ORDERS
GROUP BY DATE(CREATE_TIME)
) DATA INITIALLY DEFERRED REFRESH DEFERRED;

# 6. 监控并优化
# 定期检查排序溢出率
# 如果超过5%，需要增大SORTHEAP或优化SQL