Adabas fields that are not being used

, , ,
1

Hello :slight_smile:
Recently I started doing three things to improve Adabas performance:

  • 1. to erase fields not used from FDT;
    2. to reorder the FDT putting at the beggining the fields more frequently used;
    3. to sort the file by the descriptor more used in sequential read operations.

These three things above, in some cases, have reduced Adabas cpu usage by 40% and more in read operations.

Now, my biggest problem is how to identify accurately which fields are in use or not in a file and which fields are more frequently used. Considering that I don’t have Adabas Review, does anyone have any idea how to solve it?

2

I think about the only way to achieve this would be to enable command logging and analyze the logs,
or write your own user exit 4 / uex B to capture the file and FB information.

3

Wolfgang, thank you very much for your attention.

I’ll try to capture the necessary information on UEXIT4. I’ll post the code here if it works…

4

Spontaneously I´m thinking of the usage of Triggers (SPATs), there you can defined fieldnames in conjunction with commands and if a trigger ´fires´ you know that this is used and vice versa.
But during the time of ´inspection´ you´ll probably see an increase of CPU :frowning:

But are you sure that there are no fields which are maybe only used once a year from a special program ?

Do you have only Natural programs or also others ?

If yes, I think first removing the fields from the DDM and then - if all works - from the database would be easier ?

5

Just to add to Ursula’s comment.

In the course of traveling around the world teaching Natural, I can tell you that the single most common File View is a … DDM. Right, the entire DDM, even though only five fields out of 60 might actually be used in the code. To make this harder to deal with, the view is often in an external LDA. This LDA may be used by a dozen or more programs, none of which uses more than fifteen fields from the file.

The consequence of this? In order to “unravel” this mess you would have to create specific LDA’s for each program (or embed the views in the code). Since this means a lot of work, few shops are actually interested in such a project.

I have also seen code similar to, but even nastier to deal with, another of Ursula’s comments. Within a single program (not even a special program), there will be a large IF statement, the argument of which is true once a year for a special year end sales analysis. Yet, there will be a single view, used for the daily processing and the year end (or quarter end) processing.

With a 40% Adabas CPU saving from the three actions you mentioned, you have probably saved more than enough to justify the effort involved in the three actions. The effort to deal with “real need” of fields (that is, fields that are really used in programs), rather than dealing with field transfer from Adabas, which can be VERY misleading, would be an expensive action (although the results could be even more impressive than the 40% you have already saved). And, as noted earlier, such actions are often rejected by non technical management, concerned with disturbing code that works (and works, and works…)

steve

6

HenriAlbert - did you do the changes in stages and check the improvement in performance from each? Without knowing the specifics of your environment and application, the last item (reorder on the most frequently used superdescriptor) is what I would expect to gain you the most as it impacts disk I/O and its attendent CPU. In earlier versions of Adabas, CPU associated with disk I/O was considerably more than could be readily identified with the other tuning options. It is also the easiest to do, and can be re-done periodically to retain the CPU reductions.

If fields are not referenced by programs, I would expect them to have little effect on CPU usage. And removing them is a bit of work (and may cause programs to fail, even if they’re not really using the removed fields).

If you have the opportunity to do so, I’d love to hear what the difference in performance is between a properly ordered FDT and same one in the reverse order (which should cause the higher CPU usage), and at what volume it becomes measurable. It has been a while since I could do detailed performance tests on specific cases, so I’m very interested in how Adabas 8 is impacted by these types of tuning.

7

Hi Ursula, Steve, Douglas. First of all, thank you for your comments.

48 hours before I believed Uexit4 was everything I needed. Now I’m not sure.

Anyway, let me explain a bit more about what I’ve done.

How it all began:
Observing the Natural User Sessions (SYSTP), I’ve noted several copies of two or three programs running for more than a minute, and at times when this happened, the response time of the others got worse, reaching a point where customers started to complain. So, I’ve selected some of those “bad guys” to investigate the possible reasons of the high response time. I noticed they were not too complex. In common, they just had access to the same Adabas file.

Change the logic of the programs was possible but time consuming, and I had to have the cooperation of others. What else could I do?.. investigate what could be done in terms of Adabas.

So, I developed a program to replicate in lab a situation with a READ instruction similar to those of production. In this way I could validate the three criteria I’ve mentioned earlier, that I recall here:
(A) Elimination of obsolete fields in the FDT*;
(B) Reordering the fields of FDT;
(C) Sorting files by the most commonly used descriptors.

