October 1996 Newsletter

Volume 6, No. 4

Highlights of This Issue

Exploring the SYSMASTER Database (Part II) - by Lester Knutsen

Informix Installation and OnLine Configuration Procedures (Part II) - by Mike Tinius

November 6, 1996 - Using ODBC with Informix Databases

	Date and Time:		November 6, 1996, 6:00 p.m. to 9:00 p.m. 

				(evening meeting)

	Location:		Advanced DataTools Corporation

				4216 Evergreen Lane, Suite 136, Annandale, VA 22003

	Software Raffle: 	One copy of INFORMIX-OnLine Workstation for NT and

				IQ/LiveWeb will be raffled at the end of the evening.

December 11, 1996 - INFORMIX-Universal Server

Introduction of the new object-oriented Informix database server. Universal Server extends the Informix database into the management of Web pages, electronic documents, images, video, and spatial and time series data.

Developing datablades for Informix Universal Server by Michael Keeler, president of ECOlogic.

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

	Location:		Informix Software Corporation

				8065 Leesburg Pike, Suite 600, Vienna, VA 22182

A one day training event for WAIUG members at the University of Maryland during the March spring break. Four half-day techinical sessions have been scheduled. Planned cost is $5 for members.

• Enterprise Security - Summit Data Group
• Migrating to INFORMIX-OnLine 7.X from SE or 5.X - Mike Tinius
• Integrating your Informix Database With the Internet - Lester Knutsen
• SQL Performance Tuning - Kevin Fennimore

Depending on the space available at the University of Maryland, we may have openings for a few more sessions. If you would be interested in conducting a half-day techinical session at this event, please contact Lester Knutsen at 703-256-0267.

At our September meeting elections to the WAIUG Board of Directors were conducted. The Board is composed of volunteers who plan our activities and work hard at putting them together. The current officers were re-elected unanimously. The officers are: Lester Knutsen, President; John Petruzzi, Membership Director; Sam Hazelett, Treasurer/Secretary; Nick Nobbe, Programs/Sponsors Director. Mike Tinius, of the Informix Commerical office, and Sherryl Dorch, from the Informix Federal office represent Informix on the WAIUG steering committee. We also decided to expand our steering committee and I would like to welcome two new members, Ed Brogden and Tracy Nedd.

In addition to this newsletter and our local activities, there is a new reason to be a member of the Washington Area Informix User Group. All current members will automatically become members of the International Informix User Group for one year. Some of the benefits this includes are discounts to the Informix World Wide User Conference in Chicago in July, and full access to the members-only section of the IIUG Web Pages. Other discount programs are being worked on as well. Have you renewed your membership for 1996? Membership dues are $20.00. We also have a Corporate Membership Program. Forms are at the back of this issue. For more membership information, please call our Membership Director, John Petruzzi, at 703-490-4598.

Exploring the SYSMASTER Database (Part II)

This is the second of three articles on how to monitor your INFORMIX-OnLine DSA system using the sysmaster database. In the last issue we looked at how to tell who is accessing your server with a program called "dbwho". We also discussed how to view your server configuration, system performance profiles, logical logs, and virtual processor profiles using SQL from the sysmaster database.

This issue will focus on dbspaces, chunks, tables and monitoring IO using the sysmaster database. We will explore how to create scripts to monitor the following:

• Display how much free space is available in each dbspace in a format like the Unix df command.

• List the status and characteristics of each chunk device.

• Display blocks of free space within a chunk. This allows you to plan where to put large tables without fragmenting them.

• Display IO statistics by chunk devices.

• Display IO usage of chunk devices as a percent of the total IO and show which chunks are getting used the most.

• Display tables and the number of extents, and number of pages used.

• Present a layout of dbspace, databases, tables, and extents similar to the command "tbcheck -pe".

• Display table usage statistics sorted by which tables have the most reads, writes or locks.

The sysmaster database is one of the new features implemented in INFORMIX-OnLine 7.x. The database contains tables that can be used for monitoring your system. These are referred to as the System Monitoring Interface (SMI) tables. The examples and references to this database are based on OnLine 7.13.UC1. I have also tested some of the examples with versions 7.10.UC1 and 7.12.UC1. There are some minor changes between versions in the undocumented features and structures of these tables. Only 15 of the sysmaster 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.

Dbspace and Chunk Information

Dbspace Configuration: sysdbspaces

The sysmaster database has three key tables containing dbspace and chuck information. The first one is sysdbspaces. This is a view that interperates the underlying table sysdbstab. The view serves a two purposes: it translates a bit field containing flags into separate columns where 1 equals yes and 0 equals no, and it allows the underlying table to change between releases without changing code. The view is defined as follows:

View sysdbspaces

	dbsnum		smallint,   	dbspace number, 

	name		char(18),   	dbspace name, 

	owner		char(8)     	dbspace owner, 

	fchunk		smallint,   	first chunk in dbspace, 

	nchunks		smallint,   	number of chunks in dbspace,

	is_mirrored	bitval, 	flag is dbspace mirrored, 1=Yes, 0=No

	is_blobspace	bitval, 	flag is dbspace a blob space, 1=Yes, 2=No

	is_temp		bitval,		flag is dbspace temp, 1=Yes, 2=No

	flags		smallint,   	dbspace flags

