During the installation of IDS 11.50, Program Group folder is opened and Start Menu shortcuts are added. If, these actions were optional then it would make IDS more embeddable with other applications, which uses IDS in background. Customers who are embedding IDS as part of their software package usually desire near-complete invisibility.
This request was met by adding a new comma-line option “hidden” for instillation. This option will prevent creation of start menu shortcuts and suppress the Program Group folder from popping-up. Users can use this option by invoking the setup.exe (IDS 11.50 installation) from command-line and supplying “–hidden” option with the same command.
Previous version of JDBC 3.50 could not be installed on Windows 64-bit using 64-bit JRE. Installing JDBC 3.50 gave an error "Directory not writable" for all directories. Same error message was received, even after running all the process as administrator by turning UAC (User Account Control) off. Workaround for this problem was to use 32-bit JRE on Windows 64-bit to install JDBC 3.50.
This problem was solved in IDS 11.50. Now JDBC 3.50 uses newer version of Install Shield, which allows for JDBC 3.50 to be installed on 64-bit Windows using 64-bit JRE. Now, users no longer have to use 32-bit JRE to install JDBC 3.50 on Windows 64-bit. This makes users life easier, since users do not have to set up 32-bit JRE on 64-bit Windows.
Introduction:In most cases the Informix error codes are very good at explaining why there is a problem and how to fix it. Finderr(Error Message Utility), is the utility shipped with IBM Informix Dynamic Server and client products(CSDK and IConnect) that’s helps to check an error code and returns error messages corresponding to IBM Informix error numbers.
Problem:Finderr uses WinHelp. The Help for this program was created in Windows Help format, which was used in previous versions of Windows and it is not supported in the newer flavors of Windows Operating Systems like Vista or Windows 2008. Windows Vista and Windows Server 2008 are not shipped with Winhelp application. So you will get error messages popping up while executing finderr utility shipped with IDS 11.50 on Windows Server 2008 and Vista:
The GUI finderr program supplied with CSDK and IConnect also doesn't work on Windows Vista or Windows Server 2008. The execution of finderr utility in client products pops-up the following error messages.
Solution: The workaround is to download the Windows Help program (WinHlp32.exe) from the following Microsoft support Web site.
For Windows 2008: http://www.microsoft.com/downloads/details.aspx?FamilyID=0468fefd-b54f-4c57-8340-c6dd2ec20c0a&displaylang=en
For Vista: http://www.microsoft.com/downloads/details.aspx?familyid=6ebcfad9-d3f5-4365-
The problem has been fixed in IDS 11.50.xC3 and CSDK-3.50.xC3 releases. So you can always upgrade to these releases to get finderr utility working in newer flavors of Windows Operating System.
In IDS releases prior to 10.00.TC5, the Informix Dynamic Server(IDS) Windows Service was allowed to log on only as user informix. Launching the IDS installation program setup.exe with the -system command line argument (only) will install IDS and create a new instance running as the Windows Local System user, bypassing screens which prompt for informix user password(informix user does not get created when this option is used).
Starting with version 11.50, you can install IDS on Windows as the local system user account by selecting the Local System User option right on the IDS Server configuration Setup panel. This option is available only in custom mode of installation.
Typically this option provides the same privileges as the informix user account; however, it uses an internal account representing a pseudo-user that does not require a password. The local system account is used by the operating system and services running under Windows during the installation of Dynamic Server. The informix user is still created by default.
However user can choose not to create an informix user account at all, but we have to note that Enterprise Replication between Dynamic Server on UNIX and IDS on Windows Operating System will not work if informix user and Informix-Admin group is not present.Along with the “Start database server as Local System User” checkbox another checkbox “Do not create user informix account” is also provided on the same panel which is greyed out unless the user picks the System User option.
Some of the benefits of why people would want to do this:
if the machine has a password expiry policy and it is not convenient to change the password of the IDS service this will save having to do it
some security policies require that the informix user not be a member of the Administrators group. If the IDS service logs on as local system user the informix user no longer has to be an administrator.
Suppose you have an application which depends on IDS. Your business requirement is application must starts after a successful IDS memory initialization. Previously there was no way you can validate the return code of oninit process to make a decision whether or not IDS initialize successfully. So, if you have a script that automatically starts IDS and the application respectively, it possible the application may start even though IDS failed to initialize.
A new oninit option has introduced to IDS 11.50 that generate a return code. Based on the return code you can customize the script and automate startup process.
The 'oninit -w' command generates following return codes:
0 - when success
1 - when initialization fails or exceed the timeout value
The 'oninit -w' command forces IDS to wait until it successfully initializes before returning to a shell prompt. You can also provide an addition argument for timeout value with '-w' option. Without any timeout value with '-w' option, IDS will use the default value e.g. 10 minutes. If IDS cannot initialize within the timeout period, oninit generates return code 1 and writes following error message to the online.log file:
Warning: wait time expired
The syntax of new command as follows:
You can use the '-w' option with combination of any other oninit initialization options.
Couple of points to remember:
In a high-availability environment, you can only use the 'oninit -w' command on primary server; it is not valid on secondary servers.
The oninit command returns success when sysmaster, sysutils, sysuser and sysadmin are successfully created.
The value of locale variable, CLIENT_LOCALE or DB_LOCALE can be broken into 4 parts.
1 2 3 4 <language>_<territory>.<Code set name/Code set number>[@modifier] -------- -------- ----------------------------- --------
Conventional, I represent this as ll_tt.xxxx@xyz, where ...
ll ............ represents the Language
tt ........... represents the Territory, or cultural convention.
xxxx ....... represents the Code set Name or the Code set Number supported by the locale and
xyz ......... represents the Modifier. This is the only optional part in a locale value.
The modifier, sometimes refered as variant, modifies the cultural-convention settings that the language and territory settings imply. It usually indicates a special localized collating order that the locale supports.
Let us look at an example.
CLIENT_LOCALE = de_at.cp1252@euro, and CLIENT_LOCALE = de_at.1252@euro
Here, both CLIENT_LOCALE values represent the same locale.
1252 is the Code set number for Code set name, cp1252. We can specify either Code set name or the Code set number in a locale value.
- de ........... represents the German language - at ............ the territory, Austria - cp1252 ... the code set used for the encoding and - euro ....... the modifier used for the locale
So, this is German language locale, for Austria, using cp1252 encoding and euro modifier.
Now, to check if this locale file exists, where to lookup ?
All locale files reside under directory $INFORMIXDIR/gls/lc11
To lookup for locale files for language (ll) and territory (tt), we check under $INFORMIXDIR/gls/lc11/ll_tt directory.
In our example, to lookup for locale files for German language (de), for territory Austria (at), we will lookup $INFORMIXDIR/gls/lc11/de_at directory
Next, under the specified locale directory, look for files with name represented by hex value of the code set name/ code set number, along with modifier name if modifier is specified, with an extension .lco
In our example, hex value for Code set cp1252 is 04e4 and modifier euro is used. So, we will look for file 04e4euro.loc under directory $INFORMIXDIR/gls/lc11/de_at.
How and where to find the hex value for a Code set name ?
For any Code set name, its Code set number and hex value can be looked-up in file $INFORMIXDIR/gls/cm3/registry.
Let us find the hex value for Code set name Latin-3.
We can find the information in file
In the registry file ...
- first coulmn represents the code set name, - second column is code set number - third column is the hex value of the code set number, and - fourth column, is either blank or has comment about the code set.
Let us lookup for code set, Latin-3 in registry file and see what we find. We get the following value.
Latin-3 57346 0xe002 --------- ------------ ----------- -------------------------------- code set name code set number hex value in this case, there is no comment
Locale values are case in-sensitive.
DB_LOCALE = de_de.cp1252, DB_LOCALE = de_de.CP1252. Here, both locale values are valid, representing the same code set.
You can specify either code set name or code set number in a locale value, but you cannot use the hex value of the code set number.
DB_LOCALE = fr_ca.57372 or fr_ca.utf8, ........ both values are valid and they represent the same code set.
DB_LOCALE = de_de.cp1252 or de_de.1252 .... both values are valid and they represent the same code set
DB_LOCALE = de_de.04e4 ............... this in invalid. Code set's hex value cannot be used in a locale value.
If modifier is not specified in the locale variable, like say ...
CLIENT_LOCALE = de_at.cp1252
- to locate the locale file, look for .loc under the language_territory directory. In this case, we look for following file ...
If modifier is specified in the locale variable, like ...
CLIENT_LOCALE = de_at.cp1252@euro
- to locate the locale file, look for .lco file under language_territory directory. In this case, we look for following file ...
Code set name, its corresponding Code set number and hex value is specified in file
Locale Territory/ Country code and Language code can be looked up in file
Conventionally, for LOCALE variable having value ll_tt.xxxx[@xyz], following locale file should exist.
$INFORMIXDIR/gls/lc11/ll_tt/<hex value of xxxx>[xyz].lco
IDS 11.50 introduced a new installation wizard (installer), which makes life easier than in past releases to set up an instance to use a variety of database clients. In this section we will discuss how to set DRDA connection during installation process.
The installation wizard is extremely user friendly and easy to navigate. Options are available with installation wizard to configure a database server alias (DBSERVERALIASES) and a port for clients that use the DRDA protocol. You can easily setup this DRDA connection via a checkbox when creating the demonstration database server instance during installation. We will see some screenshots latter in this section to get a better understanding on DRDA configuration.
There are little differences between UNIX and Windows installation wizard.
Select option use the default configuration file to able to install database server with DRDA.
Do not select the option to customize the default configuration file, as this option does not allow setting DRDA.
By default the server installation includes DRDA setup.
You must select custom installation to exclude DRDA setup.
On UNIX platform you need to answer 'Yes' for question Do you want to create an IDS demonstration database server instance. Similarly, you need to choose option 2 - Use the default configuration file under demonstration database instance configuration. You may use default server name and server alias values or change those as per your requirement.
Following is a DRDA configuration screenshot on UNIX platform:
On Windows platform the typical installation process (default) automatically include the DRDA configuration. It will set the configuration parameter DBSERVERALIASE as "svc_drda". Custom installation process is required if you want to disable DRDA setup in demonstration server instance.
Following are some DRDA configuration screenshot on Windows platform:
The custom installation process provides controls on initialize the demonstration server. You can exclude the DRDA configuration, if you wish. By default DRDA is selected.
Things to notice if you enable a custom configuration file on windows platform, DRDA support is removed.
By default IDS will configure TCP/IP port 9089 and drtlitcp or drsoctcp connection protocol for DRDA configuration.
IDS 11.50 made a significant improvement in installation process. Started with IDS 11.50 informix introduced a new versatile installation wizard (installer), which can automatically creates a customized database server configuration file (ONCONFIG) suitable for your system environment and create a demonstration database server during installation process.
Part of the installation process installer captures inputs from user. Then evaluates the input values to ensure settings are valid, and it calculates several values for other configuration parameters based on hardware settings and database instance needs. You can use the Instance Configuration Wizard in GUI or console installation modes to use this new feature. However, make sure you choose the option “Creating Demonstration Database Server Instance” during installation process to automatically create the customized configuration file.
There are little differences between UNIX and Windows installation wizard.
Checked 'Yes' for Use create an IDS demonstration database server instance
Choose option for Customize the default configuration file
On Windows platform the Instance Configuration Wizard is only available with custom setup in GUI mode.
You must checked 'Demos' under select the feature you want to install
The 'Enable a custom configuration file' must checked also
Following are some restrictions that you need to remember during installation:
Installation directory must be empty
The root chunk file must be empty or not exists. Installer will create the file.
Parameters affected by the Instance Configuration Wizard are not available for silent installation.
During installation, Instance Configuration Wizard captured following input from user:
Database Server Name
Database Server Number
No. of central processing units (CPUs)
Memory: System RAM dedicated to the IDS server (in MB)
No. of online transaction clients
No. of decision support clients
Based on the above information installer will set following configuration parameters: ROOTPATH, ROOTSIZE, MSGPATH, DBSERVERNAME, DBSERVERALIASES, SERVERNUM, DRLOSTFOUND BAR_ACT_LOG, BAR_DEBUG_LOG, DUMPDIR, JVPJAVAHOME, JVPHOME, JVPPROPFILE, JVPLOGFILE, ALARMPROGRAM, SYSALARMPROGRAM, JVPCLASSPATH, BUFFERPOOL, VPCLASS
If the Instance Configuration Wizard encounters a problem while validating or calculating configuration parameters for customize configuration file, the configuration file is created with default, workable configuration parameters and an appropriate message displayed.
The customized configuration file will save as 'onconfig.<DBSERVERNAME>' in etc directory under <INFORMIXDIR>.
The "Creating Demonstration Database Server Instance" option with installation process will create a demo database instance. Once the installation process complete, installer generate two script files called profile_settings (korn shell) and profile_settings.csh (C shell) for setting environment variable for demo server instance in $INFORMIXDIR/demo/server (%INFORMIXDIR%\demo\server for Windows) directory.
Following is a screenshot of instance configuration wizard on UNIX platform (console installation mode):
You must choose '1 - Yes' for create an IDS demonstration database server instance and '3- Customize the default configuration file to suit your needs and hardware' in console installation mode.
Following are some screenshot of instance configuration wizard on Windows platform (GUI installation mode):
You must select 'Custom' in GUI mode
Make sure to checked 'Demos' for create an IDS demonstration database instance
Make sure 'enable a custom configuration file to suit your needs and hardware' also checked
An example of server configuration setup. You need to insert values for customize the database server.
Upgrading Informix Dynamic Server Version 10.00 to Versions 11.10 or 11.50:
When installing IDS 11.10 or 11.50, if you choose the option "Upgrade from the previous version", all the server binaries will be upgraded to the newer version(s) automatically.
Upgrading Informix Dynamic Server from Version 11.10 to 11.50:
When installing IDS 11.50 on Windows, direct upgrade from IDS 11.10 is not supported. If “Upgrade from the previous version” is selected the following message pops up. Installer stops beyond this point.
Since the support to install both 11.10 and 11.50 on the same machine exists, it is recommended to choose the option “Install into a default/different directory” (shown in the panel below) to install IDS 11.50. Note: This will not upgrade IDS 11.10 to IDS 11.50.
As part of providing a folder name to install the product, a completely new path must be supplied. If the folder selected already contains binaries from IDS 11.10, then the following message pops up.
User can respond to the question “Do you want to select another folder? “. Clicking “yes”, the installer returns to the destination panel where they can provide a different folder. Clicking No, the installer goes to finish panel
The recommended approach to upgrade to 11.50 is
Uninstall IDS 11.10 using "Retain database, but remove server binaries option"
Save all the Registry Keys in HKEY_LOCAL_MACHINE\SOFTWARE\Informix\OnLine\
Install the later version (IDS 11.50) to the same path.
Make sure that you do not initialize the server when installing.
Copy the ONCONFIG file to the target and set parameters that are new for the current release.
Bring the server up using Control Panel->Services or any other method without initializing.
Introduction:IDS 11.50 makes use of IBM Global Security Kit (GSKit) for data encryption and SSL communication. GSKit is deployed as part of IDS installation.
Due to the manner in which GSKit is deployed by the IDS installer, you may get a Windows installer error during IDS installation stating:
Error 1706. No valid source could be found for product IBM Informix Dynamic Server. The Windows installer cannot continue.
If GSKit 7 is not present on the machine, it will not be deployed during IDS installation. Furthermore, this error impacts the invisibility of IDS being deployed using silent mode of installation. If present on the machine, GSKit is deployed in [PROGRAM_FOLDER]\IBM\gsk7
After IDS installation, INFORMIXDIR (IDS installation directory) will contain the GSKit-related msi file "IBM Informix Dynamic Server.msi". Prior to any subsequent installation of IDS, clean entries related to the GSKit-related msi from the Microsoft installer database by running the following command:
Note:The command above will not uninstall IDS. "IBM Informix Dynamic Server.msi" does contain the IDS application files or IDS-related information. Therefore, uninstalling IDS is not an alternate workaround.
Below is are the repro steps for the problem:
On clean machine, install IDS, OR
On a machine with no IDS installation present, run the Windows installer clean up utility and clean up all reference to IBM Informix Dynamic Server.
After installation, uninstall IDS and clean up all the remnants of INFORMIXDIR
Install IDS in a separate directory from the previous installation.
The problem will be fixed in IDS 11.50.xC3 release.
Most of us fairly familiar with errno -28 (No space left on device) during Assertion Failure (AF), while Informix Dynamic Server (IDS) generates diagnostics data (AF file and shared memory dump). Diagnostics data are very critical to determine the root cause of failure. AF files are generally not too big, where as shared memory dumps often huge in size, almost same as the total memory size used by the IDS instance. The lack of disk space can cause partially dump of shared memory file, which add very little or no value to diagnose the failure.
In large IDS systems, the amount of space required to dump the shared memory is excessive because of gigantic sizes of the resident segment. Most of it contains is BUFFERPOOL information. Large size of the shared memory dump file not only create space issue, it difficult also for technical support to extract useful information in a timely manner.
The IDS version 11.50 provides some flexibility to control how much memory is written to a dump file. We can exclude the buffer pool information from resident segment to significantly reduce the shared memory dump file size. Configuration parameter DUMPSHMEM and onstat both provide some new options to control the shared memory dump size.
Use the DUMPSHMEM configuration parameter to automatically create a dump file during AF. Set DUMPSHMEM to 2 to create a shared memory dump that excludes the buffer pool. You can dynamically change the value of DUMPSHMEM with onmode -wm and onmode -wf. The DUMPSHMEM can take following values:
0 - Do not dump shared memory during AF 1 - Dump full shared memory (default) 2 - Dump shared memory without bufferpool (new option)
The 'onstat -o' command also allows to dump shared memory file on-demand. Use the new ‘nobuffs’ options with 'onstat -o' to generate shared memory dump without bufferpool. If you use 'onstat -o' without 'nobuffs' option, the DUMPSHMEM configuration parameter controls the content of shared memory file. The 0 or 1 configuration value will generate full shared memory dump file and 2 exclude buffer pool information.
All oncheck options works on the shared memory dump file without buffer pool, except options that access buffer information e.g. -b, -B, -P.
Typically onstat shows segments as “FACADE” while working with full shared memory, where as shared memory without buffer pool shows as "FAÇADE NOBUFFERS".
This article describes a customer’s experience with their poll thread configuration while upgrading from IDS 7.31.FD9 to IDS 10.00.FC6. This particular upgrade was related to their busiest IDS server running on an HP Superdome. Typically, one could observe upwards of at least 3000 short-lived soctcp connections on this system.
Original IDS 7.31 Configuration:
Key configuration settings that were active in the IDS 7.31 environment were initially used after upgrading to IDS 10.00:
23 CPU VPs
20 poll threads running on NET VPs
multiple server aliases
Optimal IDS 10.00 Configuration:
An optimal configuration was ultimately determined and incorporated the following configuration settings in the IDS 10.00 server:
23 CPU VPs
a handful (3-5) of poll threads running on NET VPs
enable new IDS 10.00 ONCONFIG parameter, FASTPOLL
multiple server aliases
Stress testing supportive of this optimal configuration was conducted on a 16-processor/32-core HP server using the latest IDS 7.31 and IDS 10.00 64 bit products. The testing involved a multi-threaded ESQL/C application that would spawn 3000 threads over 3 server aliases. Each thread would connect to the server, complete a small amount of read-only work and disconnect from the server 30 times. These 90,000 total connections mimicked the customer’s workload and considered poll threads running both on CPU VPs (inline) and on NET VPs against servers configured with 23 CPU VPs. The following chart shows results from the stress testing that were considered for the optimal customer configuration:
The maximum_number_of_sessions option defines the limit for the maximum number of sessions and can be set to a value in the range of 0 to 2,097,152. The value of 0 is the default and indicates that this feature is turned off.
The print_warnings option can be set to 1 or 0. When this value is set to 1, a warning message is written to the online.log when the number of sessions approaches the limit. These warning messages are not reported when the value is set to 0. The default is 0.
The limit imposed by LIMITNUMSESSIONS takes effect when the database server is shut down and restarted. It can also be started, modified or stopped dynamically by using onmode –wf or onmode –wm.
When the number of connections reaches this limit, new connection requests are rejected with the error -25571 Cannot create a user thread, until the number of connections fall below this limit. A message is reported to the online.log when this limit has been reached.
Database Server Administrators (DBSAs) are the exception to this limit. When the limit has been reached, a DBSA user will still be allowed to connect to IDS.
The following example specifies that you want a maximum of 100 sessions to connect to the server and that you would like a warning message printed to the online.log when the number of connected sessions approaches 100.
Introduction:Multiple installation of IBM Informix Dynamic Server is now enabled in IDS 11.50 xC2. With this, users will be able to install multiples copies of IDS on a single machine with the ability to create instance(s) per installation. For example, a user can have 11.50 xC1 and 11.50 xC2 co-existing on the same machine with instances related to each installation running on that machine.
With this new effort, a user that has an installation of IDS 11.50 can invoke setup.exe and will be presented with existing installation of IDS on the machine. The user can then make a choice between installing a new copy of IDS and maintaining an (one of the) existing copy(ies). If there is an existing installation of IDS 11.50 on a machine, a user that launches setup.exe in graphical mode will be presented a panel similar to the one below:
However, with an existing installation of IDS 11.50, launching setup.exe in silent mode will require a maintenance response file. Without a maintenance response file or with an installation response file, the installer will abort. To install a subsequent copy of IDS 11.50 in silent mode, the user should pass the ‘-multiple’ command line option to setup.exe.
In addition to the "-multiple" command line option, there are 2 command line options for maintenance namely "-path" and "-instnum". These 2 new command line options are used to launch maintenance mode for a specified installation. Since they perform the same functionality, they should be used interchangeably and not in conjunction with one another.
Usages of the newly implemented options:
This option can be used in conjunction with other installation command line options. E.g.
The user invokes setup.exe with the ‘-path’ option passing the installation path for which maintenance is required. This option can be used in conjunction with other maintenance command line options. E.g.
The user invokes setup.exe with the ‘-instnum’ option passing installation number for which maintenance is required. Note that each installation is tagged with an installation number in shortcut menu (except for the first installation which doesn’t have a tag). This option can be used in conjunction with other maintenance command line options. E.g.
The newest version of OpenAdmin Tool for IDS, Version 2.22, is available now! The latest features center on a Enterprise Replication (ER) monitoring and security.
ER Plug-in Version 1.1: Version 1.1 of OAT’s ER plug-in greatly enhances OAT’s Enterprise Replication monitoring capability.
The ER Routing Topology page has been transformed to allow monitoring of all nodes in the ER domain from a single page without having to drill-down on each node. Users can set thresholds for key ER statistics and then use the Routing Topology page to monitor alerts and profile data for each node in their domain. (Requires IDS server version 11.50xC2.)
The ER Node Details pages have been expanded to show errors for the current node or the entire ER domain (Errors tab) and to list current values of the ER configuration parameters (Configuration tab).
Secure SQLToolbox: OAT admins can now choose to turn on an additional level of security for the SQL Toolbox pages. If “Secure SQLToolbox” is turned on, OAT users will have to re-authenticate in order to view schema data or use the SQL Editor. This additional layer of security can be used to ensure that OAT users are not automatically allowed free access to databases or tables as the user informix.
HTTPS protects the OAT webserver from eavesdropping, tampering, and message forgery. It protects the OAT webserver from hackers who are trying to secretly listen or interfere with the OAT network. Enabling HTTPS will encrypt OAT clients’ messages before sending it to the OAT webserver. This prevents hackers from listening over the line and stealing sensitive information. Sometimes hackers will setup a fake OAT webserver and deceive OAT clients as to whom the real OAT host is. Enabling HTTPS also allow OAT clients to authenticate with the OAT host, so that hackers cannot deceive OAT clients with a fake OAT webserver.
Please note that HTTPS only encrypts communication between OAT webserver and OAT client. It does not encrypt communication between IDS server and the OAT webserver.
IBM® Informix® Dynamic Server, Version 9.4 and later, enables encryption of data between IDS server and OAT webserver using an encryption communication support module. You can find more information in the following link:
Enabling HTTPS in OAT will involve the following steps:
1. Replacing OAT’s Apache webserver with another mod_ssl enabled Apache webserver.
2. Creating an encryption key and a certificate for your new OAT webserver, so that OAT clients can authenticate your webserver based on your certificate.
3. Configure httpd.conf (the Apache configuration file) to enable HTTPS
Replacing OAT’s Apache Webserver with another mod_ssl enabled Apache Webserver
In order to use OAT with https, you will need to have an apache webserver, PHP with some extensions and the apache mod_ssl module. HTTPS functionality is provided by the apache mod_ssl module. Using the OAT automated installer will install an apache webserver and PHP with all the required PHP extensions, however apache webserver bundled with installer is NOT shipped with the mod_ssl module.
Therefore you have to install another apache webserver compiled with the mod_ssl module to replace OAT’s apache webserver. Then dynamically load OAT’s PHP apache handler (a file named libphp5.so, or php5apache2_2.dll on windows, which is the apache and php ‘glue’) to your new apache webserver.
Dynamically loading the mod_ssl module to OAT’s original apache webserver is not possible because the version of OAT’s original apache webserver is 2.2.4. Mod_ssl dynamic module only supports the older apache 1.3.x, It does not support OAT’s apache webserver.
On Linux and Mac OS X,
In order to compile Apache with mod_ssl support, you need to have OpenSSL installed. OpenSSL might have been installed on your Linux distribution already and you only need to find out the installation directory. Note that the OpenSSL binaries have to be 32-bit, because the binaries from the OAT automated installer are 32-bit. We suggest doing this only on a 32-bit machine so that there will be no architectural mismatch among the binaries.
If OpenSSL has not been installed already, you can download the latest source code release from here:
Then you have to compile the OpenSSL source code. Note that you have to compile 32-bit OpenSSL binaries because the binaries from the OAT automated installer are 32-bit. This can be done by setting CFLAGS to be –m32. Use the following commands:
cd /path/to/openssl/source/code export CFLAGS=-m32 ./config --prefix=/openssl/installation/directory/ --openssldir=/openssl/installation/directory/ make make install
Stop OAT’s apache webserver by running the /oat/installation/directory/StopApache script. Rename OAT’s Apache_2.2.4 directory to Apache_2.2.4_noSSL. This is a will serve as a backup copy of the apache binaries. You will also need to use some configuration files from this backup Apache directory in later steps. Do make a copy.
Then you have to compile the latest Apache with mod_ssl support. Download the latest Apache source code and compile with the following commands. Note that you have to compile 32-bit Apache binaries because the binaries from the OAT automated installer are 32-bit. This can be done by setting CFLAGS to be –m32. The compilation prefix should match the old OAT apache’s directory (/oat/installation/directory/Apache_2.2.4/)
cd /path/to/apache/source/code export CFLAGS=-m32 ./configure --prefix= /oat/installation/directory/Apache_2.2.4/ --enable-so --enable-ssl --with-ssl= /openssl/installation/directory/ make make install
Right now Apache should be installed with mod_ssl support. Use the following commands to check if mod_ssl is enabled in Apache.
cd /oat/installation/directory/Apache_2.2.4/bin/ ./httpd –M (Here you will see a list of Apache modules. See if the SSL module is on the list)
Then you have to change the Apache configurations file to load OAT’s PHP Apache handler (The PHP and Apache ‘glue’). Edit the Apache configuration file (/oat/installation/directory/Apache_2.2.4/conf/httpd.conf). Add the following lines. Uncomment if these lines exist but are commented out.
Search for the line “Listen 80” in the httpd.conf file. This indicates the port number that the OAT webserver should run on. You should use the same port number as OAT’s original Apache webserver that comes with OAT installer.
Search for the line “ServerName www.example.com:80” in the httpd.conf file. This indicates the name and the port that the server uses to identify itself. You should use the same ServerName as the original non-SSL Apache webserver that comes with OAT installer.
Search for the line “DirectoryIndex index.html” in the httpd.conf file. This sets the files that Apache will serve if a directory is requested. Change this line to “DirectoryIndex index.html index.php”
Copy the file /oat/installation/directory/Apache_2.2.4_noSSL/bin/envvars to /oat/installation/directory/Apache_2.2.4/bin/envvars. Apache will read this file to setup the Apache environment variables for OAT to run.
Copy the entire directory /oat/installation/directory/Apache_2.2.4_noSSL/htdocs/openadmin/ to /oat/installation/directory/Apache_2.2.4/htdocs/openadmin/. All the OAT source code resides in this directory.
Run the following commands to make sure that the PHP Apache handler is properly loaded.
cd /oat/installation/directory/Apache_2.2.4/bin/ ./httpd –M (Here you will see a list of Apache modules. See if the php5 module is on the list)
Right now your new webserver should be properly setup for OAT. You may start your webserver by running /oat/installation/directory/StartApache and visit OAT using your web browser.
Note that this server has mod_ssl enabled but HTTPS is not switched on yet. A few more steps are needed to enable HTTPS in the following sections.
First we have to download and install Openssl from the following website:
Stop the OAT Apache webserver. There should be a OpenAdmin shortcut in the start menu. You should be able to stop the OAT Apache webserver from there. Make sure that the Apache Monitor is not running on your system tray.
Rename OAT’s Apache_2.2.4 directory to Apache_2.2.4_noSSL. This is a will serve as a backup copy of the apache binaries. You will also need to use some configuration files from this Apache directory in later steps. Do make a copy.
Run the Apache MSI installer. Do a typical install and choose the installation directory to be /oat/installation/directory/Apache_2.2.4
Edit the Apache configuration file (/oat/installation/directory/Apache_2.2.4/conf/httpd.conf). Add or uncomment the following lines in the httpd.conf file:
Search for the line “Listen 80” (or “Listen 8080”) in the httpd.conf file. This indicates the port number that the OAT webserver should run on. You should use the same port number as OAT’s original Apache webserver that comes with OAT installer.
Search for the line “ServerName www.example.com:80” in the httpd.conf file. This indicates the name and the port that the server uses to identify itself. You should use the same ServerName as OAT’s original Apache webserver that comes with OAT installer.
Search for the line “DirectoryIndex index.html” in the httpd.conf file. This sets the files that Apache will serve if a directory is requested. Change this line to “DirectoryIndex index.html index.php”
Search for the line “setenv INFORMIXDIR” in the OAT’s original Apache’s configuration file (c:\oat\installation\dir\Apache_2.2.4_noSSL\conf\httpd.conf). This line sets the INFORMIXDIR variable in the Apache environment for OAT. This has NOT been set in your new Apache webserver yet. Copy this line to your new Apache webserver’s httpd.conf file. You can put this at the end of the httpd.conf file.
Copy the entire directory c:\oat\installation\dir\Apache_2.2.4_noSSL\htdocs\openadmin\ to c:\oat\installation\dir\Apache_2.2.4\htdocs\openadmin\. All the OAT source code resides in this directory.
Run the following commands in a command prompt to make sure that the PHP Apache handler and the mod_ssl modules are properly loaded.
cd c:\oat\installation\dir\Apache_2.2.4\bin\ httpd.exe –M (Here you will see a list of Apache modules. See if the php5 module and the ssl_module are on the list)
Right now your new webserver should be properly setup for OAT. You may now click on ‘Start Apache Service for OpenAdmin’ in OpenAdmin Menu under the Start Menu. You should be able to access OAT using your web browser.
Note that this server has mod_ssl enabled but HTTPS is not switched on yet. A few more steps are needed to enable HTTPS in the following sections.
Creating an Encryption Key and a Certificate for your OAT Webserver
Keys are used in encryption and decryption. They usually come in pairs, the public key and private key. Public keys are used to encrypt messages and private keys are used to decrypt messages. The message encrypted by a public key can only be decrypted by its associated private key.
A HTTPS enabled webserver will have its own pair of public and private key. The webserver will make its public key available to all clients. But nobody else except the webserver will know its private key. Thus all clients will be able to encrypt messages, but only the webserver can decrypt the message. If a client wants to send encrypted messages to the webserver, he will encrypt the message with the public key provided by the webserver and then send out the message. The webserver will use its secret private key to decrypt the client’s message. Hackers who are trying to listen over the network and steal the client’s message will not be able to decrypt the client’s message, because the hacker does not have the private key.
A certificate is a document authenticating a person to be whom he claims to be. A HTTPS enabled webserver will have its certificate, signed by a trusted certificate authority, to authenticate itself to be the real webserver that the clients are talking to. Before a client talks to the webserver, he will be prompted to view and accept the webserver’s certificate. To the client can make sure that the webserver’s certificate is signed by a trusted certificate authority before proceeding with the communication. This prevents hackers from setting up fake webservers to deceive clients.
Once a webserver is HTTPS enabled, clients get to choose whether they want to do a normal connection to the webserver or a secure connection to the webserver. If clients want to do a normal connection, they will type “http://webserver_url” in their web browser. If clients want to do a secure connection, they will type “https://webserver_url”. In a secure connection, the webserver will send the client its certificate and its public key. The client will be prompted to view and accept the webserver’s certificate before the webpage is loaded, so that the client can be sure that the webserver is not a fake webserver created by hackers to deceive you. Once the client accepts the certificate, the client’s web browser will use the public key that it received from the webserver to encrypt communication. Only the webserver has the associated private key, thus only the webserver can decrypt the client’s encrypted communication. Hackers cannot secretly listen and decrypt the communication.
To generate private/public key pairs and the certificate, we use the openssl executable. You should be able to find it under the bin directory of your openssl installation.
To generate a private key, use the following command:
openssl genrsa -des3 -out privkey.pem 2048
The private key will be stored in the privkey.pem file. Store this file in a secure location because this is the webserver’s secret decryption key. This file will be used to generate the associated public key. When we generate the certificate, it will look for this file, it will generate its associated public key and include the public key in the certificate.
To generate a certificate, we create need to create a certificate signing request, and send the certificate signing request to a trusted certificate authority (Such as VeriSign). The authority will then issue you a certificate. Use the following command to generate a certificate request. For more information about the process of signing certificate requests, contact your certificate authority.
openssl req -new -key privkey.pem -out cert.csr
If you don’t want to deal with another certificate authority and you just want to create a certificate for yourself, you can create a self-signed certificate. Note that this is not the recommended way of creating a certificate.
Openssl will prompt you to enter your personal information. After that the certificate will be stored in the cacert.pem file. This file will be displayed to web clients to verify your identity. It will also include the public key for web clients, thus they can start encrypting communication.
Openssl will also prompt you to enter a pass phrase. This is an extra layer of security. You will need to enter your pass phrase when you start your webserver.
For more information about encryption keys, please consult the OpenSSL documentations:
Configure httpd.conf (the Apache configuration file) to enable HTTPS
Edit the apache configuration file.The file should be /oat/installation/directory/Apache_2.2.4/conf/httpd.conf
Search for the following line:
This line is commented out by default. Uncomment it so that httpd.conf will include the apache ssl configuration file.
Then edit the apache ssl configuration file.The file should be /oat/installation/directory/Apache_2.2.4/conf/extra/httpd-ssl.conf
HTTPS will require one more ssl port. By default, the ssl port number is set to be 443. Make sure this port is available. If you wish to change this port, edit the Listen directive and the Virtual Host section of the httpd-ssl.conf file.
Search for the ‘SSLCertificateKeyFile’ directive and the ‘SSLCertificateFile’ directive in the httpd-ssl.conf file. These two directives indicate the location of your private key file and the certificate file. Make sure that they point to the privkey.pem and the cacert.pem created in the previous section.
Search for the ‘SSLCipherSuite’ directive. This directive indicates the ciphers for your HTTPS webserver. By default the HTTPS webserver will accept all encryption ciphers. If you wish to accept only the seven strongest ciphers, edit the directive as follow, or else you can keep the default configuration:
Your OAT webserver should be secured with HTTPS right now. Restart your webserver and launch OAT with your web browser. Instead of using http://hostname:portnumber/openadmin, try using https://hostname:ssl_portnumber/openadmin. You will be prompted to view and accept OAT webserver’s certificate before the OAT login page is launched.
Note: On Linux, you can restart your webserver by running the StopApache script and then the StartApache script in the OAT installation directory. You will be prompt to enter the pass phrase before you can start you webserver.
On Windows, you have to restart your webserver with the command prompt. Starting the webserver with Apachemonitor.exe or with the start menu shortcuts doesn’t work because they do not support pass phrases. Use the following command to restart your webserver.
httpd –k restart
If you wish to use Apachemonitor.exe or with the start menu shortcuts to control your webserver, you have to disable pass phrases. Note that this reduces the level of security.
Run the following command:
cd c:/openssl/installation/directory/bin/ openssl rsa -in privkey.pem -out privkey_nopassphrase.pem
Now an unencrypted copy of your private key will be stored in the privkey_nopassphrase.pem file. Edit your httpd-ssl.conf file SSLCertificateKeyFile directive to use the privkey_nopassphrase.pem file instead of the privkey.pem file. Then you will not be prompted to enter a pass phrase when starting your webserver. Apachemonitor.exe and the start menu shortcuts should work now.
OAT 2.21 has incorporated IDS Enterprise Replication monitoring, a plugin manager to allow customization of OAT functionality and an automated installer on Mac OS X. OpenAdmin Tool for IDS (OAT) is a PHP-based Web browser administration tool for IDS 11 and IDS 11.5 that provides the ability to administer multiple database server instances from a single location. OAT makes administration easy by allowing you to drill down on resource usage and events, view query performance statistics, and much more. And since the tool is written in PHP and available as open source, you can customize it with your own business logic and installation requirements.
New feature highlights of OpenAdmin Tool for IDS version 2.21 include:
Mac OS X Automated Installer: Automatically setup Apache, PHP, I-Connect and OAT on Mac OS X. For users who want to use or install their own Apache/PHP configurations, OAT is also released as a stand-alone package.
IDS Enterprise Replication Monitoring: Monitors Spool Disk usage, Send and Receive queues, Receiving/Apply statistics, Routing Topology, node details and much more. OAT Enterprise Replication is provided as a separate plugin. The latest automated installer will install the ER plugin for you, you can also manually place the ER plugin zip file under the OAT plugin_install directory and install it with the plugin manager (requires php zip extension).
Plugin manager: A simple way to customize OAT functionality. You can download customized OAT plugins and install it with the plugin manager. Sample plugin code is also provided and you can follow the sample in creating your own plugins. Note that the plugin manager requires the php zip extension. The latest OAT2.21 automated installer includes the php zip extension, but OAT2.20 installer does not.
In some cases it can be useful to have different copies ofsame versions of CSDK installed in the same machine, though this is not anofficially recommended approach by IBM technical support. This documentidentifies the most common problems when using different copies of same versionsof CSDK on the same machine and how to solve them. The first problem you willencounter if you try to install another copy of the same version of CSDK iswith the Windows Installer. By design, the Windows Installer detects if youhave a pre-existing installation of CSDK and it gives you the options ofModify, Repair or Remove.
Windows keeps a database of products installed on themachine. Every time you install a product using the Windows Installer a new keyis added to the registry with specific information about the product, installmedia location and options used for the install.
This is the database used when you run the “Add or RemovePrograms” applet from the “Control Panel”
When you launch the install process for a product, WindowsInstaller checks if there is already an entry in the Uninstall registry keywith the same id, if so it assumes that the product is already installed andlaunches the installer in Maintenance mode (with options Modify, Repair andRemove).
Different major versions of CSDK have unique productid’s, this means you can easily install CSDK 2.90.TC1 and CSDK 3.00.TC1, but ifyou try to install two releases of the same minor version (e.g.:3.00.TC1 and 3.00.TC3) the installer would not allow it. Currently the Windowsinstaller does support single installation of various CSDK versions indifferent locations on the same host.
To overcome this design restriction, you can use a Microsofttool like Windows Installer CleanUp to remove theRegistry entry corresponding to the version. Note that this will not uninstallthe binaries; it would only remove the entry from the Uninstall database. Thismay allow us to reinstall the product.
Due to these shortcomings sometimes there can be a need tobypass the Informix Client SDK or I-Connect installation process and copy theInformix library and API files directly to a machine. Though the officiallyrecommended and supported approach is to use the supplied CSDK/I-Connectinstaller, these instructions are provided as an alternative approach involvingcopying files for scenarios where using the installer is not possible.
CSDK 3.x is a bundle containing the followingapplication-programming interfaces:
IBM Informix Object Interface for C++ IBM Informix ESQL/C IBM Informix ODBC Driver IBM Informix OLE DB Provider IBM Informix .NET Provider LIBMI Client API
While some of these components can easily be used andredistributed without the complete CSDK bundle, there are some other componentswhich are more challenging to redistribute due to the way they interact withthe Windows operating system. APIs such as the ODBC driver and the OLE DBprovider depend on additional CSDK libraries to work. These libraries must beaccessible to your application.
In most cases, Windows searches in the directories includedin the PATH variable to load these libraries. One of the main problems whensupporting multiple versions of CSDK is mixing libraries between versions(e.g.: .NET provider from a newer version loading the ODBC library from an oldversion). In the same way as Windows uses the PATH variable to search for DLLs,Informix components use the INFORMIXDIR directory to load additional resourcessuch as GLS conversion files or message files.
The primary rule when using multiple versions is to have theINFORMIXDIR and PATH environment variables pointed to the directory containingthe version you want to use. You can accomplish this creating a batch file(e.g.: .BAT or .CMD file) to start your application or running the applicationas a user which has different environment settings.
Here is an example:
- - test.cmd - - - - set INFORMIXDIR=D:\infx\CSDK300TC1 set PATH=D:\infx\CSDK300TC1\bin;%PATH% test.exe - - - test.cmd- - - -
Interface for C++, ESQL/C and LIBMI
If you want to use the Object Interface for C++, ESQL/C orLIBMI you can follow the steps in following IDS Experts article: Redistributing IBM Informix Client products.
You will need to ensure that both the PATH and INFORMIXDIRvariables are pointing to the version of the CSDK installation folder you wantto use.
Using different versions of the IBM Informix ODBC driver canbe more complex. Any ODBC driver on a Windows machine must have a unique name.In the case of CSDK 3.50 and 3.00 the name for the ODBC driver is “IBM INFORMIXODBC DRIVER”. Windows keeps the list of installed drivers in the ODBCINST.INIregistry key.
Note that in previous versions of the CSDK package the namefor the ODBC driver was different (e.g.: IBM INFORMIX 3.82 32 BIT).
When you create a DSN using the ODBC Data SourceAdministrator tool (odbcad32.exe)
An entry is created in the registry with all the values usedin your DSN and the full path of the ODBC library used to create the DSN.
Windows uses the directory in the “Driver” registry key toload the ODBC library. You can use different versions of the ODBC driver bymanually changing the directory in the “Driver” key.
Even if you can update this key from the console or a shellscript before your application starts, the best approach is having a differentDSN depending on the driver you want to use, or use a relative path (e.g.:“.\iclit09b.dll”) and only rely on the Windows DLL loader mechanism.
The ODBC Data Source Administrator tool always loads theODBC library specified in the “ODBCINST.INI\%DRIVERNAME%\Setup” key, even if the DSN entry you are modifying has a differentpath in the “Driver” key.
For other APIs, the values for the variables PATH andINFORMIXDIR need to be changed depending of which DSN you want to use.
OLE DB Provider
The OLE DB provider library is called Ifxoledbc.dll. Windowsuniquely identifies an OLE DB provider based on the Class id (CLSID) and theprovider name (e.g.: Ifxoledbc). In the CSDK install process the OLE DBprovider is automatically registered in the Windows registry. At any point youcan manually register a different version of the provider using theREGSVR32.EXE Microsoft tool.
This tool creates the needed keys in the Windows registry soyou can load the OLE DB provider using the “Ifxolebdc” name.
There are two places where the full path of the OLE DBlibrary is stored, one for the “Ifxoledbc” provider and a second for the “IfxoledbcErrorLookup”.
Note:the CLSID could change with different versions
The registry key that contains the path for the OLE DBlibrary is “InProcServer32”.
The best approach to use different versions of theIfxoledbc.dll library is to use a relative path rather than the full path…
Remember to update both keys with the desired value (Ifxoledbcand IfxoledbErrorLookup). When an application wants to load the “Ifxoledbc”provider, it would follow Windows operating system rules to search for thelibrary. You should point your PATH and INFORMIXDIR to the version you want touse.
Further information related to the DLL Search Order could befound in the following link.
Dynamic-Link Library Search Order
Windows has a central place for .NET assemblies called theGlobal Assembly Cache or GAC. During the CSDK install process the IBM Informix.NET Provider is installed in the GAC with the unique name (strong name) “IBM.Data.Informix”.
You can have multiple versions of the .NET Provider if theassembly version is different.
The assembly version for CSDK 2.81 and CSDK 2.90 is188.8.131.52, and for CSDK 3.00 and CSDK 3.50 the version is 184.108.40.206. (Note that220.127.116.11 and 18.104.22.168 correspond to the IBM Data Server Provider.)
The assembly version for the .NET provider in minor releases(e.g.: 3.00.TC1, 3.00.TC3, etc) is the same, which means that only one versionof the library could be registered in the global cache.
To be able to use more than one .NET provider, first youneed to uninstall the IBM.Data.Informix assembly from the GAC. To do this youcan use the “gacutil.exe” with the “/u” flag for uninstall, or use the Explorercontext menu…
When a .NET assembly is not in the global cache the .NETruntime would try to load the class from the same directory as the executable.(You can find more information about the assembly loading in the followinglink: How the Runtime Locates Assemblies.)
Copy the .NET Provider (IBM.Data.Informix.dll) to thedirectory where your application would start; this would allow you to usedifferent versions of the .NET provider.
Alternatively you can specify the location of the assemblyat runtime using the Assembly.LoadFile method and the AssemblyResolveevent. Further information on this topic can be found in the following MSDNlinks: Assembly.LoadFrom and AppDomain.AssemblyResolve
Before contacting IBM technical support
The officially recommended approach is to uninstall theprevious version of CSDK before installing the new one. If you need to rundifferent versions of any of the CSDK components on the same machine, youshould be aware of the potential issues.
Always check that the versions of the libraries loaded byyour application belong to the same CSDK versions. (You can use a tool likeTLIST.EXE from Microsoft to find which libraries your process is using) - Debugging Tools for Windows
Depending on your existing Database Server setup, you might be able to upgrade directly to the current version by basically installing the product in a new directory, copying a few configuration files, and starting the new server to convert your database data. You can upgrade directly from any of the following products: Dynamic Server Version 11.10, 10.00, 9.40, 9.30, 9.21, or 7.31.
Upgrading is an in-place migration method that uses your existing test and production hardware. The operating system on those machines must be supported by Dynamic Server Version 11.50. Also, you must have enough space for the system database data conversion.
Upgrading consists of these steps:
1. Prepare your system. That includes removing outstanding in-place alters, closing all transactions, verifying the integrity of the data with oncheck, and performing a level-0 backup. If you are using Enterprise Replication or High Availability Data Replication, stop replication.
2. Install the new product on the machine. Important: Do not install it over the existing product.
3. Copy the ONCONFIG file to the target and set parameters that are new for the current release.
4. Start the Dynamic Server Version 11.50 instance. The database data is automatically converted.
5. Run UPDATE STATISTICS MEDIUM for non-leading index columns, then run UPDATE STATISTICS FOR PROCEDURE.
This type of migration minimizes the risk of introducing errors. You can always revert from the new server to the old one. In the event of a problem during reversion, you can restore the level-0 backup.
This method works perfectly on Windows thought it is not documented well.
1. When installing IDS 11.50 on Windows, choose the option "Install to a default/different directory".
2. Make sure that you do not initialize the server when installing.
3. Copy the ONCONFIG file to the target and set parameters that are new for the current release.
4. Bring the server up using Control Panel->Services or any other method without initializing.
5. Monitor the online.log for the Conversion Successful message.
Once the upgrade has completed successfully, you can remove the old instance. When you run the uninstaller make sure that you select "Retains all databases, but removes server binaries".
If for some reason, you like to go revert to the previous instance, restore it from the level 0 backup.
The only time this will not work on Windows is if you are upgrading from a 11.10.UC1 version to another 11.10 version say 11.10.UC2.
OpenAdmin Tool for IDS has been greatly enhanced in version 2.20 with a completely redesigned user interface, a new automated installer, and lots of new IDS admin functionality. OpenAdmin Tool for IDS (OAT) is a PHP-based Web browser administration tool for IDS 11 and IDS 11.5 that provides the ability to administer multiple database server instances from a single location. OAT makes administration easy by allowing you to drill down on resource usage and events, view query performance statistics, and much more. And since the tool is written in PHP and available as open source, you can customize it with your own business logic and installation requirements.
New feature highlights of OpenAdmin Tool for IDS version 2.20 include:
Automated Installer: For users who want an easy, hassle-free install, there is a new automated GUI installer that will install and configure Apache, PHP, I-Connect, and OAT. For users who want to use or install their own Apache/PHP configurations, OAT is also released as a stand-alone package.
All-New Look: OAT version 2.20 has a completely new and improved UI
Automated Update Statistics: Configure rules and define a schedule to have IDS automatically maintain your database server's statistics
Onconfig Recommendations: Get recommendations for your onconfig settings that are tuned to your specific database server instance; modify dynamic onconfig parameters directly through OAT
Enhanced Mach11 Support: Including a Connection Manager interface, an SDS Setup Wizard, and the ability to start and stop Mach11 servers remotely
Historical Performance Graphs: View and monitor historical trends of key performance measurements
Task Scheduler Wizard: Use OAT’s new wizard to define new tasks in the scheduler
Read-Only Access: Define groups in OAT that can monitor IDS database servers but not perform administrative tasks
And the list goes on…. Virtual Processor Administration, System Integrity Checks, a Privileges Manager, environment variable support on OAT connections, the ability to kill database sessions remotely through OAT, and more!
Scope: This article covers redistributing ESQL/C based demos and application. The steps required to redistribute other Informix client applications by copying files are being investigated.
Depending on how you deploy your Informix applications there is sometimes a need to bypass the Informix Client SDK or I-Connect installation process and copy the Informix library and API files directly to a target computer. Though the officially recommended and supported approach is to use the supplied CSDK/I-Connect installer, these instructions are provided as an alternative approach involving copying files for scenarios where using the installer is not possible. This article demonstrates how and where to copy all the required CSDK files and Microsoft Windows DLLs to a target computer in order to deploy applications.
Note that while this is not an officially recommended approach, these instructions have been tested by IBM and demonstrated to work. If you encounter problems with this method and need to talk to IBM technical support you may be asked to try installing via the installer to rule out other problems with your configuration.
Install Client SDK 3.50 or I-Connect on your Windows development computer. After successful installation, verify that all the shortcuts in the programs groups are created and registry keys are updated.
Make a copy of the entire CSDK installation folder (INFORMIXDIR) and transfer those to the target computer (For example by zipping the files and unzipping on the target computer). Choose any location on target computer for copying files for example c:\informix.
Copy the required Microsoft Windows runtime DLLs. Since the Informix product is not being installed via the regular installer the required runtime DLLs may not be present on the target computer. As a result applications such as setnet32.exe, ilogin.exe and finderr may not run correctly.
If a manifest is present in your application but a required Visual C++ library is not installed in the WinSxS folder, you may get one of the following error messages depending on the version of Windows on which you try to run your application:
The application failed to initialize properly (0xc0000135).
This application has failed to start because the application configuration is incorrect. Reinstalling application may fix this problem.
Alternative approach: If your deployment requirements prevent you from installing the Visual C++ Redistributable Package directly, from the development computer, copy the %WINDIR%\WinSxS\x86_Microsoft.VC80.CRT_1fc8b3b9a1e18e3b_8.0.50727.762_x-ww_6b128700 directory to same location on the target computer. (Create the same directory structure on the target computer as the development computer if it does not exist.)
Also copy the policy files from the development computer %WINDIR%\WinSxS\Policies\x86_policy.8.0.Microsoft.VC80.CRT_1fc8b3b9a1e18e3b_x-ww_77c24773 to the same location on target computer. (Again, create the same directory structure on target computer as the development computer if it doesn’t exist.)
Note: This workaround is only applicable to Microsoft Visual C++ 2005 SP1 runtime components. If later versions of the Client SDK are built with a later version of Visual Studio then the corresponding version of the runtime components would be required. Check your release notes to see which version of Visual Studio is required.
Running ESQL/C demos:
If you do not have the required Visual C++ libraries installed in the WinSxS folder then while running the demo1 program the following error pops up.
Once you install Visual C++ libraries or copy the runtime DLLs and policy files in C:\WINDOWS\WinSxS folder you should see the ESQL/C demos successfully as shown below.
The Open Admin Tool (OAT) is an open source web-basedadministration tool for Informix Dynamic Server (IDS). It is a web consolewhere users can manage dbspaces, schedule tasks and monitorMACH 11 clusters etc. through their web browsers.
OAT is a web console that is hosted on a single web server.Other machines that have access to the web server’s network can simply open upa web browser, type in the web server machine’s URL, and they can use OAT tomanage their database servers.
In order to host the Open Admin Tool website, the followingprerequisites have to be setup:
Informix Client SDK or Informix Connect
A web server, such as Apache 2.0.
PHP 5.2.4 dynamically loaded as an Apache 2.0 module. It also has to have the following extensions enabled
Gd(requires libpng and libjpeg libraries)
Openssl(requires libssl and libcrypto libraries)
Libxml(requires libxml2 libraries)
The Open Admin Tool can be hosted on any platforms that haveall of the above setup.
This article will provide the steps on how to setup OAT on aMac OS X Platform.
Setting up Open Admin Tool (OAT) on Mac OS
First step is to install Informix Client SDK for Mac OS.
The Mac OS will already have an Apache web server installed.We can use the Apache web server that comes with Mac OS. We do not need toinstall other web servers.
The Mac OS will also have an installed PHP,however the PHP that comes with Mac OS does not have all the extensions thatOAT requires. We cannot use the PHP that comes with Mac OS. We have to compilePHP binaries from source.
Our goal here is to build our own PHP binaries anddynamically load it to the Apache web server that comes with Mac OS.
Compiling PHP on Mac OS
Before starting to compile PHP, make sure that you havelibpng, libjpeg, openssl and libxml2 installed into /usr/local/ or /usr/. Some PHP extensions require these libraries in orderto compile.
Obtain the latest PHP source code from php.net (http://www.php.net/downloads.php).Decompress the source code package and issue the following commands.
The “configure” command will generate a makefile specific toyour machine’s build environment. The “make” command will use the generatedmakefile to create PHP binaries.
The “configure” and “make” command will not always runsmoothly. It is dependent on your machine’s setup. Please see the followingsection about debugging the “configure” command for more information.
If the “configure” and “make” command ran smoothly, you canrun the “make install” command. This command will copy the php binaries to/path/to/where/you/want/to/install/php. It will also overwrite the PHP apachehandler (the glue between Apache and PHP, called libphp5.so) at /usr/libexec/apache2/.You may want to backup the Mac OS’s default libphp5.so before running “makeinstall”
##Be sure that you havebacked up /usr/libexec/apache2/libphp5.so##
After the “make install” command, the php binaries can befound under /path/to/where/you/want/to/install/php/.
Right now we have compiled the PHP binaries that have allthe extension modules that OAT requires, EXCEPT the pdo_informix extension.This extension DOES NOT come with PHP source code from php.net. We have tocompile it from source code separately from PHP, and enable it as a dynamic PHPextension module. We will go through the process of compiling pdo_informix inthe later section “Compiling pdo_informix extension and enabling it in PHP”.
Right now we also have compiled the PHP apache handler (theglue between PHP and apache). This will appear as a file called “libphp5.so”,under /usr/libexec/apache2/. This file will be loaded as a dynamic Apachemodule. This will be described in the later section “Enabling PHP in Apache”
The next thing you have to do is to create a phpconfiguration file. Copy the sample php.ini file from/path/to/php/source/code/php.ini-dist to /path/to/where/you/installed/php/lib
Then edit the php.ini file. Make the following changes tothe php.ini file.
memory_limit = 128M Changethis to àmemory_limit= 300M
extension_dir ="./"Changethis to à extension_dir =“/path/to/where/you/installed/php/lib/extensions”
OAT requires a greater memory limit. Thus we increased thememory limit here.
The extension_dir is the directory where dynamic PHPextension modules will reside. Dynamic PHP extensions have to be placed underextension_dir, and enabled in the php.ini configuration file. The pdo_informixextension module will be handled in this fashion.
Debugging Information about the “configure” and “make”command
The --prefix tag lets you specify where you want to installthe php binaries. You have to make sure that you have the write permissions tothat directory before configuring PHP.
The --with-apxs2 tag lets you specify where to find yourapache binaries, so that the apache handler (i.e. the glue between Apache andPHP) can be built. This apache handler will appear as a file called“libphp5.so” after the compilation. It will appear under /usr/libexec/apache2/.This document will talk about how to dynamically load this handler to theApache web server in the later section “Enable PHP in Apache”. Here we areusing the Mac OS default Apache web server, thus this flag should be set topoint to /usr/sbin/apxs.
The --enable and --with flags specify what extensions youwant to enable. Pdo_informix will be enabled separately after we compile thePHP binaries.
The “configure” and “make” command will not always runsmoothly. One possible reason is that the prerequisite libraries are notpresent. For example, the php gd extension will not compile if libpng andlibjpeg libraries are not found on your machine. The openssl extension will notcompile if libssl and libcrypto libraries are not found. The libxml extensionwill not compile if libxml2 libraries are not found. Make sure that you have libpng, libjpeg, openssl and libxml2 installed into /usr/local/or /usr/ before running the configure command.
Another possible reason is that the library architecture andthe compilation flag do not match. If your libpng, libjpeg, libssl, libcrypto and libxml librariesare in 32bit mode, you have to set the compilation flag CFLAGS to –m32. If yourlibraries are in 64bit mode, you have to set CFLAGS to –m64. For example, ifyou want to see whether your libxml2 library is 32bit/64bit, run the followingcommands:
##If your libraries are64bit, set CFLAGS to be –m64##
setenv CFLAGS –m64
##If your libraries are32bit, set CFLAGS to be –m32##
setenv CFLAGS –m32
If you encounter problems, you can look into the config.log file generated by the configure script. You canalso open up the configure script (i.e. /path/to/php/source/code/configure)or the makefile (i.e. /path/to/php/source/code/Makefile)with a text editor, search for the error message and debug the problem. Sometimesthe configure script cannot find libraries because they are not installed on /usr/ or /usr/local/. In thatcase, hardcoding your library paths to the configurescript’s library search path will solve the problem.
You may also add the --disable-all tag to the configurecommand. This will disable all other extensions that we did not specify in theconfigure command. Compiling fewer extensions will give us fewer errors. SinceOAT doesn’t require those extra extensions, it is okay to add the --disable-allflag.
Compiling PDO_INFORMIX extension and enabling it inPHP
Before you compile pdo_informix.Make sure that CFLAGS are set accordingly. If your Client SDK/IConnect libraries are 32bit, then set CFLAGS to be –m32.If your Client SDK/IConnect libraries are 64bit, thenset CFLAGS to be –m64
Decompress the pdo_informix sourcecode and run the following commands
Add the following line to the php.ini configuration file (Under/path/to/where/you/installed/php/lib/php.ini)
This will enable the pdo_informixextension in PHP.
Enabling PHP in Apache
After PHP has been built successfully, the PHP apachehandler (the glue between PHP and apache) will be created. This will appear asa file called “libphp5.so”, under /usr/libexec/apache2/
We have to edit the Apacheconfiguration file (httpd.conf) to load thelibphp5.so. This can be done by adding the following three lines in/etc/apache2/httpd.conf. These lines might already exist in the httpd.conf but are commented out. In that case you onlyhave to uncomment them.
You also have to setup the Apacheenvironment variables. You will have to edit the /usr/sbin/envvarsfile. Add the following lines in the envvars file.
UnpackOAT source code at /Library/WebServer/Documents/OpenAdminTool.The ownership of all files under this directory has to be changed too. Makesure these files are owned by the user and group that runsapache. (These should match the user and group settings in/etc/apache2/httpd.conf)
Visithttp://<hostname>.<domainname>//OpenAdminTool/index.php andcontinue with the OAT web install. OAT should be operational after the OAT webinstall.
The GLSLIB is a library that is used to internationalize IBM Informix products such as IBM Informix Dynamic Server and IBM Informix Client SDK. Each IBM Informix product is bundled with a specific version of GLSLIB. This GLS library supports English, Western European, Eastern European, Asian and African languages. Additional Locales that are not included in the GLS library can be installed from the International Language Supplement (ILS). ILS provides all available GLS locales and code-set conversion files. It also includes error messages to support several languages. The important point about ILS is that it is a platform independent product. The languages supported by ILS are Czech, German, French, Spanish, Russian, Polish, Slovak, Japanese, Simple Chinese, Traditional Chinese and Korean.
The following tables provide information about the version of GLSLIB - bundled with IBM Informix Dynamic Server and IBM Informix Client SDK. It also provides the information about the version of ILS that is compatible with the specific version of IBM Informix product.
The table can be used to 1) Find the version of GLS library bundled with the specific version of IBM Informix product. 2) Determine the compatible version of ILS that should be used with the specific version of IBM Informix product.
MacWorld was an amazing event this past week in San Francisco at the Moscone Center. Although this is generally a consumer oriented event there were many companies with an enterprise focus showing their solutions. IBM was in the smallest size pedestal in the Enterprise Solutions Area -- People would constantly walk by and say - "IBM ---- the biggest company with the smallest pedestal" - We smiled and told them we make the biggest impact!!!!
As you know we just announced our beta for the Mac OS X this week and were at the show to start to get the word out and meet customers and business partners who are interested in an "Industrial Strength" data base for their application. There was a ton of traffic in the ped - -People were at first surprised and then pleased to see us there as they have been looking for a dependable data base that is secure and scalable to build their applications on. Many developers who have a long history using Informix were thrilled to see the announcement and anxious to try the beta.
MAC users are very happy to see an enterprise class database on the LATEST MAC OS.
Looking for something better than what is currently available on MAC which just hasn't been reliable and has not scaled to meet their needs.
Some really like the idea of OAT that is open source and allow users to customize, especially the free part.
One mid sized system integrator commented " We are glad to see IBM supporting the MAC platform as we are building applications to take into Government and Financial Markets - We need a data base that our customers can depend on. IDS is exactly what we are looking for."
In IDS, when a new row containing a column that can vary in length is to be inserted on a page, the row will be inserted only if the page contains sufficient space to allow the new row to grow to its maximum length. This can result in low disk space usage. In IDS 11, you can change this default behavior by setting the configurationparameter MAX_FILL_DATA_PAGES to 1. When MAX_FILL_DATA_PAGES is enabled, the server will add a new row to a page ifafter adding the row, at least 10 percent of the page is free for the future expansion of the rows. The database server needs to be restarted after changing this configuration parameter.
To take advantage of this setting:
Existing tables with variable-length rows must be reloaded OR
Existing pages must be modified, followed by further inserts
Advantages of enabling MAX_FILL_DATA_PAGES are:
More data can be stored in less disk space
Enables the server to use the buffer pool more efficiently
Reduces fetch times as more rows can be accessed by reading less number of pages
The possible disadvantages of enabling MAX_FILL_DATA_PAGES are:
Allowing more variable-length rows per page might store rows in a different physical order
As the page fills, updates made to the variable-length columns in a row could cause the row to expand so itno longer completely fits on the page. This causes the server to split the row onto two pages, increasing the access time for the row
Example: Suppose you have a table with a column of type lvarchar(6000). If the dbspace in which the table resides is of 4K or 8k pagesize, you might see a lot of unused space in the pages. If you do not have to make updates which would cause most of the rows to expand to their full length, you might want to enable MAX_FILL_DATA_PAGES tobetter use the space in each page.
Here we will look at each of the XML functions given in Part 1 with examples.
genxml()/genxmlclob() You can use these functions to create an XML row element for each row of a SQL query result. Each column in the rowis an attribute of the row element. genxml() is used for returned row values that are LVARCHAR(32739) or less. Forlarger values, you should use genxmlclob(), which returns a CLOB. These functions process the rows without any specific order. If the order of the rows is important, you can use thederived table queries to get the result set in the correct order, and then apply the functions on the result set.
select genxml(row (customer_num, fname), "row") from customer;
You can select all columns in a table by passing the table name as the first argument as shown below:
select genxmlelemclob(customer, "cust") from customer;
genxmlschema() & genxmlschemaclob() They are like genxml() but generate full XML schema including XML header and data. An XML header specifies documentproperties such as the document encoding, the document type definition(DTD), and XML stylesheet(XSL). Thefollowing example shows a select using genxmlschema and its output:
select genxmlschema(customer, "cust") from customer;
extract() and extractxmlclob() These functions evaluate an XPATH expression on a XML column, document, or string. These functions are identicalexcept that extractxmlclob() returns a CLOB instead of LVARCHAR. They are used to return an XML fragment of theevaluated XML column, document, or string. They are compatible with the Oracle extract()function.
execute function extract('<name><first>fred</first></name>','/name/first');
Output from the above sql:
extractvalue() and extractxmlclobvalue() These functions return the value of the XML node in contrast to extract(), which returns the XML node. They are compatible with the Oracle extractvalue() function.
execute function extractvalue('<name><first>fred</first></name>','/name/first');
Output from the above sql is just the name without the XML tags:
Existsnode() This function returns 1 if the specified XML node exists in an XML document. It is compatible with Oracle exists() function
IDS 11 provides support for XML publishing thus enabling applications to publish SQL results as XML, query XML dataand return XML. It also provides the framework to map an XML schema into a relational schema and to shred an XML document into relational tables.
The XML functions available in IDS 11 are summarized in the following table:
return SQL results as XML elements
return column values as XML elements
return schema as XML
return result set as XML
return result set as XML with head
evaluate XPATH expression
return value of XML node
verify whether a node exists in XML doc
parse XML doc to determine if it is well-formed
To use the XML functions in IDS 11:
Start an XML VP by doing one of the following
Add VPCLASS idsxmlvp,num=1 in onconfig file to start the XML VP when the server starts
Use onmode -p +1 idsxmlvp command to add an XML VP dynamically after the server has started
Note: An XML VP is required only if you use XPATH functions such as extract(), extractxmlclob(), extractvalue(), extractxmlclobvalue(),existsnode(), idsxmlparse(). XML functions starting with gen do not require the XML VP.
Verify that $INFORMIXDIR/lib/libxml.udr is read-only
Make sure a default sbspace exists.
Default sbspace is specified by the SBSPACENAME configuration parameter. You can check the output of onstat -d to verify that the space exists.
I will talk about each of the above functions with examples in Part 2.
In previous IDS versions, the logging mode of the temp table matches the logging mode of the database in which theyare created. In other words, temp tables created in a logged database will be logging by default and temp tables created in a non-logged database will be non-logging by default. If you do not want your temp tables to be loggingin your logged database you have to use the "with no log" clause when you create it.
For example the following SQL creates the temp table temptab1 with no logging.
create temp table temptab1 with no log;
In IDS 11, you can control the default logging mode of temporary tables. If you set the configuration parameter TEMPTAB_NOLOG to 1, temp tables created in a logged database will be non-logging by default. This is useful inreplication environments where you create your databases logged but do not want to replicate your temp tables.
If TEMPTAB_NOLOG is set to 0, this feature is disabled.
OpenAdmin Tool for IDS, the PHP-based administration console for IDS, is now available for downloadfrom the Open Source download site.
Updated 2/14/08 with new recommended PHP and XAMPP versions which are good for Cheetah and Cheetah 2.
Note: This article is based upon the “Installing IDSAdmin in a Windows XAMPP environment” article by Guy Bowerman and Inge Halilovic. The Windows version of this article is available on the IBM IDS Experts Blog.
Installation of OpenAdmin can appear complex - this article provides anexample of one way of installing OpenAdmin. This example uses an XAMPP package on Linux. 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:
Install CSDK 3.00.UC1 (or later)
Download and extract the XAMPP 1.6.4 runtime package and development package
Download and extract PDO_INFORMIX 1.1.0
Update the php configuration file
Download and extract the OpenAdmin package
Start the Apache web server
Install and configure OpenAdmin
1. Install Informix Client SDK
If not already installed, install the Informix Client SDK and set INFORMIXDIR in your environment to be the location where CSDK is installed. (Note: CSDK 3.00.UC1 or later is recommended.)
To install Informix CSDK, extract the product files and run as root installclientsdk. Follow the instructions on screen to install CSDK.
Important: Even if you are installing on a Linux-x86_64 machine, you will need a Linux 32-bit version of Informix CSDK. This is because Linux XAMPP is a 32-bit build; so to build a compatible PDO_INFORMIX module, you will need the Linux 32-bit version of Informix CSDK.
Important: You must download both the Linux runtime and development packages; the development package is necessary in order to be able to compile the PDO_INFORMIX driver.
To install, all you need to do is untar both XAMPP packages in the /opt directory. (If you do not want to install directly in the /opt directory, you can create a symbolic link which links /opt/lampp to the directory you choose for installation.)
tar xvfz xampp-linux-1.6.4.tar.gz –C /opttar xvfz xampp-linux-devel-1.6.4.tar.gz –C /opt
Now XAMPP is installed in the /opt/lampp directory
Untar the PDO_INFORMIX tar file into the /opt/lampp/lib/php/extensions directory.
tar xvfz PDO_INFORMIX-1.1.0.tgz –C /opt/lampp/lib/php/extensions
4. Configure PDO_INFORMIX
Set INFORMIXDIR in your environment to the directory where Informix Client SDK is installed.
Make sure your PATH is set so that the php and phpize executables are picked up from the /opt/lampp/bin directory. If you have another version of PHP installed and your PATH is not set correctly to pick up php from /opt/lampp/bin, you may get errors when executing make for PDO_INFORMIX due to version incompatibility.
Important: If you are installing on a Linux-x86_64 machine, you also need to set CFLAGS in your environment to “-m32”. This is because XAMPP for Linux is a 32-bit build and therefore you will need to compile PDO_INFORMIX into a 32-bit shared object file instead the default 64-bit version. This also means that the version of Informix CSDK must be the Linux 32-bit version.
To configure the PDO_INFORMIX module, execute the following 5 commands:
cd /opt/lampp/lib/php/extensions/PDO_INFORMIX-1.1.0/opt/lampp/bin/phpize./configuremakemake install
Example output for these commands are shown below.
Sample output after the “/opt/lampp/bin/phpize” command:
Sample output after the “./configure” command:
Sample output after the "make” and “make install” commands:
After executing “make install”, verify that pdo_informix.so was copied into your extension directory /opt/lampp/lib/php/extensions/no-debug-non-zts-20060613/
Note: For more information about building the PDO_INFORMIX module, go to the DeveloperWorks article on that topic.
5. Update the php configuration file (php.ini)
Navigate to the /opt/lampp/etc directory, and open the php.ini file in a text editor.
Edit php.ini to enable the Informix PDO driver:
Locate the “Dynamic Extensions” section in the php.ini file and add the pdo_informix extension. extension="pdo_informix.so"
Modify the memory_limit parameter to be at least 256M. Memory_limit = 256M
6. Download and Extract the OpenAdmin tar file in the htdocs directory.
Create a new directory under /opt/lampp/htdocs to contain the OpenAdmin Tool files (For example: /opt/lampp/htdocs/openadmin)
Untar oatids.tar to this new directory.
Within the OpenAdmin directory, grant write permissions to the conf and install directories and all of their contents.
cd /opt/lampp/htdocs/openadminchmod ugo+w install conf -R
7. Start the Apache web server.
Verify INFORMIXDIR is set to the Informix CSDK directory.
Start the web server by running /opt/lampp/lampp start as root.
You should see output similar to the following:
Starting XAMPP for Linux 1.6.4...XAMPP: Starting Apache with SSL (and PHP5)...XAMPP: Starting MySQL...XAMPP: Another FTP daemon is already running.XAMPP for Linux started.
8. Install and Configure OpenAdmin.
Using a web browser, go to http://< machine_name >/openadmin/install where < machine_name > is the name of the machine where you have installed the products. Follow the installation instructions to install OpenAdmin Tool. After the first install screen a check will be made to ensure you have the correct PDO drivers:
Enter the base URL for your machine, language defaults, and optionallythe Google maps key for your machine URL:
8. Start using OpenAdmin
Once installation is complete you can go to the main page (http://< machine_name >/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.UC1, there must be aSQLHOSTS entry on the web server machine for each connection you add .
Once a connection is defined you can return to the main screen and connect:
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.
The Roles Folder, as it's name implies, gives you a view of all theroles created for the table, and a visual means to create new roles oralter existing roles. To get a quick look at how this folder works,let's create a test role that will have complete access to the customertable.
Select create rule from the folder, by right clicking onthe folder, and following the menu options. It should look like thefollowing:
This should now bring up the Data Object Editor window inthe center portion of your workspace. You will start in the Generaltab, and you will see that the Role name has been pre-filled, Let's change that to customer_role, so now you should seesomething like the following:
Let's now make sure we grant privileges to customer forthis role, to do this move down to the Privilege tab, clickon the table tab, and the click on the "Grant New Privileges"which looks like a yellow star. That will bring up the Gant NewPrivileges window. While it looks like you can just type a table namehere, you actually have to press the "browse" button and select the customer table fromthe browser. Once you do that you should see the window as shown below:
Go ahead and check all the boxes listed, and then check OK.
You will now be back in the main window. Just click on the Run DDL button, andthe customer_role role will be created
Now that you have a role in your Roles folder, let's look at what youcan actually do. You should see customer_role in the folder so rightclick on it, you should see the following:
As you can see you can do the following:
Alter the role
Drop the role
Generate the DDL for the role.
The two not listed above (AnalyzeImpact, and CompareWith) are not actually used by roles. As I'm sure youhave noticed, you cannot add users to roles from the Role Folder, thatwill come with our discussion of the Users folder.
Now that you have a workspace, and a connection established, it is timeto take a quick look around the options available to you in theDatabase Explorer window. By default this window is located in thelower left hand corner, and on start up looks like this:
As you can see this has a tool bar as well as the Connections folder.Let's take a quick look at the options you have on the tool bar.Traveling from Left to right, you have:
Collapse all:This allows you to quickly collapse all the folders you have opened inyour explorer, as shown in the above dialog.
Link:This allows you to link data in the explorer to an open editor.
SQL Scrapbook:This lets you open up a file that saves the SQL you aretesting.
Disconnect:Will disconnect any connection you have highlighter.
New Connection:Will take you to the connection tool, so you can add more connectionsand Databases to your Database Explorer.
SQL Editor:This lets you go straight to the SQL Editor Window.
ExportConnection: This lets you export an existing connection toXML. Makes it easy to deploy connections to multiple machines.
ImportConnection: Allows you to import the connection settingsfrom an XML file. So if, for instance, someone were to export theconnection settings for a development database, rather than having toget all the information to fill the connection string yourself, all youhave to do is import the connection.
Now let's open up our connections folder in this window. We willbe looking at the stores_demo database. So first we open theConnections folder, and then the stores_demo connection folder, andfinally the stores_demo database folder. And when the folders are open you will see something like the following:
As you can see, each connection tells you what type of instance you areconnecting too, in my case all of my instances are 11.10, thoughDeveloper Workbench reports them as 11.0. You can see thatthe Database folderhas three sub-folders. Those Folders are:
While working with the open beta of IBMData Server Developer Workbench 9.5 , I noticed that inaddition to that product you also become open beta participants for theJava Common Client (JCC) driver. In fact, the Workbench uses the JCCdriver by default. On the one hand, this is absolutely great, because ithelps you see where IBM's long range direction is with clients. On theother hand, JCC requires some special setup on the IDS instance level.This article will detail how to setup you instance to handle JCCconnections, and then will use the Developer Workbench to show how youconnect using JCC.
Whathappened to my SQLI?
The reason why JCC connections require engine configuration changes isthe default means by which IDS instance communicate is through the SQLIprotocol, whereas JCC needs the DRDA protocol. Currently IDS does notallow for both SQLI and DRDA to connect through the same listenerthread, so this means is we need to configure a listener thread andport to accept DRDA calls.
This is done by making a simple change to the SQLHOSTS file as shownbelow:
As you can see the protocols look the same as our old ones, exceptinstead of the prefix ol,you use dr.
And full example is below:
cheetah_dr drsoctcp gofishsqlexec2
After changing the SQLHOST all you need to do is place this entry in youonconfig file, in the DBSERVERALIASES variable. So if you use theexample SQLHOSTS entry above and had no aliases currently you wouldhave the following in you onconfig file:
Now just bring your instance off-line and then back on-line, and then weare ready to use the Developer Workbench.
Setting up yourJCC Driver for 11.10 in Developer Workbench 9.5
Now that you have set your IDS instance up to accept JCC driverconnections, let's establish one using the Developer Workbench 9.5
In your Database Explorer window, select Connection->New Connectionas below:
This will bring you to the Connection Settings window. Below is anexample of the JCC driver with all field filled in:
As you can see, you do not need an INFORMIXSERVER for your connection,just the Database, Host and Port Number. Once you configure this foryour server, just click the TestConnection button. After that just click the Finish button. Younow have a JCC connection that you can use for your Applicationrunning against IDS 11.10
Now that you have installed the Developer Workbench it is now time torun it. ON the first run, you should be asked to set up yourworkspace. This should give you a screen like the following:
Once you select this, the Workbench takes a little while to build yourworkspace. Once done however you get the classic EclipseWelcome screen. I have highlighted your workspace in the followingscreen shot , because that is the next place you want to go:
Once you click on the workbench you get the generic workbench. The nextstep at that point is to start a project , and establish a connection: Setting up a new project to handle queries is fairly straightforward,and can be reached very quickly , all you have to do is select the DataDevelopment Project option as in the following screen shot:
Selecting the above puts you to the new Data Development projectscreen, as shown below:
As you can see, there are some DB2 flavored words here.Schema in DB2, is the IDS equivalent of a database, and as such, keepthe default radio button, as seen in the screen shot above, set. I would recommend that you giver your project a meaningfulname, but that is completely under you control. In my case I listed itas "Mark's Test project for Blog".
This brings us to the next page which assigns an existing databaseconnection, or creates a new one. Since this is our first time through,just hit the next button, as we will need to create a new connection.
The Connection parameters gives you several options for connections, aswell as several data server choices. Take a look at the below screenshot:
This is a standard Informix JDBC connection string at this point. Thescreen shot above differs slightly from what you see as the defaultsettings, as I have selected the "Informix JDBC driver" instead of the"IBM Data Server Driver for JDBC and SQLJ." In short I took the JDBCdriver instead of the JCC driver. The main reason is because JCC itselfis in beta, and I wanted to use something I knew how to connect withand trace. JCC also requires a DRDA listener thread, and I don't have one set up for the instance I am using. Update : Check out Establishing JCC connections in 11.10 if you want to use JCC connections.
Fill the remaining fields above with the normal Informix connectionsettings, and test you connection. Once you connection is working,click on the next button.
This final screen allows you to filter your schema. Here is the screenshot :
Please note: The default is only for users who have IDS on a windowsplatform. Since schema id is actually a user id in IDS, filtering onINFORMIX means that no information of any kind will return for anydatabase objects if your IDS instance is on a Unix platform. The reasonof course is the user id is case sensitive, and informix != INFORMIX.Quite honestly I suggest you just disable filtering for now, when Icover the the Database explorer I will show you how to change thefilter how you want to.
As soon as you have selected your filter objects, you will finally seethe workbench laid out, and ready to be used for a Data Developmentproject. Here is mine :
In the next article I will discuss the Database Explorer Window in thelower left hand corner.
One of the big ideas recently proposed by IBM, and further proof of our commitment to IDS, has been the idea of a common setof clients tools (like a Java Common Client (JCC), for example). If you aregoing to have a common set of clients, or programming interfaces, itmight not be a bad idea to have a common development tool that willallow you to use these common clients, for both DB2 and IDS. Well IBMhas released in beta an Eclipse based common tool that allows that. Forthose who want to jump right in here is the link:
As with many things IBM , since this is an open beta the actual name atthe time of release may change. I will be blogging on thisproduct over the next few weeks covering its capabilities from the IDSside of the tool. So far there will be 5 parts to this report, notcounting this introduction.