Creating and managing federated objects: SQL and XQuery editor

You can use the Data Studio SQL and XQuery editor to issue and deploy commands to configure access to any data source.

About this task

You can work with the Data Studio SQL and XQuery editor to create and edit individual or multiple federated object definitions and to deploy commands. The following procedure explains how to create a database connection and how to launch and use the SQL and XQuery editor.

Procedure

  1. Create the required federated objects:
    • Wrappers
    • Server definitions
    • User mappings
    • Nicknames
    1. Launch the SQL and XQuery editor.

      From the File menu, click File > New > Other. Then select Data > SQL or XQuery Script.

    2. Complete the New SQL or XQuery Script wizard, and leave the SQL and XQuery editor selected.
    3. Click Finish.

      The statement is added to the SQL Scripts folder of the data development project that you selected in the wizard. The statement opens in the SQL and XQuery editor.

    4. Connect the script to a database by using the Select Connection Profile wizard.
    5. Complete the SQL or XQuery statements for the objects that you want to create.
  2. Copy and edit the commands for the remaining objects.
  3. Click Run SQL in the options toolbar to run the SQL scripts.

Examples

The following examples demonstrate how to create federated objects by using the SQL and XQuery editor.

Example 1: Create federated objects to configure access to multiple servers for the ODBC data source.
  1. Create each required federated object.
    CREATE WRAPPER ODBC LIBRARY 'libdb2rcodbc.so' 
       OPTIONS (MODULE '/opt/lib/odbc.so');
CREATE SERVER mysql_server TYPE mysql 
 VERSION 4.0 WRAPPER ODBC 
 OPTIONS (NODE 'odbc_node', DBNAME 'venice');
CREATE USER MAPPING FOR arturo SERVER mysql_server
       OPTIONS (REMOTE_AUTHID 'art', REMOTE_PASSWORD 'red4blue');
CREATE NICKNAME cust_europe FOR mysql_server."vinnie"."italy";
  2. Copy and paste the SQL to create multiple objects for the remaining servers.
    CREATE WRAPPER ODBC LIBRARY 'libdb2rcodbc.so' 
       OPTIONS (MODULE '/opt/lib/odbc.so');
CREATE SERVER mysql_server TYPE mysql 
 VERSION 4.0 WRAPPER ODBC 
 OPTIONS (NODE 'odbc_node', DBNAME 'venice');
CREATE USER MAPPING FOR arturo SERVER mysql_server
       OPTIONS (REMOTE_AUTHID 'art', REMOTE_PASSWORD 'red4blue');
CREATE NICKNAME cust_europe FOR mysql_server."vinnie"."italy";

CREATE WRAPPER ODBC2 LIBRARY 'libdb2rcodbc.so' 
       OPTIONS (MODULE '/opt/lib/odbc.so');
CREATE SERVER mysql_server2 TYPE mysql 
 VERSION 4.0 WRAPPER ODBC 
 OPTIONS (NODE 'odbc_node', DBNAME 'venice');
CREATE USER MAPPING FOR arturo SERVER mysql_server2
       OPTIONS (REMOTE_AUTHID 'art', REMOTE_PASSWORD 'red4blue');
CREATE NICKNAME cust_europe2 FOR mysql_server2."vinnie"."italy";
Example 2: Create multiple server definitions.
  1. Create a server definition.
    CREATE SERVER mysql_server2 TYPE mysql 
 VERSION 4.0 WRAPPER ODBC;
  2. Copy and paste and edit DDL to create additional server definitions. 
    CREATE SERVER mysql_server2 TYPE mysql 
 VERSION 4.0 WRAPPER ODBC;
CREATE SERVER mysql2_server2 TYPE mysql 
 VERSION 4.0 WRAPPER ODBC2;
CREATE SERVER mysql3_server2 TYPE mysql 
 VERSION 4.0 WRAPPER ODBC3;