The columns of type bitval are the flags that are extracted from the flags column by a stored procedure called bitval when the view is generated.

Chunk Configuration: syschunks

The chunk table is also a view based on two actual tables, one for primary chunk information, "syschktab", and one for mirror chuck information, "sysmchktab". The following is the layout of this view:

View syschunks

	chknum 		smallint,   	chunk number 

	dbsnum		smallint,   	dbspace number

	nxchknum	smallint,   	number of next chunk in dbspace

	chksize 	integer,    	pages in chunk

	offset		integer,    	pages offset into device 

	nfree		integer,    	free pages in chunk

	is_offline	bitval, 	flag is chunk offline, 1=Yes, 0=No

	is_recovering	bitval, 	flag is chunk recovering, 1=Yes, 0=No

	is_blobchunk	bitval, 	flag is chunk blobchunk, 1=Yes, 0=No

	is_inconsistent	bitval, 	flag is chunk inconsistent, 1=Yes, 0=No

	flags 		smallint,   	chunk flags converted by bitval

	fname		char(128),  	device pathname

	mfname		char(128),  	mirror device pathname

	moffset 	integer,    	pages offset into mirror device 

	mis_offline	bitval, 	flag is mirror chunk offline, 1=Yes, 0=No 

	mis_recovering 	bitval, 	flag is mirror chunk recovering, 1=Yes, 0=No

	mflags 		smallint,   	mirror chunk flags

Displaying Free Dbspace

Now, we will look at several ways to use this information. One capability I have always wanted is a way to show the amount of dbspace used and free in the same format as the Unix "df -k" command. The sysmaster database contains information about the dbspaces and chunks so this can be generated with an SQL script. The following is an SQL script to generate the amount of free space in a dbspace. It uses the sysdbspaces and syschunks database to collection its information.

Figure 1: SQL Script - dbsfree.sql

-- Script: dbsfree.sql - display free dbspace like Unix "df -k " command

database sysmaster;

select

	name[1,8] dbspace,		-- dbspace name truncated to fit on one line

	sum(chksize) Pages_size,	-- sum of all chuncks size pages

	sum(chksize) - sum(nfree) Pages_used,

	sum(nfree) Pages_free,		-- sum of all chunks free pages

	round ((sum(nfree)) / (sum(chksize)) * 100, 2) percent_free

from 	sysdbspaces d, syschunks c

where d.dbsnum = c.dbsnum

group by 1

order by 1;

Sample output

dbspace        pages_size       pages_used       pages_free     percent_free

rootdbs             50000            13521            36479            72.96

dbspace1           100000            87532            12468            12.47

dbspace2           100000            62876            37124            37.12

dbspace3           100000              201            99799            99.80

Displaying Chunk Status

The next script lists the status and characteristics of each chunk device.

Figure 2: SQL Script - chkstatus.sql

-- Script: chkstatus.sql - display information about a chuck

database sysmaster;

select

	name dbspace,		-- dbspace name

	is_mirrored,		-- dbspace is mirrored 1=Yes 0=No

	is_blobspace,		-- dbspace is blobspace 1=Yes 0=No

	is_temp,		-- dbspace is temp 1=Yes 0=No

	chknum chunknum,	-- chuck number

	fname  device,		-- dev path

	offset dev_offset,	-- dev offset

	is_offline,		-- Offline 1=Yes 0=No

	is_recovering,		-- Recovering 1=Yes 0=No

	is_blobchunk,		-- Blobspace 1=Yes 0=No

	is_inconsistent,	-- Inconsistent 1=Yes 0=No

	chksize Pages_size,	-- chuck size in pages

	(chksize - nfree) Pages_used, 	-- chunk pages used

	nfree Pages_free,	-- chunk free pages

	round ((nfree / chksize) * 100, 2) percent_free, -- free

	mfname mirror_device,	-- mirror dev path

	moffset mirror_offset,	-- mirror dev offset

	mis_offline ,		-- mirror offline 1=Yes 0=No

	mis_recovering 		-- mirror recovering  1=Yes 0=No

from 	sysdbspaces d, syschunks c

where	d.dbsnum = c.dbsnum

order by dbspace, chunknum

Displaying Blocks of Free Space in a Chuck

In planning expansions, new databases, or adding new tables to an existing server, I like to know what blocks of contiguous free space are available. This allows placing new tables in dbspaces where they will not be broken up by extents. One of the sysmaster tables tracks the chunk free list, which is the available space in a chunk.

Table syschfree

	chknum  	integer,	chunk number                 

	extnum  	integer,	extent number in chunk       

	start 		integer,	physical addr of start       

	leng 		integer 	length of extent             

The next script uses this table to create a list of free space and the size of each space that is available.

Figure 3: SQL Script - chkflist.sql

-- Script: chkflist.sql - display list of free space within a chunk

database sysmaster;

select	

	name dbspace,		-- dbspace name truncated to fit

	f.chknum,		-- chunk number

	f.extnum,		-- extent number of free space

	f.start,		-- starting address of free space

	f.leng free_pages	-- length of free space

