Guy Bowerman is the Informix Dynamic Server (IDS) architect for Windows, Installation and common libraries. This blog covers IDS application development, with digressions into operating systems, security and other topics. The postings on this site are his own and do not represent positions, strategies or opinions of IBM.

Chat with the Labs - Optimizing Performance

Tuesday, April 8th, 2008

8 AM PST, 10 AM Central, 11 AM Eastern, 4 PM London, 5 PM Paris

Optimizing IDS Performance

In this Chat, some of the more popular onstat options for performance issues will be covered, showing how they have evolved throughout the history of IDS. With this as a backdrop, guidance will be given to a performance-minded administrator on how to use the improved onstat information to get the most out of IDS. Our speakers will be Jerry Keesee, Informix Lab Director and John Miller iii, Senior Technical Staff Member.

You may RSVP for this Chat at: https://ww4.premconf.com/webrsvp/register?conf_id=9943887 . For RSVP assistance by phone, you may call 1-800-289-0579 and refer to the call with Confirmation Code 9943887.

Questions, comments: lspina _ at_ us.ibm.com

DLL Hell Part 4: Optimizing Shared Memory Usage on Windows

...
I thought I would take a chance on guessing your email address to send 
you some of my experiences regarding IDS shared memory allocation and 
DLL's ...maybe something you could include in your blog series on the 
topic (part 1, part 2, part 3). 
I had planned to send this as a comment to your blog, but since comments 
are disabled...(hint, hint ;o)...

I'm working with a customer that just took the big step from 7.31 to
10.00. The migration went fairly well, but after a week or so they got
problems with onstat, onmode and oncheck. They got the standard error:

shmat: [22]: operating system error
MapViewOfFileEx: w32ec=487 at nt_shm.c:663
Unable to attach to shared memory.
Invalid argument

and this seemed to occur when the total shared memory size had grown to
around 1.7GB. They are running 10.00.TC6X2 with /3GB turned on.

We tried applying the KB913409 hotfix and ran this with both 10.00.TC6X2
and 10.00.TC7 (because of the fix for IC52767), but we could still
reproduce the problem.

After some experiments, I managed to pinpoint the exact segment causing
the problem and compared the set of DLL's loaded by oninit.exe with the
DLL's loaded by the other utilities. I found that there were two DLL's in
the address space occupied by that segment: WINMM.DLL and PSAPI.DLL.

By increasing SHMADD to 34000, that particular address space wasn't used
by IDS and this allowed the customer to allocate all of their memory
without causing problems for onstat et al.

So, this problem can actually occur even though DLL's are loaded in the
correct address space and the best way to avoid it is to set SHMADD to a
higher value to make it stay away from those small areas in the system DLL
address space. Also, I think Informix should consider to:

  * make oninit.exe load the same DLL's as the shared memory based utilities.
  * have oninit.exe stay away from the system DLL address space altogether
   (not sure that Win32 API allows this in a simple way).
  * add documentation on the problem with suggestions on increasing SHMADD
   - maybe even set the minimum value to a higher value than now (like 64M).

...

cheers

Johan

Ruby/Informix 0.7.0 - Now with INTERVAL Support

Update 4/3/08

Unfortunately, one of the structural changes introduced in 0.7.0 was
not done right and caused that the name of Informix resources were not
unique.

This is fixed in 0.7.1, along with a documentation bug, and an
optimization that speeds up Database#columns (considered a bug also,
hence its inclusion in 0.7.1).


Original announcement

I'm pleased to announce another release of Ruby/Informix, a Ruby
library for connecting to IBM Informix.

In this release you'll find support for the INTERVAL data type, more
handy methods and more and better documentation with examples, along
with a new web site.

Web site: http://ruby-informix.rubyforge.org/
Documentation: http://ruby-informix.rubyforge.org/doc/

Some examples:

Connecting to a database:

    db = Informix.connect('stores')


Inserting records

    stmt = db.prepare('insert into state values(?, ?)')
    stmt.execute('CA', 'California')


Iterating over a table using a hash (shortcut):

    db.each_hash('select * from customers') do |cust|
      puts "#{cust['firstname']} #{cust['lastname']}"
    end



Changelog follows:

0.7.0  03/31/2008
------------------
New features:
    * Experimental support for the INTERVAL data type:
        - year_to_month, day_to_fraction, from_months and from_seconds class
          methods for creating an Interval object
        - +@ and -@ unary operators
        - +, * and / operations available with Integer, Rational, Date, Time
          and DateTime objects
        - methods for returning the respective fields of an Interval object
          individually (years, months, days, hours, minutes and seconds)
        - to_a method for returning the fields of an Interval object as an array
        - methods for converting the Interval object to the given unit, where
          apply (to_years, to_months, to_days, to_hours, to_minutes and
          to_seconds)
        - to_s method for displaying an Interval object as an string according
          to ANSI SQL standards
        - includes Comparable
    * Database#version returns a struct with version information of the database
      server.
    * Database#each and Database#each_hash shortcut methods for declaring and
      opening a cursor in a single step.
      Contributed by Reid Morrison 

    * Database#execute is not an alias for Database#immediate any more.
      It has become a shortcut for preparing and executing a statement in a
      single step.
    * SequentialCursor includes Enumerable
    * Ruby 1.9 compatible
    * More and better documentation

