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
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.
We are moving our user group web site to the web server maintained by the International Informix Users Group. The new URL is :
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.
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.
This is to let everybody know that the latest release of the Informix FAQ (version 2.8, March 1997) has now been released.
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.
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
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.2See 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:
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:
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
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:
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.
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:
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" } '
#############################################################################
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".
ftp.iiug.org (IIUG FTP Archive site)
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.
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".
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 "<<<":
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:
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)
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:
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
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:
Windows NT 4.0 DSA Servers:
3.0. PRODUCT REQUIREMENTS
3.1 HARDWARE AND SOFTWARE REQUIREMENTS
For optimal performance, verify that your system meets
the following requirements:
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