from 	sysdbspaces d, syschunks c, syschfree f

where	d.dbsnum = c.dbsnum

and 	c.chknum = f.chknum

order by dbspace, chknum

Sample Output 

dbspace                 chknum      extnum       start  free_pages

rootdbs                      1           0       11905        1608

rootdbs                      1           1       15129       34871

IO Statistics by Chunk Devices

Informix uses a view, syschkio, to collect information about the number of disk reads and writes per chunk. This view is based on the tables syschktab and symchktab.

View syschkio 

	chunknum	smallint,	chunk number

	reads		integer,	number of read ops

	pagesread	integer,	number of pages read

	writes		integer,	number of write ops

	pageswritten	integer,	number of pages written

	mreads		integer,	number of mirror read ops

	mpagesread	integer,	number of mirror pages read

	mwrites		integer,	number of mirror write ops

	mpageswritten	integer,	number of mirror pages written

The following script displays IO usage of chunk devices. It uses the base tables so the mirror chunks can be displayed on separate rows. It also joins with the base table that contains the dbspace name:

Figure 4: SQL Script chkio.sql

-- Script chkio.sql - displays chunk IO status

database sysmaster;

select

	name[1,10] dbspace,	-- truncated to fit 80 char screen line

	chknum,

	"Primary" chktype,

	reads,

	writes,

	pagesread,

	pageswritten

from	syschktab c, sysdbstab d

where	c.dbsnum = d.dbsnum

union all

select

	name[1,10] dbspace,

	chknum,

	"Mirror" chktype,

	reads,

	writes,

	pagesread,

	pageswritten

from	sysmchktab c, sysdbstab d

where	c.dbsnum = d.dbsnum

order by 1,2,3;

 

Sample Output

dbspace    chknum chktype       reads      writes   pagesread pageswritten

rootdbs        1  Primary       74209      165064      209177       308004

rootdbs        1  Mirror        69401      159832      209018       307985

A better view of your IO is to see the percent of the total IO that takes place per chunk. This next query collects IO stats into a temp table, and then uses that to calculate total IO stats for all chunks. Then each chunk's IO is compared with the total to determine the percent of IO by chunk. The following script uses the one above as a basis to show IO by chunk as a percent of the total IO.

Figure 5: SQL Script - chkiosum.sql

-- Script chkiosum.sql - calculates percent of IO by chunk

database sysmaster;

-- Collect chuck IO stats into temp table A

select

	name dbspace,

	chknum,

	"Primary" chktype,

	reads,

	writes,

	pagesread,

	pageswritten

from	syschktab c, sysdbstab d

where	c.dbsnum = d.dbsnum

union all

select

	name[1,10] dbspace,

	chknum,

	"Mirror" chktype,

	reads,

	writes,

	pagesread,

	pageswritten

from	sysmchktab c, sysdbstab d

where	c.dbsnum = d.dbsnum

into temp A;

-- Collect total IO stats into temp table B

select

	sum(reads) total_reads,

	sum(writes) total_writes,

	sum(pagesread) total_pgreads,

	sum(pageswritten) total_pgwrites

from	A

into temp B;

-- Report showing each chunks percent of totak IO

select

	dbspace,

	chknum,

	chktype,

	reads,

	writes,

	pagesread,

	pageswritten,

	round((reads/total_reads) *100, 2) percent_reads,

	round((writes/total_writes) *100, 2) percent_writes,

	round((pagesread/total_pgreads) *100, 2) percent_pg_reads,

	round((pageswritten/total_pgwrites) *100, 2) percent_pg_writes

from	A, B

order by 11;-- order by percent page writes



Sample output for 1 chunk

dbspace            	datadbs

chknum             	9

chktype            	Primary

reads              	12001

writes             	9804

pagesread          	23894

pageswritten       	14584

percent_reads      	0.33

percent_writes     	0.75

percent_pg_reads   	37.59

percent_pg_writes  	1.86

Database and Tables Information

Information on All Databases in a Server: sysdatabases

This view has data on all databases in a server. Have you ever needed to create a pop-up list of databases within a program? This table now allows programs to give users a list of databases to select from without resorting to ESQL/C. The following is the definition of this view:

View sysdatabases 

	name		char(18),	database name

	partnum		integer,	table id for systables

	owner		char(8),	user name of creator

	created		integer,	date created

	is_logging	bitval,		unbuffered logging, 1=Yes, 0=No

	is_buff_log	bitval,		buffered logging, 1=Yes, 0=No

	is_ansi		bitval,		ANSI mode database, 1=Yes, 0=No

	is_nls		bitval,		NLS support, 1=Yes, 0=No

	flags		smallint	logging

The following is a script to list all databases, owners, dbspaces, and logging status. Notice the function dbinfo is used. This is a new function in 7.X, with several uses, one of which is to convert the partnum of a database into its corresponding dbspace. This function will be used in several examples that follow.

Figure 6: SQL Script - dblist.sql

-- Script: dblist.sql - List all databases, owner and logging status

database sysmaster;

