Informix Experts

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

Sanjit Chakraborty[Read More]

ON-Bar whole system parallelize backup/restore

cheetahblog 1 Comment 4,901 Views

Prior to Cheetah,  a whole-system backup (onbar -b-w)  was serial backup of all storage spaces andlogical logs based on a single checkpoint. That time is stored with thebackup information. Theadvantage of using a whole-system backup was  that you canrestore the storage spaces with or without the logical logs.Because the data in all storage spaces is consistent in a whole-systembackup, you do not need to restore the logical logs to make the dataconsistent. Level 0, 1, or 2 backups are supported.

One of the new features of Cheetah is Backup/Restore dbspacesin parallel with whole system functionality .  With parallelbackup , multiple processes run simultaneously  each processbacking up a different dbspace . In most cases, parallel backups complete fasterthan serialbackups, which use only one process.

ONCONFIG parameter  "BAR_MAX_BACKUP" isused for whole system backups/restores as well. TheBAR_MAX_BACKUP parameter specifies the maximum number of parallelprocesses that are allowed for each onbar command. Both UNIX andWindows support parallel backups. Although the database server defaultvalue for BAR_MAX_BACKUP is 4, the onconfig.std value is 0.

To specify parallel backups and restores, including parallel wholesystem backups and restores, set BAR_MAX_BACKUP to a value higher than1. For example, if you set BAR_MAX_BACKUP to 3 and execute anON–Bar command, ON–Bar will spawn the maximum 3 processes concurrently.

If  BAR_MAX_BACKUP to 0, the system creates as manyON–Bar processes as needed. The number of ON–Barprocesses is limited only by the number of storage spaces or the amountof memory available to the database server, whichever is less.

To perform a serial backup or restore, including a serialwhole system backup or restore, set BAR_MAX_BACKUP to 1. 

Also in Cheetah,  storage manager efficiency isimproved  by improvingdbspace ordering .  "Biggest" dbspaces are backed up first and the "smallest"last.Dbspace with most pages used is the "biggest" and will be backed upfirst. This ensures better parallelism, no matter how BAR_MAX_BACKUP isset or how many pages are to be backed up in different dbspaces. Neworder of backup/restore is also effective for non-wholesystembackup/restore. Restore is done in the same order as backup to ease storagemanager's  access to stored objects.

To implement new ordering of dbspaces for backup and restore changes are made to the bar_instance table in the sysutils Database .
New column "ins_backup_order" (integer default 0 not null) is added to the table bar_instance .
Also changes are made to ixbar file :
New field with backup sequence integer (Last field in the line ) is added in ixbar file.
Value of the new field is always "0" (zero) for log backup objects.

Even if the BAR_MAX_BACKUP is set to 1 for serial backup,the dbspaces will be backed up using the new dbspace order .

Backup Operation
Only 1 checkpoint for all dbspaces - just before backup of rootdbs .
Rootdbs is backed up first, without parallelism (no change here).
Before image processor threads "arcbackup2" are started at this time, one for each dbspace  (more threads running in parallel.)
As each dbspace backup completes, the respective "arcbackup2" thread exits ( less "arcbackup2" threads as backup progresses. )
New order of backup. The order is based on the used-pages count at the start time of backup. Dbspace with most pages used is the "biggest" and will be backed up first.

Restore Operation
Not much changed, only it is now parallel.
Rootdbs is restored first, without parallelism (no change here).
Restore dbspaces in same order as they were backed up. Use the value of "ins_backup_order" to determine correct order. If BAR_MAX_BACKUP is changed between backup and restore, the objects will still be restored in the same order, however the timing relative to each other may differ significantly. This could have negative effects on SM performance .

Rashmi Chawak[Read More]

Index Self-Join

cheetahblog Tags:  selfjoin index 3 Comments 8,951 Views
Beginning with IDS version 11.10, Informix introduced a new index scan feature, called “Index Self Join”. Priorto IDS version 11.10, a composite index scan allows to scan a single range of an index, based on start and end index key position. The Index Self Join access method permits scan many small subsequent ranges inside the composite index, instead of a large single range, based on filter on non-leading keys of a composite index.

