DEV Community

Franz
Franz

Posted on

Uniface 10.4 + Informix: Simple Guide to Data Retrieval 📊

#uniface #informix #dataretrieval #performance

This article gives a simple overview of how data retrieval works when you use Uniface 10.4 with an Informix database. 😊 This post was created with the help of an AI assistant and is based on the official Uniface 10.4 documentation about “Data Retrieval on Informix”.

read with where clause 🔍

The Uniface read statement can use a where clause that Uniface inserts directly into the generated select statement for Informix. Everything inside the double quotes of the where clause is copied as text into the SQL, so you must always write valid Informix syntax.

trigger read read where "AMOUNT > 1000 AND STATUS = 'OPEN'" end
Enter fullscreen mode Exit fullscreen mode

In this example, Uniface does not try to interpret the condition; it simply passes the where text to Informix, which then filters the rows. This is helpful if you want to use Informix-specific features, such as special functions or optimizer hints. 🧠

Using the option parameter ⚙️

The option parameter of the read statement lets you tune how Uniface and Informix retrieve data, so you can improve performance. You can use it to control things like the maximum number of hits (rows) that should be returned, the number of rows per fetch step, or how many hits are cached.

Example (typical pattern):

trigger read read "ORDER" option "maxhits=500, stepsize=50, hitcache=100" end
Enter fullscreen mode Exit fullscreen mode

In simple words, this kind of setting can do the following:

  • maxhits=500: Stop after 500 rows, even if more rows exist.
  • stepsize=50: Fetch data in blocks of 50 rows at a time.
  • hitcache=100: Keep up to 100 hits in memory for faster navigation.

The exact option names and values are connector specific, but the idea of limiting rows and controlling fetch behavior is important for performance.

selectdb and count functions 🧮

Informix always supports the selectdb statement, but the way it behaves with count and plain field transport can affect performance. If you use the count function in a selectdb that also lists several fields, Informix may perform a separate select for every field named, and the same can happen if you use selectdb without any aggregate functions at all.

Pattern that can be slow:

; Can cause extra selects per field selectdb (count(F1), max(F2), min(F3)) from MYTABLE to (CNT.DUMMY, MAXF2.DUMMY, MINF3.DUMMY)
Enter fullscreen mode Exit fullscreen mode

Better pattern:

; 1) Count in a separate selectdb selectdb (count(F1)) from MYTABLE to (CNT.DUMMY) ; 2) Other aggregates in a second selectdb selectdb (max(F2), min(F3), ave(F4), sum(F5)) from MYTABLE to (MAXF2.DUMMY, MINF3.DUMMY, AVGF4.DUMMY, SUMF5.DUMMY)
Enter fullscreen mode Exit fullscreen mode

By separating count (and simple transport without aggregate) from the other aggregate functions, you avoid unnecessary extra selects and keep your application faster and more scalable. 🚀

Field subsetting on Informix 🧩

Uniface can automatically subset fields when working with Informix, but only in specific environments. Fields that are not painted on the form and not mentioned in ProcScript are then not selected, fetched, inserted, or updated by the Informix connector.

Important rules:

  • Field subsetting is allowed only for Informix OnLine.
  • Field subsetting is not allowed for Informix Standard Engine (SE).

Example scenario:

  • Your table has 20 columns.
  • Your Uniface form only shows 5 fields and your ProcScript also uses only these 5.
  • The connector will then work only with these 5 fields and ignore the other 15, which reduces network traffic and improves performance. 📉

The Uniface kernel decides which fields are required and which fields can be skipped, so you do not need to configure this manually.

Maximum number of open cursors 🎫

The Informix connector in Uniface supports up to 256 open cursors. An open cursor is basically an active query on the database, so every hitlist or running query can use one cursor.

What this means for your application:

  • If you open many hitlists or nested queries and never close them, you can reach the cursor limit.
  • You should close or release hitlists and instances when you no longer need them, especially in long-running components or batch jobs.

Keeping an eye on cursor usage helps you avoid “too many open cursors” errors and keeps your Uniface and Informix system stable. ⚠️

Practical mini example 🧪

Here is a small example that combines several of these ideas in one trigger:

trigger read ; 1) Read orders with a limit and tuned fetch behavior read "ORDER" option "maxhits=200, stepsize=20" ; 2) Read only recent orders using an Informix-specific where clause read where "ORDER_DATE >= TODAY - 30" option "maxhits=100" ; 3) Get total number of orders selectdb (count(ORDER_ID)) from ORDER to (CNT.DUMMY) ; 4) Get average order amount in a separate selectdb selectdb (ave(AMOUNT)) from ORDER to (AVGAMOUNT.DUMMY) end
Enter fullscreen mode Exit fullscreen mode

The two read statements show how you can combine where clauses and options to limit hits and tune performance. The pair of selectdb statements shows the recommended way to separate count from other aggregates to avoid extra selects and keep Informix efficient. 💡

With these patterns, your Uniface 10.4 applications can retrieve data from Informix in a way that is both simple to understand and friendly to database performance. 🚀

Top comments (0)