Identifying and resolving outstanding or incomplete in-place alters in versions 7 to 10

Technote (FAQ)

Question

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

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

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 9.4 migration guide has SQL to identify all tables with in place alters (chapter 5, page 5):
  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 ;
  
  Unfortunately, this 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, locks the whole table, takes a long while to run, and reports on many other things as well.
  
  This SQL will report the minimum and maximum version page version for a table (or by default, all tables) with incomplete alters. Dirty reads mean no locks, and it reports which dbspaces are involved too. It runs faster than oncheck -pT.
  

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   
           -- Tblspace tblspace partnums have format 0xnnn00001 
  and a.is_temp = 0 and a.is_blobspace = 0 and a.is_sbspace = 0 
           -- exclude tempspaces, blobspaces and smart blob spaces 
  and p.pg_next != 0 
           -- non zero = altered 
into temp altpts with no log ; 

4. Get the database and table names, 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, 
       -1 as minver, 999 as maxver 
  from altpts a, outer sysmaster:systabnames b, 
    outer sysmaster:systabinfo i 
  where a.partn = b.partnum and a.partn = i.ti_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 minver = ( 
  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  -- data page 
  and sysmaster:bitval(pg_flags, '0x8') <> 1 -- not remainder page 
  and pg_frcnt < (2020 - 4*pg_nslots)        -- with some data on it. 
) where 1 = 1 ; 

update tabvers set maxver = ( 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 ; 

Note the use of 2020 above is based on a dbspace with a 2KB pagesize, less the 24 byte header and 4 byte timestamp. For a 4KB pagesize use 4096 - 28 = 4078, for 8KB pagesize use 8192 - 28 = 8164, and so on. 

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

select database, table, dbspace, nrows, datapages, minver, maxver, 
 decode(maxver - minver, 0, "Maybe Needs Update", "Definitely Needs Update") 
 from tabvers 
order by 1, 2; 

The output of this SQL provides a list of which tablespaces (tables or fragments of tables) where in-place alters exist. If there are some data pages at a lower version from other data pages, then a dummy update is definitely required. If minver = maxver, then if there was an alter table statement run and no data pages have been written to since then (i.e. no inserts, updates, or deletes), then a dummy update may still be required. Prior to IDS version 11, it is not possible to determine from the sysmaster database what the potential maximum version of a data page is for a table. In version 11 there is a new sysmaster table called sysactptnhdr that does give this information. 


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=swg21226410