DB2——笔记

-----------集合差-------------------------

db2:except

oracle:minus

select * from dzz_01exceptselect * from dzz_02

01减去（01和02中相同的记录）后剩下的记录

---------集合交集-----------------

intersect

intersect all

SELECT id FROM tmp.DZZ_T4 WHERE id in (1,2,3,4)intersectSELECT id FROM tmp.DZZ_T3 WHERE id in (1,2,3,4)

------插入表数据-------------------------

insert into TMP.dzz_t11(ID,NAME,AGE,DATE1) SELECT ID,NAME,AGE,SYSDATE FROM TMP.DZZ_T1

insert into TMP.dzz_t11(ID,NAME,AGE) (SELECT A.* FROM TMP.DZZ_T1 A)

------分页-------------------------

select * from (SELECT * 	FROM (        SELECT * FROM TMP.DZZ_T1 		order by id asc        FETCH FIRST 20 ROWS ONLY	) A    order by a.id desc     FETCH first 5 ROWS ONLY) b order by b.id ascFETCH first 5 ROWS ONLY

select * from (SELECT a.* FROM (				SELECT * FROM 				TMP.DZZ_T1 				where id<21 				order by id desc				FETCH FIRST 20 ROWS ONLY					) A              FETCH first 10 ROWS ONLY) border by b.id asc

--更改表字段名-------------

-- 先删除再新增加

alter table tda.t_jfxt_cust_info drop column etl_dtgoalter table tda.t_jfxt_cust_info add column biz_dt VARCHAR(8)

Operation not allowed for reason code "7" on table "TDA.T_JFXT_CUST_INFO"..

SQLCODE=-668, SQLSTATE=57016, DRIVER=3.67.28

-----------调用存储过程---------------------------

call tmp.DZZ_PRO()

call sysproc.admin_cmd('reorg table tda.t_qnjf_cust_info')

chr(34)==""双引号

--------------------表占空间大小-------------

SELECT TABNAME 表名    ,SUM(DATA_OBJECT_L_SIZE)/1024 "逻辑空间大小(M)"    ,SUM(DATA_OBJECT_P_SIZE)/1024 "物理空间大小(M)"FROM TABLE(SYSPROC.ADMIN_GET_TAB_INFO('TMP','DZZ_T5')) AS T GROUP BY TABNAME

2021－7－21