systabauth fragmentation

systabauth fragmentation

Post by CJSE.. » Sat, 03 Jul 1993 04:34:53



In one of my databases the systabauth table is fragmented into 10
segments.  What's the best way to fix a system table when this happens.
I usually unload the table, recreate it and reload.  But, I'm afraid
to try anything like this with a system table.

Thanks,
Cathy


Lexmark International, Inc.      Phone:    (606) 232-7194
740 New Circle Rd. NW            Fax:      (606) 232-2177
Lexington, KY 40511-1876

 
 
 

systabauth fragmentation

Post by Jonathan Leffl » Sat, 10 Jul 1993 12:48:08


Quote:>From: uunet!lexmark.com!CJSENFT
>Subject: systabauth fragmentation
>Date: Thu, 1 Jul 1993 19:34:53 GMT
>X-Informix-List-Id: <news.3720>
>In one of my databases the systabauth table is fragmented into 10
>segments.  What's the best way to fix a system table when this happens.
>I usually unload the table, recreate it and reload.  But, I'm afraid
>to try anything like this with a system table.

(1) It probably doesn't matter.  That is to say, the 8 extent limit makes a
    marginal difference to performance, but the difference is going to be
    small bits of a tenth of a percent.

(2) You can set next extent size on a system table:
        ALTER TABLE Systabauth MODIFY NEXT SIZE 64;

(3) You won't be able to rebuild any system table short of dropping the
    database and rebuilding it.

Yours,


 
 
 

systabauth fragmentation

Post by David R. Gilbe » Sat, 10 Jul 1993 03:27:07



>In one of my databases the systabauth table is fragmented into 10
>segments.  What's the best way to fix a system table when this happens.
>I usually unload the table, recreate it and reload.  But, I'm afraid
>to try anything like this with a system table.

>Thanks,
>Cathy

I'm assuming your running under Informix-OnLine...

I've had this happen with 'syscolumns' before. When Informix
creates a database, all the system tables are created with
the default extent sizes (usually 8).

The best thing to do is alter the next extent size of the
'systabauth' table. Use either 'isql' or write a quick 4GL
program that executes the following SQL statement (su informix first):

    alter table systabauth next size (100)

(I think the syntax is correct). The '100' is the extent
size Informix will use the next time it grabs an extent.
Feel free to change that number (the larger, the less number
of extents will be created). You might as well do 'syscolumns'
too.

Hope that helps!

- Dave
*--------------------------------------------------------------------------*

|  Consultant - BALR Corporation - (708) 575-8200  |   promotes health and |
|   UUCP: ...uunet!balr.com!doc                    |   happiness to all."  |
|  Snail: 600 Enterprise Dr, Oak Brook IL 60521    |         - doc         |
*==========================================================================*

 
 
 

systabauth fragmentation

Post by Dave Kosen » Sun, 11 Jul 1993 01:11:09


Quote:Jonathan Leffler writes:

|> >In one of my databases the systabauth table is fragmented into 10
|> >segments.  What's the best way to fix a system table when this happens.
|> >I usually unload the table, recreate it and reload.  But, I'm afraid
|> >to try anything like this with a system table.
|>
|> (1) It probably doesn't matter.  That is to say, the 8 extent limit makes a
|>     marginal difference to performance, but the difference is going to be
|>     small bits of a tenth of a percent.
|>
|> (2) You can set next extent size on a system table:
|>   ALTER TABLE Systabauth MODIFY NEXT SIZE 64;
|>
|> (3) You won't be able to rebuild any system table short of dropping the
|>     database and rebuilding it.

Not necessarily.  I have found that tbunloading a table then tbloading
it back it will cause the extents to be compressed into a single extent.
I haven't tested this exhaustively, so I can't state unequivocably that
it will work in every situation.  But tbunloading the database may well
have that effect on each component table.

Dave
Disclaimer: These opinions are not those of Informix Software, Inc.
**************************************************************************
"I look back with some satisfaction on what an idiot I was when I was 25,
 but when I do that, I'm assuming I'm no longer an idiot." - Andy Rooney