Strange values in RTS table

[Former Member]

Hello

has anyone seen a similar situation : both in V8 and V9 the columns REORGPSEUDODELETES and/or REORGLEAFNEAR in certain cases have values like 2,147,483,452 (more than 2 billion anyway). An example is in V8 table BSAD and its four indexes 0,1,4,5 . Every index had been reorganized yesterday (Aug 31) and three of them show those big figures in pseudodeletes , two of them even in reorgleafnear. One index seems to be reasonable with 19 in pseudodeletes and 0 in leafnear. For every index the column REORGDELETES shows 56 . The tablespace itself had not been reorganized , the table contains about 12 million rows.

Any idea or solution is welcome , I've been searching around with no result up to now.

Best regards

Martin Ketterer

[Former Member]

Thomas and Brian

thank you for your postings. Since we are in the process of migrating our systems to V9 I am going to circumvent SQLs on these columns until we will have finished with our migration. I hope that we will have the fixes with V9 or at least get them afterwards and can then start to correct the false values. Perhaps reorganization will do it as well.

Best regards

Martin

[brian_walker]

Hi Martin!

We run SAP on DB2 for z/OS v9. We saw similar problems with some very strange values in the RTS tables. Keep in mind that even after applying whatever PTF SAP/IBM suggests for the problem, there is usually some manual cleanup work as well. Generally speaking the PTF will stop the problem from occurring again but it will not clean up damage already done.

An example for us:

PK54691: FOR A TABLESPACE WITH DDL OPTION "DEFINE

NO" AN ENTRY IN SYSIBM.TABLESPACESTATS IS CREATED.

PTF UK31675

This was causing runstats recommendations for empty items. We ended up having to run these 2 delete SQLs to correct RTS after the PTF was applied:

Tablespaces:

-

---

delete from sysibm.systablespacestats a

where exists

(select * from sysibm.systablepart b

where b.space = -1

and b.dbname = a.dbname

and b.tsname = a.name);

Indexspaces:

-

---

delete from sysibm.sysindexspacestats C where exists

(select * from sysibm.sysindexes A, sysibm.sysindexpart b

where a.name = b.ixname and a.creator =

b.ixcreator and b.space = -1

and c.indexspace = a.indexspace

and c.dbname = a.dbname

and a.creator = 'SAPR3');

I would suspect that a similar SQL delete/update will be required in your case as well after identifying and applying the PTF that corrects the problem.

Brian

[thomas_vogt]

Martin,

There are some PTFs in this area:

PK60481: INCORRECT VALUES FOR TOTALROWS IN A DATA SHARING ENVIRONMENT.

PK49539: REAL TIME STATISTICS VALUE REORGPSEUDODELETES CAN BE INCORRECT.

PK49433: COLUMN VALUE REORGPSEUDODELETES IN REAL-TIME STATISTICS TABLE ISSET INCORRECTLY DURING INDEX PAGE SPLIT AND GARBAGE COLLECTION.

You should check if the corresponding PTFs are already applied.

Regards,

Thomas