Informix Experts

BTSPart 2
BTSPart 1

So now that we have set up the BasicText Search Blade, or BTS forshort, the next question is what performance tuning tools areavailable, andhow do we primarily troubleshoot problems?

BTS comes with 4 functions in addition to the bts_contains function.They are:

• bts_tracefile :This functions lets you designate the location and name of you tracefiles.
• bts_tracelevel : This enables tracing at the session level for BTS calls
• bts_index_compact:  Think of this function as the Index Cleaner for the btsblade.
• bts_release: Allows you to skip using blademgr to find out what version of theblade you are using.

Performance Tuning:

The biggest performance tuning tool is bts_index_compact. From anadministrative standpoint, we treat the BTS indices the same way wetreat btreeindices, with one exception, we have no automated thread which goes outandclean the BTS indices, for btree indices we have btscanner threads.bts_index_compact is the BTS version of  what the btscannerthread does.So from a performance standpoint, you should frequently runbts_index_compactin order to decrease the size, both disk space and number of elements,and thusincrease performance.  Please note that you can make thisprocessautomated by creating your index with delete= 'immediate' in youstatement.This will eliminate the need for compacting the index, but will makealldeletes run longer. A better option would be to set up a function usingtheadmin api to periodically run bts_index_compact on your indices, thuscreatinga poor mans btscanner for bts, so to speak.

Another performance tuning tip is to make sure that you have createdyour extspaces on fast disk. If you create an ext space on an NFS mount you arebasically asking for horrible performance. A little bit about extspaces may bein order. External spaces, or extspaces, are exactly as they sound,they arespaces external to the database proper. What this means is that youcannot usenormal onstat's to monitor an extspace.

Troubleshooting BTS Issues:

So now you have run into a problem, maybe something so simple as notremembering where your extspace is actually located, maybe you aregettingbigger problems, in either case, this is when you need to enable BTStracing.This process is actually fairly straight forward, as BTS has provided 2functions to handle this. First you will call bts_tracefile, to setyour tracefile location, then you should call bts_tracelevel which will dumpinformationto the file you specified.

Let's say that you had just forgotten the syntax and location of yourbts. Thequickest way would be to run the following:

execute function bts_tracefile('/tmp/bts.log');
execute function bts_tracelevel(10);
select * from customer where bts_contains(company,'all~');

After that just read the file bts.log

Below is a snippet from the trace file generated from the above query:



Taking a quick look at this you can see what your extspace info is.

database name = stores_demo
owner name = informix
ext space name = bts1

You also know now that your extspace is located in/work1/informix/11.10.FC1/bts_idx/stores_demo/informix

A quick word on extspaces. Extspaces are spaces that are external tothe database;as such there is no onstat that monitors them. If you forget what thename ofyour ext space is or forget its location, the above method isessentially youronly option by which to find them.One other important note about extspaces, if you want to back them up, there is only one support method, and is as follows:

onmode -c block
External backup of external space
External backup of the instance
onmode -c unblock

What the above means is that rather than going through the above to safely backup your BTS indices you should just plan on rebuilding your BTS indices in the case of a restore.

One other consideration when troubleshooting BTS issues, BTS does notallow formultiple bts_contains in the predicate portion of an SQL Statement.

For example

select * from customer where bts_contains(company, "fred") orbts_contains(company, 'all')

would fail with the error:

(BTS22) - bts_error  bts_contains requires an index on thesearch column


This concludes my series on the bts blade.

Mark Jamison
[Read More]

In IDS 11.10, a DBA or user informix can create sysdbopen() and sysdbclose() procedures to be executed when adatabase is opened and closed. These procedures can be used to change the properties of a session without changingthe application that the session executes. Any statements that are valid in a UDR can be executed in these proceduresto change the session behavior.

A DBA or user informix can create the following procedures in a database:

• username.sysdbopen
• public.sysdbopen
• username.sysdbclose
• public.sysdbclose

where

username: Operating System User

Each time a user user1 opens a database using either a DATABASE or CONNECT TO statement, the database server executesuser1.sysdbopen() if such a procedure is defined. If not it will execute public.sysdbopen(). Each time a user user1 closes a database using either a CLOSE DATABASE or DISCONNECT statement, the database server executesuser1.sysdbclose() if such a procedures is defined. If not, it will execute public.sysdbclose(). sysdbclose() will beexecuted even if the application exits without an explicit CLOSE DATABASE or DISCONNECT statement because the serverdoes an implicit close of the current database for such cases.

The owner name is not ignored when you create sysdbopen() and sysdbclose() procedures in non-ANSI databases, so youcan create these procedures for specific users in non-ANSI databases.

The following procedure creates a table oltp_stat, sets the role to oltp and PDQ priority to 10 for user oltp_user ina database:

create procedure oltp_user.sysdbopen()
create table oltp_stat(userid int, connect_time datetime year to second);
set role to oltp;
set pdqpriority 10;
end procedure;

The following procedure creates a table public_stat, sets the role to others and the PDQ priority to 1 for the PUBLICgroup in a database.