On previous IDS versions, queries from table with composite index perform inefficiently where higher ratio of duplicate values in leading column than subsequent column(s) in the composite index. The Index Self Join is new type of index scan that can use subsets of the full range of a composite index. With this new index scan, tables can join logically to itself, and more selective non-leading index keys are applied as index bound filters to each unique combination of the leading key values.

Previously optimizer scans all index keys that fulfill the complete index key condition; otherwise it would make a sequential scan if the leading key had no WHERE clause conditions associated in the query. With this feature it will find the unique leading keys (low selectivity), and will make small queries using this unique keys and the rest of the index keys provided in the WHERE clause condition of the query.

An Index Self Join is beneficial for situations in which:

  • The lead key of an index has many duplicates, and
  • Predicates on the lead key are not selective, but predicates on the non-leading index keys are selective.

How ‘Index Self Join’ works
Lets create a table ‘tab1’ with following table schema and load some data:
      CREATE TABLE tab1 (        col1  int,        col2  int,        col3  int,        col4  char(10));      CREATE INDEX idx1 ON tab1(col1,col2,col3);

Here is an illustration how IDS process following query prior version and on v11.10:

      SELECT * FROM TAB1      WHERE col1 >= 1 AND col1 <= 3      AND   col2 >= 20 AND col2 <=31      AND   col3 >= 40 AND col3 <= 62;

The simple view of the composite index ‘idx1’ on tab1(col1,col2,col3):

Diagram of a composite index

Prior to IDS v11.10, only possible filters could use on col1 (col1 >= 1 AND col1 <= 3) for positioning of the index scan:

Prior version
Lower Filter col1 >= 1, Upper Filter col1 <= 3

Diagram of an index scan prior to v11.10

Beginning with IDS version 11.10, we can use filters on ‘col2’ and ‘col3’for positioning of the index scan that allows skip unnecessary index keys at two ends of the index. IDS scan selective index keys that are relevant, which avoid scanning a large portion of the index. This strategy will improve the query performance by reducing the portion of the index needs to scan.

IDS version 11.10
Lead Keys: col1, col2
Lower Filter col1 = col1, col2 = col2 and col3 >= 40
Upper Filter col3 <= 62

Diagram of an index scan on v11.10

Example of ‘Index Self Join’
A table ‘tab1’ has been created with following schema:

Table schema of TAB1

So the table ‘tab1’ has a composite index ‘idx1’ on columns ‘col1’, ‘col2’ and ‘col3’. Now we will test following query without the leading index key ‘col1’ in WHERE clause condition:

    SELECT * FROM TAB1    WHERE col2 >= 10 AND col2 <=11    AND   col3 >= 20 AND col3 <= 22;

Following is the query access path of above SQL in IDS version prior to 11.10 where optimizer doing sequential scan:

Explain output prior to IDS v.11.10

Following is the query access path of previous SQL in IDS version 11.10 where optimizer using subsets of the full range of composite index:

Explain output on IDS v.11.10

