DB2 - L

 View Only
  • 1.  jcc driver shipped with 11.5.5, JDBC 4.1 or 4.3?

    Posted Nov 23, 2020 01:58 PM
    From my understanding, the driver shipped with 11.5.5. should support JDBC 4.3 (with an exception for things added in JAVA 9, https://www.ibm.com/support/knowledgecenter/SSEPGG_11.5.0/com.ibm.db2.luw.wn.doc/doc/c_ds_clients_drivers_11-5-5.html ). I'm running on a server where db2version is:
    ]$ 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", "s2011011400", "DYN2011011400AMD64",
    and Fix Pack "0".
    Product is installed at "/opt/ibm/db2/V11.5".

    A small test prog reveals that the driver still thinks it is 4.1 and that localDate is not supported.
    import java.lang.*;
    import java.sql.*;
    import java.time.LocalDate;


    class Dummy
    {
    static String user = "*****";
    static String pwd = "*****";

    public static void main(String argv[])
    throws Exception
    {
    String JDBC_DRIVER = "com.ibm.db2.jcc.DB2Driver";
    String DB_URL = "jdbc:db2://localhost:50000/SAMPLE";

    Connection conn = null;
    Statement stmt = null;

    Class.forName(JDBC_DRIVER);
    conn = DriverManager.getConnection(DB_URL,user,pwd);

    DatabaseMetaData metaData = conn.getMetaData();

    int x = metaData.getJDBCMajorVersion();
    int y = metaData.getJDBCMinorVersion();

    System.out.println(x);
    System.out.println(y);

    stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery("values current_date");
    rs.next();
    System.out.println(rs.getDate(1));

    rs = stmt.executeQuery("values current_date");
    rs.next();
    System.out.println(rs.getObject(1, LocalDate.class));

    }
    }


    ]$ javac -classpath .:/opt/nya/users/db2inst1/sqllib/java/db2jcc4.jar tryLocalDate.java
    ]$ java -cp .:/opt/nya/users/db2inst1/sqllib/java/db2jcc4.jar Dummy
    4
    1
    2020-11-23
    Exception in thread "main" com.ibm.db2.jcc.am.SqlSyntaxErrorException: [jcc][t4][1092][13956][4.28.11] Invalid data conversion: Wrong result column type for requested conversion. ERRORCODE=-4461, SQLSTATE=42815
    at com.ibm.db2.jcc.am.b7.a(b7.java:810)
    at com.ibm.db2.jcc.am.b7.a(b7.java:66)
    at com.ibm.db2.jcc.am.b7.a(b7.java:133)
    at com.ibm.db2.jcc.am.ResultSet.getObject(ResultSet.java:1978)
    at Dummy.main(tryLocalDate.java:39)


    Someone else managed to use functionality from JDBC 4.3 with driver shipped with 11.5.5?

    ------------------------------
    LennartJonsson
    ITS Umeå University
    ------------------------------


  • 2.  RE: jcc driver shipped with 11.5.5, JDBC 4.1 or 4.3?

    Posted Nov 24, 2020 12:31 AM

    Hi Lennart,

    thanks for testing this! But if I read the KC link between the lines:

    JCC supports new APIs [but not all of them] from JDBC 4.3 specification with this release. JCC driver is built with JDK 1.8 [as it was to complicated for us to switch], so the JDBC 4.3 specification classes added newly in JDK 1.9 or higher are not supported in JCC. Hence, JDBC 4.3 specification support in JCC driver is [something, we've tried, but is still] partial to some extent.

    I'm afraid, the only way to complain against this, is to open an AHA idea. :-/

    So I have opened one for it: https://ibm-data-and-ai.ideas.aha.io/ideas/DB24LUW-I-1074

    Cheers



    ------------------------------
    Roland Schock
    ARS Computer und Consulting GmbH
    ------------------------------



  • 3.  RE: jcc driver shipped with 11.5.5, JDBC 4.1 or 4.3?

    Posted Nov 24, 2020 01:39 AM
    Got my vote straightaway!

    Roy Boxwell

    SOFTWARE ENGINEERING GmbH and SEGUS Inc.
    -Product Development-

    Vagedesstrasse 19
    40479 Dusseldorf/Germany
    Tel. +49 (0)211 96149-675
    Fax +49 (0)211 96149-32
    Email: R.Boxwell@seg.de
    Web http://www.seg.de
    Link zur Datenschutzerklärung

    Software Engineering GmbH
    Amtsgericht Düsseldorf, HRB 37894
    Geschäftsführung: Gerhard Schubert, Ulf Heinrich




  • 4.  RE: jcc driver shipped with 11.5.5, JDBC 4.1 or 4.3?

    Posted Nov 24, 2020 02:01 AM
    I interpret the between lines section as that all of 1.8 (2014 something) should be included. Localdate has been around since the beginning so I had some hopes for it. Anyhow, thanks for opening an aha (and for the link, I'll try to remember where aha is located until next time:-)

    The SqlDate <-> JavaDate mapping is a mess (the latter is the crook) which causes all kind of weird behaviour with timezones and DST, so it would be a relief to be able to get rid of that.

    ------------------------------
    LennartJonssonITS Umeå University
    ------------------------------



  • 5.  RE: jcc driver shipped with 11.5.5, JDBC 4.1 or 4.3?

    Posted Oct 27, 2022 03:52 AM
    I won't pop the champagne just yet, but it seems as if LocalDate is supported in 11.5.8. I pulled the latest db2 container:
    [lejo0004@7540-nya-lejo0004-hemma /]$ docker pull ibmcom/db2
    Using default tag: latest
    latest: Pulling from ibmcom/db2
    
    docker run -itd --name mydb2 --privileged=true -p 50000:50000 -e LICENSE=accept -e DB2INST1_PASSWORD=nisse12 -e DBNAME=testdb -v /data/db/db2:/database ibmcom/db2
    
    [lejo0004@7540-nya-lejo0004-hemma /]$ docker exec -ti mydb2 bash -c "su - db2inst1"
    Last login: Thu Oct 27 07:37:19 UTC 2022
    
    [db2inst1@32096d59ce93 ~]$ db2level
    DB21085I  This instance or install (instance name, where applicable: 
    "db2inst1") uses "64" bits and DB2 code release "SQL11058" with level 
    identifier "0609010F".
    Informational tokens are "DB2 v11.5.8.0", "s2209201700", "DYN2209201700AMD64", 
    and Fix Pack "0".
    Product is installed at "/opt/ibm/db2/V11.5".
    
    ​

    Then recompiled the testprogram with the db2jcc4.jar shipped with 11.5.8
    [lejo0004@7540-nya-lejo0004-hemma tryLocalDate]$ javac -classpath .:db2jcc4.jar tryLocalDate.java
    
    [lejo0004@7540-nya-lejo0004-hemma tryLocalDate]$ java -cp .:db2jcc4.jar Dummy 
    4
    1
    2022-10-27
    2022-10-27
    ​

    AFAIK, it is not mentioned under whats new for 11.5.8 and the Aha is still under concideration but it seems to work now.



    ------------------------------
    Lennart Jonsson
    ITS Umeå University
    ------------------------------



  • 6.  RE: jcc driver shipped with 11.5.5, JDBC 4.1 or 4.3?

    Posted Oct 27, 2022 04:07 AM
    Reason I tested it was that I - think - I heard someone mention it at some session that the java version shipped with 11.5.8 is updated. Still 1.8, but more recent than in 11.5.6 which is what I can compare with:

    11.5.8:
    [db2inst1@32096d59ce93 jdk64]$ pushd $(db2 get dbm cfg | grep JDK_PATH | cut -f2 -d=)
    ~/sqllib/java/jdk64 ~/sqllib/java/jdk64 ~/sqllib/java/jdk64 ~/sqllib/java/jdk64/bin
    [db2inst1@32096d59ce93 jdk64]$ jre/bin/java -version
    java version "1.8.0_331"
    Java(TM) SE Runtime Environment (build 8.0.7.10 - pxa6480sr7fp10-20220505_01(SR7 FP10))
    IBM J9 VM (build 2.9, JRE 1.8.0 Linux amd64-64-Bit Compressed References 20220427_27745 (JIT enabled, AOT enabled)
    OpenJ9   - b15041a
    OMR      - 3671a9f
    IBM      - 1b0232b)
    JCL - 20220504_01 based on Oracle jdk8u331-b09
    [db2inst1@32096d59ce93 jdk64]$ bin/java -version
    java version "1.8.0_331"
    Java(TM) SE Runtime Environment (build 8.0.7.10 - pxa6480sr7fp10-20220505_01(SR7 FP10))
    IBM J9 VM (build 2.9, JRE 1.8.0 Linux amd64-64-Bit Compressed References 20220427_27745 (JIT enabled, AOT enabled)
    OpenJ9   - b15041a
    OMR      - 3671a9f
    IBM      - 1b0232b)
    JCL - 20220504_01 based on Oracle jdk8u331-b09
    ​


    11.5.6:

    [db2inst1@nya-01 ~]$ pushd $(db2 get dbm cfg | grep JDK_PATH | cut -f2 -d=)
    ~/sqllib/java/jdk64 ~
    [db2inst1@nya-01 ~/sqllib/java/jdk64]$ jre/bin/java -version
    java version "1.8.0_281"
    Java(TM) SE Runtime Environment (build 8.0.6.25 - pxa6480sr6fp25-20210115_01(SR6 FP25))
    IBM J9 VM (build 2.9, JRE 1.8.0 Linux amd64-64-Bit Compressed References 20201218_462060 (JIT enabled, AOT enabled)
    OpenJ9   - 4c03b71
    OMR      - 86a8e1a
    IBM      - 8c30c56)
    JCL - 20210108_01 based on Oracle jdk8u281-b09
    [db2inst1@nya-01 ~/sqllib/java/jdk64]$ bin/java -version
    java version "1.8.0_281"
    Java(TM) SE Runtime Environment (build 8.0.6.25 - pxa6480sr6fp25-20210115_01(SR6 FP25))
    IBM J9 VM (build 2.9, JRE 1.8.0 Linux amd64-64-Bit Compressed References 20201218_462060 (JIT enabled, AOT enabled)
    OpenJ9   - 4c03b71
    OMR      - 86a8e1a
    IBM      - 8c30c56)
    JCL - 20210108_01 based on Oracle jdk8u281-b09
    





    ------------------------------
    Lennart Jonsson
    ITS Umeå University
    ------------------------------



  • 7.  RE: jcc driver shipped with 11.5.5, JDBC 4.1 or 4.3?

    Posted Oct 27, 2022 05:39 AM
    Hi Lennart,
    there is even a newer JDK for Db2 available. A (very security aware) customer of mine is frequently updating the JDK underneath Db2, downloading always the newest version from here: https://www.ibm.com/support/pages/java-sdk-downloads-version-80 Current version is 8.0.7.16.
    According to Db2 roadmap Db2 Roadmap@aha it was planned for this quarter. Maybe you should try to get one of the latest Security SB's (you should do anyway) and check again. I was told the code for 11.5.8 GA was already built some weeks ago, where the special build stream is much newer.

    Cheers
    Roland

    ------------------------------
    Roland Schock
    IBM Champion and IBM Gold Consultant
    ------------------------------



  • 8.  RE: jcc driver shipped with 11.5.5, JDBC 4.1 or 4.3?

    Posted Oct 27, 2022 05:49 AM
    Thanks Roland, I'll have a look

    ------------------------------
    Lennart Jonsson
    ITS Umeå University
    ------------------------------