pajama

Overview

Starting with Continuus/CM Release 4.5, the Informix Server's default configuration was modified to use the unbuffered logging method for maintaining its logical log buffers for each database.   The purpose of this bulletin is to:

·        Explain logical logging and the various logging modes

·        Describe the steps in determining which logging method is currently be used for each database within an existing Informix online Server (UNIX and NT Servers).

·        Outline how to change the logging mode (UNIX and NT Servers).

Details  

Informix Servers configured with logging allow the server to recover from system crashes, such as power outages or system crashes, to a consistent state. The Informix Server uses the logical log (a collection of logs) to store database transactions (changes). The goal is to first restore the system to its most recent point of known physical consistency, which is the last checkpoint. It then applies the transactions found in the logical logs to the system, replaying all the transactions since the time of the checkpoint.   It rolls back any transactions that were not committed at the time of the failure.   With Informix servers, the same set of logs is shared by all databases; transactions from different databases are interleaved within the transaction logs.

Because the amount of data written to these logs can be quite large, the data is first cached in buffers, to be flushed out to disk at a later point. This helps reduce the amount of physical I/O needed to write the data to disk. By default, the server allocates three buffers for use with the logical logs and stores them in the Resident Portion of shared memory. 

The flushing of the logical log buffer is determined by the logging mode of the database(s) being accessed as follows: 

Buffered logging The buffer will not be flushed until it is full. There is a potential loss of data should a system crash occur before the buffer is flushed to disk.

Unbuffered logging The buffer is flushed as soon as any transaction is complete (i.e. a commit or rollback record is written to the log buffer). This mode guarantees that the log data will be saved.

If there are transactions against databases with both buffered and unbuffered logging (i.e. some databases use buffered logging, others use unbuffered logging), then the buffer will be flushed either when it is full or when transactions against the database(s) with unbuffered logging complete. The events that cause the logical log buffer to be flushed to disk:

·         A checkpoint.

·         One of the logical log buffers becomes full.

·         A transaction is completed in a database that uses unbuffered logging.

·         A database session ends for a database that does not use transaction logging. 

Note: By default, CM Synergy databases are set up to have LTAPEDEV set to /dev/null. Therefore, when the logical logs get full, the backup of the logical logs is thrown away. Essentially, transaction logging is turned off. The reason for this is that most sites are not willing to incur the overhead of swapping out tapes or backing up files when the logs become full to gain the ability to recover to the latest transaction. 

Determining Current Logging Mode

Informix 5.x (C/CM 4.5 on UNIX)

All UNIX-based Informix Servers in CM Synergy versions up to and including 4.5 use Informix version 5.X. one method to determine the logging mode of a database using Informix 5.X is to login as user informix on the server machine, set the environment variables and run "tbmonitor".   The steps are outlined below: 

1.        Login as user informix on the server machine.

2.        Set the Informix environment variables (assuming CCM_HOME is set correctly) : 

using csh:

                % setenv INFORMIXDIR $CCM_HOME/informix

                % setenv TBCONFIG <servername>

                % setenv PATH $INFORMIXDIR/bin:$PATH 

using sh or ksh:        

                % INFORMIXDIR=$CCM_HOME/informix; export INFORMIXDIR

                % TBCONFIG=<servername>; export TBCONFIG

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

3.    Start the Informix tbmonitor facility.

      %tbmonitor

4.      Select Status (User arrow keys to highlight and then hit Enter).

5.      Select Databases and scroll down for the database you are interested in. The log status will

be in the right hand column. (B=buffered logging, U=unbuffered logging, N=no logging)

6.     Press ESC to return to the Status Menu, then select Exit -> Exit to exit tbmonitor.

Informix 7.x (all C/CM 5.0, 5.0.1, 5.1 and C/CM 4.5 on Windows NT) and Informix 9.x (5.0, 5.0.1, 5.1 on LINUX)

The logging mode of the database can be determined by querying the sysdatabases table from the sysmaster database on the server machine and checking the column of "is_logging" and "is_buff_log". The steps are outlined below: 

1.        Login as user informix on the server machine.

2.        Set the Informix environment variables by executing %CCM_HOME%\informix\setenv.cmd on Windows NT, or on the other platforms as follows : 

Set the environment variables: INFORMIXDIR, INFORMIXSERVER, onCONFIG and PATH: 

using csh:

                % setenv INFORMIXDIR $CCM_HOME/informix

                % setenv INFORMIXSERVER <servername>

                % setenv PATH $INFORMIXDIR/bin:$PATH 

using sh or ksh:        

                 % INFORMIXDIR=$CCM_HOME/informix; export INFORMIXDIR

                % INFORMIXSERVER=<servername>; export INFORMIXSERVER

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

3.        Run "dbaccess".

4.        Choose the following menus: (Use arrow keys to highlight and hit enter).

      Database -> Select -> sysmaster -> Exit -> Query Language -> New

5.        Type the following (spaces are important before and after the '=') : 

select * from sysdatabases where name = "dbname"  

(Replace dbname with the name of the database in all lower case letters.) 

6.    Hit the ESC key and then choose Run from the menu. 

      The output should appear as below: 

            name        dbname

            partnum     3145760

            owner         ccm_root

            created     05/11/1998

            is_logging   1

            is_buff_log 0

            is_ansi        0

            is_nls         0

            flags          1

  If "is_logging" is 1 then the database is in logging mode (can be buffered or unbuffered).

  If "is_buff_log" is 1 then it's buffered logging. 

7.    Select Exit -> Exit to exit dbaccess.

Change Logging Mode

Informix 5.x (C/CM 4.5 on UNIX)

Switching between buffered and unbuffered logging can be done using tbtape.

1.        Run "su informix" to become user informix on server machine.

2.        Set up environment: 

using csh:

                % setenv INFORMIXDIR $CCM_HOME/informix

                % setenv TBCONFIG <servername>

                % setenv PATH $INFORMIXDIR/bin:$PATH 

using sh or ksh:        

                % INFORMIXDIR=$CCM_HOME/informix; export INFORMIXDIR

                % TBCONFIG=<servername>; export TBCONFIG

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

3.      Run "tbtape -U dbname" will set the logging mode to Unbuffered logging.

4.      Run "tbtape -B dbname" will set the logging mode to Buffered logging.

Informix 7.x (all C/CM 5.0, 5.0.1, 5.1 and C/CM 4.5 on Windows NT) and Informix 9.x (5.0, 5.0.1, 5.1 on LINUX)

Switching between buffered and unbuffered logging can be done using ontape.

1.        Login as user ccm_root or informix on Server machine.

2.        Change directory to %CCM_HOME\informix.

4.        Set the Informix environment variables by executing %CCM_HOME%\informix\setenv.cmd on Windows NT, or on the other platforms as follows : 

Set the environment variables: INFORMIXDIR, INFORMIXSERVER, onCONFIG and PATH: 

using csh:

                % setenv INFORMIXDIR $CCM_HOME/informix

                % setenv INFORMIXSERVER <servername>

                % setenv onCONFIG <servername>

                % setenv PATH $INFORMIXDIR/bin:$PATH

using sh or ksh:        

                % INFORMIXDIR=$CCM_HOME/informix; export INFORMIXDIR

                % INFORMIXSERVER=<servername>; export INFORMIXSERVER

                % onCONFIG=<servername>; export onCONFIG

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

4.        Run ontape -U <dbname>" will set the logging mode to Unbuffered logging.

5.        Run ontape -B <dbname>" will set the logging mode to Buffered logging.

STEPS

To determine the processor type under AIX, at the shell command prompt, type:

prtconf | grep -i "Processor Type"

$ prtconf | grep -i "Processor Type"
Processor Type: PowerPC_POWER4

