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,937 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,988 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,321 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,832 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,180 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,691 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,648 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,816 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 ckp' 'onstat 6,985 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,541 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,838 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,132 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]