pajama

High Performance Loader: Mode Comparison for Load Jobs

PROBLEM

Depending upon how you create primary/foreign key constraints, IDS will generate names for them and the indexes that are used to enforce them. These names are not intuitive (like customer_pkind, customer_fk1), which causes confusion when viewing or dropping them.

Example:

If you create a table like this:

create table customer
      ( customer_num            serial(101),
        fname                   char(15),
        lname                   char(15),
        company                 char(20),
        address                 char(200),
        phone                   char(18),
       primary key (customer_num)
      );

Then you will end up with an index called ' 100_1' (note the leading space), and the constraint name is 'u100_1'. These names are generated based on the tabid (customer is tabid 100) and a sequence number. The index can't be dropped directly, because the name has a leading space character.


Adding a primary key after creating the table (with alter table) hits the same generated name issue:

  alter table customer add constraint primary key (customer_num) ;

The index name is now ' 100_26' and the constraint name is 'u100_26'.

Example:

alter table customer add constraint 
primary key (customer_num) constraint cust_pk ;

create table customer
      ( customer_num            serial(101),
        fname                   char(15),
        lname                   char(15),
        company                 char(20),
        address                 char(200),
        phone                   char(18),
       primary key (customer_num) constraint cust_pk
      );

Example:

create unique index cust_pki on customer(customer_num) ;
alter table customer add constraint
  primary key (customer_num) constraint cust_pkc ;

Q. How can I resolve various "unknown terminal" errors I receive when running dbaccess?

A. Set the environment variables TERM and TERMCAP to point to $INFORMIXDIR/etc/termcap.

Example:

For csh:
setenv TERM vt100
setenv TERMCAP $INFORMIXDIR/etc/termcap

For ksh :
export TERM=vt100
export TERMCAP=$INFORMIXDIR/etc/termcap

You may use this SQL query to identify all indices that have been marked as "bad" in your IDS instance:

(SELECT st.tabname, si.idxname, hex(spt.flags) flags
FROM  sysindexes si, systables st, sysmaster:sysptnkey spt
WHERE st.tabid = si.tabid AND
st.partnum = spt.partnum AND
sysmaster:bitval(spt.flags, 64) = 1 AND
st.partnum !=0 AND
si.idxname NOT IN  (
         SELECT sfs.indexname
         FROM sysfragments sfs
         WHERE sfs.tabid = st.tabid)
GROUP BY 1,2,3
)
UNION ALL
(SELECT st.tabname, sf.indexname idxname , hex(spt.flags) flags
FROM  systables st, sysmaster:sysptnkey spt, sysfragments sf
WHERE st.tabid = sf.tabid
AND sf.fragtype = 'I'
AND sf.partn = spt.partnum
AND sysmaster:bitval(spt.flags, 64) = 1
GROUP BY 1,2,3
)
ORDER BY 1  

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.

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

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

It may be neccessary to find the version of the Informix product or library that is installed in a particular $INFORMIXDIR directory.

Described are three methods to display version information for database server and client products. Make sure the following environment variables are set: 

INFORMIXDIR
PATH

libasf are the connectivity libraries.
libgen are the general libraries.
libOS are the OS supporting libraries. These are just there to determine if libraries exist.

ifx_getversion libcli.so
IBM/Informix-Client SDK Version 3.50.UC3
IBM/Informix ODBC LIBRARY Version 3.50.0000.UC3

ifx_getversion libgls.so
INFORMIX LIBGLS LIBRARY Version 4.50.UC4

ifx_getversion libos.so
IBM/Informix-Client SDK Version 3.50.UC3
IBM/Informix LIBOS LIBRARY Version 3.50.UC3

ifx_getversion libasf.so
IBM/Informix-Client SDK Version 3.50.UC3
IBM/Informix LIBASF LIBRARY Version 3.50.UC3

ifx_getversion libsql.so
IBM/Informix-Client SDK Version 3.50.UC3
IBM/Informix LIBSQL LIBRARY Version 3.50.UC3

ifx_getversion libgen.so
IBM/Informix-Client SDK Version 3.50.UC3
IBM/Informix LIBGEN LIBRARY Version 3.50.UC3

libasf are the connectivitylibraries.
lib gen are the general libraries.
LibOS are the OS supporting libraries. These are just there to determine if customer has libraries