How to check database consistency in Derby?

Document ID : KB000009666
Last Modified Date : 14/02/2018
Show Technical Document Details
Introduction:

Is there a way to check Derby database consistency?

Background:

Sometimes WCC is not performing very well with database errors in the WCC logs:

ERROR X0X62: Inconsistency found between table 'WCC.MON_JOB' and   index 'SQL130419105827170'.

Environment:
WCC configured to use its internal Derby database.This is the default WCC database.
Instructions:

Steps to check Derby database consistency are:

1) Logon as root

2) Go to directory  /opt/CA/WorkloadCC/derby/lib

3) Then connect to Derby database

          java -jar derbyrun.jar ij
          ij version 10.xx


         ij> connect 'jdbc:derby://localhost:1527/WCC_MAIN' user '<your_wcc_user>' password  '<your_wcc_password>';

         ij> SELECT schemaname || '.' || tablename as TableName,
           > SYSCS_UTIL.SYSCS_CHECK_TABLE(schemaname, tablename) AS OK
           > FROM sys.sysschemas s, sys.systables t
           > WHERE s.schemaid = t.schemaid
           > and t.tabletype = 'T';

4)   If there is an error with the database, you can get errors like that:

ERROR X0X62: Inconsistency found between table 'WCC.MON_JOB' and   index 'SQL130419105827170'.  Error when trying to retrieve row location '(89,18)' from the table.  The full index key, including the row location, is '{ 1, (89,18) }'. The suggested corrective action is to recreate the index.

5)   If there is no problem with Derby, you will get a full list of Derby tables with status OK = 1

TABLENAME                                                                                                                       |OK

--------------------------------------------------------------------------------------------------------------------

WCC.CFG_SERVER                                                                                                             |1
WCC.CFG_AUTOSYS_11_3                                                                                                 |1
WCC.CFG_CREDENTIAL                                                                                                       |1
WCC.CFG_GLOBAL_PREF                                                                                                     |1
            ../..  

WCC.MON_EVENT_SEVERITY                                                                                                |1
WCC.MON_EVENT_STATUS                                                                                                   |1
WCC.MON_JOB_STATUS                                                                                                       |1
WCC.MON_SERVER_STATUS                                                                                                 |1

There is no command to rebuild indexes in Derby.

The database has to be recreated.

Another option is to move WCC database from Derby to Oracle.

Additional Information:

If an index is broken, you'll have to rebuild Derby with steps described in Technical Document TEC1434254