I am backing up 7.5 million records using the CA Datacom DBUTLTY program, and it 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.
The CA 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. Here is an example:
//SYSIN DD * ACCESS DBID=###,STATUS=WRITE EXTRACT DBID=###,TABLE=xxx,DDNAME=xxxxxxxx,,SEQ=PHYSICAL /*
The ACCESS command 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=50, // UNIT=SYSDA,SPACE=(CYL,(1000,250),RLSE)
This example is using an arbitrary 50 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, you should also change the REGION=4M to REGION=0M on the job card or the step EXEC statement.
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.
For more information about the DBUTLTY BACKUP and EXTRACT commands, please refer to the CA Datacom/DB DBUTLTY Reference Guide, in the sections "BACKUP (Create Backups)" and "EXTRACT (Extract Data Table)."
As always, please contact CA Technologies support for CA Datacom if you have further questions.