zOs/SQL/PDBDEAD
-- count locks by resource from pdb
-- ==> dp2g.tadm60a1
with n as
(
select case when posStr(substr(name, 10), '.') < 1 then name
else left(name, 9+posStr(substr(name, 10), '.')) end nm
, s.*
from oa1p.tadm60a1 s
)
select count(*), type, nm, min(reason_code), max(reason_code)
from n
where ssid like 'DOF%' -- and timestamp > '2010-04-20-00.00.00'
and name like '%WI02%'
group by nm, type
order by 1 desc
fetch first 100 rows only
with ur
; x;
select *
from oa1a.tadm60a1
where timestamp > current timestamp - 14 day
and name like 'VV21A1T .VDPS026%'
order by timestamp desc
;x;
select *
from oa1p.tadm60a1
where ssid like 'DOF%' -- and timestamp > '2010-04-20-00.00.00'
and name like '%WI02%'
order by 1 desc
fetch first 100 rows only
with ur
; x;
select trunc_timestamp(timestamp, 'DD') , count(*) dbof,
sum(case when name like '%WI02%' then 1 else 0 end) wi02,
sum(case when name like '%WI02%'
and name like '%A112%' then 1 else 0 end) wi02a112
from oa1p.tadm60a1
where ssid like 'DOF%' and timestamp > '2010-01-01-00.00.00'
group by trunc_timestamp(timestamp, 'DD')
order by 1 desc
fetch first 100 rows only
with ur
;
x
select EN1PART, count(*), min(EB1POSTENNUMMER), max(EB1POSTENNUMMER)
from bua.XBDRW001PS001001
group by EN1PART
with ur
;
x
set current sqlid = 'S100447';
alter table bua.txbe011
volatile
;
commit;
x
select jobName, timestamp(max(runtime)) - timestamp(min(runtime))
-- runtime
from oa1p.tadm11a1 s
where runtime >= '2009-11-15-00.00.00'
and runtime < '2009-11-17-00.00.00'
and utility_type = 'W'
and jobName = 'QR38702P'
group by jobName
order by 2 desc
;
x
select jobName, runtime, s.* from oa1p.tadm11a1 s
where jobName like 'QR4980%'
order by runtime desc
;
x
select changeid, name, status from s100447.adbChg
where changeid = 0002342
;
x
select count(*), sum(bigint(length(ea1inhalt)))
from BUA.XBCQ4009IT002001 ;
x
select 'x' || char(bigInt(123)) || 'y'
from sysibm.sysdummy1
;
x
------------------------------------------------------------------------
-- --
-- Database 2 Administration Tool (DB2 Admin) , program 5697-L90 --
-- --
-- ADB2GEN - Extract object definitions from the DB2 Catalog tables --
-- --
-- Input prepared on : DBOF (915) Extract time : 2009-09-11 11:18 --
-- --
-- Catalog values overridden : none
-- --
-- Generate : SG=N DB=Y TS=Y TB=Y VW=Y IX=Y SY=Y AL=Y LB=Y CM=Y FK=Y --
-- TG=Y UT=N UF=N SP=N SQ=N RO=N --
-- Grants : SG=N DB=Y TS=N TB=N VW=N SC=N UT=N UF=N SP=N SQ=N --
-- --
------------------------------------------------------------------------
------------------------------------------------------------------------
-- --
-- ADB2GEN: Generate DDL for Tablespace=A835A In Database KS09A1P --
-- --
------------------------------------------------------------------------
--
------------------------------------------------------------------------
-- Database=KS09A1P Stogroup=GSMS
-- Tablespace=KS09A1P.A835A
------------------------------------------------------------------------
--
SET CURRENT SQLID='S100447 ';
--
CREATE TABLESPACE A835A
IN KS09A1P
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 4 PCTFREE 50
GBPCACHE CHANGED
TRACKMOD YES
LOGGED
SEGSIZE 64
BUFFERPOOL BP2
LOCKSIZE ANY
LOCKMAX SYSTEM
CLOSE YES
COMPRESS YES
CCSID EBCDIC
DEFINE YES
MAXROWS 255;
--
COMMIT;
--
------------------------------------------------------------------------
-- Table=OA1P .TKS835A1 In KS09A1P.A835A
------------------------------------------------------------------------
--
SET CURRENT SQLID='OA1P ';
--
CREATE TABLE OA1P.TKS835A1
(KS835010 CHAR(20) FOR SBCS DATA NOT NULL,
KS835020 CHAR(20) FOR SBCS DATA NOT NULL,
KS835030 CHAR(20) FOR SBCS DATA NOT NULL,
KS835040 INTEGER NOT NULL,
KS835050 CHAR(20) FOR SBCS DATA NOT NULL,
KS835060 CHAR(4) FOR SBCS DATA NOT NULL,
KS835070 CHAR(20) FOR SBCS DATA NOT NULL,
KS835080 CHAR(20) FOR SBCS DATA NOT NULL,
KS835090 CHAR(140) FOR SBCS DATA NOT NULL,
KS835100 CHAR(2) FOR SBCS DATA NOT NULL,
KS835110 DATE NOT NULL,
KS835120 DATE NOT NULL,
KS835130 DATE NOT NULL,
KS835140 DATE NOT NULL,
KS835150 TIMESTAMP NOT NULL,
KS835160 CHAR(10) FOR SBCS DATA NOT NULL,
KS835170 TIMESTAMP NOT NULL,
PRIMARY KEY (KS835010,
KS835030,
KS835040,
KS835020,
KS835050,
KS835060))
IN KS09A1P.A835A
AUDIT NONE
DATA CAPTURE NONE
CCSID EBCDIC
NOT VOLATILE
APPEND NO ;
--
LABEL ON TABLE OA1P.TKS835A1 IS 'SERVICE';
--
LABEL ON OA1P.TKS835A1
(KS835170 IS 'CONTROL_TIME',
KS835160 IS 'LAST_UPDATE_ID',
KS835150 IS 'LAST_UPDATE_TIME',
KS835140 IS 'VALID_TO_DATE',
KS835130 IS 'VALID_FROM_DATE',
KS835120 IS 'LOCK_TO_DATE',
KS835110 IS 'LOCK_FROM_DATE',
KS835100 IS 'STATE',
KS835090 IS 'COMMENT',
KS835080 IS 'PRODUCT_ID',
KS835070 IS 'ARRANG_PARTNER_KEY',
KS835060 IS 'ARRANGEMENT_TYPE',
KS835050 IS 'ARRANGEMENT_ID',
KS835040 IS 'PARTNER_TYPE',
KS835030 IS 'PARTNER_KEY',
KS835020 IS 'SERVICE_KEY',
KS835010 IS 'CONTRACT_KEY');
--
COMMIT;
--
------------------------------------------------------------------------
-- Database=KS09A1P
-- Index=OA1P .IKS835A0 On OA1P .TKS835A1
------------------------------------------------------------------------
--
CREATE UNIQUE INDEX OA1P.IKS835A0
ON OA1P.TKS835A1
(KS835010 ASC,
KS835030 ASC,
KS835040 ASC,
KS835020 ASC,
KS835050 ASC,
KS835060 ASC)
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 8 PCTFREE 50
GBPCACHE CHANGED
NOT CLUSTER
COMPRESS NO
BUFFERPOOL BP1
CLOSE NO
COPY NO
DEFER NO
DEFINE YES;
--
COMMIT;
--
------------------------------------------------------------------------
-- Database=KS09A1P
-- Index=OA1P .IKS835A1 On OA1P .TKS835A1
------------------------------------------------------------------------
--
CREATE INDEX OA1P.IKS835A1
ON OA1P.TKS835A1
(KS835080 ASC)
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 8 PCTFREE 30
GBPCACHE CHANGED
NOT CLUSTER
COMPRESS NO
BUFFERPOOL BP1
CLOSE NO
COPY NO
DEFER NO
DEFINE YES;
--
COMMIT;
--
------------------------------------------------------------------------
-- Database=KS09A1P
-- Index=OA1P .IKS835A10 On OA1P .TKS835A1
------------------------------------------------------------------------
--
CREATE INDEX OA1P.IKS835A10
ON OA1P.TKS835A1
(KS835030 ASC,
KS835040 ASC,
KS835010 ASC,
KS835080 ASC)
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 0 PCTFREE 10
GBPCACHE CHANGED
NOT CLUSTER
COMPRESS NO
BUFFERPOOL BP1
CLOSE NO
COPY NO
DEFER NO
DEFINE YES
PIECESIZE 2 G;
--
COMMIT;
--
------------------------------------------------------------------------
-- Database=KS09A1P
-- Index=OA1P .IKS835A3 On OA1P .TKS835A1
------------------------------------------------------------------------
--
CREATE INDEX OA1P.IKS835A3
ON OA1P.TKS835A1
(KS835050 ASC,
KS835060 ASC)
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 8 PCTFREE 40
GBPCACHE CHANGED
NOT CLUSTER
COMPRESS NO
BUFFERPOOL BP1
CLOSE NO
COPY NO
DEFER NO
DEFINE YES;
--
COMMIT;
--
------------------------------------------------------------------------
-- Database=KS09A1P
-- Index=OA1P .IKS835A4 On OA1P .TKS835A1
------------------------------------------------------------------------
--
CREATE INDEX OA1P.IKS835A4
ON OA1P.TKS835A1
(KS835020 ASC)
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 8 PCTFREE 30
GBPCACHE CHANGED
NOT CLUSTER
COMPRESS NO
BUFFERPOOL BP1
CLOSE NO
COPY NO
DEFER NO
DEFINE YES;
--
COMMIT;
--
------------------------------------------------------------------------
-- Database=KS09A1P
-- Index=OA1P .IKS835A5 On OA1P .TKS835A1
------------------------------------------------------------------------
--
CREATE INDEX OA1P.IKS835A5
ON OA1P.TKS835A1
(KS835030 ASC,
KS835040 ASC,
KS835060 ASC,
KS835050 ASC)
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 8 PCTFREE 40
GBPCACHE CHANGED
CLUSTER
COMPRESS NO
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFER NO
DEFINE YES;
--
COMMIT;
--
------------------------------------------------------------------------
-- Database=KS09A1P
-- Index=OA1P .IKS835A6 On OA1P .TKS835A1
------------------------------------------------------------------------
--
CREATE INDEX OA1P.IKS835A6
ON OA1P.TKS835A1
(KS835050 DESC,
KS835060 DESC)
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 8 PCTFREE 30
GBPCACHE CHANGED
NOT CLUSTER
COMPRESS NO
BUFFERPOOL BP1
CLOSE NO
COPY NO
DEFER NO
DEFINE YES;
--
COMMIT;
--
------------------------------------------------------------------------
-- Database=KS09A1P
-- Index=OA1P .IKS835A7 On OA1P .TKS835A1
------------------------------------------------------------------------
--
CREATE INDEX OA1P.IKS835A7
ON OA1P.TKS835A1
(KS835130 DESC)
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 8 PCTFREE 30
GBPCACHE CHANGED
NOT CLUSTER
COMPRESS NO
BUFFERPOOL BP1
CLOSE NO
COPY NO
DEFER NO
DEFINE YES;
--
COMMIT;
--
------------------------------------------------------------------------
-- Database=KS09A1P
-- Index=OA1P .IKS835A8 On OA1P .TKS835A1
------------------------------------------------------------------------
--
CREATE INDEX OA1P.IKS835A8
ON OA1P.TKS835A1
(KS835010 ASC,
KS835080 ASC)
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 8 PCTFREE 30
GBPCACHE CHANGED
NOT CLUSTER
COMPRESS NO
BUFFERPOOL BP1
CLOSE NO
COPY NO
DEFER NO
DEFINE YES;
--
COMMIT;
--
------------------------------------------------------------------------
-- Database=KS09A1P
-- Index=OA1P .IKS835A9TEMP On OA1P .TKS835A1
------------------------------------------------------------------------
--
CREATE INDEX OA1P.IKS835A9TEMP
ON OA1P.TKS835A1
(KS835070 ASC)
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 8 PCTFREE 30
GBPCACHE CHANGED
NOT CLUSTER
COMPRESS NO
BUFFERPOOL BP1
CLOSE NO
COPY NO
DEFER NO
DEFINE YES
PIECESIZE 2 G;
--
COMMIT;
--
------------------------------------------------------------------------
-- View=OA1P .VKS835A1V
------------------------------------------------------------------------
--
--
CREATE VIEW OA1P.VKS835A1V(CONTRACT_KEY, SERVICE_KEY, PARTNER_KEY,
PARTNER_TYPE, ARRANGEMENT_ID, ARRANGEMENT_TYPE,
ARRANG_PARTNER_KEY, PRODUCT_ID, COMMENT, STATE, LOCK_FROM_DATE,
LOCK_TO_DATE, VALID_FROM_DATE, VALID_TO_DATE, LAST_UPDATE_TIME,
LAST_UPDATE_ID, CONTROL_TIME) AS
SELECT ALL KS835010, KS835020, KS835030, KS835040, KS835050,
KS835060, KS835070, KS835080, KS835090, KS835100, KS835110,
KS835120, KS835130, KS835140, KS835150, KS835160, KS835170
FROM OA1P.TKS835A1 ;
--
COMMIT;
--
------------------------------------------------------------------------
-- ADB2GEN - End of generated DDL --
------------------------------------------------------------------------
--