If the JSON configuration file contains an additional “transient_devices” or “transient_storage” setting, the data pool storage is used for the following purposes only:

• Storing synchronized Db2 z/OS table data
• Writing metadata, diagnostic data and processing data

In this case any transient data is written to “transient_devices” or “transient_storage”. For more details see this technote: Configuring and using transient storage for IBM Db2 Analytics Accelerator for z/OS on Z.

Multiple filesystems are defined to serve the different purposes. With having separate filesystems, the storage usage is isolated per purpose which leads to greater processing stability and performance. The defined filesystems are the following:

• clusterfs -> serves as the appliance_data_pool filesystem. It uses most of the defined data pool storage
  • /data: Stores the Db2 Warehouse table spaces. The following table spaces are created:
    • User data table space(s) named dwa<x>-<y>, where x and y are placeholders for positive numbers, for example “dwa0-1”. The table space(s) are used to store the synchronized user data tables. Dependent on the number of connected Db2 z/OS systems and the number of synchronized tables multiple table spaces can exist. The size of the table space(s) depends on the size of the synchronized tables and on the synchronization method (Full loads, partial loads, replication)
    • Catalog table space SYSCATSPACE. Used by Db2 Warehouse to store metadata. It can have a size of up to 512 GB.
    • Temporary table space TEMPSPACE1. Used by Db2 Warehouse to store temporary results of extensive sort, join or group by operations during query processing that cannot be executed exclusively in the system memory.
      The size of TEMPSPACE1 in /data depends on the “temp_working_space” setting in the JSON configuration file. Options:
      • Automatic: Default setting. TEMPSPACE1 is created as DMS table space with the smaller of the following two values (80% of the LPAR memory, 50% of the free space of the data pool)
      • <Fixed size>: TEMPSPACE1 is created as DMS table space with the specified size
      • Unlimited: Deprecated setting. TEMPSPACE1 is created as SMS table space and can grow until the appliance_data_pool file system is fully used.
      Note, this table space is created in /data only if no “transient_devices” or no “transient_storage” is defined in the JSON configuration file. Otherwise, it is created as SMS table space on the “transient devices” or on the “transient_storage” (NVMe disks). It can grow until the capacity of the “transient devices” or “transient_storage” is fully used.
  • /scratch: Stores temporary data that Db2 Warehouse itself needs for processing and user management. The amount of stored temporary data can vary, for example multiple hundred MBs or some GBs.
  • /local: Used to store Db2 Warehouse transaction logs. The number of logfiles and size of log files is pre-allocated and does not change.
  • In addition a swap file is created, which might be used during operation to swap. It is created with a size 20% of the LPAR memory, but with a maximum size of 64 GB.
• headfs -> used for the important accelerator metadata, Db2 Warehouse diagnostics data (FODC dumps) and accelerator diagnostics data. It has a fixed size of 60 GB, the mount point is /head. In addition, this separated defined file system ensures that Db2 Warehouse can continue running even if the appliance_data_pool filesystem is fully used. On a multi-node accelerator this filesystem exists on the head node only
• scratchfs -> used by the accelerator server for writing transient data, the mount point is /scratch/dwa/query-results. This transient data consists of
  • query results that tend to arrive faster than they can be picked up by the receiving client.
  • replication spill queues when a replication-enabled table is loaded to the accelerator and at the same time new data changes on this table are replicated to the accelerator.
  Note that this filesystem is only created in the data pool storage if no “transient_devices” or no “transient_storage” is defined in the JSON configuration file. In this case, the filesystem is allocated with ~20% of the defined data pool storage and does not change dynamically even if no transient data is written. It is defined as a separate filesystem to mainly isolate query result spilling from data loads and query execution for greater stability. If the filesystem is allocated on “transient_devices” or “transient_storage”, the size is limited to the capacity of transient usage.

Back to top

2) How are the user table space(s) “dwa<x>-<y>” used?

The user table space(s) contain the synchronized Db2 for z/OS tables that are either replicated or loaded to the accelerator. At least one user table space is created per connected Db2 z/OS system, e.g. “dwa0-1”, but there can be multiple ones depending on the number of tables that are synchronized from one Db2 z/OS system.

The user table space(s) don’t have a fixed size, they are created as resizable. The size of the synchronized tables and the synchronization method (full table reloads, partial reloads, replication) determines to which extend the table space(s) grow.

After the initial load of the Db2 z/OS tables the table space(s) have a size required to store the data. When the data is reloaded, or replication starts, the table spaces grow to accommodate the space requirements of these operations:

