Stored procedures in IDS 7.30

[Roger2001]

I need some assistance please.

We use IDS 7.3 tc7 on an NT 4 platform as the main database to our manufacturing ERP system Baan. Our knowledge of Informix is very limited, and hence we need some assisitance.
We use a lot of complex ODBC linked Crystal reports in our company, and as data has built up in the Informix tables, the speed of reporting has deteriorated quite badly in some of the complexly linked reports. We have reviewed the reports and redesigned them wherever possible to get the best joins, and use of sub reports. Any future improvements in that area would be limited.
We are limited in what we can do with IDS, because the slightest change to the environment seems to upset our ERP system, and we cannot upgrade to either level 2 drivers or newer versions of Informix.
whist reading through the Crystal forum on Tek-Tips I read of people using stored procedures to improve reporting speed dramatically.
Bearing in mind our very limited knowledge of Informix, is it reasonable for us to be able to write some procedures to improve efficiency. Are there any risks involved, and can some kind soul please provide the basics to a novice on how to achieve this.
TIA

 

[olded]

Roger:

First, I must tell you I have no experience with ODBC or Crystal reports. I do have significant experience developing informix stored procedures.

While I have no experience with Baan, I can say probably you can improve performance using stored procedures. The most obviously hurtle will be your lack of SQL knowledge and experience.

An experienced SQL programmer should be able to write Informix Stored Procedures in no time. The Informix Guide to SQL and dbaccess manuals cover SPL syntax quite well.

For a little more friendly introduction, I like Chapter 13 of the Informix Handbook by Ron Flannery, Informix Press, ISBN 0-13-012247-5. (Sorry, but with IBM buying the database part of Informix, I don't know the status of Informix Press).

One big limitation of Informix SPL is its string handling capabilities. An SQL programmer experienced in Oracle or SQL-server will be very disappointed with Informix SPL.

I realize this is very little help. Good luck!

Ed
Schaefer