Modify initial and next extents
User asked how to modify initial and next extent sizes for fragmented tables without unload/reload. Expert responses clarified that initial extent cannot be changed without reloading, but next extent can be modified via ALTER TABLE. Three alternatives offered: ALTER INDEX TO CLUSTER (rebuilds table, requires space), ALTER FRAGMENT (similar rebuild, resource-intensive), or UNLOAD/RELOAD (manual but complete).
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, We have few tables with high numbers of extents. Some of the tables have up to 50s and 60s extents, and we want to reorg the tables. Beside SQL statement to alter the next extent size ALTER TABLE ttipip503104 MODIFY NEXT SIZE 360, is there any way we can modify the initial and next extents of the tables and their indexes without unload and reload? IDS 11.50.FC8 OS: HPUX-11.11 TBLspace Report for baan:baan.ttipip503104 Physical Address 18:427406 Creation date 11/24/2010 11:28:57 TBLspace Flags 802 Row Locking TBLspace use 4 bit bit-maps Maximum row size 92 Number of special columns 0 Number of keys 0 Number of extents 32 Current serial value 1 First extent size 4 Next extent size 16 Number of pages allocated 3592 Number of pages used 3592 Number of data pages 3577 Number of rows 75090 Partition partnum 7341643 Partition lockid 7341643 Extents Logical Page Physical Page Size Physical Pages 0 7:329400 4 4 4 7:427727 4 4 8 7:428077 4 4 12 7:509595 4 4 16 7:584469 4 4 20 7:584739 4 4 24 7:585862 4 4 28 7:586026 4 4 32 7:587506 4 4 36 7:590261 4 4 40 7:593531 12 12 52 7:597216 4 4 56 7:597265 4 4 60 7:597774 12 12 72 7:930695 12 12 84 7:931251 4 4 88 7:941904 8 8 96 7:943017 8 8 104 7:1000163 280 280 384 7:1001277 16 16 400 16:14021 48 48 448 16:138067 8 8 456 16:138306 1408 1408 1864 16:140228 72 72 1936 16:378667 96 96 2032 16:386091 104 104 2136 16:769924 176 176 2312 16:813437 512 512 2824 16:814349 88 88 2912 16:897377 48 48 2960 16:944183 400 400 3360 21:1022845 232 232 Index tipip5031041abaan fragment partition index1 in DBspace index1 Physical Address 58:588683 Creation date 11/24/2010 11:28:57 TBLspace Flags 802 Row Locking TBLspace use 4 bit bit-maps Maximum row size 92 Number of special columns 0 Number of keys 1 Number of extents 35 Current serial value 1 First extent size 4 Next extent size 16 Number of pages allocated 1952 Number of pages used 1940 Number of data pages 0 Number of rows 0 Partition partnum 9440345 Partition lockid 7341643 Extents Logical Page Physical Page Size Physical Pages 0 9:382431 4 4 4 53:644804 4 4 8 53:644931 4 4 12 53:645329 4 4 16 53:645643 4 4 20 53:646261 4 4 24 53:646324 4 4 28 53:646496 4 4 32 53:646517 4 4 36 53:646615 4 4 40 53:646778 4 4 44 53:646958 4 4 48 53:647031 4 4 52 53:647159 4 4 56 53:647188 4 4 60 53:647380 4 4 64 53:865362 8 8 72 53:898571 8 8 80 53:898587 8 8 88 53:924184 16 16 104 53:1023990 8 8 112 54:173987 248 248 360 54:174532 32 32 392 54:174574 696 696 1088 54:217136 24 24 1112 54:309990 32 32 1144 54:332909 88 88 1232 54:341277 584 584 1816 54:417268 8 8 1824 54:417781 8 8 1832 54:418244 16 16 1848 54:418585 8 8 1856 54:418716 16 16 1872 54:419704 48 48 1920 54:420764 32 32
Once created, there is no way to alter the initial extent without reloading.
You would want to modify the next extent. Here are your options:
ALTER INDEX TO CLUSTER: Clustering an index rebuilds the table with the rows
in index order in a different location within the dbspace. This cluster orderis not respected in future updates, requiring that this be done periodically.
For this to work, all indexes must be dropped, a single index clustered and
the remaining indexes recreated. This command requires that there be
sufficient space within the dbspace to create a copy of the original table. As
the table grows and this process is repeated, allocation of new extents cannot
be contiguous as the dbspace has holes left behind by the prior rebuild(s).
The reality of this solution is that it requires a large amount of additional
space to be maintained in the dbspace and it makes extent interleaving worse
over time.
ALTER FRAGMENT: By modifying the fragmentation scheme, INFORMIX will rebuild
the table, similar to option 1. For large tables, this is generally not a
viable alternative, as too much free space is required and performance suffers
when a temporary fragmentation scheme is employed.
UNLOAD / RELOAD DATA & DDL: You can unload the table, drop and re-create it,
then reload the data. To perform this in a realistic time frame, all indexes
must be removed before and recreated once reload is complete. In addition, by
performing a DROP on the object, constraints, stored procedures and other
related constructs need to be managed as well. This is a manual process which
requires the maintenance of DDL for the table, correct sequencing of commands
and the ability to control user access during the process. One wrong step and
data or application logic contained in the database may be corrupted or lost.
Even by performing this type of management, if more than one table exists in a
dbspace the DBA may still incur extent interleaving if he does one object at a
time.
Larry
> To: ids@iiug.org
> From: tri_trinh@hotmail.com
> Subject: Modify initial and next extents [22848]
> Date: Tue, 22 Feb 2011 15:01:07 -0500
>
> Hi,
>
> We have few tables with high numbers of extents. Some of the tables have up
to
> 50s and 60s extents, and we want to reorg the tables. Beside SQL statement to
> alter the next extent size ALTER TABLE ttipip503104 MODIFY NEXT SIZE 360,
is
> there any way we can modify the initial and next extents of the tables and
> their indexes without unload and reload?
>
> IDS 11.50.FC8
> OS: HPUX-11.11
>
> TBLspace Report for baan:baan.ttipip503104
>
> Physical Address 18:427406
>
> Creation date 11/24/2010 11:28:57
>
> TBLspace Flags 802 Row Locking
>
> TBLspace use 4 bit bit-maps
>
> Maximum row size 92
>
> Number of special columns 0
>
> Number of keys 0
>
> Number of extents 32
>
> Current serial value 1
>
> First extent size 4
>
> Next extent size 16
>
> Number of pages allocated 3592
>
> Number of pages used 3592
>
> Number of data pages 3577
>
> Number of rows 75090
>
> Partition partnum 7341643
>
> Partition lockid 7341643
>
> Extents
>
> Logical Page Physical Page Size Physical Pages
>
> 0 7:329400 4 4
>
> 4 7:427727 4 4
>
> 8 7:428077 4 4
>
> 12 7:509595 4 4
>
> 16 7:584469 4 4
>
> 20 7:584739 4 4
>
> 24 7:585862 4 4
>
> 28 7:586026 4 4
>
> 32 7:587506 4 4
>
> 36 7:590261 4 4
>
> 40 7:593531 12 12
>
> 52 7:597216 4 4
>
> 56 7:597265 4 4
>
> 60 7:597774 12 12
>
> 72 7:930695 12 12
>
> 84 7:931251 4 4
>
> 88 7:941904 8 8
>
> 96 7:943017 8 8
>
> 104 7:1000163 280 280
>
> 384 7:1001277 16 16
>
> 400 16:14021 48 48
>
> 448 16:138067 8 8
>
> 456 16:138306 1408 1408
>
> 1864 16:140228 72 72
>
> 1936 16:378667 96 96
>
> 2032 16:386091 104 104
>
> 2136 16:769924 176 176
>
> 2312 16:813437 512 512
>
> 2824 16:814349 88 88
>
> 2912 16:897377 48 48
>
> 2960 16:944183 400 400
>
> 3360 21:1022845 232 232
>
> Index tipip5031041abaan fragment partition index1 in DBspace index1
>
> Physical Address 58:588683
>
> Creation date 11/24/2010 11:28:57
>
> TBLspace Flags 802 Row Locking
>
> TBLspace use 4 bit bit-maps
>
> Maximum row size 92
>
> Number of special columns 0
>
> Number of keys 1
>
> Number of extents 35
>
> Current serial value 1
>
> First extent size 4
>
> Next extent size 16
>
> Number of pages allocated 1952
>
> Number of pages used 1940
>
> Number of data pages 0
>
> Number of rows 0
>
> Partition partnum 9440345
>
> Partition lockid 7341643
>
> Extents
>
> Logical Page Physical Page Size Physical Pages
>
> 0 9:382431 4 4
>
> 4 53:644804 4 4
>
> 8 53:644931 4 4
>
> 12 53:645329 4 4
>
> 16 53:645643 4 4
>
> 20 53:646261 4 4
>
> 24 53:646324 4 4
>
> 28 53:646496 4 4
>
> 32 53:646517 4 4
>
> 36 53:646615 4 4
>
> 40 53:646778 4 4
>
> 44 53:646958 4 4
>
> 48 53:647031 4 4
>
> 52 53:647159 4 4
>
> 56 53:647188 4 4
>
> 60 53:647380 4 4
>
> 64 53:865362 8 8
>
> 72 53:898571 8 8
>
> 80 53:898587 8 8
>
> 88 53:924184 16 16
>
> 104 53:1023990 8 8
>
> 112 54:173987 248 248
>
> 360 54:174532 32 32
>
> 392 54:174574 696 696
>
> 1088 54:217136 24 24
>
> 1112 54:309990 32 32
>
> 1144 54:332909 88 88
>
> 1232 54:341277 584 584
>
> 1816 54:417268 8 8
>
> 1824 54:417781 8 8
>
> 1832 54:418244 16 16
>
> 1848 54:418585 8 8
>
> 1856 54:418716 16 16
>
> 1872 54:419704 48 48
>
> 1920 54:420764 32 32
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
If you have another dbspace(s) that can hold the table or a major part of the
table. Change the next extent value and then something like this:
ALTER FRAGMENT ON TABLE dated_goods INIT IN db3;
This may reduce the total number of extents depending on the size of the free
blocks on the device.
How about:
ALTER TABLE customer MODIFY EXTENT SIZE 32 NEXT SIZE 32
John F. Miller III
STSM, Embedability Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 02/22/2011 12:01:07 PM:
> [image removed]
>
> Modify initial and next extents [22848]
>
> TRI TRINH
>
> to:
>
> ids
>
> 02/22/2011 12:14 PM
>
> Sent by:
>
> ids-bounces@iiug.org
>
> Please respond to ids
>
> Hi,
>
> We have few tables with high numbers of extents. Some of the tables
> have up to
> 50s and 60s extents, and we want to reorg the tables. Beside SQL
statement to
> alter the next extent size “ALTER TABLE ttipip503104 MODIFY NEXT
> SIZE 360”, is
> there any way we can modify the initial and next extents of the tables
and
> their indexes without unload and reload?
>
> IDS 11.50.FC8
> OS: HPUX-11.11
>
> TBLspace Report for baan:baan.ttipip503104
>
> Physical Address 18:427406
>
> Creation date 11/24/2010 11:28:57
>
> TBLspace Flags 802 Row Locking
>
> TBLspace use 4 bit bit-maps
>
> Maximum row size 92
>
> Number of special columns 0
>
> Number of keys 0
>
> Number of extents 32
>
> Current serial value 1
>
> First extent size 4
>
> Next extent size 16
>
> Number of pages allocated 3592
>
> Number of pages used 3592
>
> Number of data pages 3577
>
> Number of rows 75090
>
> Partition partnum 7341643
>
> Partition lockid 7341643
>
> Extents
>
> Logical Page Physical Page Size Physical Pages
>
> 0 7:329400 4 4
>
> 4 7:427727 4 4
>
> 8 7:428077 4 4
>
> 12 7:509595 4 4
>
> 16 7:584469 4 4
>
> 20 7:584739 4 4
>
> 24 7:585862 4 4
>
> 28 7:586026 4 4
>
> 32 7:587506 4 4
>
> 36 7:590261 4 4
>
> 40 7:593531 12 12
>
> 52 7:597216 4 4
>
> 56 7:597265 4 4
>
> 60 7:597774 12 12
>
> 72 7:930695 12 12
>
> 84 7:931251 4 4
>
> 88 7:941904 8 8
>
> 96 7:943017 8 8
>
> 104 7:1000163 280 280
>
> 384 7:1001277 16 16
>
> 400 16:14021 48 48
>
> 448 16:138067 8 8
>
> 456 16:138306 1408 1408
>
> 1864 16:140228 72 72
>
> 1936 16:378667 96 96
>
> 2032 16:386091 104 104
>
> 2136 16:769924 176 176
>
> 2312 16:813437 512 512
>
> 2824 16:814349 88 88
>
> 2912 16:897377 48 48
>
> 2960 16:944183 400 400
>
> 3360 21:1022845 232 232
>
> Index tipip5031041abaan fragment partition index1 in DBspace index1
>
> Physical Address 58:588683
>
> Creation date 11/24/2010 11:28:57
>
> TBLspace Flags 802 Row Locking
>
> TBLspace use 4 bit bit-maps
>
> Maximum row size 92
>
> Number of special columns 0
>
> Number of keys 1
>
> Number of extents 35
>
> Current serial value 1
>
> First extent size 4
>
> Next extent size 16
>
> Number of pages allocated 1952
>
> Number of pages used 1940
>
> Number of data pages 0
>
> Number of rows 0
>
> Partition partnum 9440345
>
> Partition lockid 7341643
>
> Extents
>
> Logical Page Physical Page Size Physical Pages
>
> 0 9:382431 4 4
>
> 4 53:644804 4 4
>
> 8 53:644931 4 4
>
> 12 53:645329 4 4
>
> 16 53:645643 4 4
>
> 20 53:646261 4 4
>
> 24 53:646324 4 4
>
> 28 53:646496 4 4
>
> 32 53:646517 4 4
>
> 36 53:646615 4 4
>
> 40 53:646778 4 4
>
> 44 53:646958 4 4
>
> 48 53:647031 4 4
>
> 52 53:647159 4 4
>
> 56 53:647188 4 4
>
> 60 53:647380 4 4
>
> 64 53:865362 8 8
>
> 72 53:898571 8 8
>
> 80 53:898587 8 8
>
> 88 53:924184 16 16
>
> 104 53:1023990 8 8
>
> 112 54:173987 248 248
>
> 360 54:174532 32 32
>
> 392 54:174574 696 696
>
> 1088 54:217136 24 24
>
> 1112 54:309990 32 32
>
> 1144 54:332909 88 88
>
> 1232 54:341277 584 584
>
> 1816 54:417268 8 8
>
> 1824 54:417781 8 8
>
> 1832 54:418244 16 16
>
> 1848 54:418585 8 8
>
> 1856 54:418716 16 16
>
> 1872 54:419704 48 48
>
> 1920 54:420764 32 32
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
With 11.50.UC8 you can run
ALTER TABLE tablename EXTENT SIZE nnn.
This will not re-org the data or effect the extents already allocated but will
set the first extent size to the specified value (nnn). If you drop and
recreate the indexes, their first extent size will be calculated off the first
extent size of the table.
Stuart McCann
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of TRI TRINH
Sent: Wednesday, 23 February 2011 7:01 AM
To: ids@iiug.org
Subject: Modify initial and next extents [22848]
Hi,
We have few tables with high numbers of extents. Some of the tables have up to
50s and 60s extents, and we want to reorg the tables. Beside SQL statement to
alter the next extent size "ALTER TABLE ttipip503104 MODIFY NEXT SIZE 360", is
there any way we can modify the initial and next extents of the tables and
their indexes without unload and reload?
IDS 11.50.FC8
OS: HPUX-11.11
TBLspace Report for baan:baan.ttipip503104
Physical Address 18:427406
Creation date 11/24/2010 11:28:57
TBLspace Flags 802 Row Locking
TBLspace use 4 bit bit-maps
Maximum row size 92
Number of special columns 0
Number of keys 0
Number of extents 32
Current serial value 1
First extent size 4
Next extent size 16
Number of pages allocated 3592
Number of pages used 3592
Number of data pages 3577
Number of rows 75090
Partition partnum 7341643
Partition lockid 7341643
Extents
Logical Page Physical Page Size Physical Pages
0 7:329400 4 4
4 7:427727 4 4
8 7:428077 4 4
12 7:509595 4 4
16 7:584469 4 4
20 7:584739 4 4
24 7:585862 4 4
28 7:586026 4 4
32 7:587506 4 4
36 7:590261 4 4
40 7:593531 12 12
52 7:597216 4 4
56 7:597265 4 4
60 7:597774 12 12
72 7:930695 12 12
84 7:931251 4 4
88 7:941904 8 8
96 7:943017 8 8
104 7:1000163 280 280
384 7:1001277 16 16
400 16:14021 48 48
448 16:138067 8 8
456 16:138306 1408 1408
1864 16:140228 72 72
1936 16:378667 96 96
2032 16:386091 104 104
2136 16:769924 176 176
2312 16:813437 512 512
2824 16:814349 88 88
2912 16:897377 48 48
2960 16:944183 400 400
3360 21:1022845 232 232
Index tipip5031041abaan fragment partition index1 in DBspace index1
Physical Address 58:588683
Creation date 11/24/2010 11:28:57
TBLspace Flags 802 Row Locking
TBLspace use 4 bit bit-maps
Maximum row size 92
Number of special columns 0
Number of keys 1
Number of extents 35
Current serial value 1
First extent size 4
Next extent size 16
Number of pages allocated 1952
Number of pages used 1940
Number of data pages 0
Number of rows 0
Partition partnum 9440345
Partition lockid 7341643
Extents
Logical Page Physical Page Size Physical Pages
0 9:382431 4 4
4 53:644804 4 4
8 53:644931 4 4
12 53:645329 4 4
16 53:645643 4 4
20 53:646261 4 4
24 53:646324 4 4
28 53:646496 4 4
32 53:646517 4 4
36 53:646615 4 4
40 53:646778 4 4
44 53:646958 4 4
48 53:647031 4 4
52 53:647159 4 4
56 53:647188 4 4
60 53:647380 4 4
64 53:865362 8 8
72 53:898571 8 8
80 53:898587 8 8
88 53:924184 16 16
104 53:1023990 8 8
112 54:173987 248 248
360 54:174532 32 32
392 54:174574 696 696
1088 54:217136 24 24
1112 54:309990 32 32
1144 54:332909 88 88
1232 54:341277 584 584
1816 54:417268 8 8
1824 54:417781 8 8
1832 54:418244 16 16
1848 54:418585 8 8
1856 54:418716 16 16
1872 54:419704 48 48
1920 54:420764 32 32
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
***************************************************************
This message is intended for the addressee named and may contain confidential
information. If you are not the intended recipient, please delete it and
notify the sender. Views expressed in this message are those of the individual
sender, and are not necessarily the views of the Land and Property Management
Authority. This email message has been swept by MIMEsweeper for the presence
of computer viruses.
***************************************************************
Please consider the environment before printing this email.
You could also repack and shrink the table ... possibly freeing up some
extents ...
Peter Logan
Senior Database Administrator
Phone: 616/878-8309
From: "Stuart McCann" <Stuart.McCann@lpma.nsw.gov.au>
To: ids@iiug.org
Date: 02/22/2011 04:10 PM
Subject: RE: Modify initial and next extents [22854]
Sent by: ids-bounces@iiug.org
With 11.50.UC8 you can run
ALTER TABLE tablename EXTENT SIZE nnn.
This will not re-org the data or effect the extents already allocated but
will
set the first extent size to the specified value (nnn). If you drop and
recreate the indexes, their first extent size will be calculated off the
first
extent size of the table.
Stuart McCann
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of TRI
TRINH
Sent: Wednesday, 23 February 2011 7:01 AM
To: ids@iiug.org
Subject: Modify initial and next extents [22848]
Hi,
We have few tables with high numbers of extents. Some of the tables have
up to
50s and 60s extents, and we want to reorg the tables. Beside SQL statement
to
alter the next extent size "ALTER TABLE ttipip503104 MODIFY NEXT SIZE
360", is
there any way we can modify the initial and next extents of the tables and
their indexes without unload and reload?
IDS 11.50.FC8
OS: HPUX-11.11
TBLspace Report for baan:baan.ttipip503104
Physical Address 18:427406
Creation date 11/24/2010 11:28:57
TBLspace Flags 802 Row Locking
TBLspace use 4 bit bit-maps
Maximum row size 92
Number of special columns 0
Number of keys 0
Number of extents 32
Current serial value 1
First extent size 4
Next extent size 16
Number of pages allocated 3592
Number of pages used 3592
Number of data pages 3577
Number of rows 75090
Partition partnum 7341643
Partition lockid 7341643
Extents
Logical Page Physical Page Size Physical Pages
0 7:329400 4 4
4 7:427727 4 4
8 7:428077 4 4
12 7:509595 4 4
16 7:584469 4 4
20 7:584739 4 4
24 7:585862 4 4
28 7:586026 4 4
32 7:587506 4 4
36 7:590261 4 4
40 7:593531 12 12
52 7:597216 4 4
56 7:597265 4 4
60 7:597774 12 12
72 7:930695 12 12
84 7:931251 4 4
88 7:941904 8 8
96 7:943017 8 8
104 7:1000163 280 280
384 7:1001277 16 16
400 16:14021 48 48
448 16:138067 8 8
456 16:138306 1408 1408
1864 16:140228 72 72
1936 16:378667 96 96
2032 16:386091 104 104
2136 16:769924 176 176
2312 16:813437 512 512
2824 16:814349 88 88
2912 16:897377 48 48
2960 16:944183 400 400
3360 21:1022845 232 232
Index tipip5031041abaan fragment partition index1 in DBspace index1
Physical Address 58:588683
Creation date 11/24/2010 11:28:57
TBLspace Flags 802 Row Locking
TBLspace use 4 bit bit-maps
Maximum row size 92
Number of special columns 0
Number of keys 1
Number of extents 35
Current serial value 1
First extent size 4
Next extent size 16
Number of pages allocated 1952
Number of pages used 1940
Number of data pages 0
Number of rows 0
Partition partnum 9440345
Partition lockid 7341643
Extents
Logical Page Physical Page Size Physical Pages
0 9:382431 4 4
4 53:644804 4 4
8 53:644931 4 4
12 53:645329 4 4
16 53:645643 4 4
20 53:646261 4 4
24 53:646324 4 4
28 53:646496 4 4
32 53:646517 4 4
36 53:646615 4 4
40 53:646778 4 4
44 53:646958 4 4
48 53:647031 4 4
52 53:647159 4 4
56 53:647188 4 4
60 53:647380 4 4
64 53:865362 8 8
72 53:898571 8 8
80 53:898587 8 8
88 53:924184 16 16
104 53:1023990 8 8
112 54:173987 248 248
360 54:174532 32 32
392 54:174574 696 696
1088 54:217136 24 24
1112 54:309990 32 32
1144 54:332909 88 88
1232 54:341277 584 584
1816 54:417268 8 8
1824 54:417781 8 8
1832 54:418244 16 16
1848 54:418585 8 8
1856 54:418716 16 16
1872 54:419704 48 48
1920 54:420764 32 32
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
***************************************************************
This message is intended for the addressee named and may contain
confidential
information. If you are not the intended recipient, please delete it and
notify the sender. Views expressed in this message are those of the
individual
sender, and are not necessarily the views of the Land and Property
Management
Authority. This email message has been swept by MIMEsweeper for the
presence
of computer viruses.
***************************************************************
Please consider the environment before printing this email.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Larry, Informix is the database for Baan ERP. Baan needs all the indexes. I don't know if option 1 to cluster the indexes is feasible. I will look into it. Perhaps, I will try in test. Thank you for the options.
Hi Peter, We just upgraded to version 11.50.FC8 from 10.00.FC6. The repack and shrink sql are new to us. Do we need to block the users out and put the engine into quiescent mode to do the repack and shrink?
No, it's a totally "online" process... Peter Logan Senior Database Administrator Phone: 616/878-8309 From: "TRI TRINH" <tri_trinh@hotmail.com> To: ids@iiug.org Date: 02/22/2011 04:49 PM Subject: Re: RE: Modify initial and next extents [22858] Sent by: ids-bounces@iiug.org Hi Peter, We just upgraded to version 11.50.FC8 from 10.00.FC6. The repack and shrink sql are new to us. Do we need to block the users out and put the engine into quiescent mode to do the repack and shrink? ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Almost forgot this ... There is a bug that exists if an index has the same name as the table ... the work around I do is rename the index prior to executing the repack and shrink .. and then naming it back when done ... Peter Peter Logan Senior Database Administrator Phone: 616/878-8309 From: "TRI TRINH" <tri_trinh@hotmail.com> To: ids@iiug.org Date: 02/22/2011 04:49 PM Subject: Re: RE: Modify initial and next extents [22858] Sent by: ids-bounces@iiug.org Hi Peter, We just upgraded to version 11.50.FC8 from 10.00.FC6. The repack and shrink sql are new to us. Do we need to block the users out and put the engine into quiescent mode to do the repack and shrink? ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
I don't know if the bug is fixed in FC8 ... it is still there in FC7W3 .... Peter Logan Senior Database Administrator Phone: 616/878-8309 From: "Peter_Logan@spartanstores.com" <Peter_Logan@spartanstores.com> To: ids@iiug.org Date: 02/22/2011 04:55 PM Subject: Re: RE: Modify initial and next extents [22860] Sent by: ids-bounces@iiug.org Almost forgot this ... There is a bug that exists if an index has the same name as the table ... the work around I do is rename the index prior to executing the repack and shrink .. and then naming it back when done .... Peter Peter Logan Senior Database Administrator Phone: 616/878-8309 From: "TRI TRINH" <tri_trinh@hotmail.com> To: ids@iiug.org Date: 02/22/2011 04:49 PM Subject: Re: RE: Modify initial and next extents [22858] Sent by: ids-bounces@iiug.org Hi Peter, We just upgraded to version 11.50.FC8 from 10.00.FC6. The repack and shrink sql are new to us. Do we need to block the users out and put the engine into quiescent mode to do the repack and shrink? ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
In 11.50 you can also ALTER TABLE tipip503104 MODIFY EXTENT SIZE 1952; to
modify the setting for the initial extent, then you can use one of the
following:
ALTER FRAGMENT FOR TABLE tipip503104 INIT IN <some dbspace>;-- You can use the same dbspace the table already lives in or a different
one.
In 11.70 you could use the new SQL API function DEFRAGMENT, but that's not
available in 11.50.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Tue, Feb 22, 2011 at 3:01 PM, TRI TRINH <tri_trinh@hotmail.com> wrote:
> Hi,
>
> We have few tables with high numbers of extents. Some of the tables have up
> to
> 50s and 60s extents, and we want to reorg the tables. Beside SQL statement
> to
> alter the next extent size ALTER TABLE ttipip503104 MODIFY NEXT SIZE 360,
> is
> there any way we can modify the initial and next extents of the tables and
> their indexes without unload and reload?
>
> IDS 11.50.FC8
> OS: HPUX-11.11
>
> TBLspace Report for baan:baan.ttipip503104
>
> Physical Address 18:427406
>
> Creation date 11/24/2010 11:28:57
>
> TBLspace Flags 802 Row Locking
>
> TBLspace use 4 bit bit-maps
>
> Maximum row size 92
>
> Number of special columns 0
>
> Number of keys 0
>
> Number of extents 32
>
> Current serial value 1
>
> First extent size 4
>
> Next extent size 16
>
> Number of pages allocated 3592
>
> Number of pages used 3592
>
> Number of data pages 3577
>
> Number of rows 75090
>
> Partition partnum 7341643
>
> Partition lockid 7341643
>
> Extents
>
> Logical Page Physical Page Size Physical Pages
>
> 0 7:329400 4 4
>
> 4 7:427727 4 4
>
> 8 7:428077 4 4
>
> 12 7:509595 4 4
>
> 16 7:584469 4 4
>
> 20 7:584739 4 4
>
> 24 7:585862 4 4
>
> 28 7:586026 4 4
>
> 32 7:587506 4 4
>
> 36 7:590261 4 4
>
> 40 7:593531 12 12
>
> 52 7:597216 4 4
>
> 56 7:597265 4 4
>
> 60 7:597774 12 12
>
> 72 7:930695 12 12
>
> 84 7:931251 4 4
>
> 88 7:941904 8 8
>
> 96 7:943017 8 8
>
> 104 7:1000163 280 280
>
> 384 7:1001277 16 16
>
> 400 16:14021 48 48
>
> 448 16:138067 8 8
>
> 456 16:138306 1408 1408
>
> 1864 16:140228 72 72
>
> 1936 16:378667 96 96
>
> 2032 16:386091 104 104
>
> 2136 16:769924 176 176
>
> 2312 16:813437 512 512
>
> 2824 16:814349 88 88
>
> 2912 16:897377 48 48
>
> 2960 16:944183 400 400
>
> 3360 21:1022845 232 232
>
> Index tipip5031041abaan fragment partition index1 in DBspace index1
>
> Physical Address 58:588683
>
> Creation date 11/24/2010 11:28:57
>
> TBLspace Flags 802 Row Locking
>
> TBLspace use 4 bit bit-maps
>
> Maximum row size 92
>
> Number of special columns 0
>
> Number of keys 1
>
> Number of extents 35
>
> Current serial value 1
>
> First extent size 4
>
> Next extent size 16
>
> Number of pages allocated 1952
>
> Number of pages used 1940
>
> Number of data pages 0
>
> Number of rows 0
>
> Partition partnum 9440345
>
> Partition lockid 7341643
>
> Extents
>
> Logical Page Physical Page Size Physical Pages
>
> 0 9:382431 4 4
>
> 4 53:644804 4 4
>
> 8 53:644931 4 4
>
> 12 53:645329 4 4
>
> 16 53:645643 4 4
>
> 20 53:646261 4 4
>
> 24 53:646324 4 4
>
> 28 53:646496 4 4
>
> 32 53:646517 4 4
>
> 36 53:646615 4 4
>
> 40 53:646778 4 4
>
> 44 53:646958 4 4
>
> 48 53:647031 4 4
>
> 52 53:647159 4 4
>
> 56 53:647188 4 4
>
> 60 53:647380 4 4
>
> 64 53:865362 8 8
>
> 72 53:898571 8 8
>
> 80 53:898587 8 8
>
> 88 53:924184 16 16
>
> 104 53:1023990 8 8
>
> 112 54:173987 248 248
>
> 360 54:174532 32 32
>
> 392 54:174574 696 696
>
> 1088 54:217136 24 24
>
> 1112 54:309990 32 32
>
> 1144 54:332909 88 88
>
> 1232 54:341277 584 584
>
> 1816 54:417268 8 8
>
> 1824 54:417781 8 8
>
> 1832 54:418244 16 16
>
> 1848 54:418585 8 8
>
> 1856 54:418716 16 16
>
> 1872 54:419704 48 48
>
> 1920 54:420764 32 32
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--0015175ce182ff920f049ce7315a
Bet you say that to all the boys!
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Tue, Feb 22, 2011 at 4:00 PM, John Miller iii <miller3@us.ibm.com> wrote:
>
> How about:
>
> ALTER TABLE customer MODIFY EXTENT SIZE 32 NEXT SIZE 32>
> John F. Miller III
> STSM, Embedability Architect
> miller3@us.ibm.com
> 503-578-5645
> IBM Informix Dynamic Server (IDS)
>
> ids-bounces@iiug.org wrote on 02/22/2011 12:01:07 PM:
>
> > [image removed]
> >
> > Modify initial and next extents [22848]
> >
> > TRI TRINH
> >
> > to:
> >
> > ids
> >
> > 02/22/2011 12:14 PM
> >
> > Sent by:
> >
> > ids-bounces@iiug.org
> >
> > Please respond to ids
> >
> > Hi,
> >
> > We have few tables with high numbers of extents. Some of the tables
> > have up to
> > 50s and 60s extents, and we want to reorg the tables. Beside SQL
> statement to
> > alter the next extent size “ALTER TABLE ttipip503104 MODIFY NEXT
> > SIZE 360”, is
> > there any way we can modify the initial and next extents of the tables
> and
> > their indexes without unload and reload?
> >
> > IDS 11.50.FC8
> > OS: HPUX-11.11
> >
> > TBLspace Report for baan:baan.ttipip503104
> >
> > Physical Address 18:427406
> >
> > Creation date 11/24/2010 11:28:57
> >
> > TBLspace Flags 802 Row Locking
> >
> > TBLspace use 4 bit bit-maps
> >
> > Maximum row size 92
> >
> > Number of special columns 0
> >
> > Number of keys 0
> >
> > Number of extents 32
> >
> > Current serial value 1
> >
> > First extent size 4
> >
> > Next extent size 16
> >
> > Number of pages allocated 3592
> >
> > Number of pages used 3592
> >
> > Number of data pages 3577
> >
> > Number of rows 75090
> >
> > Partition partnum 7341643
> >
> > Partition lockid 7341643
> >
> > Extents
> >
> > Logical Page Physical Page Size Physical Pages
> >
> > 0 7:329400 4 4
> >
> > 4 7:427727 4 4
> >
> > 8 7:428077 4 4
> >
> > 12 7:509595 4 4
> >
> > 16 7:584469 4 4
> >
> > 20 7:584739 4 4
> >
> > 24 7:585862 4 4
> >
> > 28 7:586026 4 4
> >
> > 32 7:587506 4 4
> >
> > 36 7:590261 4 4
> >
> > 40 7:593531 12 12
> >
> > 52 7:597216 4 4
> >
> > 56 7:597265 4 4
> >
> > 60 7:597774 12 12
> >
> > 72 7:930695 12 12
> >
> > 84 7:931251 4 4
> >
> > 88 7:941904 8 8
> >
> > 96 7:943017 8 8
> >
> > 104 7:1000163 280 280
> >
> > 384 7:1001277 16 16
> >
> > 400 16:14021 48 48
> >
> > 448 16:138067 8 8
> >
> > 456 16:138306 1408 1408
> >
> > 1864 16:140228 72 72
> >
> > 1936 16:378667 96 96
> >
> > 2032 16:386091 104 104
> >
> > 2136 16:769924 176 176
> >
> > 2312 16:813437 512 512
> >
> > 2824 16:814349 88 88
> >
> > 2912 16:897377 48 48
> >
> > 2960 16:944183 400 400
> >
> > 3360 21:1022845 232 232
> >
> > Index tipip5031041abaan fragment partition index1 in DBspace index1
> >
> > Physical Address 58:588683
> >
> > Creation date 11/24/2010 11:28:57
> >
> > TBLspace Flags 802 Row Locking
> >
> > TBLspace use 4 bit bit-maps
> >
> > Maximum row size 92
> >
> > Number of special columns 0
> >
> > Number of keys 1
> >
> > Number of extents 35
> >
> > Current serial value 1
> >
> > First extent size 4
> >
> > Next extent size 16
> >
> > Number of pages allocated 1952
> >
> > Number of pages used 1940
> >
> > Number of data pages 0
> >
> > Number of rows 0
> >
> > Partition partnum 9440345
> >
> > Partition lockid 7341643
> >
> > Extents
> >
> > Logical Page Physical Page Size Physical Pages
> >
> > 0 9:382431 4 4
> >
> > 4 53:644804 4 4
> >
> > 8 53:644931 4 4
> >
> > 12 53:645329 4 4
> >
> > 16 53:645643 4 4
> >
> > 20 53:646261 4 4
> >
> > 24 53:646324 4 4
> >
> > 28 53:646496 4 4
> >
> > 32 53:646517 4 4
> >
> > 36 53:646615 4 4
> >
> > 40 53:646778 4 4
> >
> > 44 53:646958 4 4
> >
> > 48 53:647031 4 4
> >
> > 52 53:647159 4 4
> >
> > 56 53:647188 4 4
> >
> > 60 53:647380 4 4
> >
> > 64 53:865362 8 8
> >
> > 72 53:898571 8 8
> >
> > 80 53:898587 8 8
> >
> > 88 53:924184 16 16
> >
> > 104 53:1023990 8 8
> >
> > 112 54:173987 248 248
> >
> > 360 54:174532 32 32
> >
> > 392 54:174574 696 696
> >
> > 1088 54:217136 24 24
> >
> > 1112 54:309990 32 32
> >
> > 1144 54:332909 88 88
> >
> > 1232 54:341277 584 584
> >
> > 1816 54:417268 8 8
> >
> > 1824 54:417781 8 8
> >
> > 1832 54:418244 16 16
> >
> > 1848 54:418585 8 8
> >
> > 1856 54:418716 16 16
> >
> > 1872 54:419704 48 48
> >
> > 1920 54:420764 32 32
> >
> >
> >
> *******************************************************************************
>
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--0015175ce18260b1c6049ce73c99
Hi, Exclusive-mode locking automatically occurs during the ALTER INDEX, ALTER TABLE, CREATE INDEX, DROP INDEX, RENAME COLUMN, RENAME TABLE, START VIOLATIONS TABLE, STOP VIOLATIONS TABLE, and TRUNCATE statements. We have some archive tables, and we know that fact that users never touch them. Is it safe to issue "ALTER TABLE or ALTER FRAGMENT" while the engine is online?
Yes, it is safe. If users are accessing the table you will not be able to acquire the needed exclusive lock. If you do acquire the lock then other users will not be able to access the table for updates until you release the lock. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Feb 23, 2011 at 2:45 PM, TRI TRINH <tri_trinh@hotmail.com> wrote: > Hi, > > Exclusive-mode locking automatically occurs during the ALTER INDEX, ALTER > TABLE, CREATE INDEX, DROP INDEX, RENAME COLUMN, RENAME TABLE, START > VIOLATIONS > TABLE, STOP VIOLATIONS TABLE, and TRUNCATE statements. > > We have some archive tables, and we know that fact that users never touch > them. Is it safe to issue "ALTER TABLE or ALTER FRAGMENT" while the engine > is > online? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0015175cde76bea3d5049cf93631