Tech Tips: CA Datacom/DB 14.0 - New Keyword SORTDFLT for DBUTLTY LOAD and RETIX

[Legacy User]

Tech Tip from Pat O'Brien, Principal Support Engineer, for July 21, 2015

 

CA Datacom Version 14.0 provided a new keyword SORTDFLT for the LOAD and RETIX functions.

 

When SORTDFLT=YES is used along with the SORT=value, DBUTLTY LOAD and RETIX look at the record counts in the CXX (or LOAD BACKUP record count

input) to override the  SORT value with an expected more accurate SORT value.

 

See the CA Datacom Version 14.nn Release Notes for more details …

https://supportcontent.ca.com/cadocs/0/CA%20Datacom%2014%200%20Public-ENU/Bookshelf_Files/PDF/Datacom1401--Release_ENU.pdf

[RGBogan]

Thanks, PAT/JD for the tip.  I had not seen this before.  I am sure there are sites that will love this and sites that will refuse to use it.  It could simplify a DBAs life a little bit, not needing to recalc SORT= parms every month or two.

[Patricia OBrien]

Thanks for your comments Bob!

[Kathy Heikes]

Thanks for this tip, like Bob I had not seen this one before.  I’ll give it a try.

 

Kathy Heikes

Mainframe DB Services

Sr. Staff – DB Administrator

Charles Schwab & Co. Inc., PHX4701A-02-2234

Tel  [phone] | Cell  480 322-4500

[David Butler]

Love this tip!

[DonAxon]

Excellent tip

[MikeSmith]

I like this tip. A test of a data area retix (in development region) had significant execution time improvement.

 

I am thinking of using this keyword soon in our production environment with a RETIX function. Can the 'SORTDFLT' keyword be used in all RETIX scenarios?

[Patricia OBrien]

Hi Mike,

 

Both version 15.0 and 14.01 DBUTLTY guides shows SORTDFLT as an option only in RETIX for "Rebuilding a Full Index Area" but not in the syntax diagrams for "Rebuilding an Area's Index in Single User" or  "Rebuild an Area's Index Area in MUF".

 

I will check into this further.

 

Thanks for asking!

Pat

[Anon Anon]

SORTDFLT=YES is indeed a great new feature, and it is appropriate in most RETIX scenarios.

 

Critical to its success (as a performance enhancement) is that the key statistics in the CXX must be accurate. If the statistics in the CXX are inaccurate then the value that SORTDFLT provides may be too high or too low - either of which can negatively impact performance.

 

If you only use normal applications (Ideal, COBOL etc.) + DBUTLTY to perform all maintenance of your CA Datacom tables then the CXX statistics should be pretty accurate, and SORTDFLT will work well.

 

In previous releases of CA Datacom, if there was a MUF failure (MUF abend or power outage etc.) then the CXX stats could become very inaccurate. But if you have kept up with the very latest PTFs in Datacom 14.0 then there are recent enhancements that aim to keep the CXX stats as accurate as possible for almost all scenarios.

 

And now I will come to a sample scenario where SORTDFLT is NOT appropriate...

 

Let's say we have two MUFs. One "Live" MUF that is heavily updated, and a second MUF (maybe a "Monthly reporting" MUF or a "User Acceptance Testing" MUF) that is refreshed from the "Live" MUF.

For performance reasons, the method used to perform the refresh to the second MUF does NOT use DBUTLTY BACKUP/LOAD. Instead it uses one of the fast DASD copy utilities (EMCSNAP, DFSMSdss etc.). The IXX and Data Area datasets are copied, but the CXX is not copied (for example because perhaps only certain tables are to be "refreshed").

Further let's say the "Live MUF" has 1,000,000 rows and 3 keys on this table. So the SORTDFLT in the Live MUF would calculate ~3,000,000 index entries to be sorted - correctly.

But the second MUF's CXX still thinks this table only has 100 rows. So SORTDFLT in the second MUF will calculate ~300 index entries to be sorted even though the actual IXX/DATA areas have the 1,000,000 rows/3,000,000 index entries. In this case the sort execution will be negatively impacted.

 

So if you use non-DBUTLTY DASD copy utilities to move/copy data from one environment to another (or even within the same MUF) then either code an explicit SORT=value from the "Live" MUF, or use the following DBUTLTY command on the second MUF after you have completed the DASD copy utility..

RETIX DBID=nnnnn,KEYNAME=*SETR

...to re-count the rows and update the CXX statistics. Then SORTDFLT=YES will work perfectly in the second MUF too.