Reducing Input/Output

The following sections describe how input/output (I/O) can be reduced:
The following sections describe how input/output (I/O) can be reduced:
Caching Files in Memory
If a database file is cached in memory, the DBMS looks in the cache before reading a database page from disk. If the page resides in the cache, the retrieval I/O is eliminated. If the page must be read from the disk, it is saved in the cache to satisfy future retrieval operations. Database files with a high number of I/Os are good candidates for caching in memory.
There are two basic types of file caching: shared cache which uses coupling facility services to enable a single cache to be shared by multiple central versions and memory cache which is accessible only by a single central version. The remainder of this discussion focuses on memory cache. For information on shared cache, see "Administrating CA IDMS System Operations."
Memory caching is available only for non-native VSAM files.
To enable the use of memory cache, take the following steps:
  • Decide which files to cache by using standard performance-monitoring tools to determine the database files with the most I/O. For example, you can use the DCMT DISPLAY STATISTICS FILES to get a list of all files and their associated I/O counts or look at gathered operating system statistics. Choose files with the highest retrieval counts.
  • Change the DMCL definition to specify MEMORY CACHE YES for each file to be cached. For details, see "DMCL Statement." Alternatively, use the DCMT VARY FILE command to dynamically initiate the use of memory cache for one or more files. For more information, see "CA IDMS System Tasks and Operator Commands."
  • Compute the total amount of storage that is needed to cache the selected files. To do this, for each file, multiply the number of blocks in the file by the file's block size and total the results. The resulting value is the amount of storage needed. Ensure that sufficient storage of the required type is available to all jobs that use the altered DMCL.
If the operating system supports 64-bit storage, the cache is allocated in 64-bit storage if sufficient memory is available. If no or not enough 64-bit storage is available to hold the entire file, the file will not be cached in memory. For details, see "DMCL Statement."
Optimizing 64-bit Memory Cache Storage
You can cache more files within a given amount of 64-bit storage by specifying a percentage of a file to be cached, instead of the whole file. When no file limit is specified, storage to hold every page in the file is allocated and every cached page is read. When a file limit is specified as a percentage of the file size, only the last
pages read are cached, where
is the number of pages that fit in the specified limit after allowing for overhead. When the cache is full, the oldest page in the cache is replaced by the newest page. For more information, see DMCL Statement. 
Database Reorganization
Database reorganization includes:
  • Reducing full pages by changing the size of a database page or increasing the number of pages
  • Reducing overflow by changing the size of a database page or increasing the number of pages
  • Decreasing fragmentation for non-SQL defined databases by:
    • Specifying page reserve
    • Changing page size
    • Reassigning records
    • Redefining fragmentation specifications
    • Increasing the number of pages
  • Increasing the efficiency of an index's structure by decreasing the number of levels in the index and/or assigning SR8 records to a separate page range
  • Reducing logically deleted and/or relocated records by physically deleting logically deleted occurrences using the CLEANUP utility statement and/or unloading and reloading the data
  • Reducing the number of fragments and/or relocated records by increasing the page size and reading all records in an update mode
Application Design
Selecting the Optimal Path
The first step to determine if the application is optimally designed is to determine if it is accessing the data it needs, using the access path that will create the fewest number of I/Os. To determine if this is true:
  1. Walk through the application and identify the actual transaction path
  2. Review the existing database design and determine if there is a more efficient way to:
    • Access the needed records
    • Process the necessary relationships
Database Design
Take into account the following database design considerations for reducing I/O:
  • Adding sets, indexes, pointers, redundancy
  • Changing set type, set (index) order for non-SQL defined databases
  • Changing location (area) of record or index, index and/or set stored VIA (or clustered)
  • Changing UNLINKED constraints to LINKED (SQL-defined databases) or repeating item (non-SQL defined databases)
  • Splitting a record
For more information, see "Administrating IDMS System Operations."
Using UPDATE STATISTICS (SQL-Accessed Databases)
Execute the UPDATE STATISTICS utility statement at the following times:
  • Periodically (according to the needs of the application) to reflect shifts in the distribution of data in the database (for example, changes in owner/member ratios, area space utilization, index layout)
  • After individual applications that alter the distribution of data; for example, monthly or year-end summary and offload processing
SQL-Defined Tables or Areas
Run UPDATE STATISTICS on individual tables or whole areas. The resulting statistics are stored in the SQL catalog and are used by the Access Module Compiler to generate optimal access strategies for SQL processing. Access modules that reference the tables whose statistics have been updated can then be recompiled to take advantage of the updated information. Table/access module cross-reference information on the catalog can be used to determine which access modules to recompile.
Non-SQL Schemas If They are Accessed by SQL
Run UPDATE STATISTICS on some or all areas defined in a non-SQL Schema. The resulting statistics are kept in the dictionary that defines the non-SQL schema. If the database is accessed by SQL the statistics will be used by the Access Module Compiler to generate optimal access strategies for SQL processing.
Restrictions on Statistics and Non-SQL Schemas
Non-SQL statistics are kept with the schema definition in the dictionary. This means statistics may be kept for only one physical database per schema. When processing an SQL command, only the current set of statistics will be used for that command regardless of the physical database being accessed by that command. The user must decide which physical database will provide the statistics that best meets their needs and run UPDATE STATISTICS against that database.