select

        dbinfo("DBSPACE",partnum) dbspace,

        name database,

        owner,

        is_logging,

        is_buff_log

from	sysdatabases

order by dbspace, name;



Sample Output

dbspace            database           owner     is_logging is_buff_log

rootdbs            central            lester             0           0

rootdbs            datatools          lester             0           0

rootdbs            dba                lester             0           0

rootdbs            roster             lester             0           0

rootdbs            stores7            lester             0           0

rootdbs            sunset             linda              0           0

rootdbs            sysmaster          informix           1           0

rootdbs            zip                lester             1           1

Information About Database Tables: systabnames, sysextents and sysptprof

Three tables contain all the data you need from the sysmaster database about tables in your database. The first of these is a real table defined as follows:

Table systabnames - All tables on the server

  	partnum		integer,	table id for table           

  	dbsname		char(18),	database name                

  	owner		char(8),	table owner                  

  	tabname		char(18),	table name                   

  	collate		char(32)	collation assoc with NLS DB  



View sysextents - Tables and each extent on the server

	dbsname		char(18),	database name

	tabname		char(18),	table name

	start		integer,	physical address for this extent

	size		integer	size of this extent

The view sysextents is based on a table, "sysptnext", defined as follows:

Table sysptnext 

	pe_partnum	integer,	partnum for this partition   

	pe_extnum	smallint,	extent number                

	pe_phys		integer,	physical address for this extent

	pe_size		integer,	size of this extent          

	pe_log		integer		logical page for start       



View sysptprof  - Tables IO profile

	dbsname		char(18),	database name

	tabname		char(18),	table name

	partnum		integer,	partnum for this table

	lockreqs	integer,	lock requests

	lockwts		integer,	lock waits

	deadlks		integer,	deadlocks

	lktouts		integer,	lock timeouts

	isreads		integer,	reads

	iswrites	integer,	writes

	isrewrites	integer,	rewrites

	isdeletes	integer,	deletes

	bufreads	integer,	buffer reads

	bufwrites	integer,	buffer writes

	seqscans	integer,	sequential scans             

	pagreads	integer,	disk reads

	pagwrites	integer		disk writes

These tables allow us to develop scripts to display tables, the number of extents, and pages used. We can also present a layout of dbspace, databases, tables, and extents similar to the command "tbcheck -pe". And finally, show table usage statistics sorted by which tables have the most hits based on reads, writes, or locks. These scripts will enable a DBA to monitor and tune the database server.

Extents are caused when a table's initial space has been filled up and it needs more space. OnLine will allocate additional space for a table. However, the table will no longer be contiguous, and performance will start to degrade. Informix will start to display warning messages when a table reaches more than 8 extents. Depending on a number of factors, at approximately 180-230 extents a table will not be able to expand and no additional rows can be inserted. The following script lists all tables sorted by the number of extents. The tables that show up with many extents may need to be unloaded and rebuilt.

Figure 7: SQL Script tabextent.sql

-- Script: tabextent.sql - List tables, number of extents and size of table.

database sysmaster;

select  dbsname,

        tabname,

        count(*) num_of_extents,

        sum( pe_size ) total_size

from	systabnames, sysptnext

where	partnum = pe_partnum

group by 1, 2 

order by 3 desc, 4 desc;



Sample Output

dbsname            tabname              num_of_extents       total_size

rootdbs            TBLSpace                          8              400

sysmaster          syscolumns                        6               56

sunset             inventory                         3              376

sunset             sales_items                       3               96

sunset             sales_header                      3               48

sunset             parts                             3               48

sunset             customer                          3               40

sunset             syscolumnext                      3               32

sunset             employee                          3               32

Sometimes it is helpful to see how the tables are interspersed on disk. The following script lists by dbspace each table and the location of each extent. This is similar to the output from "oncheck -pe".

Figure 8: SQL Script - tablayout.sql

-- Script: tablayout.sql - Show layout of tables and extents

database sysmaster;

select dbinfo( "DBSPACE" , pe_partnum ) dbspace,

	dbsname[1,10],

	tabname,

	pe_phys start,

	pe_size size

from	sysptnext, outer systabnames

where	pe_partnum = partnum

order by dbspace, start;



Sample output

dbspace            dbsname    tabname                  start        size

rootdbs            rootdbs    TBLSpace               1048589          50

rootdbs            sysmaster  sysdatabases           1050639           4

rootdbs            sysmaster  systables              1050643           8

rootdbs            sysmaster  syscolumns             1050651          16

rootdbs            sysmaster  sysindexes             1050667           8

rootdbs            sysmaster  systabauth             1050675           8

rootdbs            sysmaster  syscolauth             1050683           8

rootdbs            sysmaster  sysviews               1050691           8

rootdbs            sysmaster  sysusers               1050699           8

rootdbs            sysmaster  sysdepend              1050707           8

IO Performance of Tables

Have you ever wanted to know which tables have the most reads, writes, or locks? The last script in this article shows the performance profile of tables. By changing the columns displayed and the sort order of the script, you may have the tables with the most reads or writes or locks display first.

Figure 9: SQL Script tabprof.sql

