Appendix: Administering PeopleSoft Databases on Informix

This appendix discusses:

• Database terminology.

• DBspace strategy.

• Database server directory structure.

• Troubleshooting model.

Database Terminology

PeopleSoft uses the following technical naming conventions for Informix databases:

Working With Dbspaces

This section discusses:

• Dbspace strategy.

• Dbspace sizing.

Dbspace Strategy

The following are some dbspace strategies to consider:

• Separate the root dbspace, physical logs, logical logs, and the temporary dbspace from one another and from the application dbspaces.

  Place the root dbspace, logical log, and physical log in separate dbspaces on separate disks.

• Separate certain high volume application tables to optimize performance.

  A minimum configuration for production systems is four physical drives (at least one each for database files, physical logs, logical logs, and temporary area).

• Separate the dbspace for data on one drive and the dbspace for indexes on a separate drive.

Dbspace Sizing

This section provides an overview and discusses:

• Creating dbspaces.

• Setting Buffer Pools.

• Setting the PHYSBUFF Parameter.

Understanding Dbspace Size

Informix Dynamic Server enables you to configure the page size of standard or temporary dbspace a with non-default page size. By default, PeopleTools DDL scripts set dbspaces to 4KB regardless of the default page size of the operating system.

Advantages of having larger page sizes include:

• Dbspaces of larger page sizes can support larger index key sizes.

• More items can fit in a larger index page and there will be a decreased number of levels in a btree index, which reduces index scan times.

• Increased access efficiency because with a larger page, larger numbers of rows get transferred to/from shared memory/disk, which means fewer I/O operations for data and indices.

• Longer rows can fit into a single page reduces data access times due to the decreased number of pages read per row.

• Pages large enough to fit "oversized" rows eliminate the overhead of access time for remainder pages.

• Checkpoint time is typically reduced with larger pages.

Creating Dbspaces

PeopleTools delivers two scripts, ptddl.sh and createtblspace.sh, located in <PS_HOME>/scripts/unix directory. The ptddl.sh script is executed by the Database Creation Wizard to create dbspaces required by a PeopleTools Demo database. The createtblspace.sh script is executed when the database needs to be created using only two dbspaces, as in pstable1 and psindex1.

The –k <page_size> option of the onspaces command sets the dbspace size. The default page size is set to 4K, however, you can set the dbspace to a custom value for your site's requirements (6K, 8K , 16K, and so on).

Example ptddl.sh:

...
onspaces -c -d <DBSPACE> -k 4 -p </path>/<DBSPACE>.dbf -o 0 -s 30000
...
onspaces -c -d PSIMAGE -k 4 -p </path>/PSIMAGE.dbf -o 0 -s 150000
...
onspaces -c -d PSIMGR -k 4 -p </path>/PSIMGR.dbf -o 0 -s 5000
...
onspaces -c -d PTWORK -k 4 -p </path>/PTWORK.dbf -o 0 -s 5000

Example createtblspace.sh:

...
echo "Creating dbspace pstable1..."
   $INFORMIXDIR/bin/onspaces -c -d pstable1 -k 4  -p $1/pstable1.dbf -o 0 -s $2
   echo "Creating dbspace psindex1..."
   $INFORMIXDIR/bin/onspaces -c -d psindex1 -k 4 -p $1/psindex1.dbf -o 0 -s $3
   echo "Running a Level 0 Archive..."
   $INFORMIXDIR/bin/ontape -s -L 0

Note. You can define a different page size for temporary tables, so that they have a separate buffer pool.

Note. Rootdbs and other critical dbspaces, logdbs and physdbs, are created with the page size specified by the operating system.

Setting Buffer Pools

When creating a dbspace of non-default page size it requires a corresponding BUFFERPOOL configuration parameter in the ONCONFIG file. If it is not defined, the database server automatically adds an entry in the ONCONFIG file, which would be based on default value of BUFFERPOOL.

