Migrating VB app to DB2 from Access...problems with recordsets

[PaulAH]

I'm migrating an old application which used an Access Db with DAO controls to a DB2 database.
Firstly I found I couldn't use the original DAO controls as the recordsets were read only (why?).
Having changed the controls to ADO I'm now having problems scrolling thru/updating the records.....
1. Error "SQL0437W Performance of this complex query may be sub-optimal. Reason code 3"
2. Error "Key information is not sufficient or correct. Too many rows were affected by update".

I'm using disconnected recordsets but "updatebatch" each time a record is changed as I don't want to change the application too much.

Also sometimes getting error "CLI0129E No more handles. SQL state=01602"

I'm running W98 with VB6 and the database is on W2000 server

would appreciate any help.....thanks

 

[MarcLodge]

Paul,
Without seeing the SQL you are using I can offer a couple of suggestions...
If you have a CURSOR statement see if changing this to CURSOR WITHOUT HOLD. The default is CURSOR WITH HOLD, and this affects the number of handles that you are holding.

It seems from what you are saying that you are scrolling through records and updating records as you go. Are you issuing a COMMIT ? This will release locks, resources etc. and will free up some of the handles. Of course this might not be possibly in the business context of your application, but what you might be able to do is set a counter as commit after so many updates have been reached.

Hope this helps

Marc

 

[gregsimpson]

Paul,

I hope I can help with

1. Error "SQL0437W Performance of this complex query may be sub-optimal. Reason code 3"


I have seen this error before on some horribly complex SQL I was binding in a COBOL program. What it effectively meant was that the optimizer had tried to come up with an efficient access path but wasn't happy with it's own results. It was therefore warning me that the query may take along time to run, should I choose to run it.

To correct this I would recommend the following

Break the statement up into less complex SQL statements.

Ensure predicates do not over-specify the answer set

Issue Runstats for the tables involved in the query

As I've brought RUNSTATS into the game I should explain you can get the same error with REASON CODE6. I believe this message is also produced as a result of the RUNSTATS utility being run "incorrectly". I found the following which explains this in further detail.

If you do not have enough time available to collect all of the statistics at one time, you may choose to periodically run RUNSTATS to update only a portion of the statistics that could be gathered. If inconsistencies are found as a result of activity on the table between the periods where you run RUNSTATS with a selective partial update, then a warning message (SQL0437W, reason code 6) is issued. For example, you first use RUNSTATS to gather table distribution statistics. Subsequently, you use RUNSTATS to gather index statistics. If inconsistencies are detected as a result of activity on the table, then the table distribution statistics are dropped and the warning message is issued. It is recommended that you run RUNSTATS to gather table distribution statistics when this happens.

You should periodically use RUNSTATS to gather both table and index statistics at once, to ensure that the index statistics are synchronized with the table statistics. Index statistics retain most of the table and column statistics collected from the last run of RUNSTATS. If the table has been modified extensively since the last time its table statistics were gathered, gathering only the index statistics for that table will leave the two sets of statistics out of synchronization.

I found the following on your handles problem. Worth looking at dbforums.com as well as I've seen it on there in the past.

CLI0129E No more handles.
Cause: A call was made to SQLAllocEnv, SQLAllocConnect, or SQLAllocStmt but no more handles are available in the driver.

Action: Call SQLFreeEnv, SQLFreeConnect, or SQLFreeStmt to free up some handles which are no longer being used in the application.


Cheers
Greg

 

[gregsimpson]

Just noticed marc lodge has received multiple stars for a CLI posting which appears to be similar to yours.

thread178-384009 suggest you give it a look.

Cheers
GReg

 

[PaulAH]

Thanks guys for your help.

I found error's 1 + 2 were caused by the table (with no unique key) containing duplicate records . So the DB didn't know which record to update!
I also got around the handles problem by using only one connection.

cheers