$ prtconf | grep -i "Processor Type"
Processor Type:  PowerPC_RS64-II

INTRODUCTION

You may need to find the IP address or addresses for your Digital Unix (OSF1) system, but may not know whether the system contains more than one Network Interface Card (NIC).


METHOD

On Digital Unix (OSF1) systems, the command ifconfig supports an option (-a) to list all of the interfaces on the machine. The output of ifconfig -a varies among operating systems, but should include the IP address of any Network Interface Card (NIC) on the machine.

The interface name will be a 3 to 4 character indicator like hme0, en0 or lc1. Each entry will always begin with the interface name and a colon. This will usually be followed by a set of flags. The flag LOOPBACK indicates that the interface is the local loop back interface and not an actual NIC. Somewhere in the entry for an NIC there is a keyword of inet followed by an IP address. This is the IP address of the interface.

Execute ifconfig -a and look for entries with IP addresses that are not 127.0.0.1. The IP addresses are preceded by inet. 

Note: Some systems that support IP version 6 (IPv6) will have two interfaces for each NIC, one for IPv6 and the other for IP version 4 (IPv4). The IPv6 interface will not have an inet parameter, instead it will have an inet6 parameter that will not look like an IP address. Instead of decimal numbers separated by periods an IPv6 address is made up of hexadecimal numbers separated by colons.


Examples:

Output from an Digital Unix (OSF1) machine:

The output is for a machine with only one Network Interface Card (NIC). Notice that even though there is only one NIC, there are 3 interfaces. The lo0 interface is for the local loop back. You can tell this by looking at the inet (IP address) of 127.0.0.1 and also by the LOOPBACK flag. 

The real NIC is tu0 and the IP address is given in the inet value (109.25.146.121). 

$ /usr/sbin/ifconfig -a
lo0: flags=100c89<UP,LOOPBACK,NOARP,MULTICAST,SIMPLEX,NOCHECKSUM>
     inet 127.0.0.1 netmask ff000000 ipmtu 4096

sl0: flags=10<POINTOPOINT>

tu0: flags=c63<UP,BROADCAST,NOTRAILERS,RUNNING,MULTICAST,SIMPLEX>
     inet 109.25.146.121 netmask ffffff00 broadcast 109.25.146.255 ipmtu 1500


Finding ifconfig

The ifconfig command may not be in your default PATH. Look in the following directories:

• /usr/bin
• /etc
• /usr/etc
• /sbin
• /usr/sbin

INTRODUCTION

You may need to find the IP address or addresses for your AIX system, but may not know whether the system contains more than one Network Interface Card (NIC).


METHOD

On AIX systems, the command ifconfig supports an option (-a) to list all of the interfaces on the machine. The output of ifconfig -a varies among operating systems, but should include the IP address of any Network Interface Card (NIC) on the machine.

The interface name will be a 3 to 4 character indicator like hme0, en0 or lc1. Each entry will always begin with the interface name and a colon. This will usually be followed by a set of flags. The flag LOOPBACK indicates that the interface is the local loop back interface and not an actual NIC. Somewhere in the entry for an NIC there is a keyword of inet followed by an IP address. This is the IP address of the interface.

Execute ifconfig -a and look for entries with IP addresses that are not 127.0.0.1. The IP addresses are preceded by inet. 

Note: Some systems that support IP version 6 (IPv6) will have two interfaces for each NIC, one for IPv6 and the other for IP version 4 (IPv4). The IPv6 interface will not have an inet parameter, instead it will have an inet6 parameter that will not look like an IP address. Instead of decimal numbers separated by periods an IPv6 address is made up of hexadecimal numbers separated by colons.


Examples:

Output from an AIX machine: 

The output shows that interface en0 is an actual NIC that has an internet (IP) address of 10.25.16.1 (IP addresses have been changed as a security precaution). The device lo0 is a local loop back as shown by the LOOPBACK flag and the internet address of 127.0.0.1

