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:

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_demoowner name = informixext 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:

So you want to get the BTS datablade running on a database? Well hereis a step by step Quick Reference to get your database ready to use theBTS Datablade.

First you need to register the BTS blade in your database,this is done with the following method:

Next you need to create an external space for your BTSindex. This is accomplished by the following method:

Next you need to set up your instance so that the BTSVirtualProcessor is created. This is done by adding the following to the$ONCONFIG file:

VPCLASSbts,noyield,num=1

Please note that at this point you have two options to start your btsVP, the first is to cycle the instance (ie, onmode -ky folllowed byoninit), the second is via the onmode -p command (ie. onmode -p +1 bts)

Finally you need to create an index using the bts accessmethod. Below is an example of the code.

And now just to validate that the BTS blade is now up an running, let'srun quick test. We'll run the following query:

SELECT company FROM customer WHEREbts_contains(company, '%all');

And so now you should have the BTS set up and ready to run.

In our final Blog on the BTS we will go over some performance tuning,and how to troubleshoot some issues.

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:

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.

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

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:

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

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_FREQThe 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:

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.

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.

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

"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: '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.