Washington Area Informix User Group Newsletter


Volume 7, No.3 - July 1997

Servicing the Informix User Community for Seven Years


Highlights of This Issue

The Informix FAQ, by Kerry Sainsbury

The XTREE command, by Kevin Fennimore

Concurrency and Locking, by Ron Flannery

Using SQL to Generate SQL (DBA tricks and tips), by Lester Knutsen

Informix-Related Internet Resources, by Walt Hultgren

FREE GUI DBA Administration Tool - Informix Product Announcement


Next Meeting Agenda - September 17, 1997

Magic Software will be demonstrating Magic, a rapid application development tool that replaces the coding cycle with a high level, totally table-driven programming environment that works with Informix databases.

Creating Web Driven Web Sites, by Seth Grimes, will examine architecture and technology options for creating database driven web sites. He will compare approaches for template and dynamic Web pages, discuss applications in cyberpublishing and electronic publishing, review products and scripting languages, and outline performance and security issues. Seth is Director of Server Technologies at Magnet Interactive Communications and has designed and developed sites using Informix technology for Federal Express, Mercedes-Benz, Nissan, and Radisson Hotels.

Date and Time:		September 17,  9:00 a.m. to 12:00 noon
Location:		Informix Software, Inc.
			8065 Leesburg Pike, Suite 600, Vienna, VA 22182

The meeting is open to everyone. Please RSVP to 703-256-0267, ext. 4, so we can keep an attendance count.


This is a Special Issue of the WAIUG Newsletter for the Informix Worldwide User Conference. In addition to the members of the Washington Area Informix User group, it is being sent to members of the Mid-Altantic Informix User Group in Pennsylvania, and the Michigan Informix User Group.


Newsletter Sponsorship

The user group has been supported by many companies over the years. We would like to thank the following companies for sponsoring this issue:

Advanced DataTools Corporation
Business Systems Support Group
Magic Software Enterprises
Summit Data Group
Technology Investments, Inc.
UCI Consulting, Inc.

New WAIUG Web Site

We are moving our user group web site to the web server maintained by the International Informix Users Group. The new URL is :

http://www.iiug.org/~waiug//


Elections to the Board of Directors

At our September meeting we will be holding elections to the WAIUG Board of Directors. The Board is composed of volunteers who plan our activities and work hard at putting them together. If you are interested in volunteering to be on the Board, or would like to nominate a member, please contact the Secretary of the WAIUG, Sam Hazelett at 703-277-6882.


Benefits for Members

In addition to this newsletter and our local activities, there is another reason to be a member of the Washington Area Informix User Group. All members automatically become members of the International Informix User Group. Some of the benefits this includes are discounts to the Informix World Wide User Conference, and full access to the members-only section of the IIUG Web Pages. Other discount programs are being worked on as well. Have you renewed your membership for 1997? Membership dues are $20.00. We also have a Corporate Membership Program. Membership forms are at the back of this issue. For more membership information, please call our Membership Director, John Petruzzi, at 703-490-4598.


The Informix FAQ (Frequently Asked Questions)

by Kerry Sainsbury

This is to let everybody know that the latest release of the Informix FAQ (version 2.8, March 1997) has now been released.

0 FAQ Facts

0.1 What's new in this release of the FAQ?

I've tested all of my links to other Web sites, and either fixed or removed those which were broken.

The plain-text version of the FAQ has been abandoned. If you don't have access to a Web Browser it's time you sought therapy. Seriously though, if this is an issue let me know -- I'll consider bowing to public pressure.

A major change is the inclusion of a new section: 12 Third Party Products to help folks track down non-Informix (or ex-Informix) products that are tough to find any other way

NEW!
4.8 	Stored Procedure Language (SPL)? is an entirely new section dedicated to SPL 
4.8.1 	What is SPL? 
4.8.2 	Advantages of SPL 
4.8.3 	Disadvantages of SPL 
4.8.4 	Can I access the SQLCA via a Stored Procedure? 
4.8.5 	When should I use a SP rather than 4GL? 
5.1 	How can I speed up my SQL commands? has couple of new subsections, thanks to Dennis Pimple
(can't believe we didn't have "Use Indexes"  anywhere!): 
5.1.4 	Avoid "OR" and 
5.1.5 	Use Indexes 
5.11 	Is there *really* no "Upper" function!? includes some stored procedures from June Tong to
provide this LONG OVERDUE function (Thanks June) 

6.18 	Recovering broken (or missing) indexes in SE, thanks to Rudy Fernandes. 
10.2 	Is there a Perl interface to Informix? has two new sections
10.2.1 	A simple interface 
10.2.3 	DBD::Informix 
12 	Third Party Products is new and exciting with 
12.1 	Hyperscript Tools 
12.2 	4GL Clones 
12.2.1 	Querix 
12.2.2 	F4GL Compiler 
12.3 	Fourgen-CASE 
12.4 	ODBC Drivers 
12.5 	JDBC Drivers 

IMPROVED!
1.2 	The Informix Time-line has been updated to include the "Universal Server". 
10.2.2 	isqlperl now mentions that it is not compatible with Perl 5. 
10.7 	Is there any Web Server software available? has been updated to include some more info,
including opinion on Informix's freeware offering, and a link to Jeff Rowe's hugely useful
web site. 
11.5 	What is Informix-Link? has had a bit of Informix-bashing removed -- there is now a search
function on the bugs database! 
What was "11.8 Are there any 4GL clones available?" has been moved to
12.2 	4GL Clones. 

0.2 How can I get the latest copy of the FAQ?

0.2.1 anonymous ftp

The latest version of this document can be retrieved via anonymous ftp from New Zealand at ftp://ftp.kcbbs.gen.nz/pub/informix/faq/ifaq.tar.Z or from the USA at ftp://ftp.iiug.org/pub/informix/faq/ifaq.tar.Z

If you've no knowledge of Unix, and have never heard of .Z or .tar formats, just get a copy of WinZip from http://www.winzip.com it's a very sexy Windows95/NT program which can decipher these for you.

0.2.2 World Wide Web

USA:

http://www.iiug.org/techinfo/faq/informix.htm (The International Informix User Group's site)

http://www.garpac.com/informix/ (Contact clay@garpac.com (Clay Irving))

EUROPE:

Turkey: http://www.metu.edu.tr/METU/CC/OL/INFORMIX/ (Contact Ayhan Ergul, who also invites you to visit the main URL for the Middle East Technical University (Ankara, Turkiye) at: http://www.metu.edu.tr)

AUSTRALASIA:

Australia: http://www.dataspace.com.au/faq/informix.html (Contact Rafal Czerniawski (rafal@zip.com.au) and visit his main page at http://www.dataspace.com.au/resource.html

