Tips for improving run time when making Datadictionary changes

Products

• Datacom/DB

Issue/Introduction

When making Datadictionary changes to add fields to a table, change keys, or add tables, there are things to consider to make the overall process run more quickly.

This article looks at some of the areas to be considered when planning the workload of loading tables and the index, when multiple tasks involve a single database.

 

Environment

Component : CA DATACOM/DB

 

Resolution

For adding fields to a table, the easiest and least invasive process is to add the new fields to the end of the table. Ideally, you would take the new field space from existing filler, but if there is no filler, you can add the fields to the end, making the record longer. Then, for processing, you will need to extract the table and run it through a process to create the new record size. Some people will use SORT and pre-format the new fields with a value; if the new fields have derived values, you might need to use a migration program to initialize them.

When doing multiple tasks that involve a Null Load to a data area or changing the index, one tip is to run a REPORT AREA=IXX,TYPE=A to see how much of the index will be affected by the changes. If a large portion of the index will be changed, it might be easier to INIT the index and then run RETIX at the end to rebuild it. Then, when running the Null Load, or even when running a data load from a BACKUP or EXTRACT file, you can add the parameter INDEX=NO to avoid any index processing. Since you will run a RETIX at the end, there is no benefit to touching the index over and over for each function. Using INDEX=NO will result in much faster load jobs. The key is to minimize the amount of time spent in the index, or to avoid spending any time in the index until it is needed.

When creating or loading a BACKUP or EXTRACT file, using the JCL parameter BUFNO on the sequential file will often provide some improvement on the run time over the default 5 buffers. On the DBUTLTY LOAD function, you can add the KBYTES= parameter to perform I/O in bigger blocks of data, and if you need to update the index and do not wish to use INDEX=NO, you should add OPTIMIZE=YES to group index writes. Adding OPTION1='(I)' will produce output from the SORT program, so you can see if there is a way to improve that processing when updating the index. Also note that by default, three SORTWK areas are utilized if disk space is needed (many sorts can be performed in memory, but very large sort files will need SORTWK). If you want to use more than the default, you will need to specify SORTWK= and then add that many DD statements to your jobstep. Don't forget, too, that if your sort files should use a different unit than the default SYSDA, you will need to add UNIT= to your LOAD statement.

As an example of how all this works together, consider a change like this to a single database:

• Adding a new Table (AAA)
• Adding new fields to the end of two tables (BBB and CCC)
• Adding new keys to a table (DDD) and removing a couple others

In general, this seems like a few simple tasks. However, there are many keys for table BBB, and the index records take up 70% of the index. A Null Load of BBB will run for a long time to remove each of those index entries. When applying the above ideas, here is how the process can be shortened:

1. Extract the BBB and CCC tables using JCL BUFNO=100 or higher, then reformat the data as needed
2. Apply your Datadictionary changes for the new and removed keys in DDD, the new table AAA, and the reformatted tables BBB and CCC. Then Catalog and Enable as usual.
3. INIT your new and reformatted data areas (AAA, BBB, CCC) and Null Load the areas (with INDEX=NO)
4. Load your reformatted data and initial data, if any, for AAA, BBB, and CCC, using KBYTES=... and INDEX=NO
5. INIT the Index
6. RETIX the entire Database, using a relevant value for SORT=. Be aware that specifying a value too large for SORT could actually result in a slower process, if the SORT program determines that it needs to use SORTWK instead of memory.

This results in touching the index only once at the end of the process to Initialize it and rebuild it, eliminating the major work of deleting the keys and their individual index entries, and also the work to delete each index record for tables BBB and CCC that would normally be done with a Null Load.

 

LOADweight to be borne or conveyedMore (Definitions, Synonyms, Translation)

 

 

 

 

Additional Information

There are many ways to make database change activities run much slower than they should, or much faster than was expected. The key to success is to understand how all of the pieces fit together, and finding the best path through each function to reach the final product.

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