Using an Iterator Function

Home | Previous Page | Next Page Developing a User-Defined Routine > Planning the Routine >

Using an Iterator Function

By default, a user-defined function returns one value; that is, it calculates its return value and returns only once to its calling SQL statement. User-defined functions that return their result in a single return to the calling SQL statement are called noncursor functions because they do not require a database cursor to be executed. For information on how to invoke noncursor functions, see Invoking a UDR in an SQL Statement.

However, you can write a user-defined function that returns to its calling SQL statement several times, each time returning a value. Such a user-defined function is called an iterator function. An iterator function is a cursor function because it must be associated with a cursor when it is executed. The cursor holds the values that the cursor function repeatedly returns to the SQL statement. The calling program can then access the cursor to obtain each returned value, one at a time. The contents of the cursor are called an active set. Each time the iterator function returns a value to the calling SQL statement, it adds one item to the active set.

Important:

You cannot use OUT parameters in iterator functions.

Creating an Iterator Function

You can write iterator functions in SPL, C, or Java. Each language uses different statements, functions, and methods to manage iterator tasks:

An SPL iterator function uses the FOREACH keyword in conjunction with the RETURN WITH RESUME statement.
A C-language iterator function uses DataBlade API functions, such as mi_fp_setisdone() and mi_fp_request(), to handle each return item of the active set. MI_FPARAM maintains the iterator state that mi_fp_setisdone() and mi_fp_request() access.
A Java iterator function uses the UDREnv interface, which provides all necessary methods and constants.

Registering an Iterator Function

By default, a function written in an external language is not an iterator. To define an iterator function written in C or Java, you must register the function with the ITERATOR routine modifier. The following sample CREATE FUNCTION statement shows how to register the function TopK() as an iterator function in C:

CREATE FUNCTION TopK(INTEGER, INTEGER)
   RETURNS INTEGER
   WITH (ITERATOR, NOT VARIANT)
   EXTERNAL NAME
      '/usr/lib/extend/misc/topkterms.so(topk_integers)'
   LANGUAGE C

Tip:

An SPL iterator function does not need to be registered using the ITERATOR modifier.

Invoking an Iterator Function

You can invoke an iterator function using one of the following methods:

Directly with the EXECUTE FUNCTION statement:
- From DB–Access
- In a prepared cursor in an external routine
- In an external routine
- In an SPL FOREACH loop
With an EXECUTE FUNCTION statement as part of an INSERT statement:
- From DB–Access
- In a prepared cursor in ESQL/C or an external routine
- In a DataBlade API database server routine
- In an SPL FOREACH loop
In the FROM clause of a SELECT statement
Instead of a table, the result set of the iterator function is the source from which the query selects data. The return values from the iterator function are mapped to a virtual table. Using an iterator function in a FROM clause is described in detail, next.

Existing iterator UDRs from pre-9.4 releases can be used in the FROM clause of a SELECT statement.

Using an Iterator Function in the FROM Clause of a SELECT Statement

In addition to tables, an iterator function can be specified as a source for a SELECT statement. This means you can query the return result set of an iterator UDR using a table interface. Therefore, you can manipulate the iterator result set in a number of ways, such as by using the WHERE clause to filter the result set; by joining the UDR result set with other table scans; by running GROUP BY, aggregation, and ORDER BY operations, and so on.

Syntax and Usage

The syntax for using an iterator function in the FROM clause is:

FROM TABLE (FUNCTION iterator_func_name ([argument_list]))
[[AS] virtual_table_name] [(virtual_column_list)]

The virtual_table_name parameter is unqualified (do not include the owner or database name) and specifies the name of the virtual table that holds the result set from the iterator function.

Important:

The virtual table can only be referenced within the context of this SELECT query. After the SELECT statement completes, the virtual table no longer exists.

The virtual_column_list parameter is a comma-separated list of unqualified column names for the virtual table. The number of columns must match the number of values returned by the iterator (SPL functions can return more than one value).

If you want to reference virtual table columns in other parts of the SELECT statement, for example, in the projection list, WHERE clause, or HAVING clause, you must specify the virtual table name and virtual column names in the FROM clause. You do not have to specify the virtual table name or column names in the FROM clause if you use wildcard characters in the projection list of the SELECT clause:

SELECT * FROM ...

As an example, the following statement retrieves the result set from the function called fibseries(). This result set is held in the virtual table called vtab.

SELECT col FROM TABLE (FUNCTION fibseries(10)) vtab(col);

If a SELECT statement specifying an iterator in the FROM clause returns unexpected results, execute the iterator function separately to verify the function is behaving correctly. For example, run your function in DB-Access with a command like this:

execute function iterator_udr(args)

The SQL Explain output section for a virtual table derived from an iterator UDR is marked ITERATOR UDR SCAN.

Ensure that you call mi_fp_setisdone() in a C UDR or UDREnv.setSetIterationIsDone(true) in a JAVA UDR when the iterator UDR is finished. The server checks this flag internally to determine when to stop calling the iterator UDR.

Allocating Memory

