Solved

DB2 Error - Error in retrieving data from XYZABC: ERROR [24000] [IBM] CLI0115E Invalid cursor state. SQLSTATE=24000

Posted on 2013-05-13
16
2,414 Views
Last Modified: 2013-06-11
I am querying from the XYZABC database through my application . I am getting the following error often.

CLI0115E Invalid cursor state. SQLSTATE=24000

Specs:
DB2 - 10.1
SQL - 2005
Application on .net framework 4.0
Windows Server 2008 R2

background of the existing db2 connectivity:
1)      We are using the “Select” query to get the results from the DB2 Database.
2)      We are using the db2 data server package (db2 drives) to connect the Db2 db.
3)      We are not inserting any record to db. We are trying to pull the records from the database using Select query, not through any Stored procedure, function, etc.
4)      We are using this switch at the end of the query - “FOR READ ONLY WITH UR” for Uncommitted Read.
5)    Code Attached.

My Assumptions:

When we fetch the recordset, an implicit cursor is created at the backend. Once the transaction is finished, it is not clearing automatically.
But, sometimes, they are clearing correctly and we are not getting error at that point of time.
Please post your comments too and let me know if you need further details on this.
0
Comment
Question by:vinodreddyk
  • 8
  • 6
  • 2
16 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 39161488
can you post your code here and specify which lines causes the error?
0
 
LVL 45

Expert Comment

by:Kdo
ID: 39161494
Hi Reddy,

There can be a number of ways to produce this error.  It sounds like this one MIGHT be the culprit:

  http://www-01.ibm.com/support/docview.wss?uid=swg21267036


Kent
0
 

Author Comment

by:vinodreddyk
ID: 39161626
The Code...

public DataTable GetTicketsFromXYZABC(string PGPs, out string Status)
    {
        Status = "";
        DataTable dt = new DataTable();

        DB2DataAdapter adapter1 = new DB2DataAdapter();
        DB2Connection DB2Conn = new DB2Connection();
        string qry = "";
       
        try
        {
            qry = @"Select DISTINCT a11.TICKETID  TICKETNUMBER,
                                MXOMART.TRUNC_CLOB(a11.DESCRIPTION_LONGDESCRIPTION)  SUMMARY1,
                                MXOMART.TRUNC_CLOB(a11.DESCRIPTION)  SUMMARY2,
                                MXOMART.TZ_ET(a11.CREATIONDATE)  LASTOCCURRENCE,
                                a11.STATUS  STATUS,
                                a11.NODEALIAS  NODE,
                                VALUE(a11.REPORTEDPRIORITY, 0)  PRIORITY,
                                VALUE(a11.URGENCY, 0)  URGENCY,
                                a11.ACCOUNTABLEGROUP  PAP,
                                a11.ACCOUNTABLEPERSON_NAME ASSIGNED_NAME,
                                '' ALERTKEY,
                                a11.URL TIC_LINK
                                from      MXOMART.V_CTO_IM_INCIDENT_30DAYS         a11
                                Where
                                    (a11.STATUS IN ('QUEUED', 'INPROG'))
                                    AND (a11.ACCOUNTABLEGROUP IN (" + PAPs + @"))
                                FOR READ ONLY WITH UR";

                             

            string connectDB2 = Globals.DecryptConnectionString(ConfigurationManager.ConnectionStrings["esConString"].ConnectionString);
            using (DB2Conn = new DB2Connection(connectDB2))
            {

                DB2Command com = new DB2Command();
                com.CommandText = qry;
                com.Connection = DB2Conn;

                adapter1 = new DB2DataAdapter(com);
                adapter1.Fill(dt);
            }
        }
        catch (Exception ex)
        {
            Status = "<br>Error in retrieving data from XYZ-ABC: " + ex.Message;
        }
        finally
        {

        }
        return dt;
    }
0
 

Author Comment

by:vinodreddyk
ID: 39161801
@ Kent...

Tried that it dint work out....
0
 
LVL 45

Expert Comment

by:Kdo
ID: 39161824
Hi Reddy,

