Shortening the run time for Datacom DBUTLTY BACKUP or EXTRACT processing
Article ID: 18772
Updated On:
Products
Issue/Introduction
Backing up 7.5 million records using the Datacom DBUTLTY program takes 50 minutes to complete.
Is there anything I can do to quickly and easily speed this up? It doesn't matter if I use BACKUP or EXTRACT, the process takes a long time.
Environment
Component : Datacom/AD
Component : Datacom/DB
Resolution
The Datacom DBUTLTY commands BACKUP and EXTRACT are able to produce a file of a database or table, in one of two different sequences: Physical, where the data is read directly from the physical file in whatever order the records are present, and Native, where the data is written to the output file in Native Key sequence.
The first consideration in shortening the execution time for this process is to use SEQ=PHYSICAL. By reading the records directly from the disk file, it is easy for the program to process more records at a time. Note that to be sure you have the most current data written from any in-memory buffers to disk you will need to use a command to flush the buffers.
Increase the number of Datacom buffers by adding this as the first line of the DBUTLTY:SET OPTION1=IXXNO=9999;DXXNO=9999;DATANO=9999
On the DBUTLTY EXEC card increase the region size by specifying REGION=0M.
Here is an example:
//EXTDBUTL EXEC PGM=DBUTLTY,REGION=0M
//SYSIN DD *
SET OPTION1=IXXNO=9999;DXXNO=9999;DATANO=9999
COMM DBID=###,OPTION=STATS
EXTRACT DBID=###,TABLE=xxx,DDNAME=xxxxxxxx,SEQ=PHYSICAL
/*
The COMM...STATS will cause the buffers to be written to disk, and then the PHYSICAL backup will run very quickly. As noted above, the records are not in any particular order, so you will need to manually sort the file if the order of records is important.
Another consideration to improve the execution time of your DBUTLTY EXTRACT or BACKUP commands is to take advantage of the JCL coding for the output file. By adding the JCL parameter BUFNO to the output file, it is possible to significantly reduce the run time. The default if one is not coded is 5 buffers, and the maximum is 255 or less, depending on the region size available. Here is an example of a BACKUP file DD statement:
//DB143BK DD DISP=(,CATLG,DELETE),
// DSN=MY.BACKUP.FILENAME,BUFNO=100,
// UNIT=SYSDA,SPACE=(CYL,(1000,250),RLSE)
This example is using an arbitrary 100 buffers for processing. This could make a significant difference, and you will need to test this to determine the best setting for your systems. It is possible to have too many buffers, which could decrease your performance. In order to accommodate these buffers.
If a sort is being run after the EXTRACT, also add BUFNO=100 to the SORTIN and SORTOUT DD statements.
Finally, if you are using Striped files for your backup, you will need to further experiment to determine the best buffering, as the number of stripes is significant in determining how the operating system programs will manage the data.
These are simple changes that can easily be made and tested to improve the execution time of your DBUTLTY BACKUP and EXTRACT processing. There are other advanced ways to help with very large files (Parallel Backup, for example); these are beyond the scope of this document.
Additional Information
For more information about the DBUTLTY BACKUP and EXTRACT commands, please refer to the Datacom/DB DBUTLTY Reference, in the sections "BACKUP (Create Backups) - Backup Data Area" and "EXTRACT (Extract Data Table)."
As always, please contact Broadcom support for Datacom if you have further questions.
Feedback
