Topic
  • 2 replies
  • Latest Post - ‏2013-10-23T14:54:39Z by vincponcet
vincponcet
vincponcet
2 Posts

Pinned topic Connecting to a remote DB2 ?

‏2013-10-21T15:30:04Z |

Hello,

I'm trying to use ODBC operators to connect to a remote DB2 without success.

When I run my job doing a select with ODBCSource, I get no result and the PE is in unhealthy state.

ODBCHelper :

[streamsadmin@streamslab odbchelper]$ ./odbchelper testconnection -i VINCEDB -u DB2ADMIN -p IBMDem0s
Error on SQLConnect.
Error message #1:
sqlState = IM004
sqlCode = 0
sqlMsg = [unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_HENV failed

DB2Helper

[streamsadmin@streamslab db2helper]$ ./db2helper testconnection -i VINCEDB -u DB2ADMIN -p IBMDem0s
Error on SQLAllocEnv.

My job:

amespace com.ibm.poc.sncf ;

use com.ibm.streams.db::ODBCSource ;
use com.ibm.streams.db::ODBCEnrich ;

composite Main
{
    graph
        () as FileSink_1 = FileSink(ODBCSource_2_out0)
        {
            param
                file : "output.txt" ;
        }

        (stream<rstring arg1, rstring arg2> ODBCSource_2_out0) as ODBCSource_2 =
            ODBCSource()
        {
            param
                connection : "SenTestConnection" ;
                access : "readFromVincent" ;
        }

}

connections.xml

<?xml version="1.0" encoding="UTF-8"?>

<st:connections xmlns:st="http://www.ibm.com/xmlns/prod/streams/adapters"
                xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <connection_specifications>
    <!-- Here, you must substitute the DB name, DB user id, and DB password that are meaningful to your needs. -->
    <connection_specification name="SenTestConnection">
      <ODBC database="VINCEDB" user="DB2ADMIN" password="IBMDem0s"/>
    </connection_specification>
  </connection_specifications>

  <access_specifications>
    <!-- This access specification is used by the ODBCSource operator in the application -->
    <access_specification name="readFromVincent">
      <query query="select arg1, arg2 from vincent" />
      <uses_connection connection="SenTestConnection" />

      <native_schema>
           <column name="id" type="INTEGER" />
            <column name="arg1" type="VARCHAR" length="250" />
            <column name="arg2" type="VARCHAR" length="250" />
        </native_schema>
    </access_specification>

  </access_specifications>
</st:connections>
 

I installed DB2 V10.5FP2 Data Server Client from fixCentral : v10.5fp2_linuxx64_client.tar.gz

https://www-933.ibm.com/support/fixcentral/swg/selectFixes?parent=ibm/Information+Management&product=ibm/Information+Management/IBM+Data+Server+Client+Packages&release=10.5.*&platform=Linux+64-bit,x86_64&function=fixId&fixids=*-client-*FP002&includeSupersedes=0

/home/streamsadmin/.odbc.ini

[DEFAULT]
Driver = DB2

[VINCEDB]
DESCRIPTION = Connection to DB2
DRIVER = DB2

/home/streamsadmin/.odbcinst.ini

[DB2]
Description     = DB2 Driver
Driver          = /opt/ibm/db2/V10.5/lib64/libdb2.so
FileUsage       = 1
DontDLClose     = 1

/opt/ibm/db2/V10.5/cfg/db2cli.ini

[VINCEDB]
Database=VINCEDB
Protocol=TCPIP
Hostname=9.128.133.193
ServiceName=50000

in my .bashrc

export STREAMS_ADAPTERS_ODBC_DB2=1
export STREAMS_ADAPTERS_ODBC_INCPATH=/opt/ibm/db2/V10.5/include/
export STREAMS_ADAPTERS_ODBC_LIBPATH=/opt/ibm/db2/V10.5/lib64/

export STREAMS_ADAPTERS_ODBC_DB2 STREAMS_ADAPTERS_ODBC_INCPATH STREAMS_ADAPTERS_ODBC_LIBPATH

export DB2INSTANCE=db2inst1
export ODBCINI=/home/streamsadmin/.odbc.ini

I did

streamtool setproperty -i LabInstance  DNA.backDoorEvs=DB2INSTANCE=db2inst1,ODBCINI=/home/streamsadmin/.odbc.ini
 

Thanks for any insights,

Vincent.

  • Ivan_CA
    Ivan_CA
    10 Posts
    ACCEPTED ANSWER

    Re: Connecting to a remote DB2 ?

    ‏2013-10-21T21:56:33Z  

    Hello Vincent,

     

    I  think I have duplicated your error/scenario. The cause (at least in my case) is that there was not db2 client instance created. Please see below:

     

    [streams@v525400bd67b6 db2helper]$ env|grep STREAMS
    STREAMS_ADAPTERS_DB2_LIBPATH=/opt/ibm/db2/V10.5/lib64
    STREAMS_DEFAULT_IID=streams
    STREAMS_ADAPTERS_ODBC_INCPATH=/opt/ibm/db2/V10.5/include
    STREAMS_ADAPTERS_ODBC_LIBPATH=/opt/ibm/db2/V10.5/lib64
    STREAMS_ADAPTERS_DB2_INCPATH=/opt/ibm/db2/V10.5/include
    STREAMS_INSTALL=/home/streams/InfoSphereStreams
    STREAMS_ADAPTERS_ODBC_DB2=1
    [streams@v525400bd67b6 db2helper]$ ./db2helper testconnection -i AAA -u BBB -p CCC
    Error on SQLAllocEnv.

    **another session as root
    [root@v525400bd67b6 instance]# ./db2icrt db2cli1
    DBI1446I  The db2icrt command is running.
    <snip>
    DBI1070I  Program db2icrt completed successfully.
    **end another session as root

    *The error here already changes from SQLAllocEnv error to no directory found
    [streams@v525400bd67b6 db2helper]$ ./db2helper testconnection -i AAA -u BBB -p CCC
    Error on SQLConnect.
    sqlState = 58031
    sqlCode = -1031
    sqlMsg = [IBM][CLI Driver] SQL1031N  The database directory cannot be found on the indicated file system.  SQLSTATE=58031

     

    **Another session as db2cli1

    [db2cli1@v525400bd67b6 ~]$ db2 catalog tcpip node NODE1 remote XXX  server 50000
    DB20000I  The CATALOG TCPIP NODE command completed successfully.
    DB21056W  Directory changes may not be effective until the directory cache is
    refreshed.
    [db2cli1@v525400bd67b6 ~]$ db2 catalog db AAA at node NODE1
    DB20000I  The CATALOG DATABASE command completed successfully.
    DB21056W  Directory changes may not be effective until the directory cache is
    refreshed.

    ** End another session as db2cli1

     

    *And finally it works.

    [streams@v525400bd67b6 db2helper]$ ./db2helper testconnection -i AAA -u BBB -p CCC
    Connection successful.

     

     

    So could you please create a client db2 instance, catalog the server info and try again? Let me know how it goes.

     

    Thanks!

    Ivan

  • Ivan_CA
    Ivan_CA
    10 Posts

    Re: Connecting to a remote DB2 ?

    ‏2013-10-21T21:56:33Z  

    Hello Vincent,

     

    I  think I have duplicated your error/scenario. The cause (at least in my case) is that there was not db2 client instance created. Please see below:

     

    [streams@v525400bd67b6 db2helper]$ env|grep STREAMS
    STREAMS_ADAPTERS_DB2_LIBPATH=/opt/ibm/db2/V10.5/lib64
    STREAMS_DEFAULT_IID=streams
    STREAMS_ADAPTERS_ODBC_INCPATH=/opt/ibm/db2/V10.5/include
    STREAMS_ADAPTERS_ODBC_LIBPATH=/opt/ibm/db2/V10.5/lib64
    STREAMS_ADAPTERS_DB2_INCPATH=/opt/ibm/db2/V10.5/include
    STREAMS_INSTALL=/home/streams/InfoSphereStreams
    STREAMS_ADAPTERS_ODBC_DB2=1
    [streams@v525400bd67b6 db2helper]$ ./db2helper testconnection -i AAA -u BBB -p CCC
    Error on SQLAllocEnv.

    **another session as root
    [root@v525400bd67b6 instance]# ./db2icrt db2cli1
    DBI1446I  The db2icrt command is running.
    <snip>
    DBI1070I  Program db2icrt completed successfully.
    **end another session as root

    *The error here already changes from SQLAllocEnv error to no directory found
    [streams@v525400bd67b6 db2helper]$ ./db2helper testconnection -i AAA -u BBB -p CCC
    Error on SQLConnect.
    sqlState = 58031
    sqlCode = -1031
    sqlMsg = [IBM][CLI Driver] SQL1031N  The database directory cannot be found on the indicated file system.  SQLSTATE=58031

     

    **Another session as db2cli1

    [db2cli1@v525400bd67b6 ~]$ db2 catalog tcpip node NODE1 remote XXX  server 50000
    DB20000I  The CATALOG TCPIP NODE command completed successfully.
    DB21056W  Directory changes may not be effective until the directory cache is
    refreshed.
    [db2cli1@v525400bd67b6 ~]$ db2 catalog db AAA at node NODE1
    DB20000I  The CATALOG DATABASE command completed successfully.
    DB21056W  Directory changes may not be effective until the directory cache is
    refreshed.

    ** End another session as db2cli1

     

    *And finally it works.

    [streams@v525400bd67b6 db2helper]$ ./db2helper testconnection -i AAA -u BBB -p CCC
    Connection successful.

     

     

    So could you please create a client db2 instance, catalog the server info and try again? Let me know how it goes.

     

    Thanks!

    Ivan

  • vincponcet
    vincponcet
    2 Posts

    Re: Connecting to a remote DB2 ?

    ‏2013-10-23T14:54:39Z  
    • Ivan_CA
    • ‏2013-10-21T21:56:33Z

    Hello Vincent,

     

    I  think I have duplicated your error/scenario. The cause (at least in my case) is that there was not db2 client instance created. Please see below:

     

    [streams@v525400bd67b6 db2helper]$ env|grep STREAMS
    STREAMS_ADAPTERS_DB2_LIBPATH=/opt/ibm/db2/V10.5/lib64
    STREAMS_DEFAULT_IID=streams
    STREAMS_ADAPTERS_ODBC_INCPATH=/opt/ibm/db2/V10.5/include
    STREAMS_ADAPTERS_ODBC_LIBPATH=/opt/ibm/db2/V10.5/lib64
    STREAMS_ADAPTERS_DB2_INCPATH=/opt/ibm/db2/V10.5/include
    STREAMS_INSTALL=/home/streams/InfoSphereStreams
    STREAMS_ADAPTERS_ODBC_DB2=1
    [streams@v525400bd67b6 db2helper]$ ./db2helper testconnection -i AAA -u BBB -p CCC
    Error on SQLAllocEnv.

    **another session as root
    [root@v525400bd67b6 instance]# ./db2icrt db2cli1
    DBI1446I  The db2icrt command is running.
    <snip>
    DBI1070I  Program db2icrt completed successfully.
    **end another session as root

    *The error here already changes from SQLAllocEnv error to no directory found
    [streams@v525400bd67b6 db2helper]$ ./db2helper testconnection -i AAA -u BBB -p CCC
    Error on SQLConnect.
    sqlState = 58031
    sqlCode = -1031
    sqlMsg = [IBM][CLI Driver] SQL1031N  The database directory cannot be found on the indicated file system.  SQLSTATE=58031

     

    **Another session as db2cli1

    [db2cli1@v525400bd67b6 ~]$ db2 catalog tcpip node NODE1 remote XXX  server 50000
    DB20000I  The CATALOG TCPIP NODE command completed successfully.
    DB21056W  Directory changes may not be effective until the directory cache is
    refreshed.
    [db2cli1@v525400bd67b6 ~]$ db2 catalog db AAA at node NODE1
    DB20000I  The CATALOG DATABASE command completed successfully.
    DB21056W  Directory changes may not be effective until the directory cache is
    refreshed.

    ** End another session as db2cli1

     

    *And finally it works.

    [streams@v525400bd67b6 db2helper]$ ./db2helper testconnection -i AAA -u BBB -p CCC
    Connection successful.

     

     

    So could you please create a client db2 instance, catalog the server info and try again? Let me know how it goes.

     

    Thanks!

    Ivan

    You're right. I didn't have a DB2 instance.

    after db2icrt, my db2helper/odbchelper connection works, and my streams project too.

     

    Thanks !