[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

DB2ResultSet.Read() ERROR [24000] [IBM] CLI0115E  Invalid cursor state.

Posted on 2017-06-22
5
Medium Priority
?
144 Views
Last Modified: 2017-11-30
We have problem with the application run DB2ResultSet.Read(). sometime will get EROR [24000] [IBM] CLI0115E  Invalid cursor state. SQLSTATE=24000.

Database : DB2 for Linux, UNIX and Windows V10.5
Client: Windows 7 64bit

Method:
public int EventGetEvSegmentCnt(string SegmentID, string strEvntGroup)
        {
            int strGroupCnt = 0;
            string strSQL = string.Empty;
            DB2ResultSet objRs;
            if (string.IsNullOrEmpty(SegmentID) || string.IsNullOrEmpty(strEvntGroup))
            {
                strGroupCnt = 0;
            }
            else
            {
                strSQL = " SELECT COUNT(EVNT_CODE) AS EVNT_GROUP_COUNT FROM E_SEGMENT_EVENT WHERE C_SEGMENT_ID = " + SegmentID + " AND EVNT_GROUP = " + strEvntGroup;
                Common.DatabaseHelper helper = new Common.DatabaseHelper();
                objRs = helper.ExecuteResultSet(strSQL);
                if (objRs.Read())
                {
                    strGroupCnt = 0;
                }
                else
                {
                    strGroupCnt = int.Parse(objRs["EVNT_GROUP_COUNT"].ToString());
                }
            }
            return strGroupCnt;

        }

Error Message:
[Information]      System.Web.HttpUnhandledException (0x80004005): Exception of type 'System.Web.HttpUnhandledException' was thrown. ---> IBM.Data.DB2.DB2Exception (0x80004005): ERROR [24000] [IBM] CLI0115E  Invalid cursor state. SQLSTATE=24000
   at IBM.Data.DB2.DB2DataBuffer.FetchScroll(FetchType fetchType, Int64 offset, Int32 numRows)
   at IBM.Data.DB2.DB2DataBuffer.FetchNext()
   at IBM.Data.DB2.DB2DataReader.Fetch(FetchDirection direction, Int64 offset, Boolean& isDeleted)
   at IBM.Data.DB2.DB2ResultSet.Read()

Please help!!!!
Thanks
0
Comment
Question by:water best
  • 2
4 Comments
 
LVL 36

Expert Comment

by:Gary Patterson
ID: 42189440
Can you show DDL for table or view being used. Row count, and ideally a visual explain of the query that failed?
0
 

Author Comment

by:water best
ID: 42189563
DDL:
CREATE TABLE "ABC"."E_SEGMENT_EVENT" (
            "CC_SEGMENT_ID" INTEGER NOT NULL,
            "EVNT_CODE" CHAR(4) NOT NULL,
            "SGEV_SEQ" INTEGER NOT NULL,
            "SGEV_SOM_HH" INTEGER,
            "SGEV_SOM_MM" INTEGER,
            "SGEV_SOM_SS" INTEGER,
            "SGEV_SOM_FF" INTEGER,
            "SGEV_CREATE_DATE" TIMESTAMP NOT NULL,
            "SGEV_CREATE_USER_ID" VARCHAR(10) NOT NULL,
            "EVNT_GROUP" INTEGER
      )
      ORGANIZE BY ROW
      DATA CAPTURE NONE
      IN "ABCTS1SPACE2" INDEX IN "ABCTS2SPACE2"
      COMPRESS NO;

CREATE INDEX "ABC"."FK_E_SEGMENT_EVNT2"
      ON "ABC"."E_SEGMENT_EVENT"
      ("CC_SEGMENT_ID"            ASC)
      PCTFREE 10
      MINPCTUSED 0
      ALLOW REVERSE SCANS
      PAGE SPLIT SYMMETRIC
      COMPRESS NO;

ALTER TABLE "ABC"."E_SEGMENT_EVENT" ADD CONSTRAINT "SQL160223095623530" PRIMARY KEY
      ("CC_SEGMENT_ID",
       "SGEV_SEQ");

ALTER TABLE "ABC"."E_SEGMENT_EVENT" ADD CONSTRAINT "F_REF_540" FOREIGN KEY
      ("EVNT_CODE")
      REFERENCES "ABC"."EVENT"
      ("EVNT_CODE")
      ON DELETE RESTRICT;

GRANT ALTER ON TABLE "ABC"."E_SEGMENT_EVENT" TO USER "ADMINISTRATOR" WITH GRANT OPTION;

GRANT CONTROL ON TABLE "ABC"."E_SEGMENT_EVENT" TO USER "ADMINISTRATOR";

GRANT DELETE ON TABLE "ABC"."E_SEGMENT_EVENT" TO USER "ADMINISTRATOR" WITH GRANT OPTION;

GRANT INDEX ON TABLE "ABC"."E_SEGMENT_EVENT" TO USER "ADMINISTRATOR" WITH GRANT OPTION;

GRANT INSERT ON TABLE "ABC"."E_SEGMENT_EVENT" TO USER "ADMINISTRATOR" WITH GRANT OPTION;

GRANT REFERENCES ON TABLE "ABC"."E_SEGMENT_EVENT" TO USER "ADMINISTRATOR" WITH GRANT OPTION;

GRANT SELECT ON TABLE "ABC"."E_SEGMENT_EVENT" TO USER "ADMINISTRATOR" WITH GRANT OPTION;

GRANT SELECT ON TABLE "ABC"."E_SEGMENT_EVENT" TO USER "CPL";

GRANT SELECT ON TABLE "ABC"."E_SEGMENT_EVENT" TO USER "CPLDPR";

GRANT SELECT ON TABLE "ABC"."E_SEGMENT_EVENT" TO USER "MAO";

GRANT UPDATE ON TABLE "ABC"."E_SEGMENT_EVENT" TO USER "ADMINISTRATOR" WITH GRANT OPTION;

737 row count.

In common case, the query not failed. but repeatedly run sometimes it will show this error. This error also show up in other DB2ResultSet.Read() or DB2ResultSet.ReadFirst()  function.
Capture.PNG
rsRead1.PNG
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 42190502
Hi Best,

Does the query always fail or is the failure intermittent?
Is Common.DataHelper a standard package or something that your shop has developed?


Following the stack trace, ObjRs.Read() calls IBM.Data.DB2.DB2ResultSet.Read() and the exception is occurring in FetchScroll.

Has the database connection been opened prior to this function is called?  
Do you know if ObjRs is using SQLCA or trying to manage its own cursor?
0
 

Accepted Solution

by:
water best earned 0 total points
ID: 42192473
I add pooling = false to connectionString and test it for a while. So far so good. Thanks for helping.
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
In this video I will demonstrate how to set up Nine, which I now consider the best alternative email app to Touchdown.
Free Data Recovery software is an advanced solution from Kernel Tools to recover data and files such as documents, emails, database, media and pictures, etc. It supports recovery from physical & logical drive after a hard disk crash, accidental/inte…

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question