* 99 fields has been removed from FDT. But, after the test I found that there is more fields to remove.

These are the results:
Adabas CPU consumption obtained in the execution of the sample program.


		            Original    After (A)	 After (A+B)    After(A+B+C)	%Reduction in CPU				
data on disk:       8,74		   7,63	   6,21           4,94              43,5%
data on BP:         7,69		   6,75	   5,23           4,60              40,2%

Based on these results, I consider useful to apply the three criteria.
I really don’t know if the cpu reduction (item A) is due to I/O reduction or due to the Adabas necessity of “jump” over the fields on the DS until it gets what it needs, because approximately 50% of the fields removed were positioned after the position 161 in FDT. (see position of used fields as comments in the sample program)

The sample program:


0010 DEFINE DATA LOCAL	
0020 1 SAE VIEW OF SAEW-EXERCICIO
0030 2 EXE-VINCULO	/*	(FDT original position: 152)
0040 2 EXE-GPO-NRE	/*	(FDT original position: 153)
0050 2 EXE-MUN-EST	/*	(FDT original position: 154)		
0060 2 EXE-ENSINO	/*	(FDT original position: 155)
0070 2 EXE-FUNCAO	/*	(FDT original position: 156)
0080 2 EXE-CH		/*	(FDT original position: 157)
0090 2 EXE-ATO		/*	(FDT original position: 158)
0100 2 EXE-DATA-ATO	/*	(FDT original position: 159)
0110 2 EXE-SIT-REG	/*	(FDT original position: 160)
0120 2 EXE-MOT-CANC	/*	(FDT original position: 161)
0130 2 EXE-RG-AFA-NEW	/*	(FDT original position: 373)
0140 2 EXE-TURNO	/*	(FDT original position: 384)
0150 2 EXE-RG-LF-NEW	/*	(FDT original position: 372)
0160 *
0170 1 XX (P7)
0180 END-DEFINE
0190 *
0200 READ SAE BY EXE-SUP-MUN-NEW
0210 	ADD 1 TO XX
0220 	IF XX GT 100000
0230 		ESCAPE BOTTOM
0240 	END-IF
0250 END-READ
0260 END

With respect to Ursula’ comments, I would like to add that all the maintenances will be performed primarily in the DDM. We have a Data Dictionary (not Predict, but a better one :D). Unfortunately, not all the things registred in our data dictionary, such as removing of fields, are replicated in Adabas. And, not all the things are entered to the Dictionary.
I don’t have others accessing Adabas. Just Natural.

With respect to the Steve’ comments, I would say that last year we work to eliminate fields of views (from internal and external data areas), when those were not referenced in the code. The results were impressive.

With respect to the Douglas’ comments, I hope I explained everything. However, if I have time, I’ll put the fields referenced by my sample program at the end of the file, the far opposite of where they are today, just to check the results.

Finnaly, I believe that Uexit4 is the right place to construct a generic solution so that new fields can be added to any file in any database and no new maintenance in the exit would be necessary. That is the good.
The bad is that the use of the multi-fetch feature will mask the real values accounted for by the exit.

Probably I’ll separate the work in two steps:

  • 1. Start immediately doing the job, using the available information;
    2. collect more information over a year and redo the job.
8

Don’t think multifetch is a problem here, while you can’t count the # of calls as easily you still get a peek at the FB
so you find out which fields are actually used, and that’s all you’re after, or ?

9

Wolfgang,

I was imagining something like this:


               AA use	AA last use	AB use	AB last use	…	Z9 use	Z9 last use
File 1							
File 2							
File 3							
…							
File n

An area where I could save a counter indicating the # of times the fields from any file are being used and the date of the last use. Uexit4 would save in this area for each field from FB. Uexit8 would be used to display (or write to SMF, if possible) the area by a “special command” or when Adabas finished.

So, I would know how fields are in use and which are the fields more used. But, I believe that using multi-fetch, one command pass by the exit and many records return to the client. If just one command pass by the exit, I would lost the correct information about the # of times the fields are being used.

10

You do get the ISN and multifetch buffer, plus you have the ISN quantity field in the CB,
so while you may not get the actual # of commands you can still derive a closely matching “potential number of commands” :wink:

11

Excellent idea. Thank you, Wolfgang. :smiley:

12

You are very welcome :oops: