Set up and use federation in InfoSphere BigInsights Big SQL V3.0

Big SQL V3.0 supports federation to many data sources, including IBM® DB2 for Linux, UNIX, and Windows™, IBM PureData™ System for Analytics, IBM PureData System for Operational Analytics, Teradata, and Oracle. Federation enables users to send distributed requests to multiple data sources within a single SQL statement. Learn how to use the federation capabilities in Big SQL V3.0.

Mara Elisa de Paiva Fernandes Matias (maramati@ie.ibm.com), Quality Assurance Software Engineer, IBM

Photo of Mara Elisa de Paiva Fernandes MatiasMara works on functional verification test team for IBM® InfoSphere® BigInsights at IBM in Dublin, Ireland. Although her work currently focuses on federation testing, Mara is interested in database systems and has experience working with technologies for many database management systems.



08 August 2014 (First published 08 July 2014)

This article introduces Big SQL V3.0 federation capabilities by using the data sources Teradata, Oracle, Netezza, and IBM® DB2 for Linux, UNIX, and Windows. Some knowledge of database systems is assumed. The focus is on the basic configuration that is required to use Big SQL V3.0 federation.

Big SQL V3.0 federation, a feature of Big SQL V3.0, is highly configurable. The command syntax that is used here is a simplified version of the essential settings for a working system.

What you need for this article

  • Knowledge of Big SQL in InfoSphere BigInsights, particularly about the characteristics of the BIGSQL database.
  • Familiarity with the DBMS being used as data source
  • Knowledge of how to set up the remote server's connection and its respective client.
  • BIGSQL3 installed on a system that runs Linux AMD64 or Linux PPC
  • Clients for each data source, which are installed and configured on the BIGSQL3 machine.
  • Use of Netezza® through that DataDirect ODBC that is compatible with the Netezza client.
  • Netezza-branded DataDirect Driver and the Netezza client, which can be downloaded from IBM Fix Central

Supported data sources

The Big SQL V3.0 federation server supports several data sources, as shown in Table 1.

Table 1. Data sources and versions supported
Database management systemVersion
DB2®DB2 for Linux, UNIX, and Windows v10.5
OracleV11g R2
Teradata V12
Netezza V7.2

Explore HadoopDev, your direct channel to the InfoSphere BigInsights development team

Find all the resources that you need to develop with InfoSphere BigInsights, brought to you by the extended BigInsights development team. Doc, product downloads, labs, code examples, help, events, expert blogs — it's all there. Plus a direct line to the developers. Engage with the team now.

Because data analytics is crucial to the success of a business, data warehousing that supports consolidation and federation of data is a requirement.

Big SQL, part of IBM InfoSphere® BigInsights™, is the SQL interface to Hadoop file systems. It enables the storage and handling of massive amounts of data. Big SQL federation enables a mixed environment with consolidation of big data and federation of live data from different data sources to be created.


Overview of Big SQL V3.0 federation

The architecture of a federated server is fairly simple. It includes the Big SQL V3.0 engine, the wrapper, and the remote database management systems (DBMS) client.

One of the core elements of the engine is the optimizer (shown in Figure 1), which is responsible for choosing the execution plan for a federated query that is based on a cost analysis. The optimizer can rewrite the query, if necessary, to make it more efficient. The optimizer distributes query work between the federated server and data sources to make the process as cost efficient as possible. The cost of a query is automatically calculated per nickname by the runstats utility, which is turned on by default.

Figure 1. Architecture of Big SQL V3.0

To enable Big SQL V3.0, you need a wrapper, a server, a nickname, and a user mapping.

  • Wrapper: A library that handles the communication between the Big SQL V3.0 federation server and the DBMS client. Each type of data source must have its own wrapper. The wrapper translates the queries that come to and from the Big SQL V3.0 federation server to calls on the exposed API of the client. The client communicates with the data source.
  • Server: A remote database. Although a server object is a remote database, you need to know the system that this database resides in because that information determines what client is used to connect to it.
  • Nickname: A single remote object in the data source (for example, a table, a view, or a procedure) in a server. As with any federated server, the use of data sources is managed transparently. After the nicknames for the remote objects are defined from a user perspective, they behave as if they are local objects. If the data comes from the data source unchanged, the result of a query is determined by the local rules. This transparency is important in this case because the Big SQL database uses only binary collation, by default. This type of collation affects the options that need to be set when you create the server object.
  • User mapping: An association between a local authorization ID and a remote authorization ID. Any operation that is run on the federated server by the local ID is run on the data source by using the ID that it was mapped to. For a user to query a nickname, the user must be authorized to perform SELECT operation on the original table. For security reasons, on most systems the same authorization ID with the same password does not exist on all machines; therefore, user mapping is required.

