Migration step 27: Verify views

During migration to DB2® 10 conversion mode (from Version 8 or Version 9), some views might be marked with view regeneration errors. If any views are marked with view regeneration errors, you can regenerate those views again.

To verify views:

2. Issue the following query to determine which views were marked with view regeneration errors during migration:

   SELECT CREATOR,NAME
   FROM SYSIBM.SYSTABLES
   WHERE TYPE = 'V'
   AND STATUS = 'R'
   AND TABLESTATUS = 'V'

3. If any views have view regeneration errors, issue the following ALTER VIEW statement:

   ALTER VIEW view REGENERATE

   where view is the name of the view with regeneration errors.

   You can use the DSNTIAUL program to generate the ALTER VIEW statement as follows:

   

   // EXEC PGM=IKJEFT01 
   //SYSTSPRT DD  SYSOUT=* 
   //SYSTSIN  DD *   
     DSN SYSTEM(dsn)
       RUN PROGRAM(DSNTIAUL) PLAN(DSNTIB10) + 
         LIB('prefix.RUNLIB.LOAD') PARMS('SQL')
     END
   /*
   //SYSPRINT DD  SYSOUT=*                                
   //SYSIN    DD  *                                       
     SELECT SUBSTR(                                 
       'ALTER VIEW '                                
       || STRIP(CREATOR) || '.' || NAME             
       || ' REGENERATE ;'                        
       || '                                        '
       || '                                        '
       ,1,80)                                       
       FROM SYSIBM.SYSTABLES                      
       WHERE TYPE = 'V'                        
       AND STATUS = 'R'
       AND TABLESTATUS = 'V'
   
     ;                                              
   /*                                                     
   //SYSPUNCH DD  DUMMY                                   
   //SYSREC00 DD  DISP=(NEW,CATLG,DELETE),                
   //             DSN=&SYSUID.SQLTIAUL.ALTVIEWS,
   //             UNIT=SYSDA,SPACE=(TRK,(750,75),RLSE)