Content

Question/Answer

Question
How to access data on Microsoft SQL Server/Azure using Db2 Federation Server?
Answer
The Db2 user can use Db2 Federation Server to access data on Microsoft SQL Server/Azure via ODBC driver as following.

Description
Federation can now support on accessing data from Microsoft SQL Server/Azure through ODBC driver.

Data type mapping

data type mapping please refer to link:

Examples

Step 1: create server and create user mapping

a). MSSQL_ODBC type

create server SERVER1 type MSSQL_ODBC version 2016 authorization "MSSQLUSER1" password "password1" OPTIONS(host 'abc.xyz.xxx.com',port '1433',dbname 'mssql2016db1',CODEPAGE '1252', password 'Y', pushdown 'Y')
DB20000I  The SQL command completed successfully.

create user mapping for user server server1 options(REMOTE_AUTHID 'MSSQLUSER1',REMOTE_PASSWORD 'password1')
DB20000I  The SQL command completed successfully.

b). azure type

create server server1 type azure  authorization "AZUREUSER1" password "password1" OPTIONS(host 'abc.xyz.xxx.com',port '1433',dbname 'testdb1',CODEPAGE '1252', password 'Y',  pushdown 'Y')
DB20000I  The SQL command completed successfully.

create user mapping for user server server1 options(REMOTE_AUTHID 'AZUREUSER1',REMOTE_PASSWORD 'password1')
DB20000I  The SQL command completed successfully.

Step  2: set passthru to create table (if the table and data already exist on remote data source please ignore this step)

set passthru SERVER1
DB20000I  The SQL command completed successfully.

drop table testodbcfull
DB20000I  The SQL command completed successfully.

create TABLE testodbcfull( col_decimal DECIMAL(30,8), col_varchar VARCHAR(10), col_char CHAR(10), col_int INT, col_smallint SMALLINT, col_tinyint TINYINT, col_bigint BIGINT, col_date DATE, col_real real, col_binary BINARY(10), col_float FLOAT )
DB20000I  The SQL command completed successfully.

insert into testodbcfull values(1344.78,'vchar','ch',100,100,100,13213.,'2013-06-07',1545.00,0x1122a,1545.00)
DB20000I  The SQL command completed successfully.

set passthru reset
DB20000I  The SQL command completed successfully.

Step 3: create nickname

drop nickname nk1
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0204N  "BJGUOHUI.NK1" is an undefined name.  SQLSTATE=42704

drop nickname nk2
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0204N  "BJGUOHUI.NK2" is an undefined name.  SQLSTATE=42704

create nickname nk1 for "SERVER1"."J15USER2"."testodbcfull"
DB20000I  The SQL command completed successfully.

insert into nk1 values(2344.78,'vchar','ch',200,200,100,23213.,'2014-06-07',2545.00,bx'01122b',2545.00)
DB20000I  The SQL command completed successfully.

select  * from nk1

COL_DECIMAL                      COL_VARCHAR COL_CHAR   COL_INT     COL_SMALLINT COL_TINYINT COL_BIGINT           COL_DATE   COL_REAL                 COL_BINARY              COL_FLOAT
-------------------------------- ----------- ---------- ----------- ------------ ----------- -------------------- ---------- ------------------------ ----------------------- ------------------------
                   1344.78000000 vchar       ch                 100          100         100                13213 06/07/2013            +1.54500E+003 x'01122A00000000000000'   +1.54500000000000E+003
                   2344.78000000 vchar       ch                 200          200         100                23213 06/07/2014            +2.54500E+003 x'01122B00000000000000'   +2.54500000000000E+003

  2 record(s) selected.

describe table nk1

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
COL_DECIMAL                     SYSIBM    DECIMAL                     30     8 Yes
COL_VARCHAR                     SYSIBM    VARCHAR                     10     0 Yes
COL_CHAR                        SYSIBM    CHARACTER                   10     0 Yes
COL_INT                         SYSIBM    INTEGER                      4     0 Yes
COL_SMALLINT                    SYSIBM    SMALLINT                     2     0 Yes
COL_TINYINT                     SYSIBM    SMALLINT                     2     0 Yes
COL_BIGINT                      SYSIBM    BIGINT                       8     0 Yes
COL_DATE                        SYSIBM    DATE                         4     0 Yes
COL_REAL                        SYSIBM    REAL                         4     0 Yes
COL_BINARY                      SYSIBM    BINARY                      10     0 Yes
COL_FLOAT                       SYSIBM    DOUBLE                       8     0 Yes

  11 record(s) selected.

TIPs: Some data types mapping

1. sql_variant: to support data type sql_variant,  you will need to alter the server with the following command.

