MySQL 5.1 Reference Manual  /  ...  /  Configuration Options

14.7.2 Configuration Options

Table 14.6 IBMDB2I Storage Engine Features

NameCmd-LineOption FileSystem VarStatus VarVar ScopeDynamic
ibmdb2i_assume_exclusive_useYesYesYes GlobalYes
ibmdb2i_async_enabledYesYesYes BothYes
ibmdb2i_compat_opt_allow_zero_date_valsYesYesYes BothYes
ibmdb2i_compat_opt_blob_colsYesYesYes BothYes
ibmdb2i_compat_opt_time_as_durationYesYesYes BothYes
ibmdb2i_compat_opt_year_as_intYesYesYes BothYes
ibmdb2i_create_index_optionYesYesYes BothYes
ibmdb2i_lob_alloc_sizeYesYesYes BothYes
ibmdb2i_max_read_buffer_sizeYesYesYes BothYes
ibmdb2i_max_write_buffer_sizeYesYesYes BothYes
ibmdb2i_propogate_default_col_valsYesYesYes BothYes
ibmdb2i_rdb_nameYesYesYes GlobalNo
ibmdb2i_system_trace_levelYesYesYes GlobalYes
ibmdb2i_transaction_unsafeYesYesYes BothYes

  • ibmdb2i_assume_exclusive_use

    Introduced5.1.33
    Command-Line Formatibmdb2i_assume_exclusive_use
    System VariableNameibmdb2i_assume_exclusive_use
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted Values (IBM System i)Typeboolean
    Defaultoff

    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

  • ibmdb2i_async_enabled

    Introduced5.1.33
    Command-Line Formatibmdb2i_async_enabled
    System VariableNameibmdb2i_async_enabled
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted Values (IBM System i)Typeboolean
    Defaulton

    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

  • ibmdb2i_create_index_option

    Introduced5.1.33
    Command-Line Formatibmdb2i_create_index_option
    System VariableNameibmdb2i_create_index_option
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted Values (IBM System i)Typenumeric
    Default0

    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

    Introduced5.1.33
    Command-Line Formatibmdb2i_compat_opt_time_as_duration
    System VariableNameibmdb2i_compat_opt_time_as_duration
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted Values (IBM System i)Typeboolean
    Defaultoff

    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 provide enhanced interoperability with DB2 for i interfaces.

    Default Value: OFF

  • ibmdb2i_system_trace_level

    Introduced5.1.33
    Command-Line Formatibmdb2i_system_trace_level
    System VariableNameibmdb2i_system_trace_level
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted Values (IBM System i)Typeboolean
    Default0

    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

    Introduced5.1.33
    Command-Line Formatibmdb2i_compat_opt_allow_zero_date_vals
    System VariableNameibmdb2i_compat_opt_allow_zero_date_vals
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted Values (IBM System i)Typeboolean
    Default0

    Specifies whether the storage engine should permit 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 substituted 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

    Introduced5.1.33
    Command-Line Formatibmdb2i_propogate_default_col_vals
    System VariableNameibmdb2i_propogate_default_col_vals
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted Values (IBM System i)Typeboolean
    Defaulton

    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

    Introduced5.1.33
    Command-Line Formatibmdb2i_compat_opt_year_as_int
    System VariableNameibmdb2i_compat_opt_year_as_int
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted Values (IBM System i)Typeboolean
    Default0

    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

  • ibmdb2i_lob_alloc_size

    Introduced5.1.33
    Command-Line Formatibmdb2i_lob_alloc_size
    System VariableNameibmdb2i_lob_alloc_size
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted Values (IBM System i)Typenumeric
    Default2MB

    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

  • ibmdb2i_compat_opt_blob_cols

    Introduced5.1.33
    Command-Line Formatibmdb2i_compat_opt_blob_cols
    System VariableNameibmdb2i_compat_opt_blob_cols
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted Values (IBM System i)Typenumeric
    Default0

    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 enables 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

  • ibmdb2i_max_read_buffer_size

    Introduced5.1.33
    Command-Line Formatibmdb2i_max_read_buffer_size
    System VariableNameibmdb2i_max_read_buffer_size
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted Values (IBM System i)Typenumeric
    Default1MB

    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

  • ibmdb2i_max_write_buffer_size

    Introduced5.1.33
    Command-Line Formatibmdb2i_max_write_buffer_size
    System VariableNameibmdb2i_max_write_buffer_size
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted Values (IBM System i)Typenumeric
    Default8MB

    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

  • ibmdb2i_rdb_name

    Introduced5.1.33
    Command-Line Formatibmdb2i_rdb_name
    System VariableNameibmdb2i_rdb_name
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted Values (IBM System i)Typestring
    Default

    The name of a local DB2 for i relational database that will act as a container for all IBMDB2I tables. This enables 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>

  • ibmdb2i_transaction_unsafe

    Introduced5.1.33
    Command-Line Formatibmdb2i_transaction_unsafe
    System VariableNameibmdb2i_transaction_unsafe
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted Values (IBM System i)Typeboolean
    Defaultoff

    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
Sign Up Login You must be logged in to post a comment.