Other Issues

Highlights: Logo contest, Cool SMI queries, IDS Training, Chat wit the lab, Calendar

Welcome to the International Informix Users Group (IIUG) Insider! Designed for IIUG members and Informix user group leaders, this publication contains timely and relevant information for the IBM Informix community.

Contents:

1. Editorial...
2. Tell the world your Informix story
3. Tales from Informix's 25 years
4. IIUG Insider Logo contest
5. Denver 2005 Informix presentations downloads
6. Quick Tips: Just a few cool SMI queries - by Art S. Kagel
7. Note from the IIUG President
8. Southeast Informix Users Group full-day technical seminar
9. IDS Training
10. Informix Chat with the Lab: Upgrading from v7.x to v10
11. Event-driven fine-grained auditing with Informix Dynamic Server
12. FourGen and Gillani iDistribute available on IDS10
13. M Systems International, Inc. enters its 10th year in business with record sales
14. An outside view to the conference
15. Calendar of events
16. Developer corner
17. Useful Links
18. Closing and Credits

Editorial...

96% of our active users are receiving the IIUG Insider. It consolidates data from many sources and brings it to your desktop in a single document. But I would like it to be more then a collection of external data. It would be a much better newsletter if it contained your stories, requests and comments.

If you have a story, a technical article or a request, send it to gary@iug.org. This will add content that is valuable to all of us and cannot be found elsewhere.

Please do not hesitate to use this channel of communication to IBM. If you read through the April issue you can see that a letter by Ramon Ray the end of March was addressed by IBM management. But there is more to it the just an answer - it is a direct channel of communication between IBM management and the end user that puts IBM in touch with the pulse of the Informix user community.

Help us to make a better Insider.

Thank you.

Gary Ben-Israel
IIUG Insider Editor
IIUG Board of Directors

Tell the world your Informix story

Are you are an Informix Dynamic Server (IDS) user with a great story? Here's your chance to share your story, get your company some well deserved recognition as a savvy user of database technology, and promote Informix all at the same time. How? By nominating your company to be an IDS reference.

There are several ways you can be a reference. You can give quotes about your IDS experiences, you can do a complete case study about your IDS environment and business model or you can even participate in press releases and speak with reporters. References representing multiple industries, from small businesses to multinationals, and a variety computing environments are all welcome.

If you are interested in learning more about nominating your company and you already have legal/PR approval to be a reference for IBM, please send an email to woodsc@us.ibm.com.

Let's share your successes and spread the word about IDS!

Carolyn Woods
IBM Database Technology Awareness
IBM IIUG Board Liasion

Tales from Informix's 25 years

DB2 Magazine May 2005
Tales from Informix's first 25 years
by Stuart Litel

Over the years, I've noticed that virtually all Informix users will agree with the following statements:

• No database on the market is easier to administer or more reliable than Informix.
• No one ever bought Informix software for the advertising.
• If Informix had sold sushi, it would've called it "raw dead fish."
• In the old days, the number one job of an Informix DBA was to learn how to read the newspaper; today, it's to surf the Web.

A few years back I ran into an old friend who took over a full-time Informix position at a company where I'd done the original consulting. When I started consulting there, the company was doing about $10 million of business, with all the back-office accounting on a homegrown Informix 4GL solution. When it hired a full-time employee, it had grown to a $100 million; today, it's a household name and a $1 billion company.

When I last saw this friend, he told me he managed six employees: one Informix DBA and five Oracle DBAs. I asked whether the company was changing to Oracle. It turns out the entire company runs on Informix except for one purchased solution that runs on Oracle; that system needs so much database attention that it takes five people to keep it going.

For everyone who smiled at that story, here are more thoughts about why we all love Informix:

