DB2常用语句

--创建索引
CREATE INDEX IOL.IDX_IOL_CUSTID ON IOL.CMS_DXLOANBAL(CUSTID);
CREATE INDEX IOL.IDX_IOL_LOANACNO ON IOL.CMS_DXLOANBAL(LOANACNO);
CREATE INDEX IOL.IDX_IOL_LISTID ON IOL.CMS_DXLOANBAL(LISTID);
CREATE INDEX IOL.IDX_IOL_LOANID ON IOL.CMS_DXLOANBAL(LOANID);
CREATE INDEX IOL.IDX_IOL_CONTNO ON IOL.CMS_DXLOANBAL(CONTNO);

--判断重复
SELECT * FROM IOL.CMS_DXLOANBAL WHERE CUSTID IN(
   SELECT LOANACNO FROM iol.CMS_DXRETUSTAT GROUP BY (LOANACNO)  HAVING COUNT(LOANACNO)>1
)

ALTER TABLE T_ICIRS_CTRCT_CERT_REL add RPT_DATE DATE;
ALTER TABLE T_ICIRS_CTRCT_CERT_REL drop column RPT_DATE;
alter table T_ODS_BASE_COLL_MP_MATERIAL ALTER COLUMN  MPNM SET DATA TYPE varchar(80);

id integer not null generated always as identity(start with 1,increment by 1)
ID BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (MAXVALUE 922337203685477),

ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY(START WITH 1,INCREMENT BY 1,MAXVALUE 999999999999)

alter table ukey_info_tab alter column ukeyid restart with 12000

SELECT max(id)+1 FROM T_SYSTEM_LOCALE
--5079实际最大id+1
ALTER TABLE T_SYSTEM_LOCALE ALTER id RESTART WITH 5079; 

 

SELECT DISTINCT(name)
FROM (
    SELECT id, name,age,1 as row_num FROM test_b
    UNION ALL
    SELECT id,name,age,2 as row_num FROM test_c
) tbl

GROUP BY id,name,age
 HAVING count(*) = 1
ORDER BY id;

TRUNCATE TABLE T_RRYPISLT92_TMP IMMEDIATE;

--更改类型后解挂
CALL SYSPROC.admin_cmd('reorg table T_SYSTEM_TABLETMPL');

SELECT * FROM MBTQ_PROPERTY_CFG_GLOBAL WHERE propertyname = 'ifsToPbocOrgPasswordNameSpace'
UPDATE MBTQ_PROPERTY_CFG_GLOBAL t1 
SET t1.PROPERTYVALUE = (SELECT t2.PROPERTYVALUE FROM MBTQ_PROPERTY_CFG t2 WHERE t2.propertyname = t1.propertyname )
WHERE exists(SELECT 1 FROM MBTQ_PROPERTY_CFG t2 WHERE t2.propertyname = t1.propertyname )

SELECT * FROM A WHERE EXISTS (SELECT 1 FROM B WHERE B.ID = A.ID);

--默认表
select '1' from sysibm.sysdummy1;

select LOCATE('-','314123423-1') from sysibm.sysdummy1;

RENAME TABLE TMP_DACUSTINCOME_09 TO TMP_DACUSTINCOME_07;