April 1999 Newsletter

Volume 9, No. 2

Highlights of This Issue

WAIUG Training Day 1999, by Peter Wages and Nick Nobbe

Exploring the OnLine Command Line Utilities (Part 3) by Lester Knutsen

Informix and Red Brick - Find out what all the excitement is about

Red Brick Warehouse is an open, relational database designed specifically to meet the specialized requirements of data warehouse and data mart applications. Red Brick Warehouse is optimized for complex high performance queries and efficient management of very large databases. This presentation will provide an introduction to the new offerings in the arsenal of Informix products which address the data warehousing and data mart needs of your organization. Learn how Red Brick Warehouse will help you deliver solutions faster, keep costs low, and provide unprecedented ability to answer most complex business questions.

SuperNova - Generate Java from your 4GL

Date and Time: June 16, 1999, 9:00 a.m. to 12:00 noon

Location: Informix Software Corporation

8065 Leesburg Pike, Suite 600, Vienna, VA 22182
 

The meeting is open to everyone. Please RSVP to 703-256-0267, ext. 117, so we can keep an attendance count.
 
 

The user group has been supported by many companies over the years. We would like to thank the following companies for sponsoring this issue:

Advanced DataTools Corporation

Advanced Global Systems, Ltd.

Business Objects

FuGEN Technologies, Inc.

July 19-22 in San Diego at the San Diego Convention Center
 

Join us at the 1999 Informix Worldwide User Conference and Exhibition (IWUC), where over 3,000 Informix users, partners, and database industry press and analysts from around the world will convene to examine solutions for achieving leadership in today's global market. 
 

Tutorial Sessions 

On Monday, July 19, uncover in-depth technical information in our popular half-day tutorials. Always a sell-out, these session will help you plan for the future while leveraging your current database investment. When you register you may choose from seven morning and seven afternoon sessions:
 

Trouble-Shooting Informix Dynamic Server 

Demystifying Data Warehousing

Informix Dynamic 4GL

Centaur™ Extensibility Utilizing the Web DataBlade Module

Monitoring and Tuning Informix Dynamic Server with Advanced Decision Support 

and Extended Parallel Options

Retooling Oracle DBA to Informix

DataBlade Module Development Methods

Monitoring and Tuning Informix Dynamic Server Performance

Sneak Peak - Migration of Informix Dynamic Server 7.3 Applications to Centaur™

Introduction to Red Brick

Smart Data - Java Programming on Centaur™

Decision Frontier Solution Suite

Informix Visionary

Informix's i.Reach and i.Sell Solutions: a Technical Overview
 

Track Sessions

This year's comprehensive tracks offer invaluable insights into the latest trends and tools to help you use technology to seize new opportunities. Tracks include: 

Business Trends Solutions Overview

Tools and Application Development Server Management

Smart Data Federation Tips and Tricks 

IIUG Data Management IIUG Technical
 

$200 Discount for WAIUG Members

WAIUG members as of March 5, 1999 are eligible to receive a $200 discount off the registration fee for the Informix Worldwide User Conference, if registering before July 9, 1999. To take advantage of this excellent opportunity, you must request the International Informix User Group (IIUG) Member Discount and have your IIUG member number when you register for the Conference. All members requesting the discount will be verified for IIUG membership, so you must have joined the user group by March 5, 1999. Please call John Petruzzi at 703-405-5348 or Linda Knutsen at 703-256-0267, ext. 101, if you need your IIUG member number. More information is available by contacting (800) 638-0838, or on the web at www.informix.com.

onmode -abcDdFklMmnpQRrSsuyZz

-a <kbytes> Increase shared memory segment size

-b <version> Revert disk structures to an older version

-c Perform a checkpoint

-D <max PDQ priority allowed> Set max PDQ

-d {standard|{primary|secondary <servername>}} set Data Replication server type

-F Free unused memory segments

-k Shutdown completely

-l Force switch to next logical log

-M <decision support memory in kbytes> Set size of Decision Support Memory

-m Go to multi-user on-line mode from quiescent mode

-n Set shared memory buffer cache to non-resident

-O Override dbspace down blocking a checkpoint

-p <+-#> <class> Start up or remove virtual processors of class cpu, aio, lio, pio, 

shm, soc, or tli

-Q <max # decision support queries> Set max number of Decision Support queries

-R Rebuild the /INFORMIXDIR/etc/.infos.DBSERVERNAME file

-r Set shared memory buffer cache to resident

-S <max # decision support scans> Set max number of Decision Support Scans

-s Shutdown to single user (Graceful shutdown)

-u Shutdown and kill all attached sessions (Immediate Shutdown)

-y Do not require confirmation mode changes

-Z <address> heuristically complete specified transaction

-z <sid> Kill specified database session id

---

One of the most common uses of the ONMODE utility is to shutdown the database server. To immediately shutdown the server from any mode, type:

onmode -ky
 

The '-k' option takes the database server off-line and the 'y' avoids the prompt to confirm your action. This immediately take the server off-line, disconnecting all users. Any user in the middle of a transaction will have their transaction rolled back to the state before they started their transaction. Any work the user was doing will be lost. You must be the user 'root' or 'informix' to perform this function.
 

Figure 26 shows the error message you will get if you are not logged in as 'informix' or 'root' to shutdown IDS. It also shows the message you and your users will receive when IDS has been shutdown and you try to access the database server.
 

Figure 26: Shutting down IDS

lester@merlin >onmode -ky

Must be a DBSA to run this program

lester@merlin >su informix

Password: 

lester@merlin >onmode -ky

lester@merlin >onstat -

shared memory not initialized for INFORMIXSERVER 'train1'

One useful method of invoking this command is to put it in the UNIX shutdown script for your machine. This way when the computer is stopped it will automatically stop IDS. Check with your UNIX System Administrator on the location of the script. I like to add a call to a separate shell script that uses ONMODE to shutdown the server and ONINIT to start it up, based on a parameter passed to the script. See Figure 27 for an example script that starts and stops multiple IDS Servers. A good way to test such a startup script is to execute it as root, using the Bourne Shell with none of the Informix environment variables set. 
 
 
 

Figure 27: IDS startup and shutdown script

#############################################################################

# Module: %W% Date: %D%

# Author: Lester B. Knutsen email: lester@access.digex.net

# Advanced DataTools Corporation

# Discription: Informix IDS startup/Shutodwn script

# This script is used to start and stop 3 IDS Servers

# used for training named: train1, train2, train3

#############################################################################

# Set Global environment variables

#############################################################################

## Set the location of Informix Programs

INFORMIXDIR=/u3/informix7

export INFORMIXDIR

## Add the Informix Programs to your PATH

PATH=$INFORMIXDIR/bin:$PATH:/usr/ccs/bin

export PATH

#############################################################################

# Process and shutdown server

#############################################################################

## Set the Database Server

INFORMIXSERVER=train1

export INFORMIXSERVER

## Set the Informix Configuration File

ONCONFIG=onconfig.train1

export ONCONFIG

state=$1

        case $state in

            start) 

                oninit;

                echo "Informix Server: $INFORMIXSERVER Started";;

            stop)

                onmode -ky;

                echo "Informix Server: $INFORMIXSERVER Shutdown";;

            *)

                echo "usage: ifx.rc start|stop";;

        esac

#############################################################################

# Process and shutdown server

#############################################################################

## Set the Database Server

INFORMIXSERVER=train2

export INFORMIXSERVER

## Set the Informix Configuration File

ONCONFIG=onconfig.train2

export ONCONFIG

state=$1

    case $state in

        start) 

            oninit;

            echo "Informix Server: $INFORMIXSERVER Started";;

        stop)

            onmode -ky;

            echo "Informix Server: $INFORMIXSERVER Shutdown";;

        *)

            echo "usage: ifx.rc start|stop";;

    esac

#############################################################################

# Process and shutdown server

#############################################################################

## Set the Database Server

INFORMIXSERVER=train3

export INFORMIXSERVER

## Set the Informix Configuration File

ONCONFIG=onconfig.train3

export ONCONFIG

state=$1

        case $state in

            start) 

                oninit;

                echo "Informix Server: $INFORMIXSERVER Started";;

            stop)

                onmode -ky;

                echo "Informix Server: $INFORMIXSERVER Shutdown";;

            *)

                echo "usage: ifx.rc start|stop";;

            esac

In addition to the 'onmode -k' option to shutdown, ONMODE has three other options to change the mode of IDS. The '-k' option completely shut down the database server and takes it off-line. There are two options that take the database server to quiescent mode. Quiescent mode is like a maintenance mode or single-user mode where you can access IDS with the utilities but users cannot connect.
 

The command to gracefully take IDS to quiescent mode is: 

onmode -s
 

The command to immediately take IDS to quiescent mode is: 

onmode -u
 

The difference between these is that the '-s' option will wait until all users have disconnected before changing modes, and the '-u' option will change modes immediately and kill all connected users.
 

To return to on-line mode rrom quiescent mode so users can once again access the database server, the command is: 

onmode -m
 

Forcing a checkpoint
 

A checkpoint is one of the key events when IDS syncs shared memory with what is on disk. Several activities depend on the last completed checkpoint. An archive takes its start date and time from the last checkpoint. You cannot delete a logical log that contains the last checkpoint. To force IDS to perform a checkpoint, use the following onmode command and option:

onmode -c
 

Forcing a switch in the current logical log
 

Another option to ONMODE allows you to change the current logical log to the next logical log in sequence. This is required if you are going to backup the current logical log or to drop the current logical log. The command and option to change the current logical log is:

onmode -l
 
 
 

Free unused virtual memory segments

As IDS runs, it will add additional virtual shared memory segments as needed. Since this operation has some overhead, IDS does not release unused memory segments, but saves them for future reuse. The 'onstat -g seq' command discussed earlier in the chapter shows you the current virtual memory segments. The command to force IDS to reorganize its virtual memory segments and free unused segments is:

onmode -F
 

This operation requires some overhead and will freeze all user processing while IDS reorganizes and frees this segment. Because of the overhead of free memory and then re-adding it later, this operation should only be done when required. Monitor your virtual memory segments with the command 'onstat -g seg'. When you notice an increase in the virtual memory segments, and you see that these are no longer being used, then it may be useful to free them with this command. A common occurrence of this is after running large weekly or month-end batch jobs and reports. These type of jobs will often require extra memory that will be used until the next cycle of processing. This is a good opportunity to use this command. Do not repeatedly run this command at short intervals to free memory. The overhead of freeing memory and then re-acquiring it will slow things down.
 

Killing users' database processes

ONMODE provides an option to kill and abort an individual user's database process. This option is aware of a user's database transaction and will rollback any work that was not committed. Operating system commands to kill a user's process (e.g. the UNIX kill -9 command) are not aware of a user's database connection and may not cleanly rollback their work. This can lead to corruption of tables or indexes. The correct procedure to kill a user's database process is:
 

1. Identify the user's session id using the ONSTAT command with one of the following three options:
 

onstat -u 

onstat -g sql 

onstat -g ses

2. Use the following omode command to terminate the user's session:
 

onmode -z session_id

Figure 28: Terminating a user's session

lester@merlin >onstat -u



INFORMIX-OnLine Version 7.23.UC1   -- On-Line -- Up 28 days 11:52:49 -- 10656 Kbytes



Userthreads

address  flags   sessid   user     tty      wait     tout locks nreads   nwrites

a2d0018  ---P--D 1        informix -        0        0    0     114      486

a2d0458  ---P--F 0        informix -        0        0    0     0        5657

a2d0898  ---P--B 8        informix -        0        0    0     2        0

a2d1558  ---P--D 12       informix -        0        0    0     0        2

a2d1998  Y--P--- 190      lester   0        a3d1d50  0    1     18       0

 5 active, 128 total, 17 maximum concurrent



lester@merlin >onmode -z 190

lester@merlin onstat -u



INFORMIX-OnLine Version 7.23.UC1   -- On-Line -- Up 28 days 11:53:00 -- 10656 Kbytes



Userthreads

address  flags   sessid   user     tty      wait     tout locks nreads   nwrites

a2d0018  ---P--D 1        informix -        0        0    0     114      486

a2d0458  ---P--F 0        informix -        0        0    0     0        5657

a2d0898  ---P--B 8        informix -        0        0    0     2        0

a2d1558  ---P--D 12       informix -        0        0    0     0        2

 4 active, 128 total, 17 maximum concurrent

Another delay will occur if the user was in the middle of a large update or load in a transaction. IDS will need to rollback the transaction. The general rule that I use is that if the user was in a transaction for 30 minutes loading data, it will take about 30 minutes to rollback their work. IDS must delete all the records it has inserted. You must let the rollback complete.
 

Using ONMODE for configuration changes 

ONMODE has several options that allow you to change your configuration while the database server is up and running. This saves changing the ONCONFIG file, shutting down the server, and then restarting it with the new configuration. However, changes made using ONMODE are not written to the ONCONFIG file and will be lost when IDS is shutdown and restarted.
 

The following options to ONMODE allow changes:
 

-a <kbytes> Increase shared memory virtual segment size

-b <version> Revert OnLine disk structures to an older version of OnLine (e.g 5, 6)

-d {standard|{primary|secondary <servername>}} set Data Replication server type

-n Set shared memory buffer cache to non-resident

-O Override dbspace down blocking a checkpoint

-p <+-#> <class> Start up or remove virtual processors of class cpu, aio, lio, pio, 

shm, soc, or tli

-R Rebuild the /INFORMIXDIR/etc/.infos.DBSERVERNAME file

-r Set shared memory buffer cache to resident

Decision support Configuration changes

-D <max PDQ priority allowed> Set max PDQ

-M <decision support memory in kbytes> Set size of Decision Support Memory

-Q <max # decision support queries> Set max number of Decision Support queries

-S <max # decision support scans> Set max number of Decision Support Scans

ONLOG - debug the logical log
 

The ONLOG utility allows you to debug transactions using the logical logs. This utility should be used with care as it can display lots of information. The best use of this utility is to research why a user's transaction failed. Figure 29 contains the syntax for this command.
 

Note: Running ONLOG on the current logical log file (the default) will lock the log file and stop all user processing.
 

Figure 29: ONLOG syntax

onlog [-l] [-q] [-b] [-d <tape device>] [-n <log file number>] [-u <user name>]

[-t <TBLspace number>] [-x <transaction number>]

-l Display maximum information about each log record including hex dump

-q Do not display program header

-b Display information about logged BLOB pages (-d option only)

-d Read from tape device

-n Display the specified log(s)

-u Display the specified user(s)

-t Display the specified TBLspace(s)

-x Display the specified transaction(s)

The ONINIT utility starts IDS and without any other options brings it into on-line mode so users can connect and go to work. This is one of the key commands along with 'onmode -ky' to start and stop your database server. See Figure 27 for a script that uses these commands to automatically start or stop IDS. 
 

However, ONINIT is also one of the most dangerous commands. With a single option (-i) it will initialize you rootdbs, wiping out anything that was there and all your hard work. There may be times you will want to do this but be very careful and make sure all your environment variables are set correctly.
 

Starting IDS
 

The command to start IDS is very simple. Just type 'oninit', and it will use four environment variables to identify the database server to start. The environment variables are:
 

INFORMIXDIR - Points to the directory where the Informix products are installed and is used by IDS as a base directory to locate other files it needs.
 

PATH - This is used by your operating system to search for executables and must include the directory located in $INFORMIXDIR/bin.
 

INFORMIXSERVER - This is the name of the IDS server you wish to start. This name is also located in your ONCONFIG file.
 

ONCONFIG - This is the name of the configuration file IDS will use to start the database server. It is located in $INFORMIXDIR/etc.
 

Once these are set and you are logged in as the user 'root' or 'informix', type 'oninit' to start the server. Figure 27 was an example of a script that will start several IDS servers. Normally you want your IDS server to start automatically every time you boot your computer. This script can be called from one of the UNIX startup scripts like '/etc/rc.local' to perform this function. Check with your operating system administrator to find the name of the UNIX startup script that you will need to call this script from.
 

Figure 30 shows all the options to ONINIT. 
 

Figure 30: ONINIT syntax

oninit [-I] [-p] [-s] [-y] [-V] [-v]

-I Startup and initialize rootdbs. This will destroy any existing data in your rootdbs

-p Startup and do not delete temp tables during shared memory initialization

-s Startup and stay in quiescent mode

-V Display version

-v Startup in verbose mode. Many additional messages will display that are helpful in debugging problems.

The '-I' option of ONINIT is very powerful and dangerous. It will startup IDS and initialize your rootdbs. This process is like formatting a dbspace and will destroy all data that is there. However, this option is very handy when you know what you are doing and want to initialize your rootdbs. When I need to configure several database servers it is easier to write a script to do it rather then type all the commands and edit all the options by hand. Figure 35, shows an example script that does this, and uses the next two utilities we will talk about to initialize a rootdbs, set up several dbspaces, and move the logical logs to a separate dbspace.
 

Verbose Option

Another helpful option is the lower case '-v' for verbose. This displays extra messages as IDS goes through the different stages on initialization and is very helpful when debugging an installation. Figure 31 shows the output from this option on my training system.
 

Figure 31: Verbose startup of IDS with the '-v' option

lester@merlin >oninit -v

Reading configuration file '/u3/informix7/etc/onconfig.train1'...succeeded

Creating /etc/.infxdirs ... succeeded

Creating infos file "/u3/informix7/etc/.infos.train1" ... "/u3/informix7/etc/.conf.train1" ... succeeded

