Modify or delete RC/Extract definitions created by an obsolete user

Products

• RC/Extract for DB2 for z/OS

Issue/Introduction

Unable to modify or delete RC/Extract definitions created by an obsolete user when the Share Option is 'Y' or 'N'.

Resolution

The following SQL can be used to update the Share Option to 'U' for definitions created by an obsolete user, so that other users can then modify/delete them through the panels.

-- Update Share option for source definition (change <userid> and <srcname>) 
UPDATE PTI.PTRCX_SRCDEF_REG B                              
  SET B.DATA =                                             
     (SELECT SUBSTR(DATA,1,66) CONCAT 'U' CONCAT           
       SUBSTR(DATA,68,LENGTH(DATA)-67)                     
       FROM PTI.PTRCX_SRCDEF_REG                           
       WHERE SRCDEF_CREATOR = 'USERID' AND                 
             SRCDEF_NAME = 'SRCNAME' AND INTERNAL_TYPE = 1)
 WHERE B.SRCDEF_NAME = 'SRCNAME' AND                       
       B.SRCDEF_CREATOR = 'USERID' AND                     
       B.INTERNAL_TYPE = 1;                                  
 
-- Update Share option for target definition (change <userid> and <trgname>) 
UPDATE PTI.PTRCX_TRGDEF_REG B                               
  SET B.DATA =                                              
     (SELECT SUBSTR(DATA,1,110) CONCAT 'U' CONCAT            
       SUBSTR(DATA,112,LENGTH(DATA)-111)                      
       FROM PTI.PTRCX_TRGDEF_REG                            
       WHERE TRGDEF_CREATOR = 'userid' AND                 
             TRGDEF_NAME = 'srcname' AND INTERNAL_TYPE = 1)
 WHERE B.TRGDEF_NAME = 'trgname' AND                       
       B.TRGDEF_CREATOR = 'userid' AND                     
       B.INTERNAL_TYPE = 1;                                     
       
 -- Update Share option for Extended extract definition (change <userid> and <mdxname>) 
UPDATE PTI.PTRCX_MDX_REGISTRY B                               
  SET B.MDX_DATA =         
     (SELECT SUBSTR(MDX_DATA,1,66) CONCAT 'U' CONCAT            
       SUBSTR(MDX_DATA,68,LENGTH(MDX_DATA)-67)                      
       FROM PTI.PTRCX_MDX_REGISTRY                            
       WHERE MDX_CREATOR = 'userid' AND                 
             MDX_NAME = 'mdxname' AND MDX_INTERNAL_TYPE = 1)
 WHERE B.MDX_NAME = 'mdxname' AND                       
       B.MDX_CREATOR = 'userid' AND                     
       B.MDX_INTERNAL_TYPE = 1;