zOs/TX/WK923DDL
------------$-{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
$=cr2=$creator
SET CURRENT SQLID='S100447';
drop tablespace $db.a923a;
commit;
$@[
if $phase >= 1 then $@=[
------------------------------------------------------------------------
CREATE TABLESPACE A923A
IN $db
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 0 PCTFREE 10
GBPCACHE CHANGED
TRACKMOD YES
SEGSIZE 64
BUFFERPOOL BP32K
LOCKSIZE ANY
LOCKMAX SYSTEM
CLOSE YES
COMPRESS YES
CCSID EBCDIC
DEFINE YES
MAXROWS 255
;
commit;
CREATE TABLE $creator.TWK923
(DG127001 CHAR(1) NOT NULL,
DG127002 CHAR(4) NOT NULL,
DG127003 CHAR(20) NOT NULL,
DG127004 DATE NOT NULL WITH DEFAULT,
DG127005 CHAR(3) NOT NULL WITH DEFAULT,
DG127006 SMALLINT NOT NULL,
DG127007 TIMESTAMP NOT NULL WITH DEFAULT,
DG127008 CHAR(4) NOT NULL,
DG127009 CHAR(8) NOT NULL,
DG127010 CHAR(8) NOT NULL,
DG127011 CHAR(20) NOT NULL,
DG127012 CHAR(20) NOT NULL,
DG127013 CHAR(3) NOT NULL WITH DEFAULT,
DG127014 CHAR(4) NOT NULL,
DG127015 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127016 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127017 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127018 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127019 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127020 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127021 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127022 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127023 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127024 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127025 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127026 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127027 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127028 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127029 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127030 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127031 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127032 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127033 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127034 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127035 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127036 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127037 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127038 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127039 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127040 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127041 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127042 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127043 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127044 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127045 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127046 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127047 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127048 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127049 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127050 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127051 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127052 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127053 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127054 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127055 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127056 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127057 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127058 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127059 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127060 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127061 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127062 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127063 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127064 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127065 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127066 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127067 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127068 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127069 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127070 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127071 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127072 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127073 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127074 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127075 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127076 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127077 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127078 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127079 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127080 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127081 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127082 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127083 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127084 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127085 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127086 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127087 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127088 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127089 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127090 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127091 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127092 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127093 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127094 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127095 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127096 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127097 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127098 CHAR(18) NOT NULL WITH DEFAULT,
DG127099 SMALLINT NOT NULL,
DG127100 DATE NOT NULL WITH DEFAULT,
DG127101 CHAR(4) NOT NULL WITH DEFAULT,
DG127102 CHAR(1) NOT NULL WITH DEFAULT,
DG127103 DATE NOT NULL WITH DEFAULT,
DG127104 DATE NOT NULL WITH DEFAULT,
DG127105 CHAR(1) NOT NULL WITH DEFAULT,
DG127106 CHAR(4) NOT NULL WITH DEFAULT,
DG127107 CHAR(3) NOT NULL WITH DEFAULT,
DG127108 CHAR(2) NOT NULL WITH DEFAULT,
DG127109 CHAR(2) NOT NULL WITH DEFAULT,
DG127110 CHAR(3) NOT NULL WITH DEFAULT,
DG127111 CHAR(3) NOT NULL WITH DEFAULT,
DG127112 CHAR(3) NOT NULL WITH DEFAULT,
DG127113 CHAR(3) NOT NULL WITH DEFAULT,
DG127114 CHAR(3) NOT NULL WITH DEFAULT,
DG127115 CHAR(3) NOT NULL WITH DEFAULT,
DG127116 CHAR(8) NOT NULL WITH DEFAULT,
DG127117 CHAR(8) NOT NULL WITH DEFAULT,
DG127118 CHAR(8) NOT NULL WITH DEFAULT,
DG127119 CHAR(8) NOT NULL WITH DEFAULT,
DG127120 CHAR(8) NOT NULL WITH DEFAULT,
DG127121 CHAR(8) NOT NULL WITH DEFAULT,
DG127122 TIMESTAMP NOT NULL WITH DEFAULT,
DG127123 TIMESTAMP NOT NULL WITH DEFAULT,
DG127124 TIMESTAMP NOT NULL WITH DEFAULT,
DG127125 TIMESTAMP NOT NULL WITH DEFAULT,
DG127126 TIMESTAMP NOT NULL WITH DEFAULT,
DG127127 CHAR(30) NOT NULL WITH DEFAULT,
DG127128 CHAR(30) NOT NULL WITH DEFAULT,
DG127129 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127130 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127131 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127132 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127133 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127134 CHAR(1) NOT NULL WITH DEFAULT,
DG127135 DATE NOT NULL WITH DEFAULT,
DG127136 CHAR(3) NOT NULL WITH DEFAULT,
DG127137 DATE NOT NULL WITH DEFAULT,
DG127138 CHAR(3) NOT NULL WITH DEFAULT,
DG127139 CHAR(5) NOT NULL WITH DEFAULT,
DG127140 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127141 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127142 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127143 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127144 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127145 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127146 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127147 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127148 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127149 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127150 CHAR(1) NOT NULL WITH DEFAULT,
DG127151 CHAR(1) NOT NULL WITH DEFAULT,
DG127152 CHAR(1) NOT NULL WITH DEFAULT,
DG127153 CHAR(1) NOT NULL WITH DEFAULT,
DG127154 CHAR(1) NOT NULL WITH DEFAULT,
DG127155 CHAR(3) NOT NULL WITH DEFAULT,
DG127156 CHAR(3) NOT NULL WITH DEFAULT,
DG127157 CHAR(3) NOT NULL WITH DEFAULT,
DG127158 CHAR(3) NOT NULL WITH DEFAULT,
DG127159 CHAR(3) NOT NULL WITH DEFAULT,
DG127160 CHAR(10) NOT NULL WITH DEFAULT,
DG127161 CHAR(10) NOT NULL WITH DEFAULT,
DG127162 CHAR(10) NOT NULL WITH DEFAULT,
DG127163 CHAR(10) NOT NULL WITH DEFAULT,
DG127164 CHAR(10) NOT NULL WITH DEFAULT,
DG127165 CHAR(8) NOT NULL WITH DEFAULT,
DG127166 CHAR(8) NOT NULL WITH DEFAULT,
DG127167 CHAR(8) NOT NULL WITH DEFAULT,
DG127168 CHAR(8) NOT NULL WITH DEFAULT,
DG127169 CHAR(8) NOT NULL WITH DEFAULT,
DG127170 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127171 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127172 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127173 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127174 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127175 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127176 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127177 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127178 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
DG127179 DECIMAL(31, 8) NOT NULL WITH DEFAULT
)
IN $db.A923A;
COMMIT;
$]
if $phase >= 2 then $@=[
SET CURRENT PATH = "SYSIBM","SYSFUN", SYSPROC , $creator ;
CREATE VIEW $creator.VWK923
(DG0301, DG0301Y, DG0301J, DG0301I,
DG0301B, DG0301S, DG0301P, DG0301G, DG0301L, DG0302, DG0303,
DG0304, DG0305, DG0306I, DG0306B, DG0306N, DG0306P, DG0307,
DG0308A, DG0308B, DG0308C, DG0309, DG0310, DG0311, DG0312,
DG0313, DG0314, DG0315A, DG0315B, DG0315C, DG0315D, DG0316,
DG0317, DG0318, DG0319, DG0320, DG0321, DG0322, DG0323A,
DG0323B, DG0324A, DG0324B, DG0325A, DG0325B, DG0326A, DG0326B,
DG0327, DG0328, DG0329, DG0330, DG0331, DG0332, DG0333, DG0334,
DG0335, DG0336, DG0337, DG0338, DG0339, DG0340, DG0341C,
DG0341X, DG0342, DG0343, DG0344, DG0345, DG0346, DG0347, DG0348,
DG0349, DG0350, DG0351, DG0352, DG0353, DG0354, DG0355, DG0356,
DG0357, DG0358, DG0359, DG0360, DG0361, DG0362, DG0363, DG0364,
DG0365, DG0366, DG0367, DG0368, DG0369, DG0370, DG0371, DG0372,
DG0373, DG0374, DG0375, DG0376, DG0377) AS
SELECT $cr2.TWK923.DG127001, SMALLINT($cr2.TWK923.DG127002),
$cr2.TWK923.DG127002,
SUBSTR($cr2.TWK923.DG127003, 1, 1),
SUBSTR($cr2.TWK923.DG127003, 2, 3),
SUBSTR($cr2.TWK923.DG127003, 5, 7),
SUBSTR($cr2.TWK923.DG127003, 12, 1),
SUBSTR($cr2.TWK923.DG127003, 13, 1),
SUBSTR($cr2.TWK923.DG127003, 14, 3),
$cr2.TWK923.DG127139,
SUBSTR(CHAR($cr2.TWK923.DG127007), 1, 4) || SUBSTR(CHAR(
$cr2.TWK923.DG127007), 6, 2) || SUBSTR(CHAR($cr2.
TWK923.DG127007), 9, 2),
SUBSTR(CHAR($cr2.TWK923.DG127004), 7, 4) || SUBSTR(CHAR(
$cr2.TWK923.DG127004), 4, 2) || SUBSTR(CHAR($cr2.
TWK923.DG127004), 1, 2),
SUBSTR($cr2.TWK923.DG127010, 1, 7),
SUBSTR($cr2.TWK923.DG127011, 1, 1),
SUBSTR($cr2.TWK923.DG127011, 2, 3),
SUBSTR($cr2.TWK923.DG127011, 5, 7),
SUBSTR($cr2.TWK923.DG127011, 12, 1),
$cr2.TWK923.DG127114,
DECIMAL($cr2.TWK923.DG127129, 15, 0),
DECIMAL($cr2.TWK923.DG127130, 15, 0),
DECIMAL($cr2.TWK923.DG127131, 15, 0),
DECIMAL($cr2.TWK923.DG127072, 15, 3),
DECIMAL($cr2.TWK923.DG127145, 15, 3),
DECIMAL($cr2.TWK923.DG127146, 15, 3),
DECIMAL($cr2.TWK923.DG127036, 15, 3),
DECIMAL($cr2.TWK923.DG127048, 15, 3),
DECIMAL($cr2.TWK923.DG127076, 15, 3),
DECIMAL($cr2.TWK923.DG127080, 15, 3),
DECIMAL($cr2.TWK923.DG127056, 15, 3),
DECIMAL($cr2.TWK923.DG127147, 15, 3),
DECIMAL($cr2.TWK923.DG127132, 15, 3),
DECIMAL($cr2.TWK923.DG127060, 15, 3),
DECIMAL($cr2.TWK923.DG127133, 15, 3),
DECIMAL($cr2.TWK923.DG127064, 15, 3),
DECIMAL($cr2.TWK923.DG127068, 15, 3),
$cr2.TWK923.DG127134, $cr2.TWK923.DG127102,
SUBSTR(CHAR($cr2.TWK923.DG127123), 7, 4) || SUBSTR(CHAR(
$cr2.TWK923.DG127123), 4, 2) || SUBSTR(CHAR($cr2.
TWK923.DG127123), 1, 2),
SUBSTR($cr2.TWK923.DG127117, 1, 7),
SUBSTR(CHAR($cr2.TWK923.DG127135), 7, 4) || SUBSTR(CHAR(
$cr2.TWK923.DG127135), 4, 2) || SUBSTR(CHAR($cr2.
TWK923.DG127135), 1, 2),
SUBSTR($cr2.TWK923.DG127165, 1, 7),
SUBSTR(CHAR($cr2.TWK923.DG127103), 7, 4) || SUBSTR(CHAR(
$cr2.TWK923.DG127103), 4, 2) || SUBSTR(CHAR($cr2.
TWK923.DG127103), 1, 2),
SUBSTR($cr2.TWK923.DG127166, 1, 7),
SUBSTR(CHAR($cr2.TWK923.DG127104), 7, 4) || SUBSTR(CHAR(
$cr2.TWK923.DG127104), 4, 2) || SUBSTR(CHAR($cr2.
TWK923.DG127104), 1, 2),
CHAR($cr2.TWK923.DG127106 || ' '),
CHAR($cr2.TWK923.DG127008 || ' '),
CASE($cr2.TWK923.DG127100) WHEN '01.01.0001' THEN
'00000000' ELSE SUBSTR(CHAR($cr2.TWK923.DG127100), 7, 4)
|| SUBSTR(CHAR($cr2.TWK923.DG127100), 4, 2) || SUBSTR(
CHAR($cr2.TWK923.DG127100), 1, 2) END,
DECIMAL($cr2.TWK923.DG127140, 15, 3),
$cr2.TWK923.DG127115,
DECIMAL($cr2.TWK923.DG127173, 15, 3),
DECIMAL($cr2.TWK923.DG127174, 15, 3),
DECIMAL($cr2.TWK923.DG127175, 15, 3),
DECIMAL($cr2.TWK923.DG127176, 15, 3),
DECIMAL($cr2.TWK923.DG127177, 15, 3),
DECIMAL($cr2.TWK923.DG127178, 15, 3),
DECIMAL($cr2.TWK923.DG127179, 15, 3),
DECIMAL($cr2.TWK923.DG127018, 5, 3),
CHAR($cr2.TWK923.DG127150 || ' '),
DECIMAL($cr2.TWK923.DG127097, 15, 3),
SUBSTR($cr2.TWK923.DG127012, 1, 16),
$cr2.TWK923.DG127136, $cr2.TWK923.DG127013,
$cr2.TWK923.DG127014, $cr2.TWK923.DG127151,
$cr2.TWK923.DG127127, $cr2.TWK923.DG127128,
$cr2.TWK923.DG127098,
DECIMAL($cr2.TWK923.DG127026, 5, 3),
SUBSTR($cr2.TWK923.DG127009, 1, 7),
$cr2.TWK923.DG127167, $cr2.TWK923.DG127101,
CASE($cr2.TWK923.DG127137) WHEN '01.01.0001' THEN ''
ELSE SUBSTR(CHAR($cr2.TWK923.DG127137), 7, 4) || SUBSTR(
CHAR($cr2.TWK923.DG127137), 4, 2) || SUBSTR(CHAR($cr2.
TWK923.DG127137), 1, 2) END, $cr2.TWK923.DG127105,
SUBSTR($cr2.TWK923.DG127160, 1, 5),
DECIMAL($cr2.TWK923.DG127132, 15, 0),
DECIMAL($cr2.TWK923.DG127133, 15, 0),
$cr2.TWK923.DG127106, $cr2.TWK923.DG127107,
$cr2.TWK923.DG127008,
DECIMAL($cr2.TWK923.DG127018, 15, 7),
DECIMAL($cr2.TWK923.DG127026, 15, 7),
DECIMAL($cr2.TWK923.DG127022, 15, 7),
SUBSTR($cr2.TWK923.DG127009, 1, 4),
$cr2.TWK923.DG127108, $cr2.TWK923.DG127109,
$cr2.TWK923.DG127110, $cr2.TWK923.DG127111,
$cr2.TWK923.DG127112, $cr2.TWK923.DG127113,
$cr2.TWK923.DG127115,
DECIMAL($cr2.TWK923.DG127032, 15, 0),
DECIMAL($cr2.TWK923.DG127027, 15, 3),
$cr2.TWK923.DG127116,
DECIMAL($cr2.TWK923.DG127084, 15, 3),
DECIMAL($cr2.TWK923.DG127088, 15, 3),
DECIMAL($cr2.TWK923.DG127092, 15, 3),
DECIMAL($cr2.TWK923.DG127096, 15, 3),
DECIMAL($cr2.TWK923.DG127028, 15, 3)
FROM $cr2.TWK923 ;
commit;
$]
$]
---||| end ddl testcase $mbr env $env phase $phase