After the setup is complete, Big SQL V3.0 is used through JSqsh, similar to how Big SQL versions are accessed. To create federated objects, an authorization ID with DBADM authority is required.


Installation

The installer for InfoSphere BigInsights automatically installs the wrappers and applies the federation licence. The wrappers that are shown in Table 2 are included by default in the directory $HOME/sqllib/lib/.

Table 2. Wrappers
Data source typeWrapper libraryAuxiliary files
DB2libdb2drda.solibdb2drdaF.so
libdb2drdaU.so
Teradata libdb2teradata.so libdb2STteradataF.a

libdb2teradataU.so
Oraclelibdb2net8.so libdb2net8F.so
libdb2net8U.so
libdb2STnet8F.a
Netezza libdb2rcodbc.so libdb2odbct.so
libdb2rcodbcF.so
libdb2rcodbcU.so

Environment setup

Login as user bigsql. By default, Big SQL V3.0 federation is not enabled for immediate use. Enable it by using the following command.

DB2 UPDATE DBM CFG USING FEDERATED YES

You must restart the database for the change to take effect.

For many data sources, you must set certain variables in the db2dj.ini file. Variables set to file names or directories must use the fully qualified name, and values cannot use meta characters or environment variables like ~, or $HOME. The db2dj.ini is in $HOME/sqllib/cfg if it exists. Otherwise, you can use any editor to create it. After you make changes, reload the profile file and restart the instance.

The instance profile file is $HOME/sqllib/db2profile or $HOME/sqllib/db2cshrc depending on whether you use the Korn shell or the Bourne shell. To reload the profile, issue the following command, substituting the name of your profile file:

. $HOME/sqllib/db2profile

To restart the instance of the Big SQL V3.0 federation server, run the following code:

$BIGSQL_HOME/bin/bigsql stop
$BIGSQL_HOME/bin/bigsql start

Set up the connection to DB2 for Linux, UNIX, and Windows

To identify which database the federated server connects to, the remote DB2 database must be cataloged in the federated server system database directory.

Listing 1. Catalog the remote server
DB2 CATALOG TCPIP NODE RMNODE REMOTE MYHOST SERVER 12345

RMNODE is the node name.

MYHOST is the host name for the remote machine.

12345 is the service the remote DB2 server is running on, defined by setting the svcename variable at the remote data source (db2 often runs on port 50000).

Catalog the database as shown:

Listing 2. Catalog the database
DB2 CATALOG DATABASE RDB2DATA AS DB2DATA AT NODE RMNODE

RDB2DATA is the name of the database at the remote node.

DB2DATA is the name for the database in the local catalog.

RMNODE is the node name.

Refresh the local catalog by running the command db2terminate.

Set up the connection to Teradata

  1. Download and install the Teradata client.
  2. Add the following command to the startup file of the shell that is used (for example, .kshrc if you use Korn shell), to export the environmental variable TERADATA_LIB_DIR.
    export   TERADATA_LIB_DIR=/opt/teradata/client/lib64

    /opt/teradata/client/lib64 is the absolute path where the Teradata client libraries are located.

  3. As shown in the following command, run the djxlinkTeradata command in $HOME/sqllib/bin/ as root. If the operation is successful, the file libdb2STteradataF.a that is required for the Teradata wrapper is created in the $HOME/sqllib/lib64 directory.
    su root
    <HOME>/sqllib/bin/ djxlinkTeradata

    <HOME> is the home directory for Big SQL.

  4. To avoid errors when you use nicknames, set the variable TERADATA_CHARSET in the db2dj.ini file to the character set used at the data source, as shown:
    TERADATA_CHARSET=ASCII

    ASCII is the character set used by the Teradata database.

