Beispiel zum Komprimieren
mehrerer Tabellen und Indexes:
select 'Alter Table '
concat RTrim(TB.Creator)
concat '.'
concat TB.Name
concat ' COMPRESS YES ;'
from Sysibm.Systables TB
Where TB.CREATOR LIKE '<YOUR
CREATOR>'
For Fetch Only
With Ur
;
select 'Alter Index '
concat RTrim(IX.Creator)
concat '.'
concat IX.Name
concat ' COMPRESS YES ;'
from Sysibm.Systables TB
Inner Join Sysibm.Sysindexes IX
on IX.TBCreator = TB.Creator
And IX.TBName = TB.Name
Where TB.CREATOR LIKE '<YOUR
CREATOR>'
For fetch only
with ur
;
Abfragen welche Tabellen
komprimiert sind :
Select creator, name, compression
from
sysibm.systables
where
compression <> 'N'
ODER SO
select "TABSCHEMA", "TABNAME", "DBPARTITIONNUM",
"DATA_PARTITION_ID",
"COMPRESS_ATTR", "DICT_BUILDER",
"DICT_BUILD_TIMESTAMP",
"COMPRESS_DICT_SIZE", "EXPAND_DICT_SIZE",
"ROWS_SAMPLED",
"PAGES_SAVED_PERCENT", "BYTES_SAVED_PERCENT",
"AVG_COMPRESS_REC_LENGTH",
"OBJECT_TYPE"
from "SYSIBMADM"."ADMINTABCOMPRESSINFO"
Where COMPRESS_ATTR = 'Y'
and OBJECT_TYPE = 'DATA'
and DICT_BUILD_TIMESTAMP is not null
Abfragen welche Indexes
komprimiert sind :
select creator, name, COMPRESSION
from sysibm.sysindexes
where COMPRESSION = 'Y'
;
Informationen über Index compressen :
Select INDSCHEMA
, INDNAME
, TABSCHEMA
, TABNAME
, DBPARTITIONNUM
, IID
, DATAPARTITIONID
, COMPRESS_ATTR
, INDEX_COMPRESSED
, PCT_PAGES_SAVED
FROM TABLE(sysproc.admin_get_index_compress_info('', '', '', -2, -2))
AS t
where COMPRESS_ATTR = 'Y'
;
SELECT STATEMENT FÜR REORG TABLE
(Creator = SYSIBM)
SELECT DISTINCT 'REORG TABLE ' CONCAT RTRIM (CREATOR) CONCAT '.' CONCAT
RTRIM (NAME)
CONCAT CASE
WHEN IXNAME IS NOT NULL THEN ' INDEX ' CONCAT IXNAME
ELSE ''
END
CONCAT ' ALLOW READ ACCESS '
CONCAT CASE
WHEN TEMPSPACE IS NOT NULL THEN 'USE ' CONCAT RTRIM (TEMPSPACE)
ELSE ''
END
CONCAT ' ;'
FROM (SELECT tb.CREATOR,
tb.NAME,
tb.CARD,
ts.pagesize,
TS.TBSPACE,
(Select TMP.TBSPACE
From SYSIBM.SYSTABLESPACES TMP
Where TMP.pagesize = TS.pagesize
AND TMP.Datatype = 'T'
FETCH FIRST 1 ROWS ONLY
)
AS TEMPSPACE,
(SELECT RTRIM (IX.CREATOR) CONCAT '.' CONCAT RTRIM (IX.NAME)
FROM SYSIBM.SYSINDEXES IX
WHERE UNIQUERULE IN ('P', 'U', 'D')
AND IX.TBCREATOR = TB.CREATOR
AND IX.TBNAME = TB.NAME
ORDER BY CASE
WHEN INDEXTYPE = 'CLUS' THEN 1
WHEN UNIQUERULE = 'P' THEN 2
WHEN UNIQUERULE = 'U' THEN 3
WHEN UNIQUERULE = 'D' THEN 4
ELSE 4
END
FETCH FIRST 1 ROWS ONLY
) AS IXNAME
FROM
SYSIBM.SYSTABLES tb
INNER JOIN SYSIBM.SYSTABLESPACES TS
ON tb.TBSPACE = TS.TBSPACE
WHERE TB.TYPE IN ('S',
'T') AND TB.CREATOR = 'SYSIBM'
ORDER BY tb.CARD DESC,
tb.CREATOR, tb.NAME) REORGTB
;
Die Query hatte ich mal
für DB2 LUW
geschrieben um zu prüfen welche Tabellen Reorganissiert werden sollte
(wie REORGCHK) F4 (Clusterratio) wird
dabei
vorgezogen und bei der Tabelle angezeigt
SELECT CHAR(T. TABSCHEMA,12) AS TABSCHEMA , CHAR(T.TABNAME,40) AS
TABNAME ,
T.CARD , T.NPAGES , T.FPAGES , I.NLEAF ,
CAST(T.CARD
* (C.AVGCOLLEN + 10)
AS BIGINT) AS TSIZE ,
CASE
WHEN T.CARD = 0
THEN NULL
ELSE
CAST(100*T.OVERFLOW/T.CARD AS
DEC(13,2))
END AS F1 ,
CASE
WHEN
BIGINT(T.FPAGES-1) *
BIGINT(S.PAGESIZE-76) = 0
THEN NULL
ELSE
CAST(100*BIGINT(T.CARD) *
(C.AVGCOLLEN + 10) / (BIGINT(T.FPAGES-1)
* BIGINT(S.PAGESIZE-76)) AS
DEC(17,2))
END AS F2 ,
CASE
WHEN T.FPAGES = 0
THEN NULL
ELSE
CAST(100*T.NPAGES/T.FPAGES AS
DEC(17,2))
END AS F3 , F4 ,
CASE
WHEN T.CARD =0
THEN '-'
ELSE
CASE
WHEN
CAST(100*T.OVERFLOW/T.CARD AS DEC(13,2)) >= 5
THEN
'*'
ELSE '-'
END
END ||
CASE
WHEN
BIGINT(T.FPAGES-1) *
BIGINT(S.PAGESIZE-76) = 0
THEN '-'
ELSE
CASE
WHEN
CAST(100*BIGINT(T.CARD) * (C.AVGCOLLEN + 10) / (BIGINT(T.FPAGES
-1) * BIGINT(S.PAGESIZE-76)) AS DEC(17,2)) <= 70
THEN
'*'
ELSE '-'
END
END ||
CASE
WHEN T.CARD =0
THEN '-'
ELSE
CASE
WHEN
CAST(100*T.NPAGES/T.FPAGES AS DEC(17,2)) <= 80
THEN
'*'
ELSE '-'
END ||
CASE
WHEN F4 IS NOT
NULL
AND
F4 < 90
THEN
'*'
ELSE '-'
END
END AS REORG
FROM SYSCAT.TABLES T
INNER JOIN SYSCAT.TABLESPACES S
ON T.TBSPACEID = S.TBSPACEID
INNER JOIN (
SELECT TABSCHEMA,
TABNAME, SUM(
CASE
WHEN
LOGGED = ''
THEN AVGCOLLEN
WHEN
LENGTH <= 1024
THEN 72
WHEN
length <= 8192
THEN 96
WHEN
length <= 65536
THEN 120
WHEN
length <= 524000
THEN 144
WHEN
length <= 4190000
THEN 168
WHEN
length <= 134000000
THEN 200
WHEN
length <= 536000000
THEN 224
WHEN
length <= 1070000000
THEN 256
WHEN
length <= 1470000000
THEN 280
WHEN
length <= 2147483647
THEN 316
END +
CASE
WHEN
LOGGED <> ''
AND NULLS = 'Y'
THEN 1
ELSE
0
END ) AS
AVGCOLLEN
FROM SYSCAT.COLUMNS
GROUP BY TABSCHEMA, TABNAME
) AS C
ON T.TABSCHEMA = C.TABSCHEMA
AND T.TABNAME = C.TABNAME
INNER JOIN (
SELECT TABSCHEMA,
TABNAME, SUM(NLEAF) AS
NLEAF, SUM(NLEVELS) AS NLEVELS
FROM SYSCAT.INDEXES
GROUP BY TABSCHEMA, TABNAME)
AS I
on T.TABSCHEMA = I.TABSCHEMA
AND T.TABNAME = I.TABNAME
LEFT OUTER JOIN (
SELECT TABSCHEMA,
TABNAME, Indextype,
CASE
When
CLUSTERRATIO < 0
Then Integer(CLUSTERFACTOR*100)
ELSE
CLUSTERRATIO
END AS F4
FROM SYSCAT.INDEXES ) AS CLU
on T.TABSCHEMA = CLU.TABSCHEMA
AND T.TABNAME = CLU.TABNAME
AND CLU.Indextype = 'CLUS'
WHERE T.TYPE IN ( 'T','S')
;
Restore Database
SELECT DISTINCT 'restore database ' concat
RTRIM(CURRENT SERVER)
CONCAT case when UCASE(LOCATION)
LIKE '%DB2TSM%' THEN ' USE TSM '
Else ' FROM ' CONCAT RTrim (LOCATION)
End
CONCAT ' taken at '
CONCAT char (START_TIME)
Concat ' BUFFER 1024 REPLACE
EXISTING WITHOUT ROLLING FORWARD WITHOUT PROMPTING ;'
FROM sysibmadm.DB_history
WHERE operation = 'B'
AND Start_time = (SELECT Max
(Start_time)
FROM sysibmadm.DB_history
WHERE operation = 'B'
AND SQLCODE IS NULL
)
;
select 'db2 CREATE OR REPLACE NICKNAME '
concat RTRIM(TABSCHEMA) concat '.' concat Rtrim(TABNAME)
concat ' FOR '
concat RTRIM(SERVERNAME) concat '.'
concat RTRIM(REMOTE_SCHEMA) concat '.'
concat RTRIM(REMOTE_TABLE)
concat ''
from SYSCAT.NICKNAMES nn
inner join db2pbs.sysibm_systables t1
on t1.creator = nn.REMOTE_SCHEMA
and t1.name = nn.REMOTE_TABLE
where nn.REMOTE_SCHEMA <> 'SYSIBM'
and nn.SERVERNAME = '<Creator>'
;
select '-- DROP NICKNAME "' concat RTRIM(TABSCHEMA) concat '"."'
concat Rtrim(TABNAME) concat '" ;'
from SYSCAT.NICKNAMES nn
left outer join '<Creator>'.sysibm_systables t1
on t1.creator = nn.REMOTE_SCHEMA
and t1.name = nn.REMOTE_TABLE
where nn.REMOTE_SCHEMA <> 'SYSIBM'
and nn.SERVERNAME = '<Creator>'
and t1.creator is null
;
1.) Möglichkeite eins wenn die Nicknames mit einem anderen Owner
angelegt wurden.
select 'TRANSFER OWNERSHIP OF NICKNAME ' concat RTRIM(TABSCHEMA) concat
'.' concat Rtrim(TABNAME)
concat ' TO USER
M00DB2UC PRESERVE PRIVILEGES ; '
from SYSCAT.NICKNAMES nn
where nn.REMOTE_SCHEMA <> 'SYSIBM'
and nn.owner <> '<Creator>'
;
2.) Möglichkeit 2 "SET SESSION AUTHORIZATION", dafür
muss aber vorher der Grand gemacht werden !
db2 CREATE OR REPLACE NICKNAME '<Creator>'.'<Name>'
FOR <Location>.'<Creator>'.'<Name>'
db2 grant SETSESSIONUSER on user <FROMUSERID> to user <to
userid>
db2 SET SESSION AUTHORIZATION '<USERID>'
Siehe IBM :
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp
Delete LOGS von TSM
Logs von TSM löschen, Infos kommen von SYSIBMADM.DB_HISTORY,
also erst mit Prune History
die Informationen bereinigen und dann LOGS aus TSM löschen !
SELECT 'db2adutl delte logs between '
concat FIRST_DEL_LOG
concat ' and '
concat LAST_DEL_LOG
FROM (SELECT FIRSTLOG, LASTLOG, START_TIME
CHAR ('S0000000') AS FIRST_DEL_LOG,
SUBSTR (FIRSTLOG, 1, 1)
CONCAT Substr (
CHAR (DIGITS (INT (SUBSTR (FIRSTLOG, 2, 7) - 1))),
4, 7)
AS LAST_DEL_LOG
FROM
SYSIBMADM.DB_HISTORY
WHERE OPERATION = 'B'
ORDER BY START_TIME
FETCH FIRST 1 ROWS ONLY);
Not LOGGED
ALTER TABLE Creator.Name ACTIVATE NOT LOGGED
INITIALLY;
Rebind Package
select DISTINCT
'DB2 REBIND PACKAGE ' concat RTrim(PKGSCHEMA ) Concat '.' Concat PKGNAME concat Case when pkgversion =
'' Then ''
Else 'VERSION "' CONCAT RTrim(pkgversion) CONCAT '" RESOLVE
ANY REOPT NONE '
End as Version from SYSCAT.PACKAGEDEP Where pkgversion =
'<PGVERSION>' and PKGSCHEMA = '<PGSCHEMA>'
and PKGNAME in
('<PGNAME1>','<PGNAME2>', '<PGNAMEn>')
;
FILE SYSTEM CACHING
select 'alter tablespace '
concat RTrim(TBSP_NAME)
concat ' NO FILE SYSTEM CACHING ;'
from sysibmadm.SNAPTBSP
where FS_CACHING = 0
;
Indexes not USED !
select IX.INDSCHEMA
,
IX.INDNAME
,
IX.TABSCHEMA
,
IX.TABNAME
,
IX.LASTUSED
,
IX.MADE_UNIQUE
FROM SYSCAT.INDEXES IX
Inner join SYSCAT.TABLES TB
ON TB.TABSCHEMA = IX.TABSCHEMA
AND TB.TABNAME = IX.TABNAME
WHERE TB.LASTUSED > '01.01.0001'
AND IX.MADE_UNIQUE = 'N'
AND IX.UNIQUERULE = 'D'
-- AND TB.TABSCHEMA = 'XXXXX'
;
Clustering Indexes
select IX.INDSCHEMA
,
IX.INDNAME
,
IX.TABSCHEMA
,
IX.TABNAME
,
IX.LASTUSED
,
IX.MADE_UNIQUE
FROM SYSCAT.INDEXES IX
WHERE IX.INDEXTYPE = 'CLUS'
;
Doppelte Indexcolumns finden !
--- Herrn Google gefragt "db2 sql figure out duplicate indexes"
SELECT A.creator, A.NAme, A.TBCREATOR, A.TBNAME, A.COLNAMES
, B.creator, B.NAme, B.TBCREATOR,
B.TBNAME, B.COLNAMES
FROM SYSIBM.SYSINDEXES A
INNER JOIN SYSIBM.SYSINDEXES B
ON A.TBNAME
= B.TBNAME
AND A.TBCREATOR = B.TBCREATOR
-- AND A.COLCOUNT = B.COLCOUNT
AND NOT ( A.NAME = B.NAME AND A.CREATOR = B.CREATOR )
WHERE A.COLCOUNT = ( SELECT COUNT(*)
FROM SYSCAT.INDEXCOLUSE C
WHERE C.INDNAME = B.NAME
AND C.INDSCHEMA = B.CREATOR
AND ( COLNAME , COLSEQ, COLORDER ) IN
( SELECT COLNAME,COLSEQ, COLORDER
FROM SYSCAT.INDEXCOLUSE D
WHERE D.INDNAME = A.NAME
AND D.INDSCHEMA = A.CREATOR ))
;
Index Compress Info :
SELECT *
FROM TABLE(sysproc.admin_get_index_compress_info('', '', '', -2, -2))
AS ICI
Index Info :
SELECT *
FROM TABLE(sysproc.admin_get_index_info('','','')) AS t
from (
SELECT TABSCHEMA
,TABNAME
,CONSTNAME
,REFTABSCHEMA
,REFTABNAME
,COLCOUNT
,CASE
WHEN DELETERULE = 'C'
THEN 'ON DELETE CASCADE '
WHEN DELETERULE = 'R'
THEN 'ON DELETE RESTRICT '
WHEN DELETERULE = 'R'
THEN 'ON DELETE SET NULL '
ELSE 'ON DELETE NO ACTION '
END DELETERULE
,CASE
WHEN UPDATERULE = 'R'
THEN 'ON UPDATE RESTRICT '
ELSE 'ON UPDATE NO ACTION '
END UPDATERULE
,FK_COLNAMES
,PK_COLNAMES
FROM SYSCAT.REFERENCES
where TABSCHEMA = '<tabschema>'
ORDER BY TABSCHEMA
,TABNAME
,CONSTNAME
) T1
FOR FETCH ONLY
;
from (
SELECT TABSCHEMA
,TABNAME
,CONSTNAME
,REFTABSCHEMA
,REFTABNAME
,COLCOUNT
,CASE
WHEN DELETERULE = 'C'
THEN 'ON DELETE CASCADE '
WHEN DELETERULE = 'R'
THEN 'ON DELETE RESTRICT '
WHEN DELETERULE = 'R'
THEN 'ON DELETE SET NULL '
ELSE 'ON DELETE NO ACTION '
END DELETERULE
,CASE
WHEN UPDATERULE = 'R'
THEN 'ON UPDATE RESTRICT '
ELSE 'ON UPDATE NO ACTION '
END UPDATERULE
,FK_COLNAMES
,PK_COLNAMES
FROM SYSCAT.REFERENCES
where TABSCHEMA = '<tabschema>'
ORDER BY TABSCHEMA
,TABNAME
,CONSTNAME
) T1
FOR FETCH ONLY
;