jULY 1996 Newsletter

Volume 6, No. 3

Highlights of This Issue

Exploring the SYSMASTER Database (Part 1), by Lester Knutsen

Informix Event Alarms, by Kevin Fennimore and Ivy Ho

Testing an Informix DBA, by Madhu Reddy

Informix Installation and OnLine Configuration Procedures, by Mike Tinius

WAIUG History

Next Meeting - INFORMIX-NewEra 3.0

Date and Time:		September 11, 1996 at 9:00 a.m. to 12:00 noon

Location:		Informix Commercial Division Office, 

			Two Democracy Plaza

			6707 Democracy Blvd, Suite 500, Bethesda, MD

User Group Activities at the Informix Worldwide User Conference

I. International Informix User Group (IIUG) Exhibit Booth - Check for location in the Conference Guide

II. Track Session: Informix User Groups on the Move - Wednesday, July 10 at 2:30pm

III. BOF Session: International Informix Users Group Meeting - Wednesday, July 10 at 5:00pm

Special Edition - WAIUG Polo Shirts

Newsletter Sponsorship

Elections to the Board of Directors

Benefits for Members

President/Editor: Lester Knutsen, Email: lester@access.digex.net, Phone: 703-256-0267

Membership: John Petruzzi, Phone: 703-490-4598

Treasurer/Secretary: Sam Hazelett, Phone: 703-277-6882

Programs/Sponsors: Nick Nobbe, Phone: 202-707-0548

Exploring the SYSMASTER Database (Part 1)

One of the great new features that appeared in INFORMIX-OnLine 6.x and 7.x is a database called the sysmaster that contains tables that can be used for monitoring your system. These are referred to as the System Monitoring Interface (SMI) tables. This article is the first in a series that will take a look at some of the tables and views that are in this database. We will describe an SQL script to get information about who is using what database, and look at the server information contained in the sysmaster database. In future issues we will look at user sessions, locks, disk space and table space.

The examples and references to this database are based on OnLine 7.10.UD1. I have also tested some of the examples with versions 7.10.UC1, 7.12.UC1 and 7.13.UC1. There are some minor changes between versions in the undocumented features and structures of these tables. Only 15 of these tables are documented in the INFORMIX-OnLine Dynamic Server, Administrators Guide, Volume 2, chapter 39. A warning: Many of the features discussed in this article are undocumented and may change in future versions or may not turn out to be the way I interpret them.

Dbwho (Who is using which database)

My interest in this database started while consulting on a project for a development group where I needed to know who had a database open and which workstation they where using to connect to the database. This was a development environment, and in order to make updates to the database schema, I would have to get the developers to disconnect from the database. The "onstat -u" utility would tell me which users were connected to the server, but not what database and what workstation they were using. "Onstat -g ses" told me the user and workstation, but not the database. "Onstat -g sql told me the session id and database, but not the user name and workstation. All the information I wanted was in the sysmaster database and could be retrieved with SQL queries. The following query shows the database, who has it open, the workstation they are connected from, and the session id.

Every user that opens a database opens a shared lock on the row in the sysdatabases table of the sysmaster database that points to that database. First we need to find all the locks in syslocks on the sysdatabases table. This gives us the rowid in sysdatabase which has the database name. Finally we join with the table syssessions to get the username and hostname. I put all this together in a shell script that could be run from the unix prompt and called it dbwho. Figure 1 contains the shell script.

Figure 1. Dbwho shell script.

:

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

# Program: dbwho

# Author:  Lester Knutsen

# Date:    10/28/1995

# Description: List database, user and workstation of all db users

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



echo "Generating list of users by database ..."

dbaccess sysmaster - <<EOF

select

        sysdatabases.name database,

        syssessions.username,

        syssessions.hostname,

        syslocks.owner sid

from  syslocks, sysdatabases , outer syssessions

where syslocks.rowidlk = sysdatabases.rowid

and   syslocks.tabname = "sysdatabases"

and   syslocks.owner = syssessions.sid

order by 1;

EOF   

One of the first things you will notice is that this script is slow. This started me digging into what was causing the slow performance. Running this query with set explain turned on (this shows the query optimizer plan) shows that there is a lot of work going on behind the scenes. Syslocks is a view, and it takes a sequential scan of six tables to produce the view. A temp table is created to hold the results of the syslocks view, and this is then joined with the other two tables. The tables sysdatabase and syssessions are also views. And the view syssessions calls a stored procedure, "bitval" in its creation. Figure 2 contains the output from turning set explain on. In spite of sometimes being a bit slow, these tables are a tremendous value and make it much easier to monitor your database server.

Figure 2: Dbwho sqexplain.out

QUERY:

------

create view "informix".syslocks 	(dbsname,tabname,rowidlk,keynum,type,owner,waiter) 

as 	select x1.dbsname ,x1.tabname ,x0.rowidr ,x0.keynum ,

	x4.txt [1,4] ,x3.sid ,x5.sid 

	from 	"informix".syslcktab x0 ,

		"informix".systabnames x1 ,

		"informix".systxptab x2 ,

		"informix".sysrstcb x3 ,

		"informix".flags_text x4 ,

		outer("informix".sysrstcb x5 ) 

	where ((((((x0.partnum = x1.partnum ) 

	AND (x0.owner = x2.address ) ) 

	AND (x2.owner = x3.address ) ) 

	AND (x0.wtlist = x5.address ) ) 

	AND (x4.tabname = 'syslcktab' ) ) 

	AND (x4.flags = x0.type ) ) ;

Estimated Cost: 713

Estimated # of Rows Returned: 51



1) informix.syslcktab: SEQUENTIAL SCAN

2) informix.flags_text: SEQUENTIAL SCAN

    Filters: informix.flags_text.tabname = 'syslcktab' 

DYNAMIC HASH JOIN 

    Dynamic Hash Filters: informix.syslcktab.type = informix.flags_text.flags 

3) informix.systxptab: SEQUENTIAL SCAN

DYNAMIC HASH JOIN 

    Dynamic Hash Filters: informix.syslcktab.owner = informix.systxptab.address 

4) informix.systabnames: SEQUENTIAL SCAN

    Filters: informix.systabnames.tabname = 'sysdatabases' 

DYNAMIC HASH JOIN 

    Dynamic Hash Filters: informix.syslcktab.partnum = informix.systabnames.partnum 

5) informix.sysrstcb: SEQUENTIAL SCAN

DYNAMIC HASH JOIN (Build Outer) 

    Dynamic Hash Filters: informix.systxptab.owner = informix.sysrstcb.address 

6) informix.sysrstcb: SEQUENTIAL SCAN

DYNAMIC HASH JOIN 

    Dynamic Hash Filters: informix.syslcktab.wtlist = informix.sysrstcb.address 



QUERY:

------

select  sysdatabases.name database,

        syssessions.username,

        syssessions.hostname,

        syslocks.owner sid

from  syslocks, sysdatabases , outer syssessions

where syslocks.rowidlk = sysdatabases.rowid

