# 创建系统维护的MQT（实时刷新）
CREATE TABLE MQT_REALTIME_SALES AS (
SELECT
DATE(CREATE_TIME) AS SALES_DATE,
PRODUCT_ID,
SUM(QUANTITY) AS TOTAL_QUANTITY,
SUM(TOTAL_AMOUNT) AS TOTAL_AMOUNT,
COUNT(*) AS ORDER_COUNT
FROM ORDER_ITEM
GROUP BY DATE(CREATE_TIME), PRODUCT_ID
) DATA INITIALLY DEFERRED REFRESH IMMEDIATE
ORGANIZE BY COLUMN;

REFRESH TABLE MQT_REALTIME_SALES;

# 创建每小时聚合的MQT
CREATE TABLE MQT_REALTIME_SALES_HOUR AS (
SELECT
DATE(CREATE_TIME) AS SALES_DATE,
HOUR(CREATE_TIME) AS SALES_HOUR,
PRODUCT_ID,
SUM(QUANTITY) AS TOTAL_QUANTITY,
SUM(TOTAL_AMOUNT) AS TOTAL_AMOUNT,
COUNT(*) AS ORDER_COUNT
FROM ORDER_ITEM
GROUP BY DATE(CREATE_TIME), HOUR(CREATE_TIME), PRODUCT_ID
) DATA INITIALLY DEFERRED REFRESH IMMEDIATE
ORGANIZE BY COLUMN;

REFRESH TABLE MQT_REALTIME_SALES_HOUR;

# 检查MQT刷新状态
SELECT
SUBSTR(TABSCHEMA, 1, 20) AS TABSCHEMA,
SUBSTR(TABNAME, 1, 40) AS TABNAME,
REFRESH,
INITIAL_REFRESH,
STATS_TIME
FROM SYSCAT.TABLES
WHERE TABSCHEMA = CURRENT SCHEMA
AND TABNAME LIKE ‘MQT_REALTIME%’;

# 创建存储过程：刷新实时MQT
CREATE OR REPLACE PROCEDURE SP_REFRESH_REALTIME_MQT()
LANGUAGE SQL
BEGIN
REFRESH TABLE MQT_REALTIME_SALES;
REFRESH TABLE MQT_REALTIME_SALES_HOUR;
REFRESH TABLE MQT_REALTIME_INVENTORY;
REFRESH TABLE MQT_REALTIME_ORDER;

RUNSTATS ON TABLE MQT_REALTIME_SALES WITH DISTRIBUTION;
RUNSTATS ON TABLE MQT_REALTIME_SALES_HOUR WITH DISTRIBUTION;
RUNSTATS ON TABLE MQT_REALTIME_INVENTORY WITH DISTRIBUTION;
RUNSTATS ON TABLE MQT_REALTIME_ORDER WITH DISTRIBUTION;
END;

# 创建任务：每5分钟刷新MQT
CREATE EVENT SCHEDULE SCHEDULE_REFRESH_MQT
AT (
EVERY 5 MINUTES
STARTING ‘2026-04-01-00.00.00.000000’
)
DO CALL SP_REFRESH_REALTIME_MQT();

# 创建Shell脚本：手动刷新MQT
#!/bin/bash
# refresh_mqt.sh

echo “开始刷新实时MQT…”

db2 connect to FGEDB

db2 “CALL SP_REFRESH_REALTIME_MQT()”

db2 connect reset

echo “MQT刷新完成”

# 添加到crontab，每5分钟执行
# */5 * * * * /db2scripts/refresh_mqt.sh >> /db2logs/refresh_mqt.log 2>&1

# 今日实时销售统计
SELECT
SALES_DATE,
SALES_HOUR,
SUM(TOTAL_QUANTITY) AS TOTAL_QUANTITY,
SUM(TOTAL_AMOUNT) AS TOTAL_AMOUNT,
SUM(ORDER_COUNT) AS ORDER_COUNT
FROM MQT_REALTIME_SALES_HOUR
WHERE SALES_DATE = CURRENT DATE
GROUP BY SALES_DATE, SALES_HOUR
ORDER BY SALES_HOUR;

# 今日实时销售排行
SELECT
p.PRODUCT_ID,
p.PRODUCT_NAME,
p.CATEGORY_NAME,
m.TOTAL_QUANTITY,
m.TOTAL_AMOUNT,
m.ORDER_COUNT
FROM MQT_REALTIME_SALES m
INNER JOIN PRODUCT p ON m.PRODUCT_ID = p.PRODUCT_ID
WHERE m.SALES_DATE = CURRENT DATE
ORDER BY m.TOTAL_AMOUNT DESC
FETCH FIRST 10 ROWS ONLY;

# 本周销售趋势
SELECT
SALES_DATE,
DAYNAME(SALES_DATE) AS DAY_NAME,
SUM(TOTAL_AMOUNT) AS DAILY_AMOUNT
FROM MQT_REALTIME_SALES
WHERE SALES_DATE BETWEEN CURRENT DATE – 6 DAYS AND CURRENT DATE
GROUP BY SALES_DATE
ORDER BY SALES_DATE;

# 实时订单监控
SELECT
ORDER_STATUS,
COUNT(*) AS ORDER_COUNT,
SUM(TOTAL_AMOUNT) AS TOTAL_AMOUNT
FROM ORDERS
WHERE CREATE_TIME >= CURRENT TIMESTAMP – 1 HOUR
GROUP BY ORDER_STATUS;

