Back to Contents

ODI Guide For IBM Informix database servers 5.x, 7.x, 8.x, 9.x, 10.x, 11.x

Introduction

Runtime Configuration

Fully supported IBM Informix SQL features

Partially supported IBM Informix SQL features

Un-supported IBM Informix SQL features

Purpose of the Informix ODI guide

This section contains information to configure your Genero runtime system to work with an Informix database engine, and describes the IBM Informix SQL features that are not supported (or partially supported) by Genero BDL.

Understand that Genero BDL was designed to work with IBM Informix databases, so most of the IBM Informix SQL features are supported. However, new features implemented in recent server versions need modifications in the Genero BDL compilers and runtime system to be supported.

Some topics show an enhancement reference note with a number, identifying the request id as filed in our internal "TODO" database. If the SQL feature is mission critical for your application, contact the support center and mention the enhancement identifier. 

Runtime configuration

Supported IBM Informix server and CSDK versions

1. Genero BDL is certified with all IBM Informix servers from version 5.x to the latest 11.x version, including the Standard Engine, On-Line and IDS server families, as long as the IBM Informix Client SDK is compatible with the server.

2. Genero BDL is certified with IBM Informix SDK version 3.50 or higher.

Install IBM Informix and create a database - database configuration/design tasks

1. Install the IBM Informix database software (IDS for example) on your database server.

2. Install the IBM Informix Software Development Kit (SDK)  on your application server.
   With some IBM Informix distributions (IDS 11), this package is included in the server bundle.
   You should check the IBM web site for SDK upgrades or patches.
   Genero BDL is certified with IBM Informix SDK version 3.50 or higher.

3. Define a database user dedicated to your application: the application administrator, referenced as appadmin in the next parts of this documentation. This user will manage the database schema of the application (all tables will be owned by it). With IBM Informix, database users reference Operating System users, and must be part of the IBM Informix group. See IBM Informix documentation.

4. Connect to the server as IBM Informix user (for example with the dbaccess tool) and give all requested database administrator privileges to the application administrator.
   
      GRANT CONNECT TO appadmin ;
      GRANT RESOURCE TO appadmin ;
      GRANT DBA TO appadmin ;

5. Connect as application administrator and create an IBM Informix database entity, for example with the following SQL statement:
   
    CREATE DATABASE dbname WITH BUFFERED LOG;

6. Create the application tables.

Prepare the runtime environment - connecting to the database

1. In order to connect to IBM Informix, you must have a database driver "dbmifx*" in FGLDIR/dbdrivers.

2. Make sure the the IBM Informix client environment variables are properly set. Check for example INFORMIXDIR (the path to the installation directory), INFORMIXSERVER (the name of the server defined in the sqlhosts list), etc. For more details, see the IBM Informix documentation.

3. In order to connect to an IBM Informix server, you must define a line in the $INFORMIXDIR/etc/sqlhosts file, referencing the server name specified in the INFORMIXSERVER environment variable. On Windows platforms, the sqlhost entries are defined in the registry database. See IBM Informix documentation.

4. Verify the environment variable defining the search path for IBM Informix SDK database client shared libraries. On UNIX platforms, the variable is specific to the operating system. For example, on Solaris and Linux systems, it is LD_LIBRARY_PATH, on AIX it is LIBPATH, or HP/UX it is SHLIB_PATH. On Windows, you define the DLL search path in the PATH environment variable.

   IBM Informix SDK version	Shared library environment setting
   All versions	UNIX: Add $INFORMIXDIR/lib, $INFORMIXDIR/lib/esql, $INFORMIXDIR/lib/tools and $INFORMIXDIR/lib/cli to LD_LIBRARY_PATH (or its equivalent). Windows: Add %INFORMIXDIR%\bin to PATH.

5. Check the database locale settings (CLIENT_LOCALE, DB_LOCALE, etc). The DB locale must match the locale used by the runtime system (LANG).

6. To verify if the IBM Informix client environment is correct, you can start the SQL command interpreter:
   
        $ dbaccess - -
        > CONNECT TO "dbname" USER "appadmin";
          ENTER PASSWORD: password

7. Set up the fglprofile entries for database connections.
   
   Important:  Make sure that you are using the ODI driver corresponding to the database client and server version.

What are the supported IBM Informix SQL features?