and   syslocks.tabname = "sysdatabases"

and   syslocks.owner = syssessions.sid

order by 1



Estimated Cost: 114

Estimated # of Rows Returned: 11

Temporary Files Required For: Order By  



1) (Temp Table For View): SEQUENTIAL SCAN

2) informix.sysdbspartn: INDEX PATH

    (1) Index Keys: ROWID

        Lower Index Filter: informix.sysdbspartn.ROWID = (Temp Table For          View).rowidlk 

3) informix.sysscblst: INDEX PATH

    (1) Index Keys: sid (desc) 

        Lower Index Filter: informix.sysscblst.sid = (Temp Table For          View).owner 

4) informix.sysrstcb: AUTOINDEX PATH

    Filters: informix.bitval(informix.sysrstcb.flags ,'0x80000' )= 1 

    (1) Index Keys: scb 

        Lower Index Filter: informix.sysrstcb.scb = informix.sysscblst.address 

Overview of the Sysmaster database

The sysmaster database keeps track of information about the database server just like the system tables keep track of information in each database. This database is automatically created when you initialize OnLine. It includes tables for tracking two types of information: the System Monitoring Interface (SMI) tables, and the On-Archive catalog tables. This article will focus on the SMI tables. There is a warning in the documentation not to change any information in these tables as it may corrupt your database server. Also there is a warning that OnLine does not lock the these tables and all selects from this database will use an isolation level of DIRTY READ. This means that the data can change dynamically as you are retrieving it. This also means that selecting data from the sysmaster tables does not lock any of your users from processing their data. The SMI tables are described as pseudo-tables which point directly to the shared memory structures in OnLine where the data is stored. That means they are not actually on disk. However, because many of the SMI tables are really views, selecting from them does create temporary tables and disk activity.

The script $INFORMIXDIR/etc/sysmaster.sql contains the SQL statements to create the sysmaster database. The process of creating it is interesting and outlined as follows:

• create real tables with the structures of the pseudo tables

• copy the structure of the real tables to temp tables

• drop the real tables

• update the systables.partnum to indicate they point to pseudo tables in shared memory

• create the flags_text table which has the interpretations for flags used in the SMI tables

• create the stored procedures used to create the views, two of which are interesting:

        bitval() is a stored procedure for getting the boolean flag values

           l2date() is a stored procedure for converting unix time() long values to dates

• create the SMI views
• create the on-archive tables and views

There are 125 tables and views in the sysmaster database. One is the flag_text table, 37 are On-Archive tables, and the rest are the SMI tables and views. Figure 3 lists all the SMI tables and views in OnLine 7.10.UD1.

Figure 3. SMI tables and views.

Table Name        Type       Status

sysadtinfo        Table      Documented pg 39-9 	Auditing configuration

sysaudit          Table      Documented pg 39-10	Auditing event masks

sysbtcreq         Table      Undocumented

sysbufhdr         Table      Undocumented

syscfgtab         Table      Undocumented

syschfree         Table      Undocumented

syschktab         Table      Undocumented

sysconblock       Table      Undocumented

sysconlst         Table      Undocumented

syscrtadt         Table      Undocumented

sysdbspartn       Table      Undocumented

sysdbstab         Table      Undocumented

sysdic            Table      Undocumented

sysdrcb           Table      Undocumented

sysdsc            Table      Undocumented

syslcktab         Table      Undocumented

syslogfil         Table      Undocumented

syslrus           Table      Undocumented

sysmchktab        Table      Undocumented

sysmtxlst         Table      Undocumented

sysopendb         Table      Undocumented

syspaghdr         Table      Undocumented

sysplog           Table      Undocumented

syspoollst        Table      Undocumented

sysprc            Table      Undocumented

sysptnbit         Table      Undocumented

sysptncol         Table      Undocumented

sysptnext         Table      Undocumented

sysptnhdr         Table      Undocumented

sysptnkey         Table      Undocumented

sysptntab         Table      Undocumented

sysrawdsk         Table      Undocumented

sysrstcb          Table      Undocumented

sysscblst         Table      Undocumented

syssdblock        Table      Undocumented

sysseglst         Table      Undocumented

sysshmem          Table      Undocumented

sysshmhdr         Table      Undocumented

sysshmvals        Table      Undocumented

syssltdat         Table      Undocumented

sysslttab         Table      Undocumented

syssqlstat        Table      Undocumented

syssqscb          Table      Undocumented

systabnames       Table      Documented pg 39-26	Database, owner and tablenames

systcblst         Table      Undocumented

systraces         Table      Undocumented

systwaits         Table      Undocumented

systxptab         Table      Undocumented

sysvplst          Table      Undocumented

syschkextents     View       Undocumented

syschkio          View       Documented pg 39-11	Chunk I/O statistics

syschunks         View       Documented pg 39-12	Chunk information

sysconditions     View       Undocumented

sysconfig         View       Undocumented

sysconq           View       Undocumented

sysdatabases      View       Documented pg 39-13	Database information

sysdbspaces       View       Documented pg 39-15	Dbspace information

sysdiccache       View       Undocumented

sysdistcache      View       Undocumented

sysdri            View       Documented pg 39-15	Data replication information

sysextents        View       Documented pg 39-15	Table extent information

syslocks          View       Documented pg 39-17	Lock information

syslocktab        View       Undocumented

syslogs           View       Documented pg 39-17	Logical log information

sysmutexes        View       Undocumented

sysmutq           View       Undocumented

sysphyspaghdrs    View       Undocumented

syspools          View       Undocumented

sysproccache      View       Undocumented

sysprofile        View       Documented pg 39-18	System profile information and statistics

sysptprof         View       Documented pg 39-20	Table profile information and statistics

syssegments       View       Undocumented

syssesprof        View       Documented pg 39-21	User sessions profile statistics

syssessions       View       Documented pg 39-23	User session information

sysseswts         View       Documented pg 39-25	User session wait list and statistics

syssqexplain      View       Undocumented

syssqlcurall      View       Undocumented

syssqlcurses      View       Undocumented

systabextents     View       Undocumented

systabinfo        View       Undocumented

systabpaghdrs     View       Undocumented

systabpagtypes    View       Undocumented

systhreads        View       Undocumented

systhreadwaits    View       Undocumented

systrans          View       Undocumented

sysuserthreads    View       Undocumented

sysvpprof         View       Documented pg 39-27	Virtual processor information and statistics

The remainder of the articles in this series will examine some of the more interesting tables in the sysmaster database and look at some of the things you can do with them. Figure 4 contains a diagram of some of the key tables and their relationships.

Server Configuration Parameters: syscfgtab

The table syscfgtab contains information from your ONCONFIG file. Have you ever needed to know from within a program how your server was setup? Or, what TAPEDEV is set to? This is a base table which is also used to create a view sysconfig. This view has all rows where the cf_flag equals 0 which are the supported configuration parameters.