# 实时库存监控
SELECT
w.WAREHOUSE_ID,
w.WAREHOUSE_NAME,
COUNT(*) AS PRODUCT_COUNT,
SUM(CASE WHEN i.QUANTITY < i.MIN_QUANTITY THEN 1 ELSE 0 END) AS LOW_STOCK_COUNT FROM INVENTORY i INNER JOIN WAREHOUSE w ON i.WAREHOUSE_ID = w.WAREHOUSE_ID GROUP BY w.WAREHOUSE_ID, w.WAREHOUSE_NAME ORDER BY LOW_STOCK_COUNT DESC;

# 实时用户注册统计
SELECT
DATE(CREATE_TIME) AS REGISTER_DATE,
HOUR(CREATE_TIME) AS REGISTER_HOUR,
COUNT(*) AS REGISTER_COUNT
FROM USER_ACCOUNT
WHERE CREATE_TIME >= CURRENT TIMESTAMP – 24 HOURS
GROUP BY DATE(CREATE_TIME), HOUR(CREATE_TIME)
ORDER BY REGISTER_DATE, REGISTER_HOUR;

# 实时在线用户统计
SELECT
COUNT(*) AS ONLINE_COUNT
FROM USER_SESSION
WHERE LAST_ACTIVE_TIME >= CURRENT TIMESTAMP – 5 MINUTES;

# 实时用户活跃统计
SELECT
u.USER_ID,
u.USERNAME,
COUNT(DISTINCT s.SESSION_ID) AS SESSION_COUNT,
COUNT(*) AS ACTION_COUNT,
MAX(s.LAST_ACTIVE_TIME) AS LAST_ACTIVE_TIME
FROM USER_ACCOUNT u
INNER JOIN USER_SESSION s ON u.USER_ID = s.USER_ID
WHERE s.LAST_ACTIVE_TIME >= CURRENT TIMESTAMP – 1 HOUR
GROUP BY u.USER_ID, u.USERNAME
ORDER BY ACTION_COUNT DESC
FETCH FIRST 20 ROWS ONLY;

# 实时页面访问统计
SELECT
PAGE_URL,
COUNT(*) AS VIEW_COUNT,
COUNT(DISTINCT USER_ID) AS USER_COUNT
FROM PAGE_VIEW
WHERE VIEW_TIME >= CURRENT TIMESTAMP – 1 HOUR
GROUP BY PAGE_URL
ORDER BY VIEW_COUNT DESC
FETCH FIRST 10 ROWS ONLY;

# 为实时查询创建专门的索引
CREATE INDEX IDX_ORDER_CREATE_TIME ON ORDERS(CREATE_TIME DESC);
CREATE INDEX IDX_ORDER_ITEM_CREATE_TIME ON ORDER_ITEM(CREATE_TIME DESC);
CREATE INDEX IDX_USER_CREATE_TIME ON USER_ACCOUNT(CREATE_TIME DESC);
CREATE INDEX IDX_PAGE_VIEW_TIME ON PAGE_VIEW(VIEW_TIME DESC);

# 使用WITH UR（Uncommitted Read）提高并发
SELECT
COUNT(*) AS ORDER_COUNT,
SUM(TOTAL_AMOUNT) AS TOTAL_AMOUNT
FROM ORDERS WITH UR
WHERE CREATE_TIME >= CURRENT TIMESTAMP – 1 HOUR;

# 使用SKIP LOCKED避免锁等待
SELECT
ORDER_ID,
ORDER_STATUS,
TOTAL_AMOUNT
FROM ORDERS
WHERE ORDER_STATUS = ‘PENDING’
AND CREATE_TIME >= CURRENT TIMESTAMP – 1 HOUR
SKIP LOCKED DATA;

# 只查询需要的列
SELECT
COUNT(*) AS ORDER_COUNT,
SUM(TOTAL_AMOUNT) AS TOTAL_AMOUNT
FROM ORDERS
WHERE CREATE_TIME >= CURRENT TIMESTAMP – 1 HOUR;

# 避免使用SELECT *

# 增大缓冲池
ALTER BUFFERPOOL BP32K SIZE 100000;

# 配置排序堆
UPDATE DATABASE CONFIGURATION USING SORTHEAP 4096;
UPDATE DATABASE CONFIGURATION USING SHEAPTHRES_SHR 100000;

# 配置日志缓冲
UPDATE DATABASE CONFIGURATION USING LOGBUFSZ 4096;

# 配置并行处理
UPDATE DATABASE CONFIGURATION USING DFT_DEGREE 4;

# 监控缓冲池命中率
SELECT
SUBSTR(BP_NAME, 1, 20) AS BP_NAME,
TOTAL_LOGICAL_READS,
TOTAL_PHYSICAL_READS,
CASE
WHEN TOTAL_LOGICAL_READS > 0
THEN ROUND((1 – TOTAL_PHYSICAL_READS * 1.0 / TOTAL_LOGICAL_READS) * 100, 2)
ELSE 100.0
END AS HIT_RATIO_PERCENT
FROM SYSIBMADM.BP_HITRATIO;

# 监控长时间运行的查询
SELECT
SUBSTR(STMT_TEXT, 1, 100) AS STMT_TEXT,
TOTAL_EXEC_TIME,
TOTAL_USR_CPU_TIME,
TOTAL_SYS_CPU_TIME,
TOTAL_WAIT_TIME
FROM SYSIBMADM.TOP_DYNAMIC_SQL
ORDER BY TOTAL_EXEC_TIME DESC
FETCH FIRST 10 ROWS ONLY;