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 Enterprise Edition data source by issuing the CREATE SERVER statement:

Example for DSN mode connection:

CREATE SERVER mysql_server TYPE mysql 
       VERSION 5.0 WRAPPER odbcwrapper  
        OPTIONS (NODE 'mysql_node', DBNAME 'venice')

Example for DSN-less mode connection:

CREATE SERVER oracle_server TYPE oracle_odbc 
       VERSION 5.0 WRAPPER odbcwrapper  
        OPTIONS (HOST 'oracle.cn.ibm.com’, PORT '1521' CODEPAGE '1208' SERVICE_NAME 'orclpdb.test.ibm.com')

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 '`')

When remote server is SQL Server, you can enable the data block join using the following server options in the CREATE SERVER statement:tions.

ENABLE_ARRAY_BIND ' Y'

This option can optimize data binding process to improve query efficiency in some cases.

For example:

CREATE SERVER mysql_server TYPE mysql_odbc 
       VERSION 2012 WRAPPER wrapper_name 
       OPTIONS (HOST 'mysql_host', PORT 'mysql_port', 
       PASSWORD 'Y', PUSHDOWN 'Y', ENABLE_ARRAY_BIND 'Y');

Restriction of ENABLE_ARRAY_BIND:

Query is NOT for cursor update/delete
No LOB is involved in input or output columns
Supports only one NLJN for RETURN. Also, the output columns of RETURN are all from NLJN inner table. In all other cases, the data block join will be invalid even if the option is ‘Y’.