Bugs fixed:
    * The documentation for class Error was not being recognized by rdoc

Remarks:
    * Database.new deprecated in favor of Database.open
    * Database#do was removed
    * A lot of C code has been reimplemented in Ruby
    * Modules and classes have been reorganized
    * Database#execute still behaves the same, except that it can also accept
      input parameters and return at most one record. Database#immediate is
      more efficient though.
-- 
Gerardo Santana

How to register a blade without using Blade Manager

DATABASE mydb;
EXECUTE FUNCTION SYSBldPrepare('ifxmngr', 'sysblderrorlog'); 
EXECUTE FUNCTION SYSBldRegister('binaryudt.1.0','sysblderrorlog'); 

bladereg.bat:

  echo EXECUTE FUNCTION SYSBldPrepare('ifxmngr', 'sysblderrorlog'); ^
  EXECUTE FUNCTION SYSBldRegister('%2','sysblderrorlog') | dbaccess %1

Database Popularity Contest

IBM’s Top Performing Value Add Distributor - Bell Micro

"Bell Micro has been awarded as our best OEM Distributor in North East Europe for 2007,” said Aurore Achour, VAD Sales Manager, IBM Software Group - North East Europe. “The company, whose core offering is Informix OEM, has managed to grow both new business and maintenance OEM revenue in 2007, leading sales for North East Europe. More importantly, Bell Micro has demonstrated great value with a high number of technical certifications and skills, plus 24x7 support for both end-users and ISVs. Not only have they highly performed in OEM sales but they have also more than demonstrated their skills as Value Added OEM distributor.”

IDS Problems with Windows domains - status

If the Install in Domain option is checked and if the informix 
user does not exist on the PDC/BDC, then an error message like 
the following appears:					       
Installation Message					       
---------------------------				       
The user DOMAIN\informix does not exist. Please specify an     
existing user name.					       
---------------------------				       

In a new installed and configured MS Windows domain of Windows 
Server 2003 Enterprise					       
an IDS version 10 cannot be installed.			       
After the installation was started, a new user has to be       
created during the installation process.		       
When the password for the new user 'informix' was inserted,    
and the 'ok' button was used				       
an error message box like this appears: 		       
---------------------------				       
Installation Message					       
---------------------------				       
The user DOMAINNAME\informix does not exist. Please specify an 
existing user name.					       
---------------------------				       
OK							       
---------------------------				       
Normally the information for the new user 'informix' should be 
collected.						       
Therefore this user cannot exist on this system at this time.

You have a Windows 2000 domain style user, and a client        
computer that belongs to that domain.			       
Starting from IDS 10.00.TC6  when authenticating domain users, 
the only format accepted is				       
   DOMAINNAME\username					       
while							       
    username						       
always returns error -951.
(also reported as idsdb00153303 for IDS 11.10.xC2)

When IDS 11 is installed on a Windows Domain Controller on     
which the informix user does not exist the installation fails  
with the following error:				       
norolesep_createinfxuser 3 - Could not add the specified user  
to the group.						       
Although the Informix user and the Informix-Admin group get    
created, the Informix user is not added to the Informix-Admin  
group and not granted the required privileges to start IDS as a
service.						       
This problem is specific to the IDS 11.

DLL Hell Part 3 - DLL address clash problem in Windows Server 2003 SP2

shmat: [22]: operating system error
MapViewOfFileEx: w32ec=487 at nt_shm.c:663
Unable to attach to shared memory.
Invalid argument

Learn along with me

Webcast Tuesday - Securing Your Data

There's no doubt that information security, especially that of your database, is of ever increasing importance. Just pick up a newspaper or turn on the news and you'll hear about how yet another large company or organization has put thousands or millions of their constituents' private information at risk - and paid the penalties including loss of confidence.

• The impact of the latest government regulations on database choices
• What even small companies need to know about database security
• How to be best prepared for audits or regulators
• How to protect your data - at rest and while 'on the wire'

Chapter 1. Technology overview
Chapter 2. Role separation
Chapter 3. Auditing
Chapter 4. Securing data with SQL
Chapter 5. Client-server communication
Chapter 6. Server-server communication
Chapter 7. Security issues with Backup and Restore
Appendix A. Audit event mnemonics
Appendix B. PAM API and macros

Informix on Facebook

IBM Database Magazine Issue 1

• IIUG View - Informix Runs ahead - by Stuart Litel
• The IDS 11 Sysmaster Database, Part 2 - by Lester Knutsen (part 1 was here)

More components will be integrated into the various life-cycle stages IBM Data Studio manages in the coming year. Some will be components of the free download, others will be paid features. Here's what features are likely to be added in the near future.


• Rational Data Architect will become a fully integrated modeling component of Data Studio and will remain a paid feature
• The Data Studio Administration Console, which provides monitoring capabilities, will be a component of the free offering and is currently available as a technology preview
• Paid features for performance management, change management, query tuning, problem determination using pureQuery, and a test data generator are all likely additions in 2008

Visual Studio Add-Ins standalone download

Cheetah 2 Open Beta - download it now

Farewell DB2 Magazine