Set up the connection to Oracle

  1. Set the variable ORACLE_HOME to the absolute path where the Oracle client is installed in db2dj.ini, as shown:
    ORACLE_HOME=/opt/oracleclient

    /opt/oracleclient is the absolute path where the Oracle client is installed.

  2. Export the variable ORACLE_HOME and add the Oracle client libraries to the Big SQL federation server library path variable DB2LIBPATH in the instance profile file as shown:
    export ORACLE_HOME=/opt/csdlclient
    export DB2LIBPATH=$ORACLE_HOME/lib:$DB2LIBPATH

    /opt/oracleclient is the absolute path where the Oracle client is installed.

  3. Run the profile.
  4. During the client installation, if the location of the tnsnames.ora file moves from the default directory $ORACLE_HOME/network/admin, set the variable TNS_ADMIN in the db2dj.ini file, as shown:
    TNS_ADMIN=/home/bigsql/resources

    /home/bigsql/resources is the absolute path to the location of the tnsnames.ora file.

Set up the connection to Netezza

General steps are given here. For more details, see the complete guide for installing and configuring Netezza access through the ODBC driver.

  1. Download and install the Netezza client and the compatible ODBC driver. Consider these two compatible drivers:
    • DataDirect Technologies Connect for ODBC driver
    • IBM DataDirect ODBC driver

    You can download a complete package that includes the DataDirect ODBC Driver and the Netezza client. The package includes a sample odbc.ini file, which describes the Netezza servers that are used on the create server command.

  2. Create a symbolic link named .odbc.ini in the home directory to the location where you created your odbc.ini file:
    ln -sf $HOME/resources/odbc.ini $HOME/.odbc.ini

    $HOME/resources/odbc.ini is the odbc.ini file being used (a sample is in the netezza package).

  3. Export variables NZ_ODBC_INI_PATH, ODBCINI, and LIBPATH.
    export NZ_ODBC_INI_PATH=$HOME/resources
    export ODBCINI=$HOME/resources/odbc.ini
    export LIBPATH=/opt/odbc64v51/lib

    $HOME/resources is the directory where odbc.ini is located.

    $HOME/resources/odbc.ini is the path to the odbc.ini file.

    /opt/odbc64/lib is the absolute path to ODBC driver libraries.

  4. Add the required variables to the db2dj.ini file, as shown:
    NZ_ODBC_INI_PATH=/home/bigsql/resources
    ODBCINI=/home/bigsql/resources/odbc.ini
    LIBPATH=/opt/odbc64v51/lib

    where /home/bigsql/resources is the absolute path to the location of the odbc.ini file.

  5. Add the ODBC driver library path to the Big SQL V3.0 federation server library path variable DB2LIBPATH as shown:
    export DB2LIBPATH=/opt/odbc64v51/lib:$DB2LIBPATH

    /opt/odbc64v51/lib is the absolute path where the ODBC driver is installed.

Complete system setup

The following code listings show a sample of the configurations a user would have, if he used all of the data sources at the same time.

Listing 3. Content of .kshrc
  export ORACLE_HOME=/opt/oracleclient
  export TERADATA_LIB_DIR=/opt/teradata/client/lib64
  export NZ_ODBC_INI_PATH=$HOME/resources
  export ODBCINI=$HOME/resources/odbc.ini
  export LIBPATH=/opt/odbc64v51/lib
Listing 4. Content of db2dj.ini
  ORACLE_HOME=/opt/oracleclient
  TNS_ADMIN=/home/bigsql/resources
  TERADATA_CHARSET=ASCII
  NZ_ODBC_INI_PATH=/home/bigsql/resources
  ODBCINI=/home/bigsql/resources/odbc.ini
  LIBPATH=/opt/odbc64v51/lib
Listing 5. Lines added to db2profile
  export ORACLE=/opt/oracleclient
  export DB2LIBPATH=$ORACLE_HOME/lib:/opt/odbc64v51/lib
Listing 6. Content of tnsnames.ora
  ora11gr2 =
  (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ora11qa.svl.ibm.com)(PORT = 1521))
      (CONNECT_DATA =
        (SERVER = DEDICATED)
        (SERVICE_NAME = ora11qa)
      )
    )