For example, if the onspaces command creates a new dbspace with a 4 KB page size, the database server takes the values of buffers, lrus, lru_min_dirty, and lru_max_dirty from the BUFFERPOOL default line, unless there already is a BUFFERPOOL entry for that page size.

The following example shows how the database server would automatically add a BUFFERPOOL entry for 4 KB based on the default values:

BUFFERPOOL
default,buffers=1000,lrus=8,lru_min_dirty=50.000000,lru_max_dirty=60.000000
BUFFERPOOL
size=2K,buffers=5000,lrus=8,lru_min_dirty=50.000000,lru_max_dirty=60.000000
BUFFERPOOL
size=4K,buffers=1000,lrus=8,lru_min_dirty=50.000000,lru_max_dirty=60.000000

Buffer pools can also be added using the onparams command. For example:

onparams -b -g 4 -n 3000 -r 2 -x 2.0 -m 1.0

This example adds 3000 buffers of 4KB each with 2 LRUS with a maximum dirty of 2% and minimum dirty of 1%. When using the onparams utility to add a buffer pool or when adding a dbspace with a different page size with the onspaces utility, the information specified is automatically appended to the ONCONFIG file and new values are specified using the BUFFERPOOL keyword.

You cannot change the values by editing the onconfig file. If you need to resize or delete an existing buffer pool, you must restart the database server and then run onparams again.

Buffer pools that are added while the database server is running go into virtual memory, not into resident memory. Only those buffer pool entries that are specified in the ONCONFIG file at startup go into resident memory, depending on the availability of the memory you are using.

Setting the PHYSBUFF Parameter

It is recommended to set the PHYSBUFF configuration parameter to at least 128 kilobytes. If the database server is configured to use RTO_SERVER_RESTART, set the PHYSBUFF configuration parameter to at least 512 kilobytes. Setting PHYSBUFF to a lower value could impact transaction performance and will result in a performance warning during server initialization.

Database Server Directory Structure

The environment variable $INFORMIXDIR points to the directory where Informix is installed on your machine; normally this is set to /usr/INFORMIX. The standard Informix directory structure is built under INFORMIX directory by the Informix install process.

The standard Informix architecture uses the “Two-Task Model.” In this architecture, when a user connects to the database server a network thread is created to handle the network processing for that user.

Each Informix server instance consists of the following pieces:

• At least eight database processes that operate the database.

• At least three shared memory segments, through which the database processes communicate.

• The $INFORMIXDIR/etc/sqlhosts file holds networking parameters for each accessible server instance.

  Entries include server name, network protocol, host name and tcp-ip service.

• The $INFORMIXDIR/etc/$ONCONFIG file.

  This file primarily holds shared memory configuration parameters for the local server instance. These include the number of buffers, number of locks, size of the initial shared memory segment, and so on The “onconfig” file also includes pointers to the root dbspace, the temporary dbspace, and the physical log dbspace, as well as the names of the backup tape devices. By convention, these files are often given a name such as onconfig.inf11, where inf11 is the name of the server. This is helpful when managing multiple server instances on one host.

• Database “chunks.”

  Hold the data stored in the database. Under UNIX, these may be either “raw” or “cooked” files. In either case they should be stored in a common directory, with links pointing to their physical locations, if necessary.

See Also

Administrator’s Guide for Informix Dynamic Server

Troubleshooting Model

This section discusses some steps you can take to diagnose system signon problems. Understanding basic operations and process flow is essential when you are troubleshooting connectivity errors. Use the following model as a reference for this section.

1. Test terminal connection.

   Try using TELNET, or a similar network utility, to get a terminal connection to your database server. If this succeeds, you probably have a problem with the way Informix-Connect or Informix is set up. Check to see if the Informix database server is active. If Step 2 fails, then the problem is within the networking layer.

2. Consult your networking experts.

   The problem has been isolated to something within the network layer. Try to isolate the network problem. Can you log on to other servers? Are other terminals still able to connect? Try lowering level network diagnostics, if they exist