April 1997 Newsletter
Volume 7, No. 2
Highlights of This Issue
WAIUG Training Day, by Nick Nobbe
WAIUG Survey Results, by Nick Nobbe
The International Informix User Group Advocacy Program
Informix Character-based Administration Tools Update
Informix World Wide User Conference
Understanding and Using INFORMIX Stored Procedures, by Rafal Czerniawski
INFORMIX-4GL Programming Test, by Stuart Litel
A Web/CGI Interface to the ONSTAT Utility, by Lester Knutsen
Informix Press Releases:
Informix Introduces Informix Developer Network
Symantec and Informix Form Strategic Alliance to Deliver Enterprise Java
Development Database Solution
Next Meeting Agenda - June 18, 1997
ERwin Data Modeling Tools Presentation by Logic Works Using Triggers and Stored Procedures by Tom Wolfe This presentation will highlight the use of triggers and stored procedures as they can be used to
implement business rules, additional database security, and detailed audit tables. Mr Wolfe is the SE
Regional Director for Terwilliger & Associates, Inc. His expertise includes project management,
development, systems audits, and performance tuning.
Date and Time: June 18, 1997 1996 at 9:00 a.m. to 12:00 noon
Location: Informix Commercial Division Office, Two Democracy Plaza
6707 Democracy Blvd, Suite 500, Bethesda, MD
The meeting is open to everyone. Please RSVP to 703-256-0267, ext. 4, so we can keep an
$200 Discount for Members Attending
the Informix Worldwide User Conference
Members are eligible to receive a $200.00 discount off of the registration fee for the Informix
Worldwide User Conference, July 22 to 25, in San Francisco. To take advantage of this excellent
opportunity, you must request the International Informix User Group (IIUG) Member Discount and
have your IIUG member number when you register for the Conference. All WAIUG members as of
April 30, 1997 are enrolled in the IIUG. All members requesting the discount will be verified for
IIUG membership, so you must join the user group prior to registering for the Conference. Please
call John Petruzzi at 703-490-4598 or Linda Knutsen at 703-256-0267 if you need your IIUG
member number. Information about the conference is available by contacting (800) 784-6580, or
on-line at www.informix.com.
See Page 6 for more information about the Informix Worldwide User
Best New Informix-Related Web Site of the Quarter
Tim Schaefer has put together a web site that includes his tools and an on-line Informix related
magazine called INXUTIL. The March issue of INXUTIL Magazine is now available. This site is
well worth a visit at http://www.mindspring.com/~tschaefe.
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
Pure Software, Inc.
Summit Data Group
WAIUG Training Day 1997, by Nick Nobbe
Judging from member response and sheer numbers, the recent training day was a success. Over 100
members attended the event, which was held March 26 at the University of Maryland campus. The
seminar rooms were the right fit for day - ample space, comfortable seating, good lighting, and other
amenities. Lunch, scheduling, the raffle, and general logistics all went smoothly thanks to the efforts
of Sam Hazelett, Mimi Wallace, and Linda Knutsen.
The sessions I attended - Migration to OnLine 7+ by Mike Tinius, and Query Optimization by Kevin
Fennimore - offered many practical tips and some useful free handouts and software. There was a
lively exchange of questions and answers between speakers and the audience. The general relaxed
tone of the sessions also encouraged sharing of experiences and information. The two hours allotted
to each session passed in a flash, and left many people hanging on afterwards.
Lunch, provided as part of admission, included deli sandwiches, chips, cookies, and a fruit drink. A
radio, IQ/Live Web software, and an Informix bag were raffled off during the lunch break.
The Training Day was designed to test member response to the all-day format, which we knew to be
successful with other groups and potentially interesting, based on our reading of the recent survey
results. The format looks promising, so we hope to schedule another event like this in the future.
Two of the presenters from our Training Day have put their session material on the web:
Web Pages From Your Informix Database, by Lester Knutsen is located at: www.access.digex.net/~lester
Developing Applications With the Web DataBlade, by Michael W. Chaney is located at: www3.chainlink.com/hazmat/a1 (NOT www.chainlink.com which is the company site.)
Survey Results Help Point to Future Direction
by Nick Nobbe
Thanks to those who took the time to answer the recent survey. Let us know if you have not yet received your free
copies of the IQ/Live Net CD-Rom. While response to the recent survey was not overwhelming, we have drawn
some early conclusions about you and your interests.
The typical member is a develope or a database administrator working primarily with ESQL/C and 4GL in a
Windows 95, NT, or HP-UX environment. Not surprisingly, most are interested in information and training to
help hone your technical skills.
Response to the preference sections of the survey indicated a strong interest in the WAIUG newsletter and web
site followed closely by training events. Technical tutorials, user case studies, and Q&A sessions scored highest
in the category of meeting focus. Topics of greatest interest for meetings were database administration and
database design. A summary of the actual final rankings for each category is given at the end of this article. The
recent turnout of 100 members at the March 26 Training Day is encouraging evidence that our findings are
meaningful. Rankings in each category were as follows:
- Informix products used:
- 1. ESL/C
- 2. 4GL
- 3. ISQL
- 4. OnLine 6/7
- 5. OnLine 5
- 6. SE
- 7. NewEra
- 8. C-ISAM and Illustra
- Job Duties:
- 1. Developer
- 2. DBA
- 3. Management
- Operating Systems Used:
- 1. Win95
- 2. NT, HP-UX
- 3. Solaris
- 4. SCO-Unix
- 5. AIX, Sun-OS
- Preferred User group activities:
- 1. Newsletter
- 2. Web site
- 3. Training events
- 4. Quarterly meetings
- 5. IIUG (Informix International User Group)
- Preferref User Group Meeting focus:
- 1. Technical tutorials
- 2. User case studies
- 3. Q&A sessions
- 4. Informix product announcements
- 5. Vendor presentations
- Topics of interest:
- 1. Database administration
- 2. Database design
- 3. Database & Web integration
- 4. Database networking
- 5. OnLine performance tuning
- 6. SQL optimization,
- 7. Object relational technology
- 8. Web 4GL/CGI programming
- 9. Java
- 10. DataBlade development
- 11. 4GL programming
- 12. ESL/C
- 13. Advanced SQL
- 14. ODBC
- 15. Migration schemes
- 16. GUI client tools
- 17. GUI report writers
- 18. Database hardware platforms
- 19. New Era
- 20. Power Builder
- 21. Delphi
- 22. Case Tools
- 23. Visual Basic
The International Informix User Group Advocacy Program
by Carlton Doe
The IIUG is pleased to announce its Informix Advocacy Program. This program serves as a two-way
communication channel with Informix at Menlo Park, giving end-users a coherent voice capable of directly
influencing Informix development plans and dealing with issues affecting Informix products and services.
The program currently has two sub-committees: "Tools" and "Engines". Our "Engines" committee has already
had some success helping Informix re-evaluate their strategy concerning the "dbaccess" and "onmonitor" utilities.
Informix had earlier announced that these tools were to be phased out, but following advice from the IIUG and
the Informix community this move has been placed on hold. The Tools committee will focus on issues surround
the Informix NewEra product and will be commencing its work shortly.
When fully organized, the IIUG Advocacy Program will consist of members of the IIUG Board of Directors and
the Informix User Group Leadership Council (IUGLC). Currently, Gavin Nour, Clem Akins, and Carlton Doe
from the IIUG Board are on the various committees with Carlton as chair. IUGLC members will be selected
during the IUGLC meeting to be held in conjunction with the 1997 Informix Worldwide Users Conference this
For more information about the IIUG and its activities, please see our web server at http://www.iiug.org. General
questions or requests for information about the IIUG can be sent to email@example.com.
Informix Character-based Administration Tools Update
by Carlton Doe
Informix Product Management team has asked we forward to you this new status on character-based OnLine
DSA administration tools. But first, some background.
October 1996, Informix met with the IIUG Board of Directors to get input on INFORMIX-Enterprise Command
Center and the proposed future direction of character-based DSA administration tools. There was ongoing
discussion through year end between the Board and Informix on this issue. The IIUG Board adopted this topic
within our forming IIUG Advocacy Program. More information on the IIUG Advocacy Program will follow in
a separate announcement.
Early in the year, a public discussion about these tools was held on the comp.databases.informix (c.d.i.) Usenet
group independent of the discussions being held with the IIUG and Informix. The feelings expressed on c.d.i.
were also forwarded to Informix for their review.
Based on the feedback received from the IIUG and other sources, Informix is releasing the following:
Informix Product Management apologizes for the confusion caused by the statements regarding the obsolescence
of DB-Access and ON-Monitor as stated in the Release Notes of the INFORMIX-OnLine Dynamic Servers on
UNIX starting with version 7.21. We have decided to remove those as well as the statements regarding Motif
tools obsolescence from future releases of OnLine Dynamic Server Release Notes. Obsolescence of product
components such as DB-Access and ON-Monitor in the future will occur when Informix can provide our user
base with more definitive time frames and details on the replacements for the existing utilities.
Regards, Informix Product Management
The IIUG appreciates Informix' responsiveness to concerns expressed by the IIUG and others on this topic. We
look forward to ongoing collaboration with Informix as they evolve the tools and technology we use daily.
Carlton Doe, President, International Informix Users Group, firstname.lastname@example.org
Informix 1997 Worldwide User Conference
July 22-25 1997 at San Francisco's Moscone Center
General Colin L. Powell to Keynote Informix 1997 Worldwide User ConferenceAlso to Feature "Dilbert" Creator Scott Adams
MENLO PARK, Calif. (February 3, 1997) -- Informix Software, Inc., (NASDAQ:IFMX) the
leading provider of innovative database technology, today announced that General Colin L. Powell,
one of the most admired and respected men in America, will present the keynote address for
Informix's sixth-annual Worldwide User Conference and exhibition. This year's conference, to be
held July 22-25 at San Francisco's Moscone Center and entitled "Make Waves," will focus on
Informix's vision for the future and examine the technologies, products and solutions that will enable
today's organizations to take advantage of the next great wave in database innovation.<P>
General Powell, former chairman of the Joints Chiefs of Staff, Department of Defense for the United
States Government, will offer conference attendees his insights on the challenges of leadership and
effective organizational management based on his wisdom and experiences most visibly demonstrated
during crises at the highest levels of the government.
The conference will also feature Scott Adams, creator of the Dilbert comic strip and author
of The Dilbert Principle and Dogbert's Top Secret Management Book. Adams is recognized for his
knowledge in finance and information technology and is known for capturing the humor in corporate
America in his Dilbert comic strip.
"We are excited and honored to have General Powell and Mr. Adams address our attendees,"
said Phil White, chairman and CEO, Informix. "Both are unquestionably at the top of their respective
fields, and will offer our attendees their unique insights on managing effectively in corporate
Informix's Sixth-Annual Conference
This year's conference for end-users, developers and partners will feature conference session on three
technology areas of particular interest to today's IT community: on-line transaction procession
(OLTP), decision support, and content management. Informix will relate these three solutions to
today's technology trends in electronic commerce, Internet/intranet, enterprise solutions, publish and
subscribe applications, Java, smart cards, relational on-line analytical processing (ROLAP), and data
The conference will also feature a 150,000 square foot exhibition hall at which more than 150
Informix hardware partners, independent software vendors, and application providers will showcase
their Informix-based solutions. Informix will also demonstrate its technology and solutions, including
a DataBlade® Partner Pavilion, an Internet village, a multimedia center, and a live
"cyberzine"--capturing the conference on the Web.
More information about the conference is available by contacting (800) 784-6580 in the U.S.,
(508) 652-1015 outside the U.S., or on-line at http//www.informix.com. You may also email
questions to email@example.com.<P>
Understanding and Using Informix Stored Procedures
Copyright Dataspace Consulting Pty Ltd, 1995
Since the introduction of stored procedures in version five of the Informix engine there has been a
lot of discussion about their use, benefits, limitations and their impact on the applications'
performance. They have gained acceptance in the client/server environment but their usefulness in the
single host type environment has been seriously underestimated. As with any tools, before using them
it is important to consider whether it is the right tool for the job at hand. To be in the position to
make this decision you need to understand its workings, how well it integrates with your environment
and development tools, the type of application you are developing and whether it is a small scale
application or large mission critical OLTP system. When used appropriately stored procedures can
be a very powerful tool both in the client/server environment and single host type environment.
The aim of this article is to provide a high level overview of stored procedures and their features,
particularly the advantages and disadvantages of their use from the two perspectives: client/server
and single host type environment. I will also discuss issues to consider before using stored procedures
on a large scale. Hopefully by the end of this article you will be in the position to decide whether
stored procedures are the right for you.
Overview of Stored Procedures
Stored procedures can be thought of as user defined program modules stored in the database and
executed by the database engine. They are the simplest form of remote procedure calls where the
remote client sends a message to the server, which then executes the procedure. Stored procedures
are written in Stored Procedure Language which is a superset of SQL with additional looping and
conditional statements. When a stored procedure is created it's source and the compiled executable
is stored in the system catalog tables. From the administration point of view they are like any other
database objects which can be created and dropped using 'create' and 'drop' statements. Their
permissions can be administered using grant statements. The source of stored procedures can be
extracted from the system catalogs using 'dbschema -f' utility. To fully appreciate the benefits and
shortcomings of stored procedures it is important to understand what happens at the time stored
procedure is created and executed.
Stored procedures are created using a 'create procedure' statement. The following steps take place
at the time of creation:
1. The source is parsed and stored in the SYSPROCBODY system table.
2. The SQL is extracted from the source of stored procedure and a dependency list built. The
dependency list is an index to all database objects accessed by the stored procedure. It is used
by Informix at the time of execution to verify that all objects referenced still exist and have
3. The extracted SQL is optimised to produce a query plan, just like any other SQL.
4. The dependency list and query plan are stored in the system table: SYSPROCPLAN. Note
that while the stored procedure is created Informix places exclusive locks on some of the
Stored procedures are executed using 'execute procedure' statement. Following steps take place
1. Retrieve the dependency list, pcode and query plan for the stored procedure from the system
catalog tables if they are not already in the stored procedure cache.
2. Verify that the objects in the dependency list have not changed since the time the stored
procedure was created.
3. If no changes were detected execute procedure.
4. If there were changes to the database object, the SQL is re-optimised and a new query access
plan is stored in the catalog tables. The procedure is then executed.
The SQL in the stored procedures is optimised at the time of its creation except in the following
1. When the structure of the tables referenced by the procedure or their indices have changed
it will be re-optimised at the first execution after the change.
2. If stored procedure uses temporary tables it will be re-optimised every time it is executed.
This has been fixed in later versions of 5.x
3. If the stored procedure does not contain any DML statements (e.g. select, insert, update,
delete) it will be re-optimised every time it is executed. This has been fixed in later versions
of 5.x. The re-optimisation of SQL during stored procedure execution should be avoided as
it results in exclusive locks on the SYSPROCBODY table and can lead to serious
Stored Procedures System Tables
The following system tables are used to store information about stored procedures:
- SYSPROCEDURES - General information
- SYSPROCBODY - Pcode executable
- SYSPROCPLAN - Dependency list and query plans
- SYSPROCAUTH - Security authorisation
Security and Stored Procedures
Stored procedures can operate in two security modes at the time of execution:
1. Owner Privileged Procedures
These are stored procedures created by the user with 'resource' authority on the database. The stored
procedure inherits all the permissions on the objects owned by the creator plus permissions on other
objects that been granted to the creator with the grant option. In case of owner privileged stored
procedures Informix will check all permissions on referenced objects at the time of execution.
2. DBA Privileged Procedures
These are stored procedures created by the user with 'dba' authority on the database or with a dba
keyword in the create statement. In this case the stored procedure has 'dba' privileges for the duration
of it's execution. When dba privileged procedure is executed there is no need to check the
permissions on referenced objects.
Benefits of Using Stored Procedures
For the purpose of this article it is useful to look at the benefits of using procedures from the
perspective of the environment you will use to deploy your application. That is whether it is
client/server or a single host type environment. Note that the benefits gained from using stored
procedures in a single host type environment are also applicable to client/server.
Advantages in Client/Server Environment
1. Provide means for partitioning the application logic between the server and client. For
example CPU intensive work such as calculations could be implemented in the stored
procedures and the results returned back to the client for presentation.
2. Improve application performance by reducing network traffic. One of the biggest performance
bottlenecks in the client /server environment is the network. With use of stored procedures
you can substantially reduce network traffic. There are two reasons for this:
a. Instead of sending whole SQL statements only the stored procedure name and
arguments have to be sent to the server.
b. In a situation where you have a transaction which consists of multiple SQL statements
the intermediate results from the SQL have to be sent between the server and the
client. If stored procedures are used then all transaction work can be done on the
server and only the final result returned to the client.
3. Allow encapsulation and enforcement of business rules on the server. For example if business
rules are controlled through stored procedures then they can be enforced regardless of what
tool is used to update the database.
4. Improved application maintenance. If business rules and application logic are implemented
in the stored procedures then any changes to business rules are localised. This means that
application changes can be done on the server without a need to distribute them to all the
5. Provide more robust transaction control. When using stored procedures transaction control
can be implemented on the server instead of the client. If transactions are controlled on the
client there exists a possibility of transactions being left open if the client crashes. Open
transactions might prevent archiving of logical logs and lead to long transactions. This
problem has been addressed in later versions of release 5 where Informix will terminate any
open sessions without client connection.
6. Allow to extend the functionality of some 4GL development tools. Some of the Client/Server
development tools are not very good in handling complex database interactions such as
7. Improve application security. In ODBC environments any application users can access the
database using desktop tools. Using stored procedures you can eliminate the need to grant
direct access to all tables and control it through the procedures.
8. Can be used to invoke Unix programs from the PC client on the server using SPL 'SYSTEM'
command. For example a stored procedure can be used to send Unix mail or update files on
the Unix host.
Advantages in Single Host Environment
Note that all the benefits of stored procedures in the single host environment are also applicable to
1. Allow implementation of business rules and database integrity checking in the database by
disallowing programs and users from accessing the tables directly.
2. Encourage code reuse. Commonly used functionality can be implemented in the stored
procedures which can be reused by any programs.
3. Improve performance. When SQL is executed through 4GL or E/SQL, the SQL statement
is sent through a pipe to the Informix engine and then parsed, optimised and security checked
before it's execution. With stored procedures the above work needs to done only at the time
of it's creation. Generally stored procedures with four or more SQL statements provide best
4. Improve application security. If database access is encapsulated within the procedures then
it is no longer necessary to grant privileges to users on the tables.
5. Allow to extend the functionality provided by standard SQL functions. Stored procedures can
be used to create user defined functions that are transparently utilised within the SQL
statements, for example: if a measurement is stored on the database in inches. A function
could be written which will convert inches to centimetres. This function can be then used as
if it was built-in SQL function.
6. Extend functionality provided by the database triggers. The language used to write triggers
is limited to simple type operation.
7. Hide the complexity of the underlying database structures from the users and programmers,
for example stored procedures can be written to perform standard reports which are then
invoked by GUI end-user reporting tools.
8. Aid in application's performance tuning and monitoring. Procedures provide ability to perform
SET EXPLAIN on SQL with out first executing it. Since the stored procedure's SQL is
optimised when the procedure is created, it is possible to obtain access paths chosen by the
optimiser at this time. In the case of SQL executed from E/SQL or 4GL it is not possible to
see the explain output unless the SQL has been run against the database which might not be
always possible or practical in a production environment.
9. Can be used increase application's independence from the underlying database structures,
therefore, isolating the programs from the database structure changes, for example: data
Disadvantages of Stored Procedures
Most of the disadvantages of stored procedures are due to an extra layer of complexity and lack of
support by development and administration tools
1. Some of the 4GL development tools are designed to work best by accessing the database
tables directly. If you are using stored procedures then some of the default functionality
provided by the development tool might be lost. For example most of 4GL tools will
automatically implement optimistic locking (checking for lost updates). If you choose to
perform updates through stored procedures then in most cases you will lose the functionality
provided by the tool which will result in decreased productivity.
2. For the above reason, when using stored procedures you might lose the database
independence provided by your 4GL tools. Also the stored procedure's code is not portable
to other DBMS platforms.
3. More complicated program debugging.
4. Before version seven of the engine, stored procedures are buffered in the sqlturbo processes
hence each user holds their own copy of the stored procedures cache. As a result of this your
application might require more memory for each sqlturbo process and you will lose the
benefits of sharing buffered procedures across different database sessions. This is no longer
a problem since version seven has a common stored procedure cache for all the users.
5. Extra dependency is introduced into the applications. It necessary to ensure that the programs
execute correct versions of the procedures. Configuration management is a major issue. 6.In
some situations the use of stored procedures might degrade the performance. As stated
earlier, when stored procedure is executed the Informix engine has to check the system tables
to ensure that the tables used by the procedure have not changed.
7. The stored procedure language does not support dynamic SQL and versions prior to seven
did not support access to SQLCA.
Using Stored Procedures
As illustrated, many benefits can be gained from using stored procedures. When embarking on their
use, consider the impact on the developers due to the extra layer of complexity in the environment.
It is best to carefully plan how they will be utilised and administered. Following is an overview of
issues that should be considered before using stored procedures:
1. Devise naming standards. Good naming standards are important with a large number of stored
procedures. They will improve the maintainability of the application by allowing easy
identification of stored procedure function and the tables it accesses.
2. Document SPL coding standards. The standards should address issues such as:
a. Guidelines on when to use stored procedures. You might decide that only certain type
of work should be done in stored procedures. Good candidates are: complex reports
and queries, access to sensitive data, processing of large cursors, any SQL that is used
repetitively, transactions which perform more then four SQL statements.
b. Document conventions for parameter passing between the application programs and
c. Define how the stored procedures will communicate back with the application.
d. Provide guidelines for transaction control. Transaction control can be either done
from the application program or within the stored procedure. The choice will largely
depend on the development tools at use. Try to stick to one consistent method.
3. Error handling. How will the errors handled and communicated back to the application
4. Workout how the dependencies between the procedures and programs will be tracked.
5. Update configuration management procedures.
6. Management of SYSPROCBODY and SYSPROCPLAN tables. When a large number of
stored procedures is used these tables can grow to many extents which can effect the
performance of loading the procedures into memory. This can be eliminated by altering the
'next extent' to a larger size.
7. Security administration.
8. Remember that by default stored procedures are created with execute to public permission.
When migrating stored procedures to production execute to public should be revoked.
Summary and Conclusion
As you can see stored procedures are very powerful tools when used appropriately. The decision to
use stored procedures should depend on your organisation, development environment and the type
of application being developed. All of the benefits of store procedures can be fully realised when
building large mission critical applications, where database integrity, security and performance with
a large number of concurrent users are critical. Stored procedures should be also given serious
consideration when building client/server applications which will be deployed over the wide area
networks where the amount of network traffic will have a significant impact on your application's
performance. In most cases you will find yourself using stored procedures just for a specific tasks
which cannot be easily done using your current development tools or in situations where central
enforcement of business rules and security is critical to the application.
Rafal Czerniawski is director of Dataspace Consulting Pty Ltd. He specialises in logical and physical
database design, performance tuning, database administration, enterprise client/server design and
CASE tools, his experience includes Informix, Sybase and DB2. You can contact him through email
INFORMIX-4GL Programming Test
by Stuart Litel
1) Explain what the Informix "Construct" statement does?
2) What is the difference between the "Input by name" and "input prog_rec from screen_rec"?
3) In a 4GL report what does "order external" do?
4) What will happen in the following code sample - find at least two mistakes...
define p_array of smallint
define x smallint
#NOTE assume the next two lines are correct and xyz is a smallint field
declare abc_curs cursor for
select xyz from abc
foreach abc_curs into p_array[x]
5) Explain briefly what the following Informix built-in functions do:
- a. errorlog(char-expr)
- b. infield(field-name)
- c. scr_line()
- d. arg_val(integer_expr)
6) Explain what will be displayed on the lines below in bold for the value of x:
define x smallint
define x smallint
display x #I AM A BOLD LINE
let x = 2
display x #I AM A BOLD LINE
display x #I AM A BOLD LINE
display x #I AM A BOLD LINE
call funct3() returning x
display x #I AM A BOLD LINE
define x smallint
let x = 3
let x = 4
let x = 5
7) How do you "force the cursor" in the input array statement to go down on line or bounce to
the next row?
8) What is int_flag? How and when is it set?
9) What is the prepare statement used for?
10) What is the difference in the following code
# SAMPLE #1
define x smallint
declare abc_curs cursor for
select field from table
fetch abc_curs into x
if (status = 100) then
# SAMPLE #2
define x smallint
declare abc_curs cursor for
select field from table
foreach abc_curs into x
11) When using ring menus, explain how to make a different key other than the first key the
"choice" key? For example I have a ring menu that is hEllo and I want to make the "E" key
the key that will pick this choice.
12) When using ring menus explain how to Hide Options to certain users?
13) What is the difference between the "format" and "picture" attribute in a .per screen file?
For the next few questions (#14 - # 19) - use the following two tables
Person Table State table
first_name char(20) state char(2)
last_name char(20) description char(20)
14) Write an SQL query that will find all the people in the person table that do not live in a state
that begins with the letter "M"
15) Write an SQL query that will give a list of all the first and last names of the people in the
person table and the state long description that "goes with" or "joins" them from the state
16) Write an SQL query that will give a list of all the first and last names of the people in the
person table and the long description that "goes with" or "joins" them and the state is or is
not in the state table. For example John Smith lives in NH (New Hampshire) and Jane Doe
lives in XX and there is no state XX, but you want to show both John Smith with New
Hampshire and Jane Doe with no state.
17) Write a single query that will give a list of the average age of all the people in the Person table
broken down by state and in order of the youngest to oldest age.
18) Write a query that will give you a count of the people who live in each state, and the results
should be printed in alphabetic order by state description. (i.e. Alabama 10, Alaska 5, etc...)
19) Write a single query that will give you a list of people in the Person table that live in states
where the state is NOT IN the State table.
20) What is the difference in "Select Distinct" and "Select Unique" sql statement?
21) What is the difference on the following two statements:a) Lock table Person in exclusive
mode b) Lock table Person in share mode
22) Explain what the Informix 4GL "on key" statement does?
23) What happens in the following code - what will be displayed by the last line?
define x smallint
let x = 2
when x = 2
let x = 8
when x = 4
let x = 5
when x = 6
let x = 8
let x = 10
24) Explain how to change the "Accept" key in Informix to "F10" (the Accept key by default is
the Esc or Escape key)?
25) What is the "Options input wrap" do in 4gl?
This test is the property of Stuart Litel. Feel free to use it, administer it or burn it. This test is for
the sole purpose of testing peoples knowledge of Informix 4GL / SQL. It is not to be reproduced
except for making copies to give it as a test, nor is it to end up in any book or publication
You can get the answers from me thru e-mail....
If you have any other questions you wish to add, please also e-mail them to me. I will of course
give credit to where credit is due. I am looking to expand this into having more questions about
SQL, 4GL and DBA stuff for On-Line 5.X and On-Line 7.X. If anyone can send me 20 GREAT
questions to add here, then I will also send them a nice T-Shirt from Boston (or buy you a few drinks
at the next Informix World Wide User Conference).
Remember the questions must be relating to Informix and they must be clear and include the answers.
Actually if they are about 4GL and/or SQL do not send me the answers, see if you can stump me.
E-Mail your questions to: firstname.lastname@example.org
Lastly if you find any mistakes, also please let me know. This test was actually written while eating
dinner a few years back stuck in a hotel while traveling on a business trip for a customer, so I may
have had a few too many beers and made a few mistakes.
A Web/CGI Interface to the ONSTAT Utility
by Lester Knutsen
This is one of the scripts I presented at the WAIUG Training Day. The purpose is to provide a
web interface to the INFORMIX-OnLine ONSTAT Utility. There are two parts to this interface:
the first is a web page from which the user selects which onstat option is to be executed, and the
second is a CGI script that runs onstat with the requested option. Figure 1 is an example of what
the web page would look like. The user is presented with a scrolling list of options to select.
Onstat has may more options than I use in this example. After the user selects an option, a CGI
script is executed which runs onstat and generates a web page displaying the output.
First we will take a look at the web page that created the screen shown above. The HTML code
is show in Figure 2. This is very basic HTML that can be created in any text editor or using the
Netscape Editor. HTML is a markup language that uses tags within angle brackets <> to tell the
web browser how to display the page. There are three parts to this web page: a header, a title,
and the form with the list of options. The first part of our example document is a header that is
not displayed by the browser. Next we display a title, centered in a larger font saying "Informix
OnLine ONSTAT Utility Web Interface".
The next action is to display a form on the browser. The tag <FORM method=post
action="cgi-bin/wonstat.sh" instructs the server create a form on the browser's screen, and then
to execute the CGI script wonstat.sh when the user presses the execute button. The rest of the
tags within the form tag list the options to display in the pop-up scrolling list.
Figure 2: onstat.html
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<TITLE>Informix Onstat Options</TITLE>
<META NAME="GENERATOR" CONTENT="Mozilla/3.0Gold (X11; I; SunOS 5.5 sun4m)
<CENTER><P><FONT COLOR="#003388"><FONT SIZE=+2>Informix OnLine Onstat Utility
<CENTER><P><FORM method=post action="cgi-bin/wonstat.sh"
<table><B>Choose one Onstat Option :<SELECT name=o_command>
<INPUT type=submit value="Execute Command"></FORM></P></CENTER>
When the user presses the execute button, the web server runs the CGI script wonstat.sh. This is
really a unix shell script. The web server must have a location in its configuration for CGI scripts,
and must be configured to execute CGI scripts. This script must be located in the specified
directory. There are a number of security considerations with CGI scripts that are beyond the
scope of this article.
There are four parts to the script, and Figure 3 lists the full shell script. The first part of the script
reads from standard input into a shell variable o_command, the option the user selected from the
web page. Next the Informix environment variables INFORMIXDIR, PATH,
INFORMIXSERVER, and ONCONFIG must be set. Most web servers run as the user "nobody"
with a very limited environment to prevent executing most programs that you do not specify on
your system. Therefore, to run an Informix script you need to specify all the environment
information the user will need. The third part of the script is a case statement to determine what
options to execute with onstat. The final part of the script is the onstat command.
These scripts and other examples from the Training Day can be downloaded from my web site at
Figure 3: wonstat.sh
# Module: @(#)wonstat.sh 1.1 Date: 97/03/25
# Author: Lester B. Knutsen email: email@example.com
# Advanced DataTools Corporation
# Description: Web Page to display the output of onstat -p
# Get STDIN from Web Server
# Set-up the Informix environment
## Set the location of Informix Programs
## Add the Informix Programs to your PATH
## Set the Default Database Server
## Set the Informix Configuration File
# Script to generate web page
echo Content-type: text/html
echo "<TITLE>DB Server Status</TITLE>
<H1>Informix OnLine Server Status for: $INFORMIXSERVER</H1>
case $o_command in
o_command=VP_Statistics) o_command="-g sch";;
o_command=Memory_Grant_Manager) o_command="-g mgm";;
o_command=SQL_Sessions) o_command="-g sql";;
INFORMIX INTRODUCES INFORMIX DEVELOPER NETWORK
New Program Provides "One-Stop-Shop" Information Delivery Program for Informix Developer
SAN JOSE, Calif. (February 24, 1997) Informix Software, Inc. (NASDAQ:IFMX), the leading
provider of innovative database technology, introduced today, at its Worldwide Partner Developer
and Business Forum in San Jose, Calif., the Informix Developer Network (IDN). The IDN is an
aggressive, new program, developed for developers by developers, that will provide advanced,
technical information and knowledge transfer to the growing Informix developer community
worldwide. The IDN will also provide exclusive information about Informix architecture, technology,
products and developer services to Informix partners and application developers.
Based on the Informix Universal Web Architecture, the IDN features a dedicated Web site that is
structured as a subscription-based information delivery program. IDN subscribers will have access
to many services such as a self-help system and knowledge base to resolve problems, simplified
software development kits, regular product update releases, knowledge transfer programs, and an
on-line knowledge base. Developers will also be able to communicate and interact with other
Informix developers through a private IDN forum and news groups. The IDN Web site is also linked
to other Informix developer support programs such as the DataBlade Developers Program.
Informix's advanced publish and subscribe technology will enable the IDN to become a
"one-stop-shop" for application developers. Once a partner has registered its profile and preferences
with IDN, the partner will be able to receive customized solutions, access to the developer self-help
knowledge base and receive early access to Informix's new products and technology. The program
will also offer customized information, grouped by technology such as C, C++, or Java, to ensure
the delivery of proactive and timely, technical information to IDN subscribers' specific needs.
"The Informix Developer Network will help developers create new applications that will differentiate
themselves in the marketplace and help deliver competitive advantage to the organizations they
support," said Martin Brauns, vice president, Worldwide Channels and Partners. "Through the
dedicated Web site and other service offerings, the IDN will provide an exciting information base for
any developer who wants to write an application with the leading, most advanced database
technology in the industry."
The IDN is an evolving set of services and deliverables that will continue to grow and be improved
throughout 1997. Currently, the IDN plans to provide a dedicated track at the Informix Worldwide
User Conference in San Francisco, Ca. in July 1997. Informix is also planning to organize other IDN
developer events and services, such as seminars and workshops. A reference CD, which will include
new release information, technology updates, sample code, developer documentation and release
notes, and a regular product CD, which will include information on new products or components,
are also planned.
More information about IDN will be available beginning on February 28 by visiting the IDN Web site
Informix Software, based in Menlo Park, Calif., provides innovative database technology that enables
the world's leading corporations to manage and grow their business. Informix is widely recognized
as the technology leader for corporate computing environments ranging from workgroups to very
large OLTP and data warehouse applications. Informix's database servers, application development
tools, superior customer service, and strong partnerships enable the company to be at the forefront
of many leading-edge information technology solution areas. More information about Informix is
available via the World Wide Web at http://www.informix.com.
MENLO PARK, Calif. (April 2, 1997)--Informix Software, Inc., (NASDAQ:IFMX), the leading
provider of innovative database technology, and Symantec Corporation (Nasdaq:SYMC), the leading
provider of Java development environments, today announced a strategic alliance to develop and
market solutions allowing developers to rapidly create and deploy next-generation enterprise Java
applications. Symantec and Informix have agreed to integrate and market each others leading
products to professional Web and enterprise Java developers worldwide.
Informix has adopted Symantec Visual Café Pro as the premier Java development tool for Informix
OnLine and INFORMIX-Universal Server databases. Symantec has adopted INFORMIX-Universal
Server as its premier database solution, and will replace Sybase SQL Anywhere, bundling
INFORMIX-Universal Server with future versions of Symantec Visual Café Pro. INFORMIX-Data
Director will also be included to provide direct access to rich media types stored in Informix's
Servers. To provide for maximum client deployment, Informix will be supporting Symantec Visual
Café Pro for Windows 95, NT and Macintosh.
Java developers will be able to create powerful enterprise level applications using drag-and-drop and
the powerful Rapid Application Development (RAD) capabilities of Symantec's Visual Café Pro
product and INFORMIX-Universal Server. Informix and Symantec will also jointly develop the next
generation of Visual Café Pro to extend its capabilities to support multi-tier enterprise Java
application development for INFORMIX-Universal Server applications.
"Informix's object-relational database management system provides the best data storage structure
for the complex data types required for the Web," said Mansour Safai, general manager of Symantec's
Internet Tools Division. "As Informix developers begin to expand their enterprise applications to the
Web, they can be sure of ease and compatibility with the market-leading Java tools."
"By enabling its leading Java tool for the Enterprise with Informix-Universal Server, Symantec is an
excellent choice for the growing number of customers moving to incorporate rich data types in their
enterprise applications," said Phil White, chairman and CEO of Informix. "Companies can now take
advantage of Informix's leading database technology and Symantec's Java expertise to quickly bring
the most innovative business applications to market."
The industry's leading open, completely integrated object-relational database management system
(ORDBMS), INFORMIX-Universal Server provides the extensibility needed to handle a broad range
of data types such as images, video and audio, not managed effectively by traditional relational
databases. Based on Informix's Dynamic Scalable Architecture (DSA), the database is provided with
near-linear scalability to support Enterprise deployment of applications utilizing such exciting new
As the first [and most powerful] Visual RAD tool for developing applets and applications that
connect to relational databases, Symantec Visual Café Pro is the ideal Java development environment
to build applications for INFORMIX-Universal Server. Symantec and Informix will work together
to ensure that future versions of Visual Café Pro include wizards and controls that are
INFORMIX-Universal Server-aware and complement the features already enabled through
INFORMIX-Data Director, which will be included in a future version of Visual Café Pro, is the first
open component-based Java development tool that allows developers to create intelligent
database-aware Java applets for Web-enabled client/server applications with the
INFORMIX-Universal Server. The tight integration of these products will provide a rich development
environment for INFORMIX-Universal Server.
This combination allows developers to easily create powerful INFORMIX-Universal Server-ready
applications, enabling rich content stored directly in the database to be powerfully deployed on the
Internet and corporate Intranets. The accelerated application development provided by Symantec
Visual Café Pro, INFORMIX-Universal Server and INFORMIX-Data Director will ultimately result
in reduced time to market for such leading-edge database applications.
By using INFORMIX-Data Director for Java with Visual Café Pro, developers will be provided with
direct access to INFORMIX-Universal Server, and model-driven, drag-and-drop functionality to
rapidly develop content-rich, scalable Java applications. Visual Café Pro's wizards will make Java
application development for INFORMIX-Universal Server fast and easy by automatically creating
applications for such databases and bringing exciting new data types to the Web.
For instance, developers will be able to simply drag-and-drop new data types, such as documents,
directly into INFORMIX-Universal Server forms or reports without writing any code. Developers
will also be able to incorporate spatial data stored in INFORMIX-Universal Server into their
applications and have their spatial data queries displayed on users' screens in the form of a map.
"Informix is a strategic partner of Netscape and an important supplier of products that are helping
us build next generation Internet and Extranet applications in the areas of sales, marketing and
customer interaction," said Larry Geisel, senior vice president of information systems and CIO at
Netscape. "With the combination of Symantec's Visual Café Pro, INFORMIX-Data Director and
environment that will allow us to rapidly build crossware applications using Informix's database
Visual Cafe Pro
Visual Cafe Pro is the only fully-integrated Visual Rapid Application Development tool for Java and
database application solutions and provides distributed database access from anywhere on the Web
using only a Java-enabled browser. Visual Cafe Pro allows web developers to quickly and easily
create thin Java applets and applications that can access existing relational databases, using innovative
database wizards and pre-built Java Beans components.
Included in Visual Cafe Pro is dbANYWHERE WorkGroup Server, a middleware database
application server technology that allows Java programs to access corporate databases over the
Internet using JavaSoft's JDBC API. dbANYWHERE provides the scalability and security of a
three-tier solution with the ease of development and implementation of a two-tier solution. Sun
Microsystems, Inc.'s Java, is the de facto standard programming language for network computing.
Informix Software, based in Menlo Park, California, provides innovative database technology that
enables the world's leading corporations to manage and grow their businesses. Informix is widely
recognized as the technology leader for corporate computing environments, ranging from workgroups
to very large OLTP and data warehouse applications. Informix's database servers, application
development tools, superior customer service, and strong partnerships enable the company to be at
the forefront of many leading-edge information technology solution areas. More information about
Informix is available via the World Wide Web at http://www.informix.com.
Symantec Corporation develops, markets, and supports a complete line of application and system
software products designed to enhance individual and workgroup productivity as well as manage
networked computing environments. Platforms supported include IBM personal computers and
compatibles, Apple Macintosh computers as well as all major network operating systems. Founded
in 1982, the company's global operations span North America, Europe and several fast growing
markets throughout Asia Pacific, Japan and Latin America. Information on the company and its
products can be obtained by calling (800) 441-7234 toll free or (541) 334-6054.
This Newsletter is published by the Washington Area Informix Users Group.
Lester Knutsen, President/Editor
Washington Area Informix Users Group
4216 Evergreen Lane, Suite 136, Annandale, VA 22003