Writing to infos file ... succeeded

Checking config parameters...succeeded

Allocating and attaching to shared memory...succeeded

Creating resident pool 2160 kbytes...succeeded

Creating buffer pool 402 kbytes...succeeded

Initializing rhead structure...succeeded

Initializing ASF ...succeeded

Initializing Dictionary Cache and Stored Procedure Cache...succeeded

Onlining 0 additional cpu vps...succeeded

Onlining 2 IO vps...succeeded

Forking main_loop thread...succeeded

Initialzing DR structures...succeeded

Forking 1 'ipcshm' listener threads...succeeded

Forking 1 'tlitcp' listener threads...succeeded

Starting tracing...succeeded

Initializing 1 flushers...succeeded

Initializing log/checkpoint information...succeeded

Opening primary chunks...succeeded

Opening mirror chunks...succeeded

Initializing dbspaces...succeeded

Validating chunks...succeeded

Forking btree cleaner...succeeded

lester@merlin >Initializing DBSPACETEMP list

Checking database partition index...succeeded

Checking location of physical log...succeeded

Initializing dataskip structure...succeeded

Checking for temporary tables to drop

Forking onmode_mon thread...succeeded

Verbose output complete: mode = 5

lester@merlin >onstat -

INFORMIX-OnLine Version 7.23.UC1 -- On-Line -- Up 00:00:47 -- 10656 Kbytes

Figure 32: Startup messages in the IDS Message Log

Sat Aug 9 23:52:16 1997

23:52:16 Event alarms enabled. ALARMPROG = '/u3/informix7/log_full.sh'

23:52:17 DR: DRAUTO is 0 (Off)

23:52:18 INFORMIX-OnLine Initialized -- Shared Memory Initialized.

23:52:18 Physical Recovery Started.

23:52:18 Physical Recovery Complete: 0 Pages Restored.

23:52:18 Logical Recovery Started.

23:52:21 Logical Recovery Complete.

0 Committed, 0 Rolled Back, 0 Open, 0 Bad Locks

23:52:22 Dataskip is now OFF for all dbspaces

23:52:22 On-Line Mode

23:52:22 Checkpoint Completed: duration was 0 seconds.

This utility allows you to add, drop and change the mirroring of dbspaces. It is the equivalent of the menu options in ONMONITOR. If you seldom change your dbspace configuration it is easier to use the menus in ONMONITOR. The ONSPACES utility is very useful if you need to create scripts to change your dbspace configuration. Figure 33 has the syntax for this utility. Figure 35 is an example of a script that uses this utility to configure a database server from scratch.
 

Figure 33: ONSPACES syntax

onspaces { -a spacename -p pathname -o offset -s size [-m path offset] |

-c {-d DBspace [-t] | -b BLOBspc -g pagesize} -p pathname -o offset -s size 

[-m pathoffset]

-d spacename [-p pathname -o offset] [-y] | 

-f [y] off [DBspace-list] | on [DBspace-list] | 

-m spacename {-p pathname -o offset -m path offset [-y] | 

-f filename} | 

-r spacename [-y] | 

-s spacename -p pathname -o offset {-O | -D} [-y] }

-a Add a chunk to an existing DBspace or BLOBspace

-c Create a new DBspace or BLOBspace

-d Drop a DBspace, BLOBspace or chunk

-f Change dataskip default for specified DBspaces

-m Add mirroring to an existing DBspace or BLOBspace

-r Turn mirroring off for a DBspace or BLOBspace

-s Change the status of a chunk

ONPARAMS - Change logical and physical log configuration
 

This utility allows you to add logical logs, drop logical logs, and change the location of the physical log. Figure 34 shows the syntax for this command. This utility is handy because you can do some things with it that cannot be done with ONMONITOR. It allows you to add logical logs of different sizes and locations. One common use of this is after you have set up your server, you will often want to move your logs out of the rootdbs into their own dbspaces. Figure 35 contains an example of a script using this command to move the logical logs to their own dbspace and the physical log to its own dbspace.
 

Figure 34: ONPARAMS syntax

onparams { -a -d DBspace [-s size] | -d -l logid [-y] |-p -s size [-d DBspace] [-y] }

-a Add a logical log

-d Drop a logical log

-p Change physical log size and location

-y Answer YES to all questions

Figure 35: Script to initialize a database server, add dbspaces, and add logs

#############################################################################

# Module:       %W%     Date: %D%

# Author:       Lester B. Knutsen

#               Advanced DataTools Corporation

