NOTE: Metadata cleanup is a Technology Preview (Complete But Not Tested) feature in Trafodion Release 1.1.


 

Introduction

 

This document describes support for the CLEANUP command in Trafodion. CLEANUP is used to clean up objects whose information is inconsistent in the metadata. This command can be used to clean up individual objects or all objects in a schema. This command can also be used to locate all inconsistent objects in the Trafodion metadata and then clean them up.

 

Syntax

 

Cleanup of Individual Objects

 

CLEANUP <obj-type> <obj-name> [ , UID <object-uid> ]

	<obj-type> is:       TABLE | INDEX | SEQUENCE | OBJECT
	<obj-name> is:       Name of the object that needs to be cleaned up
	<object-uid> is:     UID (Unique ID) of the object that is stored in metadata

 

Cleanup of All Objects in a Schema

 

CLEANUP [ PRIVATE | SHARED ] SCHEMA <schema-name>

	<schema-name> is:    Name of the schema whose contents need to be cleaned up

 

Cleanup of an Object Based on Its Object UID

 

CLEANUP UID <object-uid>

	<object-uid> is:     UID (Unique ID) of the object that is stored in metadata

 

Cleanup of All Inconsistent Metadata Entries

 

CLEANUP  METADATA  [ , CHECK ] [ , RETURN DETAILS ]

 

Object Cleanup

 

Inconsistent objects can be cleaned up and removed from the metadata and HBase by using one of the following options.

 

Cleanup of Individual Objects

 

CLEANUP <obj-type> <obj-name> [ , UID <object-uid> ]

 

This command will clean up the specified object from all Trafodion metadata tables and HBase. If an object cannot be found in the metadata but exists in HBase, then it will be removed from HBase. All dependent objects, such as indexes, views, LOBs, internal sequences, and constraints, are also removed from the metadata and HBase.

 

If an object type is specified as TABLE, INDEX, or SEQUENCE and that object exists in the metadata, then a check will be done to make sure that the specified <obj-name> is of the specified type. An error is returned if they are not the same.

 

If an object type is specified as OBJECT, then any object that matches the specified name is removed from the metadata and HBase.

 

Optionally, an object UID can also be specified. If an object exists in the OBJECTS metadata table, then the stored object UID is compared with the specified object UID. If they do not match, then cleanup is not done. If an object does not exist in the OBJECTS metadata table, then all entries matching the specified object UID are removed from the metadata tables.

 

Cleanup of All Objects in a Schema

 

CLEANUP [ PRIVATE | SHARED ] SCHEMA <schema-name>

 

This command will clean up all objects that are created in the specified schema. Cleanup will be done following the steps listed in Cleanup of Individual Objects.

 

Cleanup of an Object Based on Its Object UID

 

CLEANUP UID <object-uid>

 

This command will remove all entries from all metadata tables that match the specified object UID. If the specified object UID exists in the OBJECTS metadata table and the object name associated with that UID can be retrieved, then that object is also removed from HBase.

 

Metadata Cleanup

 

CLEANUP  METADATA  [ , CHECK ] [ , RETURN DETAILS ]

 

This command will look for inconsistent entries in the metadata, return information about them, and clean them up. Multiple checks are done to validate metadata consistency. For details, see the next sections.

 

One can check for inconsistent metadata entries without actually cleaning them up. This can be done by specifying the CHECK option. A summary of inconsistent objects belonging to the categories listed before is returned. If RETURN DETAILS is specified, then details on which objects are inconsistent is also returned.

 

Orphan Metadata Objects

 

This check is done to validate that objects that exist in metadata also exist in HBase. If the object does not exist in HBase, then it is marked as an orphan metadata object.

 

Orphan HBase Objects

 

This check is done to validate that objects that exist in HBase also exist in metadata. If the object does not exist in metadata, then it is marked as an orphan HBase object.

 

Inconsistent Object Entries

 

This check is done to make sure that all entries in the OBJECTS table also exist in the OBJECTS unique index (OBJECTS_UNIQ_IDX) and that all entries in OBJECTS_UNIQ_IDX also exist in the OBJECTS table. It also checks to make sure that all entries in the COLUMNS table have a corresponding entry in the OBJECTS table. If any of the previous checks fail, then that entry is marked as inconsistent.

 

Inconsistent View Entries

 

This check is done to make sure that all view entries are consistent among the views and the OBJECTS table.

 

Security and Privileges

 

To clean up an object, one needs to have DROP privileges. If object privileges cannot be retrieved from the metadata or if metadata cleanup is being done, then one needs to be DB__ROOT to perform the cleanup.

 

Consistency Checks During Access

 

If an object exists in the metadata but does not exist in HBase, then error 4254 will be returned if that object is accessed during any DML or DDL operation.

 

An object is also marked as inconsistent during any complex DDL operation. For example, if a CREATE TABLE also creates implicit indexes or constraints, then the object will be marked as inconsistent until the complex DDL operation is completed. If an abnormal error happens (like a process crash or a node failure), then that object remains inconsistent. Any attempt to access that object will return error 4254.

 

An inconsistent object will need to be cleaned up before it can be accessed.

 

Examples

 

Accessing Inconsistent Objects

 

Table T026T1 has been marked as inconsistent from a previous session. Any operation on it will return error 4254. For this test case, a metadata update was done, and the table was marked as invalid in the OBJECTS table.

 

>>invoke t026t1;

*** ERROR[4254] Object TRAFODION.SCH026.T026T1 has invalid state and cannot be accessed. Use cleanup command to drop it.

*** ERROR[4082] Object TRAFODION.SCH026.T026T1 does not exist or is inaccessible.

