Log in

No account? Create an account
Previous Entry Share Next Entry
Db2 Federation: PostgreSQL (Linux)
Accessing PostgreSQL data sources with Db2 Federation under Linux:

1. Configure the PostgreSQL server to output diagnostic messages in English.
This should make the life a bit easier in case of any problems.

        lc_messages = 'en_US.UTF-8'

Other settings should also work, but messages with multibyte characters (for example, error messages in Russian) might get improperly truncated in the Db2 output.

2. Configure the PostgreSQL database for ODBC, by creating the 'lo' datatype, and a set of ODBC-compliant helper functions.

The exact steps depend on the version of PostgreSQL server.
On my CentOS 7 system, this included executing the command "CREATE EXTENSION lo", and running the commands from file odbc.sql (supplied with the open source PostgreSQL ODBC driver).

3. Configure the ODBC data source in the odbc.ini file.
Use the Db2 bundled DataDirect ODBC driver for PostgreSQL (should work for most PostgreSQL variants).
You can put the odbc.ini file to any directory accessible by the instance owner (I used the db2inst1's home directory).

Description=My PostgreSQL data source

Please note the IANAAppCodePage parameter (see the official documentation), the value "106" means UTF-8 (usually correct for Linux).

4. Enable the Db2 instance as a federated data server (if not done before).

[db2inst1@toster ~]$ db2 get dbm cfg | grep -i federated
 Federated Database System Support           (FEDERATED) = NO
 Bypass federated authentication            (FED_NOAUTH) = NO
 Maximum Asynchronous TQs per query    (FEDERATED_ASYNC) = 0
[db2inst1@toster ~]$ db2 update dbm cfg using federated yes
[db2inst1@toster ~]$

To activate the FEDERATED parameter you will need to restart the Db2 instance.
You can postpone the restart until step 4 is completed.

5. Set the ODBCINI environment variable to point to the created odbc.ini file

.bash_profile or .profile:
        export ODBCINI


Please note that ODBCINI needs to be configured both as the environment variable and in the db2dj.ini configuration file.
You need to restart the Db2 instance (db2stop, db2start) after making this change. Don't forget to re-login, so that the environment variable will be in effect.

6. Connect to the Db2 database which will be used to access the federated data sources, and set up the connection to the PostgreSQL server.

[zinal@toster ~]$
[zinal@toster ~]$ su - db2inst1
Последний вход в систему:Ср дек 27 10:58:20 MSK 2017на pts/8
[db2inst1@toster ~]$
[db2inst1@toster ~]$ db2 connect to zodak

   Database Connection Information

 Database server        = DB2/LINUXX8664
 SQL authorization ID   = DB2INST1
 Local database alias   = ZODAK

[db2inst1@toster ~]$ db2 -t
(c) Copyright IBM Corporation 1993,2007
Command Line Processor for DB2 Client

<...skipped some lines...>

db2 => create server pgserver1 type POSTGRESQL version 9.2
    authorization "user1" password "z0mbiE"

DB20000I  The SQL command completed successfully.
db2 =>
db2 => create user mapping for db2inst1 server pgserver1
    options (REMOTE_AUTHID 'user1', REMOTE_PASSWORD 'z0mbiE');
DB20000I  The SQL command completed successfully.
db2 =>

CREATE SERVER command creates the connection to PostgreSQL server, configured as "MYPGDB" in the odbc.ini file. The created server connection is named pgserver1.

The PUSHDOWN and DB2_MAXIMAL_PUSHDOWN options usually allow for better processing. Db2 most times does not have enough information to estimate the query execution cost at the remote server, and it is usually beneficial to minimize the amount of data flying between Db2 and PostgreSQL, by allowing as much query pushdown as possible.

CREATE USER MAPPING command configures the username and password which are used to authenticate against the PostgreSQL server in cases when the server connection is accessed by the instance owner (db2inst1 user).

7. Test the created connection, by executing simple queries against the remote PostgreSQL.

db2 => set passthru pgserver1;
DB20000I  The SQL command completed successfully.
db2 => select cast(table_schema as varchar(20)), count(*) from information_schema.tables group by table_schema;

table_schema         count
-------------------- ---------------------
information_schema                     60,
demo                                    1,
pg_catalog                             90,
public                                  7,

  4 record(s) selected.

db2 => set passthru reset;
DB20000I  The SQL command completed successfully.
db2 =>

SET PASSTHRU command switches the current Db2 session to the mode in which it sends all the commands to the federated data source, which is specified as the command parameter. SET PASSTHRU RESET switches back to the normal mode.
CAST operator in the SELECT statement is needed for proper formatting of output.

8. Create nicknames for the tables in the federated data source, to access those tables by local names.

db2 => create type mapping pg_sql_id from sysibm.varchar(100) to server pgserver1 remote type "sql_identifier"();
DB20000I  The SQL command completed successfully.
db2 => create type mapping pg_char_data from sysibm.varchar(100) to server pgserver1 remote type "character_data"();
DB20000I  The SQL command completed successfully.
db2 => create type mapping pg_yes_no from sysibm.varchar(3) to server pgserver1 remote type "yes_or_no"();
DB20000I  The SQL command completed successfully.
db2 => create nickname demo.pgserver1_tables for pgserver1."information_schema"."tables";
DB20000I  The SQL command completed successfully.
db2 => describe table demo.pgserver1_tables;

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
TABLE_CATALOG                   SYSIBM    VARCHAR                    100     0 Yes
TABLE_SCHEMA                    SYSIBM    VARCHAR                    100     0 Yes
TABLE_NAME                      SYSIBM    VARCHAR                    100     0 Yes
TABLE_TYPE                      SYSIBM    VARCHAR                    100     0 Yes
SELF_REFERENCING_COLUMN_NAME    SYSIBM    VARCHAR                    100     0 Yes
REFERENCE_GENERATION            SYSIBM    VARCHAR                    100     0 Yes
USER_DEFINED_TYPE_CATALOG       SYSIBM    VARCHAR                    100     0 Yes
USER_DEFINED_TYPE_SCHEMA        SYSIBM    VARCHAR                    100     0 Yes
USER_DEFINED_TYPE_NAME          SYSIBM    VARCHAR                    100     0 Yes
IS_INSERTABLE_INTO              SYSIBM    VARCHAR                      3     0 Yes
IS_TYPED                        SYSIBM    VARCHAR                      3     0 Yes
COMMIT_ACTION                   SYSIBM    VARCHAR                    100     0 Yes

  12 record(s) selected.

db2 =>

CREATE TYPE MAPPING commands are required to map custom types created on the PostgreSQL side to the Db2 types. Note the double quotes around the PostgreSQL type names - those are important, because without the quotes those types would be automatically converted to the upper case. Please also note the round brackets after the type name - those are also required.

CREATE NICKNAME command defines the table residing in the PostgreSQL database to Db2. It uses the existing type mappings to define the column types in the Db2 catalog.

9. Use the just defined nickname to write a query combining data from local and remote tables.

db2 => select cast(tabname as varchar(20)), source from (
db2 (cont.) => (select tabschema, tabname, cast('Db2' as varchar(10)) as source from syscat.tables)
db2 (cont.) => union all
db2 (cont.) => (select table_schema as tabschema, table_name as tabname, 
db2 (cont.) =>     cast('PgSql' as varchar(10)) as source from demo.pgserver1_tables) ) x
db2 (cont.) => where tabname like 'A%';

1                    SOURCE
-------------------- ----------
ABC7XYZ              PgSql
ABC1XYZ              PgSql
ABC2XYZ              PgSql
ABC3XYZ              PgSql
ABC4XYZ              PgSql
ABC5XYZ              PgSql
ABC6XYZ              PgSql
ATTRIBUTES           Db2
AUDITUSE             Db2

  17 record(s) selected.

db2 =>

In the query above we get the list of tables with names starting with letter 'A' from system catalogs of Db2 and PostgreSQL.