RVSpy and DBSpy
For anyone that has dealt with customer support on a thorny Sage ERP Accpac application issue, you have probably been asked to provide a RVSpy and/or a DBSpy log. What are these logs? Can I use these tools to diagnose problems myself? These are the topics we’ll be covering in this blog posting.
Log files are typically generated by programs to produce a record of what has happened and all the steps along the way. These are useful for diagnosing problems since engineers can study these to see, not only what the actual problem was, but what were the steps that led up to the problem.
Below is the usual Architecture diagram for Sage ERP Accpac. Basically it is a three tier architecture with a thin API into each tier.
This is the basis for the DBSpy and RVSpy programs. DBSpy spies and reports on all calls made to the Database Services layer. RVSpy spies and reports on all calls made to the Common Business Logic API. Thus these tools can give a complete picture of everything going to and from the Database Layer (including failure codes and diagnostic message) as well as everything going to and from the Business Logic Layer.
We originally included these as part of the SDK back when version 1.0 was released, but found these tools so useful, and were giving them out so often, that we included them into the regular product.
To run DBSpy, go to Start Menu – All Programs – Sage Accpac – Tools – DBSpy. This will bring up:
In the options for this program you can control where you want to log the output to. For longer running programs you might want to only log to a file and turn off the logging to a window, since it can slow your system down scrolling all this information past.
You can actually get more information out of DBSpy by configuring it with a utility called logAdder:
If you check the various boxes here you can get more info in the log files. If you are getting a strange database error with no output in the DBSpy log file then check everything here and try again. Often the more detailed logging will give you the answer. This tool is given out by customer support. If you turn on these options, make sure you turn them off again after you are done as generating all these log strings can slow down processing.
Usually you run DBSpy, reproduce your problem and immediately stop DBSpy, so the problem will be near the end of the log file. Then you go back from the end to see what statements failed or what additional error diagnostics are logged. If you see the error code: DBS_NO_MORE_DATA, this probably just means the program was scrolling through a range of records and got to the end. This error return rarely indicates a problem. If you see the error code: DBS_NOT_FOUND, this means the program tried to read a record and it wasn’t found. This may be ok, since the program might just be seeing if an optional feature needs processing, but it might also be a validation error meaning a required record is missing and this is the source of your problem. Other error returns are usually real problems.
For a bit more information on the Accpac Database Layer, check out this posting. Most of the output you see in the logfile corresponds to the various functions in our Database API, things like OPEN-TABLE, SELECT, FETCH, etc. From the routine names you can get an idea of what sort of database operations are going on. If you are running SQL Server or Oracle, these will cause SQL statements to be generated and executed, which you can see in ODBCTrace. For Pervasive.SQL they may produce SQL statements or calls to the Pervasive transactional engine. There isn’t a one to one correspondence between our Database API and SQL statements so one call may cause several statements to be executed and some calls don’t cause any SQL to be executed.
RVSpy records all API calls to the Accpac Views (Business Logic Objects). This includes all SDK produced applications whether from Sage or from ISVs. Often one View will call other Views, and this is all recorded and nested inside the log file or output Window. There is also an option to include a DBSpy inside the RVSpy so you can see what View calls generated which Database calls. You run RVSpy from the Start Menu – All Programs – Sage Accpac – Tools – RVSpy. Below is the RVSpy program:
Again you can configure the output whether you want it to the RVSpy Window and/or to a file. Beware that by default you won’t have permission to write it to C:\ so you either need to grant yourself permission or put it somewhere else. One tip is to turn off some of the field level API calls since these are quite noisy and tend not to give useful information such as: Field, Fields, Attribs, Type, Name, Key, Keys, Presents and event Get. This then speeds up logging quite a bit and makes it easier to read the log file.
Views are referenced by their ID and name such as IC0580: ICREED in the above screen shot. You can get additional information on the Views by looking them up on the Application Object Model (AOM) on our website.
There is a standard set of View API calls such as Get, Put, Fetch, Verify, Update, Insert, Read. You see each of these go by with their parameters and return code. Each of these will always return an error code. 0 means success. The standard return codes are:
// Error codes. There are two sets: old and extended. These can co-exist
// quite well since they don’t overlap. Some of the old codes are made
// redundant in the extended codes; these are prefixed with “OLD_”.
// Old error codes
#define ERRNUM_SUCCESS 0
#define ERRNUM_LOAD_FAILED 100
#define ERRNUM_OPEN_FAILED 101
#define ERRNUM_COMPOSE_FAILED 102
#define ERRNUM_ROTOENTRY_FAILED 103
#define OLD_ERRNUM_GENERAL 1
#define OLD_ERRNUM_WARNING 10
#define OLD_ERRNUM_OTHER 2
#define OLD_ERRNUM_RECORD_NOT_FOUND 1
#define OLD_ERRNUM_RECORD_NO_MORE_DATA 1
#define OLD_ERRNUM_RECORD_EXISTS 1
#define OLD_ERRNUM_RECORD_DUPLICATE 1
#define OLD_ERRNUM_TABLE_EXISTS 1
// Extended error codes
#define ERRNUM_WARNING WARNING_GENERAL
#define ERRNUM_GENERAL 1000
#define ERRNUM_RECORD_NOT_FOUND 1020
#define ERRNUM_RECORD_NO_MORE_DATA 1021
#define ERRNUM_RECORD_EXISTS 1022
#define ERRNUM_RECORD_DUPLICATE 1023
#define ERRNUM_RECORD_INVALID 1024
#define ERRNUM_RECORD_LOCKED 1025
#define ERRNUM_RECORD_CONFLICT 1026
#define ERRNUM_RECORD_NOT_LOCKED 1027
#define ERRNUM_RECORD_PROTOCOL 1028
#define ERRNUM_TABLE_EXISTS 1040
#define ERRNUM_TABLE_NOT_FOUND 1041
#define ERRNUM_PERMISSION_NONE 1060
#define ERRNUM_MEMORY_NO_MORE 1080
#define ERRNUM_MEMORY_BUFFER_LIMIT 1081
#define ERRNUM_FILTER_SYNTAX 1100
#define ERRNUM_FILTER_OTHER 1101
#define ERRNUM_KEY_INVALID 1120
#define ERRNUM_KEY_NUMBER 1121
#define ERRNUM_KEY_CHANGED 1122
#define ERRNUM_FIELD_INVALID 1140
#define ERRNUM_FIELD_NUMBER 1141
#define ERRNUM_FIELD_INDEX 1142
#define ERRNUM_FIELD_DISABLED 1143
#define ERRNUM_FIELD_READONLY 1144
#define ERRNUM_TRANSACTION_NONE 1160
#define ERRNUM_TRANSACTION_OPEN 1161
#define ERRNUM_REVISION_PROTOCOL 1180
#define ERRNUM_DATABASE_PARAMETER 1200
#define ERRNUM_DATABASE_LIMIT 1201
#define ERRNUM_DATABASE_OTHER 1202
#define ERRNUM_DATABASE_DICTIONARY 1203
#define ERRNUM_RPC_FAILURE 1220 // remote procedure call had communications failure
#define ERRNUM_APPLICATION_DEFINED_BASE 9000
#define ERRNUM_APPLICATION_DEFINED_END 9999
// Extended warning codes
#define WARNING_GENERAL -1
#define WARNING_FILTERDELETE_ORPHANS -10
#define WARNING_FILTERCOUNT_APPROXIMATE -20
#define WARNING_APPLICATION_DEFINED_BASE -1999
#define WARNING_APPLICATION_DEFINED_END -1000
The Views can also define custom error codes that you will see now and then.
Tips to Analyzing Logs
Customer support usually asks for these logs, when they are going to escalate an issue to development, because development will always ask for them. Development can take the logs and look in the source code to get additional insights into what may be going on. You don’t have access to the source code, but often you can still get clues as to problems. Also remember to run the Accpac Data Integrity Checker as this will often uncover issues to do with database relational integrity.
First stop logging when the error occurs. Then the problem will be near the end of the log. When an error occurs often something will fail when it happens and then everything that called it will fail. So at the end of the log you will see a bunch of calls that failed. Go back in the log to find the first one, this is often the problem. Also the problem might be right before this, so something might fail and you don’t see why, but chances are it might be due to a successful read just before it returning something it doesn’t like.
If the first error is a Read failed, then chances are you have a data integrity problem. See what View was being read and what the key is. Is this from a setup function? Is it some value that was recently deleted? Often fixing the problem just involves re-inserting this setup record. Often when you are phasing something out you might think it’s no longer needed, but somewhere in the system is an old unprocessed document that will fail as a result. Usually the regular error reporting for these sorts of problems is all you need, other times they are quite cryptic and that sends you to the log files.
If you see a lot of Read failures for no apparent reason, it might be the indexes in your database are corrupted. This is more frequent in Pervasive, but we have seen it in SQL Server also. The remedy here is to re-index the database either via a database vendor supplied tool or by doing a Database Dump and Load with the Accpac utilities. Sometimes Inserts failing for cryptic reasons can be due to this also.
If you get an Insert failing because of record already exists, don’t just delete it. This often means an options record which contains a next sequence number needs updating to a higher value. Sometimes you can do this in the application, sometimes you need to update it in the database manager.
Hopefully you won’t have to go spelunking in the log files too often. But hopefully with a bit of knowledge you can diagnose a few more problems independently without needing to call Customer Support.