Installation

Windows Installation

To execute the installation the user must start the executable “SubsetSetup_x86_<version>.exe”. The following screens will be presented:

License

The first time DATPROF Subset is started a valid license key should be submitted. This license is stored in the file “dpfsub.lic” in the folder shown below:

License Directory

Installing for DB2 for Linux, Unix or Windows

To enable DATPROF Subset for DB2 for LUW some specific DB2 actions are required on the the Database Server platform.

DATPROF Subset uses the IBM Federation facilities to connect two database schemas across the network.

These are the steps to take to use Federation:

A Node must be created.
Federation must be active.
A DRDA Wrapper must be created.
A Server should be specified using this wrapper.
To use this server a User Mapping must be defined.

DATPROF Subset will use this setup to create so called Nicknames for all Remote tables.

The setup would typically be done by a DBA on the machine with the Target database defined as a Federated server.

Below each step is shown and briefly explained.

Add a Node

To connect two machines Federation uses a so called Node. To create a Node follow this instruction

Create a node

BASH

db2 catalog tcpip node {node-name} remote {ip|host} server {port}

An example of creating a node

BASH

db2 catalog tcpip node remnode remote 192.100.20.10 server 50000

To check your node, execute the following command: db2 list node directory

Then create an alias to the remote database (the data source)

To do this use this command:

Create an alias to the remote database

BASH

db2 catalog db {remote-databasename} as {local-alias} at node {node-name}

An example of creating an alias

BASH

db2 catalog db CRM_01 as localias at node srcnode

Following these commands a refresh of the Directory cache is required. To do this use this command:

Refreshing the Directory Cache

BASH

db2 terminate

Beware that all active connections will be closed.

Enable Federation

To enable Federation the owner of the local DB2 instance should execute the following commands:

Enabling Federation

BASH

db2 update database manager configuration using federated yes
db2stop
db2start

Create a DRDA Wrapper

A wrapper is an alias for the protocol used. DATPROF Subset uses the “DRDA” (Distributed Relational Database Architecture) protocol.

A database user with the DBADM Role should connect to the target database to issue the following command to create the wrapper.

(For instance the db2inst1 user connects to the target database )

Creating a wrapper

BASH

create wrapper {custom-name} library db2drda.(so|dll)

An example of creating a wrapper

BASH

create wrapper drda

DRDA is the default protocol for a connection between two DB2 databases. Creating a wrapper is a one-time action which can be reused by other users.

Create a Server

To have a connection between two Database Instances a “Server” is required.

A database user with the DBADM Role should connect to the target database to issue the following command to create this Server:

(For instance the db2inst1 user connects to the target database )

Creating a server

BASH

create server {server-name}
  type db2/udb
  version {database version (10.5)}
  wrapper {wrapper-name (drda)}
  authid {remote-username}
  password "{remote-password}"
  options(dbname '{local-alias}');

An example of creating a server

BASH

create server srcserver
  type db2/udb
  version 10.5
  wrapper drda
  authid MyRemoteUser
  password "MyRemotePassword"
  options(add dbname 'localias');

This example creates a named connection between the local (Target) database instance where the query will be executed and the remote (Source) database instance where the data is read from. This is also a one-time action and the Server can be reused by other users.

The User and Password specified are only required to register the connection.

The name of this Server is the Server Name DATPROF Subset requires you to enter in the project settings screen.

Create a User Mapping

After the valid Server is created a connected Database user can setup a mapping between a Local user and a Remote user.

Whenever a local user executes a query using this User Mapping the permissions granted to the Remote User apply.

This is how to create a User Mapping:

Create user mapping

BASH

create user mapping for {local-username}
  server {server-name}
  options (remote_authid '{remote-username}',remote_password '{remote-password}');

An example of creating user mapping

BASH

create user mapping for LocalUser
  server sDataServer
  options (remote_authid 'RemoteUser',remote_password 'RemotePassword’);

Nicknames

At this point the Federation is setup and active and DATPROF Subset can access objects in the remote database. For every Remote table DATPROF Subset will create a Nickname.

Every table is Prefixed with “DSB_” : DSB_<OriginalTableName>

This Nickname must also be used in user generated SQL scripts whenever references to Remote tables are made.