• Full table reload: A new table is created, and the loaded data is inserted into the new table. The old table still exists but is marked for deletion.
  For example, after full table reload of all tables, the size of the table space(s) is doubled compared to the size after the initial load.
  • Additional info: Before you have installed the accelerator you might have completed a sizing estimation together with your IBM representative. The sizing tool estimates the required storage for the table space(s) twice as much as just storing the data to accommodate full table reloads of all tables.
  Additional info: Before you have installed the accelerator you might have completed a sizing estimation together with your IBM representative. The sizing tool estimates the required storage for the table space(s) twice as much as just storing the data to accommodate full table reloads of all tables.
• Partial reloads: The loaded partitions are inserted into the existing table. The old data of the partitions still exists within the tables but is marked for deletion.
  For example, after partial reload of all tables, the table space(s) have grown by the size of the loaded partitions compared to the size after the initial load, but they are not double in size as with full table reloads.
• Replication: The replicated I/U/D statements are executed as INSERT or DELETE statements on the tables within the user table space(s). Note, that UPDATEs on Db2 for z/OS tables are composed as DELETE/INSERT statements on the corresponding tables on the accelerator. With INSERT statements the table space(s) grow according to the amount of inserted data compared to the size after the initial load. With DELETE statements the data is directly deleted within the table space(s) resulting in free pages within the table space(s) that can be reused for further INSERTs.

The 'marked for deletion' data within the table space(s) is finally deleted by the accelerator table space maintenance process. This process checks every 30 minutes for data to be deleted/dropped and for executing REORGs to reclaim the space within the table spaces for reuse. Note, that the size of the table space(s) does not shrink by these delete/drop/reorg operations. The size stays the same and the size determines how much storage is allocated on the filesystem for the table space(s). But inside the table space(s) the pages change from status ‘used’ to status ‘pending free’ or ‘free’ and can then be reused for further reloads or INSERTs. See next question 3) for more details about the table space maintenance process.

If your Db2 for z/OS data does not grow and if your synchronization method stays the same (e.g. nightly full table reloads, nightly partial reloads or replication) then it is expected that after a few days the size of the table space(s) reached a steady state and don’t grow any more.

Back to top

3) Which operations does the table space maintenance process execute?

The table space maintenance daemon has the following purposes:

• A) Delete data inside the table space after table loads, including reorgs to reclaim space
• B) Reduce the table space size if required
  • Threshold based operation
  • For more details answer to question 4)

A) The table space maintenance process kicks in every 30 min by default as defined by the internal TABLE_SPACE_MAINTENANCE_CHECK_INTERVAL configuration parameter. It checks for data to be deleted within a table space.

The following data is deleted:

• Old table partitions after partial reload has occurred.
  • With a partial reload the newly loaded partitions are inserted into the existing table in addition to current partitions. After the load completed the old partitions are marked for deletion and the new partitions are enabled for usage.
  • If no queries use the old partitions any more the table maintenance process DELETEs the data of the old partitions followed by a REORG.
• Old tables after full table reload has occurred.
  • With a full table reload a new table is created and the data is inserted. The old table still exists but is marked for deletion.
  • If no queries use the old table anymore, the old table is DROPped.

The freed space within the table space can then be used for further partial or full table reloads. A portion might also be released back to the OS if the requirements for the ALTER TABLESPACE REDUCE operation are met.

Note, that any freed space by DELETE or DROP operations is not available immediately for new data. The freed pages are marked as pending free pages (TBSP_PENDING_FREE_PAGES) first. Db2 Warehouse uses an asynchronous process to make the pending free pages reusable by new data as free pages (TBSP_FREE_PAGES).

B) In the answer to question 4) it is described in detail that the size of the table space is reduced periodically in small portions if some requirements are met (breached thresholds).

The main purpose of reducing the size of a table space is to release storage to the OS if tables have been removed from an accelerator. This storage can then be reused for new tables from the same Db2 for z/OS system or from another one that is connected to the same accelerator.

Back to top

4) How and when is allocated storage freed and released back to the OS?

Back to top

5) When does the DSNX881I ID 24 warning message occur that a filesystem only has x% free space?

If the /head or the appliance_data_pool filesystem space has less than 12% free space, a DSNX881I message with ID 24 is issued in the z/OS SYSLOG informing that the filesystem has only x% free space.
For example:

11:12:00.71 STC45881 00000014 DSNX881I -D3D1 24 E 89761 (2024-05-20 15:11:52 UTC) 334 334 00000014 DXNGD3BM(190.24.2.48) File system mounted at "/scratch" has only 334 00000014 11.97% free space

This sample message applies to the complete appliance_data_pool filesystem with all its mountpoints (/data, /scratch, /local).

With Accelerator maintenance level 7.5.13 the message has improved; it now uses the /data mount point in the message text instead of /scratch to refer to the appliance_data_pool filesystem.