Table syscfgtab

 	cf_id           integer,        unique numeric identifier

 	cf_name         char(18),       config parameter name    

 	cf_flags        integer,        flags, 0 = in view sysconfig

 	cf_original     char(256),      value in ONCONFIG at

boottime

 	cf_effective    char(256),      value effectively in use    



 	cf_default      char(256)       value by default

	To find out what the current tape device is:

		select cf_effective from syscfgtab where cf_name =

"TAPEDEV";

	To find the server name:

		select cf_effective from syscfgtab where cf_name =

"DBSERVERNAME";

	To find out if data replication is turned on:

		select cf_effective from syscfgtab where cf_name =

"DRAUTO"; 

Server Profile Information: sysprofile

This table is a view based on values in a table called syshmhdr. This contains information like the output of the utility "onstat -p". As with this utility, when you zero out the statistics with "onstat -z", all values are reset to zero.

View sysprofile

        name            char(16),      profile element name 

        value           integer        current value

One of the best uses of this data is to develop alarms when certain values fall below acceptable levels. The Informix documentation says that tables in the sysmaster database do not run triggers. This is because the updates to these tables take place within OnLine shared memory and not through SQL which activates triggers. However, you can create a program to poll this table at specified intervals to select data and see if it falls below your expectations.

Logical Logs Information: syslogs

Syslogs is a view based on the table syslogfil. This is an example where the SMI views are a great tool in presenting the data in a more understandable format. Syslogfil has a field called flags which contains status information encoded in boolean smallint. The view syslogs decodes that data into six fields, is_used, is_current, is_backed_up, is_new, is_archived, is_temp, with a 1 if true or a 0 if false.

View syslogs

        number          smallint,       logfile number 

        uniqid          integer,        logfile uniqid 

        size            integer,        pages in logfile 

        used            integer         pages used in logfile 

        is_used         integer,        1 for used, 0 for free

        is_current      integer,        1 for current

        is_backed_up    integer,        1 for backuped

        is_new          integer,        1 for new

        is_archived     integer,        1 for archived

        is_temp         integer,        1 for temp

        flags           smallint,       logfile flags

Sysvpprof is another view that is more readable than the underlying table sysvplst. As with the view syslogs in the above paragraph, this view has data that is converted to make it more understandable. This time the flags are converted to text descriptions from the flags_text table.

View sysvpprof

        vpid            integer,        VP id

        txt             char(50)        VP class name

        usecs_user      float,          number of unix secs of user time

        usecs_sys       float,          number of unix secs of system time

The following query on the base table sysvplst achieves the same results as the view.

	select  vpid,

              txt[1,5] class,

              pid,

              usecs_user,

              usecs_sys,

              num_ready

	from sysvplst a, flags_text b

	where a.flags != 6

	and a.class = b.flags

	and b.tabname = 'sysvplst';

  

Output:

       vpid class         pid     usecs_user      usecs_sys   num_ready

        

          1 cpu           335         793.61          30.46           0  

          2 adm           336           0.02           0.11           0  

          3 lio           337           1.15           5.98           0  

          4 pio           338           0.19           1.13           0  

          5 aio           339           0.94           4.27           0  

          6 msc           340           0.15           0.14           0  

          7 aio           341           0.81           5.72           0  

          8 tli           342           1.79           3.02           0  

          9 aio           343           0.52           2.50           0  

         10 aio           344           0.28           1.16           0  

         11 aio           345           0.09           0.86           0  

         12 aio           346           0.16           0.48           0  

In the next newsletter we will discuss some of the other tables in the sysmaster database.

Lester Knutsen 					Email:  lester@access.digex.net

Advanced DataTools Corporation		Phone:  703-256-0267

4216 Evergreen Lane, Suite 136, Annandale, VA 22003

INFORMIX EVENT ALARMS

Informix-OnLine DSA version 7 introduced a new feature called Event Alarms. This feature enables the online log to be monitored by having a program called each time a message is written to the log file. This feature is useful for monitoring the general health of the OnLine system and generating alerts when there are problems.

In order to activate the Event Alarm feature, a value must be specified in the onconfig file for the parameter ALARMPROGRAM. The value specified is the full path name to the program which will be called when a message is written to the log file. This program, whether it is a script or a compiled program, is called and sent four or five parameters. These parameters are:

1. Event Severity
2. Event Class Id
3. Event Class Message
4. Specific Message
5. See Also File (optional)

Event Severity

The event severity parameter is a number from 1 to 5 indicating the severity of the message that caused the alarm program to be invoked. The different levels are:

1. Not noteworthy: The alarm program is not called for events with a severity of 1.
2. Informational: These events do not indicate that an error has occurred, only that something has completed, like when a logical log completes.
3. Attention: The system is still up but something has happened that should be noted. An example of this severity is when an archive is aborted before it completes. However, some events are sent with a severity of 3 but are really more serious (for example, when the logical logs are full).
4. Emergency: Something serious has happened and needs to be fixed immediately, like when a chunk has gone down.
5. Fatal: Something has happened which caused the engine to crash.

Event Class Id and Message

The event class id and message parameters indicate what the message was that caused the alarm program to be invoked. There is a list of 24 ids and messages in the Informix-OnLine Administrator's Guide under the section on Event Alarms. The class id is a better indicator of the severity of the event, since many of the more critical events that occur are given a severity of 3. For example, the class id 20 (with a message of 'Logical Logs are full -- Backup is needed') invokes the alarm program with a severity of 2. In this case, the alarm program can check the class id, instead of the severity, and react appropriately.

Specific Message

The specific message is actually just the message written to the log file. For example, 'Logical Log 788 Complete'.

See Also File

This parameter is optional and indicates that OnLine has written additional information to another file. This might be used when an Assertion Failure occurs.

An Example Alarm Program