$ ifconfig -a
en0: flags=4e080863< UP,BROADCAST,NOTRAILERS,RUNNING,
SIMPLEX,MULTICAST,GROUPRT,64BIT,PSEG>
     inet 10.25.16.1 netmask 0xffffff00 broadcast 109.25.146.145
lo0: flags=e08084b < UP,BROADCAST,LOOPBACK,RUNNING,SIMPLEX,
MULTICAST,GROUPRT,64BIT>
     inet 127.0.0.1 netmask 0xff000000 broadcast 127.255.255.255
     inet6 ::1/0


Finding ifconfig

The ifconfig command may not be in your default PATH. Look in the following directories:

• /usr/bin
• /etc
• /usr/etc
• /sbin
• /usr/sbin

When tuning IBM® Informix® database servers you need to know the amount of memory on your IBM AIX® system. The values you use in the Shared Memory Parameters section of the onCONFIG configuration file determine the amount of memory the database server needs. The amount of memory on the computer limits how much memory the database server has available. 

On AIX you can use a combination of the commands lsdev and lsattr to determine how much memory the computer has. These commands (located in /usr/sbin) are used to display the system devices and their attributes. 


STEPS

1. Log in as user informix 

2. Run the command lsdev -C | grep mem

3. For each device displayed in step 2., run the command:

lsattr -El memX

memX
Device name from step 2.


Example:


This example is from a system with 3 GB of physical memory and one memory device named mem0.

$ lsdev -C | grep mem

mem0       Available 00-00        Memory


$ lsattr -El mem0

size   3072 Total amount of physical memory in Mbytes  False

When tuning IBM Informix® database servers you need to know the amount of memory on your Solaris™ system. The values you use in the Shared Memory Parameters section of the onCONFIG configuration file determine the amount of memory the database server needs. The amount of memory on the computer limits how much memory the database server has available. 

On Solaris, you can use the command prtconf to determine how much physical memory the computer has. This command (located in /usr/sbin) displays the total amount of memory for the computer in megabytes. 


STEPS

1. Log in as any user. Superuser or user informix is not required.

2. Run the command  prtconf | grep Mem

Example:

This is an example from a system with 2048 megabytes (2 GB) of physical memory.

$ prtconf | grep Mem

Memory size: 2048 Megabytes

For Informix Dynamic Server version 7.31, "cdr list repl" command does not tell you whether it is a "Update Anywhere" or "Primary Target" replication. 

One method of checking the ER type is by checking partdef and repdef table from syscdr database for the definition, and finding out whether they are using Primary Target or Update Anywhere. If it is Update Anywhere, the partmode will be "P" for the repid entry. If it is Primary Target, you should see a "P" and "R" for the pair. 


For both 7.31 and 10.00, you can check from syscdr using the following SQL (you can modify the query to your own needs): 

dbaccess -> syscdr-> query 
select a.repid, a.repname, a.repstate, b.partstate, b.partmode, 
b.flags, b.selecstmt, b.servid from repdef a, partdef b 
where a.repid = b.repid; 

Example Results: 

From below results, you can tell there are 2 types of replications: 

1. repid 6881287( servid 105, servid 106) is update anywhere, as both 
partmode is "P", means Primary. 

2. repid 6881294 ( servid 105, servid 106) is Primary ->target type 
replication, as servid 105 's partmode is "P" and servid 106 's partmode is "R", 
means read only. 

repid 6881287 
repname rep_state 
repstate 2 
partstate 2 
partmode P 
flags 0 
selecstmt select * from state 
servid 105 

repid 6881287 
repname rep_state 
repstate 2 
partstate 2 
partmode P 
flags 0 
selecstmt select * from state 
servid 106 

repid 6881294 
repname rep_customer 
repstate 2 
partstate 2 
partmode P 
flags 0 
selecstmt select * from customer 
servid 105 

repid 6881294 
repname rep_customer 
repstate 2 
partstate 2 
partmode R 
flags 0 
selecstmt select * from customer 
servid 106 