-- Script: tabprof.sql

database sysmaster;

select

	dbsname,

	tabname,

	isreads,

	bufreads,

	pagreads

	-- uncomment the following to show writes

	-- iswrites,

	-- bufwrites,

	-- pagwrites

	-- uncomment the following to show locks

	-- lockreqs,

	-- lockwts,

	-- deadlks

from	sysptprof

order by isreads desc; -- change this sort to whatever you need to monitor.



Sample Output

dbsname            tabname                isreads    bufreads    pagreads

zip                zip                     334175    35876509        1111

sysmaster          sysviews                259712      634102        1119

sysmaster          systables                60999      240018        1878

zip                systables                 3491        8228         543

sysmaster          sysusers                  2406        8936          87

sysmaster          sysprocauth               1276        5104          12

sunset             systables                  705        2251          26

sysmaster          sysprocedures              640        2562          21

sysmaster          syscolumns                 637        1512          49

stores7            systables                  565        1361          16

sysmaster          sysdatabases               534        2073         902

In the next issue we will discuss monitoring user threads, user statistics, and locks. If you have any questions or suggestions, please send me email at the address below. Also, if you have any creative scripts for monitoring your server with the sysmaster database please, send them in and I will include them in the next article.

Lester Knutsen, has been providing consulting services, performance tuning, database support, training, and tools for Informix database systems for over 12 years

INFORMIX INSTALLATION

and

ONLINE CONFIGURATION PROCEDURES (Part II)

Editors Note: This is the continuation of an article started in the last issue. Chapters 1, Informix Installation Procedures, and 2, OnLine Configuration Procedures were published in our last issue. Please refer to our web site www.access.digex.net/~waiug for these articules.

3. Distributed Database Configuration

Introduction

Refer to Chapter 4 of the OnLine Dynamic Server Administration Guide for a complete discussion on configuration files. This discussion will center around the connectivity requirement for an OnLine Dynamic Server configuration. There are several configuration files involved in setting up a distributed database environment. These files enable a database server to communicate with another database server, as in data replication or distributed joins. The connectivity configuration files can be divided into four groups followed by a discussion on Environment Variables:

Network-configuration files

Network-security files

• $INFORMIXDIR/etc/sqlhosts
• $INFORMIXDIR/etc/onconfig
• Informix Environment Variables

The following sections describe each of these files. Of these files, the OnLine administrator manages only the Informix sqlhosts files. You must have an sqlhosts file on each computer that has either a client application or a database server. The other files are UNIX operating-system files that are managed by the end user. For discussion purposes, let's assume three host machines with a database server running on each of those machines. Additionally, the machine will be running TCP/IP and either sockets or TLI. As a matter of practice I like to make the database server name descriptive of the network protocol being used (i.e. _soc for sockets and _tli for transport layer interface, _shm for shared memory). I also like to make the service name the same as the database server name so that it makes it easy to remember which database server I am accessing. These practices are merely suggestions and not required.

				(INFORMIXSERVER)

	Host machine		DBSERVERNAME  (onconfig)	Service Name	

	host_1			db1_soc or db1_tli		db1_soc or db1_tli

	host_2			db2_soc or db2_tli		db2_soc or db1_tli

	host_3			db3_soc or db3_tli		db3_soc or db1_tli

Network-Configuration Files

When you configure OnLine to use the TCP/IP network protocol, you use information from the network-configuration files /etc/hosts and /etc/services to prepare the sqlhosts file. The /etc/hosts and /etc/services are UNIX files that the network administrator maintains. Whenever you add a host, or a software service such as OnLine database server, you need to inform the network administrator so that he or she can make sure the information contained in these files is accurate.

The /etc/hosts and /etc/services files must be present on each computer than runs an Informix client/server product, or on the NIS server if your network uses Network Information Services (NIS).

/etc/hosts File

The /etc/hosts file needs a single entry for each computer on the network that uses an Informix client/server product. Each line in the file contains the following information:

Internet Address

Host Name

• Host Aliases (optional)

An example /etc/hosts file follows:

198.105.10.1 host_1

198.105.10.2 host_2

198.105.10.3 host_3

/etc/services File

The /etc/services file contains an entry for each service available through TCP/IP. Each entry is a single line containing the following information.

Service name

Port number / protocol

• Aliases (optional)

The service name and port number are arbitrary. As the DBA, you can decide what you will call your services for an OnLine instance. However, they must be unique within the context of the file and must be identical on all computers running Informix client/server products. The service entry that you choose will also be used in the last entry of the $INFORMIXDIR/etc/sqhosts file. An example /etc/services file follows:

Service Name Port / Protocol

db1_soc 1500/tcp

db2_soc 1501/tcp

db3_soc 1502/tcp

The entries in the services file allow and OnLine database server to access the port for client/server communications.

Network-Security Files

Informix products follow standard UNIX security procedures, governed by information contained in the network-security files. For a client application to connect to a database server on a remote computer, the user of the client application must have a valid user ID on the remote computer (that is, entries in the /etc/passwd and if appropriate, /etc/shadow).

~/.netrc File