Genero BDL was first designed for IBM Informix databases. The answer to the above question is: Every SQL feature that is not listed in the other sections of this chapter.

The following list gives an idea of the IBM Informix SQL elements you can use with Genero BDL:

• Database connection control instructions (DATABASE, CONNECT). See Connections.
• Transaction control instructions and concurrency settings (BEGIN WORK, SET ISOLATION). See Transactions.
• Basic, portable data types (lNT, BIGINT, DECIMAL, CHAR, VARCHAR, DATE, DATETIME, TEXT, BYTE, etc). See Data Types.
• Common Data Definition Language statements (CREATE TABLE, DROP TABLE, etc). See Static SQL.
• Common Data Manipulation Language statements (SELECT, INSERT, UPDATE, DELETE, etc). See Static SQL.
• Result set handling with cursors (DECLARE / OPEN / FETCH / CLOSE / FREE). See Result Sets.
• Positioned UPDATEs and DELETEs (UPDATE/DELETE WHERE CURRENT OF). See Positioned Updates.
• Cursors to insert rows (DECLARE / OPEN / PUT / FLUSH). See Insert Cursors.
• Stored procedure calls. See SQL Programming.
• SQL execution status and error messages (SQLCA, SQLSTATE). See Connections.
• Global Language Support with single and multi-byte character sets for CHAR/ VARCHAR data storage. See Localization.
• LOAD and UNLOAD utility statements. See I/O SQL instructions.
• Database schema extraction to define program variables LIKE database columns. See Database Schema.

The NCHAR / NVARCHAR data types

IBM Informix supports the standard NCHAR and NVARCHAR data types. These types are equivalent to CHAR and VARCHAR (the same character set is used), except that the collation order is locale specific with NCHAR/NVARCHAR types.

With Genero BDL, you can handle character strings of NCHAR/NVARCHAR database columns by using program variables defined with the CHAR/VARCHAR types. Since the character set is identical for NCHAR/NVARCHAR and CHAR/VARCHAR, not specific consideration needs to be given for the "N" character types.

When extracting a database schema with fgldbsch, NCHAR/NVARCHAR types will be identified in the .sch file by the native Informix type codes 15 and 16. When compiling .4gl or .per sources referencing NCHAR/NVARCHAR columns in the schema file, the compilers will automatically use the CHAR and VARCHAR Genero BDL types for the type codes 15 and 16.

However, Genero BDL is missing full support of NCHAR and NVARCHAR types as it is not possible to declare program variables directly with the NCHAR / NVARCHAR keywords. Further, sorting features of Genero should follow the same collation order as the IBM Informix database when using "N" character types.

Enhancement reference: 20004

 The LVARCHAR data type

IBM Informix supports the LVARCHAR type as a "large" VARCHAR type. The LVARCHAR type was introduced to bypass the 255 bytes size limitation of the standard VARCHAR type. Starting with IDS version 9.4, the LVARCHAR size limit is 32739 bytes. In older versions the limit was 2048 bytes.

Genero BDL does not support the LVARCHAR type natively, but it has the VARCHAR type which can hold up to 65535 bytes. IBM Informix LVARCHAR values can be inserted or fetched by using the BDL VARCHAR type.

Static SQL statements such as CREATE TABLE can be include the LVARCHAR column type.

When extracting a schema with fgldbsch, LVARCHAR(N) columns will by default be converted to VARCHAR2(N) in the schema file. VARCHAR2 is a Genero BDL-only pseudo type identified with the type code 201, and allows define VARCHAR variables with a size that can be greater than 255 bytes.

Enhancement reference: 3464

DISTINCT data types

IBM Informix supports DISTINCT data types as User Defined Types based on a source data type, but with different casts and functions than those on the source data type.

Genero BDL partially supports the IBM Informix DISTINCT data types:

The fgldbsch schema extractor can extract columns defined with a distinct type and write the distinct type code in the .sch schema file. For more details, see Distinct type codes in the Database Schema page. 

However, there are some restrictions you must be aware of:

• It is not possible to define BDL variables explicitly with the name of a distinct type. Variables must be defined indirectly with the schema by using the DEFINE LIKE statement. 
• The static SQL syntax does not support OPAQUE-related syntax elements:
  • The DDL statements CREATE DISTINCT TYPE, DROP TYPE, CREATE CAST, and DROP CAST are not allowed,
  • In CREATE TABLE / ALTER TABLE DDL statements, the data type must be a built-in type.
  • The :: cast operator is not supported.

