water best
asked on
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(strin g SegmentID, string strEvntGroup)
{
int strGroupCnt = 0;
string strSQL = string.Empty;
DB2ResultSet objRs;
if (string.IsNullOrEmpty(Segm entID) || string.IsNullOrEmpty(strEv ntGroup))
{
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(st rSQL);
if (objRs.Read())
{
strGroupCnt = 0;
}
else
{
strGroupCnt = int.Parse(objRs["EVNT_GROU P_COUNT"]. ToString() );
}
}
return strGroupCnt;
}
Error Message:
[Information] System.Web.HttpUnhandledEx ception (0x80004005): Exception of type 'System.Web.HttpUnhandledE xception' was thrown. ---> IBM.Data.DB2.DB2Exception (0x80004005): ERROR [24000] [IBM] CLI0115E Invalid cursor state. SQLSTATE=24000
at IBM.Data.DB2.DB2DataBuffer .FetchScro ll(FetchTy pe fetchType, Int64 offset, Int32 numRows)
at IBM.Data.DB2.DB2DataBuffer .FetchNext ()
at IBM.Data.DB2.DB2DataReader .Fetch(Fet chDirectio n direction, Int64 offset, Boolean& isDeleted)
at IBM.Data.DB2.DB2ResultSet. Read()
Please help!!!!
Thanks
Database : DB2 for Linux, UNIX and Windows V10.5
Client: Windows 7 64bit
Method:
public int EventGetEvSegmentCnt(strin
{
int strGroupCnt = 0;
string strSQL = string.Empty;
DB2ResultSet objRs;
if (string.IsNullOrEmpty(Segm
{
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(st
if (objRs.Read())
{
strGroupCnt = 0;
}
else
{
strGroupCnt = int.Parse(objRs["EVNT_GROU
}
}
return strGroupCnt;
}
Error Message:
[Information] System.Web.HttpUnhandledEx
at IBM.Data.DB2.DB2DataBuffer
at IBM.Data.DB2.DB2DataBuffer
at IBM.Data.DB2.DB2DataReader
at IBM.Data.DB2.DB2ResultSet.
Please help!!!!
Thanks
Can you show DDL for table or view being used. Row count, and ideally a visual explain of the query that failed?
ASKER
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
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
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?
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.
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.