TraceLog XML Logging Format Uses
Original Publication Date: 2004-Sep-29
Included in the Prior Art Database: 2004-Sep-29
Production database programs usually fail because of bad expected data in the database. A structured query language (SQL) error may be thrown but it still takes some time to determine the root cause of an SQL statement failure. Specifically what record or SELECT condition may be causing the problem. Often the program developer know exactly how to isolate the error using specific diagnostic SQL. Logging of these failures at the time of failure is critical to resolving the problem. Having the knowledge of the program developer is also critical.
TraceLog XML Logging Format Uses
During the development of a database program, the programmer would use a logging facility similar to the one described here to log SQL statements and errors in a form easily readable by a database analyst or IBM*DB2**customer support person without resorting to using the DB2 logging facility which provides information at a very detailed level. Specific diagnostic SQL can be saved into the log at the time of failure, sql designed by the program developer for diagnosing the problem, for future execution by the diagnostic programmer when attempting to fix the problem.
Logging information is presented in a user-friendly manner via a log browser that reads the extensible markup language (XML) based log file. Snapshot variable values are shown substituted into error message descriptions. These variable values could be programmatically substituted into the specific diagnostic SQL provided by the program developer for execution by the diagnostic programmer.
The TraceLog facility was implemented as a JAVA*** class with a set of public application programming interface (API) methods used by the programmer during the development of the database program. The APIs provide means for logging the SQL statement, capturing a snapshot of key program variable values at the time of the error and possibly additional diagnostic SQL to run a the point of failure.
Using the Log viewer, a DB2 analyst or IBM DB2 customer support person could locate the failing SQL statement, extract it and run it again as is or in some modified form to isolate the problem or run any provided diagnostic SQL.
Decomposing and running the SQL statement was the subject of an related draft disclosure that described an algorithm for diagnosing SQL failures.
The current implementation of TraceLog uses a hypertext markup language (HTML) formatted log file scheme where the methods generate HTML tags according to the type of error and type of content to logged.
Sample TraceLog Format
<t id="1.0.0" name="WCAUPDT" ts="YYYYMMDD.HHMMSS.UUU">Task Text</t> <s id="1.1.0" name="WCAUPDT.1" ts="YYYYMMDD.HHMMSS.UUU">Step text</s> <a id="1.1.1" name="WCAUPDT.1.1" ts="YYYYMMDD.HHMMSS.UUU">Action text</a>
...errors, notes, sql statements...
<t id="2.0.0" ts="YYYYMMDD.HHMMSS.UUU">
TraceLog error and note messages use standard IBM Message Label conventions and allow for parameter substitution from the Var list into the text of the message.
The error tagging scheme would appear as follows:
<e id="ABC1234X" tm="2" ts="YYYYMMDD.HHMMSS.UUU">
The note tagging would be:
<note id="ABC1234I" tm=2 ts="YYYYMMDD.HHMMSS.UUU">