Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2012
    Posts
    2

    Unanswered: Help on DB2 SQL error: SQLCODE: -440, SQLSTATE: 42884, SQLERRMC: LIKE;FUNCTION

    Recently I migrated db2 using db2move and db2look from Windows to Solaris.
    After migration, when I try to run my system using hibernate, it only can run once but for 2nd time onwards, an error occur. Below is the log message;

    org.hibernate.exception.SQLGrammarException: could not execute query
    at org.hibernate.exception.SQLStateConverter.convert( SQLStateConverter.java:67)
    at org.hibernate.exception.JDBCExceptionHelper.conver t(JDBCExceptionHelper.java:43)
    at org.hibernate.loader.Loader.doList(Loader.java:214 8)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(L oader.java:2029)
    at org.hibernate.loader.Loader.list(Loader.java:2024)
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoa der.java:375)
    at org.hibernate.hql.ast.QueryTranslatorImpl.list(Que ryTranslatorImpl.java:308)
    at org.hibernate.engine.query.HQLQueryPlan.performLis t(HQLQueryPlan.java:153)
    at org.hibernate.impl.SessionImpl.list(SessionImpl.ja va:1106)
    at org.hibernate.impl.QueryImpl.list(QueryImpl.java:7 9)
    at com.bcb.osa.actions.MibsIfStatement.execute(MibsIf Statement.java:84)
    at org.apache.struts.action.RequestProcessor.processA ctionPerform(RequestProcessor.java:431)
    at org.apache.struts.action.RequestProcessor.process( RequestProcessor.java:236)
    at org.apache.struts.action.ActionServlet.process(Act ionServlet.java:1196)
    at org.apache.struts.action.ActionServlet.doPost(Acti onServlet.java:432)
    at javax.servlet.http.HttpServlet.service(HttpServlet .java:647)
    at javax.servlet.http.HttpServlet.service(HttpServlet .java:729)
    at org.apache.catalina.core.ApplicationFilterChain.in ternalDoFilter(ApplicationFilterChain.java:269)
    at org.apache.catalina.core.ApplicationFilterChain.do Filter(ApplicationFilterChain.java:188)
    at org.displaytag.filter.ResponseOverrideFilter.doFil ter(ResponseOverrideFilter.java:125)
    at org.apache.catalina.core.ApplicationFilterChain.in ternalDoFilter(ApplicationFilterChain.java:215)
    at org.apache.catalina.core.ApplicationFilterChain.do Filter(ApplicationFilterChain.java:188)
    at org.apache.catalina.core.StandardWrapperValve.invo ke(StandardWrapperValve.java:213)
    at org.apache.catalina.core.StandardContextValve.invo ke(StandardContextValve.java:172)
    at org.apache.catalina.core.StandardHostValve.invoke( StandardHostValve.java:127)
    at org.apache.catalina.valves.ErrorReportValve.invoke (ErrorReportValve.java:117)
    at org.apache.catalina.core.StandardEngineValve.invok e(StandardEngineValve.java:108)
    at org.apache.catalina.connector.CoyoteAdapter.servic e(CoyoteAdapter.java:174)
    at org.apache.coyote.http11.Http11Processor.process(H ttp11Processor.java:879)
    at org.apache.coyote.http11.Http11BaseProtocol$Http11 ConnectionHandler.processConnection(Http11BaseProt ocol.java:665)
    at org.apache.tomcat.util.net.PoolTcpEndpoint.process Socket(PoolTcpEndpoint.java:528)
    at org.apache.tomcat.util.net.LeaderFollowerWorkerThr ead****nIt(LeaderFollowerWorkerThread.java:81)
    at org.apache.tomcat.util.threads.ThreadPool$ControlR unnable****n(ThreadPool.java:689)
    at java.lang.Thread****n(Unknown Source)
    Caused by: com.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -440, SQLSTATE: 42884, SQLERRMC: LIKE;FUNCTION
    at com.ibm.db2.jcc.b.id.e(id.java:1640)
    at com.ibm.db2.jcc.b.id.a(id.java:1229)
    at com.ibm.db2.jcc.c.fb.h(fb.java:149)
    at com.ibm.db2.jcc.c.fb.a(fb.java:43)
    at com.ibm.db2.jcc.c.s.a(s.java:30)
    at com.ibm.db2.jcc.c.wb.g(wb.java:152)
    at com.ibm.db2.jcc.b.id.n(id.java:1209)
    at com.ibm.db2.jcc.b.jd.eb(jd.java:1779)
    at com.ibm.db2.jcc.b.jd.a(jd.java:2232)
    at com.ibm.db2.jcc.b.jd.V(jd.java:505)
    at com.ibm.db2.jcc.b.jd.executeQuery(jd.java:488)
    at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStat ement.executeQuery(DelegatingPreparedStatement.jav a:96)
    at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStat ement.executeQuery(DelegatingPreparedStatement.jav a:96)
    at org.hibernate.jdbc.AbstractBatcher.getResultSet(Ab stractBatcher.java:139)
    at org.hibernate.loader.Loader.getResultSet(Loader.ja va:1669)
    at org.hibernate.loader.Loader.doQuery(Loader.java:66 2)
    at org.hibernate.loader.Loader.doQueryAndInitializeNo nLazyCollections(Loader.java:224)
    at org.hibernate.loader.Loader.doList(Loader.java:214 5)
    ... 31 more



    My query is in Java as below;

    List result = hibernate_session.createQuery("from Staff where UPPER(custName) like ? and smtDate Between ? and ?")
    .setString(0,"%"+mibsForm.getSearchkey().trim()+"% ").setDate(1,startDate).setDate(2,endDate).lis t();


    On 1st time load, it worked but 2nd time onwards, hit errors. It only happen on Solaris box. On windows its working fine.

    Below my db2 version;

    On windows - 9.7
    On Solaris - 10.1

    I tried google my error but seems cannot find appropriate solutions.

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    have you tried native connect and execute a simple query with like ?
    try db2updv97
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Dec 2012
    Posts
    2
    i tried to execute using SQL editor and working fine.

    If I change from

    List result = hibernate_session.createQuery("from Staff where UPPER(custName) like ? and smtDate Between ? and ?")
    .setString(0,"%"+mibsForm.getSearchkey().trim()+"% ").setDate(1,startDate).setDate(2,endDate).lis t();

    to

    List result = hibernate_session.createQuery("from Staff where UPPER(custName) like '"+mibsForm.getSearchkey().trim()+"' and smtDate Between ? and ?")
    .setDate(0,startDate).setDate(1,endDate).lis t();

    it also working fine.
    I cannot find db2 version 9.7 for solaris. Mind to give the link?

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    I cannot find db2 version 9.7 for solaris. Mind to give the link?
    do you mean the doc
    IBM DB2 9.7 Information Center for Linux, UNIX, and Windows
    or base code
    : see ibm.com/partnerworld...
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

Tags for this Thread

Posting Permissions

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