It's going to be something like that.  :)  Can you explain your topography a bit more.

On one hand, it looks like you have an application that is connecting to SQL Server via .net and that the DB2 database is connected to SQL Server.  But the application looks like it is connecting directly to DB2.


Kent
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 39162005
do you know which line generates the error?
0
 

Author Comment

by:vinodreddyk
ID: 39164963
Hello Sabag,

Seems that "adapter1.Fill(dt);" is the culprit.

/Kent,

Will provide you with the complete topology soon.


Regards,
Thanks in Advance,
Vinod R Kovvuri
0
 

Author Comment

by:vinodreddyk
ID: 39170966
Hello Kent,

Here is the complete description of the issue and the topology.

Apologies for the delay.

Problem:
•      To retrieve the live tickets from the XYZ-ABC Database, we have implemented this query.
•      It is querying the database every 5 minutes and fetch the latest tickets available in the queue and display in the UI.
•      When I fill the datatable with the dataadapter, I am getting the error. This error is not occurring always.
•      We are getting this error for four hours daily in the afternoon timings (IST). Otherwise, it is working fine in other time. It is rectifying itself after sometime.

Technical Background:
•      We are using the IBM Data Server Package 9.7 Drivers to connect the DB2 Database.
•      We are connecting to the view available in the DB2 database and not to the tables directly, as it is a violation.
•      I have tried to read the data through the DataReader too, but got the same error.
•      Basically, the fetch from the view is very slow comparing with the tables, because it may have some complex joins of multiple tables.
•      But the query is very much simple to the view.
•      We are not inserting any record to db. We are trying to pull the records from the database using Select query, not through any Stored procedure, function, etc.
•      We are using this switch at the end of the query - “FOR READ ONLY WITH UR” for Uncommitted Read.
•      Googled a lot and implement some of the solutions given there, but no luck.
•      I have re-installed the drivers and tried to connect again.

Assumptions:
•      When we fetch the recordset, an implicit cursor is created at the backend. Once the transaction is finished, it is not clearing automatically.
•      Whenever they are clearing correctly, we are not getting error. So, WITH HOLD option will not help us.
•      Sometimes, the previous fetch is not yet closed and trying for the next fetch, we may get this error.
•      As this is a web application, multiple users are trying to connect the db. This may not be create the issue. I understood. But still I assume.

Regards,
Vinod R Kovvuri
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 45

Expert Comment

by:Kdo
ID: 39171134
Hi Vinod,

Thanks for a lot of good information there.

Several things come to mind.  Some easier than others.

-  The problem is load related.  Check your timeout and connection limits.  This may get you past the error, but it doesn't address what appears to be a performance issue.
-  Does the 5 minute refresh query on time or the ticket status?  If on status, try applying an index on the status, or a composite index where status is the first column.
-  When you query the view, do you accept all of the records from the view or do you filter them?  The way DB2 handles multiple filters (on in the view and another in your query) is to filter twice.  If the view returns a lot of rows there's a performance issue here that can be made better with a better view or reading the tables directly.


Kent
0
 

Author Comment

by:vinodreddyk
ID: 39171560
Hello Kent,

Thanks a lot for digging into the issue.

Here is more information.

-      Timeout is not at all a problem. We have not restricted anything there. But, it is not in the code I provided to you. We have included timeout earlier, but we have the issue.
Code:
using (DB2Conn = new DB2Connection(connectDB2))
            {

                DB2Command com = new DB2Command();
                com.CommandText = qry;
                com.Connection = DB2Conn;
  com.CommandTimeout = 0;

                adapter1 = new DB2DataAdapter(com);
                adapter1.Fill(dt);
            }

-      On Time only. But, by default we are giving the manual option to the users. It is not based on the Status or any other column. We are pulling all the data again and again for every 5 mins if the user choose the Auto Refresh Option.

-      We are filtering the view with only two columns. 1) Status = Queued, InProg  2) Queue Filter
0
 
LVL 45

Expert Comment

by:Kdo
ID: 39171604
Hi Vinod,

