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.

For further information on troubleshooting, check out these blog postings on diagnosing problems: part 1, part 2, part 3 and part 4.

DBSpy

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

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.

Summary

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.