IDS Experts




For a complete list of current SQL reserved words, see the IBM Informix Guide to SQL: Syntax.


Sanjit Chakraborty[Read More]

Introduction to GLSLIB

cheetahblog Tags:  gls 2 Comments 1,607 Visits

The GLSLIB is a library that is used to internationalize IBM Informix products such as IBM Informix Dynamic Server and IBM Informix Client SDK. Each IBM Informix product is bundled with a specific version of GLSLIB. This GLS library supports English, Western European, Eastern European, Asian and African languages. Additional Locales that are not included in the GLS library can be installed from the International Language Supplement (ILS). ILS provides all available GLS locales and code-set conversion files. It also includes error messages to support several languages. The important point about ILS is that it is a platform independent product. The languages supported by ILS are Czech, German, French, Spanish, Russian, Polish, Slovak, Japanese, Simple Chinese, Traditional Chinese and Korean.

The following tables provide information about the version of GLSLIB - bundled with IBM Informix Dynamic Server and IBM Informix Client SDK. It also provides the information about the version of ILS that is compatible with the specific version of IBM Informix product.


The table can be used to
1) Find the version of GLS library bundled with the specific version of IBM Informix product.
2) Determine the compatible version of ILS that should be used with the specific version of IBM Informix product.

 



































































































































































































IDS Version
GLSLib Version
Compatible ILS Version
3.13
4.00
4.50
xC3
xC5
xC1
xC2
xC4
xC5
xC6
xC7
xC8
xC9
xC10
xC1
xC2
xC3
7.31.xD6
X
2.40.MCx
7.31.xD6W2
X
7.31.xD7
X
7.31.xD8
X
7.31.xD9
X
7.31.xD10
X
9.40.xC1
X
3.40.MCx
9.40.xC5
X
9.40.xC6
X
9.40.xC7
X
9.40.xC7W1
X
9.40.xC7W2
X
9.40.xC7W3
X
9.40.xC7W4
X
9.40.xC7W5
X
9.40.xC7W6
X
9.40.xC7W7
X
9.40.xC9
X
10.00.xC1
X
3.40.MCx
10.00.xC3W2
X
10.00.xC3W3
X
10.00.xC3W4
X
10.00.xC3W5
X
10.00.xC4
X
10.00.xC4W1
X
10.00.xC4W2
X
10.00.xC4W3
X
10.00.xC5W1
X
10.00.xC5W2
X
10.00.xC5W3
X
10.00.xC5W4
X
10.00.xC5W5
X
10.00.xC6
X
10.00.xC6W1
X
10.00.xC6W2
X
10.00.xC6W3
X
10.00.xC6W4
X
10.00.xC6W5
X
10.00.xC7
X
10.00.xC7W1
X
10.00.xC7W2
X
10.00.xC8
X
11.10.xC1
X
3.50.MC1
11.10.xC2
X
11.10.FC2W1
X




































CSDK Version
GLSLib Version
Compatible ILS Version
3.13
4.00
4.50
xC3
xC5
xC1
xC2
xC4
xC5
xC6
xC7
xC8
xC9
xC10
xC1
xC2
xC3
2.90.xC1
X
3.40.MCx
2.90.xC3
X
2.90.xC4
X
3.00.xC2
X
3.50.MC1
3.00.xC3
X
3.50.xC1
X


Parag A Sheth[Read More]

New onstat option for ER

cheetahblog Tags:  sbspace rqm er onstat 1,584 Visits

If you use Enterprise Replication(ER), you can use onstat -g rqm to print the statistics and contents of the low-levelqueues managed by the Reliable Queue Manager(RQM). From IDS 10.00.xC7 onwards, a new option to print information aboutthe sbspaces configured for ER is available.

onstat -g rqm SBPACES prints detailed statistical information about the sbspaces configured for CDR_QDATA_SBSPACE.A sample output is shown below:
$ onstat -g rqm SBSPACES

IBM Informix Dynamic Server Version 11.10.FC1 -- On-Line -- Up 00:18:03 -- 67584 Kbytes

RQM Space Statistics for CDR_QDATA_SBSPACE:-------------------------------------------name/addr number used free total %full pathname0xda3d088 2 1 4586 4587 0 ./replsbs replsbs 2 1 4586 4587 0

0xda539c0 3 1 4586 4587 0 ./sbsp1 sbsp1 3 2 9172 9174 0


As you can see it prints the address of the chunk or name of the sbspace, chunk/space number, total pages, free pages,percentage used and path name for the chunks. The information printed is for each chunk in a sbspace followed bysbspace itself. In this case replsbs and sbsp1 are the sbspaces configured for CDR_QDATA_SBSPACE.

Suma Vinod[Read More]

Configure Distributed Relational Database Architecture (DRDA) connection during IDS installation

cheetahblog Tags:  drda configuration 1,583 Visits

IDS 11.50 introduced a new installation wizard (installer), which makes life easier than in past releases to set up an instance to use a variety of database clients. In this section we will discuss how to set DRDA connection during installation process.



The installation wizard is extremely user friendly and easy to navigate. Options are available with installation wizard to configure a database server alias (DBSERVERALIASES) and a port for clients that use the DRDA protocol. You can easily setup this DRDA connection via a checkbox when creating the demonstration database server instance during installation. We will see some screenshots latter in this section to get a better understanding on DRDA configuration.



There are little differences between UNIX and Windows installation wizard.



UNIX

  • Select option use the default configuration file to able to install database server with DRDA.
  • Do not select the option to customize the default configuration file, as this option does not allow setting DRDA.



Windows
  • By default the server installation includes DRDA setup.
  • You must select custom installation to exclude DRDA setup.




On UNIX platform you need to answer 'Yes' for question Do you want to create an IDS demonstration database server instance. Similarly, you need to choose option 2 - Use the default configuration file under demonstration database instance configuration. You may use default server name and server alias values or change those as per your requirement.



Following is a DRDA configuration screenshot on UNIX platform:
DRDA configuration in Console Mode




On Windows platform the typical installation process (default) automatically include the DRDA configuration. It will set the configuration parameter DBSERVERALIASE as "svc_drda". Custom installation process is required if you want to disable DRDA setup in demonstration server instance.




Following are some DRDA configuration screenshot on Windows platform:
Custom  Installation in GUI Mode




The custom installation process provides controls on initialize the demonstration server. You can exclude the DRDA configuration, if you wish. By default DRDA is selected.
DRDA configuration Setup in GUI Mode




Things to notice if you enable a custom configuration file on windows platform, DRDA support is removed.
DRDA configuration Setup in GUI Mode




By default IDS will configure TCP/IP port 9089 and drtlitcp or drsoctcp connection protocol for DRDA configuration.




Sanjit Chakraborty

[Read More]

OAT Version 2.23 Available Now!