A further enhancement to the message is planned for a future release, for example saying:

File system mounted at "/data" (72.76 %), "/local" (15.2 %) and "/scratch" (excluding "/scratch/dwa/query-results") (0.07 %) on host data1 has only 11.97% free space

In addition, accelerator maintenance level 7.5.13 contains improved and more frequent checks for high disk space usage to warn early if available disk space gets low:

• The DSNX881I message with ID 24 (FileSystemTooFullEvent) is enhanced to occur for any filesystem having less than a threshold of 12% free space or 12 % free inodes (Inodes are data structures that represent individual files and directories). For an Accelerator on Z multi-node system this message can occur for any node (head or data node).
  • The first message occurs latest 10 minutes after free disk space or free inodes dropped below the threshold.
  • It is repeated again
    • if free disk space or free inodes percentage change, but are still below the threshold
    • after 6 hours if free disk space or free inodes are still below the threshold, but haven’t changed.
  • The percentage threshold for free disk space (SYSTEM_MAINTENANCE_MINIMUM_FREE_FILE_SYSTEM_SPACE_PERCENTAGE_WARNING_THRESHOLD) or fIee inodes SYSTEM_MAINTENANCE_MINIMUM_FREE_INODE_PERCENTAGE_WARNING_THRESHOLD is configurable. If you want to change them, open a support case to get assistance from IBM support.

Back to top

6) What to do if DSNX881I ID 24 occurs that /data only has x% free space?

If the DSNX881I ID 24 (FileSystemTooFullEvent) message occurs repeatedly for /data (or /scratch if maintenance level is lower than 7.5.13), consider the following actions:

• Check whether you can remove tables from the accelerator. The ACCEL_GET_TABLES_INFO stored procedure with its output value accessCount and lastAccessTimestamp could help you determine which tables are not used any more and could be removed.
• Add more storage to the accelerator if no tables have been removed or can’t be removed
• If “temp_working_space” is set to automatic, consider setting it to a fixed size. The temporary table space is recreated with each reset of the accelerator. It might have happened that for example after an IPL more space is allocated to this table space than before, and you get the DSNX881I message because of that. See question 9) how to monitor how much space is allocated to this table space.

If tables have been removed and you still get the message repeatedly over a few days then the automatic table space maintenance process to shrink a table space in size might not have run, because the defined thresholds are sill not breached (see question 4)). Open a support case with IBM to check storage usage, table space sizes and thresholds. IBM support might reduce the size of a table space manually by executing the ALTER TABLESPACE .. REDUCE.. command during a remote support session.

Back to top

7) What to do if message “SQL0289N Unable to allocate new pages in table space dwa<x>-<y>" occurs?

This error message can occur during data synchronization (for example table loads using ACCEL_LOAD_TABLES or HALOAD) to the accelerator if the available storage in the appliance_data_pool filesystem is fully used.

When this occurs open a support case to IBM Support for a check on used storage and possibilities to release storage as short-term action.

Additionally consider the following actions:

• Check whether you can remove tables from the accelerator. The ACCEL_GET_TABLES_INFO stored procedure with its output value accessCount and lastAccessTimestamp could help you determine which tables are not used any more and could be removed. This technote describes a method to collect the output of ACCEL_GET_TABLES_INFO and store it in a Db2 for z/OS table. You can use this method regularly to monitor which tables are accessed and how often.
• Add more storage to the accelerator if tables can't be removed
• If “temp_working_space” is set to automatic, consider setting it to a fixed size. The temporary table space is recreated with each reset of the accelerator, so it might have happened that for example after an IPL more space is allocated to this table space than before. See question 9) how to monitor how much space is allocated to this table space.
• Check your Db2 z/OS MSTR address space whether DSNX881I messages with ID 24 occurred before that warn about storage getting full. Implement monitoring and automation procedures on DSNX881I message to get informed early if available storage gets low.

After free storage have been made available again, restart the data synchronization process that failed before.

Back to top

8) How can a user estimate how much storage must be added to the accelerator to accommodate data growth?

As discussed in the previous Q&As in this doc the accelerator needs storage not just for storing the data, but also for operation.

For example, just for loading/reloading tables there must be twice as much storage available as the size of the tables. If you want to add/load a new table to an accelerator, that for example consumes 1 TB storage on the accelerator to store the data, then 2 TB storage on the accelerator is required to store the data and to accommodate full reloads of the table.

In general, the data pool should always have at least ~15-20% free space available for successful operation without getting any error messages or outages. If for example the data pool storage is already be used by 80-85% and new tables should be added to the accelerator, it is recommended to add more storage instead of using the 15-20% free space for the new tables.