create procedure public.sysdbopen()
create table public_stat(userid int, connect_time datetime year to second);
set role to others;
set pdqpriority 1;
end procedure

You can clean up any activities started by sysdbopen() in sysdbclose(). For example, if you had created some tables in sysdbopen(), you can drop those tables in sysdbclose().

The following procedures drop the tables created by the sysdbopen() procedures given above:

create procedure oltp_user.sysdbclose()
drop table oltp_stat;
end procedure;

create procedure public.sysdbclose()
drop table public_stat;
end procedure

A DBA or user informix can set the environment variable IFX_NODBPROC to any value, including 0, to prevent the execution of sysdbopen() and sysdbclose() procedures. When you set up sysdbopen() and sysdbclose() procedures, youcan set the environment variable IFX_NODBPROC and execute the procedures to test if the procedures work as expected. You need to unset the environment variable IFX_NODBPROC after testing.

For more information, see information on sysdbopen() and sysdbclose() in the IBM Informix Guide to SQL: Syntax

Suma Vinod[Read More]

"sysadmin" database is created in "root dbspace" at server initialization. This database is required for the Scheduler API and Remote Administration feature. Until B5 drop, there wasn't any way of moving the sysadmin database safely to any other dbspace. A new SQL Admin API command in B6 drop now simplifies this task by allowing "informix" user to drop and recreate this database to any other dbspace.

If it's determined that root dbspace does not have enough space for storing task properties and command history information, you could move the sysadmin database to a different dbspace by using the "reset sysadmin" SQL Administration API command. This command drops the sysadmin database from root dbspace and recreates it in the specified dbspace.

Here's an example to move the sysadmin database...

1. Make sure the following message has appeared in the online message log after server startup:

SCHAPI: Started 2 dbWorker threads.  

2. Let's create a special dbspace to store sysadmin... admindbs

database sysadmin;execute function task("create dbspace", "admindbs", "/vol/dbspaces/admindbs", "1 GB", "0");

...OR...

onspaces -c -d admindbs -p /vol/dbspaces/admindbs -s 1000000 -o 0

3. As user informix, run the following commands:

dbaccess sysadmin -execute function task("reset sysadmin", "admindbs"); 

The command will return the following message:

SCHAPI: 'sysadmin' database will be moved to 'admindbs'. See online message log.

The internal thread, bld_sysadmin (seen via onstat -g ath), waits up to five minutes to obtain exclusive access to the sysadmin database. The progress of the bld_sysadmin thread is logged in the online message log.

4. Our own session has sysadmin database open. So, terminate the dbaccess session with the "close database" statement.

On successful completion, the sysadmin database is dropped and recreated in the new dbspace. The dbScheduler and dbWorker threads are restarted automatically.

Mirav Kapadia[Read More]

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



Informix introduce two new configuration parameters to help onbar monitoring.

• BAR_PROGRESS_FREQ
• BAR_PERFORMANCE

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



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

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

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



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



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




BAR_PERFORMANCE


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



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



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



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

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

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



Sanjit Chakraborty

[Read More]

The installation and uninstallation applications allow to selectively add or remove components. To choose specific features during installation, you must choose a custom setup option during installation .


You can remove specific Dynamic Server features after both typical and custom installations, provided that the features are not required for your system's integrity and that they have not been used in your implementation.


You can also add or reinstall specific features after completing setup of a Dynamic Server instance without reinstalling the base server. The installation and uninstallation applications detect your implementation's setup to prompt you about the features accordingly.

UNIX and Linux-platform installation
=========================
The manifest file manifest.inf ($INFORMIXDIR/etc/manifest.inf) and installed files IIFfiles.installed($INFORMIXDIR/etc/IIFfiles.installed ) on sites using J/Foundation, and at IDS2000files($INFORMIXDIR/etc/IDS2000files) for sites not using J/Foundation are dynamic files that log installation activity of an IDS instance.
If any on the components is uninstalled the entry for that component is removed/deleted from the manifest.inf file .
These "log files" can help you quickly see what  features and component files are currently installed, as well as a history of such activity. Do not modify the content of these files.

WINDOWS-platform installation
======================
The manifest file manifest.inf (%INFORMIXDIR%\etc\manifest.inf) is a dynamic file that logs installation activity of an IDS instance. This "log file" can help you quickly see what features and component files are currently installed, as well as a history of such activity.

Do not modify the content of these files. 



Below are the snippets from the manifest.inf file
=========================================

IBM Corporation IBM Informix Dynamic Server
11.10.xC1

Installed Features
===================
Base Server
The core database server for basic DBA operations without optional extensions, libraries, or utilities
115.7 MB
Last installed:May 3, 2007

Informix Interface for Tivoli Storage Manager
For implementing XBSA functions that use Tivoli Storage Manager with ON-Bar
115 KB
Last installed:May 3, 2007

Informix Storage Manager
For managing external storage devices and media that contain backups
49.5 MB
Last installed:May 3, 2007

archecker Utility
For verifying backups and restoring portions of a database, a table, a portion of a table, or a set of tables
2.7 MB
Last installed:May 3, 2007

