Db2 Tools for z/OS

Customer Questions Answered from March 30th Webcast by Robert Catterall and Bob Bersano

By CALENE JANACEK posted Wed April 07, 2021 05:44 PM


IBM delivered a webcast entitled “Db2 Tools for z/OS: Maximizing usage of Db2 Function Levels and More on Tuesday, March 30th with questions covering both Db2 for z/OS and Db2 Tools for z/OS.  Robert Catterall, an IBM Sr. Db2 for z/OS Consulting Specialist and Bob Bersano, a Rocket Software Principal Solutions Advisor provided the responses to your questions.  A big thanks to Robert and Bob!

  1. Q: With the CREATE/REPLACE command for Stored procedures, when performing a REPLACE, do the privileges associated with the stored procedure remain with a REPLACE?
    A: Yes, If the procedure exists, the procedure is replaced. The existing definition is dropped before the new definition is replaced in the catalog, with the exception that privileges that were granted on the procedure are not affected.

  2. Q: Will "Modifying" a procedure invalidate the dependent packages?
    A: The answer depends on what is being changed for the stored procedure, and how that change is being affected. For an external stored procedure, modifying the procedure will invalidate packages that reference the stored procedure. For a native SQL procedure, keep in mind that a version can be specified for a CREATE OR REPLACE PROCEDURE statement. If CREATE OR REPLACE PROCEDURE replaces the current active version of the procedure in question, it is likely that dependent packages will be invalidated (see the information under the column, "Change requires rebind of invoking applications?" in Table 2 on the Db2 for z/OS documentation page at https://www.ibm.com/docs/en/db2-for-zos/12?topic=statements-alter-procedure-sql-native). If CREATE OR REPLACE PROCEDURE adds a new version of a native SQL procedure, that should have no effect on dependent packages. That added new version can later be made the active version of the procedure. If that is done, the ALTER PROCEDURE with ACTIVATE VERSION would be expected to result in invalidation of dependent packages if the body of the newly activated version were regenerated as a result of the CREATE OR REPLACE PROCEDURE statement that added the new version (see the information in the column, "Change results in implicit regeneration of the entire body of the procedure?' in Table 2 on the documentation page for which the URL was provided, above).

  3. Q: To use the new DB2 features in FL507, do the packages, and plans for the Admin Tool itself need to be bound with APPLCOMPAT(V12R1M507)?
    A: Yes.

  4. Q: Does Db2 AT (Administration Tool) need anything special to use the new DB2 FLs?
    A: Just the PTF that has that FL support, check compatibility matrix.

  5. Q: Can we go from DB2 V12 FL 503 to FL509?
    A: It is possible to successfully activate function level 509 on a Db2 12 for z/OS system for which 503 is the current active function level if 1) the code level of the Db2 system is at least 121509, and if the catalog level of the Db2 system is at least V12R1M509. The code level and catalog level of a Db2 system can be seen in the output of the Db2 12 command -DISPLAY GROUP (in that command output, the Db2 system's code level is shown in the field labeled DB2 LVL).

  6. Q: Does DB2 12 makes use of the new assembler instructions introduced in Z14, Z15?
    A: It likely would if Db2 for z/OS were written in Assembler, which is not the case.

  7. Q: Does FL 508 ALTER TABLESPACE with MOVE OPTION support tables with referential integrity? (that is a limitation on the LUW side)
    A: The Alter TS with Move table will only be performed on  the Table Space selected, if there is RI it will have to also be selected to be moved.

  8. Q: Is it possible to MOVE Multiple tables in a single tablespace to single UTS?

A: You can move a multi-table Table Space to the new UTS format. It will create the tablespaces for each table and then moves each table to its new UTS.

  1. Q: I assume the target of a Redirected Recovery can be treated as a stand-alone object going forward?
    A: Yes, it has no relation to the original source objects.

  2. Q: Will the SUP (March 9th announcement) also include the hold actions to be done for the full PTF list?
    A: The new Db2 for z/OS SUP product base reduces HOLD actions for all included maintenance to less than 80. For Db2 Tools for z/OS, not all Tools were involved in the March 9th announcement.  For the tools in the announcement, it does not include the full list of PTFs, only the ones that were required.  (Link to Announcement Letter)

  3. Q: Any chance of the ADBTEP2 timeout retry in the Db2 Administration Tool being made available in base DB2?
    A: We cannot comment on future Db2 for z/OS enhancements of this nature. A request for such an enhancement in the base Db2 for z/OS product can be submitted through IBM's request for enhancements process (see https://ibm-data-and-ai.ideas.aha.io/?project=DB24ZOS).

  4. Q: How do I forward the Db2 Query Monitor Webcast registration to others?
    A: Thanks for asking.  It’s listed in the Db2 Tools for z/OS Community, both on the main page and in a recent blog.  The Db2 Query Monitor Webcast will take place on Tuesday, April 20th at 11 AM ET.  The URL for registration is https://bit.ly/QM_Apr20

  5. Q: If I download the Db2 Administration Tool V20, does it include most of FL5xx?
    A: It will be current up to the PTF you have downloaded. For example, if you download the Administration Tool PTF for FL507 it will contain everything up to and including support for Db2 FL507.