RSU 1809 and ancient unreorged tablespaces - and reason code 00C900E1.

James Campbell

RSU 1809 and ancient unreorged tablespaces - and reason code 00C900E1.
Last weekend we put Db2 11 RSU1809 into a development subsystem.  Access to some
tablespaces started to fail with reason code 00C900E1.

Eventually the cause was tracked down to the page set page header having a page size
(HPGPGSZ) of zero.  Many other fields contain binary zeros as well.

It appears that these tablespaces have not been reorged since, oh 1993, and the page
header format now contains additional fields.  Something in RSU1809 now checks the page
size.

My advice is to ensure that you reorg any such tablespaces before RSU1809.

James Campbell

Philip Sevetson

RSU 1809 and ancient unreorged tablespaces - and reason code 00C900E1.
(in response to James Campbell)
James,

After you have applied RSU1809 and run into this problem, does REORG run successfully for those tablespaces which exhibit this failure? Or do you have to roll back RSU1809 in order for REORG to work?

I'm concerned, because we have tablespaces with a CREATEDTS of '0001-01-01-00.00.00.000000' and no policy of regular REORGs for test environments. I have no idea whether I have a huge problem coming down, or no problem at all. Is there a straightforward way to query or programmatically analyze tablespaces?

--Phil Sevetson

From: James Campbell [mailto:[login to unmask email]
Sent: Tuesday, November 20, 2018 10:55 PM
To: [login to unmask email]
Subject: [DB2-L] - RSU 1809 and ancient unreorged tablespaces - and reason code 00C900E1.

Last weekend we put Db2 11 RSU1809 into a development subsystem. Access to some tablespaces started to fail with reason code 00C900E1.

Eventually the cause was tracked down to the page set page header having a page size (HPGPGSZ) of zero. Many other fields contain binary zeros as well.

It appears that these tablespaces have not been reorged since, oh 1993, and the page header format now contains additional fields. Something in RSU1809 now checks the page size.

My advice is to ensure that you reorg any such tablespaces before RSU1809.

James Campbell

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Roy Boxwell

RSU 1809 and ancient unreorged tablespaces - and reason code 00C900E1.
(in response to Philip Sevetson)
Join the RTS to select where REORGLASTTIME IS NULL and to TS with a join to DB to not select TYPE = ‚W’ and select CREATEDTS To see when they were created. RELCREATED only helps for before 9, 10 and above.

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]
http://www.seg.de
Link zur Datenschutzerklärung

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

> On 21 Nov 2018, at 13:39, Sevetson, Phil <[login to unmask email]> wrote:
>
> James,
>
> After you have applied RSU1809 and run into this problem, does REORG run successfully for those tablespaces which exhibit this failure? Or do you have to roll back RSU1809 in order for REORG to work?
>
> I’m concerned, because we have tablespaces with a CREATEDTS of '0001-01-01-00.00.00.000000' and no policy of regular REORGs for test environments. I have no idea whether I have a huge problem coming down, or no problem at all. Is there a straightforward way to query or programmatically analyze tablespaces?
>
> --Phil Sevetson
>
> From: James Campbell [mailto:[login to unmask email]
> Sent: Tuesday, November 20, 2018 10:55 PM
> To: [login to unmask email]
> Subject: [DB2-L] - RSU 1809 and ancient unreorged tablespaces - and reason code 00C900E1.
>
> Last weekend we put Db2 11 RSU1809 into a development subsystem. Access to some tablespaces started to fail with reason code 00C900E1.
>
> Eventually the cause was tracked down to the page set page header having a page size (HPGPGSZ) of zero. Many other fields contain binary zeros as well.
>
> It appears that these tablespaces have not been reorged since, oh 1993, and the page header format now contains additional fields. Something in RSU1809 now checks the page size.
>
> My advice is to ensure that you reorg any such tablespaces before RSU1809.
>
> James Campbell
>
> -----End Original Message-----
> **This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
> Site Links: View post online View mailing list online Start new thread via email Unsubscribe from this mailing list Manage your subscription
>
> This email has been sent to: [login to unmask email]
> ESAi has well-regarded tools for Fast Cloning, Buffer Pool Tuning, Log Analysis, TDM & more.
> BCV4, BCV5, BPA4DB2, ULT4DB2... modern power tools to get the job done faster & easier than ever.
> http://www.ESAIGroup.com/idug
>
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>
Attachments

  • smime.p7s (3.9k)

Roy Boxwell

RSU 1809 and ancient unreorged tablespaces - and reason code 00C900E1.
(in response to Roy Boxwell)
Here’s my little SQL that I wrote:



SELECT SUBSTR(RTS.DBNAME , 1 , 8) AS DBNAME

,SUBSTR(RTS.NAME , 1 , 8) AS NAME

,RTS.PARTITION

,RTS.REORGLASTTIME

,CASE TS.RELCREATED

WHEN ' ' THEN '< DB2 9'

WHEN 'M' THEN ' DB2 9'

WHEN 'O' THEN ' DB2 10'

WHEN 'P' THEN ' DB2 11'

WHEN 'Q' THEN ' DB2 12'

ELSE ' UNKNOWN'

END AS REL_CREATED

,TS.CREATEDTS

FROM SYSIBM.SYSTABLESPACESTATS RTS

,SYSIBM.SYSTABLESPACE TS

,SYSIBM.SYSDATABASE DB

WHERE (REORGLASTTIME IS NULL

OR REORGLASTTIME = '0001-01-01-00.00.00.000000')

AND RTS.DBNAME = DB.NAME

AND RTS.DBNAME = TS.DBNAME

AND RTS.NAME = TS.NAME

AND NOT DB.TYPE = 'W'

ORDER BY 1 , 2 , 3

;



Remember it also returns the directory and catalog...interesting data but not of real relevance for the OP!



Roy Boxwell

SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email]> [login to unmask email]
Web http://www.seg.de http://www.seg.de