Users can explicitly specify the user ID and password that is used for connection to the remote computer by putting entries in the UNIX file .netrc. The .netrc is an optional file located in the home directory of each end user computer. The client application can specify a user ID and password in the USER clause of the CONNECT statement. If a user has specified an ID in the ~/.netrc file and the client application has also specified an ID, the user ID and password specified by the client application takes precedence. For more information about the CONNECT statement, refer to the Informix Guide to SQL: Syntax.

/etc/hosts.equiv and ~/.rhosts Files

The /etc/hosts.equiv and ~/.rhosts files are optional UNIX files that can be created and stored on the computer running the database server. They specify which remote hosts and user are trusted by the host computer on which the database server resided. Trusted users are allowed to access the database server computer without supplying a password. The database server uses these files to determine whether a remote client should be allowed access to the server without specifying a password explicitly.

The OnLine administrator file located on the database server computer to specify a list of trusted hosts that can log in without a password to the database server computer. Alternatively, individual users can maintain their own .rhosts file in their home directory on the database server computer. In our scenario of three hosts, let's say that host_1 is a server and host_2 and host_3 are clients. In order for the clients to access the server, host_1 would have the following entries in the /etc/hosts.equiv:

host_2

host_3

host_2

It does not contain host_3 because in this scenario, (user1) does not need to access any database on the host_3 machine.

$INFORMIXDIR/etc/sqlhosts File

The Informix sqlhosts file contains information that you supply that lets a client application find and connect to an Informix database server anywhere on the network. The sqlhosts file must contain an entry (one line) that you supply for each type of connection to each database server on the network. The entries in the sqlhosts file are described above in detail in section 2 - Configuration Files.

An example sqlhosts file is shown below: (from Host_1 perspective).

DB Server Name NETTYPE Host Service

db1_shm onipcshm host_1 db1_shm

db1_soc onsoctcp host_1 db1_soc

db2_soc onsoctcp host_2 db2_soc

db3_soc onsoctcp host_3 db3_soc

In the example above, a user from host_1 will have access to its own databases through shared memory or TCP/IP sockets. The user will also have access to a database server on host_2 and host_3. A user on host_1 could go through DB-ACCESS, select the CONNECT menu option and they would see a list of database server names which they could connect to. Once connected to that server, they could then list ONLY the databases that reside on that database server. Another way would be for the user to set their INFORMIXSERVER environment variable to point to the specific database server name. For example, a user on host_1 could set INFORMIXSERVER=db2_soc; export INFORMIXSERVER. When they run DB-ACCESS or any client application, they would see the database that reside on that database server.

Additionally, a client application (such as esql/c) uses the sqlhosts file when it issues a connection statement as in the following examples:

CONNECT TO '@dbservername';

or

CONNECT TO 'dbname@dbservername';

Please refer to CONNECT statement in the Informix Guide to SQL: Syntax for more information.

$INFORMIXDIR/etc/onconfig File

Their are two parameters in the onconfig file that reference the sqlhosts file. The variables are DBSERVERNAME and DBSERVERALIASES. DBSERVERNAME should match with an entry in the first column of your sqlhosts file. Typically, DBSERVERNAME is set to the shared memory connection for your database server; however, this is not necessary. It may actually point to the network connection type. Typically, the DBSERVERALIASES variable is set the network access entry. For example, using the scenario from above, DBSERVERNAME would be set to db1_shm which represents the shared memory connection for the database server running on host_1. DBSERVERALIASES would be set to db1_soc for the TCP/IP sockets connection type. The other entries in the sqlhosts file which refer to host_2 and host_3 would not be included in the onconfig file for host_1 since they do not apply. Each respective server would have its own entries which apply to it.

Informix Environment Variables

INFORMIXDIR (required)

The INFORMIXDIR environment specifies the directory that contains the subdirectories in which your product files are installed. You must always set INFORMIXDIR. For example: INFORMIXDIR=/usr/informix; export INFORMIXDIR

PATH (required)

The UNIX PATH environment tells the shell which subdirectory to search for executable programs. You must add the directory that contains your Informix product to your PATH before you can use the product. For example: PATH=$PATH:$INFORMIXDIR/bin; export PATH;

INFORMIXSERVER - 7.xx (required)

The INFORMIXSERVER environment variable specifies the default database server to which an explicit or implicit connection is made by an SQL API client or the DB-Access utility. The database server can be either INFORMIX-OnLine Dynamic Server or INFORMIX-SE and can either be local or remote. You must always set INFORMIXSERVER before using an Informix product.

The value of INFORMIXSERVER must correspond to a valid dbservername entry in the $INFORMIXDIR/etc/sqlhosts file on the computer running the application. The dbservername must be specified using lower case characters and cannot exceed 18 characters for OnLine and 10 characters for SE. For example to point to the shared memory connection on host_1 you would set the following:

INFORMIXSERVER=db1_shm; export INFORMIXSERVER

DBPATH (optional)

For OnLine:

