Catalog information and ts in AREO* State

alain pary

Catalog information and ts in AREO* State

Hello , 

We try to identify all the objects who need a reorg to adjust the internal row format of the data with the catalog definition. 

The easy way : display   ...... ADVISORY(AREO*). 

I write a query to find the same information in the catalog 

SELECT TRIM(TS.DBNAME) AS DBNAME,
TRIM(TS.NAME) AS NAME,
TB.CREATOR AS TBCREATOR,
TB.NAME AS TBNAME,
TB.VERSION AS TB_VERSION,
TS.OLDEST_VERSION,
TS.CURRENT_VERSION
FROM SYSIBM.SYSTABLESPACE TS
INNER JOIN SYSIBM.SYSTABLES TB ON TB.DBNAME= TS.DBNAME
AND TB.TSNAME =TS.NAME
WHERE NOT EXISTS
(SELECT 1
FROM SYSIBM.SYSCOPY C
WHERE C.DBNAME = TS.DBNAME
AND C.TSNAME = TS.NAME
AND ICTYPE = 'F'
AND TS.CURRENT_VERSION = C.OLDEST_VERSION)
AND TS.OLDEST_VERSION <> CURRENT_VERSION
AND TB.TYPE = 'T'
AND TS.CURRENT_VERSION =
(SELECT MAX(TB.VERSION)
FROM SYSIBM.SYSTABLES TB
WHERE TB.DBNAME = TS.DBNAME
AND TB.TSNAME = TS.NAME
AND TYPE = 'T')

Hopefully , all the TS in the display  are in my query , but I find more information in my query. 

Object where oldest_version < current_version in the last backup . 

Can you check , if you have also this kind of behaviour. 

We don't make repair to scrtch th status AREO* 

all ideas are welcome , to find how it happens ?

regards 

Alain

Roy Boxwell

Catalog information and ts in AREO* State
(in response to alain pary)
I just pull the DBET and look...

Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich

On 8 Jun 2018, at 11:49, alain pary <[login to unmask email]<mailto:[login to unmask email]>> wrote:


Hello ,

We try to identify all the objects who need a reorg to adjust the internal row format of the data with the catalog definition.

The easy way : display ...... ADVISORY(AREO*).

I write a query to find the same information in the catalog

SELECT TRIM(TS.DBNAME) AS DBNAME,
TRIM(TS.NAME) AS NAME,
TB.CREATOR AS TBCREATOR,
TB.NAME AS TBNAME,
TB.VERSION AS TB_VERSION,
TS.OLDEST_VERSION,
TS.CURRENT_VERSION
FROM SYSIBM.SYSTABLESPACE TS
INNER JOIN SYSIBM.SYSTABLES TB ON TB.DBNAME= TS.DBNAME
AND TB.TSNAME =TS.NAME
WHERE NOT EXISTS
(SELECT 1
FROM SYSIBM.SYSCOPY C
WHERE C.DBNAME = TS.DBNAME
AND C.TSNAME = TS.NAME
AND ICTYPE = 'F'
AND TS.CURRENT_VERSION = C.OLDEST_VERSION)
AND TS.OLDEST_VERSION <> CURRENT_VERSION
AND TB.TYPE = 'T'
AND TS.CURRENT_VERSION =
(SELECT MAX(TB.VERSION)
FROM SYSIBM.SYSTABLES TB
WHERE TB.DBNAME = TS.DBNAME
AND TB.TSNAME = TS.NAME
AND TYPE = 'T')

Hopefully , all the TS in the display are in my query , but I find more information in my query.

Object where oldest_version < current_version in the last backup .

Can you check , if you have also this kind of behaviour.

We don't make repair to scrtch th status AREO*

all ideas are welcome , to find how it happens ?

regards

Alain

-----End Original Message-----