zOs/TX/QBDPSDDL
------------$-[right(userid() sysvar(sysnode) date(s) time(), 60, '-')$]
-- testCase $dsn
-- env $env phase $phase
-- subsys $subsys db $db creator $creator
-----------------------------------------------------------------------
---||| begin ddl testcase $mbr env $env phase $phase
SET CURRENT SQLID='S100447';
SET CURRENT SCHEMA = $creator;
drop tablespace $db.ABDPS;
commit;
$@[
if $phase >= 1 then $@=[
CREATE TABLESPACE ABDPS in $db
USING STOGROUP GSMS
BUFFERPOOL BP2
NUMPARTS 128
CLOSE YES
GBPCACHE CHANGED
LOGGED
LOCKSIZE ANY LOCKMAX SYSTEM
CCSID EBCDIC
PCTFREE 18
FREEPAGE 20
COMPRESS YES
TRACKMOD YES
DEFINE NO
;
CREATE TABLE $creator.tQbDPS
(BE010001 INTEGER
NOT NULL
,BE010002 CHARACTER(16) FOR SBCS DATA
NOT NULL
,BE010003 DATE
NOT NULL
,BE010004 DATE
NOT NULL
,BE010005 TIMESTAMP (6) WITHOUT TIME ZONE
NOT NULL
WITH DEFAULT
,BE010006 CHARACTER(1) FOR SBCS DATA
NOT NULL
,BE010007 CHARACTER(1) FOR SBCS DATA
NOT NULL
,BE010009 CHARACTER(1) FOR SBCS DATA
,BE010010 CHARACTER(1) FOR SBCS DATA
,BE010011 CHARACTER(1) FOR SBCS DATA
,BE010012 CHARACTER(1) FOR SBCS DATA
,BE010013 CHARACTER(1) FOR SBCS DATA
,BE010014 CHARACTER(1) FOR SBCS DATA
,BE010015 CHARACTER(20) FOR SBCS DATA
,BE010016 INTEGER
,BE010017 INTEGER
,BE010018 DECIMAL(31, 8)
NOT NULL
,BE010019 DECIMAL(31, 8)
,BE010020 TIMESTAMP (6) WITHOUT TIME ZONE
,BE010021 TIMESTAMP (6) WITHOUT TIME ZONE
WITH DEFAULT
,BE010022 CHARACTER(8) FOR SBCS DATA
,BE010023 CHARACTER(1) FOR SBCS DATA
,BE010024 CHARACTER(1) FOR SBCS DATA
,BE010025 CHARACTER(1) FOR SBCS DATA
,BE010026 CHARACTER(1) FOR SBCS DATA
,BE010027 CHARACTER(36) FOR SBCS DATA
,BE010028 CHARACTER(1) FOR SBCS DATA
,BE010029 CHARACTER(5) FOR SBCS DATA
,BE010030 CHARACTER(5) FOR SBCS DATA
,BE010031 DATE
,BE010032 DATE
,BE010033 CHARACTER(2) FOR SBCS DATA
,BE010034 CHARACTER(20) FOR SBCS DATA
,BE010035 DATE
,BE010036 CHARACTER(35) FOR SBCS DATA
,BE010037 DATE
,BE010038 TIME
,BE010039 DATE
,BE010040 CHARACTER(2) FOR SBCS DATA
,BE010041 CHARACTER(4) FOR SBCS DATA
,BE010042 CHARACTER(20) FOR SBCS DATA
,BE010043 CHARACTER(35) FOR SBCS DATA
,BE010044 CHARACTER(8) FOR SBCS DATA
,BE010045 DATE
,BE010046 TIME
,BE010047 CHARACTER(8) FOR SBCS DATA
,BE010048 DATE
,BE010049 TIME
,BE010050 CHARACTER(8) FOR SBCS DATA
,BE010051 DATE
,BE010052 TIME
,BE010053 CHARACTER(8) FOR SBCS DATA
,BE010054 INTEGER
,BE010055 CHARACTER(2) FOR SBCS DATA
,BE010056 CHARACTER(1) FOR SBCS DATA
,BE010057 CHARACTER(2) FOR SBCS DATA
,BE010058 CHARACTER(1) FOR SBCS DATA
,BE010059 CHARACTER(4) FOR SBCS DATA
,BE010060 DECIMAL(31, 8)
,BE010061 CHARACTER(4) FOR SBCS DATA
,BE010062 DECIMAL(31, 8)
,BE010063 CHARACTER(4) FOR SBCS DATA
,BE010064 DECIMAL(31, 8)
,BE010065 FLOAT(53)
,BE010066 DATE
,BE010067 TIME
,BE010068 TIME
,BE010069 CHARACTER(5) FOR SBCS DATA
,BE010070 CHARACTER(5) FOR SBCS DATA
,BE010071 CHARACTER(4) FOR SBCS DATA
,BE010072 CHARACTER(2) FOR SBCS DATA
,BE010073 CHARACTER(2) FOR SBCS DATA
,BE010074 CHARACTER(20) FOR SBCS DATA
,BE010075 INTEGER
,BE010076 CHARACTER(4) FOR SBCS DATA
,BE010077 CHARACTER(1) FOR SBCS DATA
,BE010078 CHARACTER(35) FOR SBCS DATA
,BE010079 CHARACTER(2) FOR SBCS DATA
,BE010080 CHARACTER(1) FOR SBCS DATA
,BE010081 CHARACTER(1) FOR SBCS DATA
,BE010082 DECIMAL(7, 0)
,BE010083 CHARACTER(1) FOR SBCS DATA
,BE010084 CHARACTER(1) FOR SBCS DATA
,BE010085 CHARACTER(26) FOR SBCS DATA
,BE010086 CHARACTER(4) FOR SBCS DATA
,BE010087 INTEGER
,BE010088 DECIMAL(15, 3)
,BE010089 TIMESTAMP (6) WITHOUT TIME ZONE
,BE010090 CHARACTER(1) FOR SBCS DATA
,BE010091 CHARACTER(1) FOR SBCS DATA
,BE010092 CHARACTER(21) FOR SBCS DATA
,BE010093 DECIMAL(13, 5)
,BE010094 DECIMAL(7, 0)
,BE010095 CHARACTER(2) FOR SBCS DATA
,BE010096 CHARACTER(2) FOR SBCS DATA
,BE010097 CHARACTER(4) FOR SBCS DATA
,BE010098 CHARACTER(35) FOR SBCS DATA
,BE010099 CHARACTER(35) FOR SBCS DATA
,BE010100 CHARACTER(35) FOR SBCS DATA
,BE010101 CHARACTER(35) FOR SBCS DATA
,BE010102 CHARACTER(2) FOR SBCS DATA
,BE010103 VARCHAR(1000) FOR SBCS DATA
)
IN $db.aBDPS
APPEND NO
WITH RESTRICT ON DROP
NOT VOLATILE CARDINALITY
DATA CAPTURE CHANGES
AUDIT NONE
CCSID EBCDIC
PARTITION BY RANGE
(BE010001 NULLS LAST ASC
$@[ if $phase = 1 then $@=[
,BE010002 NULLS LAST ASC
,BE010003 NULLS LAST DESC
,BE010007 NULLS LAST DESC
,BE010018 NULLS LAST ASC
,BE010004 NULLS LAST DESC
,BE010005 NULLS LAST DESC
,BE010006 NULLS LAST DESC
$] $]
)
( PARTITION 1
ENDING ( 200
) INCLUSIVE
, PARTITION 2
ENDING ( 400
) INCLUSIVE
, PARTITION 3
ENDING ( 600
) INCLUSIVE
, PARTITION 4
ENDING ( 800
) INCLUSIVE
, PARTITION 5
ENDING ( 1000
) INCLUSIVE
, PARTITION 6
ENDING ( 1200
) INCLUSIVE
, PARTITION 7
ENDING ( 1400
) INCLUSIVE
, PARTITION 8
ENDING ( 1600
) INCLUSIVE
, PARTITION 9
ENDING ( 1800
) INCLUSIVE
, PARTITION 10
ENDING ( 2000
) INCLUSIVE
, PARTITION 11
ENDING ( 2200
) INCLUSIVE
, PARTITION 12
ENDING ( 2400
) INCLUSIVE
, PARTITION 13
ENDING ( 2600
) INCLUSIVE
, PARTITION 14
ENDING ( 2800
) INCLUSIVE
, PARTITION 15
ENDING ( 3000
) INCLUSIVE
, PARTITION 16
ENDING ( 3200
) INCLUSIVE
, PARTITION 17
ENDING ( 3400
) INCLUSIVE
, PARTITION 18
ENDING ( 3600
) INCLUSIVE
, PARTITION 19
ENDING ( 3800
) INCLUSIVE
, PARTITION 20
ENDING ( 4000
) INCLUSIVE
, PARTITION 21
ENDING ( 4200
) INCLUSIVE
, PARTITION 22
ENDING ( 4400
) INCLUSIVE
, PARTITION 23
ENDING ( 4600
) INCLUSIVE
, PARTITION 24
ENDING ( 4800
) INCLUSIVE
, PARTITION 25
ENDING ( 5000
) INCLUSIVE
, PARTITION 26
ENDING ( 5200
) INCLUSIVE
, PARTITION 27
ENDING ( 5400
) INCLUSIVE
, PARTITION 28
ENDING ( 5600
) INCLUSIVE
, PARTITION 29
ENDING ( 5800
) INCLUSIVE
, PARTITION 30
ENDING ( 6000
) INCLUSIVE
, PARTITION 31
ENDING ( 6200
) INCLUSIVE
, PARTITION 32
ENDING ( 6400
) INCLUSIVE
, PARTITION 33
ENDING ( 6600
) INCLUSIVE
, PARTITION 34
ENDING ( 6800
) INCLUSIVE
, PARTITION 35
ENDING ( 7000
) INCLUSIVE
, PARTITION 36
ENDING ( 7200
) INCLUSIVE
, PARTITION 37
ENDING ( 7400
) INCLUSIVE
, PARTITION 38
ENDING ( 7600
) INCLUSIVE
, PARTITION 39
ENDING ( 7800
) INCLUSIVE
, PARTITION 40
ENDING ( 8000
) INCLUSIVE
, PARTITION 41
ENDING ( 8200
) INCLUSIVE
, PARTITION 42
ENDING ( 8400
) INCLUSIVE
, PARTITION 43
ENDING ( 8600
) INCLUSIVE
, PARTITION 44
ENDING ( 8800
) INCLUSIVE
, PARTITION 45
ENDING ( 9000
) INCLUSIVE
, PARTITION 46
ENDING ( 9200
) INCLUSIVE
, PARTITION 47
ENDING ( 9400
) INCLUSIVE
, PARTITION 48
ENDING ( 9600
) INCLUSIVE
, PARTITION 49
ENDING ( 9800
) INCLUSIVE
, PARTITION 50
ENDING ( 10000
) INCLUSIVE
, PARTITION 51
ENDING ( 10200
) INCLUSIVE
, PARTITION 52
ENDING ( 10400
) INCLUSIVE
, PARTITION 53
ENDING ( 10600
) INCLUSIVE
, PARTITION 54
ENDING ( 10800
) INCLUSIVE
, PARTITION 55
ENDING ( 11000
) INCLUSIVE
, PARTITION 56
ENDING ( 11200
) INCLUSIVE
, PARTITION 57
ENDING ( 11400
) INCLUSIVE
, PARTITION 58
ENDING ( 11600
) INCLUSIVE
, PARTITION 59
ENDING ( 11800
) INCLUSIVE
, PARTITION 60
ENDING ( 12000
) INCLUSIVE
, PARTITION 61
ENDING ( 12200
) INCLUSIVE
, PARTITION 62
ENDING ( 12400
) INCLUSIVE
, PARTITION 63
ENDING ( 12600
) INCLUSIVE
, PARTITION 64
ENDING ( 12800
) INCLUSIVE
, PARTITION 65
ENDING ( 13000
) INCLUSIVE
, PARTITION 66
ENDING ( 13200
) INCLUSIVE
, PARTITION 67
ENDING ( 13400
) INCLUSIVE
, PARTITION 68
ENDING ( 13600
) INCLUSIVE
, PARTITION 69
ENDING ( 13800
) INCLUSIVE
, PARTITION 70
ENDING ( 14000
) INCLUSIVE
, PARTITION 71
ENDING ( 14200
) INCLUSIVE
, PARTITION 72
ENDING ( 14400
) INCLUSIVE
, PARTITION 73
ENDING ( 14600
) INCLUSIVE
, PARTITION 74
ENDING ( 14800
) INCLUSIVE
, PARTITION 75
ENDING ( 15000
) INCLUSIVE
, PARTITION 76
ENDING ( 15200
) INCLUSIVE
, PARTITION 77
ENDING ( 15400
) INCLUSIVE
, PARTITION 78
ENDING ( 15600
) INCLUSIVE
, PARTITION 79
ENDING ( 15800
) INCLUSIVE
, PARTITION 80
ENDING ( 16000
) INCLUSIVE
, PARTITION 81
ENDING ( 16200
) INCLUSIVE
, PARTITION 82
ENDING ( 16400
) INCLUSIVE
, PARTITION 83
ENDING ( 16600
) INCLUSIVE
, PARTITION 84
ENDING ( 16800
) INCLUSIVE
, PARTITION 85
ENDING ( 17000
) INCLUSIVE
, PARTITION 86
ENDING ( 17200
) INCLUSIVE
, PARTITION 87
ENDING ( 17400
) INCLUSIVE
, PARTITION 88
ENDING ( 17600
) INCLUSIVE
, PARTITION 89
ENDING ( 17800
) INCLUSIVE
, PARTITION 90
ENDING ( 18000
) INCLUSIVE
, PARTITION 91
ENDING ( 18200
) INCLUSIVE
, PARTITION 92
ENDING ( 18400
) INCLUSIVE
, PARTITION 93
ENDING ( 18600
) INCLUSIVE
, PARTITION 94
ENDING ( 18800
) INCLUSIVE
, PARTITION 95
ENDING ( 19000
) INCLUSIVE
, PARTITION 96
ENDING ( 19200
) INCLUSIVE
, PARTITION 97
ENDING ( 19400
) INCLUSIVE
, PARTITION 98
ENDING ( 19600
) INCLUSIVE
, PARTITION 99
ENDING ( 19800
) INCLUSIVE
, PARTITION 100
ENDING ( 20000
) INCLUSIVE
, PARTITION 101
ENDING ( 20200
) INCLUSIVE
, PARTITION 102
ENDING ( 20400
) INCLUSIVE
, PARTITION 103
ENDING ( 20600
) INCLUSIVE
, PARTITION 104
ENDING ( 20800
) INCLUSIVE
, PARTITION 105
ENDING ( 21000
) INCLUSIVE
, PARTITION 106
ENDING ( 21200
) INCLUSIVE
, PARTITION 107
ENDING ( 21400
) INCLUSIVE
, PARTITION 108
ENDING ( 21600
) INCLUSIVE
, PARTITION 109
ENDING ( 21800
) INCLUSIVE
, PARTITION 110
ENDING ( 22000
) INCLUSIVE
, PARTITION 111
ENDING ( 22200
) INCLUSIVE
, PARTITION 112
ENDING ( 22400
) INCLUSIVE
, PARTITION 113
ENDING ( 22600
) INCLUSIVE
, PARTITION 114
ENDING ( 22800
) INCLUSIVE
, PARTITION 115
ENDING ( 23000
) INCLUSIVE
, PARTITION 116
ENDING ( 23200
) INCLUSIVE
, PARTITION 117
ENDING ( 23400
) INCLUSIVE
, PARTITION 118
ENDING ( 23600
) INCLUSIVE
, PARTITION 119
ENDING ( 23800
) INCLUSIVE
, PARTITION 120
ENDING ( 24000
) INCLUSIVE
, PARTITION 121
ENDING ( 24200
) INCLUSIVE
, PARTITION 122
ENDING ( 24400
) INCLUSIVE
, PARTITION 123
ENDING ( 24600
) INCLUSIVE
, PARTITION 124
ENDING ( 24800
) INCLUSIVE
, PARTITION 125
ENDING ( 25000
) INCLUSIVE
, PARTITION 126
ENDING ( 25200
) INCLUSIVE
, PARTITION 127
ENDING ( 25400
) INCLUSIVE
, PARTITION 128
ENDING ( 25600
) INCLUSIVE
);
CREATE INDEX $creator.iQbDPS1
ON $creator.tQbDPS
(BE010001 ASC
,BE010002 ASC
,BE010089 ASC
)
NOT CLUSTER
PARTITIONED
COMPRESS NO
BUFFERPOOL BP1
CLOSE YES
DEFER NO
COPY NO
FREEPAGE 0
PCTFREE 15
GBPCACHE CHANGED
define no
;
CREATE INDEX $creator.iQbDPS3
ON $creator.tQbDPS
(BE010001 ASC
,BE010002 ASC
,BE010034 ASC
,BE010054 ASC
)
NOT CLUSTER
PARTITIONED
define no
COMPRESS NO
BUFFERPOOL BP1
CLOSE YES
DEFER NO
COPY NO
USING STOGROUP "GSMS "
PRIQTY -1
SECQTY -1
ERASE NO
FREEPAGE 0
PCTFREE 15
GBPCACHE CHANGED
;
CREATE UNIQUE INDEX $creator.iQbDPS0
ON $creator.tQbDPS
(BE010001 ASC
,BE010002 ASC
,BE010003 DESC
,BE010007 DESC
,BE010018 ASC
,BE010004 DESC
,BE010005 DESC
,BE010006 DESC
)
CLUSTER
PARTITIONED
DEFINE no
COMPRESS NO
BUFFERPOOL BP1
CLOSE NO
DEFER NO
COPY NO
USING STOGROUP "GSMS "
PRIQTY -1
SECQTY -1
ERASE NO
FREEPAGE 20
PCTFREE 18
GBPCACHE CHANGED
;
CREATE VIEW $creator.VqbDPS
( PROCBASE
, TECHBEZ
, DAT_BUCH
, DAT_VAL
, DAT_ERST_GB
, VERDTYP
, BUCHSEITE_KWA_VERD
, PFLEGE
, BUCHSEITE_BWA_VERD
, STORNOFEHLER
, STATUS_STORNO
, STORNOSICHT
, STORNOTEIL
, AUFTRAGS#_STORNO
, GF_LAUF#_STORNO
, ANZ_BEW_VERD
, BETR_KWA_VERD
, BETR_BWA_VERD
, SAMMEL2
, DAT_MUT
, PID_MUT
, PORTO_FREI
, ZAZ_FREI
, ZSK_FREI
, UMSATZKOMM_FREI
, ZUSATZTEXT
, ZUSATZTEXT_VERW
, TAGSCHNITT
, LIEFERANT
, DAT_LIEF
, DAT_TRADE
, AUFTRAGS#_QUAL
, AUFTRAGS#
, DAT_AUFTRAG
, KUNDREF
, DAT_ERFASS
, ZEIT_ERFASS
, DAT_BELEG
, AUFTRAGGEBER_QUAL
, BUID_AUFTRAGGEBER
, AUFTRAGGEBER
, AKTIONS#
, PID_ERFASS
, DAT_KONTROLL1
, ZEIT_KONTROLL1
, PID_KONTROLL1
, DAT_KONTROLL2
, ZEIT_KONTROLL2
, PID_KONTROLL2
, DAT_DISPOFREI
, ZEIT_DISPOFREI
, PID_DISPOFREI
, GF_LAUF#
, MELDETYP
, BUCHSEITE
, BEWEGART
, AUSSERBILANZ
, WHRG_KTO
, BETR_KTOWHRG
, WHRG_BUCH
, BETR_BUCHWHRG
, WHRG_TRANS
, BETR_TRANSWHRG
, KURS
, DAT_KURS
, ZEIT_KURS
, ZEIT_VAL
, PRODUKTE_NR
, PREISKOMP_NR
, AUSPRAEGUNGS_ID
, STORNOART
, STORNOUMFANG
, AUFTRAGS#_ORIG
, GF_LAUF#_ORIG
, REFGRUND
, SAMMELTYP
, SAMMELBEGRIFF
, OP_TYP
, OPOSTEN
, BELEG
, VORMERK#
, KRIT_VAL
, KOK_NVZ
, PROZPFAD
, STRUKTUREN
, SPESENART
, BETR_BUSP
, SAMMEL1
, VERARBEITART
, VM_ABL
, WS_ARTIKEL#
, WS_PREIS
, DE_SOUCHEN#
, DE_ERMITTLUNGART
, DE_MELDUNG
, DE_BUID_KONVERT
, SA_KARTEN#
, SA_TRANSORT
, SA_NAME
, SA_ORT
, ZV_QUAL
, ZV_ANGABEN
) AS
SELECT BE010001 , BE010002 , BE010003 , BE010004 , BE010005 , BE010006 ,
BE010007 , BE010009 , BE010010 , BE010011 , BE010012 , BE010013 ,
BE010014 , BE010015 , BE010016 , BE010017 , BE010018 , BE010019 ,
BE010020 , BE010021 , BE010022 , BE010023 , BE010024 , BE010025 ,
BE010026 , BE010027 , BE010028 , BE010029 , BE010030 , BE010031 ,
BE010032 , BE010033 , BE010034 , BE010035 , BE010036 , BE010037 ,
BE010038 , BE010039 , BE010040 , BE010041 , BE010042 , BE010043 ,
BE010044 , BE010045 , BE010046 , BE010047 , BE010048 , BE010049 ,
BE010050 , BE010051 , BE010052 , BE010053 , BE010054 , BE010055 ,
BE010056 , BE010057 , BE010058 , BE010059 , BE010060 , BE010061 ,
BE010062 , BE010063 , BE010064 , BE010065 , BE010066 , BE010067 ,
BE010068 , BE010069 , BE010070 , BE010071 , BE010072 , BE010073 ,
BE010074 , BE010075 , BE010076 , BE010077 , BE010078 , BE010079 ,
BE010080 , BE010081 , BE010082 , BE010083 , BE010084 , BE010085 ,
BE010086 , BE010087 , BE010088 , BE010089 , BE010090 , BE010091 ,
BE010092 , BE010093 , BE010094 , BE010095 , BE010096 , BE010097 ,
BE010098 , BE010099 , BE010100 , BE010101 , BE010102 , BE010103
from $creator.tQbDPS
;
commit
;
$]
$]