Enhancement reference: 20003

Stored Procedures

With IBM Informix database servers, you can write stored procedures with the SPL (Stored Procedure Language) or with an external language in C or JAVA.

Note that if you plan to support different types of database servers, you must be aware that each DB vendor has defined its own stored procedure language. In such cases, you may consider writing most of your business logic in BDL, and implementing only some stored procedures in the database, mainly to get better performance or to use database features that only exist with stored procedures.

Genero BDL partially supports SP creation, but has full support of SP invocation:

• The Genero BDL static SQL syntax does not include CREATE FUNCTION and CREATE PROCEDURE with a body block. However, you can create stored procedures with an body block by using dynamic SQL (EXECUTE IMMEDIATE), or with CREATE PROCEDURE and the FROM filename clause, which is supported by Genero BDL static SQL.
• The EXECUTE FUNCTION or EXECUTE PROCEDURE instruction is not allowed in the static SQL syntax. To invoke a stored procedure with Informix, you must use the PREPARE instruction, followed by EXECUTE or OPEN. The PREPARE instruction must initiate the EXECUTE FUNCTION/PROCEDURE instruction. 

For more details about stored procedure invocation, see SQL Programming.

Database Triggers

Triggers can be created for IBM Informix database tables with the CREATE TRIGGER instruction.

Note that if you plan to support different types of database servers, you must be aware that each DB vendor has defined its own trigger creation syntax and stored procedure language. In such cases, you may consider writing most your business logic in BDL, and implementing only some triggers in the database, mainly to get better performance or use database features that only exist with stored procedures.

Genero BDL partially supports trigger creation:

• The Genero BDL static SQL syntax does not include the CREATE TRIGGER and DROP TRIGGER instructions. However, you can create database triggers by using dynamic SQL (EXECUTE IMMEDIATE).

Optimizer directives

IBM Informix SQL allows you to specify query optimization directives to force the query optimizer to use a different path than the implicit plan. With IBM Informix, optimizer directives are specified with the following SQL comment markers followed by a plus sign:

/*+ optimizer-directives */
{+  optimizer-directives }
--+ optimizer-directives

Genero BDL partially supports optimizer directives:

• The static SQL syntax does not allow the C-style optimizer syntax.
• The curly-brace and dash-dash optimizer directive syntaxes cannot be used in static SQL statements, because these correspond to the 4GL language comments.
• However, you can execute queries with optimization directives with Dynamic SQL.

Note that optimization directives are not portable. If you plan to use different types of database servers, you should avoid the usage of query plan hints.

XML publishing support

IBM Informix IDS 11.10 introduced a set of XML built-in functions when the idsxmlvp virtual processor is turned on. Built-in XML functions are of two types: Those returning LVARCHAR values, and those returning CLOB values. For example, genxml() returns an LVARCHAR(32739), while genxmlclob() returns a CLOB. XML data is typically stored in LVARCHAR or CLOB columns.

Genero BDL partially supports XML functions:

• Because Genero BDL does not support BLOB/CLOB types, functions returning CLOB values cannot be used. You can however use the XML functions returning LVARCHAR values, and fetch the result into a VARCHAR variable of the appropriate size.
• Some of the XML functions such as genxml() take ROW() values as parameters. Because literal unnamed ROW() expressions are like regular function calls, you can use XML functions in static SQL statements.

Example:

01 FUNCTION get_cust_data(id)
02   DEFINE id INT, v VARCHAR(5000)
03   SELECT genxml(ROW(cust_name, cust_address), "custdata") INTO v
04      FROM customers WHERE cust_id = id
05   RETURN v
06 END FUNCTION

DataBlade modules

IBM Informix IDS provides several database extensions implemented with the DataBlade Application Programming Interface, such as MQ Messaging, Large OBjects management, Text Search DataBlades, Spatial DataBlade Module, etc.

Genero BDL partially supports DataBlade modules:

• DataBlade extensions are based on User Defined Functions and User Defined Types. It is not possible to define program variables with specific User Defined Types. For example, you cannot define a program variable with the ST_Point type implemented by the Spatial DataBlade module.
• The static SQL grammar does not support DataBlade specific syntax. For example, it is not possible to create a Basic Text Search index with the USING bts clause of the CREATE INDEX statement.

