finding inplace alters
Floyd Wellershaus
Running ids10.0.fc5 on aix5.3 and needing to upgrade to 10.0.fc11 to get a bugfix.
trying to find and fix inplace alters. The script ibm supplies doesn't work for mulitple reasons so far. It fails on some oncheck -pp's and also when it builds a list of tables out of our 60k plus tables, it runs out of memory.
So I am trying to use this that i got from somewhere a while ago, but I guess it's not reliable because even after I do the dummy updates the tables still show up.
Any idea of a decent way to at least find the inplace atlers on a huge ass database with over 60,000 tables some of them containing billions of rows ?
Thanks,
floyd
database sysmaster;
set isolation to dirty read;
select pg_partnum + pg_pagenum - 1 partn from syspaghdr, sysdbspaces a where pg_partnum = 1048576 * a.dbsnum + 1 and pg_next!=0 into temp pp with no log;
unload to altered_tabs
select b.dbsname database, b.tabname table from systabnames b, pp where partn = partnum;
Koya
Yes - there is a problem in the IBM script where it is getting the
version and count. I fixed it. Will send the script in email. See if
that works.
--Chavan
Fernando Nunes
Hi,
Running ids10.0.fc5 on aix5.3 and needing to upgrade to 10.0.fc11 to get a bugfix.
trying to find and fix inplace alters. The script ibm supplies doesn't work for mulitple reasons so far. It fails on some oncheck -pp's and also when it builds a list of tables out of our 60k plus tables, it runs out of memory.
So I am trying to use this that i got from somewhere a while ago, but I guess it's not reliable because even after I do the dummy updates the tables still show up.
Any idea of a decent way to at least find the inplace atlers on a huge ass database with over 60,000 tables some of them containing billions of rows ?
Thanks,
floyd
database sysmaster;
set isolation to dirty read;
select pg_partnum + pg_pagenum - 1 partn from syspaghdr, sysdbspaces a where pg_partnum = 1048576 * a.dbsnum + 1 and pg_next!=0 into temp pp with no log;
unload to altered_tabs
select b.dbsname database, b.tabname table from systabnames b, pp where partn = partnum;
This will tell you all the tables that had suffer an inplace alter table. What you want is the list of tables that have what is usually called "pending" inplace alters...
If the number is relatively low, and the dummy updates run quickly, you should be fine.
The script above will always give you the same set of tables (unless you recreate them).
AFAIK there is no quick way to find pending inplace alters... Only a slow process. Not sure if Panther changed this...
Also note: If I'm not mistaken, the issue with pending inplace alters only happens when you try to revert. The migration should work fine with pending inplace alters. Please confirm with the migration guide or tech support.
Regards.
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
Art Kagel
That said, since you have much more time BEFORE an upgrade than you normally do if the upgrade results in some performance problems or bugs that force an emergency reversion so I always recommend that one complete all pending in-place alters before attempting the upgrade.
Also, the OP is using Informix 10.00, but users of later versions of 11.50 and of 11.70 no longer have to worry about in-place alters as reversions within the ranges of those versions will complete even with pending in-place alters outstanding.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (a...@iiug.org)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.
_______________________________________________
Informix-list mailing list
Inform...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
Art Kagel
The only problem with waiting until the possible time of reversion to eliminate the pending alters is that at that point you are usually in crunch mode and downtime is a problem. Most people say "But I have downtime scheduled for the upgrade and there's lots of slack in there in case I have to revert!" The problem that they are missing is that it is REALLY rare for the upgrade itself to fail (and if it does a restore is the more likely required way to repair the damage) the more likely scenario is that the upgrade will succeed but after the upgrade, once everything is back online and users are using the system you will discover some serious performance issue or a deal breaking bug that you hadn't found during your testing of the new version in QA beforehand. At that point, users are complaining to support, the head of support has camped out behind your chair, your boss is calling every few minutes to ask when the system will be back, and you are pulling out whatever little hair you have left after the last big problem. That's not the time to begin a multiple hour long update or unload to eliminate the barriers to reversion. You want to have done that during the several days or weeks prior to the upgrade.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (a...@iiug.org)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.
Well Art, i guess you verified what Fernando said. Thank you.
So even if one table has a pending IPA, the whole reversion would fail ?
I'm wondering, if the reversion fails and complains about a specific table, if one could export that table in the upgraded version, then drop it, then revert successfully then import the table in the reverted version. Is that a possibility ?
Thanks !
Floyd
Floyd Wellershaus
ha.
Yea I hear you totally.
Thanks again !
From: art....@gmail.com
Sent: Thu, November 18, 2010, 6:48 AM
Subject: Re: finding inplace alters
Yes, Floyd. If there is even a single table that has pending alters the reversion will fail. Well, it's more like it will refuse to start. If that happens, any way that you can eliminate the pending alters is OK. So unloading the troublesome tables and reloading (either before or after the reversion) is OK.
PeterP
I should jump on the forums more often... sorrt for the late reply but
IPA is interesting especially as everyone has to do it.
If it's huge arse DB I'm interested to know what you did in the end
for next time I likely need to address.
IPA really is no fun, it's tempting to throw it at the down systems
group if it fails because of such a dumb design by Informix, although
the reason for the reversion problem is quite a tricky one. I think
someone in Lenexa wrote a script to get around this but of course it's
a ton of downtime.
I presume you did some testing so am wondering why you might want to
revert. If you don't need to revert you are giving yourself a headache
for no reason, forget about the IPA's they'll get fixed eventually
although multiple versions of tables is not ideal.
Or perhaps with a huge DB you can only test for your bug once
upgraded?
With SAN's you can always sync up a copy of disk and revert if you
have a problem in no time, but then I guess you lose transactions if
you didnt just perform testing once upgraded. Restore is also probably
too slow for you.
Maybe you can get a coded binary with just your bug fix so you know
there should not be any other new features which might cause the need
for reversion.
IPA's I find on huge databases need to be fixed over a few weeks and
make sure you have a ton of log space depending on how you fix em.
Best get onto 11.5+ ASAP, at least you'll have your IPA's done )
Art Kagel
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (a...@iiug.org)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.
Davorin Kremenjas
> AFAIK there is no quick way to find pending inplace alters... Only a slow
> process. Not sure if Panther changed this...
There is a good document somewhere on the IBM web and it is slow, but
it works, returns only tables with pending IPAs.
Since it's usually the biggest tables which end up with pending IPAs
(there's never enough time/resources to get red of them properly) I
was wondering if this could be further narrowed down to specific pages
in those tables. Never got to the bottom of this though.
Fernando, would you know if page flags could provide an answer? For
example, sysmaster:syspaghdr.pg_flags.
Thanks
Davorin
Andreas Legner
counters on (secondary) partition pages, so no data and other pages need to be scanned and you can get what you want in a second
or two. The engine will not sense a bit since the tool is reading directly from disks.
Nothing official, designed for support purposes, but usually serving its purpose...
Let me know if you need it (platform required since it is a binary).
Andreas
Art Kagel
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (a...@iiug.org)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.
I could offer a tool called 'onion' that, among other things, can provide the desired informations directly from versions' page
counters on (secondary) partition pages, so no data and other pages need to be scanned and you can get what you want in a second
or two. The engine will not sense a bit since the tool is reading directly from disks.
Nothing official, designed for support purposes, but usually serving its purpose...
Let me know if you need it (platform required since it is a binary).
Andreas
On 20:59, Davorin Kremenjas wrote:
Andreas Legner
Hi Art,
the idea is walking all (or certain) dbspaces' tblspace tblspaces, relate partition header pages carrying a version pointer to their secondary partition pages (version pages + dependent alter descriptor pages), in correct order, then walk the contained alter descriptors and read out the versions' page counters and relate them to the partition's name and overall page count.
I don't think this would be feasible using (existing) SMI tables (unless you'd want to extract single bytes by offsets which would require intimate structure knowledge).
I'd be in favour of having new SMI tables for this purpose though these probably wouldn't be trivial to implement. They'd also probably not walk tblspace tblspcace... (as soon as a partition is loaded, all its IPA information is availble in memory). One could also imagine other dedicated server/sysadmin functionality in this direction ... not only to find and show all sorts of IPA informations, but also to have a more selective way of removing outstanding IPAs (e.g. dummy updates on only as many rows per old-version page as are required to update the whole page).
On the other hand in-place alters shouldn't be such an issue any more nowadays, right? Though you should of course have a way to know what state your tables are in.
Regards,
Andreas
| From: | Art Kagel <art....@gmail.com> |
| To: | Andreas Legner/Germany/IBM@IBMDE |
| Cc: | Davorin Kremenjas <davorin....@gmail.com>, inform...@iiug.org |
| Date: | 20.12.2010 18:48 |
| Subject: | Re: Re: finding inplace alters |
Andreas, you could probably rewrite it to use the sysmaster:syspaghdr table and then you may be able to release the source.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (a...@iiug.org)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.
On Mon, Dec 20, 2010 at 12:41 PM, Andreas Legner <andreas...@de.ibm.com> wrote:
counters on (secondary) partition pages, so no data and other pages need to be scanned and you can get what you want in a second
or two. The engine will not sense a bit since the tool is reading directly from disks.
Nothing official, designed for support purposes, but usually serving its purpose...
Let me know if you need it (platform required since it is a binary).
Andreas
On 20:59, Davorin Kremenjas wrote:
Informix-list mailing list
Inform...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
PeterP
> 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. ))
Mark Scranton
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
Fernando Nunes
_______________________________________________
Informix-list mailing list
Inform...@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
Weitkamp Heinz
in Version 7.31 the table syssltdatt is not present in sysmaster.
Greetings
Heinz
>>> Fernando Nunes <domus...@gmail.com> 22.12.2010 01:47 >>>
On Tue, Dec 21, 2010 at 6:37 PM, Mark Scranton
<ma...@markscranton.com>wrote:
> On Dec 20, 4:22 pm, PeterP <peterp...@gmail.com> wrote:
> _______________________________________________
> Informix-list mailing list
> Inform...@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.
--------------------------------------------------
Fernando Nunes
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!
Hallo,
in Version 7.31 the table syssltdatt is not present in sysmaster.
Greetings
Heinz
>>> Fernando Nunes <domus...@gmail.com> 22.12.2010 01:47 >>>
> _______________________________________________
> Informix-list mailing list
> Inform...@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>
_______________________________________________
Informix-list mailing list
Inform...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
Davorin Kremenjas
> 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 from
systables where tabid in (select tabid from mincols));
Superboer
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:
Davorin Kremenjas
> 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.
PeterP
wrote:
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.
Superboer
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.
Fernando Nunes
I could offer a tool called 'onion' that, among other things, can provide the desired informations directly from versions' page
counters on (secondary) partition pages, so no data and other pages need to be scanned and you can get what you want in a second
or two. The engine will not sense a bit since the tool is reading directly from disks.
Nothing official, designed for support purposes, but usually serving its purpose...
Let me know if you need it (platform required since it is a binary).
Andreas
Hello Andreas...
Are you sure the counters are updated after a dummy update?
I get to the point where I get that info from SQL, but unless I'm doing something completely wrong, the counters did not change.....
Regards
Andreas Legner
Morning Fernando,
I'd assume so (will check once I find time). The counters should be updated for every single row/page being lifted to the latest version.
If you're taking the counters from the (secondary) partition pages, you'd probably need to checkpoint first to get the in-memory partition informations flushed to partition pages (and to disk, though this part probably isn't what you need).
HTH,
Andreas
| From: | Fernando Nunes <domus...@gmail.com> |
| To: | Andreas Legner/Germany/IBM@IBMDE |
| Cc: | inform...@iiug.org |
| Date: | 11.01.2011 01:55 |
| Subject: | Re: Re: finding inplace alters |
I could offer a tool called 'onion' that, among other things, can provide the desired informations directly from versions' page
counters on (secondary) partition pages, so no data and other pages need to be scanned and you can get what you want in a second
or two. The engine will not sense a bit since the tool is reading directly from disks.
Nothing official, designed for support purposes, but usually serving its purpose...
Let me know if you need it (platform required since it is a binary).
Andreas
Hello Andreas...
Are you sure the counters are updated after a dummy update?
I get to the point where I get that info from SQL, but unless I'm doing something completely wrong, the counters did not change.....
Regards
Fernando Nunes
Thanks for the reply, and don't bother... You were right... A checkpoint is needed.
So, at this moment I have something that apparently works... But needs some refinement and further testing...
Also... I need a way to find out if the platform is little endian or big endian.... any ideas?
Regards!
--
Fernando Nunes
Portugal
Art Kagel
#if !defined LITTLE_ENDIAN
# define LITTLE_ENDIAN 1234
#endif
int EndianNess;
int DetermineEndianNess()
{
int testint = 0x04030201, ii, result = 0, mult=1000;
char *p = (char *)&testint;
for ( ii=0; ii < 4; ii++) {
result += mult * p[ii];
mult /= 10;
}
return result;
}
int main() {
EndianNess = DetermineEndianNess();
printf( "LITTLE_ENDIAN=%d. EndianNess = %d.\n", LITTLE_ENDIAN, EndianNess );
if (EndianNess == LITTLE_ENDIAN) {
printf( "LITTLE_ENDIAN\n" );
} else /* BIG_ENDIAN */ {
printf("BIG_ENDIAN\n" );
}
return 0;
}
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (a...@iiug.org)
Blog: http://informix-myview.blogspot.com/
--
Fernando Nunes
Portugal
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
Andreas Legner
Hi Fernando,
you mean how to determine engine's endianess using an SQL query?
There might be more elegant ways, but what about
select first 1 hexdata from sysmaster:sysrawdsk;
This would return first 16 raw bytes from root chunk, so something starting on "00000000 0001" from big endian and "00000000 0100" from little endian servers.
Of course this is restricted to user informix.
In case this page zero uses an old page header (pre-9.40 page), you'd get "00000001" or "01000000" respectively. You could tell the two cases by checking if first 8 nibbles all are zeroes.
More easily, you could:
select first 1 hexdata[1,4] from sysshmem;
This should return first shmem segments magic number 0xe0b7, as either "e0b7" or "b7e0".
Cheers,
| From: | Fernando Nunes <domus...@gmail.com> |
| To: | Andreas Legner/Germany/IBM@IBMDE |
| Cc: | inform...@iiug.org |
| Date: | 12.01.2011 03:06 |
| Subject: | Re: Re: finding inplace alters |
Fernando Nunes
SELECT
s.hexdata[1,8]
INTO
v_hexdata
FROM
sysmaster:syssltdat s
WHERE
s.partnum = '0x100001' AND
s.pagenum = 1 AND
s.slotnum = 1 AND
s.slotoff = 0;
IF v_hexdata = '01001000'
THEN
LET v_endian = 'LITTLE';
ELSE
IF v_hexdata = '00100001'
THEN
LET v_endian = 'BIG';
ELSE
RAISE EXCEPTION -746, 0, 'Invalid Endianess calculation... Check procedure code!!!';
END IF
END IF
Tested on little endian and apparently it works.
If somebody can test it on a Big Endian machine (Solaris, AIX, HP-UX Itanium....) I'd appreciate...
Just run the query and see if it returns '00100001'
Kind regards.
P.S.: Pending IPA finder through SQL looks good (although the code is a bit odd, it is working).
I'll post it as soon as I test it on a "real" system to check the running time... For now, just on my VM which is just a "toy"...
Art Kagel
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (a...@iiug.org)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.
Fernando Nunes
Thanks. Andreas already tested this on Solaris and apparently it works.
Regards.
Fernando Nunes
I managed to get a quick and reliable way of identifying tables with pending inplace alters.
It's just an SQL script that creates the function. You can create it in any database on your system.
AFAIK it works. But please report any issues.
The explanation and code is here:
http://informix-technology.blogspot.com/2011/09/get-pending-in-place-alters-obter-as.html
I'd like to publicly send a big "thank you" to Andreas Legner from German tech support and Art Kagel for providing info, help, guidance and some debug skills :)
Regards.