--- SQL operation failed with errors.
>>drop table t026t1;

*** ERROR[4254] Object TRAFODION.SCH026.T026T1 has invalid state and cannot be accessed. Use cleanup command to drop it.

--- SQL operation failed with errors.
>>select * from t026t1;

*** ERROR[4254] Object TRAFODION.SCH026.T026T1 has invalid state and cannot be accessed. Use cleanup command to drop it.

*** ERROR[4082] Object TRAFODION.SCH026.T026T1 does not exist or is inaccessible.

*** ERROR[8822] The statement was not prepared.

>>

 

Accessing Incorrect Objects

 

If an object type is specified, then the object stored in the metadata must match it. In this example, object ‘TI’ is an index that cannot be cleaned up as a table.

 

>>create index ti on t(b);

--- SQL operation complete.
>>cleanup table ti;

*** ERROR[1389] Object TI does not exist in Trafodion.

*** ERROR[4256] Object type specified for this object does not match the object type stored in metadata.

--- SQL operation failed with errors.
>>

 

Cleaning Up an Inconsistent Object

 

A CLEANUP command will need to be used to clean up an object in invalid state.

 

>>drop table t026t1;

*** ERROR[4254] Object TRAFODION.SCH026.T026T1 has invalid state and cannot be accessed. Use cleanup command to drop it.

--- SQL operation failed with errors.
>>cleanup  table t026t1;

--- SQL operation complete.
>>invoke t026t1;

*** ERROR[4082] Object TRAFODION.SCH026.T026T1 does not exist or is inaccessible.

--- SQL operation failed with errors.
>>

 

DROP TABLE, CREATE TABLE, DROP INDEX, and CREATE INDEX failures will call cleanup.

 

Cleaning Up an Object by Specifying Its UID

 

Specify object T by its object UID and clean it up.

 

>>select object_uid from "_MD_".objects where schema_name = 'SCH026' and object_name = 'T';

OBJECT_UID          
--------------------

  127287511636506969

--- 1 row(s) selected.
>>cleanup uid 127287511636506969;

--- SQL operation complete.
>>invoke t;

*** ERROR[4082] Object TRAFODION.SCH026.T does not exist or is inaccessible.

--- SQL operation failed with errors.
>>

 

Metadata Cleanup

 

This example shows that the metadata is consistent and that there are no issues with it.

 

>>cleanup metadata, check, return details;
Metadata Cleanup: started, check only

  Start: Cleanup Orphan Objects Entries
  End:   Cleanup Orphan Objects Entries (0 entries found)

  Start: Cleanup Orphan Hbase Entries
  End:   Cleanup Orphan Hbase Entries (0 entries found)

  Start: Cleanup Inconsistent Objects Entries
  End:   Cleanup Inconsistent Objects Entries (0 entries found)

  Start: Cleanup Inconsistent Views Entries
  End:   Cleanup Inconsistent Views Entries (0 entries found)

Metadata Cleanup: done


--- SQL operation complete.
>>

 

This example shows inconsistencies in the metadata:

 

>>cleanup metadata, check, return details;
Metadata Cleanup: started, check only

  Start: Cleanup Orphan Objects Entries
    Entry #1(OBJECT): TRAFODION.SCH026.T026T10
  End:   Cleanup Orphan Objects Entries (1 entry found)

  Start: Cleanup Orphan Hbase Entries
    Entry #1(OBJECT): TRAFODION.SCH026.T026T11
  End:   Cleanup Orphan Hbase Entries (1 entry found)

  Start: Cleanup Inconsistent Objects Entries
    Entry #1(OBJECT): TRAFODION.SCH026.T026T12
    Entry #2(UID):    29899934458095403
  End:   Cleanup Inconsistent Objects Entries (2 entries found)

  Start: Cleanup Inconsistent Views Entries
  End:   Cleanup Inconsistent Views Entries (0 entries found)

Metadata Cleanup: done


--- SQL operation complete.
>>

 

Running the CLEANUP METADATA command cleans up the inconsistent metadata:

 

>>cleanup metadata, return details;
Metadata Cleanup: started

  Start: Cleanup Orphan Objects Entries
    Entry #1(OBJECT): TRAFODION.SCH026.T026T10
  End:   Cleanup Orphan Objects Entries (1 entry cleaned up)

  Start: Cleanup Orphan Hbase Entries
    Entry #1(OBJECT): TRAFODION.SCH026.T026T11
  End:   Cleanup Orphan Hbase Entries (1 entry cleaned up)

  Start: Cleanup Inconsistent Objects Entries
    Entry #1(OBJECT): TRAFODION.SCH026.T026T12
    Entry #2(UID):    29899934458095403
  End:   Cleanup Inconsistent Objects Entries (2 entries cleaned up)

  Start: Cleanup Inconsistent Views Entries
  End:   Cleanup Inconsistent Views Entries (0 entries cleaned up)

Metadata Cleanup: done


--- SQL operation complete.
>>
>>cleanup metadata, check, return details;
Metadata Cleanup: started, check only

  Start: Cleanup Orphan Objects Entries
  End:   Cleanup Orphan Objects Entries (0 entries found)

  Start: Cleanup Orphan Hbase Entries
  End:   Cleanup Orphan Hbase Entries (0 entries found)

  Start: Cleanup Inconsistent Objects Entries
  End:   Cleanup Inconsistent Objects Entries (0 entries found)

  Start: Cleanup Inconsistent Views Entries
  End:   Cleanup Inconsistent Views Entries (0 entries found)

Metadata Cleanup: done


--- SQL operation complete.
>>

 


  • No labels