Default Setting
By default, the optimizer considers ‘Index Self Join’ on IDS v.11.10. Following are couple of ways to change the default settings:

  • Set onconfig parameter INDEX_SELFJOIN to 0
  • Dynamically change the onconfig settings using onmode command
           onmode -wm INDEX_SELFJOIN=0 
  • Using optimizer directive AVOID_INDEX_SJ
           SELECT {+AVOID_INDEX_SJ(TAB1 IDX1) *        FROM TAB1       WHERE col2 >= 10 AND col2 <=11       AND   col3 >= 20 AND col3 <= 22;

New Directives with ‘Index Self Join’
The optimizer also supports two new access-method directives:
    INDEX_SJ (table index [ , index ...] )     AVOID_INDEX_SJ (table index [ , index ...]
Use the specified index to scan the table in an index self-join path. The optimizer is forced to scan the table using an index self-join path with the specified index (or to choose the least costly index in a comma-separated list of indexes for an index self-join path).

Does not use an index self-join path for the specified indexes. The optimizer does not consider the specified index for scanning the table in an index self-join path.

Sanjit Chakraborty[Read More]

Introduction to Basic Text Search Datablade Part 1 Description and Syntax

cheetahblog Tags:  text search syntax bts 6,282 Views
One of the new features for Cheetah is the inclusion of severaladditional datablades. One of the more exciting of these blades is theBasic Text Search blade, BTS  for short. This datablade allowsyou to do a lot of  searches on text data found in char,varchar, lvarchar, blob and clob datatypes. The intent is to provide amore robust method by which to search text than current forms like theMATCHES or LIKE functions.  The documentationfor the BTS blade can be found in TheBuilt-In DataBlade Modules User's Guide . Today we willgo over a little of the syntax that allows you to perform some neatqueries.

At the heart of the BTS datablade is a single function. The name isbts_contains, and it has the following syntax:

bts_contains (<column to search>, <searchcriteria>,  {score # REAL})

The score section at the end is optional, and what it does is allow youto give a weighted value to each member of your result set.
So let's presume that you had a varchar(255) column called remarks in atable called photos. If you wanted to search for the words "Apple" orthe phrase "Macbook Pro" , and you only wanted the score of 70.0 or greater you would do the following:

SELECT * FROM photos WHERE bts_contains(remarks, 'Apple OR "MacbookPro"', score # REAL)
AND score >= 70.0

If  you didn't care about getting the score back from yoursearch, then you could write it as the following:

SELECT * FROM photos WHERE bts_contains(remarks, 'Apple OR "MacbookPro"')

The BTS datablade takes most of the shortcuts you are familiar withfrom using most web search engines in today's marketplace.

In part 2 we will cover how to setup the BTS blade and the administrative functionsthat are available.

Mark Jamison[Read More]

onstat -g pqs

cheetahblog Tags:  diagnostic sql analysis 1 Comment 8,794 Views
In version 10.00 IDS made available a new onstat command. The onstatcommand was onstat -g pqs. For those familiar with the old X-based toolcalled xtree, onstat -g pqs allows you to dynamically track a session'squery plan/tree. The syntax is as follows:

onstat -g pqs {0||<session id>}

The 0 will actually attempt to grab the current step of each query planfor all sessions.

Two considerations

  1. onstat -g pqs only shows all phases that have currentlyprocessed, plus the current phase.
  2. onstat -g pqs only shows active queries, once a query iscomplete the plan is gone.

Below is a sample based on the following query.

SELECT c.customer_num, fname,lname, company, o.order_num
FROM customer c, orders o, items i
WHERE c.customer_num= o.customer_num
AND o.order_num = i.order_num
GROUP BY c.customer_num, o.order_num, fname, lname, company
ORDER BY c.customer_num, lname, fname, company, order_num

Onstat -g pqs output

As you can see an additional drawback is that the tables themselves are not named, but it does clearly show you the query tree as it was executed.

Mark Jamison[Read More]

Non-blocking Checkpoints - Part 1

cheetahblog Tags:  non-blocking interval checkpoint 6,139 Views
Prior to IDS Version 9, IDS supported a blocking checkpoint algorithmthat causes the system to block all transactional updates while thebufferpool is flushed to disk. In IDS Version 9 onwards, an additionalcheckpoint algorithm, called Fuzzy Checkpoint, was introduced. FuzzyCheckpoints limit the number of updates required to be flushed to diskduring checkpoint processing.

With the new non-blocking checkpoint algorithm in Cheetah, IDS ensuresthat we virtually don't block transactional updates while the entirebufferpool is flushed to disk. Transactionalupdates are blocked only for very small duration of time required to flush thepartition partitions (tablespace tablespaces) to the bufferpool and tograb the restart point. (Restart point is wherefastrecovery starts. We log the checkpoint and use that LSN as the restartpoint for the checkpoint.)

As part of this feature, the new "onstat-g ckp" command helps DBAs to view and analyze checkpointinformation and performance advisories. The "onstat -g ckp" command isexplained in more detail in thispost.

ScottLashley's whitepaper on developerWorks explains the internalsabout non-blocking checkpoints in detail.

In Part 2, we shall learn how to tune checkpoints and modify the relevant onconfig parameters (RTO_SERVER_RESTART, AUTO_CKPTS,AUTO_LRU_TUNING, etc.)

Mirav Kapadia[Read More]

Overview of LBAC - Part2

cheetahblog Tags:  lbac 6,653 Views

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:

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:

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.


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.


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:

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:


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 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 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.


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


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]

Backing up to a directory with ontape

cheetahblog Tags:  ontape onconfig administration 2 Comments 7,612 Views
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 $INFORMIXDIR/etc/ALARMPROGRAM
  1. As above, create your directory and set permissions.
  2. save a copy of your $INFORMIXDIR/etc/ or something like that
  3. edit with your favorite editor (i.e. vi oremacs)
  4. Add the –y option to the command (ontape–a –y) 
  5. Recycle the engine.
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
    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. 

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 IDS LBAC -- Part 1

cheetahblog Tags:  lbac 6,777 Views

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]

Checkpoint Performance Advisory

cheetahblog Tags:  -g 'onstat ckp' 6,950 Views

On IDS version 11.10 a significant amount of focus has been given to automatically keep administrators up-to-date on database performance. Several performance advisory triggers added to Informix server to flag administrator once a threshold reached. The IDS will automatically calculate different thresholds based on current database activities and generate an appropriate performance advisory in message log (MSGPATH).

Following is a list of performance advisory available in IDS version 11.10:

  • Physical log too small for RTO_SERVER_RESTART
  • IDS server boot time too long
  • Physical log too small
  • Logical log too small
  • Long transactions blocking checkpoints
  • Physical log too small to accommodate bufferpool flushing
  • Logical log too small to accommodate bufferpool flushing
  • Physical log too small to accommodate automatic checkpoints
  • Logical log too small to accommodate automatic checkpoints
  • Bufferpool flushing

In this article we will discuss about the checkpoint related advisory. The ‘onstat -g ckp’ command has introduced with IDS version 11.10 to get checkpoint information and checkpoint related advisory. This command will display checkpoint related configuration parameters recommendation, once a sub-optimal configuration detected.

Following configuration parameters can significantly impact performance of IDS:


During checkpoint IDS will evaluate checkpoint related configuration parameters and produce a performance advisory once a sub-optimal configuration detected. If configuration parameters is not tune properly, database server can encounter block during checkpoint.

Following is an example of ‘onstat -g ckp’ command output:

Example: 'onstat -g ckp'

The description of ‘onstat -g ckp’ command output:
  • The first section of the display describes checkpoint information

    Example: 'onstst -m'

  • The second section of ‘onstat -g ckp’ output describe the checkpoint related advisory

    In the above example physical log file size (PHYSFILE) was set to 15000 Kbytes. During checkpoint IDS evaluate the configuration parameters and found out physical log size was small, which can cause checkpoint block, and suggested PHYSFILE value as 16800 Kbytes.

The checkpoint performance advisory also available in the message log file. Following is an example of message log with a checkpoint performance advisory:

Example: 'onstst -m'

Sanjit Chakraborty[Read More]

Encrypted Communications for HDR

cheetahblog Tags:  hdr encryption 6,499 Views

In IDS Cheetah release, you can encrypt communication between an HDR pair, to secure the transmission of data over unsecured networks, including the internet. After you enable encryption, the HDR primary encrypts the data before sending it to the HDR Secondary server. The HDR secondary server decrypts the data. Use new ONCONFIG parameter ENCRYPT_HDR to enable encryption between the HDR pair. You can also customized encryption using following parameters.

Configuration Parameter Default value Comments/Description
ENCRYPT_HDR 0 0 - disable, 1 - enable HDR encryption
ENCRYPT_CIPHERS allbut:<ecb> Defines ciphers and modes that can be used by the current database session.
The following ciphers are currently supported:
  • des (64-bit key), des3 (Triple DES), desx (Extended DES, 128-bit key)
  • aes/aes128 (128-bit key), aes192 (192-bit key), aes256 (256-bit key)

  • bf-1 Blowfish (64-bit key), bf-2 (128-bit key), bf-3 (192-bit key)
ENCRYPT_MAC medium Controls the level of message authentication code (MAC) generation.

  • off - does not use MAC generation.
  • low - uses XOR folding on all messages.
  • medium - uses SHA1 MAC generation for all messages greater than 20 bytes long
    and XOR folding on smaller messages.
  • high - uses SHA1 MAC generation on all messages.

ENCRYPT_MACFILE builtin A list of the full path names of MAC key files.
ENCRYPT_SWITCH 60,60 Defines the frequency (in minutes) at which ciphers, secret keys are renegotiated.
cipher_switch_time, key_switch_time
  • To use your own MAC key file
    • Execute the following command to generate MAC Key file.
      $INFORMIXDIR/bin/GenMacKey -o  /usr/informix/etc/MacKey1.dat
    • Copy MacKey1.dat over to the paired server

    • Update ENCRYPT_MACFILE configuration parameter on both the servers as shown below
      ENCRYPT_MACFILE    /usr/informix/etc/MacKey1.dat,builtin

  • NOTE - HDR and Enterprise Replication (ER) share the same encryption configuration parameters: ENCRYPT_CIPHERS, ENCRYPT_MAC, ENCRYPT_MACFILE and ENCRYPT_SWITCH.

Nilesh Ozarkar[Read More]

Tracing SQL in Cheetah Part 2.

cheetahblog Tags:  sql diagnostics trace 3 Comments 8,796 Views
SQLTRACE part 2Today we will now cover part 2 of the new SQL tracing facility inCheetah. In part 1 we discussed how to enable SQLTRACE and how tomonitor it via onstat -g his. I'm sure you noticed that while onstat -ghis provides a lot of very useful information, you would need someserious awk and sed skills to script it, for example, that you only sawqueries that had an average execution time of 1 second or more.Fortunately SQL tracing is set up in the sysmaster database. The tableis called


This table allows you to quickly get subsections of the SQL traceoutput. So, for example,  you can find out how many queriestookmore than 1 second to run by executing the following query:

select count(*) fromsyssqltrace where sql_totaltime >1;

You can obviously drill down and get additional information includingthe actual queries that were run that took more than 1 second.

The table syssqltrace is not the only table, however, that coversinformation regarding SQL tracing. The table syssqltrace_iter
provides information in the form of an iterator tree for each sql. This allows you to know what part of your plan took the mosttime to run.

These two tables allow you to use the information gathered from SQLtracing, and will allow you to generate reports, and diagnose problemSQL more efficiently.

Mark Jamison[Read More]

Support for TRUNCATE TABLE in Enterprise Replication

cheetahblog Tags:  er replication truncate 6,096 Views
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]

New Beta Drop available

cheetahblog Tags:  cheetah 4,753 Views
The latest Beta drop of IDS Cheetah is now available, with some important new features integrated into the code line..

New features available in Beta drop 5 include:

  • AIX 64-bit platform build available for download
  • Web Feature Service for Spatial Data - Delivers spatial information as a web service for your Service Oriented Architecture
  • Label Based Access Control (LBAC) - Control data access at a row and column level using security labels
  • Continuous Availability with multiple HDR remote secondary servers

The following Infocenter page has a complete list of new features for each Beta drop:

Guy Bowerman[Read More]

RENAME support in Enterprise Replication

cheetahblog Tags:  er replication rename 4,510 Views
From IDS 10.0x onwards, Enterprise Replication (ER) supports alteroperations on a replicated table while replication is active however, RENAMEsupport was not one of them but that changes in Cheetah. IDS 11.x willstart supporting RENAME on ER columns, tables and databases. This featuresimplifies DBA tasks and increases data availability. Without this feature, theDBA would have to plan on a time in which ER could be removed from the objectso that the rename could be performed. This would require advanceplanning and scheduling during non-peek hoursfor performing the rename.

When a RENAME operation is performedaffected replicate definitions will be updated to reflect the impact of theRENAME and a control message will be sent to the other servers informing themof the rename. RENAME operation is allowed onlyon a mastered replicate. It does not propagate the RENAME command itself, there are plans to implement that too inthe future. The user simply will issue a rename DDL statement on each of theservers that are affected by RENAME. If therename is a column or a table, then the replicate will be cycled (cdr stop/start replicate) in much thesame way that occurs with a table ALTER. If a database rename occurs,however, ER will be cycled (cdr stop/start). This cycling will only occur if the renameaffects something on the local node. In all cases, the rename will causea control message to be sent to the other servers within the replication domainso that the syscdr database is correctlyupdated.

Vijay Lolabattu[Read More]

Tracing SQL in Cheetah Part 1

cheetahblog Tags:  sqltrace diagnostics sql 1 Comment 6,924 Views

One of the new features in Cheetah is the ability to enable tracing for SQL. While this has been available in limited method though the use of the IBM product I-SPY, the drawback before has always been the performance hit to actually monitor this information. That limitation is effectively eliminated with this feature in Cheetah. So how is SQL Tracing enabled? It's actually pretty simple, one way is to just put SQLTRACE in your ONCONFIG. One configuration I use most often is:

SQLTRACE level=low,ntraces=1000,size=2,mode=global

This allows me to keep track of the last 1000 sql statements on the instance.

However ONCONFIG is not the only way to enable tracing, you can also dothis through the sysadmin database by running the following:

EXECUTE FUNCTION task("set sql tracing on", 1000, 2,"low","global");

So by using either of the above methods you now have enabled SQLRACE, buthow do you validate it. The easiest method is by running :

onstat -g his

Below is a sample of onstat -g his output.

onstat -g his.jpg 


In Part 2 we will look at how to read this in SQL, and possible optionsfor the data.

Mark Jamison[Read More]

The DB Scheduler

cheetahblog Tags:  sysadmin scheduler 8,235 Views

This article describes how IBM Informix DB Scheduler tool automatically keeps administrators up-to-date on database performance and availability and execute corrective actions automatically.

What is DB Scheduler?

The DB Scheduler is a self-managing technology simplifies database administration and lets administrator concentrate on other business issues. Current 24/7 support environment where each minutes counts, this self-managing technology can significantly reduce the database downtime by sending alerts prior to a problem occur.

The DB Scheduler manages and executes scheduled maintenance, monitoring, and administration tasks. This tool enables administrator to monitor activities (for example, space management, automatic backup etc. ) and create corrective actions that run automatically.

The DB Scheduler comprise of Tasks and Sensors.

  • TASK: Task is a means to execute specific job at a specific time or interval. There are some tasks that run only once when the database server starts, which call ‘Startup Task’. A task executed by invoking one of following manners:

    • A single or compound SQL statement
    • Stored procedure
    • C User Defined Routine
    • Java User Defined Routine

  • SENSOR: Sensor is a specialized TASK for collecting and saving data without database administrator’s interventions. It provides a simple way of collecting information. There are some sensors that run only once when the database starts, which call ‘Startup Sensor’. Sensor is a way of ensuring routine jobs get completed and periodically check; analyze collected data to ensure the data server operating efficiently.

The DB Scheduler is defined and driven by tasks. A new database ‘sysadmin’ has introduced starting with IDS v.11 for DB Scheduler. The sysadmin database has five various tables which contain and organize different properties of DB Scheduler. Only members of the DBSA group are granted access to connect to the sysadmin database. By default, user informix is granted access to the sysadmin database. Following are list of tables in sysadmin database:

sysadmin table

There are two parameters available for use with create task and sensor. Values of these parameters can be used within SQL statement for storing and tracing data in sysadmin database.
  • $DATA_SEQ_ID – The number of times task has been run. Each time a task executed the sequence id increase by 1
  • $DATA_TASK_ID – A unique task id for each task.

The SQL statement is checked for two reserve strings $DATA_SEQ_ID & $DATA_TASK_ID. If those are found inside a SQL statement than the current sequence id is replace the $DATA_SEQ_ID and the current task id replaces $DATA_TASK_ID.

How to create a new Task?

A task can be created by inserting a row in the ph_task table. The ph_task table lists all tasks and contains information about how and when the database server will execute each task.

When the database server executes a task, the server invokes the SQL object contained in the pk_execute column of the ph_task table. The pk_execute column can contain a user-defined function, a single SQL statement, or a multiple-statement.

Prior to creating a new task we need to plan on following:
  • A description of the task
  • SQL object, stored procedure, or a function to execute the objective of the task
  • Information on when and how often task needs to run
  • The SQL statement to insert a new row into ph_task table to create task

The following is an example of create task that runs once a day at 4:00 A.M. to ensure that table ‘orders’ contains only last one month of data:

Example: Task 1

The same above task can be creating using ph_threshold table for order retention policy information. An associated row must be inserted in ph_threshold table for 'DELETE ORDER RECORDS'. Modify the order retention period by changing information in the 'DELETE ORDER RECORDS' row in the ph_threshold table. The 'DELETE ORDER RECORDS' parameter sets the length of time rows should remain in the order table. Following is an example of create task using ph_threshold table:

Example: Task 2

How to create a Sensor?

Sensor is a specialized TASK for collecting and saving data. Sensor can be created same way as task by inserting a row in the ph_task table, with some additional information.

Prior to creating a new sensor we need to plan on following:

  • A description of the sensor
  • The result table to save collect information
  • SQL object, stored procedure, or a function for collect information
  • Information on when and how often sensor needs to run
  • The SQL statement to insert a new row into ph_task table to create sensor

Following is an example of sensor that collects information on database memory usage and stores information in a result table called ‘mon_memory_system’. In case the result table does not exist, it creates the table. In the example sensor runs every 30 minutes, and also deletes data from ‘mon_memory_system’ table older than 30 days:

Example: Sencor 1

The DB Scheduler has some built in sensors and tasks, which can be use for different scheduled maintenance, monitoring, and administration. Following is a list of built in sensors and tasks:

Built in Task List

How to stop the DB Scheduler?
The DB Scheduler is a useful feature. However, if it needs to stop for some reason, the recommended way is use the task() routine. Following SQL can be use for shutdown DB Scheduler:

   DATABASE sysadmin;   EXECUTE FUNCTION task("scheduler shutdown");   CLOSE DATABASE;

How to limit space utilize by DB Scheduler?
The DB Scheduler needs additional disk space for historical data. Sensors collect and save information in results table at sysadmin database. Following formula can be use for estimate the disk usage for each sensor:

   (Number of rows collected   *     Size of the row collected  *     The frequency of data collection per day   *     The retention period)

By disabling sensors one can prevent collect and save historical information. However, disabling sensors can effects adversely other database server functions. It is always better to update the ph_task table in sysadmin database to limit the amount of data collect by a sensor than disable sensors. Following is an example that tells the DB Scheduler to delete data older than 4 times the collection frequency:

   DATABASE sysadmin;   UPDATE ph_task    SET tk_delete = (4 * tk_frequency)    WHERE tk_type = 'SENSOR';

Sanjit Chakraborty[Read More]

New onstat option for ER

cheetahblog Tags:  sbspace rqm er onstat 4,995 Views
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]

Installing IDSAdmin in a Windows XAMPP environment

cheetahblog Tags:  oat idsadmin 4 Comments 12,368 Views

OpenAdmin Tool for IDS, the PHP-based administration console for IDS, is now available for downloadfrom the Free Product download site.

Updated 2/14/08 with new recommended PHP and XAMPP versions which are good for Cheetah and Cheetah 2.

Note: This article was updated for the latest version of OpenAdmin Tool on July 30 2007.

Installation of OpenAdmin can appear complex - this article provides anexample of one way of installing OpenAdmin. This example uses an XAMPP package on Windows. XAMPP is a bundle of open-source utilities (such as Apache, PHP and PEAR) thatare used by OpenAdmin. Installing XAMPP simplifies the OpenAdmin install,but many other installation and configuration options are possible.

High-level Summary of Install steps
Detailed steps are provided below. This is the high-level sequence of stepsyou will follow:

  1. Install CSDK or Connect 3.00.TC1 (or later)
  2. Download and extract the XAMPP 1.6.4 zip file
  3. Run XAMPP setup
  4. Edit the php configuration file
  5. Download and extract the OpenAdmin package
  6. Start the Apache Webserver
  7. Install and configure OpenAdmin
1. Install Informix Client SDK or Connect

If not already installed, install the Informix Client software. Rememberwhere you install it; the INFORMIXDIR value will need to be set as an environment variable when you start thewebserver. You can set INFORMIXDIR in the system environment using Control Panel->System. (Note: CSDK or Connect 3.00.TC1 or later is recommended.)

2. Download XAMPP and extract files.
  • Unzip XAMPP to the destination directory of your choice. (In this example we'll assume the zip was extracted to c:\, which would put the XAMPP software in c:\XAMPP\.)

3. Run XAMPP setup

cd to the XAMPP directory and run setup_xampp. When the batch file finishes running, press any key to continue.

This illustration shows a Windows command interpreter window displaying the output of the setup_xampp program. The screen indicates that AcceptEx Winsocks v2 support for NT systems has been completed.

4. Update the php configuration file (php.ini)

Navigate to the xampp\apache\bin subdirectory, and open the php.ini file in a text editor. (Note: the XAMPP package contains more than onephp.ini file - the correct one is in xampp\apache\bin (i.e., c:\xampp\apache\bin\php.ini).)

Edit php.ini to enable the SQLITE and Informix PDO drivers:

  1. Locate the following lines and remove any comment indicators from in front of them:
  2. Add the following line to the file:

5. Download and Extract the OpenAdmin zip file.

  1. Download the OpenAdmin zip file from the Open Source download site to a temporary location.
  2. Create a new directory under c:\xampp\htdocs to contain the zipped files (For example: c:\xampp\htdocs\openadmin)
  3. Unzip to this new directory.

6. Start the Apache Webserver.

  1. Verify INFORMIXDIR is set to the Informix Connect or CSDK directory.
  2. Run c:\xampp\xampp-control.exe.
  3. Start the Apache Webserver.

This illustration is a screen capture of the XAMPP Control Panel. It shows that Apache has been started.

7. Install and Configure OpenAdmin.

Go to http://localhost/openadmin/install and follow the installation instructions. After the first install screen a check will be made to ensure you have the correct PDO drivers:

This image is a screen capture of a web browser displaying the OpenAdmin Tool installation screen. It shows that PDO, pdo_informix, and pdo_sqlite PHP modules have been found and are ready for installation.

Enter the base URL for your machine, language defaults, and optionallythe Google maps key for your machine URL:

This image is a screen capture of a web browser showing the OpenAdmin Tool configuration parameters, including the default language to use, the directory for connections database, the BASEURL for links, where everything is installed, the number of seconds to pausewith a redirect, the Google map key, and how often to check server status (in seconds).

8. Start using OpenAdmin

Once installation is complete you can go to the main page (http://localhost/openadmin in this example), click Admin and start adding IDS 11.10 connections. Important: If you are using a version of CSDK prior to 3.00.TC1, there must be aSQLHOSTS entry on the Webserver machine for each connection you add (use setnet32 to add Informix servers to SQLHOSTS on Windows).

This image is a screen capture of the OpenAdmin tool login screen. The user is required to fill out the following fields: Informix Server, Host Name, Port, Username, and Password.

Once a connection is defined you can return to the main screen and connect:

This image is a screen capture of the main OpenAdmin Tool after a successful login.

The connection administration allows you to create groups of connections, and assign a password for each group, making it easier to administer a large number of instances.

Inge Halilovic, Guy Bowerman[Read More]

Support For Node Datablade

cheetahblog Tags:  node default datablade 5 Comments 6,781 Views
There is good news for those who were downloading the Node.1.0 DataBlade from the developer works and using it AS-IS. As per policy there is no support for AS-IS software downloaded from the developer works.

Node DataBlade creates an Informix DataBlade with a node opaque type data that addresses the problem of transitive closure/hierarchical data queries.

From IDS V11.10 onwards, Node.2.0 DataBlade (with some added functionality) is being bundled as one of the default DataBlades along with the server. That means moving forward Node.2.0 DataBlade (and subsequent higher versions) would be eligible for support.

Anup Nair[Read More]

How to monitor your SQL Shared Memory Caches

cheetahblog Tags:  performance cache 4,841 Views

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]


cheetahblog 3,056 Views
Welcome to the IDS Experts Blog, an international team blog authored by IBM software engineers. These experts from the Informix Technical Support and R&D labs will be posting about a variety of topics related to Informix and IDS. A popular topic over the next few months is likely to be the 11.10 release of Informix Dynamic Server currently in Open Beta.

If you have suggestions on articles you'd like to see from support or development engineers please add a comment to the blog. Your feedback to this blog goes directly to engineers working on Informix products.[Read More]