Sample SELECT statements for generating REBIND commands

You can select specific plans or packages to be rebound and concatenate the REBIND subcommand syntax around the plan or package names. You can also convert a varying-length string to a fixed-length string, and append additional blanks to the REBIND PLAN and REBIND PACKAGE subcommands, so that the DSN command processor can accept the record length as valid input.

Building REBIND subcommands: The examples that follow illustrate the following techniques:

Using SELECT to select specific packages or plans to be rebound
Using the CONCAT operator to concatenate the REBIND subcommand syntax around the plan or package names
Using the SUBSTR function to convert a varying-length string to a fixed-length string
Appending additional blanks to the REBIND PLAN and REBIND PACKAGE subcommands, so that the DSN command processor can accept the record length as valid input

If the SELECT statement returns rows, then DSNTIAUL generates REBIND subcommands for the plans or packages identified in the returned rows. Put those subcommands in a sequential data set, where you can then edit them.

For REBIND PACKAGE subcommands, delete any extraneous blanks in the package name, using either TSO edit commands or the Db2 CLIST DSNTEDIT.

For both REBIND PLAN and REBIND PACKAGE subcommands, add the DSN command that the statement needs as the first line in the sequential data set, and add END as the last line, using TSO edit commands. When you have edited the sequential data set, you can run it to rebind the selected plans or packages.

If the SELECT statement returns no qualifying rows, then DSNTIAUL does not generate REBIND subcommands.

The examples in this topic generate REBIND subcommands that work in Db2 for z/OS® Db2 12. You might need to modify the examples for prior releases of Db2 that do not allow all of the same syntax.

Example: REBIND all plans without terminating because of unavailable resources.

SELECT SUBSTR('REBIND PLAN('CONCAT NAME
  CONCAT')                        ',1,45)
FROM SYSIBM.SYSPLAN;

Example: REBIND all versions of all packages without terminating because of unavailable resources.

SELECT SUBSTR('REBIND PACKAGE('CONCAT COLLID CONCAT'.'
  CONCAT NAME CONCAT'.(*))        ',1,55)
FROM SYSIBM.SYSPACKAGE;

Example: REBIND all plans bound before a given date and time.

SELECT SUBSTR('REBIND PLAN('CONCAT NAME
  CONCAT')                        ',1,45)
FROM SYSIBM.SYSPLAN
WHERE BINDDATE <= 'yymmdd' OR
     (BINDDATE <= 'yymmdd' AND
      BINDTIME <= 'hhmmssth');

where yymmdd represents the date portion and hhmmssth represents the time portion of the timestamp string.

If the date specified is after 2000, you need to include another condition that includes plans that were bound before year 2000:

WHERE 
      BINDDATE >= '830101' OR
      BINDDATE <= 'yymmdd' OR
     (BINDDATE <= 'yymmdd' AND
      BINDTIME <= 'hhmmssth');

Example: REBIND all versions of all packages bound before a given date and time.

SELECT SUBSTR('REBIND PACKAGE('CONCAT COLLID CONCAT'.'
  CONCAT NAME CONCAT'.(*))        ',1,55)
FROM SYSIBM.SYSPACKAGE
WHERE BINDTIME <= 'timestamp';

where timestamp is an ISO timestamp string.

Example: REBIND all plans bound since a given date and time.

SELECT SUBSTR('REBIND PLAN('CONCAT NAME
  CONCAT')                        ',1,45)
FROM SYSIBM.SYSPLAN
WHERE BINDDATE >= 'yymmdd' AND
  BINDTIME >= 'hhmmssth';

where yymmdd represents the date portion and hhmmssth represents the time portion of the timestamp string.

Example: REBIND all versions of all packages bound since a given date and time.

SELECT SUBSTR('REBIND PACKAGE('CONCAT COLLID
  CONCAT'.'CONCAT NAME
  CONCAT'.(*))        ',1,55)
FROM SYSIBM.SYSPACKAGE
WHERE BINDTIME >= 'timestamp';

where timestamp is an ISO timestamp string.

Example: REBIND all plans bound within a given date and time range.

SELECT SUBSTR('REBIND PLAN('CONCAT NAME
  CONCAT')                        ',1,45)
FROM SYSIBM.SYSPLAN
WHERE
 (BINDDATE >= 'yymmdd' AND
  BINDTIME >= 'hhmmssth') AND
  BINDDATE <= 'yymmdd' AND
  BINDTIME <= 'hhmmssth');

where yymmdd represents the date portion and hhmmssth represents the time portion of the timestamp string.

Example: REBIND all versions of all packages bound within a given date and time range.

SELECT SUBSTR('REBIND PACKAGE('CONCAT COLLID CONCAT'.'
  CONCAT NAME CONCAT'.(*))        ',1,55)
FROM SYSIBM.SYSPACKAGE
WHERE BINDTIME >= 'timestamp1' AND
  BINDTIME <= 'timestamp2';

where timestamp1 and timestamp2 are ISO timestamp strings.

Example: REBIND all invalid versions of all packages.

SELECT SUBSTR('REBIND PACKAGE('CONCAT COLLID CONCAT'.'
  CONCAT NAME CONCAT'.(*))        ',1,55)
FROM SYSIBM.SYSPACKAGE
WHERE VALID = 'N';

Example: REBIND all plans bound with ISOLATION level of cursor stability.

SELECT SUBSTR('REBIND PLAN('CONCAT NAME
  CONCAT')                        ',1,45)
FROM SYSIBM.SYSPLAN
WHERE ISOLATION = 'S';

Example: REBIND all versions of all packages that allow CPU and/or I/O parallelism.

SELECT SUBSTR('REBIND PACKAGE('CONCAT COLLID CONCAT'.'
  CONCAT NAME CONCAT'.(*))        ',1,55)
FROM SYSIBM.SYSPACKAGE
WHERE DEGREE='ANY';