However, as long as the syntax of the DataBlade functions follows basic SQL expressions, it can be used in static SQL statements. For example, the next query uses the bts_contains() function of the Basic Text Search extension:

   SELECT id FROM products WHERE bts_contains( brands, 'standard' )

You can also use Dynamic SQL to perform queries with a syntax that is not allowed in the static SQL grammar.

Specific CREATE INDEX clauses

In addition to the standard index-key specification using a column list, the CREATE INDEX statement supported by IBM Informix SQL allows specific clauses,  for example to define storage options.

Genero BDL partially supports the CREATE INDEX statement; the following are not supported in static SQL grammar:

• The IF NOT EXISTS clause.
• Functional index specification is now allowed in the index-key list.
• Storage options such as IN dbspace, EXTEND SIZE, NEXT SIZE.
• The index mode clauses such as FILTERING WITH/WITHOUT ERROR.
• The USING clause.
• The HASH ON clause.
• The FILLFACTOR clause.

You can use Dynamic SQL to execute CREATE INDEX statements with clauses that are not allowed in the static SQL grammar.

Other SQL instructions

Genero BDL static SQL syntax implements common Data Manipulation Statements such as SELECT, INSERT, UPDATE and DELETE. Data Definition Language statements such as CREATE TABLE, CREATE INDEX, CREATE SEQUENCE and their corresponding ALTER and DROP statements are also part of the static SQL grammar. These are supported with a syntax limited to the standard SQL clauses. For example, Genero BDL might not support the most recent CREATE TABLE storage options supported by IBM Informix SQL.

Since the first days of the 4GL language the SQL language has been extended,  and it has become so large that it's impossible to embed all the existing new statements without introducing grammar conflicts with the 4GL language. In addition, each DB vendor has improved the standard SQL language with proprietary SQL statements that are not portable; it would not be a good idea to use these specific instructions if you plan to make your application run with different types of database engines.

However, the Genero BDL static SQL is constantly improved with standard SQL syntax that works with most types of database servers. For example, Genero BDL supports the ANSI outer join syntax, constraints definition in DDL statements, sequence instructions, BIGINT and BOOLEAN data types, and there is more to come.

If a statement is unsupported in static SQL, that does not mean that you cannot execute it. If you want to execute an SQL instruction that is not part of the static SQL grammar, you can use Dynamic SQL as follows:

• Use PREPARE + EXECUTE for statements that do not generated a result set
• Use (PREPARE/) DECLARE + OPEN for statements returning a result set
• Use EXECUTE IMMEDIATE if no SQL parameters are required and no result set is generated

Dynamic SQL instructions take a string as the input, so there is no limitation regarding the SQL text you can execute; however, only one statement can be executed at a time. It is better, however, to write your SQL statements directly in static SQL when possible, because it makes the code more readable and the syntax is checked at compiled time.

For more details about statements supported in the static SQL syntax, see Static SQL.

Below is a list of the IBM Informix SQL statements that are not allowed in the static SQL syntax (last updated from IDS 11.50 SQL instructions). Note that the IBM Informix SQL Syntax manual includes ESQL/C specific statements such as ALLOCATE DESCRIPTOR, which are not part of the basic SQL statements supported by the engines. ESQL/C specific statements are not listed here:

