pajama

INTRODUCTION

The values in the onCONFIG configuration file may not be the settings current in effect by the database server. This is true when:

• One of the parameters in the file is changed after the database server is started

• A parameter has been changed dynamically with the onparams utility

• A parameter that has been change dynamically with the onmode utility

SELECT cf_name, cf_default, cf_effective, cf_original FROM   syscfgtab;

The following steps are a guide for people who want to use a non-default database locale.

The first thing is to find out which datetime format should be used. This is decided by the following environment variables in this order:

a) DBTIME environment variable:
setenv DBTIME '%Y-%m-%d %H:%M:%S'

b) If DBTIME is not set, then the value of the GL_DATETIME environment variable will be used:
setenv GL_DATETIME '%Y-%m-%d %H:%M:%S'

c) If neither DBTIME or GL_DATETIME are set, the default time format from the CLIENT_LOCALE will be used.
This ESQL/C program shows the time format for a given CLIENT_LOCALE.

/* start datetime.ec */
-------------------------------------------------------------------------------
#datetime.ec
#include <stdio.h>

main(int argc, char **argv)
{
$datetime year to second dt;
char output[100];
char buffer[100];

if (argc!=2) {
printf(" USAGE : datetime <CLIENT_LOCALE value> \n");
printf(" EXAMPLE : datetime ko_kr.ksc \n");
exit(1);
}

sprintf(buffer,"CLIENT_LOCALE=%s",argv[1]);
putenv(buffer);
dtcurrent(&dt);
dttofmtasc(&dt,output,sizeof(output),NULL);
printf("%s \n",output);
}
-------------------------------------------------------------------------------
/* end datetime.ec */

You compile the ESQL/C program above with this command:
esql -o datetime datetime.ec -static

You then execute it like this:
datetime de_de.8859-1

The output looks like this:
Mi. 08 Dez. 2010, 21:19:16


Once the relevant datetime format is known, we can restore one table at a point in time. The example restores a single table from a database where DB_LOCALE & CLIENT_LOCALE are both set to de_de.8859-1, and DBTIME & GL_DATETIME are not set. the datetime will be as shown above

1. Create SQL command file for archecker with a correct time format.
-------------------------------------------------------------------------------
#Archecker SQL example...
#/informix/adm/point-in-time-recovery.cmd
database mydb;

create table t_source (
cm_cd smallint not null ,
c_cmp_cd integer not null ,
corp_ins_no char(13),
prv_no char(20),
is_rpt char(1),
rpt_ymd integer
) in mydbsp ;

create table t_target (
cm_cd smallint not null ,
c_cmp_cd integer not null ,
corp_ins_no char(13),
prv_no char(20),
is_rpt char(1),
rpt_ymd integer);

insert into t_target select * from t_source;
restore to 'Mi. 08 Dez. 2010, 01:02:03';

2. Run the archecker command for recovery. 

Lack of available contiguous memory can cause ontape archive to fail with an assertion warning "ISAM error: An error has occurred during archive back up".

When you start an ontape archive, the ontape (front-end) process will allocate four blocks of contiguous memory in the size of TAPEBLK configuration parameter. If you have constraints on available memory, a workaround could be reduce the value of TAPEBLK configuration parameter to a minimum value (On UNIX: 32K and on Windows: 16K).

The following message will be recorded in the message log file when the connection to the database server fails:

 Check for password aging/account lock-out.

 listener-thread: err = -952: oserr = 0: errstr = informix@hostname: User (informix@hostname)'s password is not correct for the database server.

AIX 6.1 and later implemented a Loadable Password Algorithm (LPA) mechanism that allows easily deploy new password encryption algorithms. Each supported password encryption algorithm is implemented as a LPA load module that is loaded at runtime when the algorithm is needed. The supported LPAs and their attributes are defined in the /etc/security/pwdalg.cfg system configuration file. By default only user 'root' can read this system configuration file.

# ls -ld /etc/security /etc/security/pwdalg.cfg

   drwxr-x--- 9 root security  4096 Jun 28 09:13 /etc/security

   -rw-r----- 1 root security  3807 Mar 04 2010 /etc/security/pwdalg.cfg

Informix does not support LPA. However, changing permission of the system configuration file may be a possible solution.

Assumptions:

1. 100% confidence that the account/password combination is correct
2. The OS is properly configured to authenticate Kerberos/AD user accounts
3. The user account is a remote Kerberos/AD account
4. AIX OS

A -952 error is reported in the server message log and possibly a -951 error to the client.

The remote AD server may not have a crypt hash password stored for the user account. If so it will return "*" for the crypt hash password value. This value does not match the crypt hash value of the passed-in password.

Take one of the following actions where applicable:

1. Ensure the permissions of the oninit binary are rwsr-sr--, owner is root and group is informix
2. Try running the instance as user root instead of user informix
3. Use some other form of authentication such as PAM or SSO

The underlaying problem is that the default settings on AIX for the LPA mapping config file are

such that only root can read the file. Crypt() function in IDS is run on a CPU VP which is non-root and AIX requires it to be run as root if using LPA, a workaround is to use PAM (which will use MSC VP for authentication )

Use PAM to configure LPA on AIX

Following example instructions to set it: 

1) Add to /etc/pam.conf: 
idslogin auth required pam_aix 
idslogin account required pam_aix 

2) Define DBSERVERALIAS ids_pam_srv 

3) Add to SQLHOSTS: 
ids_pam_srv onsoctcp <host> <port> 
s=4,pam_serv=idslogin,pamauth=password 

Don't forget to add an appropriate entry to /etc/services (if needed). 

4) check /etc/security/login.cfg for: 
auth_type = STD_AUTH 
pwd_algorithm = sblowfish 


5) and final IDS check: 

