Showing posts with label stored procedures. Show all posts
Showing posts with label stored procedures. Show all posts

Thursday, June 25, 2020

Db2 12 for z/OS Function Level 507

This month, June 2020, IBM introduced a new function level, FL507, for Db2 12 for z/OS. This is the first new function level this year, and the first since October 2019. The Function Level process was designed to release Db2 functionality using Continuous Delivery (CD) in short, quick bursts. However, it seems that the global COVID-19 pandemic slowed things a bit… and that, of course, is understandable. But now we have some new Db2 for z/OS capabilities to talk about for this first time in a little bit! 

There are four significant impacts of this new function level:

  • Application granularity for locking limits
  • Deletion of old statistics from the Db2 Catalog when using profiles
  • CREATE OR REPLACE capability for stored procedures
  • Passthrough-only expressions with IBM Db2 Analytics Accelerator (IDAA)

Let’s take a quick look at each of these new things.

The first new capability is the addition of application granularity for locking limits. Up until now, the only way to control locking limits was with NUMLKUS and NUMLKTS subsystem parameters, and they applied to the entire subsystem. 

NUMLKTS defines the threshold for the number of page locks that can be concurrently held for any single table space by any single DB2 application (thread). When the threshold is reached, DB2 escalates all page locks for objects defined as LOCKSIZE ANY according to the following rules:

  • All page locks held for data in segmented table spaces are escalated to table locks.
  • All page locks held for data in partitioned table spaces are escalated to table space locks.

NUMLKUS defines the threshold for the total number of page locks across all table spaces that can be concurrently held by a single DB2 application. When any given application attempts to acquire a lock that would cause the application to surpass the NUMLKUS threshold, the application receives a resource unavailable message (SQLCODE of -904).

Well, now we have two new built-in global variables to support application granularity for locking limits. 

The first is SYSIBMADM.MAX_LOCKS_PER_TABLESPACE and it is similar to the NUMLKTS parameter. It can be set to an integer value for the maximum number of page, row, or LOB locks that the application can hold simultaneously in a table space. If the application exceeds the maximum number of locks in a single table space, lock escalation occurs.

The second is SYSIBMADM.MAX_LOCKS_PER_USER and it is similar to the NUMLKUS parameter. You can set it to an integer value that specifies the maximum number of page, row, or LOB locks that a single application can concurrently hold for all table spaces. The limit applies to all table spaces that are defined with the LOCKSIZE PAGE, LOCKSIZE ROW, or LOCKSIZE ANY options. 

The next new capability is the deletion of old statistics when using profiles. When you specify the USE PROFILE option with RUNSTATS, Db2 collects only those statistics that are included in the specified profile. Once function level 507 is activated, Db2 will delete any existing statistics for the object(s) that are not part of the profile. This means that all frequency, key cardinality, and histogram statistics that are not included in the profile are deleted from the Db2 Catalog for the target object. 

This is a welcome new behavior because it makes it easier to remove old and stale distribution statistics. Keep in mind that this new behavior also applies when you use profiles to gather inline statistics with the REORG TABLESPACE and LOAD utilities.

Another great new capability that stored procedure users have been waiting for for some time now is the ability to specify CREATE OR REPLACE for procedures. This means that you do not have to first DROP a procedure if you want to modify it. You can simply specify CREATE OR REPLACE PROCEDURE and if it already exists, the procedure will be replaced, and if not, it will be created. This capability has been available in other DBMS products that support stored procedures for a while and it is good to see it come to Db2 for z/OS!

Additionally, for native SQL procedures, you can use the OR REPLACE clause on a CREATE PROCEDURE statement in combination with a VERSION clause to replace an existing version of the procedure, or to add a new version of the procedure. When you reuse a CREATE statement with the OR REPLACE clause to replace an existing version or to add a new version of a native SQL procedure, the result is similar to using an ALTER PROCEDURE statement with the REPLACE VERSION or ADD VERSION clause. If the OR REPLACE clause is specified on a CREATE statement and a procedure with the specified name does not yet exist, the clause is ignored and a new procedure is still created.

And finally, we have support for passthrough-only expressions to IDAA. This is needed because you may want to use an expression that exists on IDAA, but not on Db2 12 for z/OS. With a passthrough-only expression, Db2 for z/OS simply verifies that the data types of the parameters are valid for the functions. The expressions get passed over to IDAA, and the accelerator engine does all other function resolution processing and validation. 

