Automate Database Area Extensions Using SQL and DBUTLTY - Datacom/DB
Article ID: 446649
Updated On:
Products
Issue/Introduction
The database directory (CXX) contains metadata regarding the Multi-User Facility (MUF) and its associated databases. When a database area reaches a high percentage of space utilization, subsequent operations like an online reorganization (OLREORG) may fail. To prevent these failures, an automated method is required to monitor space usage and generate the necessary DYNAMIC_EXTEND commands. This process uses the Interactive SQL Service Facility (DBSQLPR) to query the DIR_DATASET table to identify areas exceeding a defined threshold. Unlike standard solutions that apply a universal percentage test, this method allows for granular control by explicitly defining specific databases and areas with unique targets and extension amounts.
Cause
Manual monitoring of database area growth is prone to error and may result in insufficient space for critical utility operations such as OLREORG. Standard automated solutions often lack the flexibility to apply different thresholds or extend amounts across varied database environments.
Resolution
The provided solution utilizes a two-step approach within a single job. First, DBSQLPR queries the system tables to identify areas that exceed specific, user-defined percentage thresholds. Second, the output of the SQL step is passed directly to the DBUTLTY utility to execute the generated DYNAMIC_EXTEND commands.
- Identify the target Database ID, Area Name, and the specific threshold percentage for each area.
- Configure the JCL symbolic variables and the SQL script to insert these granular parameters into the internal scratch table.
- The SQL query calculates the current space utilization for each defined database and area
- If the specific threshold for an entry is exceeded, a DBUTLTY COMM function command is formatted to perform a DYNAMIC_EXTEND using the unique extend amount specified for that area.
- Execute the job to apply the tailored extensions automatically.
For detailed JCL implementation, refer to the attached file: Auto_Extend_JCL.txt.
Additional Information
References
- Datacom/DB SQL User Guide:
- Subscribe to Knowledge Base articles:
- Contact Us:
Attachments
Feedback
