Informix Upgrade 7.31 to 9.3

[Jn]

We will process our first Informix upgrade from 7.31 to 9.3 on AIX.
Has anyone previously upgraded on these versions and if so can you
tell me the general steps you followed? Also, are there any tips or
alerts we should know about?

Thanks very much,
J Nethercutt

[Martin, Wayne E.]

Only thing we did was the update statistics drop distributions and then
rebuild them high again..

Wayne Martin
Database Administrator
Kmart Corporation

This message and its contents (to include attachments) are the property of Kmart Corporation (Kmart) and may contain confidential and proprietary information. You are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on information contained herein is strictly prohibited. Unauthorized use of information contained herein may subject you to civil and criminal prosecution and penalties. If you are not the intended recipient, you should delete this message immediately.

sending to informix-list

[Zunino, Kathy]

First, you might search through the IIUG website to see if you can find a copy of Carlton Doe's presentation on 'Upgrading to IDS 9.3 / 9.4' from last years' Data Management conference (?).

Below is an excerpt from a document we wrote at the time to smooth our transition from IDS 7.31.UC2 to IDS 9.30.FC1 a year ago. We did an 'upgrade in place'. You'll find many people prefer to export / import. With the size of most of my databases, that is not an option - we must upgrade in place (our databases range from 20 GB to 300 GB) and we are never allowed more than 36 hours of downtime.

1. Install the IDS (9.3) in a new directory in the following order
4GL -> Install as root
IDS -> Install as root
IDS Patch -> Install as Informix
(Change all root owned files to informix in $INFORMIXDIR.
After the installation RUN_AS_ROOT.server as root
Change owner $INFORMIXDIR/incl/esql to informix:informix)
SDK -> Install as informix
After the installation RUN_AS_ROOT.clientsdk as root

2. Back up the database. <<-- verify your backup! (we usually make 2 and verify both - one on tape and one is a disk image (BCV)).

3. Backup the critical configuration files
sqlhosts, onconfig files, termcap etc...

4. Stop replication and drop the syscdr database.
For production, you'll need to do a graceful drop: 'cdr stop repl ~~~~~', 'cdr delete repl ~~~~~~' and then 'cdr delete server grp_xxxxxx'.

5. <snip (this step won't affect you)>

6. Stop database processing and force a change to a new logical log.
onmode -sy; onmode -l; onmode -yuck

7. Restart the instance in single user mode.
oninit -sv

8. Verify the data and index integrity
oncheck -cr; oncheck -cc; oncheck -ce etc...

9. Shutdown the instance
onmode -ky

10. Export your INFORMIXDIR to the IDS 9.3 directory.

11. Do NOT copy the ONCONFIG files from 7 to 9. Modify the IDS 9.3 standard config file. Prepare your sqlhosts & termcap.

12. Restart the instance and monitor the online log file. Wait until you see in the online log "Conversion Completed Successfully"

13. Verify the data and index integrity.
oncheck -cr; oncheck -cc; oncheck -ce, oncheck -cDI etc...

14. You must add a 'smart blobspace' before you can prepare the servers for replication.

15. Setup replication and start the replication ('cdr define server grp_~~~~...', 'cdr define repl ~~~~~.... ', 'cdr start repl ~~~~~.... ').

16. <snip - this step won't affect you.>

17. Run update statistics low drop distributions on each database.
Run update statistics high on sysmaster
Run your regular update statistics script(s) with distributions on each database. (you might see Art Kagel's do_stats script).

IDS 9.3 reversion

Important: Before you revert from Dynamic Server 9.30 or 9.2x to a 9.14, 7.3x, or 7.2x database server, you need to remove all Version 9.30 and Version 9.2x objects from the databases except those that the boot scripts created in the system catalog. Do not remove the objects that the boot90.sql and boot901.sql scripts created because the reversion utility uses them

1. Review the database schema to determine whether reversion is possible.

2. Save copies of the current configuration files

3. Verify the integrity of the data

4. Back up IDS 9.3 <<-- verify your backup!

5. Stop the replication and drop syscdr database:

cdr stop repl ~~~~~
cdr delete repl ~~~~~
cdr delete server grp_xxxxxx

6. Remove any version 9.30 and/or version 9.2x features

7. Drop the SPL's, tables, indices which are created under 9.3 (make sure you have them backed up before you drop them)

8. Complete any pending In-Place Alters (force any outstanding in-place alters to complete so that no table has more than the current version).

9. Drop any Smart Blobspaces:

10. Run the reversion utility (onmode -b)
onmode -b 7.3

11. IF YOU FAIL the reversion
onmode -my
fix any problems/issues and repeat from step 9

12. Reset environment variables & configuration parameters for IDS 7.3

13. Start the engine

14. Run update statistics

15. Verify the integrity of the data

We created a new directory for the Informix installation so that we had our '7' directory ("/opt/ifmx731xj") and a '9' directory ("/opt/ifmx93fc1"). In our case, we've linked both the termcap and sqlhosts to another location so that we don't have to keep multiple copies. Then we created new ONCONFIGs in the IDS 9 directory for each of the engines.

The gottcha's we encountered, were some cross-platform issues relating to CDR -
1. If running CDR between both 7 and 9.2x and 9.3, each 9.3 engine will need a new environment variable set: CDRSITES_731 and/or CDRSITES_92x
2. In a select on a replicated table between both 7 and 9.3 engines, the shadow columns, CDRTIME & CDRSERVER appear when they should not.

We did encounter a few weird quirks on a handful of practice attempts where not all databases 'upgraded'. On those occasions, we found that if you 'dbaccess' into the unconverted database, the engine would complete the conversion for them. Tech support never found why. Any schema changes under IDS 9 will not be reversible through the standard reversion.

-----Original Message-----
From: Jn [mailto:jneth...@wehco.com]
Sent: Tuesday, July 29, 2003 6:25 AM
To: inform...@iiug.org
Subject: Informix Upgrade 7.31 to 9.3

We will process our first Informix upgrade from 7.31 to 9.3 on AIX.
Has anyone previously upgraded on these versions and if so can you
tell me the general steps you followed? Also, are there any tips or
alerts we should know about?

Thanks very much,
J Nethercutt

**********************************************************************
Privileged/Confidential information may be contained in this message.
If you are not an addressee indicated in this message (or responsible
for delivery of the message to such person[s]), you may not copy or
deliver this message to anyone. If you have received this message in
error, you should destroy and delete it from your computer and notify
the sender by reply email. Opinions, conclusions and other information
in this message that do not relate to the official business of
Diversified Collection Services shall be understood as neither given
nor endorsed by the company. Accordingly, Diversified Collection
Services disclaims all responsibility and accepts no responsibility for
the consequences of any person(s) acting, or refraining from acting,
on such information prior to the receipt by that person(s) of
subsequent written confirmation.
**********************************************************************

sending to informix-list