External Environment for UDFs

An improperly defined UDF may cause memory violations or may lead to a database server failure. Running a UDF outside the database server, in an external environment, eliminates this risk to the server.

If a runtime exception occurs in the external environment, the server process is unaffected. The server issues an error to the UDF caller, and any subsequent calls to the UDF result in a restart of the external environment.

Note: The external runtime environments do not require the IQ_UDF or IQ_IDA license. The external runtime environments do not require the a_v3_extfn or a_v4_extfn APIs.

The database server includes support for these external runtime environments for UDFs:

ESQL and ODBC (C/C++ Embedded SQL or ODBC server-side requests)
Java
Perl
PHP

Each environment has its own set of APIs for processing arguments and returning values back to the server. The Java external environment, for example, uses the JDBC API.

System Tables

The system table SYSEXTERNENV stores the information needed to identify and launch each of the external environments.

The system table SYSEXTERNENVOBJECT stores non-Java external objects.

SQL Statements

The following SQL syntax allows you to set or modify the location of external environments in the SYSEXTERNENV table.

 ALTER EXTERNAL ENVIRONMENT environment-name 
     [ LOCATION location-string ]

Once an external environment is set up to be used on the database server, you can then install objects into the database and create stored procedures and functions that make use of these objects within the external environment. Installing, creating, and using these objects, stored procedures, and stored functions is similar to installing Java classes and creating and using Java stored procedures and functions.

To add a comment for an external environment, you can execute:

 COMMENT ON EXTERNAL ENVIRONMENT environment-name
  IS comment-string

To install a Perl or PHP external object (for example, a Perl script) from a file or an expression into the database, execute an INSTALL EXTERNAL OBJECT statement similar to:

INSTALL EXTERNAL OBJECT object-name-string
  [ update-mode ] 
  FROM { FILE file-path | VALUE expression }
  ENVIRONMENT environment-name

To add a comment for an installed Perl or PHP external object, you can execute:

 COMMENT ON EXTERNAL [ENVIRONMENT] OBJECT object-name-string 
  IS comment-string

To remove an installed Perl or PHP external object from the database, use a REMOVE EXTERNAL OBJECT statement:

 REMOVE EXTERNAL OBJECT object-name-string

Once external objects are installed in the database, you can use them in external stored procedure and function definitions (similar to the current mechanism for creating Java stored procedures and functions).

 CREATE PROCEDURE procedure-name(...)
  EXTERNAL NAME '...' 
  LANGUAGE environment-name
 
 CREATE FUNCTION function-name(...)
  RETURNS ... 
  EXTERNAL NAME '...'
  LANGUAGE environment-name

Once these stored procedures and functions are created, you can use them like any other stored procedure or function in the database. The database server, when encountering an external environment stored procedure or function, automatically launches the external environment (if it has not already been started), and sends the necessary information to get the external environment to fetch the external object from the database and execute it. Any result sets or return values resulting from the execution are returned as needed.

To start or stop an external environment on demand, use the START EXTERNAL ENVIRONMENT and STOP EXTERNAL ENVIRONMENT statements:

 START EXTERNAL ENVIRONMENT environment-name
STOP EXTERNAL ENVIRONMENT environment-name

Executing UDFs from an External Environment
Execute UDFs in the ESQL, ODBC, Java, Perl, or PHP external environments.
External Environment Restrictions
Restrictions apply to all external environments for UDFs.
The ESQL and ODBC External Environments
To run a compiled native C function in an external environment instead of within the database server, the stored procedure or function is defined with the EXTERNAL NAME clause followed by the LANGUAGE attribute specifying one of C_ESQL32, C_ESQL64, C_ODBC32, or C_ODBC64.
The Java External Environment
The database server includes support for Java stored procedures and functions. A Java stored procedure or function behaves the same as a SQL stored procedure or function except that the code for the procedure or function is written in Java and the execution of the procedure or function takes place outside the database server (that is, within a Java VM environment).
PERL External Environment
A Perl stored procedure or function behaves the same as a SQL stored procedure or function except that the code for the procedure or function is written in Perl and the execution of the procedure or function takes place outside the database server (that is, within a Perl executable instance).
PHP External Environment
A PHP stored procedure or function behaves the same as a SQL stored procedure or function except that the code for the procedure or function is written in PHP and the execution of the procedure or function takes place outside the database server (that is, within a PHP executable instance).