[jira] [Commented] (TRAFODION-1645) Add sql support to retrieve region space and access stats for trafodion tables

["Anoop Sharma (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/TRAFODION-1645?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15025846#comment-15025846 ] 

Anoop Sharma commented on TRAFODION-1645:
-----------------------------------------

Syntax of commands to retrieve region stats.

-- virtual table to represent region stats that is to be used in traf queries.
     TABLE(REGION STATS (<table-name>))

-- Columns of stats virtual table
     CATALOG_NAME                             CHAR(256 BYTES)
     SCHEMA_NAME                               CHAR(256 BYTES) 
     OBJECT_NAME                                CHAR(256 BYTES)
     REGION_NUM                                  LARGEINT
     REGION_NAME                                CHAR(512 BYTES) 
     NUM_STORES                                  INT NO DEFAULT NOT NULL
     NUM_STORE_FILES                         INT NO DEFAULT NOT NULL
     STORE_FILE_UNCOMP_SIZE           LARGEINT NOT NULL
     STORE_FILE_SIZE                            LARGEINT NOT NULL
     MEM_STORE_SIZE                           LARGEINT NOT NULL
     READ_REQUESTS_COUNT              LARGEINT NOT NULL
     WRITE_REQUESTS_COUNT             LARGEINT NOT NULL
 
-- retrieve stats in relational form for table <table-name>. One row returned per region.
   select * from table(region stats (<table-name>))

-- retrieve stats in relational form for index <index-name>. One row returned per region.
   select * from table(region stats (index <index-name>))

-- retrieve stats in relational for for multiple tables.
   This will return stats for all tables that are returned by the inner 'get tables' query.
  select * from table(region stats (using select * from (get tables in schema <sch>) x(a)))

-- retrieve formatted stats for table.
  This command will return stats in readable format with one row per stats entry.
 get region stats for table <table-name>
 get region stats for index <index-name>

-- retrieve formatted stats for multiple tables
  get region stats for (select * from (get tables in schema <sch>) x(a)))


> Add sql support to retrieve region space and access stats for trafodion tables
> ------------------------------------------------------------------------------
>
>                 Key: TRAFODION-1645
>                 URL: https://issues.apache.org/jira/browse/TRAFODION-1645
>             Project: Apache Trafodion
>          Issue Type: New Feature
>            Reporter: Anoop Sharma
>            Assignee: Anoop Sharma
>            Priority: Minor
>
> Add support to retrieve region related statistics using trafodion sql commands.
> Capabilities needed are listed below:
> -- represent region space and access stats of a trafodion table or index as a  
>    virtual relational table
> -- run sql queries against that virtual table to return data. These queries
>     could be used to aggregate or group or order returned data.
> -- provide support to return region stats in a formatted way for display
> -- provide support to return stats for multiple tables in one query



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)