Below is an example of an alarm program that could be used for the Event Alarms. This alarm program prints the event information to a log file and sends mail or raises an alert based on the severity of the event. This script does not get into the details of how the mail is sent or an alert is raised. It simply calls another program, call_alert_program, with the necessary parameters and it assumes that this other program will handle the mailing and alerting. Comments are noted between the line numbers.

    1	#!/bin/ksh

    2	

    3	. some_environment_setup_file

    4	

    5	PROG=`basename $0`

    6	

    7	usage()

    8	{

    9	   cat >&2 <<!

   10	   Usage: ${PROG} severity class_id class_msg specific_msg [ see_also ]"

   11	   ${PROG} requires 4 or 5 arguments" >&2

   12	!

   13	   exit 0

   14	}

   15	

   16	

   17	# MAIN

   18	

   19	exec >> some_log_file 2>&1

   20	

   21	

   22	if [ $# -ne 4  -a  $# -ne 5 ]

   23	then

   24	   usage

   25	fi

   26	

   27	# Get the arguments themselves

   28	severity=$1

   29	class_id=$2

   30	class_msg=$3

   31	specific_msg=$4

   32	if [ $# -eq 5 ]

   33	then

   34		see_also="See also: $5"

   35	else

   36		see_also=""

   37	fi

   38	

   39	datevar=`date`

   40	

   41	if [ $severity -ge 3 ]

   42	then

   43	   # Severity ATTENTION or better - send email

   44	

   45	   # case on class_id to reset severity if necessary...

   46	   case $class_id in

   47		20 )	severity=5

   48			;;

   49		11 )	severity=5

   50			;;

   51		12 )	severity=5

   52			;;

   53	   esac

   54	

   55	

   56	  

   57

   58	   case $severity in

   59	

   60	      3) SEV="WARNING"

   61	      	 sev_msg="WARNING ONLINE EVENT";

   62	         ;;

   63	      4) SEV="SEVERE"

   64		 sev_msg="SEVERE ONLINE EVENT";

   65	         ;;

   66	      5) SEV="CRITICAL"

   67		 sev_msg="CRITICAL ONLINE EVENT";

   68	         ;;

   69	      *) SEV="WARNING"

   70		 sev_msg="UNKNOWN ONLINE EVENT - $severity - ";

   71		 ;;

   72	   esac

   73	

   74	   class_text="$class_msg"

   75	

   76	# Log information

   77		echo "==================================================="

   78		echo $datevar

   79		echo "Class ID       : $class_id"

   80		echo "Severity level : $severity"

   81		echo "Severity msg   : $SEV"

   82		echo "Class text     : $class_text"

   83		echo "Specific msg   : $specific_msg"

   84		echo "See Also msg   : $see_also"

   85		echo "==================================================="

   86		echo "\n"

   87	

   88	# check the last occurrence of this class_id

   89	

   90	   check_class_id $class_id

   91	   if [ $? -eq 0 ]

   92	   then

   93	     # last occurrence is over the acceptable time period

   94	     call_alert_program $SEV "$sev_msg\n$class_text\n$specific_msg\n$see_also"

   95	   fi

   96	fi

   97	

   98	exit 0

Warnings on Using Event Alarms

As of Informix-OnLine version 7.12.UC1 there were two bugs with using event alarms which should be noted. The first bug is having the alarm program exit with a non-zero status. This causes Assertion Failures and crashes the engine. The work-around is to make sure the alarm program always exits with a zero.

The second bug occurs when an archive is performed. After the archive completes, the alarm program is called (class id 16 - 'Archive Completed: "dbspace-list"'). In some cases the memory containing the dbspace list is corrupted which causes an Assertion Failure and crashes the engine. In this case the engine crashes hard and leaves the shared memory segments and oninit processes which must be removed manually. Removing the shared memory is accomplished by using the ipcs -m command to get the shared memory id's and the ipcrm command to remove those ids. The oninit processes can be removed by using the kill command (see your system administrator for more information).

The work-around for this bug is to reinitialize the engine before performing the archive (this means no on-line archives). This refreshes the shared memory and removes the corruption.

Conclusion

The Event Alarm feature is a very useful tool which provides the ability to monitor an OnLine system without having to constantly check the log files. The feature is very useful for monitoring several OnLine systems, even remote systems, with very little manual effort. It gives administrators the ability to be proactive in identifying problems with an OnLine system before users are affected.

Kevin Fennimore				Ivy Ho

UCI Consulting, Inc.	and		Marriott International

This article provides information for evaluating an INFORMIX DBA. For any company, recruiting good staff is always a difficult task. This article presents how we came up with test questions to evaluate an INFORMIX DBA. Recently American Computer Technology, Inc. (ACT) has been awarded a contract by a large commercial company to evaluate their INFORMIX-4GL developers and Database Administrators, and to recruit a new staff of INFORMIX developers and Database Administrators. The major goal for the company was to identify the problem areas of developers and database administrators and train them in these specific areas.

In the previous issue of WAIUG in the article TESTING AN INFORMIX DEVELOPER, I discussed how we came up with two tests, TEST ON DATABASE CONCEPTS and TEST ON INFORMIX-4GL CONCEPTS to test an INFORMIX developer and how we trained them to be a good developer.

Testing a DBA is more complicated than testing a developer. One of the major problem is to define the functionality of DBA. The DBA means different roles to different managers. A person who can load and unload data may be represented as a DBA. A person who can just create a database instance may be represented as a DBA. In fact, any of the following tasks/functions may be represented as DBA functions: creating a logical data model, writing DDL scripts, helping developers, overseeing the system architecture and database architecture, providing production data support, evaluating different database products and recommending a database product to management, performing tests and benchmarks, tuning database engines, or helping developers to optimize their SQL statements, and so on.

Depending on the project and the skill level of an individual, a DBA can be categorized into three major types: Central DBA, Development DBA and Production Support DBA. A Central DBA should be experienced with the systems development life cycle, provide help with system architecture, and help the Development and Product Support DBAs. A Development DBA should be experienced in specific database products and tools, support application developers, and assist the Central DBA. The Production Support DBA, called Operations DBAs, must have a general understanding of the database(s) and be able to provide production support with the help of the Central DBA. For smaller projects, a DBA will be performing all of these functions.

DBAs are critical to any project. Good DBAs can save time and effort on a project. In one project, we studied the architecture and identified the possibility of generating long transactions in some areas, and suggested possible solutions to the Central DBA. The Central DBA did not fully understand the problem and issues and instructed developers to implement as originally planned. The DBA was satisfied because he was able to successfully execute long transactions with the small amount of data on the test database. However, when user acceptance testing started, the transaction was not able to complete. The company was then required to spend overtime modifying the application. Thus the Central DBA must be proactive, not reactive.

In another project, we were called at the last minute help provide solutions to performance problems. Every one believes Development DBAs create databases and tables, but they can actually do much more than that, as can be seen in the next example.

One of the programs on a test case was taking 1000 seconds to execute. The requirement was simple: when a data field is modified, send a message to all users of the data. There was a simple mechanism for identifying all users to whom the messages were to be sent - no problem.

There were two tables, table1 has unique id(serial no), sender id, transaction time, text message of 2000 characters, and table2 has unique id(table ones data), receiver id. The developer wrote a function and calls this function every time with sender id, receiver id, transaction time and text message. The function searches every time in table1 with the key sender id, transaction time and the text field. If a record is not found, then it inserts a record into table1 and gets the serial number and inserts serial no and sender id into table2. If a record is found, it gets the serial number and inserts the serial number and sender id into table2.

We suggested the programmer write two functions: the first function would get the serial no and insert sender id into table1 and the second function would write receiver data into table2 only. After modifications to the program, the same test case took 3 seconds. A good Development DBA would have been able to avoid this problem in the beginning.

Since a DBA is critical to a project and involved in many functional areas, it is important to have a special test for DBAs. Thus, in addition to the above two tests (covered for developers) , we added a special test, TEST FOR INFORMIX DBA, to evaluate DBAs. The test covers questions on distributed database concepts, parallel database concepts, object oriented database concepts and specifics to INFORMIX database organization. This test helped us to identify the individual problem areas. We then conducted a one-day course covering concepts on distributed databases, parallel databases, and INFORMIX database architecture. DBAs were more confidant after the course.

