Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2017
    Posts
    1

    Unanswered: DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=TIMESTAMPDIFF;FUNCTION

    Was run by a java application, using jdbc db2 v 11.1

    When you run this query in the log, with these parameters (follow the order of replacing the variables with '?'), this error has been generated, follows part of the log:

    2017-02-08 20:14:09.595 DEBUG 5970 --- [apr-8080-exec-3] org.hibernate.SQL :
    SELECT
    CASE
    WHEN C6 IS NOT NULL THEN TIMESTAMPDIFF(2,
    CHAR (TIMESTAMP(DATE('1970-01-01'),
    TIME('00:00:00')) + ta.C3 SECOND -3 HOUR) - (TIMESTAMP(DATE('1970-01-01'),
    TIME('00:00:00')) + ta.C6 SECOND -3 HOUR) )
    ELSE TIMESTAMPDIFF(2,
    CHAR (TIMESTAMP(DATE('1970-01-01'),
    TIME('00:00:00')) + ta.C3 SECOND -3 HOUR) - CURRENT TIMESTAMP )
    END
    from
    ARADMIN.T369 ta
    where
    ta.C240000006 in ?
    and ta.C200000004 LIKE ?
    and ta.C200000004 is NOT NULL
    and ta.C3 BETWEEN ? and ?
    2017-02-08 20:14:09.596 TRACE 5970 --- [apr-8080-exec-3] o.h.type.descriptor.sql.BasicBinder : binding parameter [3] as [BIGINT] - [1483833600]
    2017-02-08 20:14:09.596 TRACE 5970 --- [apr-8080-exec-3] o.h.type.descriptor.sql.BasicBinder : binding parameter [2] as [VARCHAR] - [%ALERTA%]
    2017-02-08 20:14:09.596 TRACE 5970 --- [apr-8080-exec-3] o.h.type.descriptor.sql.BasicBinder : binding parameter [4] as [BIGINT] - [1484438399]
    2017-02-08 20:14:09.596 TRACE 5970 --- [apr-8080-exec-3] o.h.type.descriptor.sql.BasicBinder : binding parameter [1] as [VARCHAR] - [MR-SUP PRODUTOS LEGADOS BR]
    2017-02-08 20:14:09.604 WARN 5970 --- [apr-8080-exec-3] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: -440, SQLState: 42884
    2017-02-08 20:14:09.605 ERROR 5970 --- [apr-8080-exec-3] o.h.engine.jdbc.spi.SqlExceptionHelper : DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=TIMESTAMPDIFF;FUNCTION, DRIVER=4.22.29
    2017-02-08 20:14:09.605 WARN 5970 --- [apr-8080-exec-3] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: -727, SQLState: 56098
    2017-02-08 20:14:09.605 ERROR 5970 --- [apr-8080-exec-3] o.h.engine.jdbc.spi.SqlExceptionHelper : DB2 SQL Error: SQLCODE=-727, SQLSTATE=56098, SQLERRMC=2;-440;42884;TIMESTAMPDIFF|FUNCTION, DRIVER=4.22.29
    2017-02-08 20:14:09.605 WARN 5970 --- [apr-8080-exec-3] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: -727, SQLState: 56098
    2017-02-08 20:14:09.605 ERROR 5970 --- [apr-8080-exec-3] o.h.engine.jdbc.spi.SqlExceptionHelper : DB2 SQL Error: SQLCODE=-727, SQLSTATE=56098, SQLERRMC=2;-440;42884;TIMESTAMPDIFF|FUNCTION, DRIVER=4.22.29
    2017-02-08 20:14:09.608 ERROR 5970 --- [apr-8080-exec-3] o.s.boot.web.support.ErrorPageFilter : Forwarding to error page from request [/] due to exception [could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet]

    org.springframework.dao.InvalidDataAccessResourceU sageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet

    Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet

    Caused by: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=TIMESTAMPDIFF;FUNCTION, DRIVER=4.22.29
    at com.ibm.db2.jcc.am.ld.a(ld.java:810) ~[db2jcc4-11.1.jar:na]
    at com.ibm.db2.jcc.am.ld.a(ld.java:66) ~[db2jcc4-11.1.jar:na]
    at com.ibm.db2.jcc.am.ld.a(ld.java:140) ~[db2jcc4-11.1.jar:na]
    at com.ibm.db2.jcc.am.up.c(up.java:2796) ~[db2jcc4-11.1.jar:na]
    at com.ibm.db2.jcc.am.up.d(up.java:2784) ~[db2jcc4-11.1.jar:na]
    at com.ibm.db2.jcc.am.up.a(up.java:2212) ~[db2jcc4-11.1.jar:na]
    at com.ibm.db2.jcc.am.vp.a(vp.java:8077) ~[db2jcc4-11.1.jar:na]
    at com.ibm.db2.jcc.t4.bb.i(bb.java:148) ~[db2jcc4-11.1.jar:na]
    at com.ibm.db2.jcc.t4.bb.b(bb.java:41) ~[db2jcc4-11.1.jar:na]
    at com.ibm.db2.jcc.t4.p.a(p.java:32) ~[db2jcc4-11.1.jar:na]
    at com.ibm.db2.jcc.t4.vb.i(vb.java:145) ~[db2jcc4-11.1.jar:na]
    at com.ibm.db2.jcc.am.up.lb(up.java:2181) ~[db2jcc4-11.1.jar:na]
    at com.ibm.db2.jcc.am.vp.zc(vp.java:3699) ~[db2jcc4-11.1.jar:na]
    at com.ibm.db2.jcc.am.vp.a(vp.java:4578) ~[db2jcc4-11.1.jar:na]
    at com.ibm.db2.jcc.am.vp.b(vp.java:4151) ~[db2jcc4-11.1.jar:na]
    at com.ibm.db2.jcc.am.vp.mc(vp.java:771) ~[db2jcc4-11.1.jar:na]
    at com.ibm.db2.jcc.am.vp.executeQuery(vp.java:736) ~[db2jcc4-11.1.jar:na]
    at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement .executeQuery(NewProxyPreparedStatement.java:76) ~[c3p0-0.9.1.2.jar:0.9.1.2]
    at org.hibernate.engine.jdbc.internal.ResultSetReturn Impl.extract(ResultSetReturnImpl.java:70) ~[hibernate-core-5.0.9.Final.jar:5.0.9.Final]
    ... 101 common frames omitted


    I'd like some help finding a solution.

    Thanks!

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    579
    Provided Answers: 3
    Basically "SQLCODE=-440, SQLSTATE=42884" means that SP/function cannot be found. In your case, that would be TIMESTAMPDIFF function.
    Try calling the function with schema name: SYSFUN.TIMESTAMPDIFF
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •