Hello. I know this has probably been covered a dozen times or more
here, but we're preparing to do a database engine upgrade, and one of
the required tasks is to close any pending in-place alters, so that all
records in all tables conform to the current data definition.

I have a sysmaster query that identify which tables have been altered
in-place, but it doesn't tell me which of these tables actually HAS old
records out there. The only way I've found so far is to do an oncheck
-pT, which I have yet again learned the hard way locks tables and wreaks
all sorts of havoc on live production systems.

So does anyone know a way to do this _without_ resorting to invasive
onchecks?

Thanks in advance,

- TJG

select
{+ ORDERED }
pg_partnum + pg_pagenum - 1 partn
from
sysdbspaces a,
syspaghdr
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 systabnames b, pp where partn =
partnum;

ahum a quick search in the news group helps too....

See you

Superboer.


Thomas J. Girsch schreef:

> Hello. I know this has probably been covered a dozen times or more
> here, but we're preparing to do a database engine upgrade, and one of
> the required tasks is to close any pending in-place alters, so that all
> records in all tables conform to the current data definition.
>
> I have a sysmaster query that identify which tables have been altered
> in-place, but it doesn't tell me which of these tables actually HAS old
> records out there. The only way I've found so far is to do an oncheck
> -pT, which I have yet again learned the hard way locks tables and wreaks
> all sorts of havoc on live production systems.
>
> So does anyone know a way to do this _without_ resorting to invasive
> onchecks?
>
> Thanks in advance,
>
> - TJG

Thomas J. Girsch said:
>
> Hello. I know this has probably been covered a dozen times or more
> here, but we're preparing to do a database engine upgrade, and one of
> the required tasks is to close any pending in-place alters, so that all
> records in all tables conform to the current data definition.
>
> I have a sysmaster query that identify which tables have been altered
> in-place, but it doesn't tell me which of these tables actually HAS old
> records out there. The only way I've found so far is to do an oncheck
> -pT, which I have yet again learned the hard way locks tables and wreaks
> all sorts of havoc on live production systems.
>
> So does anyone know a way to do this _without_ resorting to invasive
> onchecks?

I've been to this movie a couple of times recently, and the upshot was
that it turned out every time to be more "cost-effective" to freeze any
schema changes and just run global updates in batches across all tables.

--
Bye now,
Obnoxio

"C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule"
- Coluche

did i mention i like nulls? heck, i even go so far as to say that all
columns in a table except the primary key could/should be nullable. this
has certain advantages, for example, if you need to insert a child record
and you don't have a parent row for it, just do an insert into the parent
table with the primary key value (everything else null), and voila,
relational integrity is preserved. but this is, admittedly, a bit
controversial among modellers.

--r937, dbforums.com

TJG Said
>I have a sysmaster query that identify which tables have been altered in-place, but it >doesn't tell me which of these tables actually HAS old records out there.

What is your query that does this?

>So does anyone know a way to do this _without_ resorting to invasive onchecks?

If you can restore your production server to a test system run your
onchecks there.

bozon wrote:
> TJG Said
>> I have a sysmaster query that identify which tables have been altered in-place, but it >doesn't tell me which of these tables actually HAS old records out there.
>
> What is your query that does this?
>
See Superboer's response.

>> So does anyone know a way to do this _without_ resorting to invasive onchecks?
>
> If you can restore your production server to a test system run your
> onchecks there.
>
This is what we've done in the past, but this time tight development
schedules prohibited us from doing so.

Superboer wrote:

> ahum a quick search in the news group helps too....
>
>
And a quick re-read of my message would show you that I already _knew_
how to do those sysmaster queries, but that those only show the
_possible_ pending in-place alters, not the _actual_ ones.

I'm not above a RTFM reprimand now and again, but let's save them for
when they apply, shall we?

As it turns out, on one of our systems, those queries identified about
two dozen tables, only a third of which actually needed the dummy
updates. On large tables, we'd like to avoid running those dummy
updates except where absolutely necessary.

Thomas J. Girsch said:
>
> Superboer wrote:
>
>> ahum a quick search in the news group helps too....
>>
>>
> And a quick re-read of my message would show you that I already _knew_
> how to do those sysmaster queries, but that those only show the
> _possible_ pending in-place alters, not the _actual_ ones.
>
> I'm not above a RTFM reprimand now and again, but let's save them for
> when they apply, shall we?
>
> As it turns out, on one of our systems, those queries identified about
> two dozen tables, only a third of which actually needed the dummy
> updates. On large tables, we'd like to avoid running those dummy
> updates except where absolutely necessary.

RTFM.

--
Bye now,
Obnoxio

"C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule"
- Coluche

did i mention i like nulls? heck, i even go so far as to say that all
columns in a table except the primary key could/should be nullable. this
has certain advantages, for example, if you need to insert a child record
and you don't have a parent row for it, just do an insert into the parent
table with the primary key value (everything else null), and voila,
relational integrity is preserved. but this is, admittedly, a bit
controversial among modellers.

--r937, dbforums.com

Obnoxio The Clown wrote:
> RTFM.
>

say it ain't so, they took the cow away...