DB2 for z/OS: Native SQL Procedures and STAY RESIDENT

Two or three days back, I got from a DB2 for z/OS proficient an inquiry regarding put away techniques. This individual got some information about the STAY RESIDENT choice of CREATE PROCEDURE (and ALTER PROCEDURE), and its impact on a local SQL methodology (i.e., a put away technique that is composed in SQL strategy dialect – otherwise known as SQL PL – and which keeps running in the DB2 database administrations address space). Not as much as a hour after the email containing the inquiry hit my in-box, another message from the same DB2 individual arrived. The lead word in this second note was “Dismissal,” and that was trailed by clarification of the revelation that STAY RESIDENT isn’t pertinent to a local SQL strategy.

Things being what they are, this inquiry asker had rapidly discovered that STAY RESIDENT is just significant to an outside DB2 put away method (alluding to a put away system that is composed in a dialect, for example, COBOL and which keeps running in a DB2 put away technique address space); still, I feel that the inquiry stays intriguing in light of the fact that it prompts another: if STAY RESIDENT YES or NO can’t be determined on a CREATE or ALTER PROCEDURE explanation fixing to a local SQL strategy, the end result for the executable after the local SQL methodology has been called and afterward hurries to finishing?

In thinking about this inquiry, consider the idea of the executable. For an outer put away technique, the related executable is a heap module. At the point when the outer put away method is first summoned, its heap module will be replicated from a heap library on plate into a put away methodology address space in the z/OS LPAR in which the DB2 subsystem is running. https://www.lsm99-online.com/ After the put away strategy has race to culmination, its heap module will stay in memory in the put away methodology address space if the put away system was made with (or consequently changed to have) the STAY RESIDENT YES determination; generally, the heap module will be erased from memory after the put away technique has got done with executing. For an outside put away technique, in this way, STAY RESIDENT YES is an execution tuning alternative that can be fittingly used for outer put away methodology programs that are accumulated and connected as reentrant and reusable (if a put away strategy program isn’t reentrant and reusable and is characterized – appropriately, all things considered – with STAY RESIDENT NO, module stack time can be lessened by stacking from the z/OS Virtual Lookaside Facility, otherwise known as VLF).

In case we’re discussing a local SQL system, that put away methodology’s unparalleled executable is its DB2 bundle. To put it a somewhat extraordinary route, for a local SQL strategy the bundle is the executable (instead of the outer put away method circumstance, in which the bundle is just piece of the executable story – the other part being the put away technique’s heap module). What would we be able to say in regards to the “remain inhabitant” normal for a local SQL method’s executable (its bundle)? We can say in regards to that what we’d say for ANY DB2 bundle: it relies upon the span of the DB2 skeleton pool, the nature of the string through which the local SQL methodology is summoned, and the RELEASE determination of the SQL system’s bundle. Hereinafter I’ll develop that announcement.

Leave a Reply

Your email address will not be published. Required fields are marked *