Can I manually extend my CA Datacom database area to a new size while the database is in use?

Document ID : KB000040185
Last Modified Date : 29/03/2019
Show Technical Document Details
Introduction:
When certain applications plan an activity that will add a significant number of records to a table in a database, the table might not be large enough to handle the new records. It would be easier if a new extent could be added to the database beforehand to handle this growth.
Question:

If the database is used for some high-availability applications, it can’t be easily taken offline to run a job to allocate a new, larger file. Is there a way to give it a special extent while the database is open?

Answer:
There is a feature, which was added in version 12.0, to allow a new, custom size extent to be added to either a data or index area without closing the database. This feature is called the "On-Demand Dynamic Extend" feature.

A regular dynamic extend, if Dynamic Extend is enabled in the Datadictionary definition, will create a new extent based on the Datadictionary Dynamic Extend value or the file's secondary allocation amount, if there is no amount defined in the Datadictionary. However, if a large number of rows (records) will be added to a table, it would be more prudent to create the new extent to handle the additional rows before it is needed, and this is where the On-Demand Dynamic Extend feature is quite useful. Note that the amount used for the DYNAMIC_EXTEND command is stored in memory, and any further dynamic extends will use this new value until the MUF is shut down.
Here is an excerpt about this feature from the CA Datacom/DB Database and System Administration Guide:
It is better for you to be able to anticipate needed extends and to request them during off-hours instead of during a peak processing time in the MUF. It is also useful if you can override the following three current choices for dynamic extend:
  • Whether dynamic allocation is to be requested
  • Whether the type is track or cylinder
  • The number of the type
The on-demand dynamic extend feature is designed to provide maximum flexibility. It is supported as the DYNAMIC_EXTEND console-like command. Being console-like, the command can be issued as follows:
  • By using the DBUTLTY COMM function with OPTION=CONSOLE
  • By issuing it as a true console command
  • By issuing it using any SQL tool that can insert a record (including non-mainframe)
To use the on-demand dynamic extend feature, you must know:
  1. How large an extent you desire;
  2. If the volume(s) on which you will extend have the free space to accommodate your extent
  3. If the file you are extending is able to be extended (the file is not already at 16 extents on a single volume, for example).
Once you have determined the above, the process is quite easy. While this command can be executed at any time, we recommend it be performed during a period of lighter activity in case any other problems would arise. There are also some requirements or considerations for using this feature, as noted in the CA Datacom/DB Database and System Administration Guide:
  • In z/OS environments, using a zero for number requires that the data set has been defined with a secondary allocation or has a user-provided DD statement with a secondary allocation.
  • In z/VSE environments, CA Dynam/D for z/VSE is required to provide the actual dynamic extend.
  • Every Index Area (IXX) in the database must have been initialized.
  • The data area to be extended must have been initialized and loaded. If not open, the data area must have no roadblocks to being opened. There are many open errors possible.
  • The table selected must be open or have no roadblocks to being opened. There are many open errors possible.
Below are examples of a standard DBUTLTY Extend along with the three methods noted above. First, though, is a report of the CXX and the file information before starting the extend processing.
This is the CXX TYPE=A report after the file has been set up:
AREA DATA       TOTAL           TOTAL       TOTAL       USED
NAME BASE      TRACKS         RECORDS      BLOCKS      BLOCKS
 PMF    1           2             200          66          17

This is the initial report from CA File Master Plus for the file:
Data Set Name                                Volume Org  Lrecl Blksz Recfm  Tracks %Used Ext
-------------------------------------------- ------ ---- ----- ----- ----- ------- ----- ---
DATACOM.BD1400.MYMUF.DBPMF                   DCMSP8 PS    1024  1024  F          2   100   1
 
EXAMPLES
  1. Standard DBUTLTY Extend
  2. DBUTLTY Console command to execute a custom DYNAMIC_EXTEND
  3. SQL addition of a DYNAMIC_EXTEND record to the Datacom System Tables
  4. A standard z/OS console command to execute a custom DYNAMIC_EXTEND