# Discription:  Script to Creat a training environment Informix IDS

#               database server

#############################################################################



#############################################################################

# Set Global environment variables

#############################################################################



## Set the location of Informix Programs

INFORMIXDIR=/u3/informix7

export INFORMIXDIR



## Add the Informix Programs to your PATH

PATH=$INFORMIXDIR/bin:$PATH:/usr/ccs/bin

export PATH



## Set the Database Server

INFORMIXSERVER=train2

export INFORMIXSERVER



## Set the Informix Configuration File

ONCONFIG=onconfig.train2

export ONCONFIG



#############################################################################

# Check that this is the correct ONCONFIG and INFORMIXSERVER

#############################################################################



set `grep "^DBSERVERNAME" $INFORMIXDIR/etc/$ONCONFIG`

if [ "$2" != "$INFORMIXSERVER" ]

then

        echo "Invalid INFORMIXSERVER: $INFORMIXSERVER"

        exit

fi

echo "Creating and Initializing INFORMIXSERVER: $INFORMIXSERVER"

echo "Press RETURN to continue"

read ans



#############################################################################

# Create the disk devices - this training server uses cooked files

# but you could replace these command with the commands to use raw files.

#############################################################################



touch /u3/dev/rootdbs2

touch /u3/dev/logdbs2

touch /u3/dev/rootdbsM2

touch /u3/dev/data2dbs2

touch /u3/dev/tempdbs2



# Set owner to informix - group informix

chown informix:informix /u3/dev/rootdbs2

chown informix:informix /u3/dev/logdbs2

chown informix:informix /u3/dev/rootdbsM2

chown informix:informix /u3/dev/data2dbs2

chown informix:informix /u3/dev/tempdbs2



# Set permissions to read/write owner and group only

chmod 660 /u3/dev/rootdbs2

chmod 660 /u3/dev/logdbs2

chmod 660 /u3/dev/rootdbsM2

chmod 660 /u3/dev/data2dbs2

chmod 660 /u3/dev/tempdbs2





#############################################################################

# Initialize the rootdbs - after this anything that was there is wipped out

#############################################################################



oninit -i



## must sleep long enough for the sysmaster database to be created or the 

## next step will fail.

sleep 200



        FuGEN ad# Display the log

onstat -m



## now shutdown to single user mode

onmode -sy



# Display status

onstat -



#############################################################################

# Creat the additional Dbspaces

#############################################################################



echo "Creating logdbs..."

## Create dbspace for logical logs

onspaces -c -d logdbs -p /u3/dev/logdbs2 -o 0 -s 25000



echo "Creating datadbs..."

## Create dbspace for data

onspaces -c -d datadbs -p /u3/dev/data2dbs2 -o 0 -s 50000



echo "Creating tempdbs..."

## Create dbspace from temp tables

onspaces -c -d tempdbs -t -p /u3/dev/tempdbs2 -o 0 -s 10000



#############################################################################

# Create additional logical logs in logsdbs

#############################################################################



echo "Creating additional Logical Logs"

onparams -a -d logdbs -s 4000

onparams -a -d logdbs -s 4000

onparams -a -d logdbs -s 4000

onparams -a -d logdbs -s 4000

onparams -a -d logdbs -s 4000

onparams -a -d logdbs -s 4000



echo "Creating archive to activate new Logical Logs"

ontape -s



#############################################################################

# Show message log and status

#############################################################################



onstat -m 



echo "IDS Configuration complete"



#############################################################################

ONTAPE is the basic utility to backup and restore the whole IDS server. The backup may be performed while the system is running and while users are accessing and updating data. In addition to your basic backup and restore ONTAPE performs a few other functions:
 

Backups the logical logs.

Provides a utility to change the logging mode of a database.

Performs a restore to start Data Replication.
 

IDS backup strategies and procedures are very important and merit a whole chapter. This is just a quick overview of ONTAPE's syntax and a few options. Figure 36 contains the syntax for ONTAPE.
 

Figure 36: ONTAPE syntax

ontape { -a | -c | -l | -p | -r [-D DBspace_list] | -s [-L archive_level]

[-A database_list] [-B database_list] [-N database_list] [-U database_list] }

-a Automatic backup of logical logs

-c Continuous backup of logical logs

-l Logical restore

-p Physical restore for Data Replication (HDR)

-r Full restore DBspaces/BLOBspaces as listed

-s Archive full system

-A Set the following database(s) to ansi logging

-B Set the following database(s) to buffered logging

-N Set the following database(s) to no logging

