English

How To: Create a spatially enabled view using a DB2 UDB Federated attribute table

Summary

It is possible to create a spatially enabled view that joins an ArcSDE feature class to a nicknamed Federated table stored in another instance of DB2 UDB. The steps to do so include:

1. Configuring the DB2 UDB client to communicate with the server using TCP/IP.
2. Create a DB2 UDB server wrapper.
3. Create a server to define a data source to the federated system.
4. Map the local user to a remote user.
5. Create table nickname.
6. Grant additional permissions to the local user.
7. Create indexes on the joined columns.
8. Create the spatially enabled view.

Prerequisites
It is assumed the server on which the view is created is the same server and database where ArcSDE resides. The IBM client and server are also assumed to be on this server. The assistance of a DB UDB administrator may be required if the user performing the task has insufficient permissions. You will be required to replace the following names with valid entries from your installation.
� <remote workstation name> Name of remote workstation where a remote instance of DB2 UDB resides
� <remote database name> Name of a remote database that contains non-spatial data
� <TCPIP port of the remote instance> Port number that the remote instance “listens” on
� <local database name> Name of the local ArcSDE database
� <remote table name> Non-spatial table in the <remote database name>
� <local table name> An alias for the nicknamed table in the local database
� An ArcSDE feature class in <local database name>
� <DB2 administrator name> Name for the DB2 Administrator (usually DB2ADMIN)
� <local user name> Name of the local schema that owns the local feature class an will own the nicknamed table.
� <remote user name>
� <password> Valid password for the login specified in the syntax. You will need this for:
o the local DB2 Administrator login
o the remote DB2 Administrator login
o the local user schema login
o the remote user schema login
� <remote node alias name> Alias for the remote node to be cataloged on the local server
� <data source server name> Name of the local data source name created in step 3.

Procedure

The procedures below specify a command line methodology for performing DB2 UDB tasks. Please consult the IBM DB2 UDB documentation for performing these tasks by way of DB2 GUI tools.

  1. Configuring the DB2 UDB client to communicate with the server using TCP/IP.

    Catalog the remote DB2 UDB node.
    At the db2=> prompt, type:
    CATALOG TCPIP NODE <remote node alias name> REMOTE <remote workstation name> SERVER <TCPIP port of the remote instance>

    Catalog the remote DB2 UDB database
    At the db2=> prompt, type:
    CATALOG DATABASE <remote database name> AT NODE <remote node alias name>

    Please note: node names and database names are case sensitive.
  2. Create a DB2 UDB server wrapper

    Create a wrapper for the local server's database. A wrapper enables a federated system to communicate with and retrieve data from a particular class or category of a data source.

    At the db2=> prompt, type:
    CONNECT TO <local database name> USER <DB2 administrator name> USING <password>
    CREATE WRAPPER "DRDA" LIBRARY 'drda.dll'
  3. Create a server to define a data source to the federated system

    At the db2=> prompt, type:
    CREATE SERVER "<data source server name>" TYPE DB2/NT VERSION 7.2 WRAPPER "DRDA" AUTHID <local user name> PASSWORD <password> OPTIONS (NODE ‘<remote node alias name>, DBNAME ‘<remote database name>')
  4. Map the local user to a remote user

    Use the Create User Mappings window to create a mapping between a user's authorization at a DB2 database and the user's authorization at a data source. If the authorization ID of the statement is different than the authorization name that is being mapped to the data source, then the authorization ID must include SYSADM or DBADM authority. Otherwise, if the authorization ID and the authorization name match, then no privileges or authorities are required. If this is true, this first statement must be run:

    At the db2=> prompt, type:
    CREATE USER MAPPING FOR <DB2 administrator name>" SERVER "<data source server name>" OPTIONS (REMOTE_AUTHID '<remote user name>', REMOTE_PASSWORD '<remote user password>'
    If not, only the following mapping must be performed:
    At the db2=> prompt, type:
    CREATE USER MAPPING FOR "<local user name>" SERVER "<data source server name>" OPTIONS (REMOTE_AUTHID '<remote user name>', REMOTE_PASSWORD '<remote user password>'
  5. Create table nickname

    Use the “Create Nicknames” command option to specify nicknames for tables and views at the data source.
    Nicknames are names that you provide to reference data source tables and views during query processing. Create a nickname for a table in the remote database that you cataloged above.

    At the db2=> prompt, type:
    CREATE NICKNAME "<local user name>"."<local table name>" FOR "<data source server name>"."<remote user name>"."<remote table name>"
  6. Grant additional permissions to the local user

    The following permissions are required per the DB2 UDB documentation. IMPLICIT_SCHEMA Grants the authority to implicitly create a schema. The definer of a base table, summary table, or nickname automatically receives the CONTROL privilege. It must be explicitly stated for the stated user schema if it is different from the schema creating the nickname.

    At the db2=> prompt, type:
    GRANT IMPLICIT_SCHEMA ON DATABASE TO USER <local user name>
    GRANT CONTROL ON <local user name>.<local table name> TO USER <local user name>
  7. Creating indexes on the joined columns

    To speed the performance of joined table queries, make certain you index the columns involved in the query.

    DB2 CREATE INDEX <local user name>.<index name> ON <local user name>.<local table name>(<column name> ASC) {other options}

    Note, an index cannot be created on a nicknamed table. The index must be created on the source table in the remote instance.
  8. Creating the spatially enabled view

    A spatially enabled view is a view consisting of one or more tables and a spatial column. This view will appear as a feature class to ArcSDE and ArcSDE client applications.

    This view is created via the ArcSDE command “sdetable”. Please consult the ArcSDE documentation for more details about this command.

    sdetable -o create_view -T <view name> -t <tablename1,tablename2�> –c <columns> -w "where clause" -u <local user name> -p <local user password -D <local database> -i <ArcSDE instance name> -s <local server name>

    Verify that the view was created correctly by displaying, and querying, it in ArcCatalog.
    o Start ArcCatalog.
    o Open a Spatial Database Connection.
    o Add <local database>.<local user name>.<view name> as a layer in your map.
    o Click the `Zoom In' button.