Table of Contents

 0 FAQ Facts: 
      0.1 What's new in this release of the FAQ? 
      0.2 How can I get the latest copy of the FAQ? 
           0.2.1 anonymous ftp 
           0.2.2 World Wide Web 
           0.2.3 comp.databases.informix 
           0.2.4 email 
           0.2.5 Compuserve 
      0.3 Who do I complain to? 
 1 Frivolous background stuff: 
      1.1 Who, or what, is Informix? 
      1.2 The Informix Time-line 
      1.3 How do you pronounce "Informix"? 
      1.4 What products to Informix sell, and what do they do? 
           1.4.1 Engines 
           1.4.2 Tools 
      1.5 What're the differences between Turbo/Online/SE/4GL/RDS/ESQL... 
           1.5.1 Online vs Standard Engine. 
           1.5.2 Online vs Turbo. 
           1.5.3 4GL vs 4GL-RDS. 
           1.5.4 4GL vs 4GL for Windows vs 4GL/GX 
           1.5.5 ESQL/C vs 4GL 
           1.5.6 HyperScript Tools vs WingZ 
      1.6 What are TPC-C benchmarks? 
      1.7 What are the meaning of the acronyms used in this newsgroup? 
      1.8 New Features by product (or: Why you should upgrade) 
 2 Not so frivolous background stuff: 
      2.1 Who are we - comp.databases.informix 
      2.2 How do I join the list or unsubscribe? 
      2.3 What's the Consensus on Job Postings? 
      2.4 Other User Groups 
      2.5 Other Mailing lists 
 3 Tell me More about... 
      3.1 The Integrated Debugger 
      3.2 4GL for Windows 
      3.3 I-Star/I-Net 
 4 Common Questions about tools: 
      4.1 Informix-4GL 
           4.1.1 I keep getting -4518 errors! How can I fix this problem? 
           4.1.2 How can I write to a flat ASCII file? 
           4.1.3 How can I read from a flat ASCII file? 
           4.1.4 How can I force INPUT ARRAY onto the next line? 
           4.1.5 How can I make a screen dump? 
           4.1.6 Any undocumented features that I could play with? 
           4.1.7 Performance tips (Code Optimization) 
           4.1.8 Performance tips (Design Optimization) 
           4.1.9 How can I use #DEFINE-style constructs, like C? 
           4.1.10 Why are RUN's return codes screwy? 
           4.1.11 Are there any RDS compatibility problems? 
           4.1.12 Can you link Compiled I4GL functions into a custom P-code runner? 
           4.1.13 How can I check if any rows exist, without OPEN-FETCH-CLOSE? 
           4.1.14 How do you include Printer control characters in an Informix Report? 
           4.1.15 How can I dynamically change Report OPTIONs at runtime? 
           4.1.16 My .err files are incomplete 
           4.1.17 Can I run a Stored Procedure from 4GL? 
           4.1.18 What problems will I have upgrading from 4.x to 6/7.x? 
           4.1.19 Tips on running 4GL via Cron 
           4.1.20 Will Informix explode in the year 2000? 
           4.1.21 How can I use GNU C (gcc) with 4GL? 
           4.1.22 Why does 4GL generate invalid dates when it performs arithmetic with months? 
      4.2 ESQL/C: 
           4.2.1 Why have Informix duplicated stleng, stcopy() etc in ESQL/C? 
           4.2.2 How can I get a list of databases? 
           4.2.3 Can I use ESQL with C++? 
           4.2.4 Can I use ESQL with C++ under 6.0? 
      4.3 ACE: 
           4.3.1 How do I call a custom C function from ACE? 
           4.3.2 How can I create unique output filenames? 
      4.4 WingZ 1.0: 
           4.4.1 How can I make DataLink not pop-up 'no rows found'-style dialog boxes 
      4.5 Hyperscript Tools: 
           4.5.1 How do I run a Hyperscript Worksheet directly from a Windows Icon? 
           4.5.2 Which is better Datalens or built-in SQL? 
           4.5.3 Who did Informix sell Hyperscript to? 
      4.6 Informix-4GL for Windows 
           4.6.1 How can I get a UK Pound sign as my MONEY code? 
      4.7 Informix-NewEra 
           4.7.1 What 4GL to NewEra conversion tools exist? 
           4.7.2 Arrgh: -4518 in NewEra too! 
           4.7.3 How generate 4GL from .WIF without Window Painter? 
           4.7.4 What's the performance difference between base types and object types? 
           4.7.5 Can't run New Era 2.0 applications under Win 95 
           4.8 Stored Procedure Language (SPL)? 
           4.8.1 What is SPL? 
           4.8.2 Advantages of SPL 
           4.8.3 Disadvantages of SPL 
           4.8.4 Can I access the SQLCA via a Stored Procedure? 
           4.8.5 When should I use a SP rather than 4GL? 
 5 Common SQL Questions: 
      5.1 How can I speed up my SQL commands? 
           5.1.1 SELECTs using sorts or joins 
           5.1.2 SELECTs using NOT IN or NOT EXISTS clauses 
           5.1.3 Use of SET EXPLAIN ON 
           5.1.4 Avoid "OR" 
           5.1.5 Use Indexes 
      5.2 How can I use the results of an SQL query in a shell script? 
      5.3 Why can't I create a view with a calc column 
      5.4 How can I access data outside the current database? 
           5.4.1 Using Standard Engine 
           5.4.2 Using Online 
      5.5 How can I SELECT rows in detail-table missing a matching header-table row 
      5.6 How can I SELECT a fraction (say 10%) of the database 
      5.7 I want to CREATE a TEMP TABLE LIKE permanent table 
      5.8 How to convince the optimizer to use indexes YOU want 
      5.9 How to alter the next SERIAL number 
      5.10 How can I speed up SELECT COUNT(DISTINCT)? 
      5.11 Is there *really* no "Upper" function!? 
 6 Common Questions about the Engine: 
      6.1 What're some pointers for configuring Online? 
      6.2 What do I need to use MS-Access/Visual Basic with my Informix database? 
      6.3 THIS SPACE INTENTIONALLY LEFT BLANK 
      6.4 What error messages are caused by locking problems? 
      6.5 What should I use instead of kill -9? 
      6.6 How can I tell what version I'm running? 
      6.7 Locking in pre-6.0 engines 
           6.7.1 What is the "Twin Problem"? 
           6.7.2 How does this work and what are the ramifications? 
      6.8 Space reclamation 
      6.9 Online Disk Fragmentation 
      6.10 Why do UPDATE STATISTICS? 
      6.11 How do we get serial field starting values for a table 
      6.12 Can I count the number of rows SELECTed, without using SELECT COUNT? 
      6.13 Is the number of rows processed in the SQLERRD structure accurate? 
      6.14 Interpreting SysColumns.ColType and SysColumns.ColLength 
      6.15 More tuning suggestions for Online 
      6.16 DATETIME, INTERVAL and other tricks 
      6.17 Reading SE Audit Trails 
      6.18 Recovering broken (or missing) indexes in SE 
 7 Connectivity Questions 
      7.1 I-Net/Star Security 
      7.2 I-Net is slow... Help! 
      7.3 Intermittant problems with I-NET vs SCO 
      7.4 I-NETgives -932 errors and complains about getservbyname (WSANO_DATA) 
 8 DBA Issues 
      8.1 Performance - make it go faster! 
      8.2 Should I use Online DSA mirroring? 
      8.3 Questions about NFS mounted databases 
      8.4 How can I run tbtape from a cron job? 
      8.5 Calculating extent sizes 
      8.6 How can I list a table's dbspace? 
      8.7 How should I use the "informix" user & group? 
      8.8 How can I measure CPU time? 
      8.9 Using ISQL/DBACCESS to optimize SELECTs 
      8.10 dbexported & imported - now runs like a dog! 
 9 Configuring things 
      9.1 Environment variables 
      9.2 How do you read termcap's ZA string? 
      9.3 Where can I get a termcap for ...? 
      9.4 How do I define function keys 37 thru 64? 
      9.5 How can I run Informix Online under Solaris 2.4? 
 10 Gimme something for nothing, what's available? 
      10.1 Informix Archive Sites 
      10.2 Is there a Perl interface to Informix? 
           10.2.1 A simple interface 
           10.2.2 isqlperl 
           10.2.3 DBD::Informix 
      10.3 What about Mac Client Software? 
      10.4 Is there an RDS de-compiler available? 
      10.5 What about a GUI interface? 
      10.6 Recommended free software 
      10.7 Is there any Web Server software available? 
 11 Miscellany 
      11.1 What machines get ported to first? 
      11.2 I've finished the FAQ, what else can I read? 
      11.3 Is there any Informix info on The Web? 
      11.4 List of major bugs fixed in the various versions 
      11.5 What is Informix-Link? 
      11.6 Does Informix run under Linux? 
      11.7 Tell me more about product X, Y, or Z 
 12 Third Party Products 
      12.1 Hyperscript Tools 
      12.2 4GL Clones 
           12.2.1 Querix 
           12.2.2 F4GL Compiler 
      12.3 Fourgen-CASE 
      12.4 ODBC Drivers 
      12.5 JDBC Drivers 
 Appendices 
      APPENDIX A Temporary String Space (TSS) 
      APPENDIX B Sample 4GL Routine to read ASCII files 
      APPENDIX C Sample C Routine to read ASCII files 
      APPENDIX D C Routine to give a list of databases 
      APPENDIX E How to interpret syscolumns (in English) 
      APPENDIX F How to interpret syscolumns (in 4GL) 
      APPENDIX G GUI builders summary 
      APPENDIX H Shell to list Informix versions 
      APPENDIX I DATETIME, INTERVAL and other tricks 
      APPENDIX J Dynamically changing Report OPTIONs 
      APPENDIX K Calculating extent sizes 
      APPENDIX L dbschema with extents 
      APPENDIX M How to put Blobs into Reports 
 Credits 


Kerry Sainsbury, kerry@kcbbs.gen.nz
THE INFORMIX FAQ v2.8 March 1997
Quanta Systems, Auckland, New Zealand
Work: +64 9 377-4473, Home: +64 9 279-3571
http://www.iiug.org/techinfo/faq/
ftp://ftp.iiug.org/pub/informix/faq
ftp://kcbbs.gen.nz:/pub/informix/

The XTREE Command

by Kevin Fennimore

There is a little known and even littler used command in Informix DSA version 7.x called xtree. This command, as the x implies, is an X/Windows utility which shows, in a graphical format, what the Informix engine is doing while processing a select statement. This utility displays the query in a B-tree type of format with each node representing a specific operation in the query. It also gives you various information about how many records are being read and how fast.

To start, you must be running on an X/Windows terminal or X/Windows terminal emulator. Some environment variables that must be set are DISPLAY and SHLIB_PATH. The SHLIB_PATH must be set to point to the shared libraries for X/Windows. In my HP-UX environment this variable was set as follows:

$ export SHLIB_PATH=/usr/lib/X11R5:/usr/lib/Motif1.2
See your system administrator if you have trouble with these environment variables.

The easiest way to examine a select in xtree is to run it through dbaccess. This gives you the ability to control when the query starts and lets you turn on set explain in order to see the order in which the engine is reading from the tables. Once you have the query ready to run in dbaccess, you are ready to start up xtree. Since xtree is in the Informix bin directory it can be run just like onstat, etc. The following is an example:

$ xtree &
Once XTREE is running you have three options/buttons across the top of the screen:

  1. Select Session - Use this button to specify the session id that you are interested in monitoring.
  2. Quit - Use this to exit xtree. Closing the window sometimes leaves the xtree process running and consuming processor time. Using the Quit is the safest way to exit.
  3. Help - This option gives very simple text help which explains a little about what is on the xtree screen.
To examine a query, run dbaccess, connect to a database and get the session id of your dbaccess process. Go to the xtree window and click on "Select Session". You will get a window that looks like the following:

If your session id is listed, click on it to highlight it and then click on "Accept". If it is not listed you must enter it in the box provided. Your cursor must be on the box in order to type in a session id.

Now that we've selected a session id, let's run the following query:

select *
from ps_ledger
order by deptid

Since this is a simple query from one table we do not need to examine the set explain output in order to determine the order that the engine is reading from the tables. For multi-table joins, this is necessary because xtree does not display any table names. Once the query starts to run, the xtree window will change and display something like the following:

What we are seeing in this example is that the scan, which is sequential, is reading 4,730 rows per second. At this point in time, 111,352 rows have been read so far and passed to the filter node. In our query, there are no filters so the filter node is reading at about the same speed as the scan node (4,725 per second) and has passed up almost the same number of records to the sort node (111,343). The sort node has not read any records because the sort is done after all of the records have been read.

Now let's consider a more complex query:

SET EXPLAIN ON;
SELECT
A.DSCNT_DUE_DT, A.SCHEDULED_PAY_DT, A.PYMNT_GROSS_AMT,
B.GROSS_AMT_BSE, A.DSCNT_PAY_AMT
FROM PS_PYMNT_VCHR_XREF A,
        PS_VOUCHER B,
        PS_VENDOR C,
        PS_VENDOR_ADDR D,
        PS_VENDOR_PAY E
WHERE
 A.BUSINESS_UNIT = B.BUSINESS_UNIT AND
  A.VOUCHER_ID = B.VOUCHER_ID AND
  A.REMIT_SETID = C.SETID AND
  A.REMIT_VENDOR = C.VENDOR_ID AND
  A.REMIT_SETID = D.SETID AND
  A.REMIT_VENDOR = D.VENDOR_ID AND
  A.REMIT_ADDR_SEQ_NUM = D.ADDRESS_SEQ_NUM AND
  D.EFF_STATUS = 'A' AND . . .

The query continues but in the interest of space, I'll cut it off here. The set explain output indicates that the order that the tables are being read is: ps_pymnt_vchr_xref, ps_vendor_pay, ps_vendor, ps_vendor_addr and ps_voucher. Knowing this order is important since xtree does not label the nodes with table names. Now let's look at the xtree screen keeping in mind that the tree is read from bottom left to top right:

The nodes continue up until we hit the fifth table at the very top. Here are some of the things we can get from this picture:

This seems to indicate that the engine is satisfying this query in an efficient manner. The first table seems to be the driving table with the other tables just providing supplemental data. We also see that the speed of reading from the first table is not very fast (67 records per second). One reason for this could be that for each record read from the first table, four additional tables must be read from. Although it was not shown due to space restrictions, the other tables have correlated sub-queries which could slow down the scans of those tables. In addition, this could also be an indication that the first table's index has a large number of extents which slow down the scan or that the disk containing the index is slow because of system activity.

In conclusion, xtree is a nice tool which can show you a lot of information about a running query. It is limited in that it can only look at select statements and that it does not display information about subqueries. It also examines shared memory instead of connecting to the Informix server so that performance is not impacted. Unfortunately, the server can unexpectedly change some of the information that xtree is examining which causes xtree to get a little confused and quit. This is not a problem and xtree can just be started up again. As you use xtree you will be able to see a variety of uses which were not mentioned in this article. Good luck with it and have fun.

Kevin Fennimore

1-888-UCI-FOR-U


Concurrency and Locking

By Ron M. Flannery

In a multi-user system, you'll almost certainly need to have more than one user access the same row(s) of data at the same time. This is especially true in OLTP systems. The ability to allow multiple users to access the same rows of data is known as concurrency. Concurrency control is essential for performance and integrity. Informix handles concurrency through various strategies, which will be described in this article.

Locking

Informix uses different locking strategies to control concurrency. It is very important that you consider these locking strategies when designing any multi-user application. Locks can be placed on these types of objects: databases, tables, pages, and rows. Here are the three types of locks that can be applied to these objects:

1. Shared. A shared lock is essentially a read-only lock on an object. It allows you to read the object and also allows others to place shared locks on the object. No one can update the object when a shared lock exists on it.

2. Exclusive. An exclusive lock gives the user exclusive access to the object. This type of lock is used when the user is going to change the object. No other locks are allowed when an exclusive lock exists. If an exclusive lock is attempted and a shared lock exists, an error will occur if the lock is not released before the wait time expires (see "lock wait times" below).

3. Promotable. A promotable lock is a hybrid between a shared and exclusive lock. This lock only applies to rows. The user is basically saying "I want to update this row, but not right now." This type of lock can be placed when shared locks already exist on the data. When the user fetches the row, a shared lock is obtained. When he is about to update the row, the lock is upgraded to an exclusive lock and no other lock (not even shared) can exist.

Here is a description of how each of the objects interact with locking:

Database - Simply opening a database (with a "DATABASE dbname" statement) places a shared lock on the database. To exclusively lock a database, use the "DATABASE dbname EXCLUSIVE" command. An exclusive lock is automatically placed on a database by commands such as dbexport and onunload.

Table - You can lock a table with the LOCK TABLE command. A table can be locked in shared mode with LOCK TABLE tabname IN SHARE MODE or exclusively with LOCK TABLE tabname IN EXCLUSIVE MODE. A table level lock is automatically done by SQL commands such as ALTER TABLE, CREATE INDEX, and RENAME COLUMN.

Page and Row - Page or row level locking is done automatically when a row is locked. The scope of the lock (page or row) is determined by the definition of the table itself. The CREATE TABLE and ALTER TABLE statements allow you to specify "lock mode." Tables defined as "lock mode page" lock the whole page containing the row while tables defined as "lock mode row" only lock the specific row.

Index key - Informix automatically does an index key lock in certain situations. An index key lock is done to preserve an index value that does not exist. This is done when a user might be adding or deleting a row; Informix must wait until the update is completed and thus preserves the index key value. This is done "behind the scenes" but is something you should know.

Lock wait times

You can define how long a user process can wait for a lock by using the SET LOCK MODE command. This can be done interactively (through dbaccess or isql), in a program, or SQL file. The syntax is:

SET LOCK MODE TO NOT WAIT; (this is the default behavior) - or -
SET LOCK MODE TO WAIT [seconds];

If you have used the SET LOCK MODE TO NOT WAIT command, statements trying to obtain a lock will immediately return an error if the lock can not be granted.

The SET LOCK MODE TO WAIT command will wait until the requested lock becomes available. If seconds is specified, the process will wait that many seconds for the requested lock to become available. If seconds is not specified, the process will wait indefinitely, so be careful.

A deadlock can occur when more than one user process is trying to perform certain locks on the same object (table, page, etc.). It happens when all the processes have used SET LOCK MODE TO WAIT. When the transaction involves only one database server, Informix detects this condition immediately and reports an error. If the transaction involves more than one database server, a true deadlock can occur unless the Online administrator has set the ONCONFIG parameter DEADLOCK_TIMEOUT on the involved servers.

Rules of locking

To maintain consistency of a database, locks need to follow certain rules. If a lock can not be granted because it breaks the rules, an error will occur. The same rules apply to each object that can be locked (database, table, page, row):

1. If the lock being requested is shared (a shared lock or the initial step of a promotable lock) and there are no exclusive locks, the lock is granted.

2. If the lock being requested is exclusive (an exclusive lock or the update step of a promotable lock) and there are no shared or exclusive locks, the lock is granted.

3. If the lock can not be granted immediately, the process will wait the amount of time specified with SET LOCK MODE. If the lock can not be granted in this amount of time, an error will be reported to the calling process.

Logged and non-logged databases

An Informix database can be logged or non-logged. In a logged database, every transaction (insert, update, and delete) is tracked and must be committed to the database. In a non-logged database, this is not the case.

A logged database enables transactions by using the "BEGIN WORK COMMIT WORK" syntax in programs and SQL files. If a BEGIN WORK is not issued, a "singleton transaction" is performed, meaning that a transaction is automatically done. A logged database is necessary in applications that require a series of commands to complete for an update to be successful. A logged database does, however, require more overhead in locking and transaction logging.

In a logged database, you need to declare all cursors WITH HOLD if any COMMIT WORK statements occur within them. If this is not done, the cursors are closed.

Update cursors

An update cursor is declared by using the FOR UPDATE clause in a DECLARE statement. An update cursor will place a promotable lock on each row it fetches. If the row is fetched successfully, the program knows that no other program can update the row. Other users can continue to read the row. When the user is ready to update or delete the row, the lock is promoted to exclusive (if possible) and the action is performed.

The behavior of an update cursor is different between logged and non-logged databases. In a logged database, all updated rows hold a lock until the transaction completes. In a non-logged database, each lock is released when the row is written to disk. This behavior can vary slightly, depending on the isolation level (see next section).

Isolation level

The isolation level helps you control the level of concurrency and how your program will work with other active transactions. You don't have to set isolation level; there are defaults (see descriptions below). The SET ISOLATION command uses the syntax "SET ISOLATION TO isolation_level" where isolation_level is one of the following:

Dirty read - This isolation level simply reads a row from the database without placing locks or checking if any other programs are updating the row. It is a good option for static tables or low usage applications. This is the only isolation level available in databases without logging.

Committed read - If isolation level is committed read, Informix will not read rows that have a pending update (i.e., rows that have an exclusive lock on them). This is the default isolation for logged, non ANSI-compliant databases.

Cursor stability - This isolation level will place a lock on each row being fetched from the database. For a normal cursor, it will place a shared lock; for an update cursor, a promotable lock. When a row is updated, the lock is held until the end of the transaction (i.e., until the next COMMIT WORK or the cursor is closed). If the row is not updated, the lock is freed.

