http://git-wip-us.apache.org/repos/asf/incubator-trafodion-site/blob/604f53d6/docs/2.2.0/sql_reference/dependencies.html ---------------------------------------------------------------------- diff --git a/docs/2.2.0/sql_reference/dependencies.html b/docs/2.2.0/sql_reference/dependencies.html index 57eebd9..53650a0 100644 --- a/docs/2.2.0/sql_reference/dependencies.html +++ b/docs/2.2.0/sql_reference/dependencies.html @@ -1,5 +1,5 @@ - + @@ -10,7 +10,7 @@ @import url("./css/site.css"); - + @@ -27,7 +27,7 @@
- Last Published: 2017-05-02 + Last Published: 2017-05-03  | Version: 2.2.0
Trafodion SQL Reference Manual http://git-wip-us.apache.org/repos/asf/incubator-trafodion-site/blob/604f53d6/docs/2.2.0/sql_reference/index.html ---------------------------------------------------------------------- diff --git a/docs/2.2.0/sql_reference/index.html b/docs/2.2.0/sql_reference/index.html index d5effa8..e9ef5e0 100644 --- a/docs/2.2.0/sql_reference/index.html +++ b/docs/2.2.0/sql_reference/index.html @@ -1005,39 +1005,46 @@ table.CodeRay td.code>pre{padding:0}
  • 4. SQL Utilities @@ -11413,7 +11420,7 @@ authorization are enabled in Trafodion. For more information, see
    GRANT {privilege [,privilege]... |ALL [PRIVILEGES]}
       ON [object-type] [schema.]object
    -  TO {grantee}
    +  TO grantee
       [WITH GRANT OPTION]
       [[GRANTED] BY grantor]
     
    @@ -17181,6 +17188,10 @@ This section describes the Trafodion SQL utilities:

    +

    CLEANUP Statement

    +

    Cleans up objects whose information is inconsistent in the metadata.

    + +

    LOAD Statement

    Uses the Trafodion Bulk Loader to load data from a source table, either a Trafodion table or a Hive table, into a target Trafodion table.

    @@ -17223,7 +17234,396 @@ to connect to and issue SQL utilities, see the
    -

    4.1. LOAD Statement

    +

    4.1. CLEANUP Statement

    +
    +

    The CLEANUP Statement cleans 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.

    +
    +
    +

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

    +
    +
    +
    +
    CLEANUP object-type object-name [ ,UID {object-uid} ]
    +
    +CLEANUP [ PRIVATE | SHARED ] SCHEMA {schema-name}
    +
    +CLEANUP UID {object-uid}
    +
    +CLEANUP METADATA [ , CHECK ] [ , RETURN DETAILS ]
    +
    +
    +
    +
    +
    object-type is: TABLE | INDEX | SEQUENCE | OBJECT
    +
    +object-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
    +
    +schema-name is: Name of the schema whose contents need to be cleaned up
    +
    +
    +
    +

    4.1.1. Syntax Description of CLEANUP Statement

    +
    +
      +
    • +

      CLEANUP object-type object-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.

      +
      +
      +
        +
      • +

        object-type

        +
        +
          +
        • +

          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 object-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.

          +
        • +
        +
        +
      • +
      • +

        object-name

        +
        +

        Specifies the name of the object to be removed.

        +
        +
      • +
      • +

        object-uid

        +
        +

        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 [ PRIVATE | SHARED ] SCHEMA {schema-name}

      +
      +

      This command will clean up all objects that are created in the specified schema.

      +
      +
    • +
    • +

      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.

      +
      +
    • +
    • +

      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.

      +
      +
      +
        +
      • +

        CHECK

        +
        +

        One can check for inconsistent metadata entries without actually cleaning them up. +This can be done by specifying the CHECK option. If CHECK is specified, the following checks will be performed simultaneously, and a summary of inconsistent objects belonging to the categories listed below will be 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.

          +
          +
        • +
        +
        +
      • +
      • +

        RETURN DETAILS

        +
        +

        If RETURN DETAILS is specified, then details on which objects are inconsistent is also returned.

        +
        +
      • +
      +
      +
    • +
    +
    +
    +
    +
    +

    4.1.2. Considerations for CLEANUP Statement

    +
    +
    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 (such as 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.

    +
    +
    +
    +
    +
    +

    4.1.3. Examples of CLEANUP Statement

    +
    +
      +
    • +

      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.
      +
      +
      +
    • +
    +
    +
    +
    +
    +
    +

    4.2. LOAD Statement

    The LOAD statement uses the Trafodion Bulk Loader to load data from a source table, either a Trafodion table or a Hive table, into a target @@ -17312,7 +17712,7 @@ option is:

    -

    4.1.1. Syntax Description of LOAD

    +

    4.2.1. Syntax Description of LOAD

    • @@ -17415,7 +17815,7 @@ inserts without a transaction.

    -

    4.1.2. Considerations for LOAD

    +

    4.2.2. Considerations for LOAD

    Required Privileges
    @@ -17624,7 +18024,7 @@ lower compaction activity after completion than Trickle Load.

    -

    4.1.3. Examples of LOAD

    +

    4.2.3. Examples of LOAD

    • @@ -17700,7 +18100,7 @@ Task: POPULATE INDEX Status: Ended ET: 00:00:05.262
    -

    4.2. POPULATE INDEX Utility

    +

    4.3. POPULATE INDEX Utility

    The POPULATE INDEX utility performs a fast INSERT of data into an index from the parent table. You can execute this utility in a client-based @@ -17715,7 +18115,7 @@ index-option is:

    -

    4.2.1. Syntax Description of POPULATE INDEX

    +

    4.3.1. Syntax Description of POPULATE INDEX

    • @@ -17761,7 +18161,7 @@ SELECT is allowed.

    -

    4.2.2. Considerations for POPULATE INDEX

    +

    4.3.2. Considerations for POPULATE INDEX

    When POPULATE INDEX is executed, the following steps occur:

    @@ -17828,7 +18228,7 @@ true:

    -

    4.2.3. Examples of POPULATE INDEX

    +

    4.3.3. Examples of POPULATE INDEX

    • @@ -17854,7 +18254,7 @@ uses the default schema:

    -

    4.3. PURGEDATA Utility

    +

    4.4. PURGEDATA Utility

    The PURGEDATA utility performs a fast DELETE of data from a table and its related indexes. You can execute this utility in a client-based tool @@ -17866,7 +18266,7 @@ like TrafCI.

    -

    4.3.1. Syntax Description of PURGEDATA

    +

    4.4.1. Syntax Description of PURGEDATA

    object

    @@ -17876,7 +18276,7 @@ like TrafCI.

    -

    4.3.2. Considerations for PURGEDATA

    +

    4.4.2. Considerations for PURGEDATA

    • @@ -17924,7 +18324,7 @@ case.

    -

    4.3.3. Examples of PURGEDATA

    +

    4.4.3. Examples of PURGEDATA

    • @@ -17942,7 +18342,7 @@ indexes, their data is also purged.

    -

    4.4. UNLOAD Statement

    +

    4.5. UNLOAD Statement

    The UNLOAD statement unloads data from Trafodion tables into an HDFS location that you specify. Extracted data can be either compressed or @@ -17967,7 +18367,7 @@ option is:

    -

    4.4.1. Syntax Description of UNLOAD

    +

    4.5.1. Syntax Description of UNLOAD

    • @@ -18102,7 +18502,7 @@ each table name.

    -

    4.4.2. Considerations for UNLOAD

    +

    4.5.2. Considerations for UNLOAD

    • @@ -18139,7 +18539,7 @@ component.

    -

    4.4.3. Examples of UNLOAD

    +

    4.5.3. Examples of UNLOAD

    • @@ -18173,7 +18573,7 @@ Task: MERGE FILES Status: Ended ET: 00:00:00.063
    -

    4.5. UPDATE STATISTICS Statement

    +

    4.6. UPDATE STATISTICS Statement

    The UPDATE STATISTICS statement updates the histogram statistics for one or more groups of columns within a table. These statistics are used to @@ -18224,7 +18624,7 @@ sample-table-clause is:

    -

    4.5.1. Syntax Description of UPDATE STATISTICS

    +

    4.6.1. Syntax Description of UPDATE STATISTICS

    • @@ -18515,7 +18915,7 @@ can then be used for later incremental statistics update.

    -

    4.5.2. Considerations for UPDATE STATISTICS

    +

    4.6.2. Considerations for UPDATE STATISTICS

    Using Statistics
    @@ -18781,7 +19181,7 @@ histograms, but at the price of a longer-running operation.

    -

    4.5.3. Examples of UPDATE STATISTICS

    +

    4.6.3. Examples of UPDATE STATISTICS

    • @@ -44065,7 +44465,7 @@ or 256 bytes of UTF8 text, whichever is less.