database space management
Article ID: 10053
Updated On:
Products
Issue/Introduction
This article is in English. For the Spanish-language version, please see Knowledge Base article 10045.
Este artículo está en inglés. Para la versión en español, consulte Knowledge Base article 10045.
This article will explain briefly how Datacom database files are allocated and used, and how they can be monitored and maintained. It will help you identify if a database area is getting dangerously full regardless of your application configuration, and how to manage it.
There are references to several jobs throughout this document to help you manage your database space; these can be found in the PDS created from the attached file, which has been tersed. You should upload the tersed file from your PC to the mainframe as a BINary file, FB, LRECL=1024, BLKSIZE=27648. Here is JCL you can use to recreate the PDS from the uploaded tersed file:
//*
// SET TERSFL=<<Your downloaded tersed file (attached below)
// SET OUTPDS=<<A New PDS filename for the utilities
//*
//DELETE EXEC PGM=IEFBR14
//X01 DD DSN=&OUTPDS,
// DISP=(MOD,DELETE,DELETE),UNIT=3390,SPACE=(TRK,1)
//* - - - - - -C- - - - - - - - - - - - - - - - - - - - - - - - - - -7-
//UNTERSE EXEC PGM=AMATERSE,PARM=UNPACK
//SYSPRINT DD SYSOUT=*
//SYSUT1 DD DISP=SHR,DSN=&TERSFL
//SYSUT2 DD DISP=(NEW,CATLG,DELETE),DSN=&OUTPDS,
// UNIT=3390,STORCLAS=TSO,DSORG=PO,SPACE=(CYL,(1,1,5),RLSE)
//*
These are the members from the above PDS that are referenced in this writeup:
| ALLOC161 | Job to reallocate Jobtrac (DB 161) areas |
| ALLOC430 | Job to reallocate Scheduler (DB 430) areas |
| ALLOC601 | Job to reallocate CA 11 (WA) (DB 601) areas |
| CLOSE | Job to turn off access and close a database in the MUF |
| CXRPFULL | Job to run a full CXX report for a database |
| CXRPTYPA | Job to run a CXX space report (Type=A) for a database |
| DEFRAG | Job to defragment the index for a database |
| RETIXA | Job to recreate the index for a database - in place |
| RETIXB | Job to recreate the index for a database with a new allocation |
| SPACERPT | Job to produce a detailed space report of all Datacom databases |
Items in RED will affect the operation of the database, and applications should be stopped; items in BLUE are benign and can run at any time.
Environment
This article applies to Datacom/AD and Datacom/DB version 14.0 and higher, running on z/OS 2.1 and higher.
Resolution
Each application database file is a standard sequential (PS) file, subject to the same rules as any other PS file. As a BASIC type file, it is limited to 16 extents per volume, and no more than 65535 tracks on one volume. This file can be allocated on as many as 59 volumes if needed. If more than 65535 tracks are needed on a volume, allocating the file with DSNTYPE=LARGE will allow up to 16,777,215 tracks on a single volume. Datacom does not support SMS Extended Format files, but using DSNTYPE=LARGE should meet most application needs. For more information about file allocations and limits, please see the IBM DFSMS Using Data Sets manual:
Using these standard PS file rules means that a file that is nearing 100 percent full is really only nearing 100 percent full for the extents allocated, so it can most likely be extended rather than requiring a reallocation. To accomplish this, all database areas should have two key parts to their creation:
- The allocation of the file with both primary and secondary space specified; and
- The database definition (in Datadictionary and the CXX) with Dynamic Extend enabled.
How can you tell the file allocation for your database files? Since Datacom gets the volume information from the catalog when it dynamically opens each database area, we do not store all of that information in our system, but some fields are held in the Directory area (called CXX). You have two options:
- You can run a full CXX report and use that information to get the file allocations from the system catalog; or
- You can run a query with SQL to extract the information from the Datacom System Tables, then use a REXX program to look up the information directly.
To run the CXX report, please see member CXRPFULL in the aforementioned PDS.
Note that you can choose a single DBID per function, or you can remove the DBID= parameter to report on the entire system. In the output of the CXX report, you should look for "DATA SET NAME" to get the filename, and on the right side of that same line, you should see the area and database. For example, if your CXX report shows:
DATA SET NAME - QDBA00D.C131.AEX DATA DTF/DDNAME - AEX131
Area AEX in database 131 has a filename of QDBA00D.C131.AEX. You would then take this file and search in your catalog to display the attributes (ISPF 3.2 or 3.4) and to see how many extents the file has.
To report the data from the Datacom System Tables, you can run program DBSQLPR, with a job to extract the database information, and then pass it into a REXX program which will look up the catalog details and provide a 2-part report of the results. Note that the report output is 150 bytes long.
To run this report, please see member SPACERPT in the aforementioned PDS. This job will give you a 2-part report like this:
Part 1:
DBDSI Output:
DBID ARA Used Blks Tot Blks Pct Tot Tracks Extnd Extend Amt Area File Name Volser Seq#
----- --- ---------- ---------- ----- ---------- --- ---------- -------------------------------------------- ------ -----
1 DEM 12 99 12.1 3 NO DATACOM.MUF001.BD1400A.DBDEM DCMSP2 1
1 IXX 15 36 41.6 3 NO DATACOM.MUF001.BD1400A.DBIXX DCMSP4 1
1 PAY 13 66 19.6 2 NO DATACOM.MUF001.BD1400A.DBPAY DCMSP2 1
1 PMF 17 238656 0.0 7232 NO DATACOM.MUF001.BD1400A.DBPMF DCMSP2 1
1 PMF 17 238656 0.0 7232 NO DATACOM.MUF001.BD1400A.DBPMF DCMSP5 2
1 PMF 17 238656 0.0 7232 NO DATACOM.MUF001.BD1400A.DBPMF DCMSP6 3
2 DD1 2598 11700 22.2 975 YES 10 TRK DATACOM.MUF001.BD1400A.DD1002 DCMSP5 1
2 IXX 1901 3600 52.8 300 YES 20 TRK DATACOM.MUF001.BD1400A.IXX002 DCMSP8 1
Part 2:
File Name RC Seq Volume Ext AllocTyp Primary,Seconds Alloc Used Seq Type
-------------------------------------------- -- --- ------- --- -------- (-------,-------) ------- ------- --------
DATACOM.MUF001.BD1400A.DBDEM 00 1 DCMSP2 1 TRACK ( 3, 0) 3 3 BASIC
DATACOM.MUF001.BD1400A.DBIXX 00 1 DCMSP4 1 TRACK ( 3, 0) 3 3 BASIC
DATACOM.MUF001.BD1400A.DBPAY 00 1 DCMSP2 1 TRACK ( 2, 0) 2 2 BASIC
DATACOM.MUF001.BD1400A.DBPMF 00 1 DCMSP2 1 TRACK ( 2, 1) 2 2 BASIC
DATACOM.MUF001.BD1400A.DBPMF 00 2 DCMSP5 1 TRACK ( 2700, 2700) 2700 2700 BASIC
DATACOM.MUF001.BD1400A.DBPMF 00 3 DCMSP6 3 TRACK ( 2700, 2700) 4530 4530 BASIC
>>> Totals for 3 volumes: >>3>>> 5 7232
- - - - - - - - - - - - - - - - - - - - - -
DATACOM.MUF001.BD1400A.DD1002 00 1 DCMSP5 1 CYLINDER ( 65, 5) 65 65 BASIC
DATACOM.MUF001.BD1400A.IXX002 00 1 DCMSP8 1 CYLINDER ( 20, 2) 20 20 BASIC
In this report, you can see if the database area is defined to dynamically extend (column Extnd), and if so, what the extend amount is as shown in the CXX. This is the same as looking in the full CXX report for the "DYNAMIC EXTEND" and "DYN.EXT.TRACKS" values. If the CXX/SQL report contains an extend amount as in the example above - showing 10 and 20 tracks for the DB002 DD1 and IXX areas, respectively - this value will be used for each new extent. If this value is blank or zero, each new extent be sized based on the value of the file's secondary allocation if it exists in the catalog (as shown above, 5 cylinders and 2 cylinders, respectively).
At this point, you have seen how to determine the physical file attributes (space allocation and extents), and how to determine if the database area can be dynamically extended. How can you determine if you have a problem and need to extend the database area?
For Datacom/AD, the database areas should already be defined with DYNAMIC EXTEND set to YES. This means that as records are added to the database, if an area will reach 100% full, the MUF will hold and stop adding records for a moment, and it will add an extent to the file, initialize it and update the CXX (internal database catalog). It will then resume adding records to the file without issue. Once you have reached the limit of file extents and/or volumes, any attempt to extend the file further will result in an x37 abend; consequently, it is good practice to monitor the number of extents on a regular basis.
The space utilization is best monitored through the use of the Directory Space Utilization CXX report. This will produce a 1-line summary of each area, showing the current and maximum PCT FULL values.
To run this report, please see member CXRPTYPA in the aforementioned PDS. As before, you can restrict each function to a single DBID, or you can remove the DBID= parameter to report on all databases. Be sure to include the
COMM OPTION=STATS
function before the REPORT command to ensure you have the most current data. This will produce a report like this (the two right-hand columns were removed here):
DATACOM/AD DATA AREA SPACE UTILIZATION REPORT
AREA DATA TOTAL TOTAL TOTAL USED PERCENT
NAME BASE TRACKS RECORDS BLOCKS BLOCKS FULL MAX
CXX 250 N/A 3,000 302 10 10
IXX 3575 1,650 N/A 19,800 15,506 78 78
G01 3575 3,000 615 18,000 9 0 0
G02 3575 6,000 1,032,845 12,000 11,098 92 92
G03 3575 54,000 5,768,535 108,000 105,323 97 97
G04 3575 3,000 936 18,000 19 0 0
In this report, you can see that area G02 in database 3575 has reached 92 percent full, and this is the highest it has been (MAX) since the area was initialized.
Now, to maintain the database files, you have several possibilities. If the Index is getting full, you can allow it to dynamically extend if desired. However, we have seen that certain Datacom/AD products can benefit by clearing out empty blocks that formerly pointed to records which have been deleted. To recover these empty blocks and reduce your index utilization, you can use the DBUTLTY DEFRAG function. This will run while the MUF is up and running and while the application is processing. One thing to note about the DEFRAG is that while it runs as a background task and does not affect overall performance, this task can run for a long time on very large index files. Most jobs on most databases will run in seconds, but on a very large index, it could run longer than 1 day. If necessary, the DEFRAG function can be ended using the REQABORT command, and then restarted again by resubmitting the job.
To run this DEFRAG job, please see member DEFRAG in the aforementioned PDS.
In some cases, you may want or need to rebuild the index, using its current allocation. For this process, you will need exclusive control of the database. Normally the application will be suspended or shut down to run this process - but the MUF will stay running.
To run this RETIX job "in-place," please see members CLOSE and RETIXA in the aforementioned PDS.
If you have a need to reallocate the index because it is truly getting full, this also requires exclusive control of the database, meaning the application must be shut down or suspended. Note that there are two jobs here - this is necessary to avoid any problems with ENQues on the Index file. Run the first, and when it has CC of zero, then you can run the second to correct the problem.
To run this RETIX job and allocate a new index file, please see members CLOSE and RETIXB in the aforementioned PDS.
The process to reallocate the file for a data area is similar. You will start by taking a backup of the database, and then you can allocate a new data area and reload the data into it. This job can be used to reallocate a single area, or all areas in the database.
To run this BACKUP and LOAD job, please see members CLOSE and ALLOCnnn in the aforementioned PDS (nnn = 161 for Jobtrac, 430 for Scheduler, 601 for Workload Automation Restart Option for z/OS Schedulers - called CA 11).
With this explanation and with the supplied jobs, you will be able to easily and effectively monitor and maintain your application databases.
Attachments
Feedback