Repeatable read - The repeatable read isolation level is very similar to cursor stability except that it maintains the lock on every row it fetches, even for rows that aren't updated. The locks are shared for an ordinary cursor and exclusive for an update cursor. The locks aren't freed until the cursor is closed or the transaction ends. This type of isolation level is good for cursors that must ensure that none of the retrieved rows are updated while the user scrolls through them. This is the default isolation level for ANSI-compliant databases.

Tying it together

We've now learned the following:

1. There are three different kinds of locks: shared, exclusive, and promotable.

2. There are five types of database objects that can be affected by locks: database, tables, pages, rows, and index keys. The type of object being locked is known as the scope of the lock.

3. Lock wait times determine how long a program will wait for a lock before an error occurs.

4. Logged and non-logged databases handle locking differently.

5. Update cursors place locks on rows. In logged databases, the lock remains until the end of the transaction.

6. Isolation level is used to control the concurrency of your transactions. The types of isolation levels are dirty read, committed read, cursor stability, and repeatable read. Non-logged databases only allow dirty read.

How does this all fit together? It is generally best practice to include SET LOCK MODE and SET ISOLATION statements in your programs, though the default behavior might do. This is best illustrated through an example. Here is a 4GL program sample that will help you understand locking and isolation level. I will explain what happens in some different scenarios.

DATABASE stores7	     -- select database; place shared lock on it
SET LOCK MODE TO WAIT 30     -- Wait 30 seconds for locks, then abort
			     -- insert SET ISOLATION statement here if desired
DECLARE update_curs CURSOR FOR select * from customer FOR UPDATE;
FOREACH update_curs INTO....
	IF (update condition) THEN
		UPDATE customer SET ...  WHERE CURRENT OF update_curs;
	....
END FOREACH;
CLOSE update_curs

If this is a non-logged database, each time a row is fetched a promotable lock is obtained. This means that the lock is shared after a row is initially fetched. If the row is updated, an exclusive lock is obtained. When the row is written to disk and the user fetches the next row, the lock is freed.

If this is a logged, non ANSI-compliant database, the behavior of this code will differ only in that a lock will be held on all updated rows until the CLOSE update_curs statement. This is true because the default isolation level is committed read for a logged, non ANSI-compliant database.

If this is a logged database and the statement SET ISOLATION TO repeatable read is included before the FOREACH, a lock will be held on every row that is fetched until the "CLOSE update_curs" statement. This includes rows that are not updated.

If update_curs is not declared FOR UPDATE, you can still include the "UPDATE customer" statement. The program will simply use less locks: A shared lock on each row fetched (depending on isolation level) and an exclusive lock when the row is updated (the lock might be held, depending on isolation level). Remember that if this is a logged database, you need to declare update_curs WITH HOLD so it is not closed during the UPDATE.

Running out of locks

As with other system resources, there are only a finite number of locks. We've probably all seen "ISAM error: No more locks." at one time or another. You must be very careful in controlling the number of locks that your application uses. The easiest way to run out of locks is to update every row in a table:

update customer set cust_status = "A";

In a logged database, this will place an exclusive lock on every row in the table until the transaction is complete. If there are 100,000 rows in the table and your system only allows 10,000 locks (LOCKS parameter in ONCONFIG), you're in trouble. Two ways around this are:

1. Lock the whole table (lock table customer in exclusive mode)

2. Create a program that will read a row, update it, and go to the next record.

Also, be mindful of creating cursors that will lock every row. If you have a FOR UPDATE cursor with isolation level of repeatable read or committed read, be sure not to read too many rows at one time.

There are many different variations on locking strategies. Don't worry: If you understand the concepts of locking, lock scope, logged/non-logged databases, and isolation level, you should be well on your way.


Ron M. Flannery (rflanner@speedlink.net)
Author of the upcoming book, "Special Edition, Using Informix" by Que Publishing
President of One Point Solutions, Inc. (www.one-point.com)
President of Michigan Informix User Group (www.zenacomp.com/miug/)
Phone: (248)-887-8470 Fax: (248)-887-5698

Using SQL to Generate SQL Scripts (DBA Tricks and Tips)

by Lester Knutsen

This article will take a look at some tricks for using SQL to generate SQL to help you maintain and support your databases. One of the advantages of querying the system tables is that you can use the output of a query to generate SQL. This is very useful when you have to change database privileges, or change the locking mode of all tables in a database.

Using SQL to create an SQL script to change page level locking

When a table is first created, a locking mode is defined. The two lock modes are 1) page level - all rows on a page are locked when one row is locked, or 2) row level - only the one row in use is locked. The default is page level locking for performance reasons. However to maximize concurrence, a DBA will often need to change this to row level locking. The first example will be to look at a script to check the lock level of all tables in a database, and then for any tables that use page level locking, to generate the SQL code to alter that to row level locking.

Example 1. SQL script to generate another SQL script to set row level locking for all database tables.


{
#################################################################
# Module:       %W%     	Date: %D%
# Author:       Lester B. Knutsen  email: lester@advancedatatools.com
#               Advanced DataTools Corporation
# Description:  Generate SQL to set row level locking for all
#               database tables
#################################################################
}
output to lockmod.sql		-- Create SQL script
without headings		-- Don't include column headings
select "alter table ", 		-- Text 
	tabname , 		-- tablename
	" lock mode (row);"	-- Text
from 	systables
where tabid > 99		-- Don't get the systables
and 	tabtype = "T"		-- Get real tables not views
and 	locklevel = "P"		-- Get tables with page level locking
order by tabname


This is what lockmod.sql looks like after the script has been run on the stores7 database:


alter table  call_type     	lock mode (row);
alter table  catalog           	lock mode (row);
alter table  cust_calls         lock mode (row);
alter table  customer           lock mode (row);
alter table  items              lock mode (row);
alter table  log_record         lock mode (row);
alter table  manufact           lock mode (row);
alter table  orders             lock mode (row);
alter table  state              lock mode (row);
alter table  stock              lock mode (row);


Using SQL to generate scripts to revoke privileges

Another Informix default that I often need to change is "public" privileges. When a table is created, by default public is granted select, update, delete, and insert on that table. This means that any user can who can access the database can modify all data in that table. Example 2 is an SQL script which generates the SQL to revoke all public table privileges on a database. Since it reads the system tables to create the script, this script can be run on any database. One note: because you cannot revoke privileges granted by someone else, the script limits itself to tables where you granted the public privilege. If several developers are creating tables in a database, each developer will need to run this script to revoke public privileges.

Example 2. SQL script to generate another SQL script to revoke all public privileges.


{
#################################################################
# Module:       %W%     	Date: %D%
# Author:       Lester B. Knutsen  email: lester@advancedatatools.com
#               Advanced DataTools Corporation
# Description:  Revoke all public privileges for all tables
#################################################################
}
output to "revokepub.sql"
without headings
select "revoke all on ",  -- Revoke all privileges
        tabname,	  -- Table name from systables
        " from public;"
