Running SQLAnywhere 17, relatively current patched. Hosts the database for our ERP system. The .db file is appx 75gb. Yesterday,everything was fine, but this morning entering the sales module started throwing this error: Database transaction information not available CallSetTrans or SetTransObject I worked the the ERP vendor to eliminate a program issue. When run against a 36 hour old backup of the database on the test system, the error does not happen. I used DBBACKUP to make a copy if the current data. The database validates OK. I tried rebuilding the indexes for related tables, drop/recreate foreign keys. No joy in resolving the issue. Employing what I know, I'm down to unloading the database and reloading to a fresh .db file, but I won't know if that addresses the issue until I try it. I'm doing that process now, but know it will take a few hours. In the meantime, is there somewhere else I should look for errors? The database engin error log has nothing. |
> Database transaction information not available
AFAIK that means the PowerBuilder application doesn't have a connection to the database. Some earlier PowerBuilder operation (CONNECT, SetTransObject) may have returned an error that can help.
> Yesterday,everything was fine, but this morning
Something was changed, what was it?
First rule of engineering, and I wish I new. I first thought it was something in the app, built with PowerBuilder. We've been making updates to the libraries. But, I ran an old version of the app (libraries dated Feb 2021), and the problem persists. I completed the unload/load and that did not resolve the issue.
Some Google searching is pointing back to the PB app, but that doesn't make sense to me if the error happens on the old version now too. I was hoping for some database parameter adjustment. Oh well, kicking it back to the developers..
Does the PB app check for errors reported by the CONNECT USING statement(s)? Does it have scripts that check for errors reported by the application systemerror event and the datawindow dberror and error events?
If memory serves "Database transaction information not available" has two possible explanations, both of them application deficiencies...
(1) a call to SetTransObject was missing or skipped or failed, or
(2) some earlier error was not detected by the application, leading to (1).
> the error happens on the old version now too
Are you absolutely sure everyone has a firm grasp on what's actually running, as in "old version versus new version"?
Historically, "Database transaction information not available" usually means "rookie misteak" :)
The application isn't connected to the database, and there's nothing in the database console log to say a connection was forced off, sooooo... the problem probably lies in the application, not the database.
> Database transaction information not available
FWIW this message should read "Connection information not available".
Historically, PowerBuilder called SQLCA a "transaction object" instead of what it really is, a connection object. A transaction is something that happens on a connection, but it's optional... you can establish a connection without starting a transaction. This bad terminology has confused generations of developers :)
I did the testing for the error on my desktop, and was careful (i actually took notes!) for what version of the app I had, and which version of the database it was connecting to.
The summary is that any version of the app connecting to a version of the db older than Thursday 8/5 does not have the error. Any version connecting to the current version of the DB throws the error. To me this implies a database problem. Database validation comes back clean, and I've tried looking at all the data for the affected modules in it's raw form (or as close as I can get) and not spotted anything. So I was hoping there was some db server setting around transaction creation that needed adjustment.
Please answer these questions: Does the PB app check for errors reported by the CONNECT USING statement(s)? Does it have scripts that check for errors reported by the application systemerror event and the datawindow dberror and error events?
If the answers are all "no" then code needs to be added... you must find out where and why the application is losing the connection.
Your application can't connect (or stay connected) to a particular copy of the database... you need to find out why. Some kind of tracing may help, since you (probably) don't have sufficient PB code in place to catch the error.
> So I was hoping there was some db server setting around transaction creation that needed adjustment.
The error is "no connection". The symptom doesn't have anything to do with transactions.
Please show us the PowerScript code for
(1) the DataWindow.Retrieve (or whatever) plus error checking that catches the "Database transaction information not available" symptom
(2) the preceding SetTransObject plus error checking
(3) the preceding CONNECT plus error checking
Unfortunately, I don't have any code at all in place, nor do I have access to what is there. It all comes from the ERP vendor.
A new wrinkle is that I found 2 records that when removed from a table, causes the error to go away. Using iSQL the data looked fine, I sent the dbunload data for those records to the vendor. The first response was that maybe the 'x\0dx\0d' in the mailing address field was the problem (Grrr..).
The vendor sent me a copy of the main module with some debug logging enabled. The adventure continues.
That was my gut feeling, but I'm aware that I don't know everything so I figured it couldn't hurt to ask.
If the connection really fails (in contrast to "the PB code fails to even try to establish a connection"), a Disconnect system event and/or using the LOG connection parameter may be of help.