Listing 7. Content of odbc.ini
  NZSQL = NetezzaSQL

  [NZSQL]
  Driver = /opt/netezza/lib64/libnzodbc.so
  Description = NetezzaSQL ODBC
  Servername = netz.ibm.co.uk
  Port = 5480
  Database = DWDB
  Username = nzuser
  Password = nzuser

Wrapper creation and use

A wrapper is required for each different data source type because each wrapper is associated with a single library file. Wrappers can be written as C++ or Java™ applications. They can be seen as a composition of two subcomponents: the query compiler and the execution engine.

For wrappers written in C++, it is possible to increase performance by running them as trusted applications in the database engine, a practice that enables the compiler and the execution engine to run concurrently.

Java wrappers are always run in fenced mode. In this mode, the compiler must finish before the query is handed over to an external process to be run. However, this mode has the advantage of allowing queries with only reads across different data sources to be run in parallel. By default, the wrapper is run in trusted mode. To set it to fenced mode, set the variable DJ_FENCED when the wrapper is created.

Create the wrapper syntax

CREATE WRAPPER <WRAPPER_NAME> LIBRARY <WRAPPER_FILE> OPTIONS ( <OPTIONS> )

<WRAPPER_NAME> is a unique wrapper name.

<WRAPPER_FILE> is the wrapper library file.

For most data sources, the options (<OPTIONS>) portion of the command is optional.

Note: Because the Netezza wrapper is a generic ODBC wrapper, the MODULE options must be defined. The two most relevant options are:

  • MODULE: Required option when you create a wrapper for Netezza. Set it to the absolute path of the ODBC driver library.
  • DB2_FENCED: Controls what mode the wrapper is to be run in: fenced or trusted.

Examples of wrappers for different data sources

The following examples show how to create wrappers for each data source type.

Listing 8. DB2 data source
CREATE WRAPPER DRDA LIBRARY 'libdb2drda.so'
Listing 9. Teradata data source
CREATE WRAPPER TERA LIBRARY 'libdb2teradata.so'
Listing 10. Oracle data source
CREATE WRAPPER ORA LIBRARY 'libdb2net8.so'
Listing 11. Netezza data source
CREATE WRAPPER NETZ LIBRARY 'libdb2rcodbc.so' OPTIONS(MODULE '/opt/odbc64v51/lib/libodbc.so')

Note: In the previous code listing, /opt/odbc64v51/lib/libodbc.so is the absolute path to the ODBC driver.


Server definition and use

A server refers to a remote database. The server definition must include the type of server and the database version or release level that it is in, the wrapper that should be used to communicate with it, and a user and password to be used for authentication.

The following section describes the minimum options that are required for each data source and the value that the collating_sequence option must be set to. The Big SQL V3.0 federation server uses only binary collation. Binary collation is blank sensitive and differentiates empty from null strings. Determine whether the data source's collating sequence is compatible with binary collation by checking whether the data sources collation behavior is the same for blank sensitivity and treatment of null strings.

Create server syntax

CREATE SERVER <SERVER_NAME> TYPE <SERVER_TYPE> 
VERSION <VERSION> WRAPPER <WRAPPER_NAME> 
AUTHORIZATION <REMOTE_USER>
PASSWORD <USER_PASSWORD> OPTIONS (<SERVER OPTIONS>)

<SERVER_NAME> is a unique server name.

<SERVER_TYPE> is the data source type.

<VERSION> is the data source.

<WRAPPER_NAME> is a unique wrapper name defined by the create wrapper command.

<REMOTE_USER> is a valid remote user.

<USER_PASSWORD> is the password for <REMOTE_USER>.

<SERVER OPTIONS> definition of options for the server being created.

The values for <SERVER TYPE> and <SERVER VERSION> can be found on the respective data source type in the following section.

Options for DB2 data source

The <TYPE> of a DB2 server is specified as db2/<SUBTYPE> where SUBTYPE is your platform. DB2 for Linux, UNIX, and Windows is the only supported platform; therefore, the type is db2/udb.

The fully supported <VERSION> is 10.5.