What new expressions does FL507 support you may ask? Well all of the following built-in functions are now supported as passthrough-only expressions to IDAA:

  • ADD_DAYS
  • BTRIM
  • DAYS_BETWEEN
  • NEXT_MONTH
  • Regression functions 
    • REGR_AVGX
    • REGR_AVGY
    • REGR_COUNT
    • REGR_INTERCEPT
    • REGR_ICPT
    • REGR_R2
    • REGR_SLOPE
    • REGR_SXX
    • REGR_SXY
    • REGR_SYY
  • ROUND_TIMESTAMP (when invoked with a DATE expression)

You can find more details on the regression functions from IBM here

Summary

These new capabilities are all nice, new features that you should take a look at, especially if you have applications and use cases where they can help. 

The enabling APAR for FL507 is PH24371. There are no incompatible changes with FL 507. But be sure to read the instructions for activation details and Db2 Catalog impacts for DL 507.

Wednesday, October 09, 2013

Using the DISPLAY Command, Part 4

In this fourth entry of our series on the DISPLAY command, we take a look at using the DISPLAY command to monitor DB2 stored procedures and user-defined functions. Part 1 of this series focused on using DISPLAY to monitor details about you database objects; Part 2 focused on using DISPLAY to monitor your DB2 buffer pools. And Part 3 covered utility execution and log information. 

If your organization uses stored procedures and/or user-defined functions (UDFs), the DISPLAY command once again comes in handy. 

Stored Procedures

You can use the DISPLAY PROCEDURE command to monitor stored procedure statistics. The output will consist of one line for each stored procedure that a DB2 application has accessed. You can qualify stored procedure names with a schema name.

DISPLAY PROCEDURE returns the following information:

  • The status, that is, whether the named procedure is currently started or stopped
  • How many requests are currently executing
  • The high-water mark for concurrently running requests
  • How many requests are currently queued
  • How many times a request has timed out
  • How many times a request has failed
  • The WLM environment in which the stored procedure executes

Here is an example of what will be output by the DISPLAY PROCEDURE command:

DSNX940I = DSNX9DIS DISPLAY PROCEDURE REPORT FOLLOWS
PROCEDURE  STATUS   ACTIVE  QUED  MAXQ  TIMEOUT  FAIL WLM_ENV
CUSTPROC   STARTED    0       0     1      0       0  WLMDB21
SAMPPRC1   STOPQUE    0       5     5      3       0  WLMSAMP
SAMPPRC2   STARTED    2       0     6      0       0  WLMSAMP
GETDATA1   STOPREJ    0       0     1      0       0  WLMDB21
DSNX9DIS DISPLAY PROCEDURE REPORT COMPLETE

DSN9022I = DSNX9COM '-DISPLAY PROC' NORMAL COMPLETION

Keep in mind that the information returned by  DISPLAY PROCEDURE is dynamic. By the time the information is displayed, it is possible that the status could have changed.

User-Defined Functions (UDFs)

For UDFs, you can use the DISPLAY FUNCTION SPECIFIC command to monitor UDF statistics. This command displays one output line for each function that a DB2 application has accessed.  Similar to what is shown for stored procedures, the DISPLAY FUNCTION SPECIFIC command will show:
  • Whether the named function is currently started or stopped, and why
  • How many requests are currently executing
  • The high-water mark for concurrently running requests
  • How many requests are currently queued
  • How many times a request has timed out
  • The WLM environment in which the function executes
When displaying information about stored procedures and UDFs using the DISPLAY PROCEDURE and DISPLAY FUNCTION SPECIFIC commands, a status is returned indicating the state of the procedure or UDF. A procedure or UDF can be in one of four potential states:


STARTED
Requests for the function can be processed
STOPQUE
Requests are queued
STOPREJ
Requests are rejected
STOPABN      
Requests are rejected because of abnormal termination


Summary

When using stored procedures and/or user-defined functions, be sure to use the DISPLAY command to keep track of their status.

Thursday, March 27, 2008

Updated Stored Procedure RedBook for DB2 V9

If you are a stored procedure developer, or a DBA who manages a system with stored procedures - - and you are running DB2 9 for z/OS - - there is an updated RedBook you should download immediately. It is titled DB2 9 for z/OS Stored Procedures: Through the CALL and Beyond. You may have the previous edition, DB2 for z/OS Stored Procedures: Through the CALL and Beyond (SG24-7083)...

This new edition of an all-time favorite RedBook is newly updated to show the changes that have happened to DB2 stored procedures and related tools from V8 to V9. It offers examples and guidelines for developing stored procedures in several languages. You will also find many useful recommendations for setting up and tuning your environment for stored procedures in this free-to-download manual.

And if you are looking for some "stuff" on using Data Studio with stored procedures, this is the place to go... so, it is time to update by downloading this new edition today!