zOs/SQL/WI101
set current sqlid = 'S100447';
drop view oA1P.VWI101A1$PK;
drop view oA1P$.VWI101A1$$PK;
drop view oA1P.VWI101A1$PKGR;
drop view oA1P$.VWI101A1$$PKGR;
--drop TABLESPACE WI02A1P.a101$$gr;
commit;
create view oA1P$.VWI101A1$$PKGR as
with w as
( select WI10101T, WI10101D, WI10101L
, translate(hex(substr(WI10101T, 1, 4))
, 'abcdefghijklmnop', '0123456789ABCDEF') t
, max(WI101PK) WI101PK
from oA1P.twi101a1
group by WI10101T, WI10101D, WI10101L
)
select WI10101T, WI10101D, WI10101L
, smallint(coalesce(case
when WI101PK > 0 then WI101PK
when translate(WI10101L, '0', '123456789', '0')
<> '00000' then WI101PK
when translate(WI10101D, '0', '123456789', '0')
<> '00000000' then WI101PK
else
mod(
int(substr(WI10101L, 5, 1)) -- * 1
+ int(substr(WI10101L, 4, 1)) * 44
+ int(substr(WI10101L, 3, 1)) * 28
+ int(substr(WI10101L, 2, 1)) * 13
+ int(substr(WI10101L, 1, 1)) * 42
+ int(substr(WI10101D, 8, 1)) * 46
+ int(substr(WI10101D, 7, 1)) * 10
+ int(substr(WI10101D, 6, 1)) * 16
+ int(substr(WI10101D, 5, 1)) * 15
+ int(substr(WI10101D, 4, 1)) * 24
+ int(substr(WI10101D, 3, 1)) * 49
+ int(substr(WI10101D, 2, 1)) * 36
+ int(substr(WI10101D, 1, 1)) * 47
+ (ascii(substr(t,7,1)) * 16 + ascii(substr(t,8,1))) -- * 1
+ (ascii(substr(t,5,1)) * 16 + ascii(substr(t,6,1))) * 44
+ (ascii(substr(t,3,1)) * 16 + ascii(substr(t,4,1))) * 28
+ (ascii(substr(t,1,1)) * 16 + ascii(substr(t,2,1))) * 13
+ 14, 53) + 1
end, WI101PK, 0)) WI101PK
from w
;
commit
;
CREATE TABLESPACE a101$$gr
IN WI02A1P
USING STOGROUP GSMS
FREEPAGE 5 PCTFREE 5
segsize 64
BUFFERPOOL BP2
LOCKSIZE ANY
CLOSE NO
COMPRESS YES
;
--
COMMIT;
--
------------------------------------------------------------------------
-- Table=OA1P$.twi101$1 In WI02A1P.a101$
------------------------------------------------------------------------
--
--
CREATE TABLE OA1P$.twi101$$pkgr
(WI10101T CHAR(4) NOT NULL,
WI10101D CHAR(8) NOT NULL,
WI10101L CHAR(5) NOT NULL,
WI101PK SMALLINT NOT NULL
)
IN WI02A1P.a101$$GR
;
------------------------------------------------------------------------
--
CREATE UNIQUE INDEX OA1P$.iwi101$$pkgr1
ON OA1P$.twi101$$pkgr
(WI10101T ASC,
WI10101D ASC,
WI10101L ASC,
WI101PK ASC)
CLUSTER
USING STOGROUP GSMS
FREEPAGE 5 PCTFREE 10
BUFFERPOOL BP1
CLOSE NO;
--
COMMIT;
create view oA1P$.VWI101A1$$PK as
with w as
( select t.*,
translate(hex(substr(t.WI10101T, 1, 4))
, 'abcdefghijklmnop', '0123456789ABCDEF') t,
k.WI101PK k
from oA1P.twi101a1 t
left join oA1P$.twi101$$pkgr k
on k.WI10101T = t.WI10101T
and k.WI10101D = t.WI10101D
and k.WI10101L = t.WI10101L
)
select
WI10101T
, WI10101D
, WI10101L
, WI10101G
, WI10101V
, WI10101B
, WI10101I
, WI10102A
, WI10102S
, WI10103
, WI10104
, WI10105
, WI10106
, WI10107
, WI10108
, WI10109
, WI10110
, WI10112
, WI10113
, WI10114
, smallint(coalesce(case
when WI101PK > 0 then WI101PK
when k is not null then k
when translate(WI10101L, '0', '123456789', '0')
<> '00000' then WI101PK
when translate(WI10101D, '0', '123456789', '0')
<> '00000000' then WI101PK
else
mod(
int(substr(WI10101L, 5, 1)) -- * 1
+ int(substr(WI10101L, 4, 1)) * 44
+ int(substr(WI10101L, 3, 1)) * 28
+ int(substr(WI10101L, 2, 1)) * 13
+ int(substr(WI10101L, 1, 1)) * 42
+ int(substr(WI10101D, 8, 1)) * 46
+ int(substr(WI10101D, 7, 1)) * 10
+ int(substr(WI10101D, 6, 1)) * 16
+ int(substr(WI10101D, 5, 1)) * 15
+ int(substr(WI10101D, 4, 1)) * 24
+ int(substr(WI10101D, 3, 1)) * 49
+ int(substr(WI10101D, 2, 1)) * 36
+ int(substr(WI10101D, 1, 1)) * 47
+ (ascii(substr(t,7,1)) * 16 + ascii(substr(t,8,1))) -- * 1
+ (ascii(substr(t,5,1)) * 16 + ascii(substr(t,6,1))) * 44
+ (ascii(substr(t,3,1)) * 16 + ascii(substr(t,4,1))) * 28
+ (ascii(substr(t,1,1)) * 16 + ascii(substr(t,2,1))) * 13
+ 14, 53) + 1
end, WI101PK)) WI101PK
from w
;
commit
;;;;;;;;
;;;
set current sqlid = 'S100447';
drop view oA1P.VWI101A1$PK;
create view oA1P.VWI101A1$PK as
with w as
( select t.*,
translate(hex(substr(WI10101T, 1, 4))
, 'abcdefghijklmnop', '0123456789ABCDEF') t
from oA1P.twi101a1 t
)
select
WI10101T
, WI10101D
, WI10101L
, WI10101G
, WI10101V
, WI10101B
, WI10101I
, WI10102A
, WI10102S
, WI10103
, WI10104
, WI10105
, WI10106
, WI10107
, WI10108
, WI10109
, WI10110
, WI10112
, WI10113
, WI10114
, smallint(coalesce(case
when WI101PK > 0 then WI101PK
when translate(WI10101L, '0', '123456789', '0')
<> '00000' then WI101PK
when translate(WI10101D, '0', '123456789', '0')
<> '00000000' then WI101PK
else
mod(
int(substr(WI10101L, 5, 1)) -- * 1
+ int(substr(WI10101L, 4, 1)) * 44
+ int(substr(WI10101L, 3, 1)) * 28
+ int(substr(WI10101L, 2, 1)) * 13
+ int(substr(WI10101L, 1, 1)) * 42
+ int(substr(WI10101D, 8, 1)) * 46
+ int(substr(WI10101D, 7, 1)) * 10
+ int(substr(WI10101D, 6, 1)) * 16
+ int(substr(WI10101D, 5, 1)) * 15
+ int(substr(WI10101D, 4, 1)) * 24
+ int(substr(WI10101D, 3, 1)) * 49
+ int(substr(WI10101D, 2, 1)) * 36
+ int(substr(WI10101D, 1, 1)) * 47
+ (ascii(substr(t,7,1)) * 16 + ascii(substr(t,8,1))) -- * 1
+ (ascii(substr(t,5,1)) * 16 + ascii(substr(t,6,1))) * 44
+ (ascii(substr(t,3,1)) * 16 + ascii(substr(t,4,1))) * 28
+ (ascii(substr(t,1,1)) * 16 + ascii(substr(t,2,1))) * 13
+ 14, 53) + 1
end, WI101PK)) WI101PK
from w
;
commit
;;;
with l (l, p) as
(
select 0,1 from sysibm.sysDummy1
union all select l+1, mod(p*256, 53) from l where l < 30
)
select * from l
;
select mod(256, 53), mod(256*256, 53), mod(28*256, 53), mod(28*28, 53)
from sysibm.sysDummy1
;
with v as
( select WI10101T, WI10101D, WI10101L,
translate(hex(substr(WI10101T, 1, 4))
, 'abcdefghijklmnop', '0123456789ABCDEF') t,
WI101PK
from oA1P.twi101a1
where wi101pk <> 0
fetch first 100000 rows only
)
, w as
(
select v.*,
case when translate(WI10101L, '0', '123456789', '0') <> '00000'
then -1
when translate(WI10101D, '0', '123456789', '0') <> '00000000'
then -2
else
mod(
int(substr(WI10101L, 5, 1)) -- * 1
+ int(substr(WI10101L, 4, 1)) * 44
+ int(substr(WI10101L, 3, 1)) * 28
+ int(substr(WI10101L, 2, 1)) * 13
+ int(substr(WI10101L, 1, 1)) * 42
+ int(substr(WI10101D, 8, 1)) * 46
+ int(substr(WI10101D, 7, 1)) * 10
+ int(substr(WI10101D, 6, 1)) * 16
+ int(substr(WI10101D, 5, 1)) * 15
+ int(substr(WI10101D, 4, 1)) * 24
+ int(substr(WI10101D, 3, 1)) * 49
+ int(substr(WI10101D, 2, 1)) * 36
+ int(substr(WI10101D, 1, 1)) * 47
+ (ascii(substr(t,7,1)) * 16 + ascii(substr(t,8,1))) -- * 1
+ (ascii(substr(t,5,1)) * 16 + ascii(substr(t,6,1))) * 44
+ (ascii(substr(t,3,1)) * 16 + ascii(substr(t,4,1))) * 28
+ (ascii(substr(t,1,1)) * 16 + ascii(substr(t,2,1))) * 13
+ 14, 53) + 1 end c
from v
)
select c, WI101PK, count(*),
min(WI10101T || '?' || WI10101D || '?' || WI10101L),
max(WI10101T || '?' || WI10101D || '?' || WI10101L)
from w
group by c, WI101PK
with ur
;;;
with v (e) as
( select ' ' from sysibm.sysDummy1
union all select 'a' from sysibm.sysDummy1
union all select 'b' from sysibm.sysDummy1
union all select '0' from sysibm.sysDummy1
union all select '1' from sysibm.sysDummy1
union all select '9' from sysibm.sysDummy1
)
, w as
( select e, hex(e) h, ascii(e) a,
translate(hex(e), 'abcdefghijklmnop', '0123456789ABCDEF') t
from v
)
select w.*, ascii(substr(t,1,1)) * 16 + ascii(substr(t,2,1))
- 1649
from w
;;;
select mod(0, 53), mod(3,53), mod(52,53), mod(530,53), mod(54, 53)
, mod(-0, 53), mod(-3,53), mod(-52,53), mod(-530,53), mod(-54, 53)
from sysibm.sysDummy1
;;;
( select translate(WI10101L, '0', '123456789', '0') d
from oA1P.twi101a1
-- fetch first 1000000 rows only
)
select d, count(*)
from w
group by d
with ur
;;,
with a (c, t, a, s, r) as
( select count(*) c, WI10101T t, WI10102A a, WI10102S s,
row_number () over(order by WI10101T , WI10102A, WI10102S) r
from oA1P.twi101a1
where wi10101t < 'E'
group by WI10101T , WI10102A, WI10102S
)
, b (su, gR, gC, c, t, a, s, r, l) as
( select c, 1, c, c, t, a, s, r, 1
from a where r = 1
union all
select b.su+a.c,
case when b.gC+a.c > 1000 then 0 else b.gR+1 end,
case when b.gC+a.c > 1000 then a.c else b.gC + a.c end,
a.c, a.t, a.a, a.s, a.r, b.l+1
from b, a
where b.l + 1 = a.r and l < 1000
)
select * FROM b
where b.gR = 0
order by r
with ur
;;;
union all
select sum(c), left(k, length(k) - 1), l+1
from a
where l < 20
)
row_number () over(order by WI10101T , WI10102A, WI10102S)
--
select trunc_timestamp(wi10101i, 'HH'), count(*), max(wi10101T)
from (select * from oA1P.twi101a1
where wi10101i > current timestamp - 10 days
and wi10101T <= 'BK'
-- fetch first 10000 rows only
) x
group by trunc_timestamp(wi10101i, 'HH')
order by 1 desc
with ur
;;;
select trunc_timestamp(wi10101i, 'HH'), count(*), max(wi10101T)
from (select * from oA1P.twi101a1
where wi10101i > current timestamp - 10 days
and wi10101T <= 'BK'
-- fetch first 10000 rows only
) x
group by trunc_timestamp(wi10101i, 'HH')
order by 1 desc
with ur