3. TEST FOR INFORMIX DBA ( Distributed, Parallel, and Object oriented concepts)

• What is the name of the INFORMIX client/server connectivity product?
• What is the name of the INFORMIX distributed connectivity product?
• What is the fuss about INFORMIX DSA?
• What are the major feature supported by Objected oriented database?
• Tell us something about a two phase commit.
• IF you are implementing a two-phase commit in a distributed environment, which optimization do you choose (presumed commit or presumed abort)?
• What kind of presumed optimization does INFORMIX use with the two-phase commit?
• What are the major differences between two phase commit and three phase commit?
• What do you need replication for? Is two-phase commit enough?
• Name some of the important algorithms suggested to implement a replication in the distributed environment.
• Do you believe the replication solution provided by INFORMIX/SYBASE/ORACLE is a true solution in the distributed environment? Explain.
• Why do you need version management?
• What kind of SQL optimization mechanisms does INFORMIX use?
• Why is cost-based SQL optimization mechanism is better than rule-based optimization?
• Do you believe that INFORMIX distributed products adhere to 12 rules of C.J. Dates's for distributed systems? Explain.
• Stefano Ceri and Giuseppe Pelgati explain six major reasons why companies distribute their data in their book "Distributed Databases, Principles and Systems". Do you remember them? Explain.
• How does PDQ help to improve performance?

• What do you know about inter-query parallelism and intra-query? Does INFORMIX support them?
• What is data fragmentation (Data Partitioning)?
• What are the advantages of data fragmentation?
• What type of distribution schemes for data fragmentation are supported by INFORMIX?
• Do you believe that data rang-partition is good for sequential and associative access? Explain.
• Do you believe the round-robin data partition strategy is good for sequential and associative access? Explain.
• Do you believe the Hashing data partition strategy is good for clustering, sequential and associative access? Explain.
• What is a data-skew?
• Do you believe that data partitioning strategies round-robin, hashing, and range-partition do create data-skew? Explain.
• Which one of the above three data partitioning mechanisms risks data skew?
• Define a processor affinity.
• What are the types of database-logging are supported by INFORMIX?
• What is the command that you use to change the logging status of a database?
• What is a logical log?
• What is the purpose of the physical log?
• What are the major tuning parameters?
• What kinds of archives are supported by INFORMIX?
• Running decision support reports and OLTP on one system causes locking problems - what mechanisms avoid this problem?
• How is replication is supported by INFORMIX?
• What are the advantage of using software mirroring rather than hardware disk mirroring?
• What is multiple residency? How are they maintained?
• What is the advantage of a CLUSTERED INDEX and how you can create it?
• Tell us something about the reorganization of the database.
• What command is used to kill an INFORMIX hanging process?
• What are the major parameters to be considered when creating an EXTENT and NEXT sizes for a table space?
• Are variable size logical log files supported by INFORMIX?
• Define a virtual processor. What classes of VP are supported by INFORMIX?
• What is a thread? How do virtual processors service threads?
• Tell us how disk spaces is organized.
• What are OnLine's operating modes?
• What is the purpose of the DBIMPORT and DBEXPORT commands?
• What is the sysmaster database?
• What is a data blade? Why it is important for spatial data?

Madhu Reddy  

American Computer Technology, Inc.

10816 Estate Ct., Fairfax, VA-22030

Phone: (703) 385-3273 

Fax: (703) 385-4969

INFORMIX INSTALLATION

ONLINE CONFIGURATION PROCEDURES

1. Informix Installation Procedures

Planning for INFORMIX-OnLine Dynamic Server

When planning for OnLine, you need to consider both your priorities and your resources.

Consider Your Priorities

As you prepare the initial configuration and plan your backup and archiving strategies, you need to keep in mind the characteristics of your database server such as:

• What is your highest priority, transaction speed or safety of the data?

• Will the database server usually handle short transactions or fewer long transactions?

• Will this OnLine instance be used by applications on other computers?

• What is the maximum number of users you can expect?

• How much help or supervision will the users require? To what extent do you want to control the environment of the users?

• Are you limited by resources for space? CPU? Availability of operators?
• How much does the OnLine instance have to do without supervision?

• How many disk drives are available? What are there device names? What is the size of each disk? Are some disk drives faster than others? How many disk controllers are available? What is the disk controller configuration? During the initialization of OnLine, everything -- tables, log files, indexes, data -- goes into the root dbspace on one disk drive. After OnLine is running, you can move different objects to different drives. For example, you should put the most frequently used tables on the fastest drives. (See a discussion on disk management in Chapter 11 of the OnLine Dynamic Server System Administration Guide).

• How many tape drives are available? What are their device names? What size tapes can be used? When is an operator available to change tapes? You need to select the number and size of the logical-log files so that they do not fill up before a tape backup can be made. OnLine keeps statistics that help you adjust these parameters after your OnLine database server has been running for a while. Your archiving strategy also needs to take into account availability of tape drives. Archiving is discussed in the INFORMIX-OnLine Dynamic Server Archive and Backup Guide.
• What are the UNIX kernel parameters? How much shared memory is available? How much can OnLine use? How many semaphores are configured?
• What are the network names and addresses of the other computers on your network? You may need assistance from the network administrator to update the operating system files network files.

Installing Informix Products

Installation refers to the process of loading the product files onto your UNIX system and running the installation script to correctly set up the product files.

Order of Installation

The proper order of installation is tools, engines and connectivity products. Within each group you will install earlier versions first and higher versions last. For example, I-4GL 6.0, I-SQL 6.01, OnLine 7.10 would be the order of installation for those three products. I-4GL and I-SQL are tools and OnLine is an engine. I-4GL has a lower version than I-SQL and thus is installed before I-SQL. The reason behind this is so that newer files will overwrite older files. These file could message files (/msg), binary files (/bin) and various other files. See the UNIX Products Installation Guide for more detailed information about this subject. Another example is I-4GL 4.11, I-SQL 4.13, ESQL/C 5.05, OnLine 5.05, and I-STAR 5.05 in the correct order of installation. I-4GL, I-SQL and ESQL/C are tools, OnLine is the engine and I-STAR is the connectivity. I-STAR does not exist in 7.xx products because it is built in.

Procedure (Versions 5.0x or 7.xx)

1. Obtain the ROOT password

2. Login as ROOT

3. If you do not have a user "informix" and a group "informix", create them now. The home directory for Informix should be placed in the passwd file so that when you login as "informix", you are placed in that directory.

4. Set up your environment variables for installation purposes.

			C shell:		setenv INFORMIXDIR /usr/informix

						setenv PATH {$PATH}:{$INFORMIXDIR/bin}

			

			Bourne shell:		INFORMIXDIR=/usr/informix

						export INFORMIXDIR

						PATH=$PATH:$INFORMIXDIR/bin

5. Type set or env to verify the parameters are set correctly.