Use DBPATH to identify the database servers that contain databases. It also is used by DB-ACCESS look for command scripts (.sql files). If DBPATH is pointing to database servers, the Database menu option in DB-ACCESS will give you a complete list of databases available on your own database server as well as all databases available on the database servers listed in your DBPATH. In our scenario from above, you would set DBPATH as follows if you want DB-ACCESS to recognize remote databases:

DBPATH=//db2_soc://db3_soc; export DBPATH

For INFORMIX-SE

Use DBPATH to identify the directories and / or database servers that contain databases.

ONCONFIG - 7.xx or TBCONFIG 5.0x (optional)

The ONCONFIG environment variable specifies a file that holds configuration parameters for INFORMIX-OnLine Dynamic Server. This file is read as input during startup of OnLine. If ONCONFIG is not specified, the default file will be $INFORMIXDIR/etc/onconfig. The file is initially created when onmoniter copies from the onconfig.std file. If you have multiple instances of OnLine, each instance must have its own uniquely named file.

INFORMIX-STAR 5.0x Configuration

INFORMIX-STAR is the connectivity piece for OnLine 5.0x that provides client/server and distributed database access to OnLine 5.0x. This product is not required with OnLine 7.xx since the connectivity is built into OnLine 7.xx. The following procedure should be used to set up I-STAR.

Login as ROOT.

The INFORMIXDIR environment must be set.

1. The PATH must be set to PATH=$PATH:$INFORMIXDIR/bin.

2. TBCONFIG is optionally set as explained above.

3. You must have a valid service in /etc/services.

4. Execute: $INFORMIXDIR/lib/sqlexecd sqlexec5 -l $INFORMIXDIR/istar.log

The above example start I-STAR for the sqlexec5 service and will log connections in the istar.log file in the Informix home directory.

The above procedure should be put in /etc/rc or /etc/rc2.d so that when the server is rebooted the service will start automatically. If we assume that the Informix software is loaded in /usr/informix, tbconfig is the standard configuration file and sqlexec5 is the service the /etc/rc file would look like:

INFORMIXDIR=/usr/informix; export INFORMIXDIR

PATH=$PATH:$INFORMIXDIR/bin; export PATH

TBCONFIG=tbconfig; export TBCONFIG

tbinit

$INFORMIXDIR/lib/sqlexecd sqlexec5 -l $INFORMIXDIR/istar.log

Distributed Database Example

The following example assumes that any user on any host can access any other database server. The information in the /etc/hosts and the /etc/services will be exactly the same for all three computers. Assume the Informix path is the same as well. That information is provided below:

Network-Configuration Files

/etc/hosts:

IP Address Host Name

198.105.10.1 host_1

198.105.10.2 host_2

198.105.10.3 host_3

/etc/services: (You must have 3 different ports for each dbservername)

Service Name Port / Protocol

db1_soc 1500/tcp

db2_soc 1501/tcp

db3_soc 1502/tcp

(host_1) Configuration Files

Network-Security Files

~/.netrc (optional)

user ID and password

~/.rhosts or /etc/hosts.equiv (must have 1 or the other)

host_2

host_3

DB Server Name NETTYPE Host Service

db1_shm onipcshm host_1 db1_shm

db1_soc onsoctcp host_1 db1_soc

db2_soc onsoctcp host_2 db2_soc

db3_soc onsoctcp host_3 db3_soc

DBSERVERNAME db1_shm

DBSERVERALIASES db1_soc

INFORMIXSERVER=db1_shm; export INFORMIXSERVER (required)

ONCONFIG=onconfig.db1; export ONCONFIG (optional)

DBPATH=//db2_soc://db3_soc; export DBPATH (optional)

(host_2) Configuration Files

Network-Security Files

~/.netrc (optional)

user ID and password

~/.rhosts or /etc/hosts.equiv (must have 1 or the other)

host_1

host_3

DB Server Name NETTYPE Host Service

db2_shm onipcshm host_2 db2_shm

db2_soc onsoctcp host_2 db2_soc

db1_soc onsoctcp host_1 db1_soc

db3_soc onsoctcp host_3 db3_soc

DBSERVERNAME db2_shm

DBSERVERALIASES db2_soc

INFORMIXSERVER=db2_shm; export INFORMIXSERVER (required)

ONCONFIG=onconfig.db2; export ONCONFIG (optional)

DBPATH=//db1_soc://db3_soc; export DBPATH (optional)

(host_3) Configuration Files

Network-Security Files

~/.netrc (optional)

user ID and password

~/.rhosts or /etc/hosts.equiv (must have 1 or the other)

host_2

host_3

DB Server Name NETTYPE Host Service

db3_shm onipcshm host_3 db3_shm

db3_soc onsoctcp host_3 db3_soc

db1_soc onsoctcp host_1 db1_soc

db2_soc onsoctcp host_2 db2_soc

DBSERVERNAME db3_shm

DBSERVERALIASES db3_soc

INFORMIXSERVER=db3_shm; export INFORMIXSERVER (required)

ONCONFIG=onconfig.db3; export ONCONFIG (optional)

DBPATH=//db1_soc://db2_soc; export DBPATH (optional)

4. Starting and Stopping OnLine

It is possible to put the following scripts in the /etc/rc2.d directory to enable the automatic startup and shutdown of OnLine during system boot.

