RE: Identifying In-place Alters before IDS upgrade
Informix Technical Support (case 200515) provided me with the following
script to identify outstanding
"in-place ALTERs". It runs quickly, even with 9,000+ SAP tables. This
might be a good item to include
in the Informix FAQ. I have successfully run it with IDS 7.31.UC3-1 and IDS
7.30.UC7XK.
# ksh script - run from ksh
tblpartnum=1048577
numdbs=`dbaccess sysmaster << !!! 2> /dev/null |grep -v max|awk '{print $0}'
select {+ full(sysdbstab)} max(dbsnum) from sysdbstab
!`
i=1
while (( i <= $numdbs ))
do
dbaccess sysmaster <<!
select hex(t1.pg_partnum), t1.pg_pagenum,t1.pg_physaddr,hex(t2.partnum),t3.
tabname
from syspaghdr t1, sysptntab t2, systabnames t3
where t1.pg_partnum=$tblpartnum
and t1.pg_flags=2
and t1.pg_next !=0
and t1.pg_physaddr=t2.physaddr
and t2.partnum=t3.partnum
!
let i=i+1
let tblpartnum=tblpartnum+1048576
done
> -----Original Message-----
> From: Bernstein, Rick
> Sent: Monday, October 16, 2000 12:28
> To: informix-list@iiug.org
> Subject: Identifying In-place Alters before IDS upgrade
>
> We are planning an Informix upgrade. Before-hand we need to consolidate
> each table into one version.
> It is not practical for us to run "oncheck -pT <database>:<table>"
> commands against 11,000 tables.
> Are there SQL commands, which can identify tables with multiple versions
> from "in-place ALTER" commands?
> If so, can someone provide the proper syntax?
>
> Your assistance in this endeavor would be appreciated.
>
> Thanks,
> Rick
>
"Bernstein, Rick" wrote: > > Informix Technical Support (case 200515) provided me with the following > script to identify outstanding > "in-place ALTERs". It runs quickly, even with 9,000+ SAP tables. Thanks for providing us with that script. However, I don't know how to interpret it's output. When I run it, it prints an endless list of table names like this: (expression) pg_pagenum pg_physaddr (expression) tabname 0x00100001 145 1054650 0x00100001 TBLSpace 0x00100001 145 1054650 0x00100002 sysdatabases 0x00100001 145 1054650 0x00100003 systables 0x00100001 145 1054650 0x00100004 syscolumns 0x00100001 145 1054650 0x00100005 sysindexes 0x00100001 145 1054650 0x00100006 systabauth 0x00100001 145 1054650 0x00100007 syscolauth 0x00100001 145 1054650 0x00100008 sysviews 0x00100001 145 1054650 0x00100009 sysusers and so on ad infinitum (more than 8000 lines). What does that mean? This is on IDS 7.31UC4X1 on Siemens Reliant Unix 5.43. Regards, Richard -- +-----------------------------+-------------------------------------+ | Dr. med Richard Spitz | Mail: spitz@ana.med.uni-muenchen.de | | Klinik für Anaesthesiologie | Tel : +49-89-7095-6110 | | Klinikum der Univ. München | FAX : +49-89-7095-6420 | | 81366 München, Germany | GSM : +49-172-8933578 | +-----------------------------+-------------------------------------+