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

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
water bestAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gary PattersonVP Technology / Senior Consultant Commented:
Can you show DDL for table or view being used. Row count, and ideally a visual explain of the query that failed?
0
water bestAuthor Commented:
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
Kent OlsenData Warehouse Architect / DBACommented:
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
water bestAuthor Commented:
I add pooling = false to connectionString and test it for a while. So far so good. Thanks for helping.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.