sysXXXX XX tables
Frank asked how to reorganise system catalog tables (syscolauth, sysprocbody, etc.) that had accumulated 20-70 extents, since dbschema won't work on them. Consensus: you can't rebuild catalog tables in place; the only supported way is an unload/export and reload of the database (a feature request was said to exist), or to resize extents right after database creation. Art Kagel argued it's largely harmless since catalog data is cached and 72 extents is far from the ~200 limit. Others suggested ALTER TABLE sysXXX NEXT SIZE nnnn (needs an exclusive lock, works from around 9.21), though one poster reported some catalog tables ignored the resize in older versions.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Does anyone know how to re-org those sysXXXXX tables? I noticed those
sysXXXXXXX tables have too many extents, but can I re-org those tables just
like I do to my regular tables (create a temp duplicate, unload all rows to
the temp, drop the table, rename the temp to the table, and so on....)? If so,
how do I create a duplicate? where is the dictionary? I could not run dbschema
on these tables because they are pseudo tables.
Here is my sysXXXXXX tables and their extents:
syscolauth 41
sysdefaults 22
sysprocauth 71
sysprocbody 68
sysprocplan 57
systabauth 39
systrigbody 26
Frank
Legally this can not be done without an export. There is supposed to be a
feature request in the system for future releases.
Paul Watson
Tel: +44 1414161772
Mob: +44 7818003457
Web: www.oninit.com
GO FURTHER with DB2
GET THERE FASTER with Informix.
Attend IDUG 2007 San Jose, North America
May 6-10, 2006
Visit http://www.iiug.org/conf for more information.
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On
> Behalf Of FRANK LAI
> Sent: 10 November 2006 16:55
> To: ids@iiug.org
> Subject: sysXXXX XX tables [7793]
>
>
> Does anyone know how to re-org those sysXXXXX tables? I
> noticed those sysXXXXXXX tables have too many extents, but
> can I re-org those tables just like I do to my regular tables
> (create a temp duplicate, unload all rows to the temp, drop
> the table, rename the temp to the table, and so on....)? If
> so, how do I create a duplicate? where is the dictionary? I
> could not run dbschema on these tables because they are
> pseudo tables.
>
> Here is my sysXXXXXX tables and their extents:
> syscolauth 41
> sysdefaults 22
> sysprocauth 71
> sysprocbody 68
> sysprocplan 57
> systabauth 39
> systrigbody 26
>
> Frank
>
>
> **************************************************************
> *****************
> Forum Note: Use "Reply" to post a response in the
> discussion forum.
You can't. What you can do is resize them just after you create the
database so that yoyu wind up with only 2 or 3 extents each.
j.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of
FRANK LAI
Sent: Friday, November 10, 2006 5:55 PM
To: ids@iiug.org
Subject: sysXXXX XX tables [7793]
Does anyone know how to re-org those sysXXXXX tables? I noticed those
sysXXXXXXX tables have too many extents, but can I re-org those tables just
like I do to my regular tables (create a temp duplicate, unload all rows to
the temp, drop the table, rename the temp to the table, and so on....)? If
so,
how do I create a duplicate? where is the dictionary? I could not run
dbschemaon these tables because they are pseudo tables.
Here is my sysXXXXXX tables and their extents:
syscolauth 41
sysdefaults 22
sysprocauth 71
sysprocbody 68
sysprocplan 57
systabauth 39
systrigbody 26
Frank
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
You cannot. Not to worry though, all that information is cached so as long as
your caches are sized correctely the engine will rarely go to disk for catalog
information and the fragmentation is irrelevant.
Art S. Kagel
----- Original Message -----
From: Frank Lai <ids@iiug.org>
At: 11/10 18:03:55
Does anyone know how to re-org those sysXXXXX tables? I noticed those
sysXXXXXXX tables have too many extents, but can I re-org those tables just
like I do to my regular tables (create a temp duplicate, unload all rows to
the temp, drop the table, rename the temp to the table, and so on....)? If so,
how do I create a duplicate? where is the dictionary? I could not run dbschema
on these tables because they are pseudo tables.
Here is my sysXXXXXX tables and their extents:
syscolauth 41
sysdefaults 22
sysprocauth 71
sysprocbody 68
sysprocplan 57
systabauth 39
systrigbody 26
Frank
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
> You cannot. Not to worry though, all that information is
> cached so as long as your caches are sized correctely the
> engine will rarely go to disk for catalog information and the
> fragmentation is irrelevant.
Yep Fragmentation is irrelevant upto the point you run out of extents - then
it becomes very relevant :-)
>
> Art S. Kagel
> ----- Original Message -----
> From: Frank Lai <ids@iiug.org>
> At: 11/10 18:03:55
>
> Does anyone know how to re-org those sysXXXXX tables? I
> noticed those sysXXXXXXX tables have too many extents, but
> can I re-org those tables just like I do to my regular tables
> (create a temp duplicate, unload all rows to the temp, drop
> the table, rename the temp to the table, and so on....)? If
> so, how do I create a duplicate? where is the dictionary? I
> could not run dbschema on these tables because they are
> pseudo tables.
>
> Here is my sysXXXXXX tables and their extents:
> syscolauth 41
> sysdefaults 22
> sysprocauth 71
> sysprocbody 68
> sysprocplan 57
> systabauth 39
> systrigbody 26
>
> Frank
>
>
> **************************************************************
> *****************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
> **************************************************************
> *****************
> Forum Note: Use "Reply" to post a response in the
> discussion forum.
Yes, a valid point in general, but 72 extents in a system catalog table is
nowhere near running out. I don't think any catalog table has more than one
'special' column so they can all handle over 200 extents even in 2K pagesize
dbspaces.
Art S. Kagel
----- Original Message -----
From: Paul Watson <ids@iiug.org>
At: 11/13 6:17:58
> You cannot. Not to worry though, all that information is
> cached so as long as your caches are sized correctely the
> engine will rarely go to disk for catalog information and the
> fragmentation is irrelevant.
Yep Fragmentation is irrelevant upto the point you run out of extents - then
it becomes very relevant :-)
>
> Art S. Kagel
> ----- Original Message -----
> From: Frank Lai <ids@iiug.org>
> At: 11/10 18:03:55
>
> Does anyone know how to re-org those sysXXXXX tables? I
> noticed those sysXXXXXXX tables have too many extents, but
> can I re-org those tables just like I do to my regular tables
> (create a temp duplicate, unload all rows to the temp, drop
> the table, rename the temp to the table, and so on....)? If
> so, how do I create a duplicate? where is the dictionary? I
> could not run dbschema on these tables because they are
> pseudo tables.
>
> Here is my sysXXXXXX tables and their extents:
> syscolauth 41
> sysdefaults 22
> sysprocauth 71
> sysprocbody 68
> sysprocplan 57
> systabauth 39
> systrigbody 26
>
> Frank
>
>
> **************************************************************
> *****************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
> **************************************************************
> *****************
> Forum Note: Use "Reply" to post a response in the
> discussion forum.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
I am surprised that nobody mentioned increasing the size of the next extent
to perhaps 10x the current size so the number of additional extents will be
limited ... since nobody knows when they will be given the chance to export
and import a working database.
Take care.
Clifton
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of ART
KAGEL, BLOOMBERG/ 731 LEXIN
Sent: Monday, November 13, 2006 7:21 AM
To: ids@iiug.org
Subject: RE: sysXXXX XX tables [7802]
Yes, a valid point in general, but 72 extents in a system catalog table is
nowhere near running out. I don't think any catalog table has more than one
'special' column so they can all handle over 200 extents even in 2K pagesize
dbspaces.
Art S. Kagel
----- Original Message -----
From: Paul Watson <ids@iiug.org>
At: 11/13 6:17:58
> You cannot. Not to worry though, all that information is
> cached so as long as your caches are sized correctely the
> engine will rarely go to disk for catalog information and the
> fragmentation is irrelevant.
Yep Fragmentation is irrelevant upto the point you run out of extents - then
it becomes very relevant :-)
>
> Art S. Kagel
> ----- Original Message -----
> From: Frank Lai <ids@iiug.org>
> At: 11/10 18:03:55
>
> Does anyone know how to re-org those sysXXXXX tables? I
> noticed those sysXXXXXXX tables have too many extents, but
> can I re-org those tables just like I do to my regular tables
> (create a temp duplicate, unload all rows to the temp, drop
> the table, rename the temp to the table, and so on....)? If
> so, how do I create a duplicate? where is the dictionary? I
> could not run dbschema on these tables because they are
> pseudo tables.
>
> Here is my sysXXXXXX tables and their extents:
> syscolauth 41
> sysdefaults 22
> sysprocauth 71
> sysprocbody 68
> sysprocplan 57
> systabauth 39
> systrigbody 26
>
> Frank
>
>
> **************************************************************
> *****************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
> **************************************************************
> *****************
> Forum Note: Use "Reply" to post a response in the
> discussion forum.
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
I never fully researched it, but I tried to increase the next extent size of
certain system catalog tables right after creating the database. For some
reason, many of the system catalog tables do not honor this resizing. As I
say, I haven't fully followed up on it so I can't say which tables behaved
this way, but I know it was enough of them for me not to take the time to do
it again. I believe I tried it several versions back (probably 7.x something)
so it may behave differently now.
Rob Schmitz
Embarq Data Management
rob.b.schmitz@embarq.com
http://www.embarq.com/
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Clifton
Bean
Sent: Monday, November 13, 2006 8:35 AM
To: ids@iiug.org
Subject: RE: sysXXXX XX tables [7804]
I am surprised that nobody mentioned increasing the size of the next extent
to perhaps 10x the current size so the number of additional extents will be
limited ... since nobody knows when they will be given the chance to export
and import a working database.
Take care.
Clifton
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of ART
KAGEL, BLOOMBERG/ 731 LEXIN
Sent: Monday, November 13, 2006 7:21 AM
To: ids@iiug.org
Subject: RE: sysXXXX XX tables [7802]
Yes, a valid point in general, but 72 extents in a system catalog table is
nowhere near running out. I don't think any catalog table has more than one
'special' column so they can all handle over 200 extents even in 2K pagesize
dbspaces.
Art S. Kagel
----- Original Message -----
From: Paul Watson <ids@iiug.org>
At: 11/13 6:17:58
> You cannot. Not to worry though, all that information is
> cached so as long as your caches are sized correctely the
> engine will rarely go to disk for catalog information and the
> fragmentation is irrelevant.
Yep Fragmentation is irrelevant upto the point you run out of extents - then
it becomes very relevant :-)
>
> Art S. Kagel
> ----- Original Message -----
> From: Frank Lai <ids@iiug.org>
> At: 11/10 18:03:55
>
> Does anyone know how to re-org those sysXXXXX tables? I
> noticed those sysXXXXXXX tables have too many extents, but
> can I re-org those tables just like I do to my regular tables
> (create a temp duplicate, unload all rows to the temp, drop
> the table, rename the temp to the table, and so on....)? If
> so, how do I create a duplicate? where is the dictionary? I
> could not run dbschema on these tables because they are
> pseudo tables.
>
> Here is my sysXXXXXX tables and their extents:
> syscolauth 41
> sysdefaults 22
> sysprocauth 71
> sysprocbody 68
> sysprocplan 57
> systabauth 39
> systrigbody 26
>
> Frank
>
>
> **************************************************************
> *****************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
> **************************************************************
> *****************
> Forum Note: Use "Reply" to post a response in the
> discussion forum.
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Not sure at which release it came in but you can ALTER TABLE sysXXX NEXT SIZE nnnn from at least 9.21 on. You do need an X lock on system table.