The DB2 identity collation has the same behavior as binary collation in terms of empty and null strings. It is blank insensitive; therefore, it is incompatible with binary collation.

DB2 in Oracle compatibility mode is compatible in terms of blank padded comparisons. But in this mode, empty and null strings are treated the same; therefore, it is incompatible with binary collation.

To enable pushdowns in either of these cases, the COLLATING_SEQUENCE must be set to N.

Table 3. DB2 for Linux, UNIX, and Windows options reference
OptionDescription
DBNAME(Always required.) Specifies the specific database to use for the initial remote DB2 database connection. This specific database is the database alias for the remote DB2 database that is cataloged on the federated server that is mentioned in the environmental setup instructions.
PUSHDOWNSpecifies whether the federated server allows the data source to evaluate operations. Valid values are Y and N. The default is Y; the data source evaluates operations. N specifies that the federated server send SQL statements that include only SELECT with column names.
COLLATING_SEQUENCE(Required if PUSHDOWN is set to Y) Specifies whether the data source uses the same default collating sequence as the federated database. Must be set to N because there are currently no collations in DB2 that are perfectly compatible with the Big SQL V3.0 binary collation.

Teradata

The <TYPE> of a Teradata server is teradata.

The fully supported <VERSION> is 12.

Teradata collations are ANSI-compliant. This means empty strings are considered different from null strings; therefore, it is compatible with binary collation, but it is blank insensitive.

To enable pushdowns, COLLATING_SEQUENCE must be set to N.

Table 4. Teradata options reference
OptionDescription
NODE(Always required.) Specifies the Teradata server. Can be set to the server alias, the IP address, or the fully qualified domain name.
PUSHDOWNSpecifies whether the federated server allows the data source to evaluate operations. Valid values are Y and N. The default is Y; the data source evaluates operations. N specifies that the federated server send SQL statements that include only SELECT with column names.
COLLATING_SEQUENCE(Required if PUSHDOWN is set to Y.) Specifies whether the data source uses the same default collating sequence as the federated database. Must be set to N.

Oracle

The <TYPE> in Oracle is oracle.

The fully supported <VERSION> is 11.

Oracle has blank-sensitive comparison. It even has a binary collation of its own that is compatible with binary collation, but in Oracle regardless of collation, empty strings are treated the same as null strings; therefore they cannot be considered compatible.

To enable pushdowns, COLLATING_SEQUENCE must be set to N.

Table 5. Oracle options reference
OptionDescription
NODE(Always required.) Specifies an entry in the tnsnames.ora file
PUSHDOWNSpecifies whether the federated server allows the data source to evaluate operations. Valid values are Y and N. The default is Y; the data source evaluates operations. N specifies that the federated server send SQL statements that include only SELECT with column names.
COLLATING_SEQUENCE(Required if PUSHDOWN is set to Y.) Specifies whether the data source uses the same default collating sequence as the federated database. Must be set to N because there are currently no collations in Oracle that are perfectly compatible with Big SQL V3.0 binary collation.

Netezza

Netezza has blank-sensitive comparisons and because null strings are not supported, it is compatible with binary collation.

Table 6. Netezza options reference
OptionDescription
NODE(Always required.) Specifies the name of the node or the system DSN name that is assigned to the ODBC data source defined in the odbc.ini file. The value is case-sensitive.
PUSHDOWNSpecifies whether the federated server allows the data source to evaluate operations. Valid values are Y and N. The default is Y; the data source evaluates operations. N specifies that the federated server send SQL statements that include only SELECT with column names.

Usage examples

The following code listings show how to create a server for various data sources.

Listing 12. DB2
CREATE SERVER LUWSERV TYPE DB2/UDB VERSION 10.5 WRAPPER DRDA AUTHORIZATION
\”db2user\” PASSWORD \”db2user\” OPTIONS (DBNAME 'DB2DATA',  PUSHDOWN 'Y',
COLLATING_SEQUENCE 'N')
Listing 13. Teradata
CREATE SERVER TERASERV TYPE TERADATA VERSION 12 WRAPPER TERA AUTHORIZATION
\”terauser\” PASSWORD \”terauser\” OPTIONS (NODE 'TERANODE',  PUSHDOWN 'Y',
COLLATING_SEQUENCE 'N')
Listing 14. Oracle
CREATE SERVER ORASERV TYPE ORACLE VERSION 11 WRAPPER ORA AUTHORIZATION
\”orauser\” PASSWORD \”orauser\” OPTIONS (NODE 'TNSNODENAME', PUSHDOWN 'Y', 
COLLATING_SEQUENCE 'N')
Listing 15. Netezza
CREATE SERVER NZSERV TYPE ODBC VERSION 7 WRAPPER NETZ AUTHORIZATION \"nzuser\"
PASSWORD \"nzuser\" OPTIONS ( NODE 'NZSQL')

