Identifying remote objects

You can identify remote objects such as storage groups, containers, and table spaces with specific table functions.

Identify a remote table space

Db2® Warehouse 11.5.9 on Red Hat OpenShift is configured with two remote table spaces, one for user data and the other one for user temporary tables. These tables can be seen by using the MON_GET_TABLESPACE table function, which includes the column CACHING_TIER. CACHING_TIER identifies that these table spaces are using the multi-tiered cache that includes both the buffer pools and the local on-disk cache on fast storage.

Remote table spaces are the only table spaces that have this ENABLED value, so this allows users to identify remote table spaces.

Remote table spaces are configured to show a multi-level cache. Using the MON_GET_TABLESPACE table function, you can see the cache levels in the CACHING_TIER column with a value of ENABLED.

This example shows the return output of table spaces OBJSTORESPACE1 and OBJSTORESPACEUTMP1 from the MON_GET_TABLESPACE table function:

SELECT VARCHAR(TBSP_NAME, 30) AS TBSP_NAME,
       MEMBER,
       TBSP_TYPE,
       CACHING_TIER
FROM TABLE(MON_GET_TABLESPACE('',-2)) AS T

TBSP_NAME                      MEMBER TBSP_TYPE  CACHING_TIER
------------------------------ ------ ---------- ------------

OBJSTORESPACE1                      0 DMS        ENABLED
OBJSTORESPACEUTMP1                  0 DMS        ENABLED
…

You can identify remote table spaces through the associated container with a FILE_REMOTE container type using MON_GET_CONTAINER.

By using the MON_GET_CONTAINER table function, you can also query information such as CONTAINER_TYPE and CONTAINER_NAME about the two table spaces. See example:

SELECT VARCHAR(CONTAINER_NAME,40) AS CONTAINER_NAME,  
       VARCHAR(TBSP_NAME,20) AS TBSP_NAME, 
       CONTAINER_TYPE 
FROM TABLE(MON_GET_CONTAINER('',0))

CONTAINER_NAME                           TBSP_NAME            CONTAINER_TYPE
---------------------------------------- -------------------- ----------------
…
FILE_REMOTE://IBMDEFAULTREMALIAS           OBJSTORESPACE1       DB2REMOTE
FILE_REMOTE://IBMDEFAULTREMALIAS           OBJSTORESPACEUTMP1   DB2REMOTE

In the example, the table spaces OBJSTORESPACE1 and OBJSTORESPACEUTMP1 have a CONTAINER_TYPE as FILE_REMOTE. The CONTAINER_NAME uses the internally defined IBMDEFAULREMTALIAS remote storage access alias, which contains all the information required to access the object storage container (bucket).

Identify a remote storage group

You can use ADMIN_GET_STORAGE_PATHS to identify remote storage group attributes. As with existing storage group types,ADMIN_GET_STORAGE_PATHS gives an administrative view of remote storage groups to get the storage path for each storage group and the file system information for each storage path.

In the case of remote storage groups, the DB_STORAGE_PATH will include a reference to a DB2REMOTE identifier and is prefixed with DB2REMOTE://.

SELECT VARCHAR(STORAGE_GROUP_NAME, 30) AS STOGROUP, VARCHAR(DB_STORAGE_PATH, 40) AS STORAGE_PATH FROM TABLE(ADMIN_GET_STORAGE_PATHS('',-1)) AS T

STOGROUP                       STORAGE_PATH
------------------------------ ----------------------------------------
...
SGOBJSTORE                     DB2REMOTE://RSG000

Identify a remote container

The container referencing the DB2REMOTE identifier can be retrieved using the MON_GET_CONTAINER table function.

CONTAINER_TYPE: For remote containers, the container type is FILE_REMOTE.
CONTAINER_NAME: For remote containers, the container name is DB2REMOTE://.

SELECT CONTAINER_TYPE, VARCHAR(CONTAINER_NAME,20) AS CONTAINER_NAME,  VARCHAR(TBSP_NAME,20) AS TBSP_NAME FROM TABLE(MON_GET_CONTAINER('',0))"

CONTAINER_TYPE   CONTAINER_NAME       TBSP_NAME
---------------- -------------------- --------------------
FILE_REMOTE      DB2REMOTE://RSG000   OBJSTORESPACE1

These MON_GET_CONTAINER monitoring elements remain the same for remote containers:

ACCESSIBLE: For remote containers, the ACCESSIBLE value is 1.
FS_ID: For remote containers, the FS_ID value is 0.
STRIPE_SET: For remote containers, the STRIPE_SET value is 0.
DBPARTITIONNUM: Not changed.
DB_STORAGE_PATH_ID: For remote containers, the DB_STORAGE_PATH_ID value is 1024.