-U Set the following database(s) to unbuffered logging

The basic ONTAPE restore option restores the whole database server. You cannot restore just one table or database. Use ONUNLOAD and ONLOAD to perform database and table level backups.
 

ONTAPE can only restore to a the same dbspace configuration. The disk layout must match exactly the disk layout when the backup was made. 
 

The ONTAPE backup is binary and can only be restored to a computer which is binary compatible and using the same version of Informix. 
 

Backing up the IDS server

ONTAPE provides a way for you to backup the whole database server while it is running. ONTAPE will keep track of all changes made during its backup, and during a restore rollback any incompletely backed-up changes. The command to start a backup is:
 
 

ontape -s

TAPEDEV /dev/tapedev # Tape device path 

TAPEBLK 16 # Tape block size (Kbytes)

TAPESIZE 1024000 # Maximum amount of data to put on tape (Kbytes)
 

Changing TAPEDEV to /dev/null and performing a backup will reset IDS's internal parameters without performing an actual backup. 
 

Using ONTAPE requires a dedicated terminal and tape drive during backups only. It will also require an operator to monitor backups and change tapes as needed. For the backups to be used in a restore, the tapes must be labeled carefully and coordinated with Logical Log backup. Figure 37 contains an example of an IDS backup using ONTAPE.
 

Figure 37: ONTAPE backup

informix@merlin >ontape -s

Please enter the level of archive to be performed (0, 1, or 2) 0

Please mount tape 1 on /dev/rmt/0 and press Return to continue ...

10 percent done.

20 percent done.

30 percent done.

Tape is full ...

Please label this tape as number 1 in the arc tape sequence.

This tape contains the following logical logs:

9

Please mount tape 2 on /dev/rmt/0 and press Return to continue ...

Informix does not officially support backing up to disk with ONTAPE but it can be done. Figure 38 contains an example of a shell script that could be used to backup IDS to a disk file. This could be run by the UNIX Cron facility to automatically backup your server at a set time. However, the backup must be small enough to fit on disk.
 

Figure 38: Shell script to backup IDS to disk

####################################################################

# Shell script to backup Informix IDS to disk

####################################################################

## Set Informix environment variables 

## change these to match your configuration

INFORMIXDIR=/usr/informix7.1

export INFORMIXDIR

PATH=$INFORMIXDIR/bin:$PATH

export PATH

ONCONFIG=onconfig

export ONCONFIG

INFORMIXSERVER=online1

export INFORMIXSERVER



## Echo message to log file

echo "Archive Informix IDS for $INFORMIXSERVER"



## Check for valid backup device, prevents accidentally overwriting

set `grep "^TAPEDEV" $INFORMIXDIR/etc/$ONCONFIG`

if [ "$2" != "/u3/backup/online1.bak" ]

then

        echo "Invalid TAPEDEV $2"

        date

else

        echo "Archive to TAPEDEV $2"

        date

## Start ontape and respond to prompts - the following spacing is key

## There must be a 0 for the level followed by blank line for the

## the response to the prompt.

{

ontape -s <<EOF

0



EOF

} | tail -5

## Only read the last 5 lines to prevent filling up your log when errors

echo "Archive Completed"

fi

informix@merlin >ontape -r



Please mount tape 1 on /dev/rmt/0 and press Return to continue ... 



Archive Tape Information



Tape type:      Archive Backup Tape 

Online version: INFORMIX-OnLine Version 7.13.UC2   

Archive date:   Fri Sep 27 17:48:39 1996 

User id:        informix 

Terminal id:    /dev/pts/0 

Archive level:  0 

Tape device:    /dev/rmt/0 

Tape blocksize (in k): 16 

Tape size (in k): 2000000 

Tape number in series: 1 



Spaces to restore:1 [rootdbs           ] 



Archive Information



INFORMIX-OnLine Copyright© 1986-1996  Informix Software, Inc.

Initialization Time       09/03/96 17:31:15

System Page Size          2048

Version                   4

Archive CheckPoint Time   09/27/96 17:48:44



Dbspaces

number   flags    fchunk   nchunks  flags    owner    name

1        1        1        1        N        informix rootdbs           





Chunks

chk/dbs offset   size     free     bpages   flags pathname

1   1   0        50000    38641             PO-   /u3/dev/dbspace713



Continue restore? (y/n)y

Do you want to back up the logs? (y/n)n

Restore a level 1 or 2 archive (y/n) n

Do you want to restore log tapes? (y/n)y



Roll forward should start with log number 9