Example script for starting OnLine 7.xx

INFORMIXDIR=/export/home/informix7; export INFORMIXDIR

PATH=.:$INFORMIXDIR/bin:$PATH; export PATH

ONCONFIG=onconfig.test; export ONCONFIG

INFORMIXSERVER=test_shm; export INFORMIXSERVER

oninit

Example script for stopping OnLine 7.xx

INFORMIXDIR=/export/home/informix7; export INFORMIXDIR

PATH=.:$INFORMIXDIR/bin:$PATH; export PATH

ONCONFIG=onconfig.test; export ONCONFIG

INFORMIXSERVER=test_shm; export INFORMIXSERVER

onmode -ky

Example script for starting OnPerf or DB/Cockpit in OnLine 7.xx

Note: This requires Motif running.

Generic Settings:

INFORMIXDIR=/export/home/informix7; export INFORMIXDIR

PATH=.:$INFORMIXDIR/bin:$PATH; export PATH

ONCONFIG=onconfig.test; export ONCONFIG

INFORMIXSERVER=test_shm; export INFORMIXSERVER

Settings for Solaris 2.4:

DISPLAY=:0.0; export DISPLAY

PATH=$PATH:/usr/openwin/bin:/opt/SUNWmotif/bin; export PATH

LD_LIBRARY_PATH=/opt/SUNWmotif/lib:/usr/openwin/lib:/usr/ucblib:/usr/dt/lib;

export LD_LIBRARY_PATH

OW_WINDOW_MANAGER=mwm; export OW_WINDOW_MANAGER

Settings for HP-UX: (hostname is the name of your host)

DISPLAY=hostname:0.0; export DISPLAY

SHLIB_PATH=$INFORMIXDIR/lib:/lib:/usr/lib:/usr/lib/X11R5:/usr/lib/Motif1.2;

export SHLIB_PATH

To run DB/Cockpit (Graphical Database Maintenance):

NOTE: DB/Cockpit assumes their is a service in /etc/services. For example you might have the following entry:

cockpit 1554/tcp

onprobe -service cockpit -log $INFORMIXDIR/onprobe.log -severity $INFORMIXDIR/etc/severity

oncockpit &

To run OnPerf (Performance Monitor):

onperf

Example script for starting OnLine 5.0x

INFORMIXDIR=/export/home/informix5; export INFORMIXDIR

PATH=.:$INFORMIXDIR/bin:$PATH; export PATH

TBCONFIG=tbconfig.test; export TBCONFIG

tbinit

sleep 4

$INFORMIXDIR/lib/sqlexecd -s sqlexec -l $INFORMIXDIR/sqlexecd.log

Example script for stopping OnLine 5.0x

INFORMIXDIR=/export/home/informix5; export INFORMIXDIR

PATH=.:$INFORMIXDIR/bin:$PATH; export PATH

TBNCONFIG=tbconfig.test; export TBCONFIG

tbmode -ky

Example script for running NewEra (Motif)

Example of NewEra running on Solaris 2.4:

INFORMIXDIR=/export/home/informix7; export INFORMIXDIR

ONCONFIG=onconfig.test; export ONCONFIG

# INFORMIXSERVER=test_shm

# Changed to TLI because NEWERA 2.0 for Motif seems to have a problem with the

# shared memory connection (_shm).

INFORMIXSERVER=test_tli; export INFORMIXSERVER

PATH=.:$INFORMIXDIR/bin:$PATH:/usr/openwin/bin:/opt/SUNWmotif/bin:/usr/ccs/bin:/opt/gnu/bin; export PATH

LD_LIBRARY_PATH=/opt/SUNWmotif/lib:/usr/openwin/lib:/usr/ucblib:/compilers/lib:/compiler/lib:$INFORMIXDIR/lib/newera:/usr/dt/lib; export LD_LIBRARY_PATH

OW_WINDOW_MANAGER=mwm; export OW_WINDOW_MANAGER

alias cc=gcc

export INFORMIXFGLAPPS=$INFORMIXDIR/etc/fglapps

Execute from the command line to start NewEra: newera

Example script for NewEra (Motif) Application Partitioning Service:

This script must be started by root. This script assumes that test_part is a service in the /etc/services file. Their must also be an entry in $INFORMIXDIR/etc/sqlhosts. An example entry follows:

	/etc/services:	test_part	1555/tcp

	sqlhosts:	my_test		sesoctcp	hostname	test_part

#--Set Environment for INFORMIX OnLine 7.xx and NewEra (Motif)

INFORMIXDIR=/export/home/informix7; export INFORMIXDIR

PATH=.:$INFORMIXDIR/bin:$PATH; export PATH

ONCONFIG=onconfig.test; export ONCONFIG

INFORMIXSERVER=test_shm; export INFORMIXSERVER

$INFORMIXDIR/lib/fglexecd test_part -s fglexec -l /tmp/test.log

ps -ef | grep fglexec

Excerpted from INFORMIX INSTALLATION AND CONFIGURATION GUIDE, by Mike Tinius, Informix Software, Inc. This part consists of chapters 3 and 4 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.