Administrating and Developing with Informix
So one of the reason for the infrequent postings of late have been vacation related, but some have been helping a customer out with security related issues. I think that all to often we dba's forgot the impact, directly and indirectly, that these mandated changes have on application developers. One such impact is in Data Privacy Laws. Ultimately what this means is that you soon will not, from a legal liability aspect, be allowed to restore a production server instance into a test environment to try and reproduce a problem. The solution to this is using some sort of app that transforms your data so that it is still valid for testing but has no direct association (besides say data distribution) to the actual real production data.
IBM has a very nice product to help you with this, which is Optim Data Privacy, and here at developerworks we have had 2 recent articles on the very topic.
So two tutorials. Let me know what you think of the content. Is this something you would like to see more of in Developerworks, tutorials about how IBM products rok with other IBM products?
So the new version of Informix is fast approaching, and IBM has a webcast on that very topic. Below you will find a summary, and the link to sign up.
Based on support calls there is a fairly high demand for Informix integration with Pluggable Authentication Modules (PAM). When it comes to worked examples there is somewhat of a dearth. It is therefore refreshing to see this new developerWorks article by Manoj Mohan, Ramesh Gopal Srinivasan, Thamizhchelvan A. Anbalagan: Enhance Informix Dynamic Server security using the Pluggable Authentication Module framework and JDBC - Increased authentication flexibility for IDS.
The article includes a working example PAM module written in C with Solaris 32 compilation instructions and configuration file. Next is a an example JAVA callback module using JDBC. The Informix JDBC driver has supported PAM since 2.21.JC5. (Incidentally the JDBC version coming out with Cheetah will be 3.10.)
At one point the article states:
Note that IDS also supports LDAP on Windows which is set up and configured just like PAM - look in %INFORMIXDIR%\demo\authentication for an example. The IDS documentation for LDAP on Windows is here: http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.admin.doc/admin219.htm.[Read More]
During this morning's Chat with the Lab on Virtualization and Cloud computing with IDS (which I thought went quite well, in that it wasn't the worst webcast I've done, though I always think of some extra things I could have said afterward), I made a vague threat to post a link on this blog to an IDS High Availability demo showing HDR and MACH11 failover across Amazon Availability Zones.
This demo, and two others can be found on the Amazon Web Services blog: http://aws.typepad.com/aws/2009/05/ibm-informix-database-in-the-cloud.html. The high availability demo is the second one.
The slides for this lab chat (and others) can be downloaded from here: http://www-01.ibm.com/software/data/informix/labchats/#24.[Read More]
Back to updates and potentially useful information. Many of you may be now writing apps for informix that are running on Clients and Servers using some form of LDAP for user Authentication. If you happen to using Active Directory for you chosen form of authentication, please check the following:
Microsoft already has a fix for the problem, but in the meantime if you are getting inexplicable -951 errors when attempting to connect to an Informix instance using Active Directory, this may be your culprit.
This got me thinking about what my hottest feature in IDS 11.5 is. I'd probably pick something obscure like XSLT transform support or oninit -w on Windows. I think this is referring to redirected-writes, which is indeed a not bad 11.5 feature..
For those of you who made it to iiug, I'm sure you all remember Rob Thomas promising more to come on offering and other
changes. Well today is that day, and it is a great day for anyone who wants to do application development on Informix.
New offerings and prices.
So why do I think this is great for Developers? Well mainly for the Informix Ultimate-C edition for Mac and Windows.
Let me quote from the above:
Gives businesses, ISVs, and OEMs the ability to develop and deploy enterprise-class functionality for departmental
or small-to-medium sized business solutions, at no cost.
Look at that again.. Windows and Mac for the Ultimate-C Edition at no cost. So if you want to design, develop, and deploy
a Windows or Mac based solution that needs a robust full featured RDBMS, then Informix is now the clear best solution.
Wanted to pass along the official link for Informix Ultimate Warehouse Edition
That of course is the standard marketing story. The key piece being added to this edition is
For those of you who also follow DB2/Z this is effectively the Smart Analytics Optimizer for Informix. The above link is to our documentation on the product.
I've had the opportunity to work with members of my own team in the Accelerated Value Program on the Ultimate Warehouse edition for the last month. It can be made transparent to the application developer and Flat out flies. Once setup and enabled IWA has generated, for my team so far, a performance improvement of more than 66 times the speed of the same informix query when not accelerated. That query had already been significantly improved because of the Star/Snowflake support in the Optimizer added in 11.70.FC1. Essentially IWA has turned the OLAP/ROLAP queries we were running into ROLAP/OLAP queries with OLTP response time.
If you noticed above, possibly the best part of this is that it is transparent to the application, so if you are using IBM Cognos, or any other Business Intelligence type of tool, you just have to request the DBA to turn IWA on for you, you don't have to get any special "IWA aware" tool to use the product.
I will definitely talk more about the IWA as I have time and am allowed. For those of us that have to write against and Use warehouses or Data marts, this is a big deal.
Friday was my last day in Tech Support and today I take up a new position in Development as IDS architect for install, common libraries and Windows. Having spent most of my career in tech support, there is plenty about the old job I'll miss. I'll subjectively state that Informix tech support is a great environment to work in. Unlike many tech support organizations Informix support engineers have equal status with their development counterparts, and have opportunities to work with source code, fix bugs and develop product features. The customer perspective that support engineers gain is valued by development and tech support sign-off is required during the review process for new features.
In the new role I am interested in any feedback and suggestions you might have regarding the IDS installation process (on any platform) - does it meet your needs? And if you use or plan to use IDS on Windows, anything you would like to see different or Windows features you would like to see better integration with? Let me know.[Read More]
Great news for people who are bored of reading insipid mediocre blogs like this one. Three new Informix blogs have sprung up on developerWorks for our reading pleasure..
Fred Ho leads the IDS Competitive Technologies team, and despite his self-effacing first post has a wealth of experience and expertise, particularly in areas such as data warehousing and business intelligence. Fred will be enlightening us with more of what the competitive team gets up to in subsequent posts.
Keshav is the Informix architect for SQL and Optimizer and is responsible for designing many of the SQL and performance features and enhancements we've seen in recent versions of IDS. He also has interests in diverse areas such as IBM Smart Market and cloud computing. In his first post he has found an interview with Roger Sippl, venture capitalist and founder of Informix.
Jonathan Leffler's is our security architect, and the guy I tend to go to when I have obscure questions on SQL syntax and 4GL. His new blog: Ruminations on Informix begins nice and technical with the question of where to put $INFORMIXDIR.
Welcome to developerWorks!
The term DLL hell is usually invoked with reference to conflicts between DLL versions, missing DLLs and multiple copies of DLLs. In the 14th century the poet Dante portrayed metaphysical hell as having multiple layers or circles, and in my opinion the analogy readily extends to DLL hell.
What concerns me lately I'll call the fifth circle - the swamp-like water of process address space, where wrathful DLLs fight over load addresses and slothful DLLs lie gurgling beneath the surface, fragmenting contiguous space.
A process running on 32-bit Windows has 2GB of address space by default. Into this space it needs to fit any operating system and application DLLs it loads, as well as any shared memory segments it attaches to. A DLL can have a default load address set at link time, and the operating system DLLs are usually set to load in the top 256 Mb of process address space, above 0x70000000. Incidently, a great tool to view DLL load addresses and process address space on Windows is Process Explorer from sysinternals.com.
Applications which attach to IDS shared memory segments, such as oninit, onstat, onbar need a contiguous free block of address space as large as the segment they are attaching to. So if onstat connects to the Resident segment and onstat -g seg shows it to be 1GB in size (due to the number of buffers configured for example), the onstat address space will need a 1GB contiguous gap where no DLLs are loaded. The first place it will try and attach the resident segment is the value of the onconfig parameter SHMBASE which is where oninit attaches it. The default SHMBASE value on Windows is 0xC000000.
The problem starts when when a DLL has a base address somewhere in the middle of the process address space. This fragments the address space and reduces the maximum size of shared memory segment that could be attached. If base address is not set at link time, DLLs have a default address of 0x10000000. A DLL loaded there would certainly cause a problem as it's only 64 Mb higher than default SHMBASE. If a process loads multiple DLLs which have a default load address of 0x10000000, one will be loaded there and the rest will be dynamically rebased to wherever the OS sees fit.
There are currently two defects for XBSA DLLs loaded by onbar which are set to the default load address and hence onbar returns errors in larger IDS shared memory configurations:
IC50382 ISM DEFAULT LOAD ADDRESS PREVENTS ONBAR FROM WORKING WITH IDS INSTANCES WITH LARGE SHARED MEMORY CONFIGURATIONS
IC50204 TDPI CAUSING ONBAR ERROR CODE -43399 WITH RESIDENT SHARED MEMORY SEGMENT BEYOND CERTAIN SIZE ON WINDOWS - this one only relates to the XBSA DLLs supplied with Tivoli Storage Manager
CA Storage Manager XBSA DLLs also have this problem - in the past we've had to rebase their DLLs to make them play nicely with onbar.
These defects are currently open, though Technical Support can work around the problem for you by rebasing the DLLs manually using the Windows Platform SDK rebase tool.
What can be more frustrating is when an operating system DLL has a load address outside of the recommended system DLL range of 0x70000000 to 0x7FFFFFFF. Windows currently has several bad DLLs which can cause problems for IDS:
873453 The base memory address setting of the Samlib.dll file in Windows Server 2003 may interfere with programs that require a large shared memory setting
894472 Third-party programs that require lots of memory do not run in Windows XP Service Pack 2
912570 Programs that require lots of memory may not run after you install Windows Server 2003 Service Pack 1
913409 A program that allocates a large block of contiguous memory may not start or may intermittently fail in Windows Server 2003
924054 Programs that request lots of contiguous memory may fail after you install security update 921883 (MS06-040) on a Windows Server 2003 Service Pack 1-based computer or a Windows XP Professional x64 Edition-based computer
If you are experiencing problems with onbar or other IDS utilities on Windows which go away when the number of buffers is reduced you may be in this particular circle of DLL hell. Depending on the problem the solution could be to request a patch from Microsoft, or seek help rebasing XBSA DLLs from tech support. Now would be a good time to familiarize yourself with Process Explorer to assist with the troubleshooting. A good way to determine the base address of a DLL is to use the Windows Platform SDK utility dumpbin.exe. E.g. to see the base address of xpsp2res.dll type:
Some additional process address space can be opened up by setting the /3GB boot.ini switch - this will provide an extra 1GB of address space above 0x80000000 for any process which is built with the IMAGE_FILE_LARGE_ADDRESS_AWARE in the process header (and IDS binaries are built with this).
One glimmer of light on the horizon is that when x86_64 IDS is available for for Windows 64-bit, this problem should largely be a thing of the past. Shared memory segments larger than 2GB will be available, and we can start ascending the terraces of DLL Purgatory - ok I probably took the analogy too far that time.
Before anyone asks when the x86_64 Windows port of IDS will be ready, that has not been finalized yet; all I can say is that it is in progress.[Read More]
Critics might argue that this blog has over the years become a little sparse on actual weighty topics related to application development, and somewhat abundant when it comes to reprinting random announcements and links to other posts. It could be further argued, by the most exacting of readers, that my average post takes about 25 seconds to type, and I don't even bother checking it for typos..
Therefore I am very pleased to welcome Mark Jamison, an enterprise support engineer, trenchant Informix developer advocate, and posessor of many other talents.. as a technical author for this blog. You'll be seeing posts from Mark over the coming months.
RSS feeds provide a convenient way to display up to date information directly in your Firefox browser toolbar. I for one spend more time perusing the BBC News and Slashdot feeds than I should.
Implementing an RSS feed that displays database information is very simple, hence the 5 minute Informix RSS feeds..
These two examples, for JSP and PHP, display a summary of the last 20 orders from the IDS stores_demo database. They both use the following IDS 10.0 SQL query:
Five Minute JSP Informix RSS Feed
If you have a working Tomcat installation setting it up to work with Informix JDBC drivers is as simple as copying the JDBC jar files into $CATALINA_HOME/common/lib and restarting.
Customize the machine, Informix Server and links in this code and create a new Tomcat webapp. The resulting URL can be added to the Firefox toolbar as a Live Bookmark.
Five Minute PHP Informix RSS Feed
Here's the equivalent code in PHP. This example uses the Informix PDO Driver.
This should result in the same output as the JSP example.
The look of the XML output when viewed from a browser can be improved by using an XSL stylesheet, and various parameters such as refresh interval (TTL) can be tweaked. There are some good RSS tutorials and books available, or it can be fun to reverse engineer the stylesheet of your favourite news feed to see what they do.[Read More]
The last time I tried a Ruby on Rails tutorial I gave up half way through, having trouble accepting the convention over configuration ethos, where database tables and columns need to follow a naming conventionto work with Rails' automatically generated controllers and models. Maybe it's the database engineer in me that feels the application development environment should adapt to my data model rather than the other way round, or maybe I'mjust not open to new ideas.
This week Gerardo Santana has announced the first release of Rails/Informix, an Informix adapter for Ruby on Rails, and a new version 0.3.0of the Informix driver for Ruby. So nowseems like a good time to take them for a test drive.
Trying to let go of preconceptions, I resolved to followCurt Hibbs' Rolling with Ruby on Railstutorial to the bitter end to make a simple recipe application.
Documented here are mainly the Informix specific deviations from the tutorial. Note I used IDS 10.0 - the recommended version for Rails/Informix. My test platform was Windows to match the tutorial, so for any operating system specific paths and commands assume the equivalent for your OS.
Since the onlamp tutorial also includes instructions on installing MySQL, refer to Jean Georges Perrin's Installing IDS on XP article for an equivalent tutorial on installing IDS.
1. Install Ruby, and Ruby on Rails
The "One-Click" Ruby installer for Windows was downloaded from http://rubyforge.org/frs/?group_id=167. Installation is as simple as running the GUI and accepting the defaults. Once installed, add c:\ruby\bin to the path and Ruby on Rails can beinstalled by running:
C:\ruby>gem install rails --include-dependencies
2. Build and Install Ruby-Informix
Download the Windows or the UNIX package from http://rubyforge.org/projects/ruby-informix/ and unzip. On Windows (with the Informix Client SDK 2.90.x installed) set the Microsoft Visual Studio environment (vcvars32.bat), then build and install the driver:
ruby extconf.rb --with-informix-include=%INFORMIXDIR%\incl\esql --with-informix-lib=%INFORMIXDIR%\libnmakenmake installNote: on Windows only Visual Studio 6.0 is supported for building the 0.3.0 Ruby driver. The Informix CSDK version I used was 2.90.TC6.
3. Install the Rails adapter for Informix
Download the Informix adapter for Rails from http://rubyforge.org/projects/rails-informix/, unzip and (following the instructions in the INSTALL file) copy informix_adapter.rb to c:\ruby\lib\ruby\gems\1.8\gems\activerecord-*\lib\active_record\connection_adapters\.
Then "informix" needs to be added to the RAILS_CONNECTION_ADAPTERS line of c:\ruby\lib\ruby\gems\1.8\gems\activerecord-*\lib\active_record.rb.
Now Ruby on Rails is ready to use with IDS.
4. Following the Onlamp tutorial
The Rolling with Ruby on Railstutorial explains how to create a cookbook database in MySQL using MySQL-Front. An equivalent IDS database can be created in the data dbspace via dbaccess with the following Windows batch file:
echo create database cookbook in %INFORMIXSERVER% with log; ^create table recipes(id serial, title varchar(255), description varchar(255), ^ date date, instructions text, category_id int); ^create sequence recipes_seq; ^create table categories(id serial, name varchar(50)); ^create sequence categories_seq; ^ | dbaccess -Note: the database needs to be created with logging and for every table a sequence named after the table also needs to be created.
Because the "informix" string was added to active_record.rb as part of the Informix Rails adapter installation, the config\database.yml file for any new Rails application can now be populated with Informix specific connection details. My cookbook\config\database.yml looks like this:
development: adapter: informix database: cookbook@mydbserver username: myuser password: mypasswd
Where mydbserver corresponds to an SQLHOSTS entry on this machine. If a username and password, or the dbserver were not specified, a local database connection would be attempted with the credentials and environment of the user running the Ruby on Rails server.
With the exception of these data server specific parts the rest of the tutorial, including Part II can be followed verbatim.
The Informix adapter for Rails is working well with the latest Informix driver for Ruby. Give it a try, and remember the following points:
Once the basic Recipe application screens were working I was impressed with how Ruby on Rails can build a database enabled web application with just a few lines of code. Am I converted by convention over configuration into using plurals to name my tables from now on? Er, still not convinced. Comments explaining how wrong I am are welcome.[Read More]
Informix Dynamic Server Role Separation can be important for data server installations where clearly defined security roles exist. This post describes how to disable Role Separation on Windows if you decide you don't need it after all, and how to partly enable it - maybe you no longer wish to maintain separate users and groups for specific DBA tasks, or maybe you don't want to add valid users to the ix_users group before they can connect to the database..
The Informix documentation for configuring Role Separation on Windows states quite clearly:
You cannot turn off role separation once you have enabled it. To remove role separation,you must uninstall the database server and reinstall it without role separation.
This is for a good reason - to disable role separation without reinstalling involves editing the registry, and if that goes wrong it is back to reinstalling. It is better to follow the policy that if a task requires direct edits to the registry it is not supported.
With suitable disclaimers in place here's how Role Separation can be disabled on Windows without reinstalling IDS. The required edits are presented in the form of a regini script. In order to use you would need to modify it to set "ol_myserver" to your INFORMIXSERVER value. If anyone knows how to specify an environment variable as a registry key in a regini script please let me know..
; Regini script to disable IDS Role Separation; Last modified 6/6/06 - Guy Bowerman
Once a customized version of this script is run, check the changes were made correctly, and then manually delete the ix_aao and ix_dbsso Security groups that were created at install time. When IDS restarts it should be back to its default un-role separated state.
What this registry structure implies is the possibility of having a partial role separation implementation. For example, suppose you don't want separate DBA roles, but do want to improve security by only allowing members of an ix_users group to access the data server. You could implement this by creating an operating system group for your users, and then setting the
value from "*" to the name of your group.[Read More]