Db2

Db2

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only

  • 1.  unixODBC support for NULL values on Linux

    Posted Fri August 21, 2020 12:19 PM
    Edited by System Admin Fri January 20, 2023 04:50 PM
    Hello,

    I have a problem returning NULL values to an unixODBC connection.

    Configuration :
    Up to date Ubuntu 20.04
    DB2 11.5.5.0 beta
    unixODBC 2.3.7 packaged in Ubuntu 20.04 distribution:

    yae@t590:~$ odbcinst -j
    unixODBC 2.3.7
    DRIVERS............: /etc/odbcinst.ini
    SYSTEM DATA SOURCES: /etc/odbc.ini
    FILE DATA SOURCES..: /etc/ODBCDataSources
    USER DATA SOURCES..: /home/yae/.odbc.ini
    SQLULEN Size.......: 8
    SQLLEN Size........: 8
    SQLSETPOSIROW Size.: 8
    yae@t590:~$ cat /etc/odbcinst.ini
    [Db2]
    Description = Db2 Driver
    Driver = /home/db2inst1/sqllib/lib/libdb2o.so
    fileusage=1
    dontdlclose=1

    yae@t590:~$ cat /etc/odbc.ini
    yae@t590:~$ cat /home/yae/.odbc.ini
    [ODBC Data Sources]
    SAMPLE = Db2 11.5 Driver

    [SAMPLE]
    Driver = /home/db2inst1/sqllib/lib/libdb2.so <-- should be libdb2o.so
    Description = Sample Db2 ODBC Database

    And now, testing with isql, an interactive command line tool included in unixodbc package:

    yae@t590:~$ isql SAMPLE
    +---------------------------------------+
    | Connected! |
    | |
    | sql-statement |
    | help [tablename] |
    | quit |
    | |
    +---------------------------------------+
    SQL> select ID, COMM from DB2INST1.STAFF where ID<100
    +-------+----------+
    | ID | COMM |
    +-------+----------+
    | 10 | 10 |
    | 20 | 612,45 |
    | 30 | 30 |
    | 40 | 846,55 |
    | 50 | 50 |
    | 60 | 650,25 |
    | 70 | 1152,00 |
    | 80 | 128,20 |
    | 90 | 1386,70 |
    +-------+----------+
    SQLRowCount returns -1
    9 rows fetched
    SQL> select ID, COMM from DB2INST1.STAFF where ID<100 and COMM is NOT NULL
    +-------+----------+
    | ID | COMM |
    +-------+----------+
    | 20 | 612,45 |
    | 40 | 846,55 |
    | 60 | 650,25 |
    | 70 | 1152,00 |
    | 80 | 128,20 |
    | 90 | 1386,70 |
    +-------+----------+
    SQLRowCount returns -1
    6 rows fetched
    SQL> quit
    yae@t590:~$

    It looks like the value of ID is returned for COMM where COMM values are NULL.

    Did I miss anything in the configuration ?

    Thanks for your help!
    Regards.

    ------------------------------
    Yves-Antoine Emmanuelli
    ------------------------------
    #Db2
    #Db2EarlyAccessProgram(EAP)Forum


  • 2.  RE: unixODBC support for NULL values on Linux

    Posted Mon August 24, 2020 02:31 PM
    Edited by System Admin Fri January 20, 2023 04:24 PM
    Hi @Yves-Antoine Emmanuelli
    ​
    Please note that Ubuntu 20 supported OS in Db2 11.5.x stream. Also can you please provide your db2level.
    Regards:
    Mojgan Samifanni
    ------------------------------
    MOJGAN SAMIFANNI
    ------------------------------

    Original Message


  • 3.  RE: unixODBC support for NULL values on Linux

    Posted Mon August 24, 2020 05:17 PM
    Hello Mojgan,

    My test was done on latest beta:

    yae@t590:~$ db2level
    DB21085I This instance or install (instance name, where applicable:
    "db2inst1") uses "64" bits and DB2 code release "SQL11055" with level
    identifier "0606010F".
    Informational tokens are "DB2 v11.5.5.0", "s2006111000", "DYN2006111000AMD64",
    and Fix Pack "0".
    Product is installed at "/opt/ibm/db2/V11.5".

    Regards.

    ------------------------------
    Yves-Antoine Emmanuelli
    ------------------------------

    Original Message


  • 4.  RE: unixODBC support for NULL values on Linux

    Posted Sat August 29, 2020 09:15 AM
    ... and if you specify only COMMin the result set:

    SQL> select COMM from DB2INST1.STAFF where COMM is NULL
    +----------+
    | COMM |
    +----------+
    | PQU |
    | PQU |
    | PQU |
    | PQU |
    | PQU |
    | PQU |
    | PQU |
    | PQU |
    | PQU |
    | PQU |
    | PQU |
    +----------+
    SQLRowCount returns -1
    11 rows fetched

    ------------------------------
    Yves-Antoine Emmanuelli
    ------------------------------

    Original Message


  • 5.  RE: unixODBC support for NULL values on Linux

    Posted Wed September 02, 2020 12:57 PM
    Hello @MOJGAN SAMIFANNI,

    With the latest image (2020-09-01), the last query (returning COMM only) returns a slightly different result:
    ​SQL> select COMM from DB2INST1.STAFF where COMM is NULL
    +----------+
    | COMM |
    +----------+
    | �l� |
    | �l� |
    | �l� |
    | �l� |
    | �l� |
    | �l� |
    | �l� |
    | �l� |
    | �l� |
    | �l� |
    | �l� |
    +----------+
    SQLRowCount returns -1
    11 rows fetched

    Regards.

    ------------------------------
    Yves-Antoine Emmanuelli
    ------------------------------

    Original Message


  • 6.  RE: unixODBC support for NULL values on Linux

    Posted Wed September 02, 2020 01:27 PM

    Hi @Yves-Antoine Emmanuelli,

    Can you please also provide the OS level for Ubuntu that you ate testing on?

    ​​Original note mentions Unbutu 20.04 and unfortunately this is not supported OS level yet by Db2.



    ------------------------------
    MOJGAN SAMIFANNI
    ------------------------------

    Original Message


  • 7.  RE: unixODBC support for NULL values on Linux

    Posted Wed September 02, 2020 05:19 PM
    Hello @MOJGAN SAMIFANNI,

    ​yae@t590:~$ uname -a
    Linux t590 5.4.0-42-generic #46-Ubuntu SMP Fri Jul 10 00:24:02 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux

    This is an LTS (long term support) version, which should be soon be supported by Db2, if we refer to history :-).
    db2prereqcheck only checks for a minimal version of the operating system.
    More seriously the issue is due to the Db2 ODBC driver or unixODBC driver manager.
    I can help you narrow the problem, if you share how to configure tracing so that you can figure out where the issue is.

    Regards.


    Thanks.

    ------------------------------
    Yves-Antoine Emmanuelli
    ------------------------------

    Original Message


  • 8.  RE: unixODBC support for NULL values on Linux

    Posted Thu September 03, 2020 06:34 AM
      |   view attached
    Hello,

    I have traced the execution :
    • db2inst1.log contains commands executed  by instance owner, from instance start to trace commands.
    • isql.log contains application execution between db2trc on and dump commands.
    • isql.trc contains the formatted trace.
    • db2diag.log contains the diag log since db2start: it looks like there is no message related to the trace.
    I can see erroneous time elapsed values (negative and sometimes huge absolute value) in isql.trc
    Which version(s) of unixODBC do you support?

    Thanks.

    ------------------------------
    Yves-Antoine Emmanuelli
    ------------------------------

    Attachment(s)

    zip
    isqltest.zip   8 KB 1 version
    Original Message


  • 9.  RE: unixODBC support for NULL values on Linux

    Posted Thu September 03, 2020 11:21 AM
    Hello,

    @Bimal Jha has pointed out an error in my configuration file /home/yae/.odbc.ini which should be:

    ​[ODBC Data Sources]
    SAMPLE = Db2 11.5 Driver

    [SAMPLE]
    Driver = /home/db2inst1/sqllib/lib/libdb2o.so
    Description = Sample Db2 ODBC Database

    libdb2o.so instead of libdb2.so, as specified in
    https://www.ibm.com/support/knowledgecenter/SSEPGG_11.5.0/com.ibm.db2.luw.apdv.cli.doc/doc/t0006349.html

    Thanks!

    ------------------------------
    Yves-Antoine Emmanuelli
    ------------------------------

    Original Message