For IDS 9.40, 10.00 and above, you can also check from the command line using " cdr list repl ".

Example Results: 

1. for replication "rep_state, you can see the REPLMODE are both "PRIMARY for both servers. Hence, we know this is an update anywhere type. 

2. for replication "rep_customer, you can see the REPLMODE fro server 105 is "PRIMARY" and for server 106 is : READ-ONLY". Hence, we know this is an Primary - Target type. 

Sever 105 
> cdr list repl 

CURRENTLY DEFINED REPLICATES 
------------------------------- 
REPLICATE: rep_state 
STATE: Inactive on:g_er1 
CONFLICT: Timestamp 
FREQUENCY: immediate 
QUEUE SIZE: 0 
PARTICIPANT: stores_demo:informix.state 
OPTIONS: transaction,ats,fullrow 
REPLID: 6881287 / 0x690007 
REPLMODE: PRIMARY on:g_er1 
APPLY-AS: INFORMIX on:g_er1 

REPLICATE: rep_customer 
STATE: Inactive on:g_er1 
CONFLICT: Ignore 
FREQUENCY: immediate 
QUEUE SIZE: 0 
PARTICIPANT: stores_demo:informix.customer 
OPTIONS: transaction,ats,fullrow 
REPLID: 6881294 / 0x69000e 
REPLMODE: PRIMARY on:g_er1 
APPLY-AS: INFORMIX on:g_er1 


Sever 106 
> cdr list repl 

CURRENTLY DEFINED REPLICATES 
------------------------------- 
REPLICATE: rep_state 
STATE: Inactive on:g_er2 
CONFLICT: Timestamp 
FREQUENCY: immediate 
QUEUE SIZE: 0 
PARTICIPANT: stores_demo:informix.state 
OPTIONS: transaction,ats,fullrow 
REPLID: 6881287 / 0x690007 
REPLMODE: PRIMARY on:g_er2 
APPLY-AS: INFORMIX on:g_er2 

REPLICATE: rep_customer 
STATE: Inactive on:g_er2 
CONFLICT: Ignore 
FREQUENCY: immediate 
QUEUE SIZE: 0 
PARTICIPANT: stores_demo:informix.customer 
OPTIONS: transaction,ats,fullrow 
REPLID: 6881294 / 0x69000e 
REPLMODE: READ-ONLY on:g_er2 
APPLY-AS: INFORMIX on:g_er2

When tuning various IBM Informix® products you need to know the number of processors on your system. The NUMCPUVP or VPCLASS cpu ONCONFIG configuration parameter, for instance, should be set to one less than the actual number of physical CPUs present in the server. 

On Sun Solaris you can determine how many processors your system has by using the unamecommand. Use the -X option to uname (located in /usr/bin) and look for the value of the parameter NumCPU. The -X option displays expanded system information as expected by SCO UNIX. See the man page for more information on uname -X. Use this command line:

/usr/bin/uname -X

Example:

This example is from a machine with 4 CPUs. It also uses grep to limit the output.


$ uname -X | grep Num
NumCPU = 4

In order to determine what version of IBM Informix C-ISAM is installed on your system, run thedblog command that came with C-ISAM and give it a single option of -V. This will print out the product version as well as the serial number that was used to install the product.

Note: For historical reasons the -V option reports the product name as INFORMIX-SQL rather than C-ISAM.

Example:

In this example C-ISAM version 7.25.UC1 was installed in the directory/product/cisam725uc1 using a serial number of ACN#J266163.

$ /product/cisam725uc1/bin/dblog -V
INFORMIX-SQL Version 7.25.UC1
Software Serial Number ACN#J266163

Example:

In this example C-ISAM version 5.10.UD4 was installed in the directory/product/cisam510ud4 using a serial number of ACN#J266163.

$ /product/cisam510ud4/dblog -V
INFORMIX-SQL Version 5.10.UD4  
Software Serial Number ACN#J266163