Re: Re: finding inplace alters
Thread about detecting outstanding in-place alters (IPAs) in Informix. Fernando Nunes suggests an SQL approach using sysmaster's syssltdat (one 't') to read slot-6 extended partition pages, noting it's absent in 7.31 but reportedly present in 7.31.UD7, and promises follow-up. Davorin Kremenjas posts a working IBM-derived script set: find partitions with pg_next != 0, compute min/max page versions, list tables needing updates, then dummy-update rows (optionally via a generated SPL doing batched commits) to complete the alters. Superboer notes the index-column query should wrap sysindexes partX in ABS() to catch descending keys, which Davorin accepts; he also suggests alter fragment ... init or a clustered index to force a full table rewrite instead. Discussion ends with general DBA advice (locking, logs, long transactions) rather than a single definitive tool.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I made a mistake on the table name. It's "syssltdat" (just one "t" at the end). I've already received a report (directly to me - Thanks!) which states that it's present in 7.31.UD7. This is consistent to some searches I've made that returned very old cases where it's mentioned. So, in short, I believe it's possible to do. I'll try to work on it, and I'll update the list when I have any more info. Thanks! On Wed, Dec 22, 2010 at 7:35 AM, Weitkamp Heinz < Heinz.Weitkamp@westfleisch.de> wrote: > Hallo, > > in Version 7.31 the table syssltdatt is not present in sysmaster. > > Greetings > Heinz > > > >>> Fernando Nunes <domusonline@gmail.com> 22.12.2010 01:47 >>> > On Tue, Dec 21, 2010 at 6:37 PM, Mark Scranton > <mark@markscranton.com>wrote: > > > On Dec 20, 4:22 pm, PeterP <peterp...@gmail.com> wrote: > > > On Dec 18, 10:52 pm, Art Kagel <art.ka...@gmail.com> wrote: > > > > > > > The IPA reversion problem is fixed as of 11.70. No longer a > problem. > > > > > > > Art > > > > > > > Art S. Kagel > > > > Advanced DataTools (www.advancedatatools.com) > > > > IIUG Board of Directors (a...@iiug.org) > > > > Blog:http://informix-myview.blogspot.com/ > > > > > > I thought it was fixed in 11.50 with IPA pages now being treated > > > differently since 11.50 or was it just the rollback code for > revision. > > > I can't easily dd out the partition pages at the moment and guess > / > > > work out what's changed in the structures. > > > 11.70 just tells you how many pages have IPA for performance I > > > thought. Got any links ..? > > > > > > Still there are IPA bugs for things like compression etc. > > > Generally I don't worry about them (unless you have almost 255) > but > > > everytime a table has a problem it's another thing to consider, > > > especially with fragmented. > > > > > > You would think there would be a tbl_IPA_cleaner thread or at > least > > > some kind of IPA admin fix task. > > > Maybe a environment variable like NOFUZZYCKPT (strangely similar > to > > > IPA) NOIPA for when you DDL small tables. > > > Anyone else had sleepless nights over damn IPA's with their > associated > > > LTX's. > > > Yeah, Onion was the tool I was thinking of. )) > > > > I've had a script forever that identifies IPA's, but am certain it > > need tweaked to be complete - even after you resolve the IPAs, the > > script will still identify them. I believe that Jonathon (Leffler) > > actually modified the code to take care of this issue. I will post > it > > here in a few minutes. For the record, what you're looking for are > > "slot 6 pages", or as Andreas said, "secondary partition pages." > > Traditionally, each partition has a single partition page that has 5 > > slots. With an IPA there is another page, the slot 6 page. My > biggest > > usage of the IPA script was in fact the reversion issue, followed by > > any potential performance concerns. As Art mentioned, the reversion > > issue was fixed in 11.7 which is a huge and important fix. For those > > of you not on 11.7 (many I would think for now), word on the street > > was always "you can't upgrade if there are IPAs." That was always a > > myth - you can't revert (easily) if there are IPAs. I used to > mention > > this "all over the world", and for the longest time very few people > > ever believed it. If they ever "hit it", then they believed it. ;) > > > > Thanks - > > Mark Scranton > > The Mark Scranton Group,LLC > > _______________________________________________ > > Informix-list mailing list > > Informix-list@iiug.org > > http://www.iiug.org/mailman/listinfo/informix-list > > > > > I believe it's possible to get the pending in place alters using SQL. > But it > needs syssltdat and I'm not sure when it was introduced. > And yes, as Andreas wrote this requires some knowledge about the > internal > structures... but then again... You can't get more internal than Onion > :) > > We can get the extending partition pages from sysmaster, and then get > the > slot 6 data from them and interpret it to get the number of pages in > each > version. > If any returns != 0 we have a pending inplace alter. > This should run fairly quickly but I'd need to check this... > > If anybody has a V10 or V9 or v7 at hand I'd like to know if syssltdatt > is > present in sysmaster > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > -- > WESTFLEISCH eG * Hauptsitz: Brockhoffstr. 11, 48143 Münster > Amtsgericht Münster: Gen.-Reg. 307 > > Aufsichtsratsvorsitzender: Heinz Westkämper > Vorstand: Dirk Niederstucke, Peter Piekenbrock, Josef Lehmenküh > ler, Dr. > Bernd Cordes, Dr. Helfried Giesen > > Hinweise: Es können nur Mails bis 30 MB empfangen werden. > PowerPoint-Dateien müssen in eine ZIP-Datei gepackt werden. > -------------------------------------------------- > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...
On Dec 22, 1:41 pm, Fernando Nunes <domusonl...@gmail.com> wrote:
> So, in short, I believe it's possible to do. I'll try to work on it, and
> I'll update the list when I have any more info.
Until Fernando or anyone else posts the optimal solution, this is what
worked for me before, an IBM script expanded with few queries to make
it a bit more automatic, in case anyone finds it helpful.
{
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, 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
and sysmaster:bitval(pg_flags, '0x8') <> 1
and pg_frcnt < (pg_pagesize-28 - 4*pg_nslots)
) 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 < (pg_pagesize-28 - 4*pg_nslots
-- Scan all data pages for each altered tablespace
-- TODO: use syspaghdr to isolate only the pages which really need to
be dummy updated
select pg_partnum, pg_pagenum, pg_nslots, pg_next
from sysmaster:syspaghdr p, tabvers a
where p.pg_partnum = a.partnum
and p.pg_next < a.maxver
into temp pagevers with no log;
-- Display the tablespaces which require updating to complete the in-
place alters.
select database, table, dbspace, nrows, datapages, minver, maxver,
decode(maxver - minver, 0, "OK", "Needs Update")
from tabvers
where (maxver - minver) != 0
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.
-- Realistically this can only be run for small, not frequently
accessed tables
update tabname set some_nonPK_column = some_nonPK_column where 1 = 1 ;
{
For larger tables, exclusive lock on the table will prevent excessing
locking. If locking the table in exclusive mode is not an option,
then rows need to be updated in batches in the stored proc. For each
table stored proc need to be generated
on version 10 and below. On versions 11+ with dynamic SQL in stored
procs this boils down to just one SPL across the whole instance.
TODO: add logic to track the last updated (and committed) value in an
auxilliary table
}
{
The query to generate the stored proc for each table separately for
IDS<=v10.
Configrable param is the number of rows committed in one transaction,
10000 by default.
Returns the first column which has no index built on it (this will
also avoid updating the primary key) to avoid index page locking.
}
select t.tabid, MIN(c.colno) as mincolno
from syscolumns c, systables t, tabvers v
where c.tabid = t.tabid
and t.tabname = v.table
-- avoid serials
AND c.coltype not in (6, 18, 262, 274)
and NOT EXISTS
(select *
from sysindexes i
where i.tabid = c.tabid
and (c.colno = i.part1 OR c.colno = i.part2 OR c.colno =i.part3 OR c.colno = i.part4 OR c.colno = i.part5 OR c.colno = i.part6
OR c.colno = i.part7 O
R c.colno = i.part8 OR
c.colno = i.part9 OR c.colno = i.part10 OR c.colno = i.part11
OR c.colno = i.part12 OR c.colno = i.part13 OR c.colno = i.part14 OR
c.colno = i.part15
OR c.colno = i.part16)
)
group by 1
into temp mincols;
unload to /tmp/ipatest.sql delimiter " "select "create procedure pSafeDummyUpdate_" || trim(t.tabname) ||
"(v_commit_count int) returning int8;" ||
" define p_count int8; define p_count_total int8; define p_dummy like
" || trim(tabname) || "." || trim(c.colname) || ";" ||
" set isolation to committed read; set lock mode to wait 30; let
p_count = 0; let p_count_total = 0;" ||
" foreach c_curs with hold for select " || trim (c.colname) || " into
p_dummy from " || trim (t.tabname) ||
" if p_count = 0 then begin work; end if update " || trim(t.tabname)
|| " set " || trim(c.colname) || " = " || trim(c.colname) ||
" where current of c_curs; let p_count = p_count + 1; let
p_count_total = p_count_total + 1; " ||
" if p_count = v_commit_count then commit work; let p_count = 0; end
if end foreach;" ||
" if p_count != 0 then commit work; end if return p_count_total; end
procedure;" ||
" execute procedure pSafeDummyUpdate_" || trim(t.tabname) ||
"(10000);" ||
" drop procedure pSafeDummyUpdate_" || trim(t.tabname) || "(int);"
from mincols v, systables t, syscolumns c
where v.tabid = t.tabid
and t.tabid = c.tabid
and c.colno = v.mincolno
-- if there are tables with all of their columns included in indexes,
print them out and decide manually which column to update
!echo "tables with all columns indexed!"
select table from ipatemp where table not in (select tabname fromsystables where tabid in (select tabid from mincols));
Hello Davorin,
to be honest i havenot looked at it closely; however:
i would (found this in the past once in this newsgroup....) :
put ABS around partX since you may miss indexes like:
create index ixie1 on customer ( customer_num, lname desc);
select * from sysindexes where idxname ='ixie1 ';
idxname ixie1
owner informix
tabid 100
idxtype D
clustered
part1 1
part2 -3 <<<<----
part3 0
So:
(c.colno = ABS(i.part1) OR c.colno = ABS(i.part2) OR c.colno =
ABS(i.part3) OR c.colno = ABS(i.part4)
OR c.colno = ABS(i.part5) OR c.colno = ABS(i.part6)
OR c.colno = ABS(i.part7) OR c.colno = ABS(i.part8) OR
c.colno = ABS(i.part9) OR c.colno = ABS(i.part10) OR c.colno
= ABS(i.part11)
OR c.colno = ABS(i.part12) OR c.colno = ABS(i.part13) OR c.colno =
ABS(i.part14) OR
c.colno = ABS(i.part15)
OR c.colno = ABS(i.part16))
Superboer.
On 22 dec, 23:26, Davorin Kremenjas <davorin.kremen...@gmail.com>
wrote:
> On Dec 22, 1:41 pm, Fernando Nunes <domusonl...@gmail.com> wrote:
>
> > So, in short, I believe it's possible to do. I'll try to work on it, and
> > I'll update the list when I have any more info.
>
> Until Fernando or anyone else posts the optimal solution, this is what
> worked for me before, an IBM script expanded with few queries to make
> it a bit more automatic, in case anyone finds it helpful.
>
> {
> 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, 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
> and sysmaster:bitval(pg_flags, '0x8') <> 1
> and pg_frcnt < (pg_pagesize-28 - 4*pg_nslots)
> ) 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 < (pg_pagesize-28 - 4*pg_nslots>
> -- Scan all data pages for each altered tablespace
> -- TODO: use syspaghdr to isolate only the pages which really need to
> be dummy updated
>
> select pg_partnum, pg_pagenum, pg_nslots, pg_next
> from sysmaster:syspaghdr p, tabvers a
> where p.pg_partnum = a.partnum
> and p.pg_next < a.maxver
> into temp pagevers with no log;>
> -- Display the tablespaces which require updating to complete the in-
> place alters.
>
> select database, table, dbspace, nrows, datapages, minver, maxver,
> decode(maxver - minver, 0, "OK", "Needs Update")
> from tabvers
> where (maxver - minver) != 0
> 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.
> -- Realistically this can only be run for small, not frequently
> accessed tables
>
> update tabname set some_nonPK_column = some_nonPK_column where 1 = 1 ;>
> {
> For larger tables, exclusive lock on the table will prevent excessing
> locking. If locking the table in exclusive mode is not an option,
> then rows need to be updated in batches in the stored proc. For each
> table stored proc need to be generated
> on version 10 and below. On versions 11+ with dynamic SQL in stored
> procs this boils down to just one SPL across the whole instance.
> TODO: add logic to track the last updated (and committed) value in an
> auxilliary table
>
> }
>
> {
> The query to generate the stored proc for each table separately for
> IDS<=v10.
> Configrable param is the number of rows committed in one transaction,
> 10000 by default.
> Returns the first column which has no index built on it (this will
> also avoid updating the primary key) to avoid index page locking.
>
> }
>
> select t.tabid, MIN(c.colno) as mincolno
> from syscolumns c, systables t, tabvers v
> where c.tabid = t.tabid
> and t.tabname = v.table
> -- avoid serials
> AND c.coltype not in (6, 18, 262, 274)
> and NOT EXISTS
> (select *
> from sysindexes i
> where i.tabid = c.tabid
> and (c.colno = i.part1 OR c.colno = i.part2 OR c.colno => i.part3 OR c.colno = i.part4 OR c.colno = i.part5 OR c.colno = i.part6
> OR c.colno = i.part7 O
> R c.colno = i.part8 OR
> c.colno = i.part9 OR c.colno = i.part10 OR c.colno = i.part11
> OR c.colno = i.part12 OR c.colno = i.part13 OR c.colno = i.part14 OR
> c.colno = i.part15
> OR c.colno = i.part16)
> )
> group by 1
> into temp mincols;
>
> unload to /tmp/ipatest.sql delimiter " "> select "create procedure pSafeDummyUpdate_" || trim(t.tabname) ||
> "(v_commit_count int) returning int8;" ||
> " define p_count int8; define p_count_total int8; define p_dummy like
> " || trim(tabname) || "." || trim(c.colname) || ";" ||
> " set isolation to committed read; set lock mode to wait 30; let
> p_count = 0; let p_count_total = 0;" ||
> " foreach c_curs with hold for select " || trim (c.colname) || " into
> p_dummy from " || trim (t.tabname) ||
> " if p_count = 0 then begin work; end if update " || trim(t.tabname)
> || " set " || trim(c.colname) || " = " || trim(c.colname) ||
> " where current of c_curs; let p_count = p_count + 1; let
> p_count_total = p_count_total + 1; " ||
> " if p_count = v_commit_count then commit work; let p_count = 0; end
> if end foreach;" ||
> " if p_count != 0 then commit work; end if return p_count_total; end
> procedure;" ||
> " execute procedure pSafeDummyUpdate_" || trim(t.tabname) ||
> "(10000);" ||
> " drop procedure pSafeDummyUpdate_" || trim(t.tabname) || "(int);"
> from mincols v, systables t, syscolumns c
> where v.tabid = t.tabid
> and t.tabid = c.tabid
> and c
On Dec 23, 10:01 am, Superboer <superbo...@t-online.de> wrote:
> Hello Davorin,
> to be honest i havenot looked at it closely; however:
> i would (found this in the past once in this newsgroup....) :
> put ABS around partX since you may miss indexes like:
> create index ixie1 on customer ( customer_num, lname desc);
Hi Superboer,
thanks, good point, I forgot about the descending keys.
On Dec 26, 11:32 am, Davorin Kremenjas <davorin.kremen...@gmail.com>
wrote:
> On Dec 23, 10:01 am, Superboer <superbo...@t-online.de> wrote:
>
> > Hello Davorin,
> > to be honest i havenot looked at it closely; however:
> > i would (found this in the past once in this newsgroup....) :
> > put ABS around partX since you may miss indexes like:
> > create index ixie1 on customer ( customer_num, lname desc);>
> Hi Superboer,
>
> thanks, good point, I forgot about the descending keys.
Getting OFF Topic a little but.
It's annoying how we all know so much about IPA. Really, you would
think it would be some feature of Informix. I'm sure there are other
pet DBA peeves.
A good idea for a paper at the IIUG thing?
How to Informix DBA tasks for people that didn't write up the list of
problems you hit last time (because you were busy fixing them) and
need to adapt an old script for a different environment.
Someone probably did something like this before, other than the
Informix FAQ.
e.g. IPA
Different ways of doing it rather than just the SQL which helps but
really it's all the other things you need to think about that make you
a DBA... all in one go with locking tables, over several nights,
several tables at a time and lots of log space. Parallel - kicking off
several processes / anything else that might make it go faster -
aligning buffer sizes to the same as the san (can't remember if this
is only for HPL). you get the idea.
and the gotchas
Make sure the backup administrator knows you are going to write 50mb
of log space so his pool doesn't fill up or the unix team don't get
paged at 3am :) Also, is the backup going to get bigger.
look out for running out of locks or even memory!
make sure you have lots of logs and don't forget about long
transactions or changing LTX / disabling logging etc.
lock the table in exclusive mode.
use a stored procedure with a primary key for updates.
i seem to remember disabling most indexes also helps a bit, can't
remember if it was for IPA's or just table updates, but hoping I'm
making my point.
Yeah, your script needs to think of all these things and probably a
few more I've forgotten as well - suffering with the old h1n1 atm.
Another good talk would be a how to on removing duplicates from
tables. Similar thoughts on how you need to do things on large
databases.
I know it's part of IBM / Informix employees PBC's to write these mini
tech talk things and I think they end up on developerworks if not in
iiug presentations, but it would be good if there was a nice lazy dba
index to it all and more how to in the real world with a million rows
rather than just a script.
Hello Peter,
if you do not want to have an inplace alter table you can always run
something like
alter fragment on table..... init in.... after you have changed the
table, or create a clusterd index on the table...which makes the whole table to be rewritten.
(you may need to change the logging mode to unlogged if the table is
too big for the trx log...)
Working with other products tells me however with informix you are in
heaven, with other stuff
(obstacle etc) you are in hell and that is my opinion.
Superboer.
BTW don't have to do paper work business commitment since i am not ibm/
informix.
On 26 dec, 23:09, PeterP <peterp...@gmail.com> wrote:
> On Dec 26, 11:32 am, Davorin Kremenjas <davorin.kremen...@gmail.com>
> wrote:
>
> > On Dec 23, 10:01 am, Superboer <superbo...@t-online.de> wrote:
>
> > > Hello Davorin,
> > > to be honest i havenot looked at it closely; however:
> > > i would (found this in the past once in this newsgroup....) :
> > > put ABS around partX since you may miss indexes like:
> > > create index ixie1 on customer ( customer_num, lname desc);>
> > Hi Superboer,
>
> > thanks, good point, I forgot about the descending keys.
>
> Getting OFF Topic a little but.
> It's annoying how we all know so much about IPA. Really, you would
> think it would be some feature of Informix. I'm sure there are other
> pet DBA peeves.
>
> A good idea for a paper at the IIUG thing?
>
> How to Informix DBA tasks for people that didn't write up the list of
> problems you hit last time (because you were busy fixing them) and
> need to adapt an old script for a different environment.
> Someone probably did something like this before, other than the
> Informix FAQ.
>
> e.g. IPA
> Different ways of doing it rather than just the SQL which helps but
> really it's all the other things you need to think about that make you
> a DBA... all in one go with locking tables, over several nights,
> several tables at a time and lots of log space. Parallel - kicking off
> several processes / anything else that might make it go faster -
> aligning buffer sizes to the same as the san (can't remember if this
> is only for HPL). you get the idea.
>
> and the gotchas
> Make sure the backup administrator knows you are going to write 50mb
> of log space so his pool doesn't fill up or the unix team don't get
> paged at 3am :) Also, is the backup going to get bigger.
> look out for running out of locks or even memory!
> make sure you have lots of logs and don't forget about long
> transactions or changing LTX / disabling logging etc.
> lock the table in exclusive mode.
> use a stored procedure with a primary key for updates.
> i seem to remember disabling most indexes also helps a bit, can't
> remember if it was for IPA's or just table updates, but hoping I'm
> making my point.
> Yeah, your script needs to think of all these things and probably a
> few more I've forgotten as well - suffering with the old h1n1 atm.
>
> Another good talk would be a how to on removing duplicates from
> tables. Similar thoughts on how you need to do things on large
> databases.
>
> I know it's part of IBM / Informix employees PBC's to write these mini
> tech talk things and I think they end up on developerworks if not in
> iiug presentations, but it would be good if there was a nice lazy dba
> index to it all and more how to in the real world with a million rows
> rather than just a script.