cheetahblog Tags:  openadmin oat 1,569 Visits

The newest version of OpenAdmin Tool for IDS, Version 2.23, has just been released! Download the new version today to get the enhanced SQL Explorer and version 2.0 of the ER Plugin.



New feature highlights:



The SQL Explorer has been newly redesigned. New features include

  • Filters and search fields for viewing SQL tracing data.
  • Support for different SQL trace levels - global or user - so you can manage what kinds of SQL information are traced for IDS V11.50.xC1 servers.
  • Support for suspending and resuming history tracing, without releasing resources, on IDS V11.50.xC1 servers.


ER Plug-in Version 2.0: Version 2.0 of OAT’s ER plug-in includes a new Replicate Explorer and becomes the first step in supporting ER administration graphically through OAT.





  • The Replicate Explorer now lets you monitor your replicates and replicate sets.
  • Screenshot of the Replicate Explorer



  • The Node Details -> Configuration page now supports the editing and updating of ER configuration parameters






Download OAT Version 2.23 now at https://www14.software.ibm.com/webapp/iwm/web/preLogin.do?lang=en_US&source=swg-informixfpd



For additional information on OpenAdmin Tool for IDS, including feature details, screenshots and demos, go to www.openadmintool.org.

Erika Von Bargen

[Read More]

HTTPS on OAT

cheetahblog 1,559 Visits

Introduction



HTTPS protects the OAT webserver from eavesdropping, tampering, and message forgery. It protects the OAT webserver from hackers who are trying to secretly listen or interfere with the OAT network. Enabling HTTPS will encrypt OAT clients’ messages before sending it to the OAT webserver. This prevents hackers from listening over the line and stealing sensitive information. Sometimes hackers will setup a fake OAT webserver and deceive OAT clients as to whom the real OAT host is. Enabling HTTPS also allow OAT clients to authenticate with the OAT host, so that hackers cannot deceive OAT clients with a fake OAT webserver.



Please note that HTTPS only encrypts communication between OAT webserver and OAT client. It does not encrypt communication between IDS server and the OAT webserver.



IBM® Informix® Dynamic Server, Version 9.4 and later, enables encryption of data between IDS server and OAT webserver using an encryption communication support module. You can find more information in the following link:

http://www.ibm.com/developerworks/db2/library/techarticle/dm-0401dandekar/index.html

Enabling HTTPS in OAT will involve the following steps:

  • 1. Replacing OAT’s Apache webserver with another mod_ssl enabled Apache webserver.
  • 2. Creating an encryption key and a certificate for your new OAT webserver, so that OAT clients can authenticate your webserver based on your certificate.
  • 3. Configure httpd.conf (the Apache configuration file) to enable HTTPS



Replacing OAT’s Apache Webserver with another mod_ssl enabled Apache Webserver



In order to use OAT with https, you will need to have an apache webserver, PHP with some extensions and the apache mod_ssl module. HTTPS functionality is provided by the apache mod_ssl module. Using the OAT automated installer will install an apache webserver and PHP with all the required PHP extensions, however apache webserver bundled with installer is NOT shipped with the mod_ssl module.



Therefore you have to install another apache webserver compiled with the mod_ssl module to replace OAT’s apache webserver. Then dynamically load OAT’s PHP apache handler (a file named libphp5.so, or php5apache2_2.dll on windows, which is the apache and php ‘glue’) to your new apache webserver.



Dynamically loading the mod_ssl module to OAT’s original apache webserver is not possible because the version of OAT’s original apache webserver is 2.2.4. Mod_ssl dynamic module only supports the older apache 1.3.x, It does not support OAT’s apache webserver.



On Linux and Mac OS X,



In order to compile Apache with mod_ssl support, you need to have OpenSSL installed. OpenSSL might have been installed on your Linux distribution already and you only need to find out the installation directory. Note that the OpenSSL binaries have to be 32-bit, because the binaries from the OAT automated installer are 32-bit. We suggest doing this only on a 32-bit machine so that there will be no architectural mismatch among the binaries.



If OpenSSL has not been installed already, you can download the latest source code release from here:

http://www.openssl.org/source/

Then you have to compile the OpenSSL source code. Note that you have to compile 32-bit OpenSSL binaries because the binaries from the OAT automated installer are 32-bit. This can be done by setting CFLAGS to be –m32. Use the following commands:



cd /path/to/openssl/source/code
export CFLAGS=-m32
./config --prefix=/openssl/installation/directory/ --openssldir=/openssl/installation/directory/
make
make install



Stop OAT’s apache webserver by running the /oat/installation/directory/StopApache script. Rename OAT’s Apache_2.2.4 directory to Apache_2.2.4_noSSL. This is a will serve as a backup copy of the apache binaries. You will also need to use some configuration files from this backup Apache directory in later steps. Do make a copy.



Then you have to compile the latest Apache with mod_ssl support. Download the latest Apache source code and compile with the following commands. Note that you have to compile 32-bit Apache binaries because the binaries from the OAT automated installer are 32-bit. This can be done by setting CFLAGS to be –m32. The compilation prefix should match the old OAT apache’s directory (/oat/installation/directory/Apache_2.2.4/)



cd /path/to/apache/source/code
export CFLAGS=-m32
./configure --prefix= /oat/installation/directory/Apache_2.2.4/ --enable-so --enable-ssl --with-ssl= /openssl/installation/directory/
make
make install



Right now Apache should be installed with mod_ssl support. Use the following commands to check if mod_ssl is enabled in Apache.



cd /oat/installation/directory/Apache_2.2.4/bin/
./httpd –M
(Here you will see a list of Apache modules. See if the SSL module is on the list)



Then you have to change the Apache configurations file to load OAT’s PHP Apache handler (The PHP and Apache ‘glue’). Edit the Apache configuration file (/oat/installation/directory/Apache_2.2.4/conf/httpd.conf). Add the following lines. Uncomment if these lines exist but are commented out.



LoadModule php5_module /oat/installation/directory/PHP_5.2.4/libphp5.so
AddType application/x-httpd-php .php
PhpIniDir ‘/oat/installation/directory/PHP_5.2.4/lib’
Setenv INFORMIXDIR ‘/oat/installation/directory/Connect_3.50/’



Search for the line “Listen 80” in the httpd.conf file. This indicates the port number that the OAT webserver should run on. You should use the same port number as OAT’s original Apache webserver that comes with OAT installer.



Search for the line “ServerName www.example.com:80” in the httpd.conf file. This indicates the name and the port that the server uses to identify itself. You should use the same ServerName as the original non-SSL Apache webserver that comes with OAT installer.



Search for the line “DirectoryIndex index.html” in the httpd.conf file. This sets the files that Apache will serve if a directory is requested. Change this line to “DirectoryIndex index.html index.php”



