A fix is available
APAR status
Closed as program error.
Error description
If the customer has views defined on the directory tables, they will end up with orphaned rows in SYSIBM.SYSVIEWS, SYSIBM.SYSVIEWDEP, etc after running the CATMAINT job provided for PM88612.
Local fix
Problem summary
**************************************************************** * USERS AFFECTED: All users that need to fix or add the * * catalog rows updated by APARs PM88612 * * or PI08928 and have views on tables in * * the Directory database DSNDB01. * * * * or * * * * All users that have views on tables in * * the Directory database DSNDB01 and are * * going to go through ENFM for DB2 for * * z/OS V11. * **************************************************************** * PROBLEM DESCRIPTION: A view was created on SYSIBM.SYSUTIL. * * The job * * CATMAINT UPDATE UNLDDN PI08928 * * was run to correct rows in the catalog. * * After the job was run a SQLCODE204 was * * received when an attempt was made to * * drop the view. * **************************************************************** * RECOMMENDATION: * **************************************************************** APARS PM88612 and PI08928 fixed and added rows in the catalog to represent tables in the DIRECTORY database DSNDB01. The changes where made by running the jobs CATMAINT UPDATE UNLDDN PM88612 or CATMAINT UPDATE UNLDDN PI08928 If a view was created on a table in DSNDB01 before one of these jobs was run, the job would remove the row for the view from SYSIBM.SYSTABLES but it would not remove the row for the view from SYSIBM.SYSVIEWS or SYSIBM.SYSVIEWDEP. This would cause an SQLCODE204 if you try to drop the view or an SQLCODE601 when you tried to create the view. This problem can also occur when you go through ENFM for DB2 for z/OS V11.
Problem conclusion
This APAR corrects the problem where views exist on tables in DSNDB01 when a CATMAINT job is run to correct catalog rows. It also makes the corrections fixed by APARS PM88612 and PI08928. A new job is provided to make the corrections and preserve the views: CATMAINT UPDATE UNLDDN PI26218 This CATMAINT supersedes the previous CATMAINT update jobs from APARS PM88612 and PI08928. The code has also been updated to leave the views on tables in DSNDB01 intact when DSNTIJEN is run on DB2 V11. This job can only be run on DB2 V10 NFM or DB2 V11 CM. Applying this APAR does not correct views on tables in DSNDB01 that were broken before the APAR was applied. The orphaned views can exist in any mode. The following query can be used to identify views that are broken. SELECT V.CREATOR AS VCREATOR, V.NAME AS VNAME, V.SEQNO AS VSEQNO, V.TYPE AS VTYPE FROM SYSIBM.SYSVIEWS V WHERE V.TYPE = 'V' AND NOT EXISTS ( SELECT * FROM SYSIBM.SYSTABLES T WHERE T.CREATOR = V.CREATOR AND T.NAME = V.NAME); If this query returns rows, you have one or more broken views. Please contact IBM support for help. ++HOLD for V10 This APAR corrects the problems fixed by APARs PM88612 and PI08928. If the following query returns 0 rows SELECT * FROM SYSIBM.SYSDATABASE WHERE DBID=1; No rows have been added and no further action is required. The job listed below can be used to add the correct rows if needed. Problem 1: This APAR corrects rows for the SYSIBM.SYSLGRNX table in SYSIBM.SYSCOLUMNS table. To see if you have rows that need to be updated run the query: SELECT NAME,FOREIGNKEY FROM SYSIBM.SYSCOLUMNS WHERE NAME='LGRPSID' AND TBNAME = 'SYSLGRNX' AND TBCREATOR = 'SYSIBM' AND FOREIGNKEY = 'S'; If this query returns rows you have rows that need to be updated. To update the rows run the job below. Problem 2: This APAR corrects rows that were inserted into the catalog for the directory objects. To see if you have rows that need to be updated run the query: SELECT NAME,TBNAME,LENGTH(LABEL) FROM SYSIBM.SYSCOLUMNS WHERE TBCREATOR = 'SYSIBM' AND TBNAME IN ('SYSUTIL','DBDR','SCTR','SPTR','SYSLGRNX','SYSUTILX') AND LENGTH(LABEL) <> 0; If this query returns rows you have rows that need to be updated. If there are rows that need to be updated for either problem, they can be corrected by running the job: CATMAINT UPDATE UNLDDN PI26218 This job will invalidate packages that are dependent on the directory tables, delete the catalog rows and then insert the correct rows. This job only needs to be run once and it corrects both problems if needed. It is only needed in V10 NFM. ++HOLD for V11 This APAR corrects the problem fixed by APAR PI08928. If the following query returns 0 rows SELECT * FROM SYSIBM.SYSDATABASE WHERE DBID=1; No rows have been added and no further action is required. The job listed below can be used to add the correct rows if needed. This APAR corrects rows that were inserted into the catalog for the directory objects. To see if you have rows that need to be updated run the query: SELECT NAME,TBNAME,LENGTH(LABEL) FROM SYSIBM.SYSCOLUMNS WHERE TBCREATOR = 'SYSIBM' AND TBNAME IN ('SYSUTIL','DBDR','SCTR','SPTR','SYSLGRNX','SYSUTILX') AND LENGTH(LABEL) <> 0; If this query returns rows you have rows that need to be updated and they can be corrected by running the job: CATMAINT UPDATE UNLDDN PI26218 This job will invalidate packages that are dependent on the directory tables, delete the catalog rows and then insert the correct rows. This job is only needed in V11 CM because the rows are automatically corrected when the system goes through ENFM. KEYWORDS: DB2MIGV11/K DB2MIGV10/K
Temporary fix
Comments
APAR Information
APAR number
PI26218
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
A10
Status
CLOSED PER
PE
YesPE
HIPER
NoHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2014-09-22
Closed date
2014-11-18
Last modified date
2015-01-02
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UI23171 UI23172
Modules/Macros
DSNUECM0 DSNUECSE
Fix information
Fixed component name
DB2 OS/390 & Z/
Fixed component ID
5740XYR00
Applicable component levels
Fix is available
Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"Db2 for z\/OS"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Document Information
Modified date:
30 April 2020