Alternative to Full Logging

Instead of full logging, you can turn off logging when you load the smart large object initially and then turn logging back on once the object is loaded.

Use the NO LOG option to turn off logging. If you use NO LOG, you can restore the smart-large-object metadata later to a state in which no structural inconsistencies exist. In most cases, no transaction inconsistencies will exist either, but that result is not guaranteed.

The following statement creates the greek table. Data values for the table are fragmented into the dbs1 and dbs2 dbspaces. The PUT clause assigns the smart-large-object data in the gamma and delta columns to the sb1 and sb2 sbspaces, respectively. The TEXT data values in the eps column are assigned to the blb1 blobspace.

EXTENT SIZE Options

The EXTENT SIZE options can define the size of extents assigned to the table.

EXTENT SIZE Options:
 
|--+------------------------------+----------------------------->
   '-EXTENT SIZE--first_kilobytes-'
 
>--+---------------------------+--------------------------------|
   '-NEXT SIZE--next_kilobytes-'

Element	Description	Restrictions	Syntax
first_kilobytes	Length in kilobytes of the first extent for the table; default is 16.	Must return a positive number; maximum is the chunk size	Expression, p.Expression
next_kilobytes	Length in kilobytes of each subsequent extent; default is 16.	Must return a positive number; maximum is the chunk size	Expression, p.Expression

The minimum length of first_kilobytes (and of next_kilobytes) is four times the disk-page size on your system. For example, if you have a 2-kilobyte page system, the minimum length is 8 kilobytes.

The next example specifies a first extent of 20 kilobytes and allows the rest of the extents to use the default size:

CREATE TABLE emp_info
   (
   f_name     CHAR(20),
   l_name     CHAR(20),
   position   CHAR(20),
   start_date DATETIME YEAR TO DAY,
   comments   VARCHAR(255)
   )
EXTENT SIZE 20

If you need to revise the extent sizes of a table, you can modify the extent and next-extent sizes in the generated schema files of an unloaded table. For example, to make a database more efficient, you might unload a table, modify the extent sizes in the schema files, and then create and load a new table. For information about how to optimize extents, see your IBM Informix Administrator's Guide.

USING Access-Method Clause (IDS)

The USING Access Method clause can specify an access method.

USING Access-Method Clause:
 
                            (1)
|--USING--| Specific Name |------------------------------------->
 
   .-,--------------------------------------------------.
   V                                                    |
>----+------------------------------------------------+-+-------|
     |    .-,--------------.                          |
     |    V                |                          |
     '-(----config_keyword-+--+------------------+--)-'
                              '- ='config_value'-'

Notes:

See page Specific Name

Element	Description	Restrictions	Syntax
config_keyword	Configuration keyword associated with the specified access method	No more than 18 bytes. The access method must exist.	Literal keyword
config_value	Value of the specified configuration keyword	No more than 236 bytes. Must be defined by the access method.	Quoted String, p. Quoted String

A primary-access method is a set of routines to perform DDL and DML operations, such as create, drop, insert, delete, update, and scan, to make a table available to the database server. Dynamic Server provides a built-in primary-access method.

You store and manage a virtual table either outside of the database server in an extspace or inside the database server in an sbspace. (See Storage Options.) You can access a virtual table with SQL statements. Access to a virtual table requires a user-defined primary-access method.

DataBlade modules can provide other primary-access methods to access virtual tables. When you access a virtual table, the database server calls the routines associated with that access method rather than the built-in table routines. For more information on these other primary-access methods, refer to your access-method documentation.

You can retrieve a list of configuration values for an access method from a table descriptor (mi_am_table_desc) using the MI_TAB_AMPARAM macro. Not all keywords require configuration values.

The access method must already exist. For example, if an access method called textfile exists, you can specify it with the following syntax:

CREATE TABLE mybook
   (... )
   IN myextspace
   USING textfile (DELIMITER=':')

LOCK MODE Options

Use the LOCK MODE options to specify the locking granularity of the table.

LOCK MODE Options:
 
|--LOCK MODE--+-PAGE---------+----------------------------------|
              +-ROW----------+
              |  (1)         |
              '--------TABLE-'

Notes:

Extended Parallel Server only

You can subsequently change the lock mode of the table with the ALTER TABLE statement.

Granularity	Effect
PAGE	Obtains and releases one lock on a whole page of rows This is the default locking granularity. Page-level locking is especially useful when you know that the rows are grouped into pages in the same order that you are using to process all the rows. For example, if you are processing the contents of a table in the same order as its cluster index, page locking is appropriate.
ROW	Obtains and releases one lock per row Row-level locking provides the highest level of concurrency. If you are using many rows at one time, however, the lock-management overhead can become significant. You might also exceed the maximum number of locks available, depending on the configuration of your database server.
TABLE (XPS)	Places a lock on the entire table This type of lock reduces update concurrency compared to row and page locks. A table lock reduces the lock-management overhead for the table With table locking, multiple read-only transactions can still access the table.

The following table describes the locking-granularity options available.