Copy the file /oat/installation/directory/Apache_2.2.4_noSSL/bin/envvars to /oat/installation/directory/Apache_2.2.4/bin/envvars. Apache will read this file to setup the Apache environment variables for OAT to run.



Copy the entire directory /oat/installation/directory/Apache_2.2.4_noSSL/htdocs/openadmin/ to /oat/installation/directory/Apache_2.2.4/htdocs/openadmin/. All the OAT source code resides in this directory.



Run the following commands to make sure that the PHP Apache handler is properly loaded.

cd /oat/installation/directory/Apache_2.2.4/bin/
./httpd –M
(Here you will see a list of Apache modules. See if the php5 module is on the list)



Right now your new webserver should be properly setup for OAT. You may start your webserver by running /oat/installation/directory/StartApache and visit OAT using your web browser.



Note that this server has mod_ssl enabled but HTTPS is not switched on yet. A few more steps are needed to enable HTTPS in the following sections.



On Windows,



First we have to download and install Openssl from the following website:

http://www.openssl.org/related/binaries.html

Then we need to setup Apache with mod_ssl support. Download the latest Win32 Binary including OpenSSL 0.9.8g (MSI Installer). This should be available here:

http://httpd.apache.org/download.cgi

Stop the OAT Apache webserver. There should be a OpenAdmin shortcut in the start menu. You should be able to stop the OAT Apache webserver from there. Make sure that the Apache Monitor is not running on your system tray.



Rename OAT’s Apache_2.2.4 directory to Apache_2.2.4_noSSL. This is a will serve as a backup copy of the apache binaries. You will also need to use some configuration files from this Apache directory in later steps. Do make a copy.



Run the Apache MSI installer. Do a typical install and choose the installation directory to be /oat/installation/directory/Apache_2.2.4



Edit the Apache configuration file (/oat/installation/directory/Apache_2.2.4/conf/httpd.conf). Add or uncomment the following lines in the httpd.conf file:



LoadModule php5_module "c:\oat\installation\dir\PHP_5.2.4\php5apache2_2.dll"
LoadModule ssl_module modules/mod_ssl.so
AddType application/x-httpd-php .php
PhpIniDir 'c:\oat\installation\dir\PHP_5.2.4'



Search for the line “Listen 80” (or “Listen 8080”) in the httpd.conf file. This indicates the port number that the OAT webserver should run on. You should use the same port number as OAT’s original Apache webserver that comes with OAT installer.



Search for the line “ServerName www.example.com:80” in the httpd.conf file. This indicates the name and the port that the server uses to identify itself. You should use the same ServerName as OAT’s original Apache webserver that comes with OAT installer.



Search for the line “DirectoryIndex index.html” in the httpd.conf file. This sets the files that Apache will serve if a directory is requested. Change this line to “DirectoryIndex index.html index.php”



Search for the line “setenv INFORMIXDIR” in the OAT’s original Apache’s configuration file (c:\oat\installation\dir\Apache_2.2.4_noSSL\conf\httpd.conf). This line sets the INFORMIXDIR variable in the Apache environment for OAT. This has NOT been set in your new Apache webserver yet. Copy this line to your new Apache webserver’s httpd.conf file. You can put this at the end of the httpd.conf file.



Copy the entire directory c:\oat\installation\dir\Apache_2.2.4_noSSL\htdocs\openadmin\ to c:\oat\installation\dir\Apache_2.2.4\htdocs\openadmin\. All the OAT source code resides in this directory.



Run the following commands in a command prompt to make sure that the PHP Apache handler and the mod_ssl modules are properly loaded.

cd c:\oat\installation\dir\Apache_2.2.4\bin\
httpd.exe –M
(Here you will see a list of Apache modules. See if the php5 module and the ssl_module are on the list)



Right now your new webserver should be properly setup for OAT. You may now click on ‘Start Apache Service for OpenAdmin’ in OpenAdmin Menu under the Start Menu. You should be able to access OAT using your web browser.



Note that this server has mod_ssl enabled but HTTPS is not switched on yet. A few more steps are needed to enable HTTPS in the following sections.



Creating an Encryption Key and a Certificate for your OAT Webserver



Keys are used in encryption and decryption. They usually come in pairs, the public key and private key. Public keys are used to encrypt messages and private keys are used to decrypt messages. The message encrypted by a public key can only be decrypted by its associated private key.



A HTTPS enabled webserver will have its own pair of public and private key. The webserver will make its public key available to all clients. But nobody else except the webserver will know its private key. Thus all clients will be able to encrypt messages, but only the webserver can decrypt the message. If a client wants to send encrypted messages to the webserver, he will encrypt the message with the public key provided by the webserver and then send out the message. The webserver will use its secret private key to decrypt the client’s message. Hackers who are trying to listen over the network and steal the client’s message will not be able to decrypt the client’s message, because the hacker does not have the private key.



A certificate is a document authenticating a person to be whom he claims to be. A HTTPS enabled webserver will have its certificate, signed by a trusted certificate authority, to authenticate itself to be the real webserver that the clients are talking to. Before a client talks to the webserver, he will be prompted to view and accept the webserver’s certificate. To the client can make sure that the webserver’s certificate is signed by a trusted certificate authority before proceeding with the communication. This prevents hackers from setting up fake webservers to deceive clients.



Once a webserver is HTTPS enabled, clients get to choose whether they want to do a normal connection to the webserver or a secure connection to the webserver. If clients want to do a normal connection, they will type “http://webserver_url” in their web browser. If clients want to do a secure connection, they will type “https://webserver_url”. In a secure connection, the webserver will send the client its certificate and its public key. The client will be prompted to view and accept the webserver’s certificate before the webpage is loaded, so that the client can be sure that the webserver is not a fake webserver created by hackers to deceive you. Once the client accepts the certificate, the client’s web browser will use the public key that it received from the webserver to encrypt communication. Only the webserver has the associated private key, thus only the webserver can decrypt the client’s encrypted communication. Hackers cannot secretly listen and decrypt the communication.



To generate private/public key pairs and the certificate, we use the openssl executable. You should be able to find it under the bin directory of your openssl installation.



To generate a private key, use the following command:

openssl genrsa -des3 -out privkey.pem 2048

The private key will be stored in the privkey.pem file. Store this file in a secure location because this is the webserver’s secret decryption key. This file will be used to generate the associated public key. When we generate the certificate, it will look for this file, it will generate its associated public key and include the public key in the certificate.



To generate a certificate, we create need to create a certificate signing request, and send the certificate signing request to a trusted certificate authority (Such as VeriSign). The authority will then issue you a certificate. Use the following command to generate a certificate request. For more information about the process of signing certificate requests, contact your certificate authority.

openssl req -new -key privkey.pem -out cert.csr

