Enabling autobind phase-in for packages invalidated at the statement level

You can enable applications to execute specific invalidated packages without waiting for the automatic bind to complete by enabling the packages to use statement-level dependencies and invalidation.

Before you begin

Db2 uses automatic binds only when the ABIND subsystem parameter is set to YES or COEXIST. If ABIND is set to NO when an invalid package runs, Db2 returns an error. For details, see AUTO BIND field (ABIND subsystem parameter).

About this task

FL 504 With statement-level dependencies and statement-level invalidation, applications can execute a package that is invalidated at a statement level without waiting for the completion of the automatic rebind (autobind). The application thread can execute the valid statements immediately. Any invalid statements go through incremental bind in the application thread before execution. Concurrently, Db2 initiates a special autobind, which is called autobind phase-in. When the autobind phase-in finishes successfully, the subsequent executing threads can use the new valid copy of the package, and they no longer use incremental bind for any statements.

For more information, see Autobind phase-in for packages with statement-level invalidation.

Procedure

To identify and enable packages that are likely to benefit from statement-level dependencies, complete the following steps:

Identify potential packages that might be good candidates to use statement-level dependencies. The following query identifies all packages that contain at least one compiled section (static SQL) and were used within the last 18 months. The 18-month time frame is arbitrary, and you can adjust it at your discretion.

SELECT PKG.COLLID, PKG.NAME, HEX(PKG.CONTOKEN) AS CONTOKEN,
       PKG.COPYID, PKG.VERSION, PKG.OWNER, PKG.CREATOR,
       PKG.RELBOUND, PKG.LASTUSED, PKG.PLANMGMT,
       COALESCE(PKS.PKSSTMTCOUNT,0) AS PKS_COUNT,
       COALESCE(PKS2.PKSSECTCOUNT,0) AS SECT_COUNT,
       COALESCE(PKD.PKDCOUNT,0) AS PKD_COUNT,
       COALESCE(PKDN.PKDNCOUNT,0) AS PKDN_COUNT,
       MAX(COALESCE(PKD.PKDCOUNT,0),
       COALESCE(PKDN.PKDNCOUNT,0)) AS TRUE_PKDCOUNT
  FROM SYSIBM.SYSPACKAGE PKG
INNER JOIN ( SELECT COLLID, NAME, CONTOKEN, COUNT(*) AS PKSSTMTCOUNT
               FROM SYSIBM.SYSPACKSTMT
               GROUP BY COLLID, NAME, CONTOKEN ) PKS
      ON PKG.COLLID = PKS.COLLID
     AND PKG.NAME = PKS.NAME
     AND PKG.CONTOKEN = PKS.CONTOKEN
INNER JOIN ( SELECT COLLID, NAME, CONTOKEN, COUNT(DISTINCT SECTNOI) AS PKSSECTCOUNT
               FROM SYSIBM.SYSPACKSTMT
               WHERE STATUS = 'C'
               GROUP BY COLLID, NAME, CONTOKEN ) PKS2
      ON PKG.COLLID = PKS2.COLLID
     AND PKG.NAME = PKS2.NAME
     AND PKG.CONTOKEN = PKS2.CONTOKEN
FULL OUTER JOIN ( SELECT DCOLLID, DNAME, DCONTOKEN, COPYID, COUNT(*) AS PKDCOUNT
                    FROM SYSIBM.SYSPACKDEP
                   WHERE COPYID IS NOT NULL
                 GROUP BY DCOLLID, DNAME, DCONTOKEN, COPYID ) PKD
             ON PKG.COLLID = PKD.DCOLLID
            AND PKG.NAME = PKD.DNAME
            AND PKG.CONTOKEN = PKD.DCONTOKEN
            AND PKG.COPYID = PKD.COPYID
FULL OUTER JOIN ( SELECT DCOLLID, DNAME, DCONTOKEN, COUNT(*) AS PKDNCOUNT
                    FROM SYSIBM.SYSPACKDEP
                   WHERE COPYID IS NULL
                     AND DTYPE <> 'P'
                     AND DTYPE <> 'O'
                   GROUP BY DCOLLID, DNAME, DCONTOKEN ) PKDN
             ON PKG.COLLID = PKDN.DCOLLID
            AND PKG.NAME = PKDN.DNAME
            AND PKG.CONTOKEN = PKDN.DCONTOKEN
