I have a DB2 program that uses a cursor. Upon a successful fetch, for validation, I call a sub-routine statically.
Does static calling a program from a FETCH statement, poses any impact on DB2 performance (in terms of maintaining log until the statically called program is terminated)?
For eg., let say "OPEN CURSOR" results in some 20000 rows and I call the validation sub-routine 20000 times statically.
Should I use Dynamic calling?
Please let me know If I sound unclear and I will get back to you sooner.
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
whew! ever a candidate for beginners forum, this is it.
but, will provide a little info, since it is sat morning.
1. db2 will maintain the results set of the cursor until run unit/ task has ended or cursor is closed. has nothing to do with COBOL CALLing submodules.
2. the difference in execution time between static calls and dynamic calls is the extra nano-seconds required for the first dynamic call. second and subsequent calls to a dynamically called module that is not canceled is no different that that of a call to a statically linked module.
3. statically linked modules mean that each time the submodule is changed, the calling module also must be relinked after the submodule object is created.
that means if the submodul is statically linked to 50 calling modules, each time a submodul is changed, 50 other modules must be relinked. since most shops do not have separate relink steps, that means the 50 modules are recompiled (pre-compiled if db2) and then relinked. so an arguement against static calls is that if the submodule is changed this archaic methodology requires at a minimum extra binder jobs.
as Dick said, the dynamic/static call of a submodul has nothing to do with the db2 portion of the calling module.
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
Until I became a "tuning expert" (see here) I would have said exactly the same words as dbz about the CALL.
From the manual I googled at random (VS COBOL II Release 3.2 and Release 4.0 Performance Tuning):
Quote:
DYNAM or NODYNAM
The DYNAM compiler option specifies that all subprograms invoked through the CALL statement will be
loaded dynamically at run time. This allows you to share common subprograms among several different
applications, allowing for easier maintenance of these subprograms since the application will not have to be
re-linkedited if the subprogram is changed. DYNAM also allows you to control the use of virtual storage in
that when a subprogram is no longer needed, you can free the virtual storage used by that subprogram with
the CANCEL statement. However, when using the DYNAM option, you pay a slight performance penalty
since the call must go through a library routine, whereas with the NODYNAM option, the call goes directly
to the subprogram. Hence, the path length is slightly longer with DYNAM than with NODYNAM. (APG:
MVS pp 174, 296; VSE pp 168, 277)
This is for completeness. The difference is slight except for a non-trivial called program, as the code you normally have in the program takes up the vast percentange of the amount of time spent in executing the call.
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
the extra code executed in the boot-strap routines that are included in each dynamically called module is what takes up the time.
Which by the way is only measured in percentage.
and the percentages are based on repeated submodules that only consisted of a goback.
Bill, i realize that you mentioned this briefly, but since we are dealing with percentages, we are not describing the complete picture.
If we actually consider executing some code in the submodules,
the percentage difference is reduced. if the amount of code executed by the submodul is greater that the boot-strap routine,
we reduce the percentage automatically by a factor of 2.
When you consider that computers execute in the millions of instructions per second, the time is insignificant.
when you play the percentage game, the figures start approaching greater than 1 and suddenly 'significance' becomes a word that can be used.
we all know that the two speakers attached with 4 feet of cable receive the signal faster than the two attached with 10 feet of cable. but can you tell the difference?
the same applies to the dynamic vs static. on small machines, maintained by experieced professionals, that run thousands of run-units, you might save 'some' time over a 'period' of time.
but that is offset by the time necessary to relink the complete run-unit
(with special re-link parameters - consideration must be given to
REPLACE IGZEBST
and
REPLACE IGZCBSN
and
REPLACE IGZENRI)
in the case of static calls which is why most shops just go thru the complete process of pre-compile/compile/link of calling module.
which uses cpu that could be better used in the production environment.
to make a case for static
means you have to ignore all the benefits of dynamic.
The use of percentages to argue for static over dynamic is like the speaker cable length comparison. you know the speakers closer to the amp are receiving the signal faster, but there is no way you can hear the difference.
Yes, I stand corrected. the second dynamic call will also be slower than the second static call.
but even knowing this, IBM's kernal is composed of many many dynamic calls. so even IBM knows there is a benefit for dynamic over static - even though it is slower.
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
I agree dbz. It was a bit of Saturday morning irony. A couple of months ago I used almost the exact words you did, and then later found I was wrong. In the performance document you can get hit with a headline percentage which looks scary, but it is from a meaningless example - although it warns about that.
One of the tuning documents points out something like a 40% difference, but makes the warning about the balance between the code for the call and the code you have yourself for doing the useful stuff which is why you have the sub-routine in the first place.
The timings are probably for a test without USING on the CALL or the PROCEDURE DIVISION. Even putting those on, the percentage difference starts to drop. Each line of code in the program drops the difference further.
I agree with you dbz (see last para of the post). The point of the post was to try to puncture the prevarications of the performance plagiarists. If you blindly follow the advice, you'll get no improvement in performance at run-time, and reduction in development throughput. If you understand the advice, you'd know to use the STATIC for performance only in the most extreme cases.
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
I see I got "trivial" and "non-trivial" the wrong way round in my first post, explainging dbz's concerns.
To repeat. There is a difference between the time taken for STATIC/DYNAMIC calls. In all except cases of trivial sub-programs, you'd not notice the difference. For performance issues, STATIC vs DYNAMIC is not relevant, except in extreme circumstances where you are willing to trade-off against the extra maintenance effort.