Starting with version 12.10.xC3 Informix can automatically tune the logical and physical log as needed.
You can set the AUTO_LLOG configuration parameter to enable Informix to dynamically add logical logs when lack of logical logs causes frequent checkpoints, blocking checkpoints, long checkpoints etc. In addition, with AUTO_LLOG configuration parameter you can specify a dbspace in which to create new logical log files and the size of all logical log files at which the server stops adding logs for performance.
You are most probably thinking what about the the DYNAMIC_LOGS configuration parameters that exists since Informix version 10. The AUTO_LLOG and the DYNAMIC_LOGS configuration parameters works in different situations and do not interact with each other. When the AUTO_LLOG configuration parameter is enabled, logical logs are added to improve checkpoint performance. When the DYNAMIC_LOGS configuration parameter is enabled, logical logs are added under more urgent conditions, such as when a long transaction threatens to block the server. For example, the maximum size that is specified in the AUTO_LLOG configuration parameter does not affect the amount of log space that can be added by the DYNAMIC_LOGS configuration parameter. Similarly, the value of AUTO_LLOG configuration parameter does not affect the amount of log space that you can add manually.
Now, Informix also allows to expand the size of the physical log as needed to improve performance by creating an extendable chunk for the physical log. An extendable chunks can be automatically extend when additional storage space is required.
Typically, physical log get created in the root dbspace, unless you create an Informix instance during installation. However, for optimal performance it is better to create a separate dedicated dbspace for physical log on a different disk from the root dbspace and move the physical log out of root dbspace. In case, an instance created during installation the physical log create in a separate dbspace called plogspace with a default size that depends on the value of the AUTO_TUNE_SERVER_SIZE configuration parameter.
By default, the chunk that you assign to the plogspace is extendable, therefore, the initial size of the chunk can be small. The database server automatically expands the chunk when the physical log requires more space.
cheetahblog 0600028TE8 Tags:  extendable auto_tune_server_size dynamic_logs auto_llog 2,769 Views
Starting with version 12.10.xC3 Informix can automatically tune the logical and physical log as needed.
The following Informix information centers will be sunset in the near future:
cheetahblog 0600028TE8 2,325 Views
At present most of the Informix configuration parameters can modify dynamically. Typically, users run 'onmode -wf' or 'onmode -wm' command to dynamically change a specific configuration parameter. But there may be a situation when you need to modify multiple parameters at once. For example, you have ten Informix instance and want to tune some of SQL statement cache configuration parameters on all the Informix instances.
Now, you can import a configuration file using onmode command to change multiple configuration parameter at once. Importing a configuration file is often faster and more convenient than changing one configuration parameter at a time. You can use the 'onmode -wi' command to import a file that contains new values for multiple configuration parameters. If the parameters are dynamically tunable, the database server applies the new values. The import operation ignores the configuration parameters in the file that are not dynamically tunable and if the new parameter values same as the existing value.
However, you need to keep a note hare that an import operation changes the values of configuration parameters only in the memory. It does not modify the values in the ONCONFIG file on disk.
Let's take the same example as before, modify some of SQL statement cache configuration parameters using import operation. Currently, you have following settings:
You create a 'onconfig.new' file under '/tmp' directory with following values to modify the configuration parameters :
Next, you run the following command to import configuration values from file named 'onconfig.new' in the the /tmp directory:
$ onmode -wi /tmp/onconfig.new
The above command generates appropriate messages on screen and the message log. Following is an excerpt of the message log:
You can notice only three out of the five parameters has been changed because either those are not dynamically tunable or using same existing value.
As the 'onmode -wi' command import a configuration file, you can also export configuration parameters from memory to a file using 'onmode -we' command.
cheetahblog 0600028TE8 Tags:  af_inet6; dbaccess; ifx_disable_ipv6; getaddrinfo; af_inet; ipv8; 5,033 Views
On certain Operating System (for example: Linux) you may noticed dbaccess (and other client applications) always doing DNS lookup while connecting to a database, evern after found out the hostname or IP address in the local host file. This behavior sometimes caused slow connection, if you have problem related to DNS. Following is an excerpt of strace output shows the sequence of file accessed by a dbaccess request:
cheetahblog 0600028TE8 2,690 Views
A new shared memory segment has been introduced in the Informix version 12.10. The 12.10.xC2 onwards the bufferpool has been moved out of the resident segment and new bufferpool segment has been added which shown as class 'B' in the 'onstat -g seg' output. The BUFFERPOOL configuration parameter determines the amount of buffers allocated to this new segment when the database server is started.
IBM Informix Dynamic Server Version 12.10.UC2DE -- On-Line -- Up 00:20:35 -- 85899522520 Kbytes
Two new onstat commands introduced in 12.10.xC2 to view the dependencies between blocking and waiting threads. A running threads take ownership of various objects and resources; for example, buffers, locks, mutexes, decision support memory etc. Contention for these resources among hundreds or thousands of threads can result in chains of dependencies.
Highest level blocker(s)
(From the introduction)
It is already known to us that onbar backs up some critical administrative files during storage space backup. These critical files are as follows:
- The onconfig file
- UNIX: The sqlhosts file
- The ON-Bar emergency boot file: ixbar.servernum
- The server boot file: oncfg_servername.servernum
There may be a situation when you need to restore one of these files, for example: replace disks, restore to a second computer system (imported restore) etc. However, previously there was no easy way to restore these files from onbar backup. You need to depend on the Informix Technical Support to perform this operation.
Starting with Inofrmix vesion 12.10.xC2, you can restore the critical files that you backed up with onbar utility. Onbar cold restore has an option now to restore these files or you can only restore these file without perform the storate space restore.
Use 'onbar -r -cf yes' to restore critical file during cold restore. Alternatively, you can use 'onbar -r -cf only' to extract critical files while Informix server is offline.
You have upgraded to Informix 11.70.xC3 or higher version. After upgrade encountered some of your queries running slow. The common observation, a query was using index path prior to upgrade and running faster. After an upgrade the same query is doing a sequential scan. Using an index directive the query uses the index and runs much faster than the sequential scan. However, the index path has a higher estimated cost associated with it than the sequential scan.
This article provides step-by-step instructions for manually compiling Apache and PHP on Linux for OpenAdmin Tool for Informix.
Note that there is an automated installation option for OpenAdmin Tool (OAT) that installs and configures Apache and PHP for you. To use the automated installation, install OAT as part of the IBM Informix Client SDK installer or the IBM Informix bundle installer. OAT is available as part of the Client SDK and bundle installers for Linux 32, Linux 64, Windows 32, and Mac OS 64 for Informix version 11.70 and higher.
By contrast, this article is intended for advanced users who would prefer to compile their own Apache and PHP on Linux.
In this article, I will be using Apache HTTP Server version 2.4.2, Apache Portable Runtime version 1.4.6, Apache Portable Runtime Utility version 1.4.1, PHP version 5.4.4, and PDO_INFORMIX version 1.2.7. These are the versions that are currently shipped with the OAT that is part of the automated installer. Feel free to try with more recent versions of these software components if you like.
1. Install the Informix Client SDK
The Informix Client SDK is required to run OAT. It will also be required to compile the PDO_INFORMIX driver with PHP in step 3.
(a) Install the Informix Client SDK
(b) Set the INFORMIXDIR environment variable to point the directory where the Client SDK was installed.
2. Compile and Configure the Apache HTTP Server
(a) Download the UNIX source for the Apache HTTP Server from https://httpd.apache.org/
(b) Download the UNIX source for the Apache Portable Runtime (APR) and Apache Portable Runtime Utility (APR-util) from https://apr.apache.org/
(c) Extract the Apache HTTP Server source code.
tar xvfz httpd-2.4.2.tar.gz
(d) Extract the APR and APR-util packages into the Apache srclib directory. Rename the directories to apr and apr-util respectively.
(e) Set the APACHE_PREFIX environment variable to the directory where you want the Apache HTTP Sever to be installed. For example, /work/oat/apache_2.4.2
(f) Run the following commands to build and install Apache HTTP Server.
./configure --prefix=$APACHE_PREFIX --enable-so --without-pgsql --with-included-apr
Note #1: to build Apache HTTP Server with the mod_ssl module which is required for HTTPS, add "--enable-ssl --with-ssl=/usr/bin" as an additional argument to the configure command above. You will need OpenSSL installed to build the mod_ssl module. If OpenSSL is installed to a different directory from /usr/bin, substitute the appropriate directory name.
Note #2: If libpcre is not installed on your system, you will also have to compile PCRE and add "--with-pcre=/opt/pcre" as an additional argument to the Apache configure command.
(g) Edit the Apache httpd.conf file in the $APACHE_PREFIX/conf directory.
(h) Edit the $APACHE_PREFIX/bin/envvars file to include the following lines, where everything in angle brackets (<< >>) is replaced by the actual path.
(i) Start the web server by running the following command:
(j) Verify that the web server is running by using your web browser to visit the URL specified in the ServerName directive of the httpd.conf file.
3. Compile and Configure PHP
(a) Download the PHP source code from http://us.php.net/downloads.php
(b) Download the PDO_INFORMIX source code from http://pecl.php.net/package/PDO_INFORMIX
(c) Extract the PHP source code.
tar xvfz php-5.4.4.tar.gz
(d) Extract the PDO_INFORMIX source code into the PHP ext directory. Rename the PDO_INFORMIX directory to pdo_informix.
(e) Set the following environment variables:
$PHP_SOURCE_DIR - the directory where you extracted PHP
$PHP_PREFIX - the directory when you want PHP to be installed
(f) Run the following commands to build and install PHP and PDO_INFORMIX
./buildconf --force --add-modules-dir=$PHP_SOURCE_DIR/ext/pdo_informix
./configure --prefix=$PHP_PREFIX --disable-all --with-sqlite3 --enable-soap --enable-pdo --with-pdo-sqlite --with-openssl --enable-libxml --with-pcre-regex --enable-session --enable-sockets --enable-xml --enable-xmlwriter --enable-xmlreader --enable-simplexml --enable-zip --with-apxs2=$APACHE_PREFIX/bin/apxs --enable-dom --with-gd
./configure --with-pdo-informix=$INFORMIXDIR --with-php-config=$PHP_PREFIX/bin/php-config
Note: If libxml2 is not installed on your system, you will also have to compile libxml2 (e.g. version 2.8.0) and add "--with-libxml-dir=/opt/libxml2" as an additional argument to the PHP configure command.
(g) Copy the php.ini configuration file and the libphp5 module to your PHP installation directory
cp php.ini-production $PHP_PREFIX/lib/php.ini
cp libs/libphp5.so $PHP_PREFIX/libphp5.so
(h) Edit the php.ini configuration file
(i) Edit the Apache httpd.conf file ($APACHE_PREFIX/conf/httpd.conf)
LoadModule php5_module <<$PHP_PREFIX>>/libphp5.so
(j) Restart the Apache web server
4. Extract and install the OpenAdmin Tool Community Edition
(a) Download the OpenAdmin Tool Community Edition from the IBM Informix free product downloads page. The OAT Community Edition is available as a zip or tar file.
(b) Create an openadmin directory under the Apache htdocs directory ($APACHE_PREFIX/htdocs) and extract the OAT Community Edition zip or tar file.
(c) Use your web browser to navigate to the URL http://<ServerName>:<PortNumber>/openadmin/install/index.php .
(d) Follow the on-screen instructions to install OpenAdmin Tool, which involves reading and accepting the license term, verifying your configuration, building the connections database, and installing plug-ins.
(e) The Adobe Flash Player is required to use OAT after it is installed, so make sure to install the Flash Player plug-in into your web browser if you do not already have it. For example, to install the Flash Player plugin in Firefox on Linux, untar the Flash Player into the Firefox plugin directory (e.g. /usr/lib/mozilla/plugins/).
- Erika Von Bargen
There are new functions in Informix 12.10 designed to raise applicaiton compatibility with other vendors. Some of these are packaged in the excompat (External Compatibility) library, and within that library are functions for enabling tracing or logging of user routines. These are the DBMS_OUTPUT functions, which are:
The following is a short example of how these might be used.
------------------------------ -- Setup example conditions ------------------------------ -- Register the compatibility library if not already done. Uncomment these lines -- if the DBMS_OUTPUT routines are not found. --EXECUTE FUNCTION sysbldprepare('excompat.*', 'drop'); --EXECUTE FUNCTION sysbldprepare('excompat.*', 'create'); -- Include tracing information on an event or a routine. create trigger if not exists customer_insert insert on customer for each row ( execute procedure dbms_output_put_line( 'customer row inserted by session ' || dbinfo('sessionid') )); -- For our purposes, it is useful to be able to fetch back a message buffer -- line from an SQL call; so, creating a procedure to enable that. drop function if exists read_trace_buffer(); create dba function read_trace_buffer() returning lvarchar(2000); define buffer lvarchar(2000); define line_found integer; let buffer = ''; let line_found = 0; execute procedure dbms_output_get_line(buffer, line_found); return buffer; end function; ------------------------------ -- enable tracing and test ------------------------------ -- Enablement of tracing can be enabled and disabled at runtime call DBMS_OUTPUT_ENABLE(65536); insert into customer (lname, fname) values ('Barker', 'Bob'); -- Demonstrate that information was put in the message buffer by the -- INSERT trigger. call read_trace_buffer();
On the call to read_trace_buffer(), something like the following should be returned.
(expression) customer row inserted by session 71
This is a simple example to demonstrate the setup and enablement of using the DBMS_OUTPUT messaging buffer.-Chris Golledge
cheetahblog 0600028TE8 17,921 Views
The CASE statement within SPL is a long overdue request for many Informix users. Finally this wish fulfilled in Informix release 12.10. Now you can use the CASE statement within SPL routines as a faster alternative to IF...ELSE…END IF statements to define a set of conditional logical branches. The syntax for the CASE statement is:
You can use the CASE statement to create a set of conditional branches. The WHEN and the ELSE clauses are optional, but you must include at least one of them.
Here is an example that demonstrates how to use the case statement within SPL. In this example, the CASE statement validates a variable named ‘user’ and reassign the value of same variable. It also includes a test for NULL value.
This enhancement in the SPL code is really helpful for migrate databases from Informix XPS and other database servers to Informix server. For more information please check the Informix Information Center.
cheetahblog 0600028TE8 3,254 Views
Informix introduced the PSM (Primary Storage Manger); a new backup storage manager in version 12.10 that replaced the previous ISM (Informix Storage Manager). The PSM manages storage devices used for backup and restore requests that are issued by Onbar. This storage manager supports both serial and parallel processing for backup and restore requests. PSM is easier to set up and use, even in embedded environments. You just need to perform four simple steps to configure PSM and take an Onbar backup:
1) Decide where you will create the PSM catalog. The default location is $INFORMIXDIR/etc/psm.
2) Add devices for DBSPOOL and LOGPOOL
- onpsm -D add /backups/psm_spaces -g DBSPOOL -p HIGH -t FILE
- onpsm -D add /backups/psm_logs -g LOGPOOL -p HIGH -t FILE
3) Onconfig settings - you can keep the default values
- Set BAR_BSALIB_PATH to full path to libbsapsm.so
- Set PSM_CATALOG_PATH to full path to PSM catalog directory
- Set PSM_DBS_POOL DBSPOOL
- Set PSM_LOG_POOL LOGPOOL
4) Take an onbar backup
You use the Informix
Have you ever run into a situation where certain ER administrative commands executed and that caused ER to fail? Without knowing the exact sequence of activities, it would be very difficult to identify the actual commands that caused the problem. Unfortunately, not all ER commands generate alerts or update message log. If you did not use a script to run ER administrative commands, there would be no easy way to track the sequence of activity caused the failure.
An undocumented mechanism could be use for track ER object state changes. Once you enable this tracking mechanism, Informix server generates information about ER administrative operations in server message log file with full command line arguments, which could help analyzing ER issues. By default it would be disable. To enable the tracking either use –
change onconfig "CDR_FEATURES