ALTER ACCESS_METHOD
ALTER FRAGMENT
ALTER FUNCTION
ALTER PROCEDURE
ALTER ROUTINE
ALTER SECURITY LABEL COMPONENT
CREATE ACCESS_METHOD
CREATE AGGREGATE
CREATE CAST
CREATE DISTINCT TYPE
CREATE EXTERNAL TABLE Statement
CREATE FUNCTION (with body)
CREATE OPAQUE TYPE
CREATE OPCLASS
CREATE PROCEDURE (with body)
CREATE ROLE
CREATE ROUTINE FROM
CREATE ROW TYPE
CREATE SCHEMA
CREATE SECURITY LABEL
CREATE SECURITY LABEL COMPONENT
CREATE SECURITY POLICY
CREATE TRIGGER
CREATE VIEW
CREATE XADATASOURCE
CREATE XADATASOURCE TYPE
DROP ACCESS_METHOD
DROP AGGREGATE
DROP CAST
DROP FUNCTION
DROP OPCLASS
DROP PROCEDURE
DROP ROLE
DROP ROUTINE
DROP ROW TYPE
DROP SECURITY
DROP TRIGGER
DROP TYPE
DROP XADATASOURCE
DROP XADATASOURCE TYPE
EXECUTE FUNCTION
EXECUTE PROCEDURE
GRANT FRAGMENT
INFO
MERGE
OUTPUT
RELEASE SAVEPOINT
RENAME COLUMN
RENAME DATABASE
RENAME SECURITY
REVOKE FRAGMENT
SAVE EXTERNAL DIRECTIVES
SAVEPOINT
SET AUTOFREE
SET COLLATION
SET CONSTRAINTS
SET DATASKIP
SET DEBUG FILE
SET ENCRYPTION PASSWORD
SET ENVIRONMENT
SET INDEXES
SET LOG
SET OPTIMIZATION
SET PDQPRIORITY
SET ROLE
SET SESSION AUTHORIZATION
SET STATEMENT CACHE
SET TRANSACTION
SET TRIGGERS
START VIOLATIONS TABLE
STOP VIOLATIONS TABLE

CLOB and BLOB data types

In addition to the TEXT and BYTE data types (known as Simple Large Objects), IBM Informix servers support the CLOB and BLOB types to store large objects. CLOB/BLOB are known as Smart Large Objects. The main difference is that Smart Large Objects support random access to the data - seek, read and write through the LOB as if it was a OS file.

Genero BDL does not support the CLOB and BLOB types:

• It is not possible to define BDL variables with the CLOB or BLOB types, so you cannot manipulate CLOB/BLOB objects within programs.
• Defining a TEXT / BYTE variable to hold CLOB / BLOB column data is not supported; you will get error -609 (Illegal attempt to use a Text/Byte host variable).
• The static SQL syntax for DDL statements like CREATE TABLE does not allow the CLOB / BLOB keywords for column types.
• The fgldbsch schema extractor will report an invalid data type if you try to get the schema for a table with a CLOB or BLOB column.

You can, however:

• Create a table with CLOB/BLOB columns by using Dynamic SQL.
• Use the Smart Large Object functions FILETOBLOB(), FILETOCLOB(), LOCOPY(), LOTOFILE() in static SQL statements.

Enhancement reference: 476

The LIST data type

In IBM Informix databases, the LIST type is a collection type that can store ordered elements of a specific base type. Unlike the MULTISET type, the elements of a LIST have ordinal positions. Elements can be duplicated.

Genero BDL does not support the IBM Informix LIST data type.

• It is not possible to define BDL variables with the LIST type.
• The static SQL syntax does not support collection-related syntax elements:
  • DDL statements like CREATE TABLE cannot use the LIST keyword for column types,
  • The collection-derived notation TABLE() is not allowed,
  • The INSERT AT position instruction is not supported,
  • The LIST { } literal syntax is not allowed.
  • The <value> IN <identifier> syntax is not allowed.
• The fgldbsch schema extractor will report an invalid data type if you try to get the schema for a table with a LIST column.

The MULTISET data type

The MULTISET IBM Informix data type is a collection type that can store non-ordered elements of a specific base type. Unlike the LIST type, the elements of a MULTISET have no ordinal positions. Elements can be duplicated.

Genero BDL does not support the IBM Informix MULTISET data type:

• It is not possible to define BDL variables with the MULTISET type.
• The static SQL syntax does not support collection-related syntax elements:
  • DDL statements like CREATE TABLE cannot use the MULTISET keyword for column types,
  • The collection-derived notation TABLE() is not allowed,
  • The MULTISET { } literal syntax is not allowed.
  • The <value> IN <identifier> syntax is not allowed.
• The fgldbsch schema extractor will report an invalid data type if you try to get the schema for a table with a MULTISET column.

The SET data type

The SET IBM Informix data type is a collection type that stores non-ordered unique elements of a specific base type. Unlike the LIST type, the elements of a LIST have no ordinal positions. Elements cannot be duplicated.

Genero BDL does not support the IBM Informix SET data type:

