Washington Area Informix Users Group

Home

About Us

Upcoming Events

Current Newsletter

Newsletter Archive

Presentations

Become a Member

Sponsors

Links

April 1999 Newsletter

Volume 9, No. 2


Highlights of This Issue

Informix World Wide User Conference

WAIUG Training Day 1999, by Peter Wages and Nick Nobbe

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


Next Meeting Agenda - June 16, 1999


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.
 
 

Newsletter Sponsorship




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.


Get Smart This Summer!

Attend the 1999 Informix Worldwide User Conference

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.



WAIUG Training Day 1999, by Peter Wages and Nick Nobbe


Exploring the Informix OnLine Utilities (Part 3)

by Lester Knutsen




INFORMIX-OnLine comes with a set of powerful command line utilities that enable you to monitor, tune, and configure your database server. This is the third in a series of articles I have written that will focus on eight of these utilities, and present ways to use them to optimize your performance as a database administrator. 
 

The command line utilities and the order in which we will discuss them is as follows:
 

  • ONSTAT - shows shared memory and server statistics
  • ONCHECK - checks and repairs disk space
  • ONMODE - changes Servers's operating mode
  • ONLOG - logical log debugging tool
  • ONINIT - initialize and start up the database server
  • ONSPACES - configure dbspaces and chunks
  • ONPARAMS - configure logs
  • ONTAPE - backup and restore utility
  • ONLOAD - loads databases and tables
  • ONUNLOAD - unloads databases and tables

  •  
The first article in this series was on the ONSTAT utility and was published in January 1998 (Vol 8, No 1). The next focused on ONCHECK and was published in April 1998 (Vol 8, No 2). Both of these articles are available at the user group web site. (http://www.iiug.org/~waiug/)
 

ONSTAT - shows server statistics
 

ONSTAT is the command line utility that gets the most usage. It reads IDS's shared memory structures and provides lots of useful information about the state of your server. It does not place any locks on shared memory structure and uses very little overhead, so you can use it at any time. The information is current at the time the command is issued, and the data can change as you are using the command. There are more options for ONSTAT than any other Informix utility. Many of the options are debugging parameters that are not well documented and don't make sense to the average DBA. There are also many very useful options that help you manage your IDS server. 
 

ONCHECK - Check and display information about IDSs's disk space
 

ONCHECK is the tool to check and display information about your dbspaces, blobspaces, chunks, tables, indexes, and disk pages. The purpose of this utility is to insure that your database server disk space has no inconsistencies. I like to think of this as the database version of the UNIX utility 'fsck' which checks file systems, or the DOS utility 'chkdsk' which checks DOS disk space. ONCHECK will place locks on all tables and databases that it needs to access. In some cases it will place an exclusive lock on a database or table and prevent other users from accessing the data. You need to be careful when you run ONCHECK to make sure it will not disrupt your users' work. It is a good idea to run ONCHECK when the IDS Server is in quiescent mode so it does not conflict with other users. 
 

ONMODE - change IDS operating mode
 

The ONMODE utility has several key functions: it changes the operating mode of IDS, shuts IDS down, allows you to change some configuration parameters on the fly, and provides a means to kill user database connections. Figure 25 provides the complete syntax to the ONMODE command.
 

Figure 25: ONMODE syntax


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

Shutting down the database server

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'




When all the electrical power is about to fail, or the computer is shutting down for whatever reason, you don't have time to ask all users to log off the database server. And if you do not shutdown IDS, it will crash in an inconsistent state, with data in memory buffers that is not been written to disk, and users in the middle of transactions. When IDS is later restarted in will start a recovery mode to clean up from the crash, but this can take time and there may be problems.
 

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




Changing IDS modes
 

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 shows an example of identifying the session id for the user 'lester' using 'onstat -u' and killing the session with 'onmode -z' The session id is 190.
 

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


When you terminate a user's session, their processing may not stop immediately. If they are performing a large query, data may be buffered up on the user's program and they may continue to receive data. Once the buffer is empty, the user will receive an error message indicating the database connection was lost.
 

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)


ONINIT - initialize and start-up functions
 

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.


Initializing the root dbspace

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 shows the output to the IDS message log of a successful startup. There are two key messages you need to look for in the message log when IDS starts. One is the message 'Physical Recovery Completed: 0 Pages Restored', and the other is 'Logical Recovery Complete'. The zeros for 'Pages Restored' and 'Rolled Back' mean that everything was shutdown cleanly and restarted cleanly with no loss of users' work.
 

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.


ONSPACES - Adding, deleting, and changing mirroring of dbspaces
 

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




Note: You can only drop a dbspace if it is completely empty.
 

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


