StarQuest Technical Documents

SQDR Plus: Db2 LUW Staging Database - Storage Maintenance

Last Update:10 November 2021
Product: SQDR Plus
Version: 4.2 & later
Article ID: SQV00PL059

Abstract

In the four tier StarQuest Data Replication architecture, SQDR Plus runs on Tier 2 and uses a local IBM Db2 for Linux, UNIX & Windows (Db2 LUW) database as the control and staging database for each Staging Agent.

Db2 LUW is designed to be installed and run with minimal administration. In normal operation, the SQDR Plus user has no direct interaction with the Db2 LUW database software, which should be considered as embedded and for exclusive use of the replication software. However, conditions may occur that require some interaction with Db2.

Over time, the storage used by the SQDR Plus staging database may grow, especially after periods of large amounts of incremental changes or a backlog caused by temporary unavailability of tier 3 or tier 4. This technical document provides guidance in reducing the storage used by the Db2 LUW staging database.

Additional guidance in working with Db2 LUW is provided in this technical document (which in turn contains pointers to other related technical documents)

Tips for the SQDR Plus Db2 LUW Staging Database

Topics

• Reducing Table Space Storage
• REORG Hints
• Monitoring the progress of ALTER TABLESPACE REDUCE
• Adding Storage to a Table Space

Reducing Table Space Storage

The size of the Db2 staging database is not related to the size of any source table; it is related to the number of tables to be monitored and the number of changes to be staged at any given time. However, a backlog caused by the tier 3 SQDR client not fetching updates may cause the database to grow to a large size. The disk space usage will not shrink, even after the data has been retrieved from the staging database and SQDR Plus pruning run. In that case, you may need to do the following tasks:

1. REORG the staging tables - see REORG Hints below for details.
2. After the REORG is complete, alter the the table space. You can also specify reduction by a percentage.

ALTER TABLESPACE USERSPACE1 LOWER HIGH WATER MARK
ALTER TABLESPACE USERSPACE1 REDUCE MAX
ALTER TABLESPACE SQDRSPACE LOWER HIGH WATER MARK
ALTER TABLESPACE SQDRSPACE REDUCE MAX

Example: The following Windows batch file will reorganize all the tables in the SQDR schema in all the SQDRPn databases and issue the ALTER TABLESPACE command:

@ECHO OFF

setlocal enableDelayedExpansion

for /f "tokens=4" %%x in ('db2 list database directory ^| find "Database name" ^| find "SQDR"') do (

db2 connect to %%x >> reorg.log
db2 -x "SELECT 'reorg table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,50),' LONGLOBDATA;' FROM SYSCAT.TABLES WHERE (type= 'T') AND (tabschema = 'SQDR')" > reorg_sqdr.sql
db2 -tvf reorg_sqdr.sql >> reorg.log
db2 ALTER TABLESPACE USERSPACE1 LOWER HIGH WATER MARK
db2 ALTER TABLESPACE USERSPACE1 REDUCE MAX
db2 ALTER TABLESPACE SQDRSPACE LOWER HIGH WATER MARK
db2 ALTER TABLESPACE SQDRSPACE REDUCE MAX
db2 disconnect all
)

The above command reorganizes the control tables in the schema SQDR, which is the default. If you have multiple agents sharing a staging database, there will be tables in schemas SQDR0, SQDR1, etc., and you will want to repeat the above query for each additional schema e.g. tabschema = 'SQDR0'. To view the schemas in use with Control Center, select the server name (i.e. the top item in the left panel) , which lists the details about the agents in the right panel. Examine the ControlDB column for names like SQDRP0(SQDR4).

The following command shows tablespace usage:

db2 SELECT varchar(tbsp_name, 30) as tbsp_name, reclaimable_space_enabled, tbsp_free_pages, tbsp_page_top, tbsp_usable_pages FROM TABLE(MON_GET_TABLESPACE('',-2)) AS t ORDER BY tbsp_free_pages ASC

For more information, see the IBM document

STEPS TO RECLAIM ALL AVAILABLE SPACE FROM A RECLAIMABLE STORAGE DMS AUTOMATIC STORAGE TABLESPACE

 

REORG Hints

You can REORG individual tables using the GUI interface of Data Studio Client or by issuing the REORG command.

REORG SQDR.xxxxxxxx

If you see an Error -289, then there is insufficient space in the table space to do the REORG; specify another table space to use as a temporary location or add storage to the table space:

REORG SQDR.xxxxxxx USE TEMPSPACE1

To view the size of all tables in the SQDR schema, issue the following SQL:

SELECT tabname,TABSCHEMA, SUM(DATA_OBJECT_P_SIZE)+
SUM(INDEX_OBJECT_P_SIZE)+ SUM(LONG_OBJECT_P_SIZE)+
SUM(LOB_OBJECT_P_SIZE)+ SUM(XML_OBJECT_P_SIZE) FROM
SYSIBMADM.ADMINTABINFO where TABSCHEMA='SQDR' group by tabname,tabschema

The following SQL displays the largest tables (those > 10000 pages) from SQDR in descending order

SELECT tabname,TABSCHEMA, DATA_OBJECT_P_SIZE+INDEX_OBJECT_P_SIZE+LONG_OBJECT_P_SIZE+LOB_OBJECT_P_SIZE+XML_OBJECT_P_SIZE as TOTAL_SIZE
FROM SYSIBMADM.ADMINTABINFO where ((TABSCHEMA='SQDR') AND (DATA_OBJECT_P_SIZE+INDEX_OBJECT_P_SIZE+LONG_OBJECT_P_SIZE+LOB_OBJECT_P_SIZE+XML_OBJECT_P_SIZE > 10000)) ORDER BY TOTAL_SIZE DESC

To reorganize all the tables in the SQDR schema, issue the following from the Db2 command line environment:

db2 -x "SELECT 'reorg table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,50),' LONGLOBDATA;' FROM SYSCAT.TABLES WHERE (type= 'T') AND (tabschema = 'SQDR')" > reorg_sqdr.sql
db2 -tvf reorg_sqdr.sql

You can enable automatic REORG using Data Studio Web Console or by issuing the following commands; however, be aware of maintenance window times, as a system-initiated REORG could affect replications in progress - i.e. the maintenance window time should be configured for a period of low or no replication activity.

db2 update db cfg using AUTO_MAINT ON
db2 update db cfg using AUTO_TBL_MAINT ON
db2 update db cfg using AUTO_RUNSTATS ON
db2 update db cfg using AUTO_REORG ON

Monitoring the progress of ALTER TABLESPACE REDUCE

Typically the REORG of the tables can take some amount of time (5 minutes to an hour) but the ALTER TABLESPACE REDUCE step happens quickly.

However, we have occasionally seen the ALTER TABLESPACE REDUCE process happen slowly. If you issue ALTER TABLESPACE REDUCE again, you will get an error that the tablespace is in use by another process.

You can monitor the progress with this SQL statement:

db2 "select num_extents_left from table(sysproc.MON_GET_EXTENT_MOVEMENT_STATUS('SQDRSPACE',-1))"

On some occasions, we have not seen a reduction in disk usage occur until one or more of these events happen:

• disconnect from the database
  
• stop and restart Db2
  
• reboot the system

Adding Storage to a Table Space

The SQDR Plus staging tables are stored in a table space named USERSPACE1, which uses Automatic Storage (note that the Db2 utilities show the type of storage as DMS: Database managed space). The table space will automatically grow to use as much disk space as is available on the assigned disk. If that is inadequate, you have several options:

• Use the procedure Moving the Db2 control database used by the Staging Agent to move the entire database to a larger disk.
• Depending on your environment, it may be possible to expand the disk (e.g. by increasing the size of a virtual disk in a VMWare environment, or adding additional drives to a Windows dynamic disk).
• Add additional containers to the storage group (and thus the table space)

This section describes the last option, which is likely to be the quickest and least disruptive method, especially if the additional storage is only needed temporarily.

1. Determine the name of the storage group (default IBMSTOGROUP) by displaying the storage options of the table space in Data Studio Client or issue the following SQL:

   SELECT SGNAME FROM SYSCAT.STOGROUPS

2. Issue the following SQL statements to add another disk to the storage group and rebalance the contents of the table space:

   ALTER STOGROUP IBMSTOGROUP ADD 'E:'

   ALTER TABLESPACE USERSPACE1 REBALANCE

You may need to stop and start Db2 If the REBALANCE command fails with this error:

SQL2094W The rebalance of table space "USERSPACE1" either did not add or drop containers, or there was insufficient disk space to create all of the containers. Reason code: "1". SQLSTATE=01690

If you wish to remove the second disk from the STOGROUP (for example, the extra storage was needed temporarily in order to perform maintenance and the above REORG's), issue the following SQL:

ALTER STOGROUP IBMSTOGROUP DROP 'C:'
ALTER TABLESPACE USERSPACE1 REBALANCE

For additional information, refer to the IBM documentation for Altering Automatic Storage Table Spaces.