Administration Guide

Creating a Server

In a federated database, create servers to define data sources to DB2 and describe their characteristics: name, wrapper, type, version, location, and options. This information is used to map nicknames to specific data management systems and to provide information to the DB2 optimizer. Server information is located in the SYSCAT.SERVERS and SYSCAT.SERVEROPTIONS catalog views.
Note: In this section, servers represent data sources, not DRDA servers or DB2 servers. To access other data sources (for example, Oracle), DB2 Connect is required.

You can create a server object only if a wrapper has been created.

You must have SYSADM or DBADM authority at the federated database to use this statement.

You can create user mappings to manage differences in authentication processing between DB2 and data source servers. User mappings are discussed in detail in User Mappings.

When a server is dropped, all objects dependent on that server are dropped (such as user mappings, nicknames, function mappings, type mappings, and plans).

Provide server options when creating a server. These options contain necessary details about the server (such as the node name). Server options can also set specific performance and security values.

You can create servers from the Control Center or the command line processor.

To create a wrapper using the Control Center:

Expand the object tree until you see the Servers folder under the Federated Database Objects folder.
Right-click the Servers folder, and select Create wrapper from the pop-up menu.
Complete the information, and click Ok.

To create a wrapper using the command line, enter:

   CREATE SERVER <server_name> TYPE <server_type>
      VERSION <server_version> WRAPPER <wrapper_name>
      OPTIONS (<server_option_name> <string_constant>)

The following sample SQL statement creates the Oracle server ORA8:

   CREATE SERVER ORA8 TYPE ORACLE VERSION 8 WRAPPER ORACLE8 OPTIONS
   (NODE 'ONODE')

The following sample SQL statement creates the DB2 server DB2TEST:

   CREATE SERVER DB2TEST TYPE DB2 VERSION 6.1 WRAPPER DB2UDB OPTIONS
   (NODE 'DB2TEST', DBNAME 'TEST1')

The definition of NODE, in SERVER SQL statements, varies depending on the data source. If the data source is a DB2 DBMS, the value refers to an instance of DB2 that has one or more databases. In the previous example, note that the DBNAME option specifies the database name. If the data source is a DB2 for OS/390 DBMS, the value refers to the LOCATION name of the DB2 for OS/390 system. If the data source is an Oracle DBMS, the DBNAME option is not needed because an Oracle instance contains only one database.

For additional details about the CREATE SERVER statement syntax, refer to the SQL Reference. For additional details about using the CREATE SERVER statement, refer to the Installation and Configuration Supplement.

Using Server Options to Help Define Data Sources and Facilitate Authentication Processing

You can set variables called server options to values that affect how a federated server accesses data sources. This section:

Explains the purpose of server options
Describes what SQL statements you use to specify server options
Shows the server options and their settings

Purposes of Server Options

In general, you use server options to:

Supply and update information about data sources. A server reference includes both basic information about a data source--for example, its name--and information that can change over time. Some of the changeable information is conveyed by values assigned to server options. For example, the value assigned to the cpu_ratio option indicates whether the data source's CPU is faster or slower than the DB2 system CPU. If the DB2 system gets one or more processor upgrades, this value should change.
Facilitate authentication. You can set some server options to ensure that user IDs and passwords are sent to the data source in the proper case. For example, you can set the fold_id option so that before the federated server sends a user ID to a data source, the federated server transforms the name to the case (upper or lower) that the data source requires. Alternatively, if you define the user ID to the federated server in the required case, you can set the fold_id option to prevent the server from trying to change the case and consuming overhead in the process.
Optimize queries. Some server options and their values facilitate optimization. For example: in the CREATE SERVER statement, you can specify certain performance statistics as option values. Specifically, you can set the cpu_ratio option to a value that indicates the relative speeds of the data source's and federated server's CPUs. And you can set the io_ratio option to a value that indicates the relative rates of the I/O devices of the data source and federated server. When you run CREATE SERVER, these statistics are added to the catalog view SYSCAT.SERVEROPTIONS, and the optimizer uses them in developing its access plan for the data source. If a statistic changes (as might happen, for instance, if the data source CPU is upgraded), you can use the ALTER SERVER statement to update SYSCAT.SERVEROPTIONS with this change. The optimizer then uses your update in developing its next access plan for the data source.

SQL for Server Options

There are three SQL statements in which you can assign values to server options: CREATE SERVER, ALTER SERVER, and SET SERVER OPTION.

Use the CREATE SERVER statement to set an option to a value that persists indefinitely over time for multiple connections to a data source. With this statement, you can set an option to a value other than the default or, if an option has no default value, you can set it to an initial value.

Use the ALTER SERVER statement if, after setting a server option to a value with the CREATE SERVER statement, you want to set it to a different value that persists over multiple connections.

Use the SET SERVER OPTION statement to change server option values temporarily for the duration of a single connection to a database. SET SERVER OPTION statements must be issued first within the first unit of work following the connection to the data source.

For example, to temporarily enable the use of plan hints for the Oracle server ORASEB1, issue the statement:

   SET SERVER OPTION plan_hints TO 'Y' FOR SERVER ORASEB1

Server Options and Their Settings

See the "Server Options Affecting Federated Database Queries" section at the end of the "Environmental Considerations" chapter which describes the server options and the values that you can set them to. Unless otherwise stated, all server option values must be enclosed in single quotes.

Using Pass-through Sessions with Servers

Pass-through sessions let applications communicate directly with a server using the server's native client access method and native SQL dialect.

Pass-through sessions are useful when:

Applications must create objects at the data source or perform INSERT, UPDATE, or DELETE operations
DB2 does not support a unique data source operation

When referencing objects in a pass-through session, use the true name of the object (not the nickname).

Use the SET PASSTHRU statement to start a pass-through session and access a server directly. This statement must be issued dynamically. An example of this statement is:

   SET PASSTHRU BACKEND

which opens a pass-through session to the data source BACKEND.

For more information on SET PASSTHRU and SQL processing in pass-through sessions, see the SQL Reference.

[ Top of Page | Previous Page | Next Page ]