Home / server / Questions / 385272
Accepted
Peter Schuetze
Asked: 2012-05-03 05:20:07 +0800 CST

Regular expressions to find SQL Errors in Logs

  • 772

I am running a log parser to find SQL errors in my log files. If the search string (regular expression) is evaluated to true, the line will be marked. So far I used the search expression "SQL0", which was given to me by our DBAs. However, I recently discovered that there are noteworthy SQL Errors that start with "SQL1".

I found some references, that negative SQL error codes mean that the statement was unsuccessful. So my current idea is to search for "SQL[0-9]+N" (SQL error messages were the number ends with an N). Will this find all SQL error codes?

Update: I am running automated scripts on a DB2 database. I need to parse the logs of these scripts to verify if errors occurred during execution.

regex db2

2 Answers

  1. Best Answer

    Finally I found what I was looking for:

    Message Structure

    Message help describes the cause of a message and describes any action you should take in response to the message.

    Message identifiers consist of a three character message prefix, followed by a four or five digit message number, followed by a single letter suffix. For example, SQL1042C. For a list of message prefixes, see Invoking message help and Other DB2 Messages. The single letter suffix describes the severity of the error message.

    In general, message identifiers ending with a C are for severe messages; those ending with an E indicate urgent messages; those ending with an N indicate error messages; those ending with a W indicate warning messages; and those ending with an I indicate informational message.

    ....

    For SQL messages, message identifiers ending with a C indicate critical system errors; those ending with an N indicate error messages; those ending with a W indicate warning or informational messages.

    see: http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.messages.doc/doc/c0052007.html

    • 1

  2. If I understand what I found in Google correctly, your error messages look like this:

    SQL0204N "Some string explaining the error."

    It seems that the number is the error code and I would guess that the leading 0 indicates that this example is negative error code 204. A leading 1 in the number will be for positive error codes so 1204 is +204 and 0204 is -204.

    Grepping for "SQL[0-9]+N" will find all error messages that match that pattern, however I can't verify that DB2 will not produce other types of errors.

    • 0