IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Calling a Cursor Driven Program Statically or Dynamically


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
eprasad629

New User


Joined: 25 Sep 2009
Posts: 9
Location: Bangalore

PostPosted: Sat Jul 09, 2011 10:43 am
Reply with quote

Hi,

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.

Many thanks for your time.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Sat Jul 09, 2011 11:10 am
Reply with quote

Hello,

Suggest you start over with a more reasonable premise. . .

FETCHing a row is an interaction with database while a call transfers program control to some other bit of code.

Possibly, there is something i misunderstand.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Sat Jul 09, 2011 11:41 am
Reply with quote

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.
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Sat Jul 09, 2011 12:47 pm
Reply with quote

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.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Sat Jul 09, 2011 2:14 pm
Reply with quote

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.
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Sat Jul 09, 2011 3:49 pm
Reply with quote

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.
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Sat Jul 09, 2011 3:56 pm
Reply with quote

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.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Using API Gateway from CICS program CICS 0
No new posts DSNTIAUL driven delete IBM Tools 0
No new posts Calling DFSORT from Cobol, using OUTF... DFSORT/ICETOOL 5
No new posts Calling Java method from batch COBOL ... COBOL Programming 5
No new posts Calling an Open C library function in... CICS 1
Search our Forums:

Back to Top