Identifying and resolving outstanding in-place alters in version 11 and later.

Technote (FAQ)

Question

How do you identify outstanding in-place alters in order to resolve them?

These are sometimes call incomplete, or "unrealised" in-place alters.

This document is specifically for IDS version 11 and later. For IDS versions 7 to 10,
see http://www-01.ibm.com/support/docview.wss?uid=swg21226410

Cause

Since version 7.24, some ALTER TABLE statements have been done as "fast alters", where rows are only altered to fit the new schema when the data page they are on is next written to. If there are many alters done, this can lead to the data pages for a table being a variety of versions. When migrating to a later version (or just for the performance benefit) it is a good idea to "complete" the outstanding in-place alters.

Answer

The first step is to work out which tables have data pages at old versions:

There are several documented ways to determine altered tables, including:

• The migration guide has SQL to identify all tables with in place alters (chapter 3, page 52). However, some versions of the manual have SQL like this:
  
  -- first set OPTCOMPIND to 0 in onconfig and restart instance 
    set isolation to dirty read; 
  
  select pg_partnum + pg_pagenum - 1 partn 
      from sysmaster:syspaghdr, sysmaster:sysdbspaces a 
     where pg_partnum = 1048576 * a.dbsnum + 1 
       and pg_next != 0 
      into temp pp with no log ; 
  
  select b.dbsname database, b.tabname table 
      from sysmaster:systabnames b, pp 
     where partn = partnum ; 
  
  which shows all tables that ever had an in-place or fast alter, and does not exclude those even where every data page has been rewritten (and therefore updated) since the ALTER TABLE was run.

• The oncheck -pT utility prints a table showing all current versions on data pages, and page counts for each version, but it runs serially, locking the whole table, and takes a long while to run because it reports on many other things as well.

As these methods have drawbacks, the following steps can be used to obtain a more specific list of outstanding in-place alters: 

1. Set OPTCOMPIND to 0 in the onCONFIG configuration file and restart the instance. 

2. Set isolation to dirty read. 

3. Find every partnum in the tblspace tbspace of each non-temp dbspace where pg_next (pointer to extended partition page) is set to something other than zero. These tablespaces have been altered, and pg_next points to the pnvers (partition version) page. 

select (p.pg_partnum + p.pg_pagenum - 1) as partn, a.name as dbspace 
 from sysmaster:syspaghdr p, sysmaster:sysdbspaces a 
 where p.pg_partnum = 1048576 * a.dbsnum + 1 
  and a.is_temp = 0 and a.is_blobspace = 0 and a.is_sbspace = 0 
  and p.pg_next != 0 
into temp altpts with no log ; 


4. Get the database and table names, minimum and maximum possible page versions, plus row and data page numbers for all those partnums. 

  select b.dbsname database, b.tabname table, a.dbspace dbspace, 
          hex(a.partn) partnum, i.ti_nrows nrows, i.ti_npdata datapages, 
          p.pta_oldvers as oldvers, p.pta_newvers as newvers, 
          -1 as mindver, 999 as maxdver 
  from altpts a, outer sysmaster:sysactptnhdr p, 
       outer sysmaster:systabnames b, outer sysmaster:systabinfo i 
  where a.partn = b.partnum 
    and a.partn = i.ti_partnum 
    and a.partn = p.partnum 
   into temp tabvers with no log ; 


5. Scan all non-empty home data pages for each altered tablespace and get the 
minimum and maximum versions: 

  update tabvers set mindver = 
        ( select (min(pg_next)) / 16777216 
            from sysmaster:syspaghdr p, altpts a 
           where p.pg_partnum = a.partn 
             and tabvers.partnum = a.partn 
             and sysmaster:bitval(pg_flags, '0x1') = 1   
             and sysmaster:bitval(pg_flags, '0x8') <> 1 
             and pg_frcnt < (2020 - 4*pg_nslots) 
        ) 
   where 1 = 1 ; 

  update tabvers set maxdver =   
        ( select (max(pg_next)) / 16777216 
            from sysmaster:syspaghdr p, altpts a 
           where p.pg_partnum = a.partn 
             and tabvers.partnum = a.partn 
             and sysmaster:bitval(pg_flags, '0x1') = 1 
             and sysmaster:bitval(pg_flags, '0x8') <> 1 
             and pg_frcnt < (2020 - 4*pg_nslots) 
        )   
    where 1 = 1 ; 


6. Display the tablespaces which require updating to complete the in-place alters: 

   select database, table, dbspace, nrows, datapages, 
          oldvers as oldestv, mindver, maxdver, newvers as newestv 
     from tabvers 
    where newvers > mindver 
    order by 1, 2; 


The second step is to do a dummy update on at least one row on each of those pages not updated to the current version. 

For all tables which have been altered, but are not yet "complete" , you can run a "dummy update" statement, setting the primary key column to itself (or other "change" that changes nothing). This will bring all of the pages to the most current version: 

update TABNAME set PKEY = PKEY where 1 = 1 ; 

http://www-01.ibm.com/support/docview.wss?uid=swg21389693