DB2 z/OS DB2 LUW
z/OS CICS VTAM

DB2 SQL

Function SOUNDEX

select Name    from (
      values('Braeuer'), ('Bräuer'), ('Breuer')
    ,       ('Schmidd'), ('Schmid'), ('Schmidt'), ('Schmitt')
    ,       ('Schmit'), ('Schmied'), ('Schmiedt')
    ,       ('Müller'), ('Mueller'), ('Müler'), ('Mühler')
    ,       ('Miller'), ('Miler'), ('Miller')
    )T1 (Name)
WHERE UCase(SOUNDEX(Name)) = SOUNDEX('Müller')
order by Name
;

Ergebis :
Name
--------------------------
Miler
Miller
Miller
Mueller
Mühler
Müler
Müller

Rownumber

SELECT ROWNUMBER() OVER(ORDER BY Creator, Name)
, Creator,Name
FROM SYSIBM.Systables
;

Rechnen mit DB2

Select 11 * 2                    -- 22
     , 11 / 2                     -- 5
     , Decimal(11.0 / 2.0, 5,1)   -- 5.5
     , Mod(11 , 2)                -- 1 (5 Rest 1)
     , 11 + 2                     -- 13
     , 11 - 2                     -- 9
     , Power(10, 2)               -- 100
     , Int(SQRT(16))              -- 4
from sysibm.sysdummy1

Function ListAgg (V9.7 ab FP4 und db2updv97.exe)

SELECT Creator, TYPE, LISTAGG('"' CONCAT Name CONCAT '"', ' ; ')
    WITHIN GROUP (ORDER BY Name, Type)
FROM sysibm.systables
where creator Like 'SYS%'
-- AND TYPE = 'T'
GROUP BY Creator, Type
;

Ausgabe :
Creator Type Names
SYSCAT             V       "ATTRIBUTES" ; "AUDITPOLICIES" ; "AUDITUSE" ; "BUFFERPOOLDBPARTITIONS" ; ..... und so weiter
SYSIBM             T        "SYSATTRIBUTES" ; "SYSAUDITPOLICIES" ; "SYSAUDITUSE" ; "SYSBUFFERPOOLNODES" ; ..... und so weiter
SYSIBM             V        "CHECK_CONSTRAINTS" ; "COLUMNS" ; "COLUMNS_S" ; "DUAL" ; "PARAMETERS" ; ..... und so weiter
SYSIBMADM    V        "ADMINTABCOMPRESSINFO" ; "ADMINTABINFO" ; "ADMINTEMPCOLUMNS" ; ..... und so weiter
SYSPUBLIC       A        "DUAL"
SYSSTAT           V        "COLDIST" ; "COLGROUPDIST" ; "COLGROUPDISTCOUNTS" ; "COLGROUPS" ; "COLUMNS" ; ..... und so weiter
SYSTOOLS        T        "HMON_ATM_INFO" ; "HMON_COLLECTION" ; "POLICY"

Function XMLAGG z/OS

SELECT CREATOR, TYPE
     REPLACE(
     REPLACE(
     CAST(XMLSERIALIZE(XMLAGG(XMLELEMENT(NAME "TBN", '"'
                                                !! TB.NAME
                                                !! '";'
                                        )
                                         ORDER BY TB.NAME, TYPE
                     ) AS CLOB(32000)
                 )
            AS VARCHAR(32000) )
        , '</TBN>',',')
        , '<TBN>','')
AS TBLISTE
FROM SYSIBM.SYSTABLES TB
GROUP BY CREATOR, TYPE
;

Rownumber :

SELECT ROWNUMBER() OVER(ORDER BY Creator, Name)
, Creator,Name
FROM SYSIBM.Systables
;

Timestamp add hour:

Create Table tabelle (TAGESDATUM TIMESTAMP );

SELECT    TAGESDATUM
        , Hour(time('23:00:00'))
        , Hour(time('23:00:00')) - HOUR(time(TAGESDATUM))
        , TAGESDATUM + (23 - HOUR(time(TAGESDATUM))) HOUR
FROM tabelle
;

UPDATE tabelle     SET TAGESDATUM =
TAGESDATUM + (23 - HOUR(time(TAGESDATUM))) HOUR
;