- How many rows are stored every 5 minutes?  
- Can you post the filter (WHERE clause) from both the view and the query?
- I'm assuming that there is an index over the datetime column.  Is it scalar or composite?


Kent
0
 

Author Comment

by:vinodreddyk
ID: 39180230
Hello Kent,

Sorry for delay...was on a holiday..long weekend.

-      We are not storing the data every 5 minutes anywhere. We are querying the database and get the data every 5 minutes and display in the UI. The average count will be 200.
-      We are querying the view. In that, we have two filters. 1) Status 2) PGP
-      This view is using some 50+ columns from various tables. So, there is no index found. Indexes may be defined in the tables.

We are querying from the same code itself

       try
        {
            qry = @"Select DISTINCT a11.TICKETID  TICKETNUMBER,
                                MXOMART.TRUNC_CLOB(a11.DESCRIPTION_LONGDESCRIPTION)  SUMMARY1,
                                MXOMART.TRUNC_CLOB(a11.DESCRIPTION)  SUMMARY2,
                                MXOMART.TZ_ET(a11.CREATIONDATE)  LASTOCCURRENCE,
                                a11.STATUS  STATUS,
                                a11.NODEALIAS  NODE,
                                VALUE(a11.REPORTEDPRIORITY, 0)  PRIORITY,
                                VALUE(a11.URGENCY, 0)  URGENCY,
                                a11.ACCOUNTABLEGROUP  PAP,
                                a11.ACCOUNTABLEPERSON_NAME ASSIGNED_NAME,
                                '' ALERTKEY,
                                a11.URL TIC_LINK
                                from      MXOMART.V_CTO_IM_INCIDENT_30DAYS         a11
                                Where
                                    (a11.STATUS IN ('QUEUED', 'INPROG'))
                                    AND (a11.ACCOUNTABLEGROUP IN (" + PAPs + @"))
                                FOR READ ONLY WITH UR";
0
 
LVL 45

Accepted Solution

by:
Kdo earned 500 total points
ID: 39181082
It appears that MXOMART.V_CTO_IM_INCIDENT_30DAYS is your view.  

Nothing in what you've posted suggests a cause for the invalid cursor state.  I'm reasonably sure that it's due to the connection.  Improving the query could help.

If you check the explain plan for your query, you should find that the query returns a derived table with ALL of the rows in the view.  That result is then filtered to produce the query's final result.

There are a couple of things that you can do to improve this query.

How many rows are returned from the view?  From the query?

SELECT COUNT(*)
from      MXOMART.V_CTO_IM_INCIDENT_30DAYS;

SELECT COUNT(*)
from      MXOMART.V_CTO_IM_INCIDENT_30DAYS         a11
Where   (a11.STATUS IN ('QUEUED', 'INPROG'))
  AND (a11.ACCOUNTABLEGROUP IN (" + PAPs + @"))

Be sure to substitute the correct values into the filter.


Kent
0
 

Author Comment

by:vinodreddyk
ID: 39181233
Hello Kent,

For the first query...will return a lot of information.. across all the groups and with all statuses
like in millions.
The view will be slow so cannot run that now.

The second query the 2nd query returns 160 (The current tickets in the queue..this might be varied from time to time).

We are using IBM Maximo as our ticketing tool, the Maximo web interface itself is tiresome to work on. So we have designed our own web tool with connects to the DB and fetches tickets in our group with the status 'queued' and 'inprog' and shows them in our web interface. So while fetching this information we are receiving the error.

After completion of work on the ticket we will close the ticket from the same interface.

Regards,
Vinod R Kovvuri
0
 
LVL 45

Expert Comment

by:Kdo
ID: 39181255
Hi Vinod,

Unless there has been a significant change to the DB2 optimizer for version 10, the second query should not be faster than the first one.  In fact, the second query will perform an additional filter pass on the results returned by the first query.

When time permits, run those two queries so we see what kinds of data volumes are in play.


Kent
0
 

Author Comment

by:vinodreddyk
ID: 39237758
Hello Kent,

Looks like there is an issue with the query we are using...as u said.

Thanks a lot for your help on the issue.

Regards,
Vinod R Kovvuri
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now