Please mount tape 1 on /dev/rmt/0 and press Return to continue ... 

Do you want to restore another log tape? (y/n)n



Program over.

informix@merlin >onstat - 



INFORMIX-OnLine Version 7.13.UC2   -- Quiescent -- Up 00:10:35 -- 8976 Kbytes

Performing automatic backup of logical logs.

Please mount tape and press Return to continue ...

This tape contains the following logical logs:

         11 - 12



Please label this tape as number 1 in the log tape sequence.

Please mount next tape and press Return to continue ...

*** The tape was not changed ***

Please mount next tape and press Return to continue ...

This tape contains the following logical logs:

         12 - 14



Please label this tape as number 2 in the log tape sequence.

Please mount next tape and press Return to continue ...

This tape contains the following logical logs:

         14 - 16



Please label this tape as number 3 in the log tape sequence.

ONTAPE is also used to change the logging mode of a database. To change a database from no logging to some form of logging requires a backup. The command to perform a backup and change a database from no logging to buffered logging is:
 

ontape -s -B database_name

If you want to change the logging mode of a database without performing a complete backup simply change TAPEDEV in your ONCONFIG file to "/dev/null". Then run the ONTAPE command listed above. Be sure to change TAPEDEV back to its original after you are done.
 

ONUNLOAD and ONLOAD - Unloading and loading databases and tables
 

The ONUNLOAD utility and the corresponding ONLOAD utility provide a way to save whole tables or databases in a binary format to tape or disk. These two utilities copy whole pages from dbspaces and save the results in binary format. They must work together. Only ONLOAD can read and load the results of an ONUNLOAD. If you need to unload data in ASCII or text format use the SQL unload statement of DBEXPORT.
 

The advantage of these two utilities is that they are fast. They copy whole pages of data including existing indexes structures. This makes it very fast to reload because indexes do not need to be rebuilt. Also, since the output is stored in binary format, there is some security in the data being protected.
 

There are a few drawbacks to these two utilities:
 

Data is not portable. It can only be loaded using the same version of IDS on the same type of machine. Since the data is stored in binary format the operations need to be performed on computers that are binary compatible.
 

Data is not compressed. Since the data is copied in whole pages, empty data space and empty index structures on a page remain and are reloaded on the target system. SQL unload and load will rebuild the data on pages and rebuild all indexes compressing the data. 
 

The utilities will require an exclusive lock on the table or database being unloaded and loaded.
 

These utilities are useful in several ways:
 

They provide fast table level backups.

They provide fast database level backups.

When transferring tables or databases to other systems with different dbspace layouts.

When moving databases or tables from one dbspace to another.
 
 
 

Figure 41 contains the syntax for the onunload command and Figure 42 contains the syntax for the onload command.
 

Figure 41: ONUNLOAD syntax

onunload [-l] [-t <tape device>] [-b <block size>] [-s <tape size>]

<database> [:[<owner>.]<table>]

-l Use logical log tape configuration from ONCONFIG file

-t Tape devices overriding TAPDEV in ONCONFIG

-b Tape block size overriding size in ONCONFIG

-s Tape size overriding size in ONCONFIG

onload [-l] [-t <tape device>] [-b <block size>] [-s <tape size>]

[-d <Dbspace>]

<database>[:[<owner>.]<table>] [{-i <old indexname> <new indexname>}]

[{-fd oldDBspname newDBspname}] 

[{-fi indexname oldDBspname newDBspname}]

-l Use logical log tape configuration

-t Tape devices

-b Tape block size

-s Tape size

-d DBspace name

-i Rename index during load

-fd Change data fragment dbspace

-fi Change index fragment dbspace

First we need to unload the items table. ONUNLOAD requires that the file exist if you are unloading to disk. Our first step is to create an empty file using the UNIX touch command. If you are unloading to tape you can skip this step because the tape device already exists.
 

touch items.onunload

onunload -t items.onunload stores7:items

This will create a binary image of the items table using our file.
 

Now we need to use SQL and dbaccess to drop the items table. We are piping the SQL statements to dbaccess.
 

dbaccess stores7 - <<EOF

drop table items;

EOF

onload -t items.onunload -d itemsdbs stores7:items

This will create the items table in the new dbspace.
 

Conclusion
 

These utilities provide a powerful toolkit for the DBA to care for and monitor a database server. 
 

Lester Knutsen lester@advancedatatools.com Phone (703) 256-0267

Advanced DataTools Corporation
Web: www.advancedatatools.com