Table 13.5. IBMDB2I
Options and
Variables
Name | Cmd-Line | Option file | System Var | Status Var | Var Scope | Dynamic |
---|---|---|---|---|---|---|
ibmdb2i_assume_exclusive_use | Yes | Yes | Yes | Global | Yes | |
ibmdb2i_async_enabled | Yes | Yes | Yes | Both | Yes | |
ibmdb2i_compat_opt_allow_zero_date_vals | Yes | Yes | Yes | Both | Yes | |
ibmdb2i_compat_opt_blob_cols | Yes | Yes | Yes | Both | Yes | |
ibmdb2i_compat_opt_time_as_duration | Yes | Yes | Yes | Both | Yes | |
ibmdb2i_compat_opt_year_as_int | Yes | Yes | Yes | Both | Yes | |
ibmdb2i_create_index_option | Yes | Yes | Yes | Both | Yes | |
ibmdb2i_lob_alloc_size | Yes | Yes | Yes | Both | Yes | |
ibmdb2i_max_read_buffer_size | Yes | Yes | Yes | Both | Yes | |
ibmdb2i_max_write_buffer_size | Yes | Yes | Yes | Both | Yes | |
ibmdb2i_propogate_default_col_vals | Yes | Yes | Yes | Both | Yes | |
ibmdb2i_rdb_name | Yes | Yes | Yes | Global | No | |
ibmdb2i_system_trace_level | Yes | Yes | Yes | Global | Yes | |
ibmdb2i_transaction_unsafe | Yes | Yes | Yes | Both | Yes |
Command-Line Format | ibmdb2i_assume_exclusive_use |
|
Config-File Format | ibmdb2i_assume_exclusive_use |
|
Variable Name | ibmdb2i_assume_exclusive_use |
|
Variable Scope | Global | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type (ibmsystemi) | boolean |
|
Default | off |
Specifies whether an external interface (such as DB2 for i)
may be altering the data accessible by the
IBMDB2I
engine. This is an advisory value
that may improve performance when correctly set to ON. When
the value is ON, IBMDB2I
may perform some
internal caching of table statistics. When the value is set to
OFF, IBMDB2I
must assume that the table
rows could be inserted and deleted without its direct
knowledge and must call DB2 to obtain accurate statistics
before each operation.
Default Value: OFF
Command-Line Format | ibmdb2i_assume_exclusive_use |
|
Config-File Format | ibmdb2i_assume_exclusive_use |
|
Variable Name | ibmdb2i_assume_exclusive_use |
|
Variable Scope | Global | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type (ibmsystemi) | boolean |
|
Default | off |
Specifies whether buffering between IBMDB2I
and the QSQSRVR jobs responsible for fetching row data should
be done in an asynchronous manner. Asynchronous reads are
enabled by default and provide optimal performance.Under
normal circumstances, this value will never need to be
modified.
Default Value: ON
Command-Line Format | ibmdb2i_assume_exclusive_use |
|
Config-File Format | ibmdb2i_assume_exclusive_use |
|
Variable Name | ibmdb2i_assume_exclusive_use |
|
Variable Scope | Global | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type (ibmsystemi) | boolean |
|
Default | off |
Controls whether additional indexes are created for use by traditional DB2 for i interfaces. When the value is 0, no additional indexes will be created. When the value is 1 and the index created on behalf of MySQL is ASCII-based, an additional index is created based on EBCDIC hexadecimal sorting. The additional index may be useful for traditional DB2 for i interfaces which expect indexes to use EBCDIC-based sort sequences.
Default Value: 0
ibmdb2i_compat_opt_time_as_duration
Command-Line Format | ibmdb2i_assume_exclusive_use |
|
Config-File Format | ibmdb2i_assume_exclusive_use |
|
Variable Name | ibmdb2i_assume_exclusive_use |
|
Variable Scope | Global | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type (ibmsystemi) | boolean |
|
Default | off |
Controls how MySQL TIME columns are mapped to DB2 data types
when creating or altering an IBMDB2I
table.
When the value is ON
, the column is mapped
to an INTEGER
type in DB2 and supports the
full range of values defined by MySQL for
TIME
types. When the value is
OFF
, the column is mapped to a DB2
TIME
type and supports values in the range
of '00:00:00' to '23:59:59'. This option is provided to allow
enhanced interoperability with DB2 for i interfaces.
Default Value: OFF
Command-Line Format | ibmdb2i_assume_exclusive_use |
|
Config-File Format | ibmdb2i_assume_exclusive_use |
|
Variable Name | ibmdb2i_assume_exclusive_use |
|
Variable Scope | Global | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type (ibmsystemi) | boolean |
|
Default | off |
Specifies what kind of debugging information is to be gathered
for QSQSRVR
jobs servicing MySQL
connections. Multiple sources of information may be specified
by summing the respective values. Changes to this option only
affect new connections. Valid values include
0
— No information (Default)
2
— STRDBMON
4
— STRDBG
8
— DSPJOBLOG
16
— STRTRC
32
— PRTSQLINF
The most useful sources of information are
DSPJOBLOG
, which will capture the job log
for each QSQSRVR
job in a spoolfile, and
STRDBG
, which will increase the diagnostic
information in each job log.
Default Value: 0
ibmdb2i_compat_opt_allow_zero_date_vals
Command-Line Format | ibmdb2i_compat_opt_allow_zero_date_vals |
|
Config-File Format | ibmdb2i_compat_opt_allow_zero_date_vals |
|
Variable Name | ibmdb2i_compat_opt_allow_zero_date_vals |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type (ibmsystemi) | boolean |
|
Default | 0 |
Specifies whether the storage engine should allow the
0000-00-00
date in
DATETIME
, TIMESTAMP
and
DATE
columns. When the option is 0,
attempts to insert a row containing this zero date into an
IBMDB2I
table will fail. As well, a warning
will be generated when creating a column with this zero value
as the default value. When this option is 1, the zero value
will be subsituted with 0001-01-01
when
stored in DB2, and a 0001-01-01
value will
be translated to 0000-00-00
when read from
DB2. Similarly, when a column with a default zero value is
created, the DB2 default value will be '0001-01-01'. Users
must be aware that, when this option is 1, all values of
0001-01-01
in DB2 will be interpreted as
0000-00-00
. This option is primarily added
for compatibility with applications which rely on the zero
date.
Default Value: 0
ibmdb2i_propagate_default_col_vals
Command-Line Format | ibmdb2i_propogate_default_col_vals |
|
Config-File Format | ibmdb2i_propogate_default_col_vals |
|
Variable Name | ibmdb2i_propogate_default_col_vals |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type (ibmsystemi) | boolean |
|
Default | on |
Specifies whether DEFAULT
value associated
with each column should be propagated to the DB2 definition of
the table when a table is created or altered. The default
value is ON. This ensures that rows inserted from a standard
DB2 interface will use the same default values as when
inserted from MySQL.
Default Value: ON
ibmdb2i_compat_opt_year_as_int
Command-Line Format | ibmdb2i_compat_opt_year_as_int |
|
Config-File Format | ibmdb2i_compat_opt_year_as_int |
|
Variable Name | ibmdb2i_compat_opt_year_as_int |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type (ibmsystemi) | boolean |
|
Default | 0 |
Controls how YEAR columns are stored in DB2. The default is 0
and causes YEAR
columns to be created as
CHAR(4) CCSID 1208
columns in DB2. Setting
this option to 1 causes the YEAR columns to be created as
SMALLINT
columns. This provides a slight
performance increase and enables indexes that combine a
YEAR
column with a character column.
Default Value: 0
Command-Line Format | ibmdb2i_lob_alloc_size |
|
Config-File Format | ibmdb2i_lob_alloc_size |
|
Variable Name | ibmdb2i_lob_alloc_size |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type (ibmsystemi) | numeric |
|
Default | 2MB |
Controls how much space is allocated by default for reading
data from a BLOB
or TEXT
field. If an application consistently uses
BLOB
or TEXT
fields that
contain more than 2 MB of data, read performance may be
improved if this value is increased. Conversely, an
application which uses smaller BLOB
or
TEXT
fields may find that the MySQL memory
footprint is reduced if a smaller value is specified for this
option.
Default Value: 2 MB
Command-Line Format | ibmdb2i_compat_opt_blob_cols |
|
Config-File Format | ibmdb2i_compat_opt_blob_cols |
|
Variable Name | ibmdb2i_compat_opt_blob_cols |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type (ibmsystemi) | numeric |
|
Default | 0 |
Specifies how MySQL TEXT
and
BLOB
columns larger than 255 characters are
mapped when creating IBMDB2I
tables. When
the value is 0, TEXT
columns are mapped to
CLOB
or DBCLOB
columns
for DB2 for i. This allows the column to contain the maximum
size documented for TEXT
columns (64K
characters), but the column can not be included in an index.
When the value is 1, TEXT
columns are
mapped to LONG
VARCHAR
/VARGRAPHIC
columns, and
BLOB
columns are mapped to LONG
VARBINARY
. This permits indexes to be created over
the column, but it reduces the amount of storage available to
the column below the documented maximum for
TEXT
columns. This option was provided to
enable applications which relied on the ability to create
prefix indexes over TEXT
columns.
Default Value: 0
Command-Line Format | ibmdb2i_max_read_buffer_size |
|
Config-File Format | ibmdb2i_max_read_buffer_size |
|
Variable Name | ibmdb2i_max_read_buffer_size |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type (ibmsystemi) | numeric |
|
Default | 1MB |
Controls the maximum amount of memory allocated for buffering
row data when doing reads from an IBMDB2I
table. This buffering is done independently of any row
buffering done within MySQL proper. Setting this value too low
may reduce read performance, while setting it too high may
increase memory usage and may also reduce read performance.
Default Value: 1 MB
Command-Line Format | ibmdb2i_max_write_buffer_size |
|
Config-File Format | ibmdb2i_max_write_buffer_size |
|
Variable Name | ibmdb2i_max_write_buffer_size |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type (ibmsystemi) | numeric |
|
Default | 8MB |
Controls the maximum amount of memory allocated for buffering
row data when inserting multiple rows into an
IBMDB2I
table. This buffering is done
independently of any row buffering done within MySQL proper.
Setting this value too low may reduce write performance, while
setting it too high may increase memory usage.
Default Value: 8 MB
Command-Line Format | ibmdb2i_rdb_name |
|
Config-File Format | ibmdb2i_rdb_name |
|
Variable Name | ibmdb2i_rdb_name |
|
Variable Scope | Global | |
Dynamic Variable | No | |
Permitted Values | ||
Type (ibmsystemi) | string |
|
Default |
|
The name of a local DB2 for i relational database that will
act as a container for all IBMDB2I
tables.
This allows an independent auxiliary storage pool (IASP) to be
selected for usage. If no value is specified, the system
database (*SYSBAS) is used.
Default Value: <blank>
Command-Line Format | ibmdb2i_transaction_unsafe |
|
Config-File Format | ibmdb2i_transaction_unsafe |
|
Variable Name | ibmdb2i_transaction_unsafe |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type (ibmsystemi) | boolean |
|
Default | off |
Controls whether IBMDB2I
honors the
transactional commands and isolation levels specified for
MySQL. If the value is OFF, transactions are fully supported.
If the value is ON, transactional behavior is not implemented.
This may provide a moderate performance increase for
applications that do not rely on transactional guarantees.
Default Value: OFF
The values specified for
ibmdb2i_create_index_option
,
ibmdb2i_create_time_columns_as_tod
,
ibmdb2i_map_blob_to_varchar
,
ibmdb2i_compat_opt_allow_zero_date_vals
,
ibmdb2i_propagate_default_col_vals
, and
ibmdb2i_propagate_default_col_val
will be applied
whenever an IBMDB2I
table is created. Tables
are implicitly destroyed and re-created when an
offline
ALTER TABLE
is
performed on an IBMDB2I
table. Therefore it is
highly recommended that the values of these variables be
consistent across the lifetime of a table to prevent an
ALTER TABLE
from running under a different set
of options than were used to originally create the table. If this
recommendation is not followed, the ALTER TABLE
may fail upon encountering incompatible data when re-creating the
table.
User Comments
Add your own comment.