You do have a way with words don't you Art ?
ha.
Yea I hear you totally.

Thanks again !






----- Original Message -----
From: art.kagel (AT) gmail (DOT) 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.

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 whate
ver 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 (art (AT) iiug (DOT) 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 Thu, Nov 18, 2010 at 6:14 AM, Floyd Wellershaus >;floyd (AT) fusemail (DOT) com

On Nov 18, 12:21*am, Fernando Nunes <domusonl... (AT) gmail (DOT) com> wrote:

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

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, 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 (art (AT) iiug (DOT) 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.legner (AT) de (DOT) ibm.com>wrote:

This is the query used in the "check_for_ipa" database scheduler task in
11.70...

SELECT dbsname, trim(owner)||"."|| tabname AS fullname
FROM
sysmaster:sysactptnhdr h,sysmaster:systabnames t
WHERE
h.partnum = t.partnum
and dbsname not in ("sysmaster")
and pta_totpgs != 0

HTH,

-- Mirav




From: Davorin Kremenjas <davorin.kremenjas (AT) gmail (DOT) com>
To: informix-list (AT) iiug (DOT) org
Date: 12/20/2010 04:39 AM
Subject: Re: finding inplace alters
Sent by: informix-list-bounces (AT) iiug (DOT) org



On Nov 18, 12:21*am, Fernando Nunes <domusonl... (AT) gmail (DOT) com> wrote:

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
_______________________________________________
Informix-list mailing list
Informix-list (AT) iiug (DOT) org
http://www.iiug.org/mailman/listinfo/informix-list

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.kagel (AT) gmail (DOT) com>
To:
Andreas Legner/Germany/IBM@IBMDE
Cc:
Davorin Kremenjas <davorin.kremenjas (AT) gmail (DOT) com>, informix-list (AT) iiug (DOT) 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 (art (AT) iiug (DOT) 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.legner (AT) de (DOT) ibm.com> wrote:
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:
_______________________________________________
Informix-list mailing list
Informix-list (AT) iiug (DOT) org
http://www.iiug.org/mailman/listinfo/informix-list

On Dec 18, 10:52*pm, Art Kagel <art.ka... (AT) gmail (DOT) com> wrote:
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. ))

On Dec 20, 4:22*pm, PeterP <peterp... (AT) gmail (DOT) com> wrote:
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

On Tue, Dec 21, 2010 at 6:37 PM, Mark Scranton <mark (AT) markscranton (DOT) com>wrote:


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...

Hallo,

in Version 7.31 the table syssltdatt is not present in sysmaster.

Greetings
Heinz


<mark (AT) markscranton (DOT) com>wrote:


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.
--------------------------------------------------