If you don’t want to deal with another certificate authority and you just want to create a certificate for yourself, you can create a self-signed certificate. Note that this is not the recommended way of creating a certificate.

openssl req -new -x509 -key privkey.pem -out cacert.pem -days 1095

Openssl will prompt you to enter your personal information. After that the certificate will be stored in the cacert.pem file. This file will be displayed to web clients to verify your identity. It will also include the public key for web clients, thus they can start encrypting communication.



Openssl will also prompt you to enter a pass phrase. This is an extra layer of security. You will need to enter your pass phrase when you start your webserver.



For more information about encryption keys, please consult the OpenSSL documentations:

http://www.openssl.org/docs/HOWTO/keys.txt

For more information about certificates, please consult the OpenSSL documentations:

http://www.openssl.org/docs/HOWTO/certificates.txt

Configure httpd.conf (the Apache configuration file) to enable HTTPS



Edit the apache configuration file.The file should be /oat/installation/directory/Apache_2.2.4/conf/httpd.conf



Search for the following line:

#Include conf/extra/httpd-ssl.conf

This line is commented out by default. Uncomment it so that httpd.conf will include the apache ssl configuration file.



Then edit the apache ssl configuration file.The file should be /oat/installation/directory/Apache_2.2.4/conf/extra/httpd-ssl.conf



HTTPS will require one more ssl port. By default, the ssl port number is set to be 443. Make sure this port is available. If you wish to change this port, edit the Listen directive and the Virtual Host section of the httpd-ssl.conf file.


Search for the ‘SSLCertificateKeyFile’ directive and the ‘SSLCertificateFile’ directive in the httpd-ssl.conf file. These two directives indicate the location of your private key file and the certificate file. Make sure that they point to the privkey.pem and the cacert.pem created in the previous section.


Search for the ‘SSLCipherSuite’ directive. This directive indicates the ciphers for your HTTPS webserver. By default the HTTPS webserver will accept all encryption ciphers. If you wish to accept only the seven strongest ciphers, edit the directive as follow, or else you can keep the default configuration:

SSLProtocol all
SSLCipherSuite HIGH:MEDIUM

For more information about HTTPS configurations, please refer to the following website:
http://httpd.apache.org/docs/2.0/ssl/ssl_howto.html

Final Testing



Your OAT webserver should be secured with HTTPS right now. Restart your webserver and launch OAT with your web browser. Instead of using http://hostname:portnumber/openadmin, try using https://hostname:ssl_portnumber/openadmin. You will be prompted to view and accept OAT webserver’s certificate before the OAT login page is launched.


Note:
On Linux, you can restart your webserver by running the StopApache script and then the StartApache script in the OAT installation directory. You will be prompt to enter the pass phrase before you can start you webserver.


On Windows, you have to restart your webserver with the command prompt. Starting the webserver with Apachemonitor.exe or with the start menu shortcuts doesn’t work because they do not support pass phrases. Use the following command to restart your webserver.

httpd –k restart



If you wish to use Apachemonitor.exe or with the start menu shortcuts to control your webserver, you have to disable pass phrases. Note that this reduces the level of security.

Run the following command:

cd c:/openssl/installation/directory/bin/
openssl rsa -in privkey.pem -out privkey_nopassphrase.pem



Now an unencrypted copy of your private key will be stored in the privkey_nopassphrase.pem file. Edit your httpd-ssl.conf file SSLCertificateKeyFile directive to use the privkey_nopassphrase.pem file instead of the privkey.pem file. Then you will not be prompted to enter a pass phrase when starting your webserver. Apachemonitor.exe and the start menu shortcuts should work now.



Leo Chan

[Read More]

Derived Table Support in IDS 11.10

cheetahblog Tags:  iterator funtion derived table multiset 1,556 Visits

As mentioned in this blog, IDS 11.10 allows SELECT statements to include syntax to specify a full select sub-query in the FROM clause. I would like to describe it with some examples here.In IDS 10, if you had to use sub-queries in the FROM clause, you had to cast the sub-query statement to a multiset andthen to a table. Here are some example SELECT statements syntax in prior IDS versions:
    SELECT * FROM table(multiset(select a, b, c from tab1, tab2 where tab1.a = tab2.c)) vt(va, vb, vc),emptab WHERE vt.va = emptab.id;
    a, b, c: columns of any data type

    SELECT * from table((select coll_dat from sales_tab)) AS c_table(s_month, s_day);
    coll_dat: should be a collection data type column

    SELECT * from table(function f1());
    f1(): an iterator function

In IDS 11.10, the above statements can be written in the same way or you can re-write them as:
    SELECT * FROM (select a, b, c from tab1, tab2 where tab1.a = tab2.c) vt(va, vb, vc), emptab WHERE vt.va = emptab.id;

    SELECT * from (select coll_dat from sales_tab) AS c_table(s_month, s_day);

    SELECT * from table(f1());
    f1(): f1() can be any function

These sub-queries in the FROM clause are called derived tables or table expressions. You can use simple, UNION, orjoined sub-queries, including OUTER joins, and can include the ORDER BY clause. You can also write nestedFROM clause queries and can combine old and new syntax in a SQL statement. Here is an example:
    select * from (select * from (select col1 from tab3 where col1 = 10 order by col2 desc)),table(multiset(select col1 from tab2 where col1 = 20)) order by 1 ;

The derived table support in IDS 11.10 enhances its capability to execute queries that complies with the SQL-92 standard without any modifications.

Suma Vinod[Read More]

How to monitor your SQL Shared Memory Caches

cheetahblog Tags:  performance cache 1,553 Visits



I don’t know about the rest of you, but I have had a lot ofproblems truly determining if my SQL Cache settings are efficient. If my UserDefined Routine (UDR) cache, for example, always seems to be 50% full, does that mean thecache only uses 50% and so the settings are to high, or are they at 50% becauseI’m constantly cleaning the pools, and thus I’m configured too low. Beginning in Cheetah you now have a means to diagnose thattype of information, and it’s located in the sysmaster database. The table name is syssqlcacheprof, and it contains profileinformation for each of the caches.



 



Below is a sample output from this table:



 





 



 



As you can see in the above you can now quickly identify how often the cache is removing entries (orcleaning), and what the efficiency is of your cache, in terms of a hit rate. Infact a very simple query that could be used for analyzing the hit ratio for the caches would be:



 



select *, (hits/(hits+misses)) hit_ratio from syssqlcacheprof



 



This table also provides youthe benefit of quickly seeing how much memory each cache is actually using,something that before Cheetah was a bit awkward to calculate. While not a major feature of Cheetah, this new sysmaster table is a valuable new asset in performance tuning.




Mark Jamison[Read More]

Support for TRUNCATE TABLE in Enterprise Replication

cheetahblog Tags:  er replication truncate 1,551 Visits

TRUNCATE TABLE feature was actually introduced in IDS starting with 10.00.xC4 version but with restrictions and one of the restrictions was it did not work on Enterprise Replication replicated tables. But starting with Cheetah release this will be supported even on the replicated tables. This feature introduces TRUNCATE TABLE as a supported operation on a replicated table while replication is active, which basically eliminates need for dropping and redefining the replicate and saves time for DBAs. This feature will support TRUNCATE TABLE on local table only, meaning ER will not propagate the TRUNCATE operation to other participants. The TRUNCATE command needs to be issued on each participant as needed. There are plans to implement the propagation of command in future along with other alter operations(ALTER, RENAME).

One of the key uses of TRUNCATE support which is in conjunction with cdr sync is, if there is a significant difference between the source and the target, the customer might elect to perform a truncate table followed by a cdr sync command. This would be faster than performing a cdr repair because the cdr repair must scan the data pages in primary key order while the sync does not. The cdr sync/repair features were introduced in 10.x which basically provided the ability to recognize the differences between tables, between multiple nodes within the replication domain, while ER running and take necessary action as instructed.

Vijay Lolabattu[Read More]

IBM OpenAdmin Tool for IDS -- Version 2.20 – Available Now!!

cheetahblog Tags:  idsadmin openadmin oat 1,527 Visits

OpenAdmin Tool for IDS has been greatly enhanced in version 2.20 with a completely redesigned user interface, a new automated installer, and lots of new IDS admin functionality. OpenAdmin Tool for IDS (OAT) is a PHP-based Web browser administration tool for IDS 11 and IDS 11.5 that provides the ability to administer multiple database server instances from a single location. OAT makes administration easy by allowing you to drill down on resource usage and events, view query performance statistics, and much more. And since the tool is written in PHP and available as open source, you can customize it with your own business logic and installation requirements.



New feature highlights of OpenAdmin Tool for IDS version 2.20 include:



  • Automated Installer: For users who want an easy, hassle-free install, there is a new automated GUI installer that will install and configure Apache, PHP, I-Connect, and OAT. For users who want to use or install their own Apache/PHP configurations, OAT is also released as a stand-alone package.


  • All-New Look: OAT version 2.20 has a completely new and improved UI


  • Automated Update Statistics: Configure rules and define a schedule to have IDS automatically maintain your database server's statistics


  • Onconfig Recommendations: Get recommendations for your onconfig settings that are tuned to your specific database server instance; modify dynamic onconfig parameters directly through OAT


  • Enhanced Mach11 Support: Including a Connection Manager interface, an SDS Setup Wizard, and the ability to start and stop Mach11 servers remotely


  • Historical Performance Graphs: View and monitor historical trends of key performance measurements


  • Task Scheduler Wizard: Use OAT’s new wizard to define new tasks in the scheduler


  • Read-Only Access: Define groups in OAT that can monitor IDS database servers but not perform administrative tasks


  • And the list goes on…. Virtual Processor Administration, System Integrity Checks, a Privileges Manager, environment variable support on OAT connections, the ability to kill database sessions remotely through OAT, and more!






Download OAT version 2.20 now at https://www14.software.ibm.com/webapp/iwm/web/preLogin.do?lang=en_US&source=swg-informixfpd, or at www.iiug.org in the Members Area.



Erika Von Bargen[Read More]

Overview of IDS LBAC -- Part 1

cheetahblog Tags:  lbac 1,520 Visits


This article describes LBAC concept and outlines IDS LBAC solution an implementation of MAC (Mandatory Access Control) for protecting data rows and columns based on security labels.

What is LBAC?

Label-Based Access Control (LBAC) is a means by which a database system can control access to a database object based on security labels. If the security label granted to a user dominates the security label protecting that object, access to the object is authorized. A database object in this context can refer to either a row of data or a table column. Each protected database object is assigned a security label which stores information about the classification (or sensitivity) of the data. Similarly, each database user is assigned a security label that determines which labeled data (rows or columns) he or she can access. LBAC is an implementation of MAC (Mandatory Access Control),which provides a reasonable level of security assurance of sensitive data stored in database.

What is IDS LBAC solution?

IDS LBAC solution is similar to DB2 LBAC solution; some key characteristics are as following:
  1. Offer row level and column level protection, allowing protection of data rows of a table and columns of table.


  2. Support three types (Hierarchical, Horizontal and Tree structured) security label component: ARRAY, SET and TREE respectively.


  3. Ship pre-defined IDSLBAC access rules to govern the access to table row/column protected by security label. Access control enforces the proper IDSLBAC access rules that apply for the security component type for each component that makes up the security label.


  4. Allow flexible definition of security label components that make up a security label, which allows the security label structure to be compatible with US government as well as other application domain which require different label structure not necessarily compliant to traditional MLS security label structure and access rules. Support a security label to be made up to 16 components of any type.


  5. Provides means to bypass LBAC access rules through granting user exemption.


  6. Provides functions for manipulating security label. SECLABEL_BY_COMP(), SECLABEL_BY_NAME(), SECLABEL_TO_CHAR().



In part2 of LBAC overview, basic LBAC concepts and a simple user scenario will be covered.

Jihong Ma[Read More]

IDS 11.50 Installation Enhancements

cheetahblog Tags:  installation enhancements 1,516 Visits

IDS 11.50 made a significant improvement in installation process. Started with IDS 11.50 informix introduced a new versatile installation wizard (installer), which can automatically creates a customized database server configuration file (ONCONFIG) suitable for your system environment and create a demonstration database server during installation process.




Part of the installation process installer captures inputs from user. Then evaluates the input values to ensure settings are valid, and it calculates several values for other configuration parameters based on hardware settings and database instance needs. You can use the Instance Configuration Wizard in GUI or console installation modes to use this new feature. However, make sure you choose the option “Creating Demonstration Database Server Instance” during installation process to automatically create the customized configuration file.



There are little differences between UNIX and Windows installation wizard.



UNIX

  • Checked 'Yes' for Use create an IDS demonstration database server instance
  • Choose option for Customize the default configuration file



Windows
  • On Windows platform the Instance Configuration Wizard is only available with custom setup in GUI mode.
  • You must checked 'Demos' under select the feature you want to install
  • The 'Enable a custom configuration file' must checked also




Following are some restrictions that you need to remember during installation:

  • Installation directory must be empty
  • The root chunk file must be empty or not exists. Installer will create the file.
  • Parameters affected by the Instance Configuration Wizard are not available for silent installation.




During installation, Instance Configuration Wizard captured following input from user:

  • Installation Directory
  • Database Server Name
  • Database Server Number
  • Rootpath
  • Rootsize
  • No. of central processing units (CPUs)
  • Memory: System RAM dedicated to the IDS server (in MB)
  • No. of online transaction clients
  • No. of decision support clients



Based on the above information installer will set following configuration parameters:
ROOTPATH, ROOTSIZE, MSGPATH, DBSERVERNAME, DBSERVERALIASES, SERVERNUM, DRLOSTFOUND BAR_ACT_LOG, BAR_DEBUG_LOG, DUMPDIR, JVPJAVAHOME, JVPHOME, JVPPROPFILE, JVPLOGFILE, ALARMPROGRAM, SYSALARMPROGRAM, JVPCLASSPATH, BUFFERPOOL, VPCLASS



If the Instance Configuration Wizard encounters a problem while validating or calculating configuration parameters for customize configuration file, the configuration file is created with default, workable configuration parameters and an appropriate message displayed.



The customized configuration file will save as 'onconfig.<DBSERVERNAME>' in etc directory under <INFORMIXDIR>.



The "Creating Demonstration Database Server Instance" option with installation process will create a demo database instance. Once the installation process complete, installer generate two script files called profile_settings (korn shell) and profile_settings.csh (C shell) for setting environment variable for demo server instance in $INFORMIXDIR/demo/server (%INFORMIXDIR%\demo\server for Windows) directory.



Following is a screenshot of instance configuration wizard on UNIX platform (console installation mode):
Console Installation Mode on UNIX platform


You must choose '1 - Yes' for create an IDS demonstration database server instance and '3- Customize the default configuration file to suit your needs and hardware' in console installation mode.




Following are some screenshot of instance configuration wizard on Windows platform (GUI installation mode):
Custom  Installation in GUI Mode
You must select 'Custom' in GUI mode




List of Product Components
Make sure to checked 'Demos' for create an IDS demonstration database instance



Server Configuration Setup
Make sure 'enable a custom configuration file to suit your needs and hardware' also checked



Server Configuration Setup
An example of server configuration setup. You need to insert values for customize the database server.




Sanjit Chakraborty





[Read More]

Create a user define task

cheetahblog Tags:  define user task 1,483 Visits

Like a lot of fellow System Administrator, you may need to execute many system or administrative works automatically. Some of these works should occur at regular intervals. Other works need to run only once, perhaps during off hours such as evenings or weekends. You can use the Informix DB Scheduler to create a user define task to run administrative work automatically in the background at predictable times. The DB Scheduler uses SQL statements instead of operating system job scheduling tools, e.g., cron job.

In this document you will see an example of user define task that delete some unwanted files on a periodic basis. For better understanding you can perform this work in two phases.

  1. Create a stored procedure that performs the actual delete operation.
  2. Define a task that that executes the above stored procedure in certain interval.

Create a stored procedure

First you are creating a procedure under 'sysadmin' database named 'delete_apb_documents_proc()' that removes all files as 'apb_document*' from /tmp directory which accessed more than 60 minutes ago.

CREATE PROCEDURE sysadmin:delete_apb_documents_proc()

DEFINE cmd CHAR(80);
LET cmd = "find /tmp -maxdepth 1 -type f -name 'apb_document*' -amin +60 -exec rm {} \;";
SYSTEM cmd;

END PROCEDURE;

Use the UNIX manual page for 'find' command (man find) to get details on options used in the above example.


Define a task
Next, defining a task named 'delete_apb_document()' which runs top of each hours and execute the procedure delete_apb_documents_proc().

INSERT INTO sysadmin:ph_task
(
tk_name,
tk_group,
tk_description,
tk_type,
tk_execute,
tk_start_time,
tk_stop_time,
tk_frequency
)
VALUES
(
"delete_apb_document",
"MISC",
"Remove apb_document files from /tmp directory that last accessed 60 minutes ago",
"TASK",
"EXECUTE PROCEDURE sysadmin:delete_apb_documents_proc(); ",
"01:00:00",
"23:00:00",
INTERVAL ( 60 ) MINUTE TO MINUTE
);
 

Check the Informix Information Center to get more information on define a task.

You can run all the above SQL statements against 'sysadmin' database using dbaccess utility.

-Sanjit Chakraborty

Backing up to a directory with ontape

cheetahblog Tags:  ontape onconfig administration 1,461 Visits

With Cheetah, IDS now allows a DBA to use ontape to point to adirectory instead of just a particular file. This can be handy when youhave multiple backups that you want to keep in the samedirectory.   
It involves a little extra administration though, so here’s aquick primer on it.

Setting it up for archives
It’s actually pretty easy to set  IDS up to do it.Here are the steps:
  1. Create your directory: for instance:/opt/Informix/archives/ as Informix (group Informix) (I have the bestluck putting the ending slash on the path.)
  2. chmod 777 the directory 
  3. set TAPEDEV in your $ONCONFIG to that directory.
  4. run ontape to create your archive
Setting it up for back upof your logical logs with the log_full.sh $INFORMIXDIR/etc/ALARMPROGRAM
  1. As above, create your directory and set permissions.
  2. save a copy of your $INFORMIXDIR/etc/log_full.sh tolog_full.org or something like that
  3. edit log_full.sh with your favorite editor (i.e. vi oremacs)
  4. Add the –y option to the command (ontape–a –y) 
  5. Recycle the engine.
IFX_ONTAPE_FILE_PREFIX
When you take an archive by default it will use your<HOSTNAME_SERVERNUM> as the standard prefix. For example:my machine name is ryleh and my SERVERNUM=11 When I do an archive andthen look at the file it is this: ryleh_11_L0
If you want to name it something different, you can set theundocumented environmental variable IFX_ONTAPE_FILE_PREFX
    Example:
    export IFX_ONTAPE_FILE_PREFIX=play
You will have to recycle the engine after you set it for it to takeeffect.
This way you can have multiple archives stored in the same directory. 

Conclusion:
All in all, backing up to a directory can be a very handy utility tohave for an administrator that has multiple instances, especially forsuch things as development and test instances.  You can savean archive under the name of say “benchmark” andthen when you need to load it up, you don’t have to look highand low for the file or the tape.  It’s right there,and convenient.

Joe Baric[Read More]

Overview of LBAC - Part2

cheetahblog Tags:  lbac 1,460 Visits


What are the basic IDS LBAC concepts?


1. Security label component

Security label component is a new database entity that can be created, dropped, altered and renamed. It is the building block for security labels. A security label is composed of one or more security label components. For ALTER SECURITY LABEL COMPONENT, we only support adding security label component elements to existing security label component.

There are three types of security label components: arrays, sets, and trees.

ARRAY: An array represents an ordered set. In an array, the order in which the elements appear is important because it denotes the degree of sensitivity of the data. The rank of the first element is higher than the rank of the second element, and so on. There can be a maximum of 64 security label component element values in the array.

For example:
CREATE SECURITY LABEL COMPONENT levels ARRAY ['TOP SECRET', 'SECRET', 'UNCLASSIFIED'];
ALTER SECURITY LABEL COMPONENT levelsARRAY ['CONFIDENTIAL' BEFORE ‘UNCLASSIFIED’];

SET: A set is a collection of elements where the order in which those elements appear is not important. There can be a maximum of 64 unique values in the set.

For example:
CREATE SECURITY LABEL COMPONENT compartments SET {'Sales', 'HR', 'R&D', ‘Tech Support’};
ALTER SECURITY LABEL COMPONENT compartments SET {‘Marketing’, ‘Finance’};

TREE: A tree represents a hierarchy and is introduced to support the "group" concept that Oracle supports. Trees can be used to represent organizational charts and to identify departments within the organization that own the data. There can be a maximum 64 security label component elements in the tree.

For example:
CREATE SECURITY LABEL COMPONENT groupsTREE ('Worldwide' ROOT, 'Europe' UNDER 'Worldwide', 'North America' UNDER ' Worldwide ', 'Asia' UNDER 'Worldwide', 'China' UNDER 'Asia', 'USA' UNDER 'North America');
ALTER SECURITY LABEL COMPONENT groups TREE (‘France’ UNDER ‘Europe’, ‘Canada’ UNDER ‘North America’);

2. Security Policy

A security policy is a new database entity that can be created, dropped and renamed. The security policy defines the set of security label components that make up a security label. The security policy also specifies the access rules that IDS uses to determine whether a user who holds a security label L1 can access a data row or a column protected with a security label L2. A table can have maximum of one security policy attached to it and a policy can have a maximum of 16 security label components.

For example:
CREATE SECURITY POLICY secpolicy1COMPONENTS level, compartments, groups WITH IDSLBACRULES;

3. Access Rule

There are access rules for READ access and WRITE access for each type of security label component made up the security policy. Read access rules are applied by IDS when a user attempts to read (SELECT) a labeled data row. IDS applies the write access rules when a user attempts to insert, update or delete a data row. IDS will ship with a predefined set of read and write access rules. These rules are collectively called IDSLBACRULES and they can be divided into two categories as follows.

READ ACCESS RULE

The read access rules, called IDSLBACREAD, apply when data is retrieved. Data is retrieved on SELECT, UPDATE and DELETE operations. They can be summarized as follows:

IDSLBACREADARRAY: Each array component of the user security label must be greater than or equal to the array component of the data row security label i.e. the user can only read data at or below his/her level.
IDSLBACREADTREE: Each tree component of the user security label must include at least one of the elements in the tree component of the data row security label (or the ancestor of one such element).
IDSLBACREADSET: Each set component of the user security label must include the set component of the data row security label.

WRITE ACCESS RULE

The write access rules, called IDSLBACWRITE, apply for INSERT, UPDATE and DELETE operations. They can be summarized as follows:

IDSLBACWRITEARRAY: Each array component of the user security label must be equal to the array component of the data row security label i.e. the user can write data only at his/her level.
IDSLBACWRITETREE: Each tree component of the user security label must include at least one of the elements inthe tree component of the data row security label (or the ancestor of one such element).
IDSLBACWRITESET: Each set component of the user security label must include the set component of the data row security label.

4. Security Labels

A security label is a new database entity that can be created, dropped and renamed. A security label is always associated with a security policy which defines the set of components that make up a security label. There are three types of security labels:

User security label: A security label that is granted to a database user. A user can have at the most two labels per policy: one for read access and one for write access.

Row security label: A security label tagged with a data row of a database table.

Column security label: A security label associated with a column of a database table.

For example:
CREATE SECURITY LABEL secpolicy1.mylabel COMPONENT level 'TOP SECRET', COMPONENT compartments 'Marketing', COMPONENT groups 'Europe', 'north America';

5. Exemption

Exemption is a means to allow some database users to bypass the label access rules. The administrator can grant a database user an exemption to bypass one or more rules in a particular security policy. The following exemptions will be supported:

  1. An exemption to bypass one or more of the IDSLBACREAD rules

  2. An exemption to bypass one or more of the IDSLBACWRITE rules

  3. An exemption to bypass all read and write access rules


Note that the IDSLBACWRITEARRAY rule can be thought of as being two different rules combined. One prevents writing to data that is higher than user’s level (write-up) and the other prevents writing to data that is lower than user’s level (write-down). When granting an exemption to this rule one can exempt the user from either of these rules or from both.

For example:
GRANT EXEMPTION ON RULE IDSLBACWRITEARRAY WRITEDOWN FOR Secpolicy1 TO usrA, usrB;

6. Protected Table

A protected table is a database table to which a label security policy has been attached. There are two types of protection granularity supported:

Row level granularity: A database table can be marked as protected with row level granularity during CREATE TABLE or ALTER TABLE by attaching a security policy to such table and by specifying the column where the row security label will be stored; this column is referred as row label column, and the row label column is always defined as type of IDSSECURITYLABEL, which is a build-in distinct type used specifically for row label column. The security policy attached to the database table determines the type of the security label used to protect a data row, and the access rules that will govern access to labeled data rows in that table. The predefined IDSLBACRULES are the supported access rules that are essentially the same as DB2LBACRULES.

Column level granularity: A database table can be marked as protected with column level granularity during CREATE TABLE or ALTER TABLE by attaching a security policy to such table and by attaching a security label to one or more columns of that table. When a column is associated with a security label, that column is referred to as a protected column. The security policy attached to the database table determines the type of the security label used to protect a column, and the predefined IDSLBACRULES access rules that will govern access to protected columns of that table.

A protected table can also be defined with both row and column level granularities. When a protected table is accessed, column level access control is enforced before row level access control.

Example to create table with both row and column level protection
CREATE TABLE T1 (seclabel IDSSECURITYLABEL, employeeId int, SSN char (9) COLUMN SECURED WITH mylabel)SECURITY POLICY Secpolicy1;

7. Protected Column

A protected column is a column to which a security label has been attached. Users may or may not have the right to access such column depending on the security labels granted to them. When an SQL statement that refers to a protected column is submitted, IDS verifies whether the user is authorized to access that column.

IDS applies the rules above to compare the security label associated with the protected column and the security labels associated with the user. If the user cannot access that column in that mode (read or write) then an error is returned. Otherwise, the statement proceeds as usual.

For example:
CREATE TALBE T2 (salary float COLUMN SECURED WITH mylabel, name varchar (20)) security policy Secpolicy1;


What is the new database security administrator DBSECADM role?

IDS LBAC introduces a new database security administrator (DBSECADM) role. This role is required to manipulate LBAC related objects. This role is a server level role and can only be granted by a database server administrator (DBSA). The DBSECADM performs all the security related administration. The responsibilities include:
  1. Create, drop, alter and rename security label components

  2. Create, drop and rename security policies

  3. Create, drop and rename security labels

  4. Attach policies to tables

  5. Grant and revoke security labels

  6. Grant and revoke LBAC rule exemptions

  7. Change user using set session authorization

