Converting Informix 4GL Functions into C UDRs
Plenty of Informix® 4GL applications keep their business rules in FUNCTION blocks: tax
calculation, credit checks, date arithmetic, a loop over a cursor that adds things up. Those functions run
in the client, so each call that needs data pays for a round trip to the server, and nothing outside the
4GL program can use them. SQLMorph can compile such a function into a C user-defined
routine (UDR) that the engine loads and runs itself. You then call it from SQL, from SPL or from another
converted function, and no 4GL runtime is involved.
This article shows what the converter produces, how to install the result and what the generated code does at run time. The 4GL backend is pre-release; What is refused lists what it does not convert yet, and it refuses those constructs rather than skipping them.
Why a C UDR and not SPL
4GL and SPL differ in the places that matter for a faithful conversion: NULL handling in expressions,
blank-padded CHAR comparison, DECIMAL precision, DATETIME and
INTERVAL arithmetic, 1-based substrings, USING masks, records and arrays. A
C UDR can carry its own small runtime that implements the 4GL rules, so the converted function computes
what the original computed. The engine also lets a C UDR call other routines and run SQL through the
DataBlade API, which covers embedded SQL, cursors and FOREACH.
One engine fact shapes the design: a language name of your own (LANGUAGE 4gl) cannot run,
because the engine only builds routines for the built-in, C, SPL and Java managers. Converted functions are
therefore registered as LANGUAGE C.
Easy to write and test, and it runs in the engine
The two ways of getting logic into the server pull in opposite directions. 4GL is simple to write and simple to
test: a function is a few readable lines, you can compile it on its own with c4gl, call it from a
small MAIN, print the results and step through it, all without a database server in the loop. A
C UDR is the reverse. You write C against the DataBlade API, manage memory durations by hand, handle NULL
arguments and the argument and return conventions yourself, build on the engine’s own glibc, register
the module with CREATE FUNCTION, and test it by calling it from a running server. A bug can take
the engine down with it.
SQLMorph keeps the first and delivers the second. You write and test the function as 4GL, with the tools you
already have, and convert it when it is right. The same source file can be compiled by c4gl and
run against test values, then converted and called with EXECUTE FUNCTION; the
examples page does exactly that with a small driver and sets the two sets
of results side by side (they are identical). The generated C is the part you do not write, test or maintain by
hand: change the 4GL, convert again, and the deploy tool installs the new version.
A function, and what comes out
Take a function that totals a customer’s orders and adds a percentage:
FUNCTION order_total(p_cust, p_rate)
DEFINE p_cust INTEGER
DEFINE p_rate DECIMAL(5,2)
DEFINE l_sum DECIMAL(12,2)
DEFINE l_amt DECIMAL(12,2)
LET l_sum = 0
DECLARE c_ord CURSOR FOR
SELECT amount FROM orders WHERE cust_id = p_cust
FOREACH c_ord INTO l_amt
LET l_sum = l_sum + l_amt
END FOREACH
RETURN l_sum * (1 + p_rate / 100)
END FUNCTION
Convert it:
$ sqlmorph -s informix-4gl -t informix-native -o out order_total.4gl
sqlmorph: order_total.4gl: ok (1 function, generated)
Every FUNCTION becomes four files, named after the function:
| File | What it is |
|---|---|
order_total.c | The UDR. Each 4GL statement is a short block of C, preceded by a /* 4GL line N */ comment, so you can read it against the source. |
order_total_install.sql | The CREATE FUNCTION that registers it. |
order_total_manifest.json | The functions it calls, the compiler version and the hashes of the source and the generated code. |
order_total_check.sql | A query that lists callees that are not yet installed in the database. |
The install script is plain SQL:
CREATE FUNCTION order_total(p_cust INTEGER, p_rate DECIMAL(5,2)) RETURNING DECIMAL(32)
WITH (HANDLESNULLS)
EXTERNAL NAME '$INFORMIXDIR/extend/sqlmorph/order_total_b9c0ae32b2d2.so(fgl_order_total)'
LANGUAGE C;
Two details are worth noticing. The shared object name carries a hash of the generated code, so installing
a new version never overwrites a module that the engine may still have loaded; a call that is already
running finishes on the old code. And the routine is created WITH (HANDLESNULLS), because
without it the engine does not call a C routine at all when an argument is NULL, and 4GL expects to see
the NULL.
Build and install
Build on the engine’s own system, or one with the same glibc and architecture. A library built on a newer glibc will not load on an older engine host, and the deploy tool refuses to install one. The tool compiles each module, copies it into the engine’s extend directory without overwriting anything, and runs the install scripts in one transaction, after a dry run that is rolled back:
$ tools/fgl_deploy.sh -d mydb out/
$ echo "execute function order_total(1042, 8.25)" | dbaccess mydb -
It also keeps two small tables, one with the current version of each converted routine and one with the history, so you can see which source produced the code that is running. Running it again with unchanged source installs nothing.
Because it is an ordinary function, you can use it where any function goes:
SELECT c.name, order_total(c.id, 8.25) FROM customer c WHERE c.region = "EU";
What the generated code does
The generated C does not use the engine’s own arithmetic. It links a small runtime, built from the same source, that implements the 4GL rules:
- NULL. An expression with a NULL operand is NULL; comparisons give a three-valued result, and
IF,WHILEandCASEtake the false branch on unknown. - DECIMAL. Arithmetic is exact, with up to 32 significant digits, and checks for overflow instead of wrapping. The routine returns
DECIMAL(32)when the 4GL result has no fixed scale. - Strings.
CHARvalues are blank-padded and compared that way;CLIPPED,USING,MATCHESands[i,j]substrings follow the 4GL definitions, including assignment to a substring. - DATE, DATETIME, INTERVAL. Arithmetic, comparison,
CURRENT,EXTENDandUNITSare in the runtime, and a computed value carries its qualifier, sod + ivcan be passed to another function or returned directly. - Records. A record is a set of variables;
INSERT ... VALUES (r.*)binds one value per field,SELECT ... INTO r.*andFOREACH ... INTO r.*read them back,LET q.* = r.*copies field by field andCALL f(r.*)passes the fields as separate arguments. Nested records flatten to their leaf fields. - Arrays. See the next section; SPL has nothing like them.
- Errors.
statusandsqlcaare set by each SQL statement. An SQL error raises an engine exception unless the function saysWHENEVER ERROR CONTINUE,GOTOorCALL;NOT FOUNDworks the same way. - Staying a good citizen. Loops, including a loop made with
GOTO, yield to the engine and stop when the session is killed. Calls between converted functions are limited to a depth of 10,000, then fail with an error rather than exhausting the stack.
Arrays: the part SPL cannot do
SPL has no array variable. A routine can hold a handful of scalars, a ROW or a collection, but
it cannot say sales[i].amt: a collection is reached through SQL, not by subscript, so a 4GL
function that fills a table of values in memory and then indexes into it has no direct SPL translation.
People usually end up rewriting the algorithm around a temporary table, which changes both the code and the
performance. 4GL arrays are one of the main reasons a function cannot simply be moved to SPL, and they
convert without any rewrite.
The function below keeps the best customers of a region in an array of records, works out the share of the top three, and fills a two-dimensional work grid:
FUNCTION top3_share(p_region)
DEFINE p_region CHAR(10)
DEFINE i, n INTEGER
DEFINE total, top3 DECIMAL(14,2)
DEFINE sales ARRAY[500] OF RECORD
cust INTEGER,
amt DECIMAL(12,2)
END RECORD
DEFINE grid ARRAY[12,4] OF DECIMAL(12,2)
DEFINE tmp RECORD
cust INTEGER,
amt DECIMAL(12,2)
END RECORD
LET n = 0
LET total = 0
DECLARE c_s CURSOR FOR
SELECT cust_id, SUM(amount) FROM orders
WHERE region = p_region GROUP BY cust_id ORDER BY 2 DESC
FOREACH c_s INTO tmp.*
IF n = 500 THEN EXIT FOREACH END IF
LET n = n + 1
LET sales[n].cust = tmp.cust
LET sales[n].amt = tmp.amt
LET total = total + tmp.amt
END FOREACH
LET top3 = 0
FOR i = 1 TO 3
IF i <= n THEN LET top3 = top3 + sales[i].amt END IF
END FOR
FOR i = 1 TO 12
LET grid[i,1] = total / 12
END FOR
IF total = 0 THEN RETURN NULL END IF
RETURN top3 / total * 100
END FUNCTION
This converts with no change to the source. What to know about the arrays in the generated code:
- Shape. One, two or three dimensions (
ARRAY[12,4] OF ...), holding any scalar type or a flat record. The elements are used exactly as in 4GL:a[i],a[i,j],a[i].field, on either side ofLET, in expressions, asINTOtargets and as SQL host variables. - Subscripts are 1-based and checked. An index below 1 or above the declared size, or a NULL index, raises an engine error that names the bound, for example “out of range 1..10”, and the call fails cleanly. It never reads or writes a neighbouring variable, which is what a C array would do and the reason a hand translation to C is risky.
- Storage is on the heap, not the stack. Each array is allocated when the function starts and released on every way out of it: a normal
RETURNor an error that stops the function. A function may declare arrays of up to a million elements each; a request for more is refused when the function is converted, not at run time. - Row-major layout, as in 4GL.
grid[i,j]is computed once per statement from the two subscripts; with a record element, each field is held in its own array, so touchingsales[i].amtreads one value rather than a whole record. - Initial values. Every element starts with the same default as an ordinary variable of its type (0 for numbers and dates, NULL for text and DECIMAL, as
c4gl -initvardoes), so a loop that has not filled the whole array reads defined values, not leftovers.
A whole element of a record array moves in one statement: LET sales[n].* = tmp.*,
LET tmp.* = sales[i].*, FOREACH c INTO sales[n].*, INSERT ... VALUES (sales[i].*)
and CALL f(sales[i].*). An array inside an array element, a[i].b[j], converts too. One form is refused, with a
message that names the line: using the inner array as a whole, a[i].b with no subscript. As in 4GL, an array cannot be a parameter or a return value; pass the
element values, or let the function load its own array from the table.
Arrays are where converting to the server pays most. A function that loads a few hundred rows into an array, sorts or ranks them in memory and returns one number does the whole job inside the engine; the client sees one call and one value.
Functions that call functions
CALL tax(amount) RETURNING t is turned into a call through the engine’s function dispatch
(Fastpath). The converter works out the callee’s signature from the argument types, so the callee is
found by name and parameter types, as it would be from SQL. Recursion works the same way. The manifest and
the check script list every callee; if one is missing the first call fails with a clear
“routine not found” error, not a crash.
A function that returns several values (RETURN a, b, c) is installed with the extra values as
OUT parameters, and CALL f() RETURNING x, y, z reads them back. Such a function
can be called from SPL or from another converted function but not from a plain SELECT.
A callee that returns a set of rows (RETURN ... WITH RESUME) is read through a cursor on
EXECUTE FUNCTION, which is also how 4GL itself does it.
Benchmarks: 4GL against the UDR
The obvious worry about moving logic into a generated routine is speed. The test below uses five functions that
are pure computation, with no SQL inside them, so that the time measured is the code and not the database. Each
one is the same 4GL source run two ways: compiled with IBM c4gl 7.51 and run as a stand-alone
program, and converted with SQLMorph, built with -O2 and called with EXECUTE FUNCTION
on an Informix 15.0 engine. The machine is the same and the runs are back to back.
| Function | What it does | 4GL (s) | UDR (s) | UDR vs 4GL |
|---|---|---|---|---|
ex_sum(5000000) | integer loop, 5,000,000 iterations | 1.54 | 0.57 | 2.7x faster |
ex_primes(100) | sieve over a 30,000-element array, 100 passes | 0.91 | 0.68 | 1.3x faster |
ex_interest(1500000) | DECIMAL(16,6) compound interest, 1,500,000 steps | 1.47 | 0.64 | 2.3x faster |
ex_collatz(30000) | Collatz chain lengths for 1 to 30,000 | 1.62 | 0.61 | 2.7x faster |
ex_chars(1500000) | CHAR(40) substring tests, 1,500,000 iterations | 0.63 | 0.35 | 1.8x faster |
Every pair returns the same answer. The 4GL time is one call inside the program, from CURRENT
before and after, best of three runs. The UDR time is the wall clock of one statement through
dbaccess less an empty session, best of three, because the engine’s CURRENT has
one-second resolution. Treat the ratios as roughly right, not exact: on a loaded machine the 4GL timings alone
moved by up to three times between runs.
The converted code is not faster because C beats 4GL. It runs on the same kind of runtime, with typed values,
NULL checks and exact arithmetic. It is faster because the common cases are compiled in: the integer
operations are inlined into the generated C, short strings are reused instead of allocated again, and an exact
integer divide skips the decimal divide. The first version of the runtime was level with c4gl on
some of these and slower on others; profiling the slow cases is what produced those changes, and the table is
the result.
What the table does not show is the larger gain. A function that reads rows pays a client round trip for each
call in 4GL and none as a UDR, and the same function can be used from SQL, from SPL and from other routines.
That depends on your network and your data, so measure it on your own workload. A function that spends its
time waiting on SQL will not speed up from any of the changes above. The benchmarks are regenerated by
c/tests/native/examples/run_examples.sh, and the page at /sqlmorph/4gl/examples/
shows the full source, generated C and results.
What is refused
A 4GL program is more than its functions, and the backend is strict about the difference. It converts the whole file or nothing, and it names the line and the reason for each construct it cannot convert:
| Construct | Why |
|---|---|
MAIN, REPORT, GLOBALS, module-level DEFINE | A UDR is one function with no shared state. Pass globals as arguments. |
OPEN WINDOW, INPUT, DISPLAY ... TO, menus, prompts | A routine inside the server has no screen. These are rejected even in dead code, so that behaviour never changes silently. |
File, pipe and process I/O, CONNECT, session SET | Not meaningful for a routine running in the engine. |
BEGIN, COMMIT, ROLLBACK WORK | The calling statement owns the transaction. A policy for converted code is still to be decided. |
TEXT, BYTE | Not converted yet. |
Each refusal has a stable code (SQLM-4GL-UI003, SQLM-4GL-NYI002 and so on) so a
build script can count them. The practical way to start is to run the converter over your source tree and
see how many functions come out clean: the pure business-logic functions usually do, and the ones that
touch the screen do not.
$ sqlmorph -s informix-4gl order_entry.4gl # validate only, writes nothing
order_entry.4gl:2:5: error SQLM-4GL-UI003: enter_order: OPEN WINDOW: interactive/screen statement is not allowed in a converted function
LIKE and host variables
DEFINE x LIKE customer.name needs the column type, and a bare name inside an SQL statement is
a host variable only if no column has that name. Both need to know the schema, which you supply as a
dbschema snapshot:
$ dbschema -d mydb -t customer -t orders > mydb.sch
$ sqlmorph -s informix-4gl --schema mydb.sch -t informix-native -o out *.4gl
Because the function is going to run in a database, the converter can also read the schema from that database directly, so there is no snapshot to keep up to date:
$ sqlmorph -s informix-4gl --schema-db mydb --schema-table customer --schema-table orders \
-t informix-native -o out *.4gl
This is an option, off by default, because it has a cost: the converter runs dbschema against the
database, and reading every table of a large database is slow. --schema-table limits the read to the
tables your 4GL uses. Without --schema-db the converter never touches a database.
Before you rely on it
Test a converted function against the original with representative data, including NULLs, empty strings
and the edges of your dates and amounts. The runtime is checked against a real 4GL compiler: a set of 236
probes (variable defaults, NULL and empty-string rules, rounding, two-digit years, error numbers, USING
formats, date and string functions, MATCHES, loops, CASE and NULL logic, and more) is run as c4gl and as the converted function, and the two must agree. One of the things it
settled is that a 4GL DECIMAL assigned to an integer is truncated, not rounded, and that ""
is NULL. It cannot cover your own data, so do test yours.
If you find a difference, the report page takes the 4GL and the expected
result.
More: the SQLMorph product pages cover the other targets, and BLT recovers a functional specification from existing 4GL, which is a good first step when you do not know what a function is meant to do.