XXX Chats

us military online dating

Rowupdating which wasnt

A couple of days ago, I received from a DB2 for z/OS professional a question about stored procedures.This individual asked specifically about the STAY RESIDENT option of CREATE PROCEDURE (and ALTER PROCEDURE), and its effect on a native SQL procedure (i.e., a stored procedure that is written in SQL procedure language -- aka SQL PL -- and which runs in the DB2 database services address space).Anyway, the problem isn't that it can't be done 'manually' - but rather that there are a myriad of ways that you can hook up custom link/command processing and they are all similar but yet quite different.

After the stored procedure has run to completion, its load module will remain in memory in the stored procedure address space if the stored procedure was created with (or subsequently altered to have) the STAY RESIDENT YES specification; otherwise, the load module will be deleted from memory after the stored procedure has finished executing.

For an external stored procedure, therefore, STAY RESIDENT YES is a performance tuning option that can be appropriately utilized for external stored procedure programs that are compiled and linked as reentrant and reusable (if a stored procedure program is reentrant and reusable and is defined -- properly, in that case -- with STAY RESIDENT NO, module load time can be reduced by loading from the z/OS Virtual Lookaside Facility, aka VLF).

Less than an hour after the e-mail containing the query hit my in-box, another message from the same DB2 person arrived.

The lead word in this second note was "Disregard," and that was followed by explanation of the discovery that STAY RESIDENT is not applicable to a native SQL procedure.

If we're talking about a native SQL procedure, that stored procedure's one and only executable is its DB2 package.

To put it a slightly different way, for a native SQL procedure the package the executable (as opposed to the external stored procedure situation, in which the package is only part of the executable story -- the other part being the stored procedure's load module).

This is the blog of Robert Catterall, an IBM Db2 for z/OS specialist.

The opinions expressed herein are the author's, and should not be construed as reflecting official positions of the IBM Corporation.

Also check the number of free pages in the pool (I like to see a number of free page that is at least 10% of the total number of pages in the skeleton pool), and the skeleton pool hit ratio (the percentage of package allocation requests that were satisfied out of the skeleton pool, as opposed to requiring a package load from the skeleton package table in the DB2 directory -- I like to see a hit ratio of 99% or more for a production DB2 subsystem). As mentioned, what's true for a native SQL procedure's package, memory-residency-wise, is true for packages in general.

What's different about a native SQL procedure is the fact that there is no executable beyond the package; so, if that package stays resident in memory, the native SQL procedure stays resident in memory.

Comments Rowupdating which wasnt