Configuring access to BigSQL data sources

Answer

To configure a federated server to access BigSQL data sources, you must provide the federated server with information about the data sources and objects that you want to access.

Before you begin

• Verify the set up of the federated server.

Restrictions:

• DDL and Transparent DDL are not supported for HADOOP/HBASE TABLE.
• IUD is not supported for HADOOP/HBASE TABLE.
• ANALYZE command is not supported.
• BigSQL data types: array and struct are not supported.
• COMMIT and ROLLBACK are not supported for HADOOP TABLE.

Procedure

1. Catalog a node entry.
You must catalog a node entry that specifies the protocol that the federated server uses to connect to the BigSQL data source.

• Issue the CATALOG TCPIP NODE command.

For example:

CATALOG TCPIP NODE bigsql_node REMOTE system42 SERVER bigsqltcp42

where:

• bigsql_node is the name that you assign to the node.
• system42 is the host name of the system where the data source resides.
• bigsqltcp42 is the service name or primary port number of the server database manager instance.

2. Catalog the remote BigSQL database.
To identify which database the federated server connects to, you must catalog the remote BigSQL database in the federated server system database directory.

• Issue the CATALOG DATABASE command.

For example:

CATALOG DATABASE BIGSQL AS bigsql_aliasAT NODE bigsql_node AUTHENTICATION SERVER
where:

• bigsql_alias is the alias for the remote bigsql database being cataloged. If you do not specify an alias, the database manager uses the name of the remote database as the alias.
• bigsql_node is the name of the node that you previously cataloged
• AUTHENTICATION SERVER specifies that authentication takes place on the BigSQL data source node.

3. Register the DRDA wrapper.
You must register a wrapper to access BigSQL data sources. The federated server uses the wrapper to communicate with and retrieve data from the data sources. A wrapper is implemented as a set of library files.

• Issue the CREATE WRAPPER statement

The default wrapper name for the BigSQL is DRDA®. For example:

CREATE WRAPPER DRDA

When you use the default name to register the wrapper, you do not need to specify the library name because the federated server automatically uses the default library name that is associated with the wrapper. If the wrapper name conflicts with an existing wrapper name in the federated database, you can replace the default wrapper name with a name that you choose. However, if you do so, you must include the LIBRARY parameter in the CREATE WRAPPER statement.
For example, to register a wrapper with the name bigsql_wrapper on a federated server that uses the AIX® operating system, issue this statement:

CREATE WRAPPER bigsql_wrapper LIBRARY 'libdb2drda.a'

The default library name is specific to the operating system of the federated server. For more information, see DB2 wrapper library files.
When you install the federated server, wrapper library files are added to the default directory path.

4. Register the server definitions for a BigSQL data source.
The federated server requires authorization and password information to connect to each server. Because this authorization and password information is not stored in the global catalog, you must include it in the each
server definition.

• Issue the CREATE SERVER statement.

When you register the server, you must include certain required server options. This example includes only the server options that are required to register a BigSQL server:

CREATE SERVER server_definition_name TYPE bigsql VERSION 4 WRAPPER wrapper_nameAUTHORIZATION "userid" PASSWORD "password"OPTIONS (DBNAME 'database_name')

where:

• server_definition_name is the name that you assign to the server. Duplicate server definition names are not allowed. This is a required server option.
• TYPE bigsql specifies the type of data source server to which you are configuring access.
• VERSION 4 specifies the version of the BigSQL database server that you want to access. You can specify version 3 or 4.
• wrapper_name is the name that you specified in the CREATE WRAPPER statement.
• userid is the authorization ID at the data source. This ID must have BINDADD authority at the data source. This value is case sensitive.
• password is the password that is associated with the authorization ID at the data source. This value is case sensitive.
• database_name is the alias for the BigSQL database that you want to access. You defined this alias when you cataloged the database using the CATALOG DATABASE command. This value is case sensitive.

Although the DBNAME variable is specified as an option in the CREATE SERVER statement, it is required for BigSQL data sources.

