CREATE SERVER statement - Examples of the ODBC wrapper

Use the CREATE SERVER statement to register server definitions for the ODBC wrapper. This topic provides a complete example with the required parameters, and an example with additional server options.

The following example shows you how to register a server definition for a MySQL data source by issuing the CREATE SERVER statement:

CREATE SERVER mysql_server TYPE mysql 
       VERSION 4.0 WRAPPER wrapper_name 
        OPTIONS (NODE 'odbc_node', DBNAME 'venice')

CREATE SERVER mysql_server TYPE mysql 
       VERSION 4.0 WRAPPER wrapper_name 
        OPTIONS (HOST 'mysql.cn.ibm.com’, DBNAME 'venice')

mysql_server: A name that you assign to the ODBC data source server. Duplicate server definition names are not allowed.
TYPE mysql: Specifies the type of data source server to which you are configuring access. This parameter is optional for DSN connection but required for DSN-less connection.
VERSION 4.0: The version of the ODBC data source that you want to access. This parameter is optional.
WRAPPER wrapper_name: The wrapper name that you specified in the CREATE WRAPPER statement.
NODE 'odbc_node': The name of the node (the system DSN name) that was assigned to the ODBC data source when the DSN was defined. On federated servers that run Windows, this value must be the name of a system DSN in the ODBC Data Source Administrator window. On federated servers that run UNIX, the name of the node is the DSN defined in the ODBC configuration file. The ODBC configuration file is usually called odbc.ini.
Although the name of the node is specified as an option in the CREATE SERVER statement, it is required for ODBC data sources.
HOST 'mysql.cn.ibm.com': Specifies the address of the data source server. HOST is required if NODE is not defined.
DBNAME 'venice': Optional. The name of the ODBC data source that you want to access.

Server options

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

Some ODBC data sources (for example, MySQL) cannot process quotation marks around table names and column names in SQL statements. To access these data sources, you must include the following server options in the CREATE SERVER statement:

DB2_TABLE_QUOTE_CHAR ' ` '
DB2_ID_QUOTE_CHAR ' ` '
DB2_AUTHID_QUOTE_CHAR ' ` '

The ` character is the delimiter for identifiers such as schema names, table names, and column names.

For example:

CREATE SERVER mysql_server TYPE mysql 
       VERSION 4.0 WRAPPER wrapper_name 
       OPTIONS (NODE 'mysql_node', DB2_TABLE_QUOTE_CHAR '`', 
       DB2_ID_QUOTE_CHAR '`', DB2_AUTHID_QUOTE_CHAR '`')