Note, that adding new tables to the accelerator would drive new query workload which might need additional tempspace for query execution. If the tempspace definition is part of the data pool (according to configuration in JSON file) then additional storage for tempspace might be needed as well.

To estimate how much more storage must be added to add/load new tables, take the following approach:

1. Estimate how much space is needed on the accelerator to store the new data. There are two approaches available to estimate that:
   • Determine the uncompressed size of the new data on Db2 for z/OS. If you have compressed tables and want to take this approach, refer to the technote How to calculate uncompressed Db2 for z/OS table sizes for Db2 Analytics Accelerator. Use the uncompressed size as estimate of required space on the accelerator to store the new data and accommodating full table reloads
   • If you have compressed Db2 for z/OS tables, alternatively proceed as follows to estimate the required space to store new data:
     a) Determine your compression rate by comparing table sizes in Db2 for z/OS with table sizes on the accelerator.
     b) Afterwards apply this compression rate to the new data amount to estimate the required space to store the new data on the accelerator.
     c) As a third step, multiply the compression rate by 2 to accommodate potential full table reloads.
     
     Take the resulting value as estimate of required space on the accelerator to store the new data and accommodating full table reloads.
2. Take the estimated required space from the previous step and add the following overhead:
   • ~20 % for the increase of /scratchfs filesystem to write transient data (only if transient data is written to data pool)
   • ~10 % for the increase of the preallocated Db2 Warehouse logs in /local
   • ~10-20% for the increase of the temporary table space if “temp_working_space” is set to automatic (only if transient data is written to data pool). Alternatively set “temp_working_space” to a fixed size to avoid this increase
   • 25% if you use FICON attached ECKD storage since. With ECKD a MOD54 disk effectively only has 40 GB usable storage instead of 54 GB because of the different formatting that takes place to work with SSC (Linux based) LPARs.
3. Add as much storage as calculated in the previous steps.

Note, that these are just estimates, the real storage needs might vary dependent on compression rates, load behavior and tempspace requirements.

Back to top

9) How to monitor space usage?

The accelerator monitoring and user interfaces provide information how much space is currently used. However, dependent on which interface is used the meaning of the used space information is different.

• Data Studio or Admin Foundation shows table sizes based on used pages within the table space, for example:
  
• Data Studio, Admin Foundation, or DISPLAY ACCEL output shows the sum of used pages within the table spaces as used space, for example:
  • Data Studio display:
    
  • DISPLAY ACCEL command output:
    
    The corresponding SMF counters are:
    • Q8STDSKA: Total Disk capacity in MB available on the Accelerator database.
    • Q8STDSKB: Disk space (in MB) used by the Accelerator database for this Db2 system.
    • Q8STDSA: Disk space (in MB) used by the Accelerator database for all Db2 systems.
• Admin UI show the used disk space of the appliance_data_pool filesystem. This includes the table spaces (used and free pages), log data and transient data (if no transient_devices or no transient_storage is defined). The used disk space is determined by using the "df" operating system command. For example:
  

In addition to the SMF counters listed above (Q8STDSKA, Q8STDSKB, Q8STDSA) there are SMF counters available that show the used tempspace and the used log space:

• Q8STTSA: The amount of disk space used by all paired Db2 subsystems for temporary data.
  This is the amount of disk space allocated for the table space TEMPSPACE one based on the “temp_working_space” setting in the JSON configuration file. Refer to APAR PH62138 if you see a low value in Q8STTSA.
• Q8STLSA: The amount of disk space used by all paired Db2 subsystems for log data.
  This is the amount of disk space for the pre-allocated transaction logs on the accelerator in the /local directory.

Back to top

When this occurs open a support case to IBM Support for a check on used storage and possibilities to release storage as short-term action.

Additionally consider the following actions:

• Add more storage to the accelerator
• If “temp_working_space” is set to automatic, consider setting it to a fixed size. The temporary table space is recreated with each reset of the accelerator, so it might have happened that for example after an IPL more space is allocated to this table space than before. See question 9) how to monitor how much space is allocated to this table space.
• Check whether you can remove tables from the accelerator. The ACCEL_GET_TABLES_INFO stored procedure with its output value accessCount and lastAccessTimestamp could help you determine which tables are not used any more and could be removed. This technote describes a method to collect the output of ACCEL_GET_TABLES_INFO and store it in a Db2 for z/OS table. You can use this method regularly to monitor which tables are accessed and how often.
• Check your Db2 z/OS MSTR address space whether DSNX881I messages with ID 24 occurred before that warn about storage getting full. Implement monitoring and automation procedures on DSNX881I message to get informed early if available storage gets low.

Back to top