Nicknames

In Big SQL V3.0, a nickname is a local designation to a remote object such as a table or a view. To create a nickname, the user that issues the command must be mapped to a valid user on the data source. Mapping is explained in the following section.

Create nickname syntax

The following examples show how to create nicknames for various entities.

Listing 16. Explicitly create a nickname for an existing table
CREATE NICKNAME <LOCAL_NAME> FOR <SERVER>.<SCHEMA>.<REMOTE_NAME>

<LOCAL_NAME> is the name that the object has locally.

<SERVER> is a unique server name.

<SCHEMA> is the schema that the object was created under in the remote database.

<REMOTE_NAME> is the name that the object has on the remote server.

Listing 17. Implicitly create a nickname by creating a table at the data source
CREATE TABLE <TABLE_NAME> (<COLUMN_DEFINITION>) OPTIONS (REMOTE SERVER <SERVER>, REMOTE_SCHEMA <SCHEMA>)

<TABLE_NAME> is the nickname name and the remote table name.

<SERVER> is a unique server name.

<SCHEMA> is the remote schema under which the table will be created.

Listing 18. Create a nickname for an existing table nicktbl a teradata data source
CREATE NICKNAME NICK1 FOR  TERASERV.TERAUSER.NICKTBL
Listing 19. Create a table at the Netezza server and implicitly create the nickname
CREATE TABLE "NZTBL1" ("C1" INTEGER , "C2" CHAR(20) ) OPTIONS(REMOTE_SERVER
'NZSERV', REMOTE_SCHEMA 'NZUSER')

Mapping

In addition to nicknames, which are specific to objects like tables, it is possible to create mappings for users and for data types.

For data sources such as Oracle, it is a requirement that local users be mapped to valid users on the remote server.

User mapping

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, as shown in the following code listing.