6. Change directories to the informix directory using cd $INFORMIXDIR.

7. Remove the tape from the packaging and place it into the tape drive.

8. Find the serial key card that comes with the product and review the tape drive parameters used.

9. Use tar or cpio as listed on the serial key card. You may have to use a different device name depending on your specific tape drive device name. Default tape device names include:

			HP:	/dev/rmt/0m

			SUN:	/dev/rmt/0

			IBM:	/dev/?

11. You will be prompted for a serial number. The serial number can be found on the serial key card and has the format AAA#Xnnnnnn where X is a letter and n is a digit between 0 and 9. There are always 6 digits following a letter which follows the # sign.

12. Next you will be prompted for the key. The key can also be found on the serial key card. The authentication key is comprised totally of letters. There are never any numbers in the key. Sometimes a letter may look like a 0 (zero) but it is really the letter O. The key is always in capital letters.

13. Remove the tape from the system and place it back in its protective case. It is a recommended practice to fold the serial key card such that it fits in the protective case along with the tape. It is important to keep the tape and serial key card together in one place.

14. If you do an ls -al you will notice that the files have been changed to user informix and group informix. They have gone through a process known as branding. If the files have not been changed to user informix and group informix then the installation process was done incorrectly.

15. You are finished with the installation of this product. To install another Informix product, return to step 7.

2. OnLine Configuration Procedures (Versions 5.0x or 7.xx)

After OnLine is installed, it must be configured before it can be brought on-line. Configuration refers to setting specific parameters that customize the OnLine database server for your data processing environment: quantity of data, number of tables, types of data, hardware, number of users, and security needs. Chapter 3 in the INFORMIX-OnLine Dynamic Server System Administration Guide has a very detailed description of the configuration parameters set during this phase.

During this phase, you will need to be aware of UNIX kernel parameters and raw or cooked device names. You will also learn about OnLine configuration files and some new environment variables.

Configuration Files

onconfig.std or tbconfig.std

The $INFORMIXDIR/etc/onconfig.std file is the configuration file template. It is copied to the file $INFORMIXDIR/etc/onconfig during the OnLine installation procedure. The onconfig.std file contains default values for the configuration parameters and serves as the template for all other configuration files that you create.

sqlhosts

The $INFORMIXDIR/etc/sqlhosts file is the connectivity file. It contains information that enables an Informix client application to connect to any Informix database server on the network. It specifies the database server name, the type of connection, the name of the host computer, and the service name

The following example illustrates the makeup of the sqlhosts file:

		

		dbservername	nettype		hostname	servicename

		field		field		field		field		

		my_db_shm	onipcshm	my_host1	my_srvc_shm

		my_db_soc	onsoctcp	my_host1	my_srvc_soc

		my_db_tli	ontlitcp	my_host2	my_srvc_tli

The dbservername (database server name) field contains the name of the database, as specified by the DBSERVERNAME and DBSERVERALIASES configuration parameters in the onconfig file. Your application connects to a dbservername and uses the dbservername as an index to obtain the connectivity information in the remaining fields. This is the same as INFORMIXSERVER environment variable (OnLine 7.xx only)

The nettype (network protocol) field describes the type of connection that should be made between the client application and the database server. The nettype field is a series of 8 letters composed of three subgroups illustrated below:

	dd iii ppp

	dd  = Database server product

		on	OnLine (this is more common form)

		ol	OnLine

		se	INFORMIX-SE

		dr	INFORMIX-Gateway with DRDA

	iii  = Interface that enables communication

		ipc	IPC (interprocess communication)

		soc	sockets

		tli	TLI (transport layer interface)

	ppp  = represent the specific IPC mechanism or network protocol

	shm	shared-memory communication

	tcp	TCP/IP network protocol

	spx	IPX/SPX network protocol

	pip	Used when configuring SE.



        IPC connections for the OnLine database server use shared memory.  The

        rules or conventions for the behavior of networks are called network

        protocols.  Informix supports two network protocols: TCP/IP and

        IPX/SPX.  The IPX/SPX protocol is usually supported on the TLI

        interface.

			

	The following table summarizes the nettype values for OnLine:



	NETTYPE     Description				   Connection Type

	onipcshm    OnLine using shared-memory communication    (IPC)

	onsoctcp    OnLine using sockets with TCP/IP protocol   (network)

	ontlitcp    OnLine using TLI with TCP/IP protocol       (network)

	ontlispx    OnLine using TLI with IPX/SPX protocol      (network)

The hostname field contains the name of the computer where the OnLine database server resides. The hostname corresponds with the hostname entry in the /etc/hosts file. For TCP/IP communication, it is used to get the IP address of the computer.

The interpretation of the servicename field depends on the type of connection specified in the nettype field.

When you use shared-memory communication, OnLine uses the servicename entry internally to look up the name of a file that contains shared-memory information. The servicename field for a shared-memory connection can be any value that is unique on the server computer.

When you use the TCP/IP connection protocol, the servicename must correspond to a servicename entry in the /etc/services file as illustrated below. The port number in the /etc/services file tells the network software how to find the database server on the specified host. It does not matter what servicename you choose, as long as you agree on a name with the network administrator. The port number in the /etc/services file must be unique within that file.

			dbservername	nettype		hostname	servicename

			field		field		field		field		

			my_db_soc	onsoctcp	my_host1	my_srvc_soc

			my_db_tli	ontlitcp	my_host2	my_srvc_tli		

							

						Servicename 	port #/ protocol

						my_srvc_soc	526/tcp

						my_srvc_tli	1527/tcp

A service on the IPX/SPX network is simply a program that is prepared to do work for you, such as an OnLine database server. For IPX/SPX connection the value of servicename can be an arbitrary string, but it must be unique among the names of services available on the IPX/SPX network. It is convenient to use the dbservername in the servicename field. When you use INFORMIX-OnLine for Netware 4.1, the servicename must be the same as the dbservername.

Environment Variables

Environment variable are discussed in detail in chapter 4 of the INFORMIX Guide to SQL: Reference. You need to pay close attention to the following environment variables, which must be set correctly before you can initialize OnLine.

• INFORMIXDIR - Contains full pathname to Informix products

• PATH - Includes $INFORMIXDIR/bin where Informix executables reside.

• INFORMIXSERVER (Version 7.xx) - Specifies the name of the dbservername in sqlhosts and onconfig file.

• ONCONFIG - Optional (Version 7.xx). If $INFORMIXDIR/etc/onconfig is the configuration file then this environment variable does not need to be set. If any other name besides onconfig is used, then set ONCONFIG equal to the name of the file. (i.e.ONCONFIG=my_onconfig.name; export ONCONFIG)

• TBCONFIG - Optional (Version 5.0x). If $INFORMIXDIR/etc/tbconfig is the configuration file then this environment variable does not need to be set. If any other name besides tbconfig is used, then set TBCONFIG equal to the name of the file. (i.e. TBCONFIG=my_tbconfig.name; export TBCONFIG)