Rashmi Chawak



[Read More]

In IDS 11.10, you can configure session properties using sysdbopen() and sysdbclose() procedures, see Configuring Session Properties. Theseprocedures are stored in sysprocedures system catalog. A memory cache is maintained for these procedures to facilitatefaster searching of procedure identifiers. A new onstat option is available to print statistical information about thismemory cache. You can determine how many users are accessing a database, the sysdb procedures defined for public andother users from this information. A sample output is shown:

$ onstat -g cac sproc

IBM Informix Dynamic Server Version 11.10.FC1       -- On-Line -- Up 4 days 20:30:27 -- 38912 Kbytes

Sysdbproc Cache:     Number of lists             : 31

list#   refcnt  dbname          Public Procids  Username        User Procids    UsrList#                                (open,close)                    (open,close)-------------------------------------------------------------------------------------7       0       sysmaster         (-1, 0)16      1       testdb            (345, 346)                                                suma            (347, 348)      14                                                informix        (343, 344)      12218      0       sysutils          (-1, 0)21      0       sysuser           (-1, 0)




list#	List number to which the database name hashes
refcnt	Number of sessions accessing this database
dbname	Database name
Public Procids	Procedure ids of public.sysdbopen() and public.sysdbclose(). The value will be (-1,0) if there is no public or user defined sysdb procedures for that database
Username	User name of any users who have their own sysdbopen() and sysdbclose() procedures for that database
User procids	Procedures ids of sysdb procedures for a particular user in a database
UsrList#	A user hash list to which the user belongs

The number of lists is determined by the onconfig parameter DS_HASHSIZE. The maximum number of database entries for a list is determined by DS_POOLSIZE. This is a soft limit as the limit is checked only when you close a database. When you close a database, if the number of database entries for that list is greater than DS_POOLSIZE, IDS deletes all excess database entries in that list that has a reference count of 0.

Suma Vinod[Read More]

Beginning with IDS version 11.10, Informix introduced several new onstat commands to enhance server maintenance and monitoring. Database Administrator can take advantage of these new onstat commands to perform their regular activities.



Following are list of new onstat commands:

     Onstst Commands                Description   onstat -g his       Prints SQL statement tracing information using SQLTRACE                        configuration parameter. For more information on SQL tracing,                        see following articles:                       Tracing SQL in Cheetah Part 1                       Tracing SQL in Cheetah Part 2

   onstat -g ckp       Print checkpoint information and checkpoint related advisory.                       For more information on checkpoint advisory, see following                        article:                       Checkpoint Performance Advisory

   onstat -g pqs       Allows dynamically track a session's query plan. For more                        information see following article:                       onstat -g pqs                            onstat -g smx       Prints Server Multiplexer group information for servers using                        SMX. Server Multiplexer Group (SMX) is a communications interface                        that supports encrypted multiplexed network connections between                        servers in high availability environments. SMX provides a                        reliable, secure, high-performance communication mechanism                        between database server instances.

   onstat -g rss       Prints Remote Standalone Secondary (RSS) server information.                        The output of this command differs slightly depending on                        whether the command is run on the primary server or on the                        RS secondary server.

   onstat -g sds       Print Shared Disk Secondary (SDS) server information

   onstat -g ipl       Shows index page logging status information

   onstat -g cat       Prints information from the Enterprise Replication global catalog.                        The global catalog contains a summary of information about the                        defined servers, replicates, and replicate sets on each of the                        servers within the enterprise. 

   onstat -g cdr config                            Prints the settings of Enterprise Replication configuration                        parameters and environment variables that can be set with the                        CDR_ENV configuration parameter.

For a complete list of onstat commands, see the IBM Informix Administrator's Reference Guide.



Sanjit Chakraborty[Read More]

Earlier versions of Informix Dynamic Server (IDS) had restriction with C-style comment indicators ( /* ... */ ) in SQL statements, within the DB–Access operations.



Without the C-style comment indicator it was not compliance with SQL-99 standard and caused problems while migrate from other databases server.



Starting with IDS Version 11.10, restrictions with C-style comment are no longer in effect. Any of the following styles SQL comment indicators can be use in all DB–Access statements.

• Double hyphen ( -- ) complies with the ANSI/ISO standard for SQL
• Braces ( { } ) are an Informix extension to the ANSI/ISO standard
• C-style slash-and-asterisk ( /* . . . */ ) comply with the SQL-99 standard

The comment symbol indicator can be choosing depends on ANSI/ISO compliance requirements.



Comment symbol indicator can be use on the same line with the SQL statement. It can spans across single-line or multiple-line, but cannot be nested.



DB-Access will filter any comments before sending SQL statement to the database server.

Examples:  -- Double hyphen comment  SELECT * FROM orders;     -- Selects all columns and rows                            -- from the orders table

  { Comment using braces }  SELECT * FROM orders;     {  Selects all columns and rows                               from the orders table  }

  /* C-style comment */  SELECT * FROM orders;     /* Selects all columns and rows                               from the orders table */

Sanjit Chakraborty[Read More]