1. The database can always be online, even when doing a backup.
2. You don't have to turn the database off to add, alter, or drop a table.
3. If offers two-way bi-directional replication, without triggers or special SQL - the database simply does the replication for you.
4. Informix can scale from a single-user mom-and-pop shop implementation to handle millions of transactions daily. (I recently hosted a video presentation about a large Informix implementation, which you can see at http://www.watchit.com/informix.)
   

Recently, I met with some users of a different database software. One customer had an Informix-based solution the company wanted to replace with that "O"ther database. The user, an IT director in a world-famous city, told me, "We don't like to use Informix because there's nothing to do, it's for amateurs."

I held my tongue, but I'm letting it go now. I've heard of an Informix system that's been up for more than 1,561 days. How many systems "for amateurs" do you see that have been doing online transaction processing for more than 1,500 days (or even 150 days) without a restart?

Informix (even under IBM) has never had great marketing (the cold, dead fish approach), but it's getting better. The last nine months have been a breath of fresh air from IBM. Now it's time for us to stand up and be counted. We have something special.

I'm currently searching for the following:

1. Any company that gave up its Informix database for technical reasons.
2. Any person or company that doesn't believe Informix will be here for another 25 years. I'm taking bets, and my money is on a 50th anniversary.
3. Ideas about how to convince everyone that Informix can beat any database on equal terms, in speed, reliability, availability, and cost of ownership.

Are you wondering why I called this column "10-10-25"? It's a good year: IBM announced Informix Dynamic Server 10, it's the 10th anniversary of the International Informix Users Group (IIUG), and the 25th anniversary of Informix.

Stuart Litel is CTO of Kazer Inc. and President of the International Informix User Group.

IIUG Insider Logo contest

Send us a logo for the IIUG Insider and win an IIUG polo shirt!

The IIUG is pleased to announce a contest for a new logo for the Insider. The winner will receive credit for their design in the Insider and a new IIUG Polo Shirt (shipped to their doorstep).

The deadline for submitting entries is July 15. Please send your entry (or entries) in JPG format to gary@iiug.org.

Note: You must be an IIUG member to enter the contest. All entries become the property of the IIUG.

Denver 2005 Informix presentations downloads

For those who missed the Denver 2005 conference and for those who miss it.

We have a free download of all the Informix presentations in PDF format.

Just go to our web site: http://www.iiug.org and log in to the Member Area.

Or go directly to: http://www.iiug.org/cgi-bin/fglcl.cgi?membupd.

If you are not an IIUG member you have another good reason to join.

In the member are click on the "Software Downloads" tab and then on "IDUG 2005 Conference" tab. Don't let the title discourage you - these are Informix presentations.

Enjoy!

Interested in presenting for 2006? We are now accepting presentations for the 2006 IDUG / IIUG Conference in Tampa, Florida. Simply visit "http://www.iiug.org/conf for more information. All selected presenters will receive a free full conference pass for the Tampa conference.

Note: Your IIUG profile information must have been updated since Jan 1, 2005 to access the Software Downloads area. If not, please click on the "Profile" button in the Member Area to update the information.

Quick Tips: Just a few cool SMI queries - by Art S. Kagel

Identifying HP Loader populated tables

When you load a table with the HP Loader in express mode the table is marked with a bit until the server has been archived. If you try to replicate such a table before the archive completes the ER setup will fail and ER tends to get confused. Here's a query (thanks to John Miller III) to identify any tables in such a state:

SELECT trim(dbsname)||":"|| trim(tabname) AS database_table
FROM systabnames A, sysdbstab B, sysptnhdr C
WHERE trunc(A.partnum/1048575,0) = B.dbsnum
     AND A.partnum = C.partnum
     AND level0 < C.created
     AND bitval(C.flags, "0x8000" ) = 1;

Identifying temp tables

Ever wonder what temp tables are out there and how much space they are taking up in your server? Try this one.

SELECT n.dbsname AS database,
       n.owner AS owner,
       n.tabname AS temp_tabname,
       case
          when BITVAL(i.ti_flags, "0x0020") = 1
          then "System temp table"
          when BITVAL(i.ti_flags, "0x0040") = 1
       end AS temp_type,
       COUNT(*) AS num_fragments,
       SUM(i.ti_nptotal) AS total_pages,
       SUM(i.ti_nrows) AS total_rows
FROM systabnames n,
     systabinfo i
WHERE (BITVAL(i.ti_flags, "0x0020") = 1
       OR BITVAL(i.ti_flags, "0x0040") = 1)
  AND i.ti_partnum = n.partnum
GROUP BY 1, 2, 3, 4;

I leave it as an exercise for the reader to add dbspace and chunk information to this one.

Understanding sysindices

From the first SE versions through IDS 7.xx, index key information was stored in the sysindexes table. As convoluted as it was to understand how all those part<N> column entries worked, it was childs play compared to what replaced sysindexes in IDS 9.1. The sysindexes table was replaced by a view into the new catalog table sysindices. But there are only understandable rows in sysindexes for old style indexes which only contain table columns. Functional indexes are ignored by this backward compatibility aid. Ever wonder how to interpret the entries in sysindices (IDS 9.xx+) for functional and mixed key indexes? Here are the steps:

First, you have to pass the contents of the indexkeys column into the function "informix".indexkeyarray_out() in order to decode the entry. The returned string will be of the form 'keyspec, keyspec, ...' where 'keyspec' is either a column key specification or a functional key specification. Column key specifications and functional key specifications can be freely intermixed in the string since any key value in an IDS9 index can be either a column in the table or the result of some invariant function call.

Column type key specifications are of the form 'colno [op_class]' where colno is the colno from the syscolumns table (as in the older sysindexes part* entries if colon is negative it represents a descending key) and op_class is the opclassid used to index this column.

Functional type key specifications are of the form '<procid>(colno, colno, ... ) [op_class]' where is the identifier in sysprocedures of the function used to return this key entry, the colno list are the arguments to the function, and op_class is the opclassid used to index this key. Similarly to column key specs, if the key entry was declared DESCending then the colno of the first argument to the function will be negative.

Note from the IIUG President

Recently I got two e-mails from IIUG Members I met at the IDUG / IIUG Conference in Denver, CO. Both e-mails were in response to some problems with IBM. One e-mail was about a support situation and the other was about a company that had software purchase questions.

If you have any questions that you cannot get answered by IBM personnel send them to me. The same goes for suggestions or complaints. I promise to return your email within ONE business day. I can also get to the right people inside of IBM if you have hit any brick walls or just don't know where to go for answers. Also, please don't limit the e-mails to problems, as I also like to get any compliments about IBMers (and there are many more compliments deserved), but basically if you are stuck with ANY non-technical problem, please go to http://www.iiug.org/president and send me an e-mail. I cant always solve them (although I can most of the time), but I will always answer the e-mail.

And please don't limit these to IBM. I also like to hear suggestions for the IIUG, problems, and complaints too...

Stuart Litel
President
Intl. Informix User Group

Southeast Informix Users Group full-day technical seminar

The Southeast Informix Users Group (SEIUG) will host a free full-day technical seminar on July 14, 2005. Well known speaker and teacher Mark Scranton will conduct a Technical Deep Dive of new features in IDS 10. The seminar will run from 9:00 AM until 4:00 PM in the state-of-the art learning facility at the Home Depot Store Support Center in Atlanta. Lunch will be provided.

Participation is free, but pre-registration is required. Visit the SEIUG Web site "http://www.iiug.org/~seiug/ where you will find a partial list of topics that Mark will cover and a Web form for easy registration.

Please join SEIUG on July 14th for the first session of this kind in the Atlanta area on IDS 10 by one of the premiere Informix experts!

Walt Hultgren
President, Southeast Informix Users Group
seiug@iiug.org -- http://www.iiug.org/~seiug/

IDS Training

Now available, the latest release of Informix Dynamic Server (IDS) - Version 10.0 - IBM's flagship database for industrial-strength, embedded computing. IBM IDS V10.0 delivers outstanding performance, reliability, scalability and manageability for enterprises and workgroups.

At IBM, our goal is to help you be successful with your IBM Software Solution. We feel it is essential that you build the skills necessary to configure, implement and get return on your investment in Informix Dynamic Server Version 10.0. The ultimate measure of our training success is your self-sufficiency with our products.

DB2 Information Management Training Services has been involved with IDS 10.0 since beta and have been developing curriculum for the release. Our seasoned instructors understand the differences between early releases and version 10.0 and can help make your transition successful.

The following courses have been updated and enhanced for the new release:

The following courses will be updated late 2005:

To ensure a smooth transition to IDS V10.0, we have scheduled multiple sessions of our key updated courses throughout the country:

For more detailed information, or to enroll in training, please visit IBM Informix Education Services at:

http://www.iiug.org/url/ibm_education.html

Informix Chat with the Lab: Upgrading from v7.x to v10

Our next chat will address the topic of migration. We will discuss how to prepare for the migration using the pre-migration checklist, walk through a migration scenario and discuss when you can revert from a migration. Finally, we will review a customer success story.

Our speakers are:

• Jerry Keesee, Director of the Informix Database Development
• Srini Bhagavan, Architect, Information Management

Business Partners, Customers and IBMers RSVP for this conference call via the Web:

https://ww4.premconf.com/webrsvp/register?conf_id=4980342

Charts for the call will be available July 12th at the following URL:

http://www.iiug.org/url/charts_13jul.html

Wednesday July 13, 2005
11 a.m. Eastern
10 a.m. Central9 a.m. Mountain
8 a.m. Pacific
For 90 minutes

Save the date: Our next Chat will be Wednesday, August 3, 2005 on Performance Tuning for v10.

Replays of previous Chats may be found at:

http://www-1.ibm.com/partnerworld/pwhome.nsf/weblook/eac_index_biz.html

Your comments and suggestions for this series are always welcome at:

lspina@us.ibm.com

Event-driven fine-grained auditing with Informix Dynamic Server

Jacques Roy
Member of IBM's worldwide sales support organization
IBM 21 Oct 2004

Learn how to use triggers to implement fine-grained auditing. This article covers the use of the datablade API to generate auditing events from transactions. It also introduces the new feature of trigger introspection that allows you to create a generic auditing function that can be applied to any table. The article comes with example code that implements the solutions discussed.

Informix® Dynamic Server (IDS) 9.x allows you to generate events out of the execution of triggers through the use of callback functions. With the update to IDS 9.40 made available in February 2004, it is now possible to write a generic function that can generate an audit trail of the information manipulated through SQL and captured through trigger. This article describes how these facilities can be use in different context to generate auditing records.

http://www.iiug.org/url/fg_audit.html

FourGen and Gillani iDistribute available on IDS10

Gillani recently released FourGen CASE Tools and iDistribute v8.1/8.2 to run on Informix Dynamic Server v10.0. IDS 10 will provide additional features like, Improved query performance, on line index creation/deletion, new true single user mode for DBA's and column level encryption among many other benefits. These database improvements will provide FourGen CASE Tools and iDistribute users with improvements in transaction processing which will result in better overall performance compared with previous IDS releases.

http://www.gillani.com/index.htm

M Systems International, Inc. enters its 10th year in business with record sales

M Systems International, Inc. entered its 10th year in business in May 2005. Based in Research Triangle Park (Raleigh-Durham), North Carolina, USA, the systems integration and software development firm has shown consistent revenue and growth over the years, even during and after the economic downturn of 2001. M Systems was an Informix partner until Informix Corporation was acquired by IBM in 2001, and has been an IBM Business Partner since.

Founded in 1996, M Systems owes its success to a relentless quest for satisfying customers and a procedure oriented team. Founder, President, and Chief Technology Officer Halit ("Hal") Maner is a former IIUG Board member. Maner, himself a software engineer and former manager of systems and programming for a major international industrial firm, has over 20 years of industry experience, 13 of which involved Informix technology. He has a diverse education and work experience that started in his native Istanbul, Turkey, and has taken him through French, German, and American educational and commercial organizations.

So how did this small firm, with revenues of over one million dollars in 2004, manage to survive the years of the dot-com bust and slow economy? "M Systems owes its success to its principle based approach and its employees and clients", says Maner. "We have a team that has been working together here for many years and turnover is rare. We enthusiastically take on and deliver on-time, within budget solutions for our clients. Not only do we develop new solutions, but also we provide long-term, on going support at a good value." M Systems specializes in mission critical database application development/support as well as database and network administration.

M Systems' clients range from a large, multi-national firm for whom it designs its mission-critical engineering and order processing systems, all running Informix/IBM technology, to a growing reinsurance broker for whom it develops and supports specialized claim processing software based on Java technology, to a well known university for whom M Systems provides network administration and systems integration services involving Microsoft platforms. M Systems typically will go into a client, find out their needs, define the deliverables and produce for them a detailed analysis and design of the solution to be developed. "Our typical agreement includes the support and maintenance of the systems that we design. We guarantee our new systems to work according to specifications, or we fix it at no charge. Our goal is to provide our clients with the systems that improves their customer service capabilities, reduces their costs and improves their responsiveness while enhancing their productivity. We allow them to extend the values of their applications and to minimize their cycle-time and costs. When we've done our job right, our customers are better able to focus on their core competencies."

Some of the assignments M Systems is most proud of actually have nothing to do with glamorous new development. They involve solving an urgent problem or delivering a much needed add-on. Maner says his team would "love to have a shot at resolving the most challenging technical and business problems of their clients and prospects."

In 2003, M Systems launched its Snowman Software division which develops and markets packaged software. Its first product, called DaySchool, is an easy to use and affordable school administration package specifically developed for K-12 independent schools. The product is being adopted by the independent school community at a fast pace and Snowman Software now has clients in several states in the USA. Maner says that even though the product was designed for small office workgroups at schools, the same sound design and software development principles for the most mission critical software were utilized. The product was also designed for worldwide use with support for multiple languages. We are getting inquiries from all over the World almost on a daily basis, Maner says.

An outside view to the conference

Since Gary took over the Insider, I was pretty silent in the community. As we got back from Denver, I wanted to briefly come back to you, shortly, with a few tips and tricks I gathered here and there.

My interests were mainly around increasing developers' productivity and best practices for DBAs, as we do not have an in-house DBA per se.

Here is what I grabbed.

Keep configuration files under source control. Hey! This is clever, you keep an historical log of all modification, if you use CVS and WinCVS, you can even use the graphing functionality of the tool to track the history and diff files (see http://www.wincvs.org/).

Naming convention should include instance's name in the file name. People working with me know I am pretty painful on naming convention and I really think this idea (from Jonathan Leffler) is a great! When you run several instances on the same box, you can sure have a well organized directory structure, however, if you want to "locate" or "ps" things, you'll get to the right file immediately.

With the web business, security is rather critical. The new mechanism for "encryption of data over the wire" is rather cool. You can even specify a key rotation on the AES algorithm. Believe me, eavesdroppers will have quite a few headaches. On a best practice side, I guess most of us have root running "oninit" during the boot process of the machine. Why? Let's "pseudo" to Informix before, nope?

Two great things about Unix systems are symlinks (symbolic links) and environment variables. Windows really understands the latter. Jonathan Leffler also suggested to combined both to have a more flexible architecture, so on his boxes INFORMIXDIR points to a symlink, not to a full directory. Pretty clever when you need to maintain the box or plan upgrades...

I hope those tips are going to be as useful to you as they are to me. There are plenty of other things I learned at the conference, but I am not the Insider editor anymore, so my space is limited... I hope we will see each other in Tampa next year.

JGP
jgp@iiug.org

Calendar of events

Developer corner

Please visit our site for new, refreshed content:

http://www.ibm.com/developerworks/db2

Feature article:

Schema evolution for Informix Enterprise Replication
http://www.iiug.org/url/replication.html
This new feature allows the DBA to perform certain tasks while Enterprise Replication is running, with no downtime for your clients.
Informix Dynamic Server

Useful Links

In response to your input, we have created a page on the IIUG web site containing all the links we used to include. Please find it at: http://www.iiug.org/quicklinks.html

Closing and Credits

The International Informix Users Group (IIUG) is an organization designed to enhance communications between its worldwide user community and IBM. IIUG's membership database now exceeds 25,000 entries and enjoys the support and commitment of IBM's Data Management division. Key programs include local user groups and special interest groups, which we promote and assist from launch through growth.

Sources:	IIUG Board of Directors IBM Corp.
Editors:	Gary Ben-Israel Stuart Litel Jean Georges Perrin
For comments, please send an email to gary@iiug.org.