Listing 20. Define an association between a user ID and password at the federated server and data source
CREATE USER MAPPING FOR <LOCAL_ID> OPTIONS (REMOTE_AUTHID <REMOTE_ID>, REMOTE_PASSWORD <REMOTE_PASSWORD>

<LOCAL_ID> is a local authorization ID. It can also be one of the special registers USER or PUBLIC, where USER maps the ID currently connected to the database and PUBLIC maps all valid authorization IDs.

<REMOTE_ID> is a valid authorization id in the remote data source.

<REMOTE_PASSWORD> is the password for <REMOTE_ID>.

The following examples show how to create user mappings.

Listing 21. Map the local user ID foo to the DB2 server user ID db2user with password db2user
CREATE USER MAPPING FOR foo SERVER LUWSERV OPTIONS (REMOTE_AUTHID 'db2user',
REMOTE_PASSWORD 'db2user')
Listing 22. Map the local user ID orauser to the Oracle user ID orauser with password orauser
CREATE USER MAPPING FOR orauser SERVER ORASERV OPTIONS ( REMOTE_PASSWORD
'orauser')
Listing 23. Map the current authorization ID to the Teradata user terauser with password terauser
CREATE USER MAPPING FOR USER SERVER TERASERV OPTIONS (REMOTE_AUTHID 'terauser',
REMOTE_PASSWORD 'terauser')
Listing 24. Map all the valid authorization IDs to the Netezza user nzuser with password nzuser
CREATE USER MAPPING FOR PUBLIC SERVER NZSERV OPTIONS (REMOTE_AUTHID 'nzuser',
REMOTE_PASSWORD 'nzuser')

Data type mapping

Data type mappings associate the federated database data types with the data types at the data source. These mappings can be forward or reverse depending on whether a data type is transformed when it's being sent or fetched from the data source.

This association might be necessary if the data type does not exist locally or if the data type is incompatible.

For example, when you use an Oracle data source:

  • Data type NUMBER(8,0) is not a valid data type in Big SQL V3.0. You can map it to its equivalent INT data type.
  • The date data type that is represented as a time stamp in Oracle is mapped to the local date data type.

Data type mapping can be created on the server or nickname level.

Server level mapping

Server level data type mappings are applied to all transactions with the data source that use a certain data type. When a server of a determined <TYPE> is created, some data type mappings are created by default. See the full list of default mappings.

Listing 25. Syntax for mapping on a particular server
CREATE TYPE MAPPING <TYPE-MAPPING-NAME> TO/FROM <LOCAL-DATA-TYPE>
FROM/TO SERVER <SERVERNAME> REMOTE TYPE <REMOTE-DATA-TYPE>
Listing 26. Syntax for mapping on a server type
CREATE TYPE MAPPING <TYPE-MAPPING-NAME> TO/FROM <LOCAL-DATA-TYPE>
FROM/TO SERVER TYPE <SERVERTYPE> REMOTE TYPE <REMOTE-DATA-TYPE>

<TYPE-MAPPING-NAME> is a unique name for the mapping.

<LOCAL-DATA-TYPE> is a valid data type in Big SQL V3.0.

<SERVERNAME> is a server name created by using the create server command.

<SERVERTYPE> is a server type.

<REMOTE-DATA-TYPE> is a valid data type at the data source.

Nickname mapping level

Nickname level mapping affects only transactions that involve the column being mapped. Nickname level mappings are always forward and reverse and are done on a specific column.

Listing 27. Mapping by altering an existing nickname
ALTER NICKANAME <NICKNAME> SET COLUMN <COLUMN> LOCAL TYPE
<TYPE>

<NICKNAME> is the name of an existing nickname.

<COLUMN> is a column name of <NICKNAME>.

<TYPE> is a valid local data type.

Examples of data type mappings

The following code shows how to map different entities.

Listing 28. Map Oracle date to local date for all Oracle servers
CREATE TYPE MAPPING MY_ORACLE_DATE FROM LOCAL TYPE SYSIBM.DATE TO SERVER TYPE
ORACLE REMOTE TYPE DATE
Listing 29. Map date to local date for server LUWOMSERV (a DB2 for Linux, UNIX, and Windows server by using Oracle compatibility mode)
CREATE TYPE MAPPING MY_ORACLE_DATE FROM LOCAL TYPE SYSIBM.DATE TO SERVER
LUWOMSERV REMOTE TYPE DATE
Listing 30. Map decimal(7,2) column dec72 of nickname nick1 to double data type
ALTER NICKNAME NICK1 SET COLUMN dec72 LOCAL DATA TYPE DOUBLE

Common failures

Error

SQL20076N  The instance for the database is not enabled for the specified action or operation.
Reason code = "1".  SQLSTATE=0A502

Resolution

Enable federation and restart the server, by using the following commands:

db2 update dbm cfg using federated yes
$BIGSQL_HOME/bin/bigsql stop
$BIGSQL_HOME/bin/bigsql start

Error

  • SQL5182N A required environment variable, "ORACLE_HOME", has not been set.
  • SQL30090N Operation invalid for application execution environment. Reason code = "31". SQLSTATE=25000

Resolution

Follow the setup steps under "Set up the connection to Oracle".

Error

When you use Oracle data source, you get the following message:

SQL1097N  The node name was not found in the node directory.  SQLSTATE=42720

Resolution

If tnsnames.ora was created in a location different from the default set, the TNS_ADMIN variable in db2dj.ini to that location.

TNS_ADMIN=/home/bigsql/cfgfiles/

/home/bigsql/cfgfiles/ is the absolute path to the tnsnames.ora file.

Resources

Learn

Get products and technologies

Discuss

  • Discuss Big SQL and connect with other BigInsights users through the BigInsights forum.

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Big data and analytics on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Big data and analytics
ArticleID=976523
ArticleTitle=Set up and use federation in InfoSphere BigInsights Big SQL V3.0
publish-date=08082014