Arranging Storage and Locking (Table

Home | Previous Page | Next Page The Table Option > Creating or Altering a Table >

Arranging Storage and Locking (Table_options)

To display the TABLE_OPTIONS menu, as Figure 55 shows, select Table_options from the CREATE TABLE menu (or ALTER TABLE menu). You can then specify storage-management parameters, such as location and distribution of data on the storage media.

Figure 55. The TABLE_OPTIONS Menu

TABLE_OPTIONS clients:   Storage  eXtent_size  Next_size  Lock_mode  Exit
Define dbspace or fragmentation strategy for table storage.

---- Page 1 of 1 ---- mydata@mydbserv ---------- Press CTRL-W for Help ----

 Column Name                     Type            Length   Index Nulls

The TABLE_OPTIONS menu contains the following options.

Option	Purpose	Instructions
Storage	Displays dbspaces and enables you to assign a dbspace to the current table Enables you to define a fragmentation strategy for the current table	Selecting Dbspaces Fragmenting a New Table
eXtent_size	Enables you to specify the initial extent size of the table	Setting the Extent Size
Next_size	Enables you to specify the next extent size	Setting the Extent Size
Lock_mode	Enables you to select either Page or Row as the lock mode	Determining the Lock Mode
Exit	Returns to the previous menu	none

For assistance in setting Table_options values such as dbspaces, fragmentation strategy, extent sizes, and lock mode, see your IBM Informix: Administrator's Guide and IBM Informix: Performance Guide.

Selecting Dbspaces

To display the STORAGE menu, as Figure 56 shows, select the Storage option from the TABLE_OPTIONS menu.

Figure 56. The STORAGE Menu for Storing Table Data and Defining Fragmentation Strategy

STORAGE new_acct:    Dbspace    Fragment   Exit
Select a dbspace in which to store the table.

----- Page 1 of 1 ----- mydata@mydbserv -------- Press CTRL-W for Help ----

To display the SELECT DBSPACE screen, as Figure 57 shows, select Dbspace from the STORAGE menu. Use the arrow keys to highlight a dbspace from the list of dbspaces in the current database and then press RETURN.

Figure 57. The SELECT DBSPACE Screen for Specifying Table Storage

SELECT DBSPACE >>
Select a dbspace with the Arrow Keys, or enter a name, then press Return.

----- Page 1 of 1 ---- mydata@mydbserv --------- Press CTRL-W for Help ----

 rootdbs 
pers_dbs
empl_dbs

Select Fragment to set up fragmentation strategy in a series of additional menus. For instructions, see Fragmenting a New Table or Altering Fragmentation for an Existing Table.

Fragmenting a New Table

To arrange fragmentation for a new table, display the FRAGMENT menu, as Figure 58 shows. You reach this menu through the following steps:

On the main menu, select Table.
On the TABLE menu, select Create.
On the CREATE TABLE, select Table_options.
On the TABLE_OPTIONS menu, select Storage.

On the STORAGE menu, select Fragment.

Figure 58. The FRAGMENT Menu for Defining Fragmentation Strategy

FRAGMENT new_acct:   Round_robin    eXpression   rOwids   Exit
Select and define a round robin fragmentation strategy.

----- Page 1 of 1 ----- mydata@mydbserv -------- Press CTRL-W for Help ----

Select the strategy that you want from the FRAGMENT menu, as the following table shows.

Option	Purpose	Instructions
Round_robin	Selects a round-robin strategy for fragmentation	Figure 71
eXpression	Selects an expression strategy for fragmentation	Expression Strategy Setup
rOwids	Adds a column that contains rowids to a fragmented table (The database server does not automatically assign rowids when you insert rows in a fragmented table.)	Press the `o` key to explicitly enable access by rowid
Exit	Exits the FRAGMENT menu and returns to the STORAGE menu	Figure 56

DB–Access has no FRAGMENT menu option for hash or hybrid fragmentation. If you want this type of strategy, use the SQL menu to enter and run the CREATE TABLE or ALTER TABLE statement.

Extended Parallel Server

Extended Parallel Server does not support rowids for fragmented tables.

End of Extended Parallel Server

Round-Robin Setup

To display the ROUND_ROBIN menu, as Figure 59 shows, select the Round_robin option on the FRAGMENT menu.

Figure 59. The ROUND_ROBIN Menu for Selecting Fragment Storage Spaces

ROUND_ROBIN new_acct:   Add    Modify   Drop   Screen   Exit
Add a dbspace to the fragmentation strategy above the line with the highlight.

----- Page 1 of 1 ----- mydata@mydbserv ----------- Press CTRL-W for Help ----

Dbspace Name

  dbspace1 
 dbspace2

The ROUND_ROBIN menu has the following options.

Option: Purpose
Add: Displays dbspaces so that you can add a new dbspace to the round-robin fragment space assigned to the current table
Modify: Enables you to redefine the fragmentation strategy for the highlighted dbspace
Drop: Deletes the highlighted dbspace from the existing strategy, but does not delete the dbspace from the database server
Screen: Scrolls the screen to display more of the available dbspaces
Exit: Returns to the FRAGMENT menu

Use the arrow keys to highlight a dbspace from the list on the SELECT DBSPACE screen, as Figure 57 shows, and press RETURN. If you try to add a dbspace that is already part of another strategy, an error message appears.

When you return to the ROUND_ROBIN menu, the screen displays all dbspaces currently chosen for the strategy.

Expression Strategy Setup

To display the EXPRESSION menu, as Figure 60 shows, select the eXpression option on the FRAGMENT menu.

Figure 60. The EXPRESSION Menu for Defining Expression Fragmentation Strategy

EXPRESSION new_acct:    Add    Modify   Drop   Screen   Exit
Add a strategy definition.

----- Page 1 of 1 ----- mydata@mydbserv -------- Press CTRL-W for Help ----

Dbspace Name   Expression

  dbspace1       field1 <100
 dbspace2      field1 >=100 and field1 <200
 dbspace3      remainder

The EXPRESSION menu has the following options.

Option: Purpose
Add: Adds a new dbspace to those that will contain fragments of this table, according to the expression fragmentation strategy
Modify: Modifies the dbspace or expression associated with that dbspace
Drop: Deletes the highlighted dbspace and expression from the existing strategy, but does not delete the dbspace from the database server
Screen: Scrolls the screen to display more of the available dbspaces
Exit: Returns to the FRAGMENT menu

Important:

DB–Access does not perform data validation on the expression of the strategy.

The Add option on the EXPRESSION menu displays the SELECT DBSPACE screen, as Figure 57 shows. Use the arrow keys to highlight a dbspace from the list and press RETURN. If you try to add a dbspace that is already part of another strategy, an error message appears.

After you select a dbspace, the EDIT EXPRESSION menu appears, as Figure 61 shows. From this menu you can enter an expression that determines whether a particular record belongs in the highlighted dbspace.

Figure 61. The EDIT EXPRESSION Menu for Defining and Editing Expressions

EDIT EXPRESSION new_acct:   New    Modify   Use-editor   Exit
Enter a new expression which will determine where a record will be stored.

----- Page 1 of 1 ----- mydata@mydbserv --------- Press CTRL-W for Help ----

 Dbspace Name    Expression

  dbspace1        field1<100                                       
    
 dbspace2        field1>=100 and field1<200
 dbspace3        remainder

The EDIT EXPRESSION menu has the following options.

Option: Purpose
New: Displays the blank SQL editor screen so that you can enter a new expression
Modify: Displays the current expression on the SQL editor screen so that you can modify the expression
Use-editor: Displays the current expression in the system editor so that you can modify the expression
Exit: Returns to the EXPRESSION menu

After you exit the editor, DB–Access displays the CONFIRM CHANGES menu, as Figure 62 shows.

Figure 62. The CONFIRM CHANGES Menu

CONFIRM CHANGES dbspace1:    SAVE  DISCARD
Save changes.

----------------mydata@mydbserv----------------- Press CTRL-W for Help ------

 field1<100

To save the edits to the expression, press RETURN. To discard the edits to the expression, select DISCARD. You return to the EXPRESSION menu.

Altering Fragmentation for an Existing Table

If you are altering a table, you arrange or remove fragmentation with the ALTER FRAGMENT menu. To reach the ALTER FRAGMENT menu:

On the main menu, select Table.
On the TABLE menu, select Alter.
Select the Table_options option from the ALTER TABLE menu.
Select the Storage option from the TABLE_OPTIONS menu.
Select the Fragment option from the STORAGE menu.

Figure 63 shows how the ALTER FRAGMENT menu displays a table with a round-robin fragmentation strategy. Figure 64 shows how the ALTER FRAGMENT menu displays a table with an expression-based fragmentation strategy.

Figure 63. The ALTER FRAGMENT Menu for Round-Robin Fragmentation Strategy

ALTER FRAGMENT  -  new_acct:   Add   Drop  Screen  Init  aTtach  detaCh  Exit
Add one new dbspace to the end of the list.

----- Page 1 of 1 ---- newstores@mydbserv ------- Press CTRL-W for Help ---

Dbspace Name

 dbspace1
 dbspace2
 dbspace3

Figure 64. The ALTER FRAGMENT Menu for Expression Fragmentation Strategy

ALTER FRAGMENT - new_acct:  Add   Modify  Drop  Screen  Init  aTtach  detaCh  Exit
Add an expression to the fragmentation strategy above the line with the highlight.

----- Page 1 of 1 ----- newstores@mydbserv --------- Press CTRL-W for Help ----

Dbspace Name Expression

 dbspace1 field1 <100

 dbspace2 field1 >=100 and field1 <200

  dbspace3  remainder

The ALTER FRAGMENT menu has the following options.

Option	Purpose	Instructions
Add	Adds a dbspace to the round-robin scheme	Round-Robin Setup or Expression Strategy Setup, depending on the fragmentation type of the current table
Drop	Drops one dbspace from those used for fragments of the current table
Screen	Scrolls more dbspaces onto the screen	none
Init	Describes the fragmentation scheme (if any) of an existing table and enables you to change the strategy, attach fragments, detach fragments, or remove fragmentation	Fragmenting an Existing Table
aTtach	Attaches one or more tables to the current table, modifies or deletes a selected attachment	Attaching a Dbspace
detaCh	Detaches records from the current table from a specific dbspace and creates a new table with those records	Detaching a Dbspace
Exit	Returns to the TABLE_OPTIONS menu	none

Important:

You can execute only one menu option in an ALTER FRAGMENT menu, and it can be applied to the current strategy only once. For example, you can add only one dbspace to a round-robin strategy, and you cannot delete a dbspace during the same ALTER TABLE session.

Attaching a Dbspace

Select the aTtach option on the ALTER FRAGMENT expression strategy menu, and the ATTACH TABLES menu appears. Figure 65 shows the ATTACH TABLES menu for a table that has expression fragmentation. The round-robin version of this screen shows only the table name for each fragment.

Figure 65. The ATTACH TABLES Menu for Expression Fragmentation Strategy

ATTACH TABLES  new_acct:   Add    Modify   Drop   Screen   Exit
Add one new table to the fragmentation strategy.

---- Page 1 of 1 ---- newstores@mydbserv -------- Press CTRL-W for Help ----

Table Name            Expression         Position         Dbspace

 acct                 field1 <100        BEFORE           dbspace1

 cur_acct             field1 >=100

Select Add on the ATTACH TABLES menu to begin attaching a fragment. On the SELECT ATTACHING TABLE screen, highlight or type in the table name you want and press RETURN, as Figure 66 shows.

Figure 66. The SELECT ATTACHING TABLE Screen

SELECT ATTACHING TABLE >>
Select a table with the Arrow Keys, or enter the name, then press Return.

---- Page 1 of 1 ---- newstores@mydbserv -------- Press CTRL-W for Help ----

  acct 

 cur_acct

 myacct

If the table uses round-robin strategy, the resulting new fragment or fragments will be positioned at the end of the fragmentation strategy and the ALTER FRAGMENT menu returns.

If the table uses expression strategy, complete the following steps:

After you select a table, the EDIT EXPRESSION menu appears. See Figure 61.
When you exit the EDIT EXPRESSION menu, the ADD DEFINE ATTACH POSITION menu appears, as Figure 67 shows.
Figure 67. The ADD DEFINE ATTACH POSITION Menu
```
ADD DEFINE ATTACH POSITION acct:   Before  After None 
Define a position for an attaching fragment before a dbspace.

----- Page 1 of 1 ---- newstores@mydbserv -------- Press CTRL-W for Help ----
```
- Select Before to attach the new fragment before a dbspace that you select in the next step.
- Select After to attach the new fragment after the dbspace that you select in the next step.
- Select None to attach the new fragment in the default position.
If you select Before or After as the attach position, the SELECT DBSPACE screen appears, as Figure 68 shows, listing the dbspaces that the strategy encompasses prior to attaching the new one.
Figure 68. The SELECT DBSPACE Screen Listing Fragmented Dbspaces
```
SELECT DBSPACE >>
Select a dbspace with the Arrow Keys, or enter a name, then press Return.

----- Page 1 of 1 ---- newstores@mydbserv -------- Press CTRL-W for Help ---

  dbspace1 

 dbspace2

 dbspace3
```
Select the dbspace before or after which you want to attach the added fragment.

The ATTACH TABLES menu reappears, as Figure 65 shows, and shows values for the Position and Dbspace fields.

Detaching a Dbspace

The detaCh option from the ALTER FRAGMENT menu displays the DETACH DBSPACE screen, as Figure 69 shows.

Figure 69. The DETACH DBSPACE Screen for Removing Fragmentation

DETACH DBSPACE >>
Select a dbspace with the Arrow Keys, or enter a name, then press Return.

----- Page 1 of 1 ---- newstores@mydbserv ------- Press CTRL-W for Help ----

  dbspace1 

 dbspace2

 dbspace3

Select the dbspace from which you want the records copied into a new, unfragmented table. You can select a dbspace from the list or type in a dbspace name. If you enter an invalid dbspace name, an error message appears.

After you correctly enter a dbspace on the DETACH DBSPACE screen, the NEW TABLE screen appears, as Figure 70 shows.

Figure 70. The NEW TABLE Screen for Naming a Detached dbspace

NEW TABLE >>   
Enter the name you want assigned to the new table, then press Return.

---- Page 1 of 1 ---- newstores@mydbserv -------- Press CTRL-W for Help ----

Enter the name you want to assign to the new, unfragmented table. This table stores the records from the dbspace you previously selected through the DETACH DBSPACE screen. The display returns to the ALTER FRAGMENT menu.

Fragmenting an Existing Table

If a table has no fragmentation strategy when you select the Fragment option on the STORAGE menu, the ALTER FRAGMENT menu appears, as Figure 71 shows.

Figure 71. The ALTER FRAGMENT Menu for No Fragmentation Strategy

ALTER FRAGMENT  -  new_acct:    Init    Attach   Exit
Define a fragmentation strategy.

----- Page 1 of 1 ---- newstores@mydbserv ------- Press CTRL-W for Help ----

The ALTER FRAGMENT menu has the following options.

Option	Purpose	Instructions
Init	Provides the following options: fragments a previously unfragmented table removes fragmentation from a table changes the fragmentation strategy for a table	The ALTER FRAGMENT-INIT menu has the same options as the FRAGMENT menu under CREATE TABLE. For instructions, see Fragmenting a New Table.
Attach	Enables you to define a fragmentation strategy and select the tables to fragment with the new strategy. This option has the same effect as the following statement: `ALTER FRAGMENT ON TABLE table1... ATTACH table1, table2`	The ALTER FRAGMENT-ATTACH menu offers round-robin and expression fragmentation. For instructions on both types of strategy, see Fragmenting a New Table.
Exit	Returns to the TABLE_OPTIONS menu	none

Important:

You can perform only one operation during an ALTER FRAGMENT session.

Setting the Extent Size

When you create a table, you can specify how much initial disk space, or initial extent size, to reserve for the table. You can also specify the size of additional extents, or next extent spaces, the database server adds if the initial extent becomes full.

To specify an initial extent size, select the eXtent_size option on the TABLE_OPTIONS menu. DB–Access displays the Extent Size screen, as Figure 72 shows. After you set initial extent size, select the Next_size option from the TABLE_OPTIONS menu to display the Next Size screen, as Figure 73 shows.

Figure 72. The (Initial) Extent Size Screen

Extent Size >> 
Specify initial extent size in kilobytes.

----- Page 1 of 1 ----- mydata@mydbserv ----------- Press CTRL-W for Help ----

 Column Name                     Type            Length   Index Nulls

Figure 73. The Next (Extent) Size Screen

Next Size >> 
Specify next extent size in kilobytes.

----- Page 1 of 1 ----- mydata@mydbserv ---------- Press CTRL-W for Help ----

 Column Name                     Type            Length   Index Nulls

To select extent size on either screen, perform one of the following actions:

Press RETURN to accept the default size of 8 kilobytes.
Type a number (representing kilobyte units) and press RETURN.
The minimum extent size is 4 kilobytes.

Determining the Lock Mode

When you select the Lock_mode option on the TABLE_OPTIONS menu, DB–Access displays the LOCK_MODE menu, as Figure 74 shows.

Figure 74. The LOCK_MODE Menu

LOCK_MODE clients:    Page   Row  Exit
Locking is at page level. This is the default.

----- Page 1 of 1 ----- mydata@mydbserv ---------- Press CTRL-W for Help ----

 Column Name                     Type            Length   Index Nulls

The LOCK_MODE menu lets you choose the mode to use when the database locks the rows in a table. The LOCK_MODE menu has the following options.

Option: Purpose
Page: Locks the entire page on which a row resides
Row: Locks a selected row individually
Exit: Exits to the TABLE_OPTIONS menu

One row of a table is the smallest object that you can lock. A disk page contains one or more rows of a table. To determine if you will enhance performance by locking a disk page rather than individual rows on the page, see your IBM Informix: Performance Guide. Unless you specify row-level locking before you exit, DB–Access uses the default (Page).

Adding or Dropping Rowids

You can add or delete rowids only when you alter an existing table. To reach the ALTER ROWID menu:

Select Table_options from the ALTER TABLE menu.
Select Rowids from the TABLE OPTIONS menu.

The ALTER ROWID menu has the following options.

Option: Purpose
Add: Adds a column with rowids to the fragmented table
Drop: Discards the rowid column previously added
None: Cancels the selection you made on this screen so that you can exit without altering the table
Exit: Exits to the TABLE_OPTIONS menu

If you select Add or Drop, another menu prompts you to verify your selection. Select Yes to execute the Add or Drop; select No to cancel the Add or Drop.