Another new thing for me at the current client is DB2. I’ve always been working with Oracle.
So here I will list out the things I find useful for DB2. (I’m not going to be too low level on this project, but some tidbits may pop up)
First one:
Querying for a column name:
SELECT TBNAME, NAME FROM SYSIBM.SYSCOLUMNS WHERE NAME LIKE ‘%IDENT%’ and TBNAME LIKE ‘S_%’
http://philipcarrington.wordpress.com/2011/01/19/vargraphic-vs-varchar/
Vargraphic data type uses
wanting to delete duplicates, but keep 1:
(IDENTIFICATION is the table.. the partition by are the fields that logically define the dups)
DELETE FROM IDENTIFICATION
WHERE RID(IDENTIFICATION)
IN (SELECT rid
FROM (SELECT RID(IDENTIFICATION) rid
, ROWNUMBER() OVER(PARTITION BY SRC_SYS_PERSON_ID , IDENTITY_NUM , SRC_SYS_ID) rn
FROM IDENTIFICATION )
WHERE rn > 1 )
;
Equivalent to Oracle’s DUAL table, in DB2:
sysibm.sysdummy1
limit rows returned:
select * from employee fetch first 100 rows only
DB2 Materialized Query Tables (MQTs) compared to Oracle Materialized Views.. attaching a PDF…