• It is not possible to define BDL variables with the SET type.
• The static SQL syntax does not support collection-related syntax elements:
  • DDL statements like CREATE TABLE cannot use the SET keyword for column types,
  • The collection-derived notation TABLE() is not allowed,
  • The SET { } literal syntax is not allowed.
  • The <value> IN <identifier> syntax is not allowed.
• The fgldbsch schema extractor will report an invalid data type if you try to get the schema for a table with a SET column.

The ROW data types

IBM Informix supports the named and unnamed ROW data types. A ROW type is a complex type that combines several table columns. You create a ROW type with the CREATE ROW TYPE instruction, and then you can reuse the type definition for a table column.

Genero BDL does not support the IBM Informix ROW data types:

• It is not possible to define BDL variables with a named ROW type. The equivalent would be a RECORD variable, but data is not mapped directly from a structured ROW column, you must list individual fields of the ROW column.
• The static SQL syntax does not support ROW-related syntax elements:
  • The DDL statements CREATE ROW TYPE, DROP ROW TYPE, CREATE CAST and DROP CAST are not allowed,
  • In CREATE TABLE / ALTER TABLE DDL statements, the data type must be a built-in type.
  • The :: cast operator is not supported when specifying a ROW() literal. However, the CAST() expressions are allowed.
• The fgldbsch schema extractor will report an invalid data type if you try to get the schema for a table with a column defined with a ROW type.

However:

• Static SQL allows multi-level single-dot notation, so you can, for example, identify a ROW field as employee.address.city.
• Dynamic SQL can be used to insert or update rows with ROW type columns.
• Individual ROW column fields can be fetched to BDL program variables, as long as the basic types match.

Enhancement reference: 19159

OPAQUE data types

Opaque User Defined Types can be implemented in IBM Informix with the CREATE OPAQUE TYPE statement. The storage structure of an OPAQUE type is unknown to the database server, data can only be accessed through user-defined routines.

Genero BDL does not support the IBM Informix OPAQUE data types:

• It is not possible to define BDL variables with an opaque type.
• The static SQL syntax does not support OPAQUE-related syntax elements:
  • The DDL statements CREATE OPAQUE TYPE, DROP TYPE, CREATE CAST and DROP CAST are not allowed,
  • In CREATE TABLE / ALTER TABLE DDL statements, the data type must be a built-in type.
  • The :: cast operator is not supported. However, the CAST() expressions are allowed.
• The fgldbsch schema extractor will report an invalid data type if you try to get the schema for a table with a column defined with a OPAQUE type.

The :: cast operator

IBM Informix SQL implements the :: cast operator and the CAST() expressions to do an explicit cast of a value:

CREATE TABLE tab ( v INTEGER )
INSERT INTO tab VALUES ( 123456::INTEGER )
SELECT 'abcdef'::CHAR(20)||'.' FROM tab
SELECT CAST('abcdef' AS CHAR(20))||'.' FROM tab

Genero BDL does not support the :: cast operator in the static SQL grammar. However, the CAST() expressions are allowed. If you need to use the :: cast operator, you must use Dynamic SQL to perform such queries.

Enhancement reference: 19190

Table inheritance

IBM Informix SQL allows you to define a table hierarchy through named row types. Table inheritance allows a table to inherit the properties of the supertable in the meaning of constraints, storage options, triggers. You must first create the types with CREATE ROW TYPE, then you can create the tables with the UNDER keyword to define the hierarchy relationship.

CREATE ROW TYPE person_t ( name VARCHAR(50) NOT NULL, address VARCHAR(200), birthdate DATE )
CREATE ROW TYPE employee_t ( salary INTEGER, manager VARCHAR(50) )
CREATE TABLE person OF TYPE person_t
CREATE TABLE employee OF TYPE employee_t UNDER person

A table hierarchy allows you to do SQL queries whose row scope is the supertable and its subtables. For example, after inserting one row in the person table and another one in the employee table, if you UPDATE the name column without a WHERE clause, it will update all rows from both tables. To limit the set of rows affected by the statement to rows of the supertable, you must use the ONLY keyword:

UPDATE ONLY(person) SET birthdate = NULL
SELECT * FROM ONLY(person)

Genero BDL static SQL grammar does not include the syntax elements related to table hierarchy management. You can however use Dynamic SQL to perform such queries.

Enhancement reference: 19200