Topic
1 reply Latest Post - ‏2013-07-08T16:52:44Z by Stan
ENER_Faber_McMullen
ENER_Faber_McMullen
18 Posts
ACCEPTED ANSWER

Pinned topic Error while running a Streams app that reads data from Informix

‏2013-07-02T22:00:49Z |

Hello everyone,
I have been trying to run a Streams app. I followed the instructions at this page under the section "Setting up for the IBM Common Driver".
http://www.ibm.com/developerworks/library/bd-streamsinformix/
It compiles fine. But, I keep getting an error. Here is the SPL.

use com.ibm.streams.db::* ; // make operators from the database toolkit available
composite Main
{
    graph
        stream<rstring fname> Names = ODBCSource()
        { // return a stream called Names
            param
                connection : "test" ; // Identify the connection to use
                access : "customer" ; // identify the statement to execute
                connectionDocument : "connections.xml" ; // identify the connections definition file
        }

        () as FileSink_1 = FileSink(Names)
        {
            param
                file : "output.csv" ;
                format : csv ;
        }
}

Here is connections.xml

<st:connections xmlns:st="http://www.ibm.com/xmlns/prod/streams/adapters"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<connection_specifications>

<connection_specification name="test" >
    <ODBC database="test" user="informix" password="***" />
</connection_specification>

</connection_specifications>

<access_specifications>

<access_specification name="customer">
    <query query="SELECT fname FROM customer" replays="1" isolation_level="READ_COMMITTED" />
    <parameters></parameters>
    <uses_connection connection="test" />
    <native_schema>
        <column name="fname" type="CHAR" length="15"/>
    </native_schema>
</access_specification>

</access_specifications>

</st:connections>

Here are the environment variables.

export INFORMIXDIR=/opt/ibm/informix
export INFORMIXSERVER=linuxvm
export ONCONFIG=onconfig
export INFORMIXSQLHOSTS=$INFORMIXDIR/etc/sqlhosts
export STREAMS_ADAPTERS_ODBC_DB2=1
export STREAMS_ADAPTERS_ODBC_INCPATH=$HOME/dsdriver/include
export STREAMS_ADAPTERS_ODBC_LIBPATH=$HOME/dsdriver/lib
export DB2COMM=TCPIP

Here is the output I get from running the app in Streams Studio:

02 Jul 2013 16:49:24.237 [2900] ERROR #splapplog,J[0],P[0],Names,spl_operator M[Names.cpp:establishODBCConnection:314]  - An error occurred while the system attempted an SQL operation.
02 Jul 2013 16:49:24.240 [2900] ERROR #splapptrc,J[0],P[0],Names,spl_operator M[Names.cpp:establishODBCConnection:315]  - Failed to connect to database with return code SQL_ERROR
02 Jul 2013 16:49:24.397 [2900] ERROR #splapplog,J[0],P[0],Names,spl_operator M[Names.h:logSQLError:216]  - An SQL operation failed. The SQL state is      , the SQL code is -1531 and the SQL message is [IBM][CLI Driver] SQL1531N  The connection failed because the name specified with the DSN connection string keyword could not be found in either the db2dsdriver.cfg configuration file or the db2cli.ini configuration file.  Data source name specified in the connection string: "TEST".

Any help telling me how to configure this app and Informix to communicate with each other would be appreciated.

  • Stan
    Stan
    76 Posts
    ACCEPTED ANSWER

    Re: Error while running a Streams app that reads data from Informix

    ‏2013-07-08T16:52:44Z  in response to ENER_Faber_McMullen

    I recommend you switch to using the Informix CLI rather than the DB2 common driver.  The Informix CLI is the tested and documented way to access Informix for use with Streams.  The author says "..my tests show.. In addition, you can also use the IBM common driver...".  The problem with using undocumented features is that they are not tested and so could stop working in future releases.  I will look into whether there are plans to support / test the common driver with Informix in the future.  

    That said, the problem identified in the errors is that the DB2 common driver files were not setup properly.  The article mentions these in the section:
        Setting up for the IBM Common Driver

    This variable set that specifies the use of the DB2 driver:
    export STREAMS_ADAPTERS_ODBC_DB2=1

     - instead of
    STREAMS_ADAPTERS_ODBC_IDS=1 (indicates that we are using Informix)

    The error specifying the files that need to be modified:


     the SQL message is [IBM][CLI Driver] SQL1531N  The connection failed because the name specified with the DSN connection string keyword could not be found in either the db2dsdriver.cfg configuration file or the db2cli.ini configuration file.  Data source name specified in the connection string: "TEST".

    Also I notice this variable may (or may not) be truncated in your setup - it ends short of the CLI directory:
    from document:
    STREAMS_ADAPTERS_ODBC_INCPATH=/opt/IBM/informix/dsdriver/incl/cli (where to find include files)
    your setting:
    export STREAMS_ADAPTERS_ODBC_INCPATH=$HOME/dsdriver/include