For iterator functions written in C, the default memory duration for return values set by the server should be sufficient.

The MI_FPARAM data structure should be allocated a duration that lasts for all iterations, usually a PER_COMMAND duration.

Running Parallel Queries

If you are running queries in parallel using the IBM Informix Dynamic Server parallel database query (PDQ) feature and the iterator UDR in the FROM clause is not parallelizable, query parallelism is turned off for the SELECT query. However, if the iterator UDR in the FROM clause is parallelizable and no other factors disable the query parallelism, the query can run in parallel. When PDQ is on, functional tables are treated as single non-fragmented tables.

In the following example, the GROUP BY and aggregation operations can be run by multiple PDQ threads and the fibseries() function can be run by a secondary thread.

SELECT col1,col2, COUNT(*) FROM TABLE (FUNCTION fibseries(10)) 
tab1(col1),tab2
GROUP BY col1,col2;

Refer to your IBM Informix: Dynamic Server Performance Guide for information about running queries in parallel.

Restrictions

The following restrictions apply to using iterator functions in the FROM clause:

Iterator functions cannot refer to other columns in the FROM clause. For example, the following query is invalid because the fibseries iterator function specifies the column t.x as an argument:
```
SELECT t.x, vtab.col 
FROM t, TABLE (FUNCTION fibseries(t.x)) vtab(col);
```
However, iterator functions can refer to other columns when used in an outer query, as in:
```
SELECT t.x FROM t
WHERE t.y IN 
  (SELECT col FROM TABLE (FUNCTION fibseries(t.y)) vtab(col));
```
Iterator functions cannot generate OUT parameters and statement-local variables.
You cannot use iterator functions as the target in INSERT, UPDATE, or DELETE statements.
UDRs used in the FROM clause must be iterator functions.

Example SPL Iterator Function

To create an SPL iterator function to be used in the FROM clause, your function must use the RETURN WITH RESUME construct, as shown in the following example.

Because an SPL UDR can return more than one value, you can specify multiple column names in the virtual column list in the FROM clause. You can reference any of these virtual column names in the target list of the SELECT query.

create function find_top_earners()
    returning integer,decimal,lvarchar
define ret_empid integer;
define ret_salary decimal;
define ret_empname lvarchar;
    foreach select emp_id,salary into
        ret_empid,ret_salary from salary
        if (ret_salary > 100000.00)
          select emp_name into ret_empname from employee
          where emp_id = ret_empid;
        return ret_empid,ret_salary,ret_empname with 
          resume;
        end if;
    end foreach;
end function;

The following query uses the above iterator UDR, find_top_earners(), to retrieve the top earners sorted by employee name.

select vemp_name,vemp_id,vemp_sal from
    table (function find_top_accounts()) 
    vtab1(vemp_name,vemp_id,vemp_sal)
    order by vemp_name;

Example C Iterator Function

To write an iterator C function, you use DataBlade API functions, such as mi_fp_request(), mi_fp_setfuncstate(), mi_fp_setisdone(), and so on, with the MI_FPARAM data structure.

A C UDR can return only one value; therefore, there can be only one column in the virtual column list in the FROM clause. However, a C UDR can return a row type, which can capture multiple return values as a unit.

The following example demonstrates how to write a C iterator function and use it in the FROM clause; relevant DataBlade API and iterator states are highlighted.

The function fibseries() is an iterator function that returns the Fibonacci series up to the value passed to it as an argument.

create function fibseries(int x)
returns int with (handlesnulls,iterator, parallelizable)
external name "$USERFUNCDIR/fib.so"
language c;

/* A Function to return a set of integer. This function takes 
stop val as a parameter and returns a fibonaucci series up to 
stop val. 

* Three states of fparam :
*
* SET_INIT: Allocate the function state structure defined. 
This State Structure is allocated in PER_COMMAND duration to
hold the memory till the end of the command.
Make the fparam structure point to the State Structure.
Set the first two numbers of the series i.e 0 and 1; And 
set the stop val field of State Structure to the stop val passed 
to the function. 

* SET_RETONE: Computes the next number in the series. Compares 
it with the stop val to check if the exit criteria is met.  
num1 = num2;num2 = next number in the series.

* SET_END: Frees the user Allocated Func State structure.
*/

