IBM Support

PI26218: ORPHANED ROWS IN SYSIBM.SYSVIEWS, SYSIBM.SYSVIEWDEP, ETC AFTER RUNNING THE CATMAINT JOB PROVIDED FOR PM88612 OR PI08928

A fix is available

Subscribe

You can track all active APARs for this component.

 

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

  • RA10 PSY UI23171

       UP14/12/03 P F412 Ø

  • RB10 PSY UI23172

       UP14/12/03 P F412 Ø

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