https://www.seg.de/corporate/rechtliche-hinweise/datenschutz Link zur Datenschutzerklärung


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



From: Boxwell, Roy [mailto:[login to unmask email]
Sent: Wednesday, November 21, 2018 5:07 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: RSU 1809 and ancient unreorged tablespaces - and reason code 00C900E1.



Join the RTS to select where REORGLASTTIME IS NULL and to TS with a join to DB to not select TYPE = ‚W’ and select CREATEDTS To see when they were created. RELCREATED only helps for before 9, 10 and above.

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

Link zur Datenschutzerklärung https://www.seg.de/corporate/rechtliche-hinweise/datenschutz



Software Engineering GmbH

Amtsgericht Düsseldorf, HRB 37894

Geschäftsführung: Gerhard Schubert, Ulf Heinrich


On 21 Nov 2018, at 13:39, Sevetson, Phil <[login to unmask email] <mailto:[login to unmask email]> > wrote:

James,



After you have applied RSU1809 and run into this problem, does REORG run successfully for those tablespaces which exhibit this failure? Or do you have to roll back RSU1809 in order for REORG to work?



I’m concerned, because we have tablespaces with a CREATEDTS of '0001-01-01-00.00.00.000000' and no policy of regular REORGs for test environments. I have no idea whether I have a huge problem coming down, or no problem at all. Is there a straightforward way to query or programmatically analyze tablespaces?



--Phil Sevetson



From: James Campbell [mailto:[login to unmask email]
Sent: Tuesday, November 20, 2018 10:55 PM
To: [login to unmask email] <mailto:[login to unmask email]>
Subject: [DB2-L] - RSU 1809 and ancient unreorged tablespaces - and reason code 00C900E1.



Last weekend we put Db2 11 RSU1809 into a development subsystem. Access to some tablespaces started to fail with reason code 00C900E1.



Eventually the cause was tracked down to the page set page header having a page size (HPGPGSZ) of zero. Many other fields contain binary zeros as well.



It appears that these tablespaces have not been reorged since, oh 1993, and the page header format now contains additional fields. Something in RSU1809 now checks the page size.



My advice is to ensure that you reorg any such tablespaces before RSU1809.



James Campbell



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

**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

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

Attachments

  • smime.p7s (5.1k)

James Campbell

RE: RSU 1809 and ancient unreorged tablespaces - and reason code 00C900E1.
(in response to Roy Boxwell)

More info
- they have been DSN1COPY'ed (PGCOMB - x'01' bit is on)
- page size (HPGPGSZ) is zero.

Apparently the maintenance does extra checking when the DSN1COPY flag is on.

Anyone remember when  HPGPGSZ started to be properly set?  The ISC recommendation is to REPAIR TABLESPACE to set the proper HPGPGSZ.  

James Campbell

Michael Hannan

RE: RSU 1809 and ancient unreorged tablespaces - and reason code 00C900E1.
(in response to Roy Boxwell)



In Reply to Roy Boxwell:

Here’s my little SQL that I wrote:
SELECT SUBSTR(RTS.DBNAME , 1 , 8) AS DBNAME
,SUBSTR(RTS.NAME , 1 , 8) AS NAME
,RTS.PARTITION
,RTS.REORGLASTTIME
,CASE TS.RELCREATED
WHEN ' ' THEN '< DB2 9'
WHEN 'M' THEN ' DB2 9'
WHEN 'O' THEN ' DB2 10'
WHEN 'P' THEN ' DB2 11'
WHEN 'Q' THEN ' DB2 12'
ELSE ' UNKNOWN'
END AS REL_CREATED
,TS.CREATEDTS
FROM SYSIBM.SYSTABLESPACESTATS RTS
,SYSIBM.SYSTABLESPACE TS
,SYSIBM.SYSDATABASE DB
etc.

I have queries that want to know when Tables and Indexes were created, by both date and by release.

A snippet of my code ( I am a bit too lazy to code CASE when I can use the abbreviated DECODE(V10)):

 ,decode(x.relcreated,' ','<8','L','8L','M','9M','O','10O' 
,'P','11P','12+') irelc

Strangely we can determine release created as V8 when value is 'L' for tables and indexes, but for tablespaces value 'L' is not there.

On a tangent:

V8 was a pretty amazing release. Common Table Expressions, Recursive SQL, Table based partitioning (over index based), Cluster not following partitioning index, DPSIs, Non Padded VARCHARs in Indexes. So sometimes useful to know the reason an index with VARCHAR was padded because was before V8. Padded would never be used in V8 forward, right (since cannot be index only if the Varchar column is selected and wastes space for most non trivial VARCHARs)? Naturally this was also the same release we got long object names (so they could be indexed non padded), and SQL limits increased greatly.

This was my favourite release ever, since then SQL could do almost anything, with CTEs, Recursion, higher limit to query blocks, total SQL length, etc. Naturally all my queries got rewritten to use CTEs.

Unfortunately OLAP did not arrive till V9 and V10 enhanced. V6 provided numerous powerful functions.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Philip Sevetson

RSU 1809 and ancient unreorged tablespaces - and reason code 00C900E1.
(in response to Michael Hannan)
Thank you, Michael! This should help with our release of the RSU1809 code (or later).

--Phil Sevetson

From: Michael Hannan [mailto:[login to unmask email]
Sent: Sunday, November 25, 2018 12:55 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: RSU 1809 and ancient unreorged tablespaces - and reason code 00C900E1.



In Reply to Roy Boxwell:
Here’s my little SQL that I wrote:
SELECT SUBSTR(RTS.DBNAME , 1 , 8) AS DBNAME
,SUBSTR(RTS.NAME , 1 , 8) AS NAME
,RTS.PARTITION
,RTS.REORGLASTTIME
,CASE TS.RELCREATED
WHEN ' ' THEN '< DB2 9'
WHEN 'M' THEN ' DB2 9'
WHEN 'O' THEN ' DB2 10'
WHEN 'P' THEN ' DB2 11'
WHEN 'Q' THEN ' DB2 12'
ELSE ' UNKNOWN'
END AS REL_CREATED
,TS.CREATEDTS
FROM SYSIBM.SYSTABLESPACESTATS RTS
,SYSIBM.SYSTABLESPACE TS
,SYSIBM.SYSDATABASE DB
etc.

I have queries that want to know when Tables and Indexes were created, by both date and by release.

A snippet of my code ( I am a bit too lazy to code CASE when I can use the abbreviated DECODE(V10)):

,decode(x.relcreated,' ','<8','L','8L','M','9M','O','10O'
,'P','11P','12+') irelc

Strangely we can determine release created as V8 when value is 'L' for tables and indexes, but for tablespaces value 'L' is not there.

On a tangent:

V8 was a pretty amazing release. Common Table Expressions, Recursive SQL, Table based partitioning (over index based), Cluster not following partitioning index, DPSIs, Non Padded VARCHARs in Indexes. So sometimes useful to know the reason an index with VARCHAR was padded because was before V8. Padded would never be used in V8 forward, right (since cannot be index only if the Varchar column is selected and wastes space for most non trivial VARCHARs)? Naturally this was also the same release we got long object names (so they could be indexed non padded), and SQL limits increased greatly.

This was my favourite release ever, since then SQL could do almost anything, with CTEs, Recursion, higher limit to query blocks, total SQL length, etc. Naturally all my queries got rewritten to use CTEs.

Unfortunately OLAP did not arrive till V9 and V10 enhanced. V6 provided numerous powerful functions.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

John Lynt

RE: RSU 1809 and ancient unreorged tablespaces - and reason code 00C900E1.
(in response to Philip Sevetson)

Modified the SQL slightly to avoid Cartesian product & to sort without nulls in the reolrlasttime column.

SELECT SUBSTR(RTS.DBNAME , 1 , 8) AS DBNAME
,SUBSTR(RTS.NAME , 1 , 8) AS NAME
,RTS.PARTITION
,COALESCE(RTS.REORGLASTTIME,'0001-01-01-00.00.00.000000')
,CASE TS.RELCREATED
WHEN ' ' THEN '< DB2 9'
WHEN 'M' THEN ' DB2 9'
WHEN 'O' THEN ' DB2 10'
WHEN 'P' THEN ' DB2 11'
WHEN 'Q' THEN ' DB2 12'
ELSE ' UNKNOWN'
END AS REL_CREATED
,TS.CREATEDTS
FROM SYSIBM.SYSTABLESPACESTATS RTS
,SYSIBM.SYSTABLESPACE TS
WHERE RTS.DBNAME = TS.DBNAME
AND   RTS.NAME = TS.NAME
ORDER BY 4
WITH UR ;

==>

 

DBNAME    NAME      PARTITION                              REL_CREATED  CREATEDTS
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------

---------+---------+---------+---------+---------+---------+---------+---------+---------+---------
[login to unmask email]  S3TESTAL          0  0001-01-01-00.00.00.000000  < DB2 9      2007-03-29-20.53.17.490046
DSN8D81P  DSN8S81Q          0  0001-01-01-00.00.00.000000  < DB2 9      2008-11-02-02.28.28.818761
DSN8D81P  DSN8S81C          0  0001-01-01-00.00.00.000000  < DB2 9      2008-11-02-02.28.28.526825

..

..

PTDB      PTG500TS          0  2009-10-18-08.15.21.118201  < DB2 9      2007-12-20-07.49.50.652042
PTDB      PTG500T2          1  2009-10-18-08.15.21.119323  < DB2 9      2007-12-20-07.49.51.574913
PTDB      PTG500T2          2  2009-10-18-08.15.21.120544  < DB2 9      2007-12-20-07.49.51.574913
PTDB      PTG500T2          3  2009-10-18-08.15.21.121708  < DB2 9      2007-12-20-07.49.51.574913
PTDB      PTG500T2          4  2009-10-18-08.15.21.123021  < DB2 9      2007-12-20-07.49.51.574913
PTDB      PTG500T2          5  2009-10-18-08.15.21.125525  < DB2 9      2007-12-20-07.49.51.574913
PTDB      PTG500T2          6  2009-10-18-08.15.21.127068  < DB2 9      2007-12-20-07.49.51.574913
PTDB      PTG500T2          7  2009-10-18-08.15.21.128248  < DB2 9      2007-12-20-07.49.51.574913
PTDB      PTG500T2          8  2009-10-18-08.15.21.129828  < DB2 9      2007-12-20-07.49.51.574913
PTDB      PTG500T2          9  2009-10-18-08.15.21.131354  < DB2 9      2007-12-20-07.49.51.574913
PTDB      PTG500T2         10  2009-10-18-08.15.21.132903  < DB2 9      2007-12-20-07.49.51.574913
PTDB      PTG500T2         11  2009-10-18-08.15.21.134086  < DB2 9      2007-12-20-07.49.51.574913
PTDB      PTG500T2         12  2009-10-18-08.15.21.135571  < DB2 9      2007-12-20-07.49.51.574913
PTDB      PTG300UH          0  2009-10-18-08.15.21.137130  < DB2 9      2004-06-04-08.42.58.389938

..

..

 

 

 

Michael Hannan

RE: RSU 1809 and ancient unreorged tablespaces - and reason code 00C900E1.
(in response to John Lynt)



In Reply to John Lynt:

Modified the SQL slightly to avoid Cartesian product & to sort without nulls in the reolrlasttime column.

I see you omitted the join to SYSDATABASE however it did have a join predicate in his query (not Cartesian product):

AND RTS.DBNAME = DB.NAME

His join was in order to use this:

AND NOT DB.TYPE = 'W'

BTW, A possible telltale sign of a cartesian product is a lot of duplicates in the results, or an apparently unnecessary SELECT DISTINCT to get rid of the duplicates (or a GROUP BY).  Multi table joins can sometimes do a cartesian product in an early part of the join process, then have predicates on subsequent tables to remove them.   I recently tuned one such query that came down from 23 hours to several seconds.

Most absolute runaway queries have this type of problem as a typical cause.

Cartesian products or semi-cartesian products, even in intermediate results can be fatal, to any reasonable performance. I am therefore extremely wary of Star-Join unless the Cartesian product size is guaranteed to be smallish.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Nov 27, 2018 - 04:44 AM (Europe/Berlin)