alter server SERVER1 OPTIONS(add CUSTOMIZED_ODBC_ATTRIBUTES ';DataTypeCompatibility=0')

you can use the following command to drop server option.

alter server SERVER1 OPTIONS(drop CUSTOMIZED_ODBC_ATTRIBUTES)

2. datetime2:   add an server option:"DATETIME2_ENABLE" to enhance datetime2 support.

If without server option :" DATETIME2_ENABLE", datetime2 will map to db2 TIMESTAMP(10,6),  it will lose precision when the scale of datetime2 is bigger than 6.

If enable "DATETIME2_ENABLE", datetime2(n) will according to the value of n to map to TIMESTAMP.

example of enable datetime2:

alter server server1 OPTIONS(add DATETIME2_ENABLE 'Y')
DB20000I  The SQL command completed successfully.

alter server server1 OPTIONS(set DATETIME2_ENABLE 'Y')
DB20000I  The SQL command completed successfully.

set passthru server1
DB20000I  The SQL command completed successfully.

drop table testdt2
DB20000I  The SQL command completed successfully.

create table testdt2(dt2n datetime2, dt1 datetime2(1), dt2 datetime2(2),dt3 datetime2(3),dt4 datetime2(4),dt5 datetime2(5), dt6 datetime2(6), dt7 datetime2(7), primary key (dt3))
DB20000I  The SQL command completed successfully.

insert into testdt2 values('2018-10-10 23:59:59.9999999', '2018-10-10 23:59:59.9999999','2018-10-10 23:59:59.9999999','2018-10-10 23:59:59.9999999','2018-10-10 23:59:59.9999999','2018-10-10 23:59:59.9999999','2018-10-10 23:59:59.9999999','2018-10-10 23:59:59.9999999')
DB20000I  The SQL command completed successfully.

select * from testdt2  order by 1

dt2n                        dt1                        dt2                        dt3                        dt4                        dt5                        dt6                        dt7
--------------------------- -------------------------- -------------------------- -------------------------- -------------------------- -------------------------- -------------------------- ---------------------------
2018-10-10-23.59.59.9999999 2018-10-11-00.00.00.000000 2018-10-11-00.00.00.000000 2018-10-11-00.00.00.000000 2018-10-11-00.00.00.000000 2018-10-11-00.00.00.000000 2018-10-11-00.00.00.000000 2018-10-10-23.59.59.9999999

1 record(s) selected.

set passthru reset
DB20000I  The SQL command completed successfully.

drop nickname nk1
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0204N  "BJGUOHUI.NK1" is an undefined name.  SQLSTATE=42704

create nickname nk1 for server1."MSSQLUSER1"."testdt2"
DB20000I  The SQL command completed successfully.

delete from nk1
DB20000I  The SQL command completed successfully.

insert into nk1 values('2018-10-10 23:59:59.9999999', '2018-10-10 23:59:59.9999999','2018-10-10 23:59:59.9999999','2018-10-10 23:59:59.9999999','2018-10-10 23:59:59.9999999','2018-10-10 23:59:59.9999999','2018-10-10 23:59:59.9999999','2018-10-10 23:59:59.9999999')
DB20000I  The SQL command completed successfully.

update nk1 set dt2n = '2018-10-10 23:59:59.1111119' where dt1='2018-10-10 23:59:59.9'
DB20000I  The SQL command completed successfully.

select * from nk1 where dt1='2018-10-10 23:59:59.9'  order by 1

DT2N                        DT1                   DT2                    DT3                     DT4                      DT5                       DT6                        DT7
--------------------------- --------------------- ---------------------- ----------------------- ------------------------ ------------------------- -------------------------- ---------------------------
2018-10-10-23.59.59.1111119 2018-10-10-23.59.59.9 2018-10-10-23.59.59.99 2018-10-10-23.59.59.999 2018-10-10-23.59.59.9999 2018-10-10-23.59.59.99999 2018-10-10-23.59.59.999999 2018-10-10-23.59.59.9999999

  1 record(s) selected.

describe table nk1

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
DT2N                            SYSIBM    TIMESTAMP                   11     7 Yes
DT1                             SYSIBM    TIMESTAMP                    8     1 Yes
DT2                             SYSIBM    TIMESTAMP                    8     2 Yes
DT3                             SYSIBM    TIMESTAMP                    9     3 No
DT4                             SYSIBM    TIMESTAMP                    9     4 Yes
DT5                             SYSIBM    TIMESTAMP                   10     5 Yes
DT6                             SYSIBM    TIMESTAMP                   10     6 Yes
DT7                             SYSIBM    TIMESTAMP                   11     7 Yes

  8 record(s) selected.