In order to drop a logical log, the log must be backed up and cannot contain the current checkpoint or current logical log.
 

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 - the IDS backup and restore utility
 

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


Limitations of ONTAPE

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
ONTAPE uses the parameters in the ONCONFIG file to determine the tape device, block size and tape size. These parameters are:
 
 
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 ...




Backing up to disk

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


Restoring an IDS server

IDS must be off-line to perform a restore. The restore will wipe out all your current data and configuration. The disk layout must be exactly configured the same as when you created the backup.
 

Note: Before you begin, write protect your tape. The restore process has a confusing prompt asking "do you want to backup your logical logs?". I know DBA's who have responded yes to this prompt and accidentally wiped out their restore tape.

The command to start a restore is:

ontape -r
 

Figure 39 shows the full dialog of prompts during the restore process. 
 

Figure 39: Performing an IDS restore



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


First ONTAPE will display the disk configuration as it was when the backup was performed. This gives you a chance to verify that you have created the correct devices and links.
 

Next ONTAPE will prompt you if you would like to backup logical logs. This prompt is very confusing if you have not done this a few times. If your system had crashed and IDS was able to backup additional logs it will help you in the recovery process. Put a NEW tape in the drive and respond yes. Do NOT leave your restore tape in or IDS may overwrite it. If you do not want to backup any current logs, respond NO.
 

Now ONTAPE will start the restore. This is no progress report on the restore like there is on the backup. Be patient and wait. If more than one tape is required you will be prompted for it.
 

After the level 0 tape has been restored ONTAPE will ask if you have a level 1 or 2 backup to restore. 
 

When all backup levels have been restored, ONTAPE will prompt you for logical log tapes to restore. Start with the tape containing the logical log you are prompted for. You cannot skip logical logs or restore them in a different order. If you do not have any logical log backups simply respond NO to these prompts.
 

Finally, IDS will start to roll forward, or roll back any transactions necessary. When this is completed the IDS server will be in quesicent mode ready for you to check.
 

Backing up logical logs

ONTAPE is also used to backup logical logs. The logical log backup device is controlled by the following parameters in your ONCONFIG file:
 
 

LTAPEDEV /dev/tapedev # Logical Log tape device path (e.g /dev/rmt/0)

LTAPEBLK 16 # Log tape block size (Kbytes)

LTAPESIZE 10240 # Max amount of data to put on log tape (Kbytes)
 


When you do not want to backup Logical Logs to tape, set LTAPEDEV to equal "/dev/null". IDS understands this and frees the logical logs as soon as they can be reused without a backup. Otherwise, you must backup your logical logs before they can be reused.
 

Note: When all Logical Logs are full IDS will halt all processing until you backup the logs. This will stop all user activity.
 

There are two forms of Logical Log Backup:

1) Continuous Backup (ontape -c) - this runs the ontape process backing up logical logs non-stop until you stop.

2) Automatic Backup (ontape -a) - the ONTAPE process runs backing up all logical logs that need to be backed up. Once all logs have been backed up the process stops.
 

Issues with continuous backup of logical logs to tape

Continuous backups requires a dedicated terminal or window in which it runs. This is where it will display tape change prompts and expect an operator to respond to these prompts. It also requires a dedicated tape drive and an operator who will monitor the progress of its backups. The operator must carefully label tapes so they can be used in a restore. To stop continuous backups simple enter "Control-C" or the interrupt character on your system.
 

Anytime the continuous backups is aborted and restarted a new tape must be used or else the old tapes will be overwritten. Each execution of continuous or automatic backups requires a new tape. ONTAPE backups cannot append to the end of the last tape. Continuous backups must also be restarted with a new tape after your system is rebooted.
 

Issues with automatic manual backup of logical logs to tape

Automatic backup of logs requires a dedicated terminal and tape drive only during the actual backups. This tape drive may be shared with other activities. However, it still requires an operator to monitor the logs, and start a backup before they all become full. It also requires careful labeling of tapes and coordinating with ontape archives. Figure 40 shows the screen display of the automatic backup.
 
 
 

Figure 40: Automatic backup of logical logs



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.


Changing logging mode for a database
 

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




Figure 42: ONLOAD syntax


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


As an example of using these utilities let's take the steps required to move the items table in the stores7 database from one dbspace to another. These utilities are perfect for this task because they are fast.
 

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
Next we execute the unload:
 
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

The final step is to reload the items table into a new dbspace. For this example we will load it into a dbspace named itemsdbs.

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

 


This newsletter is published by the Washington Area Informix User Group
Lester Knutsen, President/Editor
Washington Area Informix User Group
4216 Evergreen Lane, Suite 126, Annandale, VA 22003
Phone: 703-256-0267

 
 
 
 

lester@advancedatatools.com