When you register the server definition, you can specify additional server options in the CREATE SERVER statement. These options include general server options and DB2 data source-specific server options. For more information, see the options reference information.

In InfoSphere™ Federation Server Version 9.7 Fix Pack 2 and later, when you run the CREATE SERVER statement, the following server options are automatically configured based on the configuration of your data source:

• DATE_COMPAT
• NUMBER_COMPAT
• SAME_DECFLT_ROUNDING
• VARCHAR2_COMPAT

If you attempt to manually configure these server options you receive the SQL1841N message.

5. Create the user mappings for a BigSQL data source.
A user mapping defines an association between a user ID and password at the federated server and the corresponding user ID and password at the data source server.

About this task:

Whether or not user mappings are required for BigSQL data sources depends on the configuration of the federated environment. If the environment uses federated trusted contexts and proxy authentication, none or only a few user mappings may be required. For best results, plan and set up federated trusted contexts before you create user mappings.

• Issue the CREATE USER MAPPING statement to map the local user ID to the BigSQL server user ID and password.

The following example shows how to map a federated server authorization ID to a remote data source user ID and password:

CREATE USER MAPPING FOR user_name SERVER server_name OPTIONS (REMOTE_AUTHID 'remote_id', REMOTE_PASSWORD 'password')

You can use the DB2 special register USER to map the authorization ID of the person issuing the CREATE USER MAPPING statement to the data source user ID specified in the REMOTE_AUTHID user option.
The following is an example of the CREATE USER MAPPING statement which includes the special register USER:

CREATE USER MAPPING FOR USER SERVER server_name OPTIONS (REMOTE_AUTHID 'remote_id', REMOTE_PASSWORD 'password')

In InfoSphere™ Federation Server Version 9.7 Fix Pack 2 and later, you can create a public user mapping to allow all local database users to access a data source through a single remote user ID and password.

Example:

CREATE WRAPPER DRDA;

CREATE SERVER server1
TYPE bigsql VERSION 4 WRAPPER DRDA
AUTHORIZATION "APP_USER" PASSWORD "secret"
OPTIONS (DBNAME 'remotedb');

CREATE USER MAPPING FOR PUBLIC SERVER server1
OPTIONS (REMOTE_AUTHID ‘APP_USER', REMOTE_PASSWORD ‘secret');

For more information, see User mappings and CREATE USER MAPPING statement.

6. Test the connection to the BigSQL data source server.
Test the connection to the DB2 data source server to determine if the federated server is properly configured to access the DB2 data source server.

• Open a pass-through session and issue an SQL SELECT statement on the system tables.

Example:
SET PASSTHRU server_definition_name
SELECT count(*) FROM SYSCAT.TABLES
SET PASSTHRU RESET

If the SQL SELECT statement returns a count, access to the data source is properly configured.

Troubleshooting data source connection errors
A test connection to the data source server might return an error for several reasons. There are actions that you can take to determine why the error occurred.

7. Register the nicknames for the BigSQL tables and views.
For each server definition, register a nickname for each table and view that you want to access. Then use the nicknames, not the names of the data source objects, when you query the DB2 database.

• Issue the CREATE NICKNAME statement.

Example:
CREATE NICKNAME nickname1 FOR SERVER1.authid.table1

where:
nickname1 is a unique nickname that identifies the remote BigSQL table or view. The nickname can include both a schema and the nickname. If you omit the schema, the authorization ID of the user who registers the nickname is used.
SERVER1.authid.table1 is a three-part identifier for the remote object:

• SERVER1 is the name that you assigned to the BigSQL database server in the CREATE SERVER statement.
• authid is the user ID of the owner of the table or view. This value is case sensitive.
• table1 is the name of the remote table or view that you want to access.

When you create the nickname, the federated server queries the data source catalog by using the nickname. This query tests the connection to the data source table or view. If the connection does not work, you receive an error message.
After you complete this task, you can use nicknames to access data source objects and perform many other operations such as creating a nickname over a temporal table.

For more information, see CREATE NICKNAME statement.