DB2 常用語法速查：序列、分頁、類型轉換、日期時間、字串、數學函數、備份恢復、LOAD、WITH 查詢

1. 建立序列

CREATE SEQUENCE "ZGC3"."SP_SEQ_MO_wbk" AS INTEGER
  MINVALUE 0 MAXVALUE 2147483647
  START WITH 21 INCREMENT BY 1
  CACHE 20 NO CYCLE NO ORDER;
SELECT NEXT VALUE FOR my_seq FROM SYSIBM.SYSDUMMY1;

2. 分頁查詢

SELECT * FROM (
  SELECT ROWNUMBER() OVER() AS ROWID, DESCRIPTION FROM SS.T_ROLE
) AS a WHERE a.ROWID BETWEEN 5 AND 8;

3. 類型轉換函數

• 數值：DECIMAL, DOUBLE, INTEGER, SMALLINT, REAL, HEX
• 字串：CHAR, VARCHAR, DIGITS
• 日期時間：DATE, TIME, TIMESTAMP

4. 日期時間函數

YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND
DAYOFYEAR, DAYOFWEEK, DAYS, MIDNIGHT_SECONDS
MONTHNAME, DAYNAME

5. 字串函數

LENGTH, LCASE, UCASE, LTRIM, RTRIM, COALESCE, CONCAT
INSERT, LEFT, LOCATE, POSSTR, REPEAT, REPLACE, RIGHT, SPACE, SUBSTR

6. 數學函數

ABS, COUNT, MAX, MIN, SUM, CEIL, FLOOR, MOD, RAND, POWER, ROUND, SIGN, TRUNCATE

7. 其他

NULLIF(arg1,arg2) -- 相等回傳 NULL，否則回傳 arg1

8. 備份/恢復指令

# 離線備份
db2stop force; db2start
db2 backup db 
# 線上備份
db2 connect to  user  using 

db2 backup db 
# 恢復
db2 restore db  taken at <時間戳>

db2 load from s.del of del insert into table

WITH temp(t1,t2,t3,t4,t5) AS (
  SELECT conntr_no,org_cd,dept_cd,SUM(acc_bal),SUM(avg_bal_Y)
  FROM deposit_m_acct WHERE org_cd='802000050'
  GROUP BY conntr_no,org_cd,dept_cd
)
SELECT t1,t2,t3,t4 FROM temp
UNION ALL
SELECT t1,t2,t3,t5 FROM temp;