#include <milib.h>
typedef struct fibState1 {
   mi_integer fib_prec1;
   mi_integer fib_prec2;
   mi_integer fib_ncomputed;
   mi_integer fib_endval;
}fibState;
mi_integer
fibseries(endval,fparam)
mi_integer endval;
MI_FPARAM  *fparam;
{
     fibState   *fibstate;
     mi_integer next;
     switch(mi_fp_request(fparam)) {
        case SET_INIT :
             fibstate = (fibState *) mi_dalloc 
(sizeof(fibState),PER_COMMAND);

             mi_fp_setfuncstate(fparam,(void *)fibstate);
             if (mi_fp_argisnull(fparam,0) || endval < 0) {
                mi_fp_setreturnisnull(fparam,0,1);
                break;
             }
             if (endval < 1) {
                fibstate->fib_prec1 = 0;
                fibstate->fib_prec2 = 1;
                fibstate->fib_ncomputed = 1;
                fibstate->fib_endval = endval; 
             }
             else {
                fibstate->fib_prec1 = 0;
                fibstate->fib_prec2 = 1;
                fibstate->fib_ncomputed = 0;
                fibstate->fib_endval = endval;                 
             }
             break;
        case SET_RETONE :
             fibstate = mi_fp_funcstate(fparam);
             if (fibstate->fib_ncomputed < 2) {
                return((fibstate->fib_ncomputed++ == 0) ? 0 : 1);
             }
             next = fibstate->fib_prec1 + fibstate->fib_prec2;
             if (next > fibstate->fib_endval) {
                mi_fp_setisdone(fparam,1);
                return 0;
             }
             if (next == 0) {
                fibstate->fib_prec1 = 0;
                fibstate->fib_prec1 = 1;
             }
             else {
                fibstate->fib_prec1 = fibstate->fib_prec2;
                fibstate->fib_prec2 = next;
                
             }
             return (next);
        case SET_END :
             fibstate = mi_fp_funcstate(fparam); 
             mi_free(fibstate);
             break;
     }
}

This function can be used in the FROM clause of a SELECT query:

select vcol1 from table (function fibseries(100)) vtab1(vcol1);

Example Java Iterator Function

The UDREnv interface provides all necessary methods and constants. A Java UDR can return only one value; therefore, there can be only one column in the virtual column list in the FROM clause.

The following example demonstrates how to write a Java iterator function and use it in FROM clause; relevant DataBlade API and iterator states are highlighted.

The iterator UDR jenv_iter() takes an integer parameter and returns a row of CHAR(40) columns. The parameter passed in determines the number of rows it returns.

public interface UDREnv
{
...
// for maintaining state across UDR invocations
void setUDRState(Object state);
Object getUDRState();
// for set/iterator processing
public static final int UDR_SET_INIT = 1;
public static final int UDR_SET_RETONE = 2;
public static final int UDR_SET_END = 3;
int getSetIterationState();
void setSetIterationIsDone(boolean value);

...
}
import java.lang.*;
import java.sql.*;
import com.informix.udr.*;
import informix.jvp.*;
public class Env
{
    public int count;
    //
    // test UDR meta
    //
    
    public static String envTest1(int i, String xchar, String
xvchar, String xlvarchar)
    throws SQLException
    {
UDREnv env = UDRManager.getUDREnv();
String res = env.getName() + "#" + 
env.getReturnTypeName() + "#";
String param[] = env.getParamTypeName();
for (int j = 0; j < param.length; ++ j)
    res += param[j] + "#";

res += i + xchar + xvchar + xlvarchar;
return res;
    }
    public static String envTest2(int i, String s[])
    throws SQLException
    {
UDREnv env = UDRManager.getUDREnv();
UDRLog log = env.getLog();
String res = env.getName() + "#" + 
env.getReturnTypeName() + "#";
String param[] = env.getParamTypeName();
for (int j = 0; j < param.length; ++ j)
    res += param[j] + "#";
res += i;
log.log(res);
s[0] = res;
return res;
    }
    //
    //test env state, iterator, log, traceable, and 
properties
    //
    public static String envIter(int num)
throws SQLException
    {
UDREnv env = UDRManager.getUDREnv();
UDRLog log = env.getLog();
UDRTraceable tr = env.getTraceable();
JVPProperties pr = env.getProperties();
int iter = env.getSetIterationState();
Env state = (Env)env.getUDRState();
        if (iter == UDREnv.UDR_SET_INIT)
    {
    state = new Env();
    state.count = num;
    env.setUDRState(state);
    log.log("SET INIT" + state.count + " " + 
state.toString());

    tr.tracePrint("UDR.ENVITER", 0, "SET INIT");
       env.setSetIterationIsDone(false);
    pr.setProperty("ENVITERPROP", "AFTER INIT");
    return "INIT";
    }
        else if (iter == UDREnv.UDR_SET_END)
    {
    log.log("SET DONE");
    tr.tracePrint("UDR.ENVITER", 0, "SET DONE");
       env.setSetIterationIsDone(true);
    return "DONE";
    }
        else if (iter == UDREnv.UDR_SET_RETONE)
    {
    log.log("SET RETONE" + state.count + " " + 
state.toString());
    tr.tracePrint("UDR.ENVITER", 0, "SET RETONE");
    String prv = pr.getProperty("ENVITERPROP");

    if (state.count <= 0)
env.setSetIterationIsDone(true);
    else
env.setSetIterationIsDone(false);
    -- state.count;
    pr.setProperty("ENVITERPROP", "AFTER RETONE" + 
(state.count + 1));
    return new String("ELEMENT " + (state.count + 1) ); 
//+ prv);
    }
        else
    throw new SQLException("Bad iter code");
    }
}

The following statement creates the Java iterator UDR, jenv_iter().

create function jenv_iter(int)
    returning char(40)
    with (class = "jvp", iterator)
    external name `Env.envIter(int)'
    language java;