zOs/TX/MFNVYDDL
SET CURRENT SQLID='S100447';
DROP TABLESPACE $db.A150A;
commit;
CREATE TABLESPACE A150A
IN $db
USING STOGROUP GSMS
FREEPAGE 30 PCTFREE 10
DSSIZE 32 G
NUMPARTS 100
BUFFERPOOL BP2
LOCKSIZE ANY
LOCKMAX 30000
CLOSE NO
COMPRESS YES
LOCKPART YES;
COMMIT;
--
------------------------------------------------------------------------
-- Table=$creator.$'$TMF'150A1 In $db.A150A
------------------------------------------------------------------------
--
SET CURRENT SCHEMA='$creator';
--
CREATE TABLE $creator.$'$TMF'150A1
(MF150001 CHAR(16) NOT NULL WITH DEFAULT,
MF150002 CHAR(1) NOT NULL WITH DEFAULT,
MF150003 CHAR(5) NOT NULL WITH DEFAULT,
MF150004 CHAR(25) NOT NULL WITH DEFAULT,
MF150005 CHAR(1) NOT NULL WITH DEFAULT,
MF150006 CHAR(1) NOT NULL WITH DEFAULT,
MF150007 CHAR(1) NOT NULL WITH DEFAULT,
MF150008 CHAR(3) NOT NULL WITH DEFAULT,
MF150009 CHAR(5) NOT NULL WITH DEFAULT,
MF150010 CHAR(4) NOT NULL WITH DEFAULT,
MF150011 CHAR(1) NOT NULL WITH DEFAULT,
MF150012 CHAR(1) NOT NULL WITH DEFAULT,
MF150013 DATE NOT NULL WITH DEFAULT,
MF150014 CHAR(4) NOT NULL WITH DEFAULT,
MF150015 CHAR(4) NOT NULL WITH DEFAULT,
MF150016 CHAR(4) NOT NULL WITH DEFAULT,
MF150017 CHAR(12) NOT NULL WITH DEFAULT,
MF150018 DECIMAL(15, 3) NOT NULL WITH DEFAULT,
MF150019 DECIMAL(15, 3) NOT NULL WITH DEFAULT,
MF150020 CHAR(8) NOT NULL WITH DEFAULT,
MF150021 TIMESTAMP NOT NULL WITH DEFAULT,
MF150022 CHAR(4) NOT NULL WITH DEFAULT,
MF150023 CHAR(12) NOT NULL WITH DEFAULT,
MF150024 CHAR(1) NOT NULL WITH DEFAULT,
MF150025 CHAR(3) NOT NULL WITH DEFAULT,
MF150026 CHAR(3) NOT NULL WITH DEFAULT,
MF150027 CHAR(3) NOT NULL WITH DEFAULT,
MF150028 CHAR(3) NOT NULL WITH DEFAULT,
MF150029 CHAR(3) NOT NULL WITH DEFAULT,
MF150030 CHAR(16) NOT NULL WITH DEFAULT,
MF150031 CHAR(2) NOT NULL WITH DEFAULT,
MF150032 CHAR(7) NOT NULL WITH DEFAULT,
MF150033 CHAR(7) NOT NULL WITH DEFAULT,
MF150034 CHAR(4) NOT NULL WITH DEFAULT,
MF150035 CHAR(1) NOT NULL WITH DEFAULT,
MF150036 CHAR(18) NOT NULL WITH DEFAULT,
MF150037 DECIMAL(15, 0) NOT NULL WITH DEFAULT,
MF150038 CHAR(16) NOT NULL WITH DEFAULT,
MF150039 CHAR(2) NOT NULL WITH DEFAULT,
MF150040 CHAR(2) NOT NULL WITH DEFAULT,
MF150041 CHAR(2) NOT NULL WITH DEFAULT,
MF150042 CHAR(4) NOT NULL WITH DEFAULT,
MF150043 DECIMAL(15, 3) NOT NULL WITH DEFAULT,
MF150044 CHAR(5) NOT NULL WITH DEFAULT,
MF150045 CHAR(4) NOT NULL WITH DEFAULT,
MF150046 CHAR(4) NOT NULL WITH DEFAULT,
MF150047 CHAR(1) NOT NULL WITH DEFAULT,
MF150051 CHAR(3) NOT NULL WITH DEFAULT,
MF150052 CHAR(3) NOT NULL WITH DEFAULT,
MF150053 DECIMAL(15, 3) NOT NULL WITH DEFAULT,
MF150054 CHAR(7) NOT NULL WITH DEFAULT,
MF150055 CHAR(1) NOT NULL WITH DEFAULT,
MF150056 CHAR(8) NOT NULL WITH DEFAULT,
MF150059 CHAR(5) NOT NULL WITH DEFAULT,
MF150060 TIMESTAMP NOT NULL WITH DEFAULT,
MF150062 CHAR(1) NOT NULL WITH DEFAULT,
MF150063 CHAR(1) NOT NULL WITH DEFAULT,
MF150064 DATE NOT NULL WITH DEFAULT,
MF150065 DATE NOT NULL WITH DEFAULT,
MF150066 CHAR(16) NOT NULL WITH DEFAULT,
MF150067 CHAR(16) NOT NULL WITH DEFAULT,
MF150068 CHAR(16) NOT NULL WITH DEFAULT,
MF150069 CHAR(4) NOT NULL WITH DEFAULT,
MF150070 CHAR(7) NOT NULL WITH DEFAULT,
MF150071 CHAR(1) NOT NULL WITH DEFAULT,
MF150072 CHAR(1) NOT NULL WITH DEFAULT,
MF150073 CHAR(1) NOT NULL WITH DEFAULT,
MF150074 CHAR(1) NOT NULL WITH DEFAULT,
MF150075 CHAR(1) NOT NULL WITH DEFAULT,
MF150076 CHAR(1) NOT NULL WITH DEFAULT,
MF150077 CHAR(6) NOT NULL WITH DEFAULT,
MF150078 CHAR(1) NOT NULL WITH DEFAULT,
MF150079 CHAR(1) NOT NULL WITH DEFAULT,
MF150080 DATE NOT NULL WITH DEFAULT,
MF150081 CHAR(5) NOT NULL WITH DEFAULT,
MF150082 CHAR(26) NOT NULL WITH DEFAULT,
MF150083 SMALLINT NOT NULL WITH DEFAULT,
MF150084 SMALLINT NOT NULL WITH DEFAULT,
MF150085 SMALLINT NOT NULL WITH DEFAULT,
MF150086 CHAR(35) NOT NULL WITH DEFAULT,
MF150087 CHAR(35) NOT NULL WITH DEFAULT,
MF150088 CHAR(35) NOT NULL WITH DEFAULT,
MF150089 CHAR(35) NOT NULL WITH DEFAULT,
MF150090 SMALLINT NOT NULL WITH DEFAULT,
MF150091 SMALLINT NOT NULL WITH DEFAULT,
MF150092 CHAR(16) NOT NULL WITH DEFAULT,
MF150019A CHAR(4) NOT NULL WITH DEFAULT '0010',
MF150HASH SMALLINT NOT NULL,
CONSTRAINT MF150001
PRIMARY KEY (MF150001))
IN $db.A150A
PARTITION BY (MF150HASH ASC)
(
part 1 values ( 105),
part 2 values ( 224),
part 3 values ( 340),
part 4 values ( 432),
part 5 values ( 554),
part 6 values ( 656),
part 7 values ( 770),
part 8 values ( 892),
part 9 values ( 991),
part 10 values ( 1115),
part 11 values ( 1211),
part 12 values ( 1315),
part 13 values ( 1435),
part 14 values ( 1524),
part 15 values ( 1651),
part 16 values ( 1751),
part 17 values ( 1867),
part 18 values ( 1988),
part 19 values ( 2079),
part 20 values ( 2153),
part 21 values ( 2273),
part 22 values ( 2367),
part 23 values ( 2492),
part 24 values ( 2609),
part 25 values ( 2675),
part 26 values ( 2795),
part 27 values ( 2901),
part 28 values ( 3015),
part 29 values ( 3134),
part 30 values ( 3233),
part 31 values ( 3311),
part 32 values ( 3432),
part 33 values ( 3538),
part 34 values ( 3659),
part 35 values ( 3770),
part 36 values ( 3845),
part 37 values ( 3966),
part 38 values ( 4075),
part 39 values ( 4191),
part 40 values ( 4311),
part 41 values ( 4417),
part 42 values ( 4542),
part 43 values ( 4651),
part 44 values ( 4763),
part 45 values ( 4879),
part 46 values ( 4983),
part 47 values ( 5105),
part 48 values ( 5214),
part 49 values ( 5333),
part 50 values ( 5457),
part 51 values ( 5566),
part 52 values ( 5691),
part 53 values ( 5802),
part 54 values ( 5918),
part 55 values ( 6034),
part 56 values ( 6149),
part 57 values ( 6273),
part 58 values ( 6385),
part 59 values ( 6505),
part 60 values ( 6608),
part 61 values ( 6720),
part 62 values ( 6844),
part 63 values ( 6956),
part 64 values ( 7070),
part 65 values ( 7179),
part 66 values ( 7295),
part 67 values ( 7420),
part 68 values ( 7540),
part 69 values ( 7650),
part 70 values ( 7749),
part 71 values ( 7862),
part 72 values ( 7989),
part 73 values ( 8119),
part 74 values ( 8225),
part 75 values ( 8332),
part 76 values ( 8447),
part 77 values ( 8531),
part 78 values ( 8659),
part 79 values ( 8778),
part 80 values ( 8889),
part 81 values ( 9011),
part 82 values ( 9088),
part 83 values ( 9216),
part 84 values ( 9340),
part 85 values ( 9444),
part 86 values ( 9562),
part 87 values ( 9664),
part 88 values ( 9733),
part 89 values ( 9862),
part 90 values ( 9971),
part 91 values (10084),
part 92 values (10206),
part 93 values (10269),
part 94 values (10396),
part 95 values (10512),
part 96 values (10620),
part 97 values (10747),
part 98 values (10843),
part 99 values (10971),
part 100 values (11046)
)
;
--
LABEL ON TABLE $creator.$'$TMF'150A1 IS 'Mittelflussrechnung';
--
LABEL ON $creator.$'$TMF'150A1
(MF150HASH IS 'PartitionierungsHash',
MF150019A IS 'Buchführungswährung',
MF150091 IS 'Limiten-Hierarchie',
MF150090 IS 'Limiten-Typ',
MF150089 IS 'Ort Begünstigter',
MF150088 IS 'Name Begünstigter',
MF150087 IS 'Ort Auftraggeber',
MF150086 IS 'Name Auftraggeber',
MF150085 IS 'Life-Cycle-Status',
MF150084 IS 'Relevant-Group',
MF150083 IS 'Transaction-Typ',
MF150082 IS 'Lade_Timestamp_ID',
MF150081 IS 'Preiskomponente',
MF150080 IS 'mutierbar bis',
MF150079 IS 'Depot_Uebertrag',
MF150078 IS 'Vorheriger GP-Typ',
MF150077 IS 'Bankenclearing-Nr',
MF150076 IS 'Kommentar_Status',
MF150075 IS 'Edit-Status Gesamt',
MF150074 IS 'EDIT-STATUS URHEBER',
MF150073 IS 'EDIT-STATUS GRUND',
MF150072 IS 'Edit-Status Gegenpartei',
MF150071 IS 'Prüfziffer Kundencif',
MF150070 IS 'Stammnummer Kundencif',
MF150069 IS 'Bankstelle Kundencif',
MF150068 IS 'int. Form OE Kundenbüro GP',
MF150067 IS 'int. Form OE Kundenbüro',
MF150066 IS 'int. Form OE-ID KV',
MF150065 IS 'Cif-Saldierungsdatum',
MF150064 IS 'Cif-Eröffnungsdatum',
MF150063 IS 'Mittelfluss-Identifikation',
MF150062 IS 'Coupon-Mantel',
MF150060 IS 'Erstellungstimestamp',
MF150059 IS 'Auftragslaufnummer',
MF150056 IS 'Endverfall Obligationen',
MF150055 IS 'Richtung des Mittelflusses',
MF150054 IS 'Leitweg',
MF150053 IS 'Volumen in Nominal/Stück',
MF150052 IS 'Bearbeitungscode',
MF150051 IS 'Domizil Gegenpartei',
MF150047 IS 'Buchungsart',
MF150046 IS 'eff.Depotstelle/Umbuchungskto',
MF150045 IS 'Depotstelle bzw. GFM_Laufnr.',
MF150044 IS 'Sequenz-Nummer zu Valor',
MF150043 IS 'Betrag zu Bestandeswährung',
MF150042 IS 'Bestandeswährung',
MF150041 IS 'Untergruppe Gegenpartei',
MF150040 IS 'Hauptgruppe Gegenpartei',
MF150039 IS 'Gegenpartei-Geldmarktzusatz',
MF150038 IS 'Gegenpartei-Beziehungsnummer',
MF150037 IS 'CSID Gegenpartei',
MF150036 IS 'Gegenpartei',
MF150035 IS 'Gegenpartei-Typ',
MF150034 IS 'BUID Gegenpartei',
MF150033 IS 'KundenverantwortlicheR',
MF150032 IS 'Kundenbüro Kunde',
MF150031 IS 'Kunden-Geldmarktzusatz',
MF150030 IS 'Kunden-Beziehungsnummer',
MF150029 IS 'Depotartenschlüssel',
MF150028 IS 'Kontoartenschlüssel',
MF150027 IS 'Kundendomizil',
MF150026 IS 'Kundensegment',
MF150025 IS 'Kundenart',
MF150024 IS 'PVV-Kunde',
MF150023 IS 'Cifnummer Kunde',
MF150022 IS 'BUID Kunde',
MF150021 IS 'Mutationsdatum',
MF150020 IS 'MutationsPID',
MF150019 IS 'Betrag in Buchführungswährung',
MF150018 IS 'Betrag in FWhrg',
MF150017 IS 'Valor',
MF150016 IS 'Währung',
MF150015 IS 'Urheber',
MF150014 IS 'Grund',
MF150013 IS 'Buchungsdatum',
MF150012 IS 'Bewegungsverdichtung',
MF150011 IS 'Produkteklassifizierung',
MF150010 IS 'MF-Produkt',
MF150009 IS 'Produktenr.Prok/Textcode Hypo',
MF150008 IS 'Bewegungsart Depot/Edelmetall',
MF150007 IS 'zu bewerten',
MF150006 IS 'nachzuerfassen',
MF150005 IS 'Storno',
MF150004 IS 'Auftragsnummer',
MF150003 IS 'Lieferantentyp',
MF150002 IS 'RZ-Herkunft',
MF150001 IS 'ID Mittelfluss (UUID)');
commit;
------------------------------------------------------------------------
-- Database=$db
-- Index=$creator.$'$IMF' $creator.$'$TMF'150A1
------------------------------------------------------------------------
--
CREATE UNIQUE INDEX $creator.$'$IMF'150A0
ON $creator.$'$TMF'150A1
(MF150001 ASC)
USING STOGROUP GSMS
BUFFERPOOL BP1
CLOSE NO
PIECESIZE 8 G;
--
------------------------------------------------------------------------
-- Database=$db
-- Index=$creator.$'$IMF'150A1 On $creator.$'$TMF'150A1
------------------------------------------------------------------------
--
CREATE INDEX $creator.$'$IMF'150A1
ON $creator.$'$TMF'150A1
(MF150013 ASC,
MF150067 ASC)
USING STOGROUP GSMS
BUFFERPOOL BP1
CLOSE NO
PIECESIZE 8 G;
--
------------------------------------------------------------------------
-- Database=$db
-- Index=$creator.$'$IMF'150A2 On $creator.$'$TMF'150A1
------------------------------------------------------------------------
--
CREATE INDEX $creator.$'$IMF'150A2
ON $creator.$'$TMF'150A1
(MF150023 ASC,
MF150013 DESC)
USING STOGROUP GSMS
CLUSTER
BUFFERPOOL BP1
CLOSE NO
;
--
------------------------------------------------------------------------
-- Database=$db
-- Index=$creator.$'$IMF'150A3 On $creator.$'$TMF'150A1
------------------------------------------------------------------------
--
CREATE INDEX $creator.$'$IMF'150A3
ON $creator.$'$TMF'150A1
(MF150066 ASC,
MF150013 DESC)
USING STOGROUP GSMS
BUFFERPOOL BP1
CLOSE NO
PIECESIZE 8 G;
--
------------------------------------------------------------------------
-- Database=$db
-- Index=$creator.$'$IMF'150A4 On $creator.$'$TMF'150A1
------------------------------------------------------------------------
--
CREATE INDEX $creator.$'$IMF'150A4
ON $creator.$'$TMF'150A1
(MF150067 ASC,
MF150013 DESC,
MF150019 DESC,
MF150066 DESC)
USING STOGROUP GSMS
BUFFERPOOL BP1
CLOSE NO
PIECESIZE 4 G;
--
------------------------------------------------------------------------
-- Database=$db
-- Index=$creator.$'$IMF'150A5 On $creator.$'$TMF'150A1
------------------------------------------------------------------------
--
CREATE UNIQUE INDEX $creator.$'$IMF'150A5
ON $creator.$'$TMF'150A1
(MF150004 ASC,
MF150059 ASC,
MF150005 ASC,
MF150013 ASC,
MF150023 ASC)
USING STOGROUP GSMS
BUFFERPOOL BP1
CLOSE NO
PIECESIZE 8 G;
--
------------------------------------------------------------------------
-- Database=$db
-- Index=$creator.$'$IMF'150A6 On $creator.$'$TMF'150A1
------------------------------------------------------------------------
--
CREATE INDEX $creator.$'$IMF'150A6
ON $creator.$'$TMF'150A1
(MF150085 ASC,
MF150084 ASC,
MF150060 ASC)
USING STOGROUP GSMS
BUFFERPOOL BP1
CLOSE NO
PIECESIZE 8 G;
--
------------------------------------------------------------------------
-- Database=$db
-- Index=$creator.$'$IMF'150A7 On $creator.$'$TMF'150A1
------------------------------------------------------------------------
--
CREATE INDEX $creator.$'$IMF'150A7
ON $creator.$'$TMF'150A1
(MF150084 ASC,
MF150085 ASC,
MF150022 ASC,
MF150023 ASC)
USING STOGROUP GSMS
BUFFERPOOL BP1
CLOSE NO
PIECESIZE 8 G;
--
------------------------------------------------------------------------
-- Database=$db
-- Index=$creator.$'$IMF'150A8 On $creator.$'$TMF'150A1
------------------------------------------------------------------------
--
CREATE UNIQUE INDEX $creator.$'$IMF'150A8
ON $creator.$'$TMF'150A1
(MF150023 ASC,
MF150013 ASC,
MF150001 ASC,
MF150019 ASC,
MF150083 ASC)
USING STOGROUP GSMS
BUFFERPOOL BP1
CLOSE NO
PIECESIZE 8 G;
--
COMMIT;
--
--
--drop trigger $creator.$'$TMF'150A1Ins ;
--drop trigger $creator.$'$TMF'150A1Upd ;
create trigger $creator.$'$TMF'150A1Ins no cascade before insert
on $creator.$'$TMF'150A1 referencing New as n
for each row mode db2sql
set MF150HASH =
smallint(mod(
month(n.MF150013) * 100000
+ int(translate(
translate('08642', hex(substr(n.MF150023, 8,5)), '1234567890')
, '024579', 'ABCDEF'))
, 11047))
;
create trigger $creator.$'$TMF'150A1Upd no cascade before
update of MF150013, MF150023, MF150HASH
on $creator.$'$TMF'150A1 referencing New as N
for each row mode db2sql
set MF150HASH =
smallint(mod(
month(n.MF150013) * 100000
+ int(translate(
translate('08642', hex(substr(n.MF150023, 8,5)), '1234567890')
, '024579', 'ABCDEF'))
, 11047))
;
COMMIT;