WHERE PKG.TYPE = ' '
  AND PKG.VALID = 'Y'
  AND PKG.LASTUSED >= DATE(DAYS(CURRENT DATE) - 548)
ORDER BY TRUE_PKDCOUNT;

Examine the following statistics returned by the query in the previous step:

PKS_COUNT

The total number of SYSPACKSTMT records. This value directly correlates to the number of SYSPACKSTMTCOPY records being recorded.

SECT_COUNT

The total number of compiled sections (static SQL statements that are fully bound) in the package.

TRUE_PKDCOUNT

The number of SYSPACKDEP records for the package. It is either the same as PKD_COUNT or PKDN_COUNT depending on whether the COPYID is populated in SYSPACKDEP.

SECT_COUNT multiplied by TRUE_PKDCOUNT is the worst-case number of SYSPACKSTMTDEP records being recorded when the package uses statement-level dependencies.

Before you run a DDL operation against an object, run the following query to identify packages that depend on the object and were used within the last 18 months.

SELECT PKG.COLLID, PKG.NAME, HEX(PKG.CONTOKEN) AS CONTOKEN,
       PKG.COPYID, PKG.VERSION, PKG.OWNER, PKG.CREATOR,
       PKG.RELBOUND, PKG.LASTUSED
FROM SYSIBM.SYSPACKAGE PKG, SYSIBM.SYSPACKDEP PKD
WHERE PKG.COLLID = PKD.DCOLLID
  AND PKG.NAME = PKD.DNAME
  AND PKG.CONTOKEN = PKD.DCONTOKEN
  AND PKG.COPYID = PKD.COPYID
  AND PKG.LASTUSED >= DATE(DAYS(CURRENT DATE) -548)
  AND PKD.BQUALIFIER = object_qualifier
  AND PKD.BNAME = object_name
  AND PKD.BTYPE = object_type;

For example, the following output indicates that V11.P3, V11.P5, and V11.P8 are packages that depend on the specified object and were used within the past 18 months.

COLLID	NAME
V11	P3
V11	P5
V11	P8

After a package is identified as a good candidate, issue a REBIND subcommand with the DEPLEVEL(STATEMENT) option to use statement-level dependencies.

For more information, see DEPLEVEL bind option.

Use the following examples query to determine whether to continue to use statement-level dependencies for the package:


SELECT DISTINCT PSD.DCOLLID, PSD.DNAME AS PKG_NAME, HEX(PSD.DCONTOKEN) AS DCONTOKEN,
       PSD.DCOPYID, PSD.BSCHEMA, PSD.BNAME AS OBJECT_NAME, PSD.BTYPE,
       COALESCE(PKS.PKSSECTCOUNT,0) AS TOTAL_COMPILED_SECT_IN_PKG,
       COALESCE(PSD2.DEPSTMT,0) AS DEP_STMT,
       ROUND(PSD2.DEPSTMT * 100.0 / PKS.PKSSECTCOUNT) AS PERCENT
  FROM SYSIBM.SYSPACKSTMTDEP PSD
 INNER JOIN ( SELECT COLLID, NAME, CONTOKEN, COPYID,
                     COUNT(DISTINCT SECTNOI) AS PKSSECTCOUNT          
                FROM SYSIBM.SYSPACKSTMT
               WHERE STATUS = 'C'
                 AND VALID = 'Y'
               GROUP BY COLLID, NAME, CONTOKEN, COPYID ) PKS
       ON PSD.DCOLLID = PKS.COLLID
      AND PSD.DNAME = PKS.NAME
      AND PSD.DCONTOKEN = PKS.CONTOKEN
      AND PSD.DCOPYID = PKS.COPYID
 INNER JOIN ( SELECT DCOLLID, DNAME, DCONTOKEN, DCOPYID, BSCHEMA,
                     BNAME, BTYPE, COUNT(*) AS DEPSTMT
                FROM SYSIBM.SYSPACKSTMTDEP
               GROUP BY DCOLLID, DNAME, DCONTOKEN, DCOPYID,
                        BSCHEMA, BNAME, BTYPE ) PSD2
       ON PSD.DCOLLID = PSD2.DCOLLID
      AND PSD.DNAME = PSD2.DNAME
      AND PSD.DCONTOKEN = PSD2.DCONTOKEN
      AND PSD.DCOPYID = PSD2.DCOPYID
      AND PSD.BSCHEMA = PSD2.BSCHEMA
      AND PSD.BNAME = PSD2.BNAME
      AND PSD.BTYPE = PSD2.BTYPE
ORDER BY DCOLLID, DNAME, DCONTOKEN, DCOPYID, BSCHEMA, BNAME, BTYPE;

Evaluate each individual package, and its usage based on the output of the query in the previous step to determine whether to continue to use statement-level dependencies for the package.

Look at the following key fields in the output of the preceding query:

TOTAL_COMPILED_SECT_IN_PKGS: The total number of compiled sections for the package.
DEP_STMT: The total number of statements that reference an object.
PERCENT: The total percentage of statements that reference a particular object.

For example, consider the following result:

PKG_NAME	OBJECT_NAME	TOTAL_COMPILED_SECT_IN_PKG	DEP_STMT	PERCENT
V11.P8	IX1	20	1	5.0
V11.P8	TB1	20	6	30.0
V11.P8	TB2	20	3	15.0
V11.P8	TB4	20	1	5.0
V11.P8	TS1	20	6	30.0
V11.P8	TS2	20	3	15.0
V11.P8	TS4	20	1	5.0
V11.P8	VW1	20	1	5.0
V11.P9	IX1	5	5	100.0
...

In this example, only one statement references table TB4. If you expect to actively alter this table, the V11.P8 package is probably a good candidate to use statement-level dependencies. Each time that TB4 is altered, it affects only a small percentage of statements in the package. However, if TB1 is a table that is altered often, this package might not benefit as much from statement-level dependencies. After TB1 is altered, executing this package would result in 30% of statements going through incremental bind until the autobind phase-in finishes. The V11.P9 package has all statements referencing the index IX1, so the worst case is that all five statements would go through the incremental bind. However, the application logic might execute only one of the five statements. Thus, not much incremental bind cost is incurred, and there is no need to wait for autobind to finish.

The benefits of using statement-level dependencies depend on the usage and characteristics of the individual package.

If you find a package is not a good candidate, issue a REBIND PACKAGE statement with the DEPLEVEL(PACKAGE) option to revert to use package-level dependencies for the package. You can issue a second rebind of the package with DEPLEVEL(PACKAGE) to remove the unnecessary statement-level dependency recording for the previous copy.

What to do next

Evaluate the MAX_CONCURRENT_PKG_OPS subsystem parameter values. For example, if you prefer the total maximum number of service tasks for regular autobind and autobind phase-in to be 10, set MAX_CONCURRENT_PKG_OPS to 5. There are at most 5 service tasks for regular autobind and another 5 service tasks for autobind phase-in.
Consider changing the maximum number of locks to avoid lock escalation. With statement-level dependencies, BIND, REBIND, autobind, and DDL processes access more catalog records, so the number of lock requests increases. If there are more row locks requested on a catalog table or by a process than the maximum number of locks allowed, Db2 escalates the table space lock with S or X mode. Lock escalation can impact the concurrent access of the catalog table by other threads and is reported in message DSNI031I. The initial default value of the LOCKMAX clause for catalog tables is determined by the NUMLKTS subsystem parameter value. You can change it by using the ALTER TABLESPACE statement with the LOCKMAX clause.
Consider whether to monitor the new accounting and statistics trace information for evaluating the cost of incremental binds. If waiting occurs for the service tasks for the autobind phase-in queue, it might imply that more threads need to incremental bind the invalid statements. Increasing the MAX_CONCURRENT_PKG_OPS might help to expedite the autobind phase-in process, which can make the new current copy available sooner, and thus eliminates incremental bind for new threads.
Plan to remove unneeded dependency records for invalidated statements in the SYSPACKDEP and SYSPACKSTMTDEP tables. When the invalid current copy of a package becomes a phased-out copy due to a successful autobind phase-in, the dependency records of the invalidated statements remain. You can free the phased-out package copies by using the FREE PACKAGE command with the PLANMGMTSCOPE(PHASEOUT) bind option.