Sample jobs to grant SELECT access on the Db2 catalog tables

These jobs are provided in HFM.SHFMSAM1 to grant SELECT access on the Db2® catalog tables to PUBLIC:

  1. HFM2GSC0 (Db2 V10)
  2. HFM2GSC1 (Db2 V11)
  3. HFM2GSC2 (Db2 V12)
  4. HFM2GVW0 (Db2 V10)
  5. HFM2GVW1 (Db2 V11)
  6. HFM2GVW2 (Db2 V12)
  7. HFM2GV20 (Db2 V10)
  8. HFM2GV21 (Db2 V11)
  9. HFM2GV22 (Db2 V12)

You should review the GRANT statements in these members whenever you install a later version of Db2. New versions of Db2 may include new or updated catalog tables, and ZDT/Db2 may require access to these new or updated tables.

Sample jobs HFM2GSCn grant SELECT access on the Db2 catalog tables to PUBLIC. This is the simplest method of ensuring ZDT/Db2 users have access to the Db2 catalog tables. It is also the least secure.

Sample jobs HFM2GVWn
  • Create views on the Db2 catalog tables with a different owner, but the same name as the Db2 catalog tables.
  • Grant access on the newly created views to PUBLIC.

If you intend to use these views, you must set the CATOWNER parameter in the HFM2POPI to match the value of the owner specified in these sample jobs.

For information about HFM2POPI, see HFM2POPI. For information about changing the options in HFM2POPT, see Changing the default options.

Sample jobs HFM2GV2n
  • Create views on the Db2 catalog tables with a different owner, but the same name as the Db2 catalog tables.
  • These views include all the Db2 catalog tables normally accessed by ZDT/Db2. Some Db2 catalog tables are not referenced and therefore no views for these Db2 catalog tables are defined.
  • Not every column of the referenced Db2 catalog tables is included in the views. The columns that are included are required for the correct functioning of ZDT/Db2. Omitting additional columns may impact the functioning of ZDT/Db2, in some cases rendering the product unusable.
  • Grant access on the newly created views to PUBLIC.

If you intend to use these views, you must set the CATOWNER parameter in the HFM2POPI to match the value of the owner specified in these sample jobs.

For information about HFM2POPI, see HFM2POPI. For information about changing the options in HFM2POPT, see Changing the default options.

Note:
  1. If you create views of the Db2 catalog tables and change the CATOWNER parameter in the HFM2POPI macro, the following example outlines how ZDT/Db2 attempts to access the Db2 catalog. For this example, it is assumed that the owner of the views (CATOWNER value) is SYSIBMV.
    • ZDT/Db2 generates an SQL statement like SELECT * FROM SYSIBMV.SYSTABLES.
    • ZDT/Db2 attempts to run the SQL statement.
    • If this attempt fails, ZDT/Db2 does NOT attempt to access the Db2 catalog using, for example, SELECT * FROM SYSIBM.SYSTABLES.
    • In summary, ZDT/Db2 attempts to access the Db2 catalog once only, using the specified CATOWNER value as the owner for the relevant catalog tables.
  2. If you do not want to grant SELECT access against the Db2 catalog tables or views to all users (that is, to PUBLIC), you can customize the GRANT statements in the sample jobs to list individual user IDs.
  3. In situations where you do not want to expose all the information in the Db2 catalog tables, you can define views (a "minimal subset") that refer only to the Db2 catalog tables, and columns within those tables, that are needed by ZDT/Db2. Sample jobs that do this are provided in HFM.SHFMSAM1:
    • HFM2GV20 for Db2 version 10 subsystems.
    • HFM2GV21 for Db2 version 11 subsystems.
    • HFM2GV22 for Db2 version 12 subsystems.
  4. The view definitions in these sample jobs refer only to the columns needed by ZDT/Db2, and not all catalog tables are included. There are consequences to using views that include only some columns of the actual Db2 catalog table. For example, option 3.4 of ZDT/Db2 provides an option to show all columns of certain catalog tables. The columns shown will reflect the columns of the view and not necessarily all columns of the catalog table, reducing the usefulness of the product.
  5. If your installation security standards prevent access to the SYSIBM.*AUTH tables, you can omit these views, or create the views, and then not grant access. This has the effect of disabling the Object Privileges utility (Option 3.5).
  6. You can define different views for different Db2 subsystems. For example, you could define the views in HFM2GVW0 against a development system, but the views in HFM2GV20 against a production system. You must ensure, however, that the owner (CATOWNER value) used is consistent across all Db2 subsystems accessed by ZDT/Db2.
  7. When you define views on the Db2 catalog table for use by ZDT/Db2, you cannot change the object name of the catalog table; you can only change the owner. For example, defining a view SYSIBMV.SYSTABLES on the table SYSIBM.SYSTABLES is valid. However, defining a view SYSIBM.TABLES on the table SYSIBM.SYSTABLES is invalid. You cannot rename the columns of the catalog tables in the views you define for use by ZDT/Db2. The owner (CATOWNER value) of the views you create must be the same for every view.