Understanding SYSERRORS Query Results
To understand the results returned by queries like those shown above, note the following:
- A refresh message consists of one or more transactions.
- A transaction consists of one or more statement(s).
- Each statement is a sequence of rows.
- A row may contain nested statements.
(Nested statements include those scenarios in which a statement fires a trigger or calls a function which issues other SQL statements.)
The TransactionListVTI class, which contains the printFailedTransaction method, is a Virtual Table Interface that presents a series of rows for each transaction in the refresh message. (See the Cloudscape Developer's Guide for information on VTIs.)
The statement that failed in each transaction is flagged with the following box:
********************************
* FAILED STATEMENT *
********************************
printFailedTransaction Output
The column list displays at the top of the output stream.
SQLCol1
---------------
TransactionLis&
Then, a set of rows is displayed for each failed transaction.
For each failed transaction:
The first row shows the contents of TransactionListVTI.
Contents of SQLCol1:
TRAN_NUMBER |ORIGINATING_DBID |ORIGINATING_CO& |SUMMARY |STATEMENT_LIST
----------------------------------------------------------------------------------------
1 |a816c00e-00da-ce22-a326-00& |(2,42744) |TRANSACTION |StatementListV&
This row displays information about the transaction including the transaction number, the ID of the originating database, and a StatementListVTI, which is a VTI containing all statements in the transaction.
The next row shows the values for the StatementListVTI.
Contents of STATEMENT_LIST:
STATEMENT_NUMB&|TYPE |SUMMARY |SUC&|CHANGED_COLUMNS|ROW_LIST
---------------------------------------------------------------------------------------------
********************************
* FAILED STATEMENT *
********************************
1 |WORK UNIT |CALL PLACEORDER |F |NULL |RowListVTI
This row displays once for each statement in the transaction, and includes information about the statement, such as the statement number, statement type, and RowListVTI, which is another VTI containing information about each statement in the transaction.
The next row shows the values for RowListVTI.
Contents of ROW_LIST:
RLV_ROW_NUMBER |RLV_TYPE |RLV_STMT_LIST |ARG_1 |ARG_2 |ARG&|ARG_4 |ARG_5
---------------------------------------------------------------------------------
1 |ARGUMENTS |NULL |018f00& |1 |ann |barbie|11
This row displays once for each row in the StatementListVTI.
The contents of RowListVTI depend on the statement type. In the output above, the statement type is a work unit, and RowListVTI contains information about the work unit arguments.
RowListVTI has a column called RLV_STMT_LIST that holds a nested StatementListVTI for any nested statement. If no nested statements exist, RLV_STMT_LIST is null. If a nested statement exists, its StatementListVTI and RowListVTI are also displayed.
More detail on TransactionListVTI, StatementListVTI, and RowListVTI are available in the javadoc for these classes.
The next example uses this query:
SELECT e.errorinfo.getErrorMessage(), printFailedTransaction(e.errorID) FROM SYS.SYSERRORS e
to show sample output for a refresh containing three failed transactions:
- a failed insert containing a nested statement
- a failed simple update
- a multi-statement transaction consisting of an insert containing a nested statement and a failed work unit call.
SQLCol1 |SQLCol2
---------------------------------------------------------------------------------------------------
The statement was aborted because it would have caused a duplicate key value in & |TransactionLis&
Contents of SQLCol2:
TRAN_NUMBER |ORIGINATING_DBID |ORIGINATING_CO&|SUMMARY |STATEMENT_LIST
-------------------------------------------------------------------------------------
1 |71ce41d2-00d8-daf|(1,14134) |TRANSACT&|StatementListV&
Contents of STATEMENT_LIST:
STATEMENT_NUMB&|TYPE |SUMMARY |SUC&|CHANGED_COLUMNS|ROW_LIST
-------------------------------------------------------------------------------------
********************************
* *
* FAILED STATEMENT *
* *
********************************
1 |INSERT |INSERT APP.BUYERS |F |NULL |RowListVTI
Contents of ROW_LIST:
RLV_ROW_NUMBER|RLV_TYPE |RLV_STMT_LIST |"SELLERID"|"BUYERNUMBER"|"NAME"|"ADDRESS"
-----------------------------------------------------------------------------------
1 |STATEMENT LIST |StatementListV&|NULL |NULL |NULL |NULL
Contents of RLV_STMT_LIST:
STATEMENT_NUMB&|TYPE |SUMMARY |SUC&|CHANGED_COLUMNS|ROW_LIST
------------------------------------------------------------------------------------------
1 |UPDATE |UPDATE APP.SELLERS |S |{3} |RowListVTI
Contents of ROW_LIST:
RLV_ROW_NUMBER |RLV_TYPE |RLV_STMT_LIST |"RLV_OLD_&|"SELLERID"|"MAXORDER" |"MAXBUYER"
------------------------------------------------------------------------------------------
1 |UPDATE ROW |NULL |Spuds |NULL |NULL |101
1 row selected
1 row selected
2 |INSERT ROW |NULL |Spuds |101 |Acme |Paradise
2 rows selected
1 row selected
1 row selected
The statement was aborted because it would have caused a duplicate key value &|TransactionLis&
Contents of SQLCol2:
TRAN_NUMBER |ORIGINATING_DBID |ORIGINATING_CO& |SUMMARY |STATEMENT_LIST
--------------------------------------------------------------------------------------------------
1 |71ce41d2-00d8-daf7-831e-0&|(1,47271) |TRANSACTION |StatementListV&
Contents of STATEMENT_LIST:
STATEMENT_NUMB&|TYPE |SUMMARY |SUC&|CHANGED_COLUMNS|ROW_LIST
----------------------------------------------------------------------------------------------
********************************
* *
* FAILED STATEMENT *
* *
********************************
1 |UPDATE |UPDATE APP.BUYERS |F |{3, 4} |RowListVTI
Contents of ROW_LIST:
RLV_ROW_N&|RLV_TY& |RLV_STMT_LI&|"RLV_OLD_&|"RLV_OLD_KEY_N&|"SELLE&|"BUYER&|"NAM&| "ADDR&
---------------------------------------------------------------------------------------
1 |UPDATE ROW |NULL |Spuds |Angstwear |NULL |NULL |Acme |Paradise
1 row selected
1 row selected
1 row selected
The exception 'COM.cloudscape.synchronization.SkipTransactionSQLException: &|TransactionLis&
Contents of SQLCol2:
TRAN_NUMBER |ORIGINATING_DBID |ORIGINATING_CO&|SUMMARY |STATEMENT_LIST
-------------------------------------------------------------------------------------------------
1 |71ce41d2-00d8-daf7-831e-0&0|(1,67737) |TRANSACTION |StatementListV&
Contents of STATEMENT_LIST:
STATEMENT_NUMB&|TYPE |SUMMARY |SUC&|CHANGED_COLUMNS|ROW_LIST
----------------------------------------------------------------------------------------------
1 |INSERT |INSERT APP.BUYERS |S |NULL |RowListVTI
Contents of ROW_LIST:
RLV_ROW_NUM&|RLV_TYPE |RLV_STMT_LIST |"SELLER&|"BUYERNUM&|"NAME" |"ADDRESS"
---------------------------------------------------------------------------------------
1 |STATEMENT LIST |StatementListV&|NULL |NULL |NULL |NULL
Contents of RLV_STMT_LIST:
STATEMENT_NUMB&|TYPE |SUMMARY |SUC&|CHANGED_COLUMNS|ROW_LIST
------------------------------------------------------------------------------------
1 |UPDATE |UPDATE APP.SELLERS |S |{3} |RowListVTI
Contents of ROW_LIST:
RLV_ROW_NUMBER |RLV_TYPE |RLV_STMT_LIST |"RLV_OLD_&|"SELLERID"|"MAXORDER" |"MAXBUYER"
----------------------------------------------------------------------------------
1 |UPDATE ROW |NULL |Spuds |NULL |NULL |102
1 row selected
1 row selected
2 |INSERT ROW |NULL |Spuds |102 |Hoedown |Flint, ME
2 rows selected
********************************
* *
* FAILED STATEMENT *
* *
********************************
2 |WORK UNIT |CALL NEWORDER |F |NULL |RowListVTI
Contents of ROW_LIST:
RLV_ROW_NUMBER |RLV_TYPE |RLV_STMT_LIST |ARG_1 |ARG_2 |ARG_3 |ARG_4
----------------------------------------------------------------------------------------
1 |ARGUMENTS |NULL |Spuds |201 |100 |Tractor
1 row selected
2 rows selected
1 row selected
3 rows selected
|