Table and Data Storage Information

The SYS.SYSTABLES table provides information about all tables that are created in a GemFire XD distributed system. You can use different queries to obtain details about tables and the server groups that host data for those tables.

Displaying a List of Tables

To display a list of all tables in the cluster:

select TABLESCHEMANAME, TABLENAME from SYS.SYSTABLES order by TABLESCHEMANAME;
TABLESCHEMANAME            |TABLENAME
-----------------------------------------------
APP                        |PIZZA_ORDER_PIZZAS
APP                        |PIZZA
APP                        |TOPPING
APP                        |PIZZA_ORDER
APP                        |HIBERNATE_SEQUENCES
APP                        |HOTEL
APP                        |BASE
APP                        |PIZZA_TOPPINGS
APP                        |BOOKING
APP                        |CUSTOMER
SYS                        |SYSCONSTRAINTS                                                                                                                  
SYS                        |ASYNCEVENTLISTENERS                                                                                                             
SYS                        |SYSCOLPERMS                                                                                                                     
SYS                        |SYSKEYS                                                                                                                         
SYS                        |SYSFILES                                                                                                                        
SYS                        |SYSCONGLOMERATES                                                                                                                
SYS                        |SYSDEPENDS                                                                                                                      
SYS                        |SYSROUTINEPERMS                                                                                                                 
SYS                        |SYSTRIGGERS                                                                                                                     
SYS                        |SYSTABLES                                                                                                                       
SYS                        |SYSALIASES                                                                                                                      
SYS                        |SYSROLES                                                                                                                        
SYS                        |SYSDISKSTORES                                                                                                                   
SYS                        |SYSVIEWS                                                                                                                        
SYS                        |SYSSTATISTICS                                                                                                                   
SYS                        |SYSCOLUMNS                                                                                                                      
SYS                        |SYSCHECKS                                                                                                                       
SYS                        |GATEWAYSENDERS                                                                                                                  
SYS                        |SYSFOREIGNKEYS                                                                                                                  
SYS                        |SYSSCHEMAS                                                                                                                      
SYS                        |SYSTABLEPERMS                                                                                                                   
SYS                        |SYSSTATEMENTS                                                                                                                   
SYSIBM                     |SYSDUMMY1                                                                                                                       
SYSSTAT                    |SYSXPLAIN_SORT_PROPS                                                                                                            
SYSSTAT                    |SYSXPLAIN_DIST_PROPS                                                                                                            
SYSSTAT                    |SYSXPLAIN_STATEMENTS                                                                                                            
SYSSTAT                    |SYSXPLAIN_RESULTSET_TIMINGS                                                                                                     
SYSSTAT                    |SYSXPLAIN_SCAN_PROPS                                                                                                            
SYSSTAT                    |SYSXPLAIN_STATEMENT_TIMINGS                                                                                                     
SYSSTAT                    |SYSXPLAIN_RESULTSETS                                                                                                            

40 rows selected

Determining Where Data Is Stored

To determine which tables are deployed to a specific set of server groups:

select TABLESCHEMANAME, TABLENAME from SYS.SYSTABLES 
       where GROUPSINTERSECT(SERVERGROUPS, 'SG1,SG2');
TABLESCHEMANAME          |TABLENAME
--------------------------------------------------------------
APP                      |PIZZA_ORDER_PIZZAS
APP                      |PIZZA_ORDER
APP                      |BASE
APP                      |PIZZA_TOPPINGS

4 rows selected

For a specific table or set of tables, you can list all of the GemFire XD members that host that table's data:

select m.ID from SYS.SYSTABLES t, SYS.MEMBERS m where t.TABLESCHEMANAME='APP' 
     and t.TABLENAME='PIZZA' and  m.HOSTDATA = 1 
     and (LENGTH(t.SERVERGROUPS) = 0 or GROUPSINTERSECT(t.SERVERGROUPS, m.SERVERGROUPS));
ID
-------------------------------------
vmc-ssrc-rh156(23870)<v1>:23802/60824
vmc-ssrc-rh154(26751)<v4>:42054/49195
vmc-ssrc-rh156(23897)<v2>:37163/43747
vmc-ssrc-rh154(26739)<v3>:9287/48842

4 rows selected

Determining Whether a Table Is Replicated or Partitioned

The DATAPOLICY column specifies whether a table is replicated or partitioned, and whether a table is persisted to a disk store. For example:

select TABLENAME, DATAPOLICY from SYS.SYSTABLES where TABLESCHEMANAME = 'APP';
TABLENAME                   |DATAPOLICY
--------------------------------------------------
PIZZA                       |PERSISTENT_REPLICATE
PIZZA_ORDER_PIZZAS          |PARTITION
BASE                        |REPLICATE
TOPPING                     |REPLICATE
PIZZA_TOPPINGS              |PARTITION
PIZZA_ORDER                 |PERSISTENT_PARTITION

6 rows selected

Determining How Persistent Data Is Stored

For persistent tables, you can also display the disk store that persists the table's data, and whether the table uses synchronous or asynchronous persistence:

select TABLENAME, DISKATTRS from SYS.SYSTABLES where TABLESCHEMANAME = 'APP';
TABLENAME                  |DISKATTRS
----------------------------------------------
PIZZA                      |DiskStore is GFXD-DEFAULT-DISKSTORE; Synchronous writes to disk
PIZZA_ORDER_PIZZAS         |DiskStore is OVERFLOWDISKSTORE;Asynchronous writes to disk
BASE                       |NULL
TOPPING                    |NULL
PIZZA_TOPPINGS             |DiskStore is GFXD-DEFAULT-DISKSTORE; Synchronous writes to disk
PIZZA_ORDER                |DiskStore is GFXD-DEFAULT-DISKSTORE; Synchronous writes to disk

6 rows selected

Displaying Eviction Settings

Use the EVICTIONATTRS column to determine if a table uses eviction settings and whether a table is configured to overflow to disk. For example:

select TABLENAME, EVICTIONATTRS  from SYS.SYSTABLES where TABLESCHEMANAME = 'APP';
TABLENAME                   |EVICTIONATTRS
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PIZZA                       |NULL
PIZZA_ORDER_PIZZAS          | algorithm=lru-entry-count; action=overflow-to-disk; maximum=100
BASE                        |NULL
TOPPING                     |NULL
PIZZA_TOPPINGS              | algorithm=lru-entry-count; action=overflow-to-disk; maximum=100
PIZZA_ORDER                 |NULL

6 rows selected

Displaying Indexes

Join SYSTABLES with CONGLOMERATENAME to determine if a table has an index and display the indexed columns. For example:

select CONGLOMERATENAME from SYS.SYSCONGLOMERATES c, SYS.SYSTABLES t 
      where c.ISINDEX = 1 and c.TABLEID = t.TABLEID and t.TABLESCHEMANAME = 'APP' 
      and t.TABLENAME = 'PIZZA';
CONGLOMERATENAME
----------------
2__PIZZA__ID
6__PIZZA__BASE

2 rows selected

Displaying Installed AsyncEventListeners

If you install AsyncEventListener implementations, you can join the SYSTABLES, MEMBERS, and ASYNCEVENTLISTENERS tables to display the listener implementations associated with a table as well as the data store ID on which the listener is installed:
select t.*, m.ID DSID from SYS.SYSTABLES t, SYS.MEMBERS m, SYS.ASYNCEVENTLISTENERS a
       where t.tablename='<table>' and groupsintersect(a.SERVER_GROUPS, m.SERVERGROUPS)
       and groupsintersect(t.ASYNCLISTENERS, a.ID);

See SYSTABLES and ASYNCEVENTLISTENERS.