Example #1 - Standard DBUTLTY Extend
This is the EXTEND command that has been traditionally used with the DBUTLTY program to manually extend an area.
//SYSIN DD *      
EXTEND DBID=001,AREA=PMF
/*

This command results in these messages in the MUF log:
DB00141I - DBUTLTY START EXTEND-U 1 PMF jobname
DB00141I - DBUTLTY END EXTEND-U 1 PMF jobname 

The CXX after the EXTEND using the Datadictionary Dynamic Extend value of 1 track:
AREA DATA       TOTAL           TOTAL       TOTAL       USED
NAME BASE      TRACKS         RECORDS      BLOCKS      BLOCKS
 PMF    1           3             200          99          17
The CA File Master Plus report:
Data Set Name                                Volume Org  Lrecl Blksz Recfm  Tracks %Used Ext
-------------------------------------------- ------ ---- ----- ----- ----- ------- ----- ---
DATACOM.BD1400.MYMUF.DBPMF                   DCMSP8 PS    1024  1024  F          3   100   2
 
Example #2 - DBUTLTY Console command to execute a custom DYNAMIC_EXTEND
Using DBUTLTY, you can execute console-like commands to dynamically extend the area as you desire. In this case, we are extending the PMF area by 111 tracks, instead of the Datadictionary value of 1 track.
//SYSIN     DD  *
  COMM OPTION=CONSOLE,OPTION2='DYNAMIC_EXTEND 001,PMF,111,TRK'
/*

This command results in these messages in the MUF log:
DB01705I - DYNAMIC EXTEND START PMF001 TRK 3 DATACOM.BD1400.MYMUF.DBPMF
DB01706I - DYNAMIC EXTEND END PMF001 TRK 114 DCMSP8 VOL 1 VOLS 1 EXT 3 TRK +111

The CXX after the EXTEND:
AREA DATA       TOTAL           TOTAL       TOTAL       USED
NAME BASE      TRACKS         RECORDS      BLOCKS      BLOCKS
 PMF    1         114             200       3,762          17
The CA File Master Plus report:
Data Set Name                                Volume Org  Lrecl Blksz Recfm  Tracks %Used Ext
-------------------------------------------- ------ ---- ----- ----- ----- ------- ----- ---
DATACOM.BD1400.MYMUF.DBPMF                   DCMSP8 PS    1024  1024  F        114   100   3
 
Example #3 - SQL addition of a DYNAMIC_EXTEND record to the Datacom System Tables
Using the DBSQLPR program or another SQL utility, you will add a row to the SQL_CONSOLE table (SQX) of the Datacom System Tables. This table is specifically designed to hold commands to be executed by the MUF. After you add the command row to the table, the MUF will remove the row and execute the command, just as if you used DBUTLTY Example #2, above, or if you issued the command via the console as shown in Example #4, below. In this case, we are using DBSQLPR to extend the PMF area by 222 tracks, instead of the Datadictionary value of 1 track. This is the input to the DBSQLPR program:
//SYSIN DD *      
INSERT INTO SYSADM.SQL_CONSOLE
VALUES('MYMUF   ','DYNAMIC_EXTEND 001,PMF,222,TRK');
/*

This command results in these messages in the MUF log:
DB01705I - DYNAMIC EXTEND START PMF001 TRK 114 DATACOM.BD1400.MYMUF.DBPMF
DB01706I - DYNAMIC EXTEND END PMF001 TRK 336 DCMSP8 VOL 1 VOLS 1 EXT 4 TRK +222 

The CXX after the EXTEND:
AREA DATA       TOTAL           TOTAL       TOTAL       USED
NAME BASE      TRACKS         RECORDS      BLOCKS      BLOCKS
 PMF    1         336             200      11,088          17
The CA File Master Plus report:
Data Set Name                                Volume Org  Lrecl Blksz Recfm  Tracks %Used Ext
-------------------------------------------- ------ ---- ----- ----- ----- ------- ----- ---
DATACOM.BD1400.MYMUF.DBPMF                   DCMSP8 PS    1024  1024  F        336   100   4
 
Example #4 - A standard z/OS console command to execute a custom DYNAMIC_EXTEND
The above two examples show different ways of issuing console-like commands to extend a database area by a custom amount. This example will show the results of issuing the command to the MUF directly at the console. In this case, the command issued is similar to the previous examples:
/F mymuf,DYNAMIC_EXTEND 001,PMF,333,TRK
This command results in these messages in the MUF log (Note that the first two lines show the MUF's acknowledgement of the command issued):
DB01311I - DYNAMIC_EXTEND 001,PMF,333,TRK
DB01335I - CONSOLE SCHEDULED - DYNAMIC_EXTEND 001,PMF,333,TRK
DB01705I - DYNAMIC EXTEND START PMF001 TRK 336 DATACOM.BD1400.MYMUF.DBPMF
DB01706I - DYNAMIC EXTEND END PMF001 TRK 669 DCMSP8 VOL 1 VOLS 1 EXT 5 TRK +333 

The CXX after the EXTEND:
AREA DATA       TOTAL           TOTAL       TOTAL       USED
NAME BASE      TRACKS         RECORDS      BLOCKS      BLOCKS
 PMF    1         669             200      22,077          17
The CA File Master Plus report:
Data Set Name                                Volume Org  Lrecl Blksz Recfm  Tracks %Used Ext
-------------------------------------------- ------ ---- ----- ----- ----- ------- ----- ---
DATACOM.BD1400.MYMUF.DBPMF                   DCMSP8 PS    1024  1024  F        669   100   5
Additional Information:
For more information about using the On-Demand Dynamic Extend feature and about using SQL-based console commands, please refer to the following Guides.
CA Datacom/DB Version 14.02 Database and System Administration Guide, in the section “IntroductionMaintenance using Console Commands
CA Datacom/DB Version 15.0 Database and System Administration, in the section “Maintenance using Console Commands
CA Datacom/DB Version 15.1 Database and System Administration, in the section “Maintenance using Console Commands

More information about the SQL_CONSOLE table and its layout can be found in the following Guides.
CA Datacom/DB Version 14.02 System Tables Reference Guide, in the section “SQL TablesSQL Maintenance Table
CA Datacom/DB Version 15.0 System Tables Reference Guide, in the section “SQL Maintenance Table
CA Datacom/DB Version 15.1 System Tables Reference Guide, in the section “SQL Maintenance Table


As always, please contact CA Technologies support for CA Datacom if you have further questions.