cheetahblog 0600028TE8 2,844 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 19,414 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,416 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
OAT Group Summary Dashboard
Monitor multiple Informix database server instances from a single dashboard
OpenAdmin Tool version 3.11 has a new Health Center > Dashboard > Group Summary page that allows you to monitor all of the database servers in your OAT group simultaneously from a single page.
This page saves you time if you have multiple Informix instances. No longer do you have to log in to each server individually. No longer do you have to click through multiple pages in OAT to check on everything from alerts, space, memory, sessions, and backups. Now all of this information is presented in a user-friendly way from a single screen.
For each server in your OAT group, you are presented with high-level summary information about the server status and activity. This information includes:
If you click on any of the cells on the Group Summary page, you will get a pop-up with more details as well as a drill-down link to where you can go in OAT to get further information or act on any issues identified. The following screenshots provide some examples of the pop-ups.
The Memory, Space, I/O, and Backups columns use red, yellow, or green icons to indicate status. These are determined by configurable thresholds.
The status thresholds for memory, space, and the chunk percentage of total I/O are configurable in the IBM® Informix Health Advisor Plug-in for OAT. To change the dashboard status thresholds, you change the thresholds for the associated alarm in the Default profile on the Health Center > Health Advisor > Alarms page. Use these steps to change a threshold in the Health Advisor:
The status thresholds for backups are determined by the threshold in the sysadmin:ph_threshold table that indicates the maximum number of days between level-0 backups (REQUIRED LEVEL 0 BACKUP). To configure this value, go to the Task Scheduler > Scheduler page and click on the task named "check_backup". On the Task Details page, click the Edit Parameter button, edit the value, and click Save.
Data Refresh Rate
The server status information on the Group Summary page is refreshed based on the Refresh rate that is set by the slider at the top of the Dashboard page. However, to improve performance, the page is refreshed with data that is cached locally in the OAT connections database. The refresh interval for the cached data is set from the Admin > OAT Config page (the default value is 5 minutes). Every time the Group Summary page refreshes, it pulls data from the OAT cache. If the data is the cache is older than the refresh interval on the Admin > OAT Config page, OAT will query the database server to refresh the data in the cache. Otherwise, it will just display what is in the cache. The time that this data was last retrieved from the database server is shown in the Last Updated column. You can bypass the cache and refresh the data directly from a specific server by clicking the Refresh icon for the server.
For more information about the OpenAdmin Tool and the new features in version 3.11, go to the OpenAdmin Tool webpage.
- Erika Von Bargen
Log in to OpenAdmin Tool as your own user name
Manage access to OAT by managing permissions to the SQL Admin API commands for administering the Informix database server
With Informix version 12.10 and OpenAdmin Tool version 3.11, you can now manage SQL Admin API privileges for individual users. Individual users can be granted privileges to run some or all SQL Admin API commands. Privilege groups identify which SQL Admin API commands a user can run and thereby determine which features the user can access in OAT.
This functionality frees OAT from the requirement that all users must login as the user informix. Once a certain user has been granted access a set of SQL Admin API privileges, that user can log in to OAT using their own user name and monitor and administer the Informix database server as themselves. Security is enhanced when all users log in to OAT using their own user name, instead of everyone sharing the common informix account.
This feature involves two separate steps, which are described in detail in the following sections.
Step 1: Grant SQL Admin API Privileges
To begin using this feature, you must first grant SQL Admin API privileges to individual users. By default, only the user informix can run SQL Admin API commands and only the user informix can grant and revoke SQL Admin API privileges to other users.
The Server Administration > User Privileges > SQL Admin API Privileges page in OAT allows you to manage SQL Admin API privileges on the database server. Before anyone can log in to OAT using their own user name, the user informix must first log in to OAT and grant SQL Admin API privileges to individual users.
SQL Admin API Privilege Groups
The SQL Admin API commands are broken down into privilege groups which can be used to control which users can run which administration commands on the database server.
There are three global privilege groups:
The remaining privilege groups break down the SQL Admin API commands by functional category. If you desire granular control of which types of commands a certain user has privileges to run, you can use these categories to, for example, grant access for a particular user to only the backup commands. A list of all SQL Admin API privilege groups can be found in Table 1.
Table 1: SQL Admin API Privilege Groups
For more information on SQL Admin API privilege groups or the commands contained in each group, see the "SQL administration API portal: Arguments by privilege groups" topic in the IBM Informix 12.10 Information Center.
Granting any SQL Admin API privileges automatically grants a user access to the sysadmin database. Users who are granted Monitor privileges will be given read-only access to the sysadmin database. Users who are granted access to any other privilege group will automatically be granted read/write access to the sysadmin database.
The following screenshot shows the OAT pop-up for granting SQL Admin API privileges to a user.
If you are not an OAT user, but would like to take advantage of the ability to grant other users access to the SQL Admin API, you can use the following new SQL Admin API commands.
To grant privileges
EXECUTE FUNCTION ADMIN('grant admin', <user>, <privilege group>);
To revoke a specific privilege
EXECUTE FUNCTION ADMIN(‘revoke admin', <user>, <privilege group>);
To revoke all privileges
EXECUTE FUNCTION ADMIN(‘revoke admin’, <user>);
EXECUTE FUNCTION ADMIN('grant admin', ‘john’, 'operator'); EXECUTE FUNCTION ADMIN('grant admin', 'jane', 'storage'); EXECUTE FUNCTION ADMIN('revoke admin', 'joe', ‘onstat');
Step 2: Log in to OAT as your own user name
Once you have been granted access to the SQL Admin API, you can log into OAT using your own user name and password. Security and access management to OAT and the Informix database server are enhanced when all users use their own user name and password rather than everyone sharing the common informix user.
The Informix database server enforces the permissions to the SQL Admin API commands, not OAT. This means that all OAT pages are visible to all users, but the ability to perform certain actions will be restricted by the database server based on the access level of the currently logged in user. If a user tries to run an action that they do not have the corresponding SQL Admin API privileges for, they will get an error message that they are ‘not authorized to run the command’ as show in the following screenshot.
SQL Admin API commands are logged in the sysdmin:command_history table and can be viewed on the Logs > Admin Command page in OAT. This page can be used to track which users have run which administration commands against the database server.
To see a demo of this feature as well as other new OpenAdmin Tool version 3.11 features, go to the OpenAdmin Tool web page or the OpenAdmin Tool YouTube Channel.
- Erika Von Bargen
cheetahblog 0600028TE8 Tags:  heat pool informix extent graph oat map buffer openadmin 7,690 Views
OpenAdmin Tool: Use Heat Map Graphs to monitor Extent and Buffer Pool Usage
OpenAdmin Tool (OAT) version 3.11 includes new Heat Map graphs to aid in the performance analysis of your Informix database server. View extent and buffer pool usage in a heat map, also known as a treemap graph.
Go to the new Performance Analysis > System Reports > Graphs page to view graphs of the number of extents in databases or the percentage of cached pages in the buffer pools. Use the drop-down controls at the top of the page to choose the type of data to graph (extents or buffer pool) and to filter by a particular database.
Extents Heat Map
Use the Extents graph to monitor the number of extents across your databases. Monitoring extents is important because a large number of extents can impact performance by requiring the database server to spend extra time finding the data.
Use the size and color cues for each object to interpret the extents graph. Each rectangle within a database represents an object, for example a table or an index. The size of the rectangle indicates the relative size of the object. The color indicates the number of extents as indicated by the legend for the graph. Hover over any object in the graph to see the number of extents and the number of total pages used.
If any table or index has a large number of extents, consider increasing the size of the extents to improve performance.
Screenshots 1 and 2 show examples of the Extents Heat Map in OAT.
Screenshot 1: Extents Heat Map for the sysadmin database
Screenshot 2: Extents Heat Map for all databases
Buffer Pool Heat Map
Use the Buffer Pool graph to monitor the cached pages in your buffer pool. Both the size of the buffer pool and which table and indexes pages are cached in the buffer pool can have a big impact on the performance of queries against the database server.
Use the size and color cues for each object to interpret the buffer pool graph. Each rectangle within a database represents an object, for example a table or an index. The size of the rectangle indicates the relative size of the object. The color indicates the percentage of pages that are cached as indicated by the legend for the graph. Red indicates a “hot” table or index that has 100% of its pages cached in the buffer pool, whereas blue indicates a “cold” table or index that has 0% of its pages cached. Hover over any object in the graph to see the total rows, total pages, buffered pages, and percentage of pages that are cached for that object.
A higher percentage of cached pages can improve performance. Queries that are run on tables with a high cache percentage are faster than queries that are run on tables with a low cache percentage. If a table has a low percentage of cached pages and queries are slow, consider increasing the size of the buffer pool.
Screenshots 3 and 4 show examples of the Buffer Pool Heat Map in OAT.
Screenshot 3: Buffer Pool Heat Map for the sysadmin database
Screenshot 4: Buffer Pool Heat Map for all databases
For more information about the OpenAdmin Tool and the new features in version 3.11, go to the OpenAdmin Tool webpage.
- Erika Von Bargen
It is not uncommon that an inefficient SQL statement causing overall performance problem. In that scenario, first you need to identify the problematic SQL, next find out the user and application that executing the SQL. Here is a glimpse to address the second part of the problem.
There are different possible ways to find out the user and application running a particular SQL. The ‘onstat -g ses <sessionid>’ command is the simple way figure out the application that running a SQL. Do you notice any thing new in the following ‘onstat -g ses <session id>’ output?
You are right. There is a new ‘Program’ section in above onstat output. Now, you can use the ‘onstat -g ses <session id>’ command to display the full path of the client program that used in a session. Use the client program information to monitor or control the application access to the database. One thing you should know that the program information is sent by the client itself, and name of the program totally depends on the client.