from    systables , systabauth
where   systables.tabid = systabauth.tabid
and grantee = "public"    -- Select table names were public has privileges
and grantor = USER        -- Only revoke the ones you have privilege to revoke
and systables.tabid > 99  -- Don't revoke the system table privileges


This is what revokepub.sql looks like after the script has been run on the stores7 database:


revoke all on  customer            from public;
revoke all on  orders              from public;
revoke all on  manufact            from public;
revoke all on  stock               from public;
revoke all on  items               from public;
revoke all on  state               from public;
revoke all on  call_type           from public;
revoke all on  cust_calls          from public;
revoke all on  custview            from public;
revoke all on  log_record          from public;
revoke all on  catalog             from public;

The basic process in all these scripts is to use the system tables, combined with text strings, in SQL to output a text file with SQL commands. You can create scripts to unload data, alter extent sizes, check tables (INFORMIX-SE), grant privileges, and automate many other DBA tasks.

Getting Column Names and 4GL Data Types with the vi Editor

How often have you tired of retyping the same database column names repeatedly in vi? One solution is to save the column names in a file and read them in as needed. However, vi has the capability to insert into a document the results of an operating system command. With a little work with shell scripts and awk, you can create a command that will get the column names from any table in any database, and insert them into your current document. And with a little more work, you can add formatting like commas, data types, or the INFORMIX-4GL format of "variable name like tablename.columnname".

To insert the results of a UNIX command on the current line in vi, use "!!command". For example, start vi and type "!!ls". This inserts the names of the files in the current directory into your document. To run the INFORMIX-SQL info command for the item table in the stores7 database, and insert the results into the current document, you would do the following:

1. Create an SQL script called itemsinfo.sql with one line as follows:
info columns for items;
2. Load vi and type the following:
!!dbaccess stores7 itemsinfo
3. This runs the sql script and inserts the results into the current document.

The following shell script is one I have created to get the column names from a database for SQL, ESQL/C, and 4GL programs. As long as the script getcol is in my path, I can get column names from any table. Type in the script, put it in your path, and from within vi type "!!getcol databasename tablename". I use awk to format the output so it is more useful. The script can also be run from the UNIX command line and the output will display on screen.

The following are two examples of running the script from a UNIX prompt. The first one returns just the column names, the second example with the "-d" option returns the data types.


lester@merlin >getcol stores7 items
item_num,
order_num,
stock_num,
manu_code,
quantity,
total_price

lester@merlin >getcol stores7 items -d
item_num                smallint,
order_num               integer,
stock_num               smallint,
manu_code               char(3),
quantity                smallint,
total_price             money(8,2)


#!/bin/sh
#############################################################################
# PROGRAM:  getcol - DataTools_Get_Columns, this will get the columns from
#			an Informix database in a format ready to use in a sql
#			query, a 4gl program etc...
#			In vi, put the cursor on a blank line where you want 
#			to insert the columns and type !!getcol database columns.
#			The columns will be inserted into the current file.
# USAGE:    getcol database table [-d\-l]
#			-d displays as column data type
#			-l displays as column like table.column
#			The default displays column names only
# AUTHOR:   Lester B. Knutsen, Advanced DataTools Corporation
#			703-256-0267, email lester@advancedatatools.com
# Copyright (c) 1993, Advanced DataTools Corporation,  All rights reserved.
#############################################################################
# function to display usage error and exit
usage() { 
echo "usage:  $1 database table [-d|-l]" 
echo "\t-d: column data type\n\t-l: column like table.column" ; exit 1
}
#############################################################################
# test for correct number of arguments 
[ $# -lt 2 ] || [ $# -gt 3 ] && { usage $0
}
# test for valid third argument  ( -l or -d )
case $3 in 
	-d|-l|"") ;; 
	*) usage $0 ;;
esac
#############################################################################
# use the info columns command to get the data from informix and pipe to awk
# check for dbaccess or isql
SQL=isql;
[ -f $INFORMIXDIR/bin/dbaccess ] && SQL=dbaccess
{ echo $3 $2; $SQL $1 - 2>/dev/null <<EOF 
info columns for $2;
EOF
} | nawk ' 
#############################################################################
# awk commands to format the data if you do not have nawk try awk
# set variables
BEGIN { opt = " "; tab = " " ; outcnt = 0 }
# check options from echo $3 $2 statement piped to awk on first line
NR==1 && $1 == "-l" { opt = $1; tab = $2; getline }
NR==1 && $1 == "-d" { opt = $1; getline }
# check lines after first line and  format based on options
NR >1 && $1 > "" && $1 != "Column" && $1 != "Error" && $1 != "Near" { 
	# add the comma and a newline to the end of the last line
	if ( outcnt >= 1 ) printf(",\n")
	# option -d print column and data type
	if ( opt == "-d" ) printf("%-18s\t%s",$1 , $2)
	# option -l print column like table.column
	else if ( opt == "-l" ) printf("%-18s\tlike %s.%s",$1 , tab, $1 )
	# no option print column name only
	else printf("%s",$1)
	outcnt++
	} 
# print a newline at the end
END { print "\n" } '
#############################################################################

Lester Knutsen
Advanced DataTools Corporation
4216 Evergreen Lane, #136, Annandale, VA, 22003
Phone: 703-256-0267
Email: lester@advancedatatools.com

Informix-Related Internet Resources

by Walt Hultgren

This is a listing of some of the more popular Informix-related sites and resources on the Internet. Many of these are maintained by the International Informix Users Group (IIUG).

The listing portion of this document is available as a Web page and may be found at "http://www.iiug.org/popular.html".

Sites and Resources:

www.iiug.org (IIUG Web site)
/iiug/membership_app.html - On-Line IIUG membership application
/iiug/confirm_member.html - IIUG membership confirmation requests
/local/grps_world.html - Worldwide listing of Informix user groups
/other_sites.html - List of other Informix-related sites
/rumors/ - Informix Rumors page
/software/ - IIUG software repository
/techinfo/faq/ - The Informix FAQ Listing
/usenet/cdi_archive.html - comp.databases.informix/informix-list archive

ftp.iiug.org (IIUG FTP Archive site)