Example to grant a user with DBSECADM role:

GRANT DBSECADM TO USER user1;


What are security label scalar functions?

Security label scalar functions are introduced by LBAC feature to manipulate security labels during INSERT, UPDATE and SELECT operation.

SECLABEL_BY_COMP(): A built-in function that can be used in insert and update operations toprovide the row security label of a data row by providing its individual components as opposed toproviding its name.
SECLABEL_BY_NAME(): A built-in function that can be used in insert and update operations toprovide the row security label of a data row by providing its name as opposed to its individualcomponents.
SECLABEL_TO_CHAR(): A built-in function that can be used in select operations that retrieve therow security label column. The function returns the row security label by giving the details of itsindividual components as opposed to its name. This function can be thought of as the reverse of SECLABEL_BY_COMP.

Examples of security label functions

INSERT INTO T1 VALUES (SECLABEL_BY_COMP('Secpolicy1', 'Top Secret:Marketing:Europe’), 1, '100200300');
INSERT INTO T1 VALUES (SECLABEL_BY_NAME('Secpolicy1', 'mylabel'), 2, '200300600');
SELECT SECLABEL_TO_CHAR('Secpolicy1', seclabel), employeeId, SSN FROM T1;

Usage scenario:

This simple usage scenario will serve as an example to demonstrate how to perform LBAC setup on your database to protect your sensitive data.

1. DBSECADM creates the security label components, security policy, and security label.

CREATE SECURITY LABEL COMPONENT level ARRAY ['TOP SECRET', 'SECRET', 'CONFIDENTIAL', 'UNCLASSIFIED'];
CREATE SECURITY LABEL COMPONENT compartments SET {'Sales', 'HR', 'R&D', ‘Tech Support’};
CREATE SECURITY LABEL COMPONENT groups TREE ('Worldwide' ROOT, 'Europe' UNDER 'Worldwide', 'North America' UNDER ' Worldwide ', 'Asia' UNDER 'Worldwide', 'China' UNDER 'Asia', 'USA' UNDER 'North America');
CREATE LABEL SECURITY POLICY Secpolicy1COMPONENTS level, compartments, groupsWITH IDSLBACRULES;
CREATE SECURITY LABEL Secpolicy1.HR COMPONENT level 'TOP SECRET',COMPONENT compartments 'HR',COMPONENT groups 'Worldwide';

2. DBSECADM grants security label to user Nancy who is an employee from HR department.

GRANT SECURITY LABEL Secpolicy1.HR FOR ALL ACCESS TO USER Nancy;

3. DBSECADM creates a protected table and attaches the label security policy to the table:

CREATE TABLE T1 (seclabel IDSSECURITYLABEL, employeeId int, SSN char(9) COLUMN SECURED WITH HR) SECURITY POLICY Secpolicy1;

4. User Nancy try to access the table.

INSERT INTO T1 (employeeId, SSN) VALUES (6, '123456789');



This statement will succeed and the seclabel column is inserted with the default valueof Nancy’s write label, in this case it is label HR.

SELECT employeeId, SSN from T1;

This statement will return one row Nancy just inserted. If the same SELECT statement is executed by user Bob and Bob has not been granted any label for Secpolicy1 yet, no rows will be returned.

DELETE from table T1 where employeeId = 6;

If the DELETE statement is executed by Nancy, one row (inserted by Nancy) will be deletedfrom the table, no row will be deleted if it is executed by Bob.


More practical usage scenarios of LBAC will be covered in subsequent LBAC blog entries to illustrate the power of LBAC on data access control.

Jihong Ma[Read More]

Monitoring Onbar Performance

cheetahblog Tags:  performance onbar 1 Comment 1,453 Visits

Previously there was no easy way to monitor onbar archiving progress. It always a question, how long onbar process will take to complete an archive or how much time onbar will spend to transfer data between server, storage manager and vice versa.



Informix introduce two new configuration parameters to help onbar monitoring.


  • BAR_PROGRESS_FREQ
  • BAR_PERFORMANCE




BAR_PROGRESS_FREQ
The BAR_PROGRESS_FREQ configuration parameter specifies, in minutes, the frequency of the progress messages in the bar activity log for backup and restore operations.



For example, if BAR_PROGRESS_FREQ is set to 5, onbar reports the percentage of the object backed up or restored every five minutes. Following is an excerpt of bar activity log that showing progress of rootdbs dbspace backup:


  2007-05-09 16:12:58 13344  13342 /export/home/informix/bin/onbar_d -b -L 0 2007-05-09 16:12:59 13344  13342 Archive started on rootdbs (Requested Level 0). 2007-05-09 16:13:00 13344  13342 Begin level 0 backup rootdbs. 2007-05-09 16:13:00 13344  13342 Successfully connected to Storage Manager. 2007-05-09 16:18:00 13344  13342 1 percent of rootdbs has been backed up. 2007-05-09 16:23:01 13344  13342 6 percent of rootdbs has been backed up. 2007-05-09 16:28:02 13344  13342 11 percent of rootdbs has been backed up. 2007-05-09 16:33:03 13344  13342 16 percent of rootdbs has been backed up. 2007-05-09 16:38:04 13344  13342 21 percent of rootdbs has been backed up. 2007-05-09 16:43:05 13344  13342 26 percent of rootdbs has been backed up.


The default value of BAR_PROGRESS_FREQ is 0. If the value set to 0, onbar does not write any progress messages to the bar activity log.



The BAR_PROGRESS_FREQ value can’t less than five minute for monitoring onbar progress.



If ON–Bar cannot determine the size of the backup or restore object, it reports the number of transfer buffers sent to the database server instead of the percentage of the object backed up or restored.




BAR_PERFORMANCE


The BAR_PERFORMANCE configuration parameter specifies the type of performance statistics to report, and write them to the bar activity log for backup and restore operations.



For example, if BAR_PERFORMANCE is set to 3, onbar reports the time spent transferring data between the Informix server and the storage manager, in the bar activity log.



The default value of BAR_PERFORMANCE is 0. If the value set to 0, onbar does not report any performance statistics to the bar activity log.



Valid values of BAR_PERFORMANCE are 0,1,2 or 3.



  • 0 - turn performance monitoring off
  • 1 - display the time spent transferring data between the server and storage manager
  • 2 - display sub-second accuracy in the timestamps
  • 3 - display both timestamps and transfer statistics




Both BAR_PROGRESS_FREQ and BAR_PERFORMANCE configuration parameters take effect while onbar process starts.



Sanjit Chakraborty

[Read More]