bash-3.2$ dbaccess - - 
> connect to "test@ids_pam_srv" user "tester"; 
ENTER PASSWORD: 

Connected.

AIX has made the /etc/security/pwdalg.cfg file unreadable to normal users (users other than root), hence -attempting to authenticate as user informix does not work.

You can use PAM to get around this problem, but if that is not an immediate option and re-writing applications to handle PAM can not be completed immediately - you can use the following work-around:

Warning: You need to ensure you are in a secure environment to use this work-around - i.e. your Informix Server machine is secure and you really trust user 'informix'. If this is true, then you can proceed.

Add user 'informix' to the 'security' group on AIX - this will allow the /etc/security/pwdalg.cfg to be read by user informix and authentication should work without throwing an error.

Note - using PAM is the preferred method for handling this situation, so use this as a temporary work-around only while taking steps to properly remedy the issue.

In baract.log file you'd see XBSA errors like the following:

XBSA Error: (BSACreateObject) A system error occurred. Aborting XBSA session.

XBSA Error: (BSAEndTxn) The transaction was aborted.

(-43296) WARNING: BAR_TIMEOUT Storage Manager Progress may be stalled.

The current backup would be aborted after such error (and possibly retried depending on BAR_RETRY setting).

This has been identified as an AIX product defect under APAR IV22133

IBM Informix onbar on AIX. The problem might be more likely on AIX lpars using virtual network adapters.

When hitting such XBSA errors, determine whether AIX OS is affected by APAR IV22133 or its siblings.

Apply latest AIX fixpacks.

An example of the warning as displayed in the online.log:

11:17:09  WARNING: Shared-memory residency is enabled while direct I/O and KAIO (kernel asynchronous I/O) are active.

This configuration could lead to runtime KAIO errors, which might shut down the instance or set chunks offline.

Set the RESIDENT configuration parameter to 0 to turn off shared-memory residency, and then restart the database server. Refer to the machine notes and APAR IC76872 for more information.

APAR "IC82623 : on AIX, WE SHOULD CHECK FOR THE SETTING OF RESIDENT BEFORE BRINGING THE INSTANCE onLINE"

On AIX, setting RESIDENT to -1 may lead to the instance running into an AIX related APAR:

APAR IC76872 : AIX: HARD TO DIAGNOSE KAIO ERRORS 22 (EINVAL) WHEN SYSTEM RUNNING LOW on PINNABLE MEMORY PAGES. 

On AIX systems with a lot of allocated pinnable ("resident") memory and KAIO or DIRECT_IO being used by Informix, it is possible that KAIO read or write calls fail with errno 22 (EINVAL), potentially leading to down dbspaces or system aborts. 

Sample message log error: 

04:30:40  KAIO: error in kaio_WRITE, kaiocbp = 0x22b620d0, errno= 22
04:30:40  fildes = 258 (gfd 3), buf = 0x700000122b64000, nbytes= 4096, offset = 130785280 

The reason for these EINVAL errors usually is the OS running low on 'pinnable' memory pages (by default 80% of the available RAM). This can be caused by Informix having a lot of shared memory segments allocated as "resident" plus pinned OS kernel memory plus KAIO resources. 

Changes are required to the onCONFIG -> RESIDENT parameter. For reference, here are the comments from an onconfig.std file.

###################################################################
# Shared Memory Configuration Parameters
###################################################################
# RESIDENT         - Controls whether shared memory is resident.
#                    Acceptable values are:
#                    0 off (default)
#                    1 lock the resident segment only
#                    n lock the resident segment and the next n-1
#                       virtual segments, where n < 100
#                    -1 lock all resident and virtual segments 

1. Turn RESIDENT off (i.e. 0). Optionally set the AIX parameters recommended by AIX Technical Support (below)
   or
2. Set RESIDENT to a positive integer so that only the initial memory segment(s) are pinned and that amount of memory does not exceed approximately 80% of the physical memory of the machine.

Q: Will there be a performance penalty?
A: AIX Technical Support assures us (the Informix team) that the performance penalty for not using RESIDENT in a machine dedicated to the database server is negligible. There are some AIX parameters that can be set to simulate the behavior of RESIDENT while using DIRECT_IO or Kernel AIO without incurring the problems that RESIDENT can cause.

Q: Tell me more about the AIX parameters that can be set to simulate the behavior of RESIDENT while using DIRECT_IO or Kernel AIO without incurring the problems that RESIDENT can cause.
A: Use larger memory page sizes. For example, a 64K page size can be enabled for the stack, data and text pages of oninit processes using this command when starting the Informix Server:LDR_CNTRL=DATAPSIZE=64K@STACKPSIZE=64K@TEXTPSIZE=64K@SHMPSIZE=64K oninit

Q: How does LDR_CNTRL compare to IFX_LARGE_PAGES?
A: First, they are two separate settings and therefore can be used independently. The current view is that LDR_CNTRL will give a small to negligible performance gain in addition to IFX_LARGE_PAGES. For some customers, since IFX_LARGE_PAGES is used with 16MB pages it may be that a LDR_CNTRL of 64K yields no discernible performance improvement.
The OS should always have sufficient pinned memory available for its own operations. For example, if there is insufficient memory errno 22 EINVAL may occur with KAIO operations (APAR IC76872 / 228003).
It is not safe to set RESIDENT to -1 no matter how much memory a system has as it can eventually be consumed and Informix corruption may result.

The amount of pinned memory currently in use may be displayed with the command svmon -G.

Example of setting pinned memory via the operating system.

• Run svmon -G and look at "L" row within the "PageSize / PoolSize" section of the output.
• export IFX_LARGE_PAGES=1
• vmo -p -o lgpg_regions=3200 -o lgpg_size=16777216
• vmo -p -o v_pinshm=1
• Start the engine.