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

$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.

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

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

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 July.

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 info@iiug.org.

Informix Character-based Administration Tools Update

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, carlton@iiug.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 America."

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 mining.

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&#174; 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 iwuc@informix.com.<P>

Understanding and Using Informix Stored Procedures

Introduction

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 not changed.

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 system tables.

Stored procedures are executed using 'execute procedure' statement. Following steps take place during execution:

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 cases:

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 concurrency problems.

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 clients.

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 denormalised data.

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 client/server environment

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 improvements.

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 denormalisation.

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 stored procedures.

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 programs.

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 at: rafal@dataspace.com.au.

INFORMIX-4GL Programming Test

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[100] 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]

	end foreach

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:

	database abc

	globals

		define  x smallint

	end globals



	main

		define x  smallint

		display x	#I AM A BOLD LINE

		let x = 2

		display x	#I AM A BOLD LINE

		call funct1()

		display x	#I AM A BOLD LINE

		call funct2()

		display x	#I AM A BOLD LINE

		call funct3() returning x

		display x	#I AM A BOLD LINE

	end main



	function funct1()

		define x smallint

		let x = 3

	end function



	function funct2()

		let x = 4

	end function



	function funct3()

		let x = 5

		return x

	end function

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

	open abc_curs

	while true

		fetch abc_curs into x

		if (status = 100) then

			exit while

		end if

		display x

	end while



	# SAMPLE #2

	define x  smallint

	declare abc_curs cursor for

		select field from table



	foreach abc_curs into x

		display x

	end foreach

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) 

	address		char(30)

	Job_title	char(30)

	city		char(30)

	state   	char(2)

	zip		char(5)

	age		smallint

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 table.

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

	case

		when x = 2

			let x = 8

		when x = 4

			let x = 5

		when x = 6

			let x = 8

		otherwise

			let x = 10

	end case



	display x

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: slitel@ziplink.net

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

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.

Figure 1:

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">

<HTML>

<HEAD>

   <TITLE>Informix Onstat Options</TITLE>

   <META NAME="GENERATOR" CONTENT="Mozilla/3.0Gold (X11; I; SunOS 5.5 sun4m)

[Netscape]">

</HEAD>

<BODY BGCOLOR="#FFFFFF">



<CENTER><P><FONT COLOR="#003388"><FONT SIZE=+2>Informix OnLine Onstat Utility

Web Interface</FONT></FONT>

<HR></P></CENTER>



<CENTER><P><FORM method=post action="cgi-bin/wonstat.sh"



<table><B>Choose one Onstat Option :<SELECT name=o_command>

<OPTION></B>Profile 

<OPTION>Users

<OPTION>DBspaces 

<OPTION>Configuration

<OPTION>Logging

<OPTION>Messages

<OPTION>Chunk_IO_Stats 

<OPTION>Page_Flushers

<OPTION>LRU_Queues

<OPTION>VP_Statistics

<OPTION>Memory_Grant_Manager

<OPTION>SQL_Sessions

</SELECT>

</P>



<P>

<INPUT type=submit value="Execute Command"></FORM></P></CENTER>



</BODY>

</HTML>

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 www.advancedatatools.com.

Figure 3: wonstat.sh

#!/bin/sh

#################################################

# Module:       @(#)wonstat.sh	    1.1   Date: 97/03/25

# Author:       Lester B. Knutsen         email: lester@advancedatatools.com

#               Advanced DataTools Corporation

# Description:  Web Page to display the output of onstat -p

#################################################



#################################################

# Get STDIN from Web Server

#################################################

read o_command



#################################################

# Set-up the Informix environment

#################################################

## Set the location of Informix Programs

INFORMIXDIR=/u3/informix7

export INFORMIXDIR



## Add the Informix Programs to your PATH

PATH=$INFORMIXDIR/bin:$PATH:

export PATH



## Set the Default Database Server

INFORMIXSERVER=train1

export INFORMIXSERVER



## Set the Informix Configuration File

ONCONFIG=onconfig.train1

export ONCONFIG



#################################################

# Script to generate web page

#################################################



echo Content-type: text/html

echo

echo "<TITLE>DB Server Status</TITLE>

<BODY>

<H1>Informix OnLine Server Status for: $INFORMIXSERVER</H1>

Option: $o_command

<PRE>

"

case $o_command in

	o_command=Profile) 		o_command="-p";;

	o_command=Users) 			o_command="-u";;

	o_command=DBspaces) 		o_command="-d";;

	o_command=Configuration)	o_command="-c";;

	o_command=Logging) 		o_command="-l";;

	o_command=Messages) 		o_command="-m";;

	o_command=Chunk_IO_Stats)	o_command="-D";;

	o_command=Page_Flushers) 	o_command="-F";;

	o_command=LRU_Queues) 		o_command="-R";;

	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";;

esac



onstat $o_command

echo "</PRE>

</BODY>"

#################################################

New Program Provides "One-Stop-Shop" Information Delivery Program for Informix Developer Community

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 at http//www.informix.com/idn.

About Informix

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.

SYMANTEC AND INFORMIX FORM STRATEGIC ALLIANCE TO DELIVER ENTERPRISE JAVA DEVELOPMENT DATABASE SOLUTION

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 content.

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.

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 Netscape Visual JavaScriptÔ, we have available to us a comprehensive visual development environment that will allow us to rapidly build crossware applications using Informix's database servers."

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.

About Informix

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.

About Symantec

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.