CREATE SERVER statement - Examples for the Informix wrapper

Use the CREATE SERVER statement to register server definitions for the Informix® wrapper. This topic includes a complete example with the required parameters, and examples with additional server options.

Complete example

The following example shows you how to register a server definition for an Informix wrapper by issuing the CREATE SERVER statement:

CREATE SERVER asia TYPE informix VERSION 10 WRAPPER INFORMIX 
       OPTIONS (NODE 'abc', DBNAME 'sales');

asia: A name that you assign to the Informix database server. Duplicate server definition names are not allowed.
TYPE informix: Specifies the type of data source server to which you are configuring access. For the Informix wrapper, the server type must be informix.
VERSION 10: The version of the Informix database server that you want to access.
WRAPPER INFORMIX: The wrapper name that you specified in the CREATE WRAPPER statement.
NODE 'abc': The name of the node where the Informix database server resides. Obtain the node name from the sqlhosts file. This value is case sensitive.
Although the name of the node is specified as an option in the CREATE SERVER statement, it is required for Informix data sources.
DBNAME 'sales': The name of the Informix database that you want to access. This value is case sensitive.
Although the name of the database is specified as an option in the CREATE SERVER statement, it is required for Informix data sources.

Additional server options

When you create a server definition, you can specify additional server options in the CREATE SERVER statement. The server options can be general server options and Informix-specific server options.

FOLD_ID and FOLD_PW server options

When the federated server connects to a data source, the federated server tries to connect using all possible combinations of uppercase and lowercase for the user ID and password, as well as the current case. The federated server might make up to nine connect attempts before successfully connecting to the data source server. These attempts can slow down connect times and might result in the user ID being locked out. You can prevent lock outs by specifying values for the FOLD_ID and FOLD_PW server options. You can set the FOLD_ID and FOLD_PW server options to 'N' (do not fold the user ID or password).

If you set the FOLD_ID and FOLD_PW server options to 'N', you must specify the user ID and password in the correct case. The advantage to setting these server options to 'N' is that when an invalid user ID or password is specified, the wrapper will not keep trying the various uppercase and lowercase combinations. These two server options can reduce the chance of exceeding the maximum number of failed login attempts and the ID getting locked out.

The following example shows an Informix server definition with these server options:

CREATE SERVER asia TYPE informix VERSION 10 WRAPPER INFORMIX 
       OPTIONS (NODE 'abc', DBNAME 'sales', FOLD_ID 'N', FOLD_PW 'N');

IUD_APP_SVPT_ENFORCE server option example

The IUD_APP_SVPT_ENFORCE option specifies whether the federated server should enforce detecting or building of application savepoint statements. Informix does not support application savepoint statements. When set to 'N', the federated server will not roll back transactions when an error is encountered. Your application must handle the error recovery.

The IUD_APP_SVPT_ENFORCE server option must be set to 'N' to enable replication to or from Informix data sources. The following example shows an Informix server definition with the IUD_APP_SVPT_ENFORCE server option.

CREATE SERVER asia TYPE informix VERSION 10 WRAPPER INFORMIX 
       OPTIONS (NODE 'abc', DBNAME 'sales', IUD_APP_SVPT_ENFORCE 'N');

INFORMIX_DB_LOCALE and INFORMIX_CLIENT_LOCALE options

The INFORMIX_DB_LOCALE option sets the database locale environment variable (DB_LOCALE) that is used for the connection between the federated server and the data source server. If the INFORMIX_DB_LOCALE option is not specified, the Informix DB_LOCALE environment variable is set to the value that is specified in the db2dj.ini file. If the db2dj.ini file does not specify the DB_LOCALE environment variable, the Informix DB_LOCALE environment variable is not set A valid value is any valid Informix locale. This option is optional. The default setting is None.

The INFORMIX_CLIENT_LOCALE option sets the client locale environment variable (CLIENT_LOCALE) to be used for the connection between the federated server and the data source server. If the INFORMIX_CLIENT_LOCALE option is not specified, the Informix CLIENT_LOCALE environment variable is set to the value specified in the db2dj.ini file. If db2dj.ini does not specify CLIENT_LOCALE, then the Informix CLIENT_LOCALE environment variable is set to the Informix locale that most closely matches the code page and territory of the federated database. A valid value is any valid Informix locale. This option is optional. The default setting is None.

The following example shows an Informix server definition with the INFORMIX_DB_LOCALE and INFORMIX_CLIENT_LOCALE options.

CREATE SERVER asia TYPE informix VERSION 10 WRAPPER INFORMIX 
       OPTIONS (NODE 'abc', DBNAME 'sales', INFORMIX_DB_LOCALE 'en_us.8859-1', 
      INFORMIX_CLIENT_LOCALE 'en_us.CP1252');