• DBPATH - This environment is used to specify the location of other database servers. (i.e. DBPATH=//dbserver1://dbserver2

Other potential environment variables which may need to be set to allow for different terminal types include:

• TERM
• TERMCAP
• INFORMIXTERM

Allocating Disk Space

The UNIX operating system allows you to use two different types of disk space: raw and cooked. Cooked disk space or cooked file space refers to ordinary UNIX files. It is space that has already been organized and that UNIX administers for you. Raw disk space is unformatted space that OnLine administers. OnLine allows you to use either type of disk space (or a mixture of both types). To gain the full benefits of OnLine capabilities, you must use raw space.

Allocating Cooked Files

Command                           Comments				

# su informix                     Login as informix. (Enter password).

# cd /usr/data                    Change to cooked space directory.

1.  # cat /dev/null > my_chunk    Create the chunk.  (i.e. my_chunk).

2.  # chmod 660 my_chunk          Set file permissions to 660 (rw-rw----).

3.  # ls -al my_chunk             Verify informix owner and group.

Allocating Raw Disk

Note: Character-special devices are used. Do not create file systems on these devices.

Command                                  Comments				

# su root                                Log in as root. (Enter the password).

# cd /dev                                Change to device driver directory.

1.  # chmod 660 /dev/rdsk/rvol1          Change permissions to 660.

2.  # chown informix /dev/rdsk/rvol1     Change ownership to informix.

3.  # chgrp informix /dev/rdsk/rvol1     Change group to informix.

4.  # l n -s /dev/rdsk/rvol1 /dev/myvol  Set up soft or hard links to disk device.

5.  # ls -al /dev/rdsk                   Verify that permissions are set correctly.

6.  # ls -al /dev                        Verify links are set up correctly.

It is recommended that the database administrator keep a file in the $INFORMIXDIR which has a detailed list of the dbspace, device name, link, offset, size and purpose (i.e. root, temporary, mirror, index, data, etc).

NOTE: For SUN and Data General machines it is not a good idea to build links in the /dev directory since those systems rebuild /dev on boot up. Keeping link names short is also recommended.

OS Kernel Parameters

Semaphores

System V:

• SEMMNI - Maximum number of semaphore sets available
• SEMMSL - Maximum number of semaphores in a set (should be >= 100)
• SEMMNS - Total number of semaphores available

      SEMMNI * SEMMSL = SEMMNS

      SEMMNS = vps + # shared memory users + utilities (i.e. onstat, dbaccess, onarchive)

• SEMMNI - Maximum number of semaphore sets available
• SEMMNS - Total number of semaphores

       SEMMNS = vps + # shared memory users + utilities (i.e. onstat, dbaccess, onarchive)

Semaphores are a sort of flag, or locking mechanism. INFORMIX-OnLine uses semaphores to control processes, putting them to sleep when no work is available. Semaphores are also used to synchronize client/server communication for shared memory connections. Semaphores are allocated for the entire system using configuration parameters in the UNIX kernel.

Each instance of OnLine requires one semaphore for each VP and one semaphore for each shared memory connection.

Each instance of OnLine uses one set for:

• Each group of (up to) 100 VPs that are initialized with OnLine
• Each additional VP you may add dynamically (while OnLine is running)
• Each group of 100 (or less) user sessions connected through the shared memory interface.

OnLine utilities such as onmode, onstat, dbaccess, and etc. use shared memory connections; you must configure a minimum of two semaphore sets for each instance of OnLine: one for the initial set of VPs, and one for the shared memory connections used by OnLine utilities.

Semaphore parameters differ from system to system. Check your operating system for the exact meaning of these configuration parameters.

In many operating systems after you make changes to the kernel, you must rebuild the kernel and reboot the system.

Shared Memory

System V:

• SHMMAX - Maximum size of a single shared memory segment
• SHMSEG - Maximum number of segments a single process can access
• SHMMNI - Maximum number of segments for you UNIX system

• SHMSIZE - Maximum size of a shared memory segment
• SHMMNI - Maximum number of segments for you UNIX system

       SMMAX * SHMSEG = How much shared memory OnLine can access

       SHMMAX * SHMMNI = How much shared memory is available for your UNIX system

In order for shared memory to be available for INFORMIX-OnLine, the UNIX operating system kernel parameters must be set up with a sufficient amount of shared memory resources to accommodate OnLine's needs. You need at least two shared memory segments (three if shared memory is used for communication) for each OnLine system, one for each portion of shared memory.

Shared memory parameters may vary from system to system. Some systems limit the number of segments that can be allocated. Consult your UNIX System Administrator's guide for more information on how to configure shared memory on your system.

Other Kernel Parameters

• Number of open files
• TCP/IP parameters (streams, TLI)
• Maximum processes and users

There are some kernel parameters that may need to be increased when the OnLine system will have a large number of sessions.

You may have to alter any kernel parameters that pertain to the number of open files in your system or per process. For example, NOFILES is the number of open files per process on SUNOS 4.1.3. The formula for configuring the number of open files per process is:

        # TCP/IP sessions + # of chunks (if using KAIO) + # listen threads + 

        # distributed connections

Each TCP/IP connection requires a file descriptor. If you are using kernel AIO, each chunk requires a file descriptor. Each listen thread (usually there is only one) requires a file descriptor. If you are connecting to other OnLine systems with distributed SQL statement, one file descriptor is required for every connection to a remote OnLine system.

You may need to increase the TCP/IP kernel parameters if applications are connecting to the database with the TCP/IP TLI interface.

Finally, take a look at any kernel parameters dealing with the maximum of processes and users.

Configuration Procedure (OnLine 5.0x and OnLine 7.xx)

1. Login as root.

2. Review the release notes in $INFORMIXDIR/release/ONLINE* for any OS patches required.

3. Make a note as to whether SOCKETS or TLI is supported. You will need this for the SQLHOSTS file.

4. Review the release notes in $INFORMIXDIR/release/ONLINE* for OS kernel parameter setting suggestions. See the section above on OS Kernel Parameters for the following parameters:

• Shared Memory Maximum (SHMMAX)

• Semaphores

• Semaphore sets
• Number of Open Files allowed

You must be ROOT to modify the kernel parameters.

5. Set up Cooked Files or Raw Character-Special Devices. See the section above on Allocating Disk Space. You must be logged in as ROOT in order to perform these functions. DO NOT USE BLOCK DEVICES. NEVER MOUNT THE DEVICE YOU ARE USING FOR RAW SPACE. You will encounter problems later on. When a system reboots, it modifies block devices during startup and thus changes the page structures that OnLine expects. The result is that OnLine may mark some chunks down. Using character-special devices does not produce this effect.

6. vi /etc/services. Add a unique entry for a TCP/IP connection to the database server such as:

my_db_soc      1525/tcp

or...

my_db_tli      1526/tcp

7. Log out of root and log in as informix.

8. Make sure that INFORMIXDIR, PATH, (INFORMIXSERVER - 7.xx), and optionally (ONCONFIG - 7.xx or TBCONFIG - 5.0x) are set accordingly in the .profile or .login. See the section above on Environment Variables. You may need to set TERM according to your terminal type. For example, if TERM=hpterm, you may need to set this to TERM=hp and export TERM. Additionally, you may need to set TERMCAP=$INFORMIXDIR/etc/termcap and export TERMCAP.

9. vi $INFORMIXDIR/etc/sqlhosts

10. Add entries for shared memory and TCP/IP connections to the sqlhosts file. You will need the information you gathered from the release notes on whether the machine supports sockets or TLI. See the section above on configuration Files: sqlhosts for additional help on configuring the entries. The service name will be the same you put in the /etc/services file for TCP/IP connections.

11. (Version 7.xx) - Copy onconfig.std to the name you chose for $ONCONFIG environment variable. If you did not choose any specific name then copy onconfig.std to onconfig. The onconfig is the standard configuration file name when $ONCONFIG is not specified. The onconfig file stores all pertinent information about an OnLine instance. Onmonitor will use onconfig or $ONCONFIG to store parameters during initialization. You can modify this file by hand if you wish. For the parameters to take effect you must bring OnLine off-line with an onmode -ky and bring it back on-line with an oninit. WARNING: Please do not run oninit -i after you initialized your instance for the first time. This command will wipe everything out. (Version 5.0x) - Copy tbconfig.std to the name you chose for $TBCONFIG environment variable. If you did not choose any specific name then copy tbconfig.std to tbconfig. The tbconfig is the standard configuration file name when $TBCONFIG is not specified. The tbconfig file stores all pertinent information about an OnLine instance. Tbmonitor will use tbconfig or $TBCONFIG to store parameters during initialization. You can modify this file by hand if you wish. For the parameters to take effect you must bring OnLine off-line with an tbmode -ky and bring it back on-line with an tbinit. WARNING: Please do not run tbinit -i after you initialized your instance for the first time. This command will wipe everything out.

12. Run onmonitor (7.xx) or tbmonitor (5.0x) to configure your OnLine system.

13. Changes to parameters are written to the $INFORMIXDIR/etc/onconfig file or the file specified by $ONCONFIG for Version 7.xx and $INFORMIXDIR/etc/tbconfig or the file specified by $TBCONFIG for 5.0x.

14. Configuration Recommendations (Applies to OnLine 7.xx):

• It is recommended that you initialize OnLine with a shared memory connection only to start out with. The NETTYPE field would have the following entries: ipcshm,,40,CPU. Since you are not configuring for network access, it is recommended that you leave the DBSERVERALIASES field blank at this time. Once OnLine has been initialized, go back and add the entries for NETTYPE and the DBSERVERALIASES network connection information. The entry for NETTYPE would be as follows: soctcp or tlitcp,,40,NET. Depending on your requirements, you may tune the number of users and the Virtual Processor type used in this field. Use the manuals to glean more information.

• The SERVERNUM field must be unique when running more than one instance of OnLine on a system. This number determines what segment(s) of shared memory should be used when accessing OnLine.
• Use /dev/null for the TAPEDEV and LOGDEV during initialization. Go back and change them once OnLine is running.
• CLEANERS: Page Cleaners should be set to the number of active disks.
• USERTHREADS and TRANSACTIONS: If TP/XA is not being used then set TRANSACTIONS the same as USERTHREADS. The maximum number of USERTHREADS that can access OnLine at any one time. USERTHREADS include:

	The main OnLine thread

	A clean-up thread

	The page cleaner threads

	Onmonitor thread

	A b-tree cleaner thread

	Recovery threads.  

	An additional thread if you are using mirroring.

	The minimum number of USERTHREADS can be calculated with the

	following formula:  5 + page cleaner threads + 6 slots for

	recovery threads + mirroring thread (optional)

• SHMADD: For the additional virtual segment size (SHMADD), it is recommended that you take into account the total memory on the machine, the amount of memory that OnLine will be using, and the amount of UNIX swap space that is configured. Don't set the number to be too large such that it tries to add a segment of virtual memory that is larger than what is actually left. The system will start swapping like crazy which will not be good for overall performance.
• LRUS: Set LRU Queues to a minimum of 4 or the number of CPU VPs whichever is greater.
• NETTYPE: Protocol, Threads, Users, VP-class

               The Protocol field refers to the type of protocol such as: 

                               ipcshm, soctcp, tlitcp, or tlispx.

               The protocol field should contain the six digit protocol in the

               same format listed in the sqlhosts file.

               The Threads field specifies the number of poll threads started

               for the protocol.  For less than 200 shared memory users, one

               poll thread is usually sufficient.  For less than 50-100

               network users, one poll thread is usually sufficient.

               The Users field specifies the number of connections that use

               this protocol.  It is used in the case of the shared memory

               protocol to set up the appropriate number of message structures

               in the message portion of shared memory.



               The VP-class field is the class that the poll threads will be

               run on (specify either CPU or NET).  The CPU class is usually

               the optimal location for the poll thread.  However, only one

               protocol can run a poll thread on the CPU VP.  The NETTYPE that

               requires the best performance should be assigned to run on the

               CPU VP.

• Make sure that the directories specified for diagnostic parameters have enough disk space to hold error files and core dump files. It is not a good idea to share /tmp with a swap disk. You may not have enough space if OnLine needs to write out error message files.

Excerpted from INFORMIX INSTALLATION AND CONFIGURATION GUIDE, by Mike Tinius, Informix Software, Inc. This consists of the first two chapters out of 9 chapters. Mike is a Systems Engineer with the Informix located in Bethesda, MD, and is the Commercial Division's representative to the Washington Area Informix User Group. Additional chapters will be published in future issues of this newsletter.

We are pleased to announce that the Washington Area Informix User Group has opened a Web Page and converted all our newsletters from the past three years to WWW documents; they are available for on-line viewing. This is the place to find the latest news about our meetings, future plans, links to other Informix related material and catch-up on back issues of our newsletter. The following is the index of articles from our newsletters over the past three years that are available on-line. Visit our Home Page at http://www.access.digex.net/~waiug. (Index not duplicated here.)

The Washington Area Informix Users Group (WAIUG) is an organization for users of Informix database software and tools. The group primarily serves the Washington DC, Virginia, and Maryland areas, but also has members from all over the USA. Our activities include regular meetings, a newsletter, and a WWW page. We have also held three very exciting local one-day Forums since 1993, with over 200 members attending seminars and exhibits on new products and developments related to Informix. The WAIUG is incorporated as a not-for-profit organization and managed by a volunteer board of directors.

The WAIUG was started in May 1991. Nineteen people from a variety of companies and federal agencies met to hear about release 5.0 and make plans for a user group. At that meeting we identified the goals of the user group as to share information, learn about new products, provide technical input, discuss problems, share solutions, and network with other Informix Users. Our first newsletter was sent out in June 1991. Since that time, our mailing list has grown to over 900 names.