ftp.mathcs.emory.edu (Mirror site)
/pub/informix/ - Top directory for IIUG anonymous FTP archive
/pub/informix/README - File explaining archive organization
/pub/informix/ls-lR - Modified "ls -lR" output for entire archive
/pub/informix/faq/ - The Informix FAQ Listing (see also ftp://kcbbs.gen.nz/pub/informix/)

www.informix.com (Informix corporate Web site)
/catalog - Informix Solutions Guide
/informix/corpinfo/ - Worldwide listing of Informix user groups usrgrups/usrgrups.htm
/informix/products/dlprod/ - Web/database information and free software webkits/docentry.htm
/informix/services/ - InformixLink Techinfo Center (login with introti.htm password required)

The above listings are but a brief summary of what is available. To learn more, visit each of the sites mentioned and do a little investigating.

The remainder of this message contains information primarily for people just learning about IIUG or the Internet.

INTERNATIONAL INFORMIX USERS GROUP - IIUG

The International Informix Users Group (IIUG) maintains publicly available archives of user-contributed documentation, shareware files, and articles from the Usenet newsgroup comp.databases.informix and its companion e-mail mailing list, Informix-List. You can access the archives either via a Web browser or anonymous FTP.

The Web archives are maintained at the IIUG Web site, which may be reached by linking to the URL "http://www.iiug.org". All archive files and c.d.i articles are kept there in plain text, so you can search, view or download any of them. If you have problems reaching the IIUG Web site, contact "webmaster@www.iiug.org" via e-mail.

There are two systems that maintain versions of the archives that are accessible via Anonymous FTP. "FTP" stands for File Transfer Protocol, and is designed primarily for moving files between systems.

The "Anonymous" part refers to a convention that allows anyone to log into a special area of a system that the administrator has made publicly available. When you ftp to a system, it will ask you for your login-ID. To access the public area, enter an ID of "anonymous". When asked for a password, enter your full e-mail address. This is also convention, though some sites now force the entry of a string that looks like an e-mail address.

The primary IIUG ftp site is ftp.iiug.org (IP 204.167.252.80). It is mirrored at ftp.mathcs.emory.edu (IP 199.76.28.1). The Informix archives on these two system are synchronized each night. All archive files on the IIUG Web site are also in the FTP archives, though many of the FTP files are compressed.

There are a number of other Informix-related archive sites around the Net that are dedicated to specific software, local user group archives, or other special purposes. You may obtain a listing of these by linking to the URL "http://www.iiug.org/other_sites.html" on the Web or downloading the file "ftp://ftp.iiug.org/pub/informix/doc/archive_sites" via FTP.

If you only have e-mail access to the Net, you can still retrieve FTP files via e-mail using a service called "ftpmail".

Listed below are both an example of an interactive anonymous ftp session, and a sample message that would be sent to an ftpmail server site. If you have problems accessing either of the sites mentioned here, please send e-mail to "ftp@ftp.iiug.org".

USING ANONYMOUS FTP

If you've never used anonymous FTP, here is a quick primer, in the form of a sample session on ftp.iiug.org with comments in {}. Lines where you type something are marked with "<<<":

$ ftp ftp.iiug.org {or: ftp 204.167.252.80} <<<
Connected to ftp.iiug.org
220 iiug FTP server ((UNIX(r) System V Release 4.0) ready.
Name (ftp.iiug.org:walt): anonymous <<<
331 Guest login ok, send ident as password.
Password: walt@rmy.emory.edu {use your e-mail address here} <<<
230 Guest login ok, access restrictions apply.
ftp> cd pub/informix <<<
250 CWD command successful.
ftp> get README <<<
200 PORT command successful.
226 ASCII Transfer complete.
ftp> get ls-lR <<<
200 PORT command successful.
226 ASCII Transfer complete.
.
. {repeat this step for each file you want} .
ftp> quit <<<
221 Goodbye.
%

USING AN FTP MAIL SERVER TO DO ANONYMOUS FTP VIA E-MAIL

There are sites connected to the Internet that provide mail servers for use by anyone who doesn't have access to FTP. Such a server acts as an intermediary that takes your request by e-mail, gets the files you want from the FTP site you specify, then e-mails those files back to you.

One of the best known sites is located at the DEC Western Research Lab in Palo Alto, CA, USA. You communicate with the retrieval utility by sending e-mail to the address "ftpmail@decwrl.dec.com". To obtain a help message, send an e-mail message to that address consisting of the word "help". The instructions for that server will be returned to you in an e-mail message.

As an example, to get the files "README" and "ls-lR" from /pub/informix on ftp.iiug.org, send a message to ftpmail@decwrl.dec.com like the following:

reply <your domain-style e-mail address or bang-path from decwrl goes here>
connect ftp.iiug.org
chdir /pub/informix
get README
get ls-lR
quit


Walt Hultgren
Emory University
954 Gatewood Road, NE
Atlanta, GA 30329 USA
Voice: +1 404 727 0648
Internet: walt@rmy.emory.edu

FREE GUI Informix DBA Adminstration Tool

Informix has released a free Database Administrators tool that is available for downloading from their web site. This is a great tool and well worth a look at. This article contains the product release notes from Informix. This was a topic of great interest on the internet news group comp.databases.informix. Joe Lumbley was one of the first to announce this with the following message:


Subject: Graphical GUI tool FREE!

There, I thought that would get your attention. Informix has just released their graphical database management tool ROM 2.1 into free distrubiton from their website. http://www.informix.com/trybuy. Hit the button for "graphical dba administration", go through their signup process, and then download all 7.8 megabytes of it. The tool includes a graphical database browser (ala SQL-Server), a query tool, and a table editor. The download comes with setnet32 and ilogin32, so you get the latest I-Connect for free with the download.

I'm impressed with the product. This is a wonderful product and is really worth a look, especially if you have UNIX-impaired users who want pretty Windows95, NT3.51, or NT4.0 screens.

FYI

Joe Lumbley(jlumbley@netcom.com)


Product Announcement: GUI Database Administration Tools, ROM 2.1.

1.0 OVERVIEW

IECC fundamentals for UNIX Servers management

Informix Product Management & Product Marketing is proud to announce the immediate general availability (GA) of the enhancements to the INFORMIX-Enterprise Command Center (IECC) known as Relational Object Manager (ROM) 2.1. ROM 2.1 is only compatible with 7.1X and 7.2X *UNIX* and Windows NT DSA servers. This product, which runs on Microsoft Windows95 or Windows NT system administration workstation, is available via the Web at *no charge*, and contains the following items:

1) Database Explorer
2) SQL Editor
3) Table Editor
4) View Editor
5) Stored Procedure Editor
6) Trigger Editor
7) I-Connect for Win32 (Windows95 & Windows NT 4.0)
8) Managing Relational Objects User's Manual
9) On-line Help

Informix has been shipping the first three tools (Database Explorer, SQL Editor, and Table Editor) as part of the INFORMIX-Enterprise Command Center (IECC) with OnLine Workgroup Server and OnLine Dynamic Server 7.22 on Windows NT. Now with this release, these tools and more are available as a stand alone product in support of UNIX & Windows NT DSA Servers!

1.1 PRODUCT POSITIONING

Informix's top database administration goal is to provide easy-to-use graphical database administration capabilities for our mainstream UNIX and Windows NT DSA database servers. With this *No Charge* version of ROM 2.1, Informix continues to deliver on its vision of providing tools designed to satisfy advanced to novice DBA relational object management requirements.

For usage instructions, refer to the Managing Relational Objects (ROM 2.1) User's Guide in Answers OnLine (http://www.informix.com/answers). An electronic copy of this manual is also included with the downloadable software.

The key marketing messages for this product are:

* Easy-to-install and use, graphical database change management utilities for novice and advanced DBAs supporting Informix's 7.1x-7.2x ODS and OWS for UNIX and Windows NT 4.0 database servers.

* Precursor to upcoming utilities and components included within IECC 3.0.

1.2 PRODUCT OVERVIEW

The Relational Object Manager 2.1 release for Windows95 and Windows NT 4.0 includes the following components:

* ROM 2.1 utilities: Database Explorer, SQL Editor, Table Editor, Trigger Editor, Stored Procedure Editor, and View Editor * INFORMIX-Connect for Win32 (Windows95 & Windows NT 4.0)

* ROM 2.1 on-line help

* Managing Relational Objects User's Manual 2.10

* On-line files: ROM 2.10 release notes, ROM 2.10 user's manual README, and INFORMIX-Connect release notes.

2.0 PRODUCT DESCRIPTION

2.1 PRODUCT FEATURES AND BENEFITS

ROM 2.1 is a graphical suite of database administration tools that support INFORMIX-OnLine Dynamic Server and INFORMIX-OnLine Workgroup Server, versions 7.1x through 7.23 on both UNIX and Windows NT platforms. Following is a detailed list of the individual components and attributes.

* Database Explorer
* SQL Editor
* Table Editor
* View Editor
* Stored Procedure Editor
* Trigger Editor
* Installation
* Connectivity
* Localization and GLS

Database Explorer

Database Explorer is the navigational component of ROM and is used to locate and manipulate relational objects on a database server. You can also use the Database Explorer to create databases and table synonyms, as well as to access the Table Editor and SQL Editor.

SQL Editor

The SQL Editor is used to create, edit, and execute SQL statements. You can also use this editor to query a database, manipulate data, and save SQL statements and returned data.

Table Editor

The Table Editor allows the user to create and edit relational database tables. This editor supports indexes, foreign keys, and constraints.

View Editor

The View Editor enables the user to modify an existing view and create new views using SQL. A template is provided for new view objects.

Stored Procedure Editor

The Stored Procedure Editor enables the user to modify an existing stored procedure and create new stored procedures using SQL. A template is provided for new stored procedure objects.

Trigger Editor

The Trigger Editor enables the user to modify an existing trigger and create new triggers using SQL. A template is provided for new trigger objects.

Complete Installation

The ROM 2.1 product includes complete install and uninstall functionality. The file customers will download (rom21tc1.exe) is a self-extracting executable file that contains the installation files, and launches the installation application. This file is installed on the system administrator's Windows95 or Windows NT workstation, although it is used to manage both Windows and UNIX DSA databases. This file cannot be installed and executed on the UNIX server.

Connectivity Included

Executing the installation program will automatically install all required runtime connectivity (I-Connect). I-Connect includes SetNet32, FindError (a help application that references Informix database error description), the I-Login demo, and the required ESQL/C runtime libraries.

Localization and GLS Level 4 Compliance

ROM 2.1 will be available in an English-only version (e.g., title bars are in English). However, any information provided by the server will be visible in the native language as defined on that particular server. Informix has no plans to localize this product since upcoming versions of these utilities will be made available within IECC.

ROM 2.1 is GLS level 4 compliant. GLS level 4 compliance is the implementation of a coding standard that allows Informix products to support multibyte characters (i.e. Japanese, Chinese, etc.). The GLS modifications will enable Informix to maintain a single source for products it sells anywhere in the world.

2.2 COMPATIBILITY MATRIX

ROM 2.1, which runs on Windows95 or Windows NT 4.0, supports the management of the following DSA servers.

UNIX DSA Servers:

* OnLine Dynamic Server, version 7.1x, 7.2x
* OnLine Workgroup Server, versions 7.1x, 7.2x

Windows NT 4.0 DSA Servers:

* OnLine Dynamic Server, version 7.1x, 7.2x
* OnLine Workstation, versions 7.1x, 7.2x
* OnLine Workgroup Server, versions 7.1x, 7.2x

3.0. PRODUCT REQUIREMENTS

3.1 HARDWARE AND SOFTWARE REQUIREMENTS

For optimal performance, verify that your system meets

the following requirements:

Hardware Requirements
* Intel-based 486 or Pentium-based systems
Operating System Requirements
* Windows95 or WindowsNT Workstation or Server version 4.0 or higher
Memory Requirements
* 16 megabytes of random access memory (RAM) under Windows95; 24 megabytes of random access memory (RAM) under WindowsNT
Disk Requirements
* 15 megabytes of available disk space
Networking Requirements
* TCP/IP only (included with Windows95 and Windows NT)

4.0 HOW TO OBTAIN THE PRODUCT

Download from the Web:

These Windows NT and Windows95 utilities are complete, integrated out-of-the box solutions for ODS/OWS relational object management. ROM 2.1 is *not* orderable through sales channels on shipping media. To download ROM 2.1 at no charge, refer to the external Informix website at: http://informix.com/trybuy/.

5.0 PRICING AND SUPPORT POLICIES

5.1 PRODUCT AND MAINTENANCE PRICING

Relational Object Manager 2.1 is provided to customers at *NO* charge. Informix currently has no plans to make a later version of ROM beyond this initial product available. Enhancements to the 2.1 ROM utilities will be included in the upcoming IECC 3.0 product. IECC 3.0 is targeted for inclusion within the upcoming Informix 7.3 DSA servers.

5.2 SUPPORT POLICIES

Customer support for this product is available to Informix customers with valid INFORMIX-OnLine DSA-based server support contracts. When calling Informix for support, the customer should be prepared to provide their database server serial number as proof of their support contract. Customers should be aware that some bug fixes may not be available until IECC 3.0 is released.

6.0 MISCELLANEOUS

6.1 FAQ's

Q: Will ROM 2.1 work against XPS or IUS servers?
A. No. ROM 2.1 is specifically targeted at ODS and OWS 7.1x - 7.2x UNIX and Windows NT servers.

Q: Is there a storage manager included in ROM 2.1?
A: No. ROM 2.1 does not include any backup and restore attributes.

Q: Will there be refreshes of this product placed out on the Web or available via sales channels?
A: No. Informix currently plans to provide this product as a one-time deliverable. Future enhancements to these utilities will appear in future upcoming IECC 3.0 release.

Q: Will ROM 2.1 support Windows NT 3.51?
A: No. ROM 2.1 clients must be either Windows95 or Windows NT 4.0.

Q: Will ROM 2.1 work against I-SE, OnLine 5.x, or Illustra servers?
A: No. Informix has no plans for ROM 2.1 nor IECC to ever support non-DSA-based Informix servers.

Q: How is ROM 2.1 different that ROM 1.1, which currently ships with ODS/OWS 7.22 for Windows NT?
A: ROM 2.1 includes 3 new editors not previously shipped with ROM 1.1. These editors include the View Editor, Trigger Editor, and the Stored Procedure Editor.

Q: With the introduction of the ROM tools, what exactly are Informix's future plans for DB/Cockpit, ON-Monitor, DB-Access, ON-Perf, and High Performance Loader (HPL)?
A: Informix will to continue to maintain these command line and character based tools for the foreseeable future. Informix will not consider discontinuing these utilities until the equivalent functionality or better is available in IECC.


This newsletter is published by the Washington Area Informix User Group
Lester Knutsen, President/Editor
Washington Area Informix User Group
4216 Evergreen Lane, Suite 136, Annandale, VA 22003
Phone: 703-256-0267
lester@advancedatatools.com