Start of change

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 (Db2 13 uses the same behavior is used for both settings.). 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:

  1. 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;
  2. 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.

  3. 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
  4. 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.

  5. 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;
  6. 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.

  7. 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.
End of change