Call Center and Predictive Dialer Technical Papers - Database Systems Corp. Informix Integration

Revision 11 of Database Systems Corp. (DSC) products now supports the INFORMIX database through the ESQL interface on all Unix platforms that support the INFORMIX Online Dynamic Server. Most prominent of these platforms are the IBM RS6000 series, HP 9000 series, SUN SOLARIS, Data General. The Database Systems Corp. data dictionary and DML now recognize INFORMIX Tables as valid file types, and the search and update routines are modified to convert normal ISAM functions to SQL statements and pass them to the INFORMIX driver for execution. DSC also supports INFORMIX on its WINDOWS NT platform through the ODBC driver; however, this support is rather limited outside of WINDOWS. The INFORMIX database and Dynamic server may reside on all INFORMIX supported Unix platforms and be accessed through ODBC on the WINDOWS NT client. However, the Unix to Unix support for ODBC is limited to just a few of the major Unix systems such as IBM, HP, and SUN.

The support for INFORMIX through ESQL is very similar to DSC’s ODBC implementation. However, since the ESQL standard is based upon a precompiler concept, this support is not a call level interface. Instead, DSC has developed its interface through a set of C routines that contain precompiled source statements that conform to ANSI 92 Embedded SQL standards. Future support of other relational databases, including ORACLE, SYBASE, and INGRESS, will be accomplished through this same method on Unix systems.

Because the ESQL support for SQL is very closely tied to the relational standard, which is the basis for ODBC as well, many parts of this document are the same as the ODBC Support document. Certain distinctions are present and will be highlighted throughout this document.

The benefits to Database Systems Corp. customers of this support are numerous. Data Transport between FASTPLUS and INFORMIX databases is now seamless. INFORMIX data can now be accessed in the same manner as FASTPLUS using TRANSACT screens and formats. TRANSVIEW queries and TRANSWRITER reports now directly support INFORMIX. Data can be related among various different databases; thus, a FASTPLUS Owner file can be linked to an INFORMIX Member file (table). TELEMATION users can use INFORMIX databases directly as Customer files, Customer Extensions, Order data, etc.

Prior to describing the INFORMIX interface to TRANSACT, it is necessary to define certain terms that are used throughout this document.

DATABASE - This refers to a specific INFORMIX database. A database is a group of related tables that are defined to the INFORMIX SQL Server. Multiple databases may be defined to this server. Prior to accessing data in a database, TRANSACT must first "connect" to the database. TRANSACT may connect to more than one database at a time, thus, TRANSACT may access information from several INFORMIX databases at one time.

TABLE - A Table is equivalent to an ISAM file. It is a unique collection of information that has common attributes. A Table contains one or more rows (records). Each row contains one or more columns (fields).

ROW - A Row is equivalent to an ISAM record. Rows contain columns (fields) of information.

INDEX - Any column within a Database Table can be defined as an index for optimum access of information. An index can also be comprised of multiple columns from a given Table.

One significant restriction is that the INDEX name must be UNIQUE for a given DATABASE. Therefore, Tables must be defined such that the index columns have unique names within the DATABASE, not just within the Table.

As mentioned earlier, Database Systems Corp. now supports an INFORMIX Table as another file type within its product line. This is accomplished by translating internal TRANSACT DML functions and calls to SQL statements which are passed to the INFORMIX driver for execution.

The first step is to define an existing INFORMIX database definition to TRANSACT. This is accomplished using the TRANSACT file definition. Likewise, an existing TRANSACT file definition (or a new definition) can be used to create a new Table within an existing INFORMIX database. Effectively, the TRANSACT dictionary and the INFORMIX database definition are synchronized. If a new Table is being created within the INFORMIX database, TRANSACT builds a CREATE TABLE SQL statement from its own dictionary definition and submits this command to the INFORMIX driver for execution. When complete, the Table is accessible to not only TRANSACT but to any other application or utility that supports INFORMIX.

An INFORMIX Table is equivalent to a TRANSACT File. Therefore, the common definition between INFORMIX and TRANSACT is the file definition within TRANSACT. Although INFORMIX maintains tables within a database, TRANSACT does not support the database definition. Instead, TRANSACT supports a large collection of files within its dictionary that may be defined in a number of databases.

Certain restrictions may apply when creating a TRANSACT Dictionary definition for an existing INFORMIX Table. TRANSACT does not support BLOBS, or large binary objects. Likewise TRANSACT only supports character fields with a length of 70 characters. TRANSACT will break up a large fixed length character type column field into multiple, 70 character fields. TRANSACT does not support large variable length character or binary columns.

An INFORMIX Table is opened using the standard SELECT function within the TRANSACT DML. Each table is opened individually and is treated as a separate file within TRANSACT. TRANSACT first connects to the appropriate database (if not already connected) and synchronizes its dictionary field definitions with the INFORMIX Table definition. Since INFORMIX data may be represented internally in a form different than TRANSACT, TRANSACT may need to translate individual fields as rows are fetched from the Table. This is important when considering the individual programming language requirements of the TRANSACT application. The overhead of connecting to a database is considerable; thus, the first time TRANSACT issues a Connect statement to INFORMIX to a particular database will require several seconds.

The heart of TRANSACT's support of INFORMIX is its ability to access information within a database. The full realm of TRANSACT's search criteria is supported within INFORMIX. TRANSACT builds an SQL SELECT statement using any and all of the search criteria established using the TRANSACT DML FIND commands. The following table illustrates the TRANSACT FIND options and the equivalent INFORMIX SQL WHERE clause relationships:

TRANSACT FIND CRITERIA	INFORMIX WHERE CLAUSE EQUIVALENT
FINDEQ	<Column> = <Value>
FINDNE	<Column> NOT= <Value>
FINDGT	<Column> > <Value>
FINDGE	<Column> >= <Value>
FIND LT	<Column> < <Value>
FIND LE	<Column> <= <Value>
FINDP	<Column> LIKE <Value%>
FINDNP	<Column> NOT LIKE <Value%>
FINDCN	<Column> LIKE <%Value%>
FINDNC	<Column> NOT LIKE <%Value%>

TRANSACT constructs the SQL SELECT statement and submits it to the INFORMIX database driver for execution. The driver executes the statement and returns to TRANSACT. TRANSACT, using the GET and GETN commands, build an SQL FETCH statement that retrieves one row at a time from the INFORMIX driver. As each row is retrieved, the columns are translated into a form usable by TRANSACT and the application. Columns are placed in fixed record positions as defined by the TRANSACT dictionary. Therefore, the INFORMIX Row information appears to the application as if it were in a fixed length record (like most ISAM systems).

Complex search criteria is handled much more effectively by INFORMIX (and SQL) than by an ISAM system. If the database is located on a remote system, the row qualification is handled much more efficiently by the remote server than by the application client.

It is appropriate here to describe another feature of INFORMIX that is not standard among all databases and drivers. This is the concept of a unique Rowid. A Rowid is a unique identifier for each row within a table. When INFORMIX returns a Table row during the SQL FETCH operation, TRANSACT retrieves the Rowid and saves it for later reference to this particular row. This value is what is passed to the application in the RECNO field. Therefore, the method of identifying a row (record) to be updated, deleted, or just re-accessed is via the RECNO. The Rowid is defined as an unsigned long integer, which is identical to the record identifier used by FASTPLUS.

A new row can be inserted into an INFORMIX Table using the TRANSACT ADD command. This is a fairly simple process. TRANSACT accepts a fixed length record passed to it via the ADD command and builds an INFORMIX SQL INSERT statement. This statement contains a column name and value for each field defined in the TRANSACT dictionary. The statement is passed to the INFORMIX driver for execution. If successful, TRANSACT returns the success code to the application program. This function may fail at the driver level if the INSERT attempts to add column values which are inconsistent with the Database Table declaration (Range check, Non Unique Index, etc.). If this occurs, TRANSACT returns an unsuccessful return code to the application.

A row within an INFORMIX Table can likewise be deleted using the TRANSACT DELETE command. TRANSACT accepts the unique row identifier from the application program and constructs an INFORMIX SQL DELETE statement. The statement is passed to the INFORMIX driver for execution and TRANSACT returns the success/failure code to the application. This statement can only fail for security reasons or if the row within the table no longer exists.

TRANSACT can update an INFORMIX Table using its standard CHANGE function. One distinction must be described, however. Within ISAM file types, TRANSACT requires that the record must be first locked using LGET or LRETRV prior to the CHANGE routine execution. The lock function is not supported within INFORMIX; thus, a successful call to lock a record need not be executed prior to the CHANGE. INFORMIX does ensure that the UPDATE SQL command preserves data integrity at the point of its execution.

To perform the Update function, TRANSACT internally builds an SQL command (UPDATE) that modifies any columns that are changed within the application. It submits this UPDATE SQL command to the INFORMIX driver for execution. If successful, the CHANGE function returns a code informing the application of this success. Possible errors could be the prior deletion of this row or the fact that the data columns being modified did not conform to the Database Table definition qualifiers (Range check, data type, etc.).

To access and update information from an INFORMIX database, TRANSACT and INFORMIX must first synchronize dictionaries. This is accomplished both ways - TRANSACT to INFORMIX and INFORMIX to TRANSACT. An existing INFORMIX database Table can be defined under TRANSACT, and TRANSACT can issue a CREATE TABLE SQL command to define a new INFORMIX Database Table. When creating a TRANSACT dictionary file from INFORMIX, some restrictions apply. Not all column data types translate readily to TRANSACT. TRANSACT does not support BLOBS (large binary blocks of data) or large variable length text fields.

To build a TRANSACT file definition from an INFORMIX Database Table, use the File Definition function within any Database Systems Corp. product. A new auto build option (I) has been added allowing you to query the INFORMIX driver for all defined databases. For each database, TRANSACT allows you to display all of the available Tables.

From the File Definition Menu, enter option 2 (Add New File), a unique 12 character filename, and enter "I" under the Automatic Build Source option. TRANSACT will display a window that allows you to enter the Database Name and Table Name which identify the appropriate INFORMIX Table.

If a valid database name is entered on this screen, you can press function key F5 to view all defined Tables within the database. Likewise, a Table can be selected by moving the cursor through the displayed Tables and pressing the ENTER key.

When building a TRANSACT dictionary from INFORMIX, you can automatically upcase the column names by entering "Y" on the above screen at the prompt "Upcase Fields?". By default, TRANSACT field names will appear in whatever case the INFORMIX Table definition has them defined. Upcasing field names are very useful, particularly within TRANSVIEW and TRANSWRITER. Some INFORMIX Online Servers require a user name and optional password. Enter these on the above screen.

When all of the above information has been properly entered, press ENTER to create the new TRANSACT file definition. If the information has been entered correctly, a new file will be defined to TRANSACT.

To build an INFORMIX Table from a TRANSACT File Definition, use the same File Definition Menu within any Database Systems Corp. product. If the file definition already exists, use Function 3 (Change Existing File) and enter the file definition name. A second screen will appear displaying the file pathname, file type, record size, and the Create File option. Change the file type to "I" and enter "Y" at the prompt "Create the File".

TRANSACT will then enter the File Field Definition Screen allowing you to change any of the dictionary fields. When finished, TRANSACT will prompt you with the same INFORMIX control screen that appears above. Enter the appropriate Table Name that you wish to create and the database that will contain this Table. Be sure that any fields defined as indexes have unique names and are not already defined in another table within this database.

In order to access any database defined under INFORMIX, you must first obtain the INFORMIX Online Dynamic Server from INFORMIX. Once this is installed, TRANSACT and other DSC products must be loaded with the actual INFORMIX library calls maintained in the object file informix_subs. This object is supplied with DSC products but is not loaded. Instead the object file informix_dummy is the default library object that satisfies the INFORMIX references. The following libraries must be obtained from INFORMIX and loaded with Database Systems Corp. products to access INFORMIX databases:

Note that certain INFORMIX libraries are loaded multiple times to satisfy the load requirements. These libraries must be loaded (along with informix_subs) within the following Database Systems Corp. lists:

DSC intends to review other INFORMIX features for future support. Likewise, certain performance features of INFORMIX will be investigated to improve the individual row access and update procedures. DSC YEAR 2000

With the approach of Year 2000, a major concern of all data managers and computer systems administrators is the effect of this event upon all computer software applications and system software. Many legacy applications have been developed using dates that were narrowly defined to the 20^th century. As the 21^st century approaches, some applications are already producing unpredictable, unreliable, or erroneous calculations and displays as a result of this procedure. Since DATABASE SYSTEMS CORP. (DSC) has developed and distributed thousands of copies of its software for the past 20 years, it is important to define to our users what the potential impact is of Year 2000 on its customers.

There are several important areas that need to be reviewed in this document. First, the effect of Year 2000 on data stored in DSC files. Next is the effect of dates as they are used in outputs - either reports and displays, or on screens. Also described in this document are the modifications required by DSC to support this transition from the 20^th to the 21^st century.

Since our inception, DSC has supported a unique field type called DATE in its dictionary. this data type consists of a structure of three consecutive short integers. Three different representations of this appear below in COBOL, C, and FORTRAN77.

05 DATEFIELD.
     03 DATEFIELD-YEAR PIC 9999 USAGE IS COMP.
     03 DATEFIELD-MONTH PIC 9999 USAGE IS COMP.
     03 DATEFIELD-DAY PIC 9999 USAGE IS COMP.

structure {
     short int year;
     short int month;
     short int day;
} datefield;

INTEGER*2 DATEFIELD(3)
INTEGER*2 DATE-YEAR, DATE-MONTH, DATE-DAY
EQUIVALENCE (DATEFIELD(1),DATE-YEAR)
EQUIVALENCE (DATEFIELD(2),DATE-MONTH)
EQUIVALENCE (DATEFIELD(3), DATE-DAY)

Dates were defined in this manner for several reasons. First, the order of the sub-fields (year, month, and day) is critical for establishing a proper collating sequence. This is important for comparison of dates, particularly greater than or less than comparisons. Also, when dates are used as file indexes (or as a part of a file index), it is important that this collating sequence is maintained. The individual fields within a date field are sometimes referenced on their own - such as determining an event based strictly upon the year, or the combination of month and year.

DSC date fields are fully compliant with the year 2000, because the year component of this date field is the full 4 digit year. Thus, the date (12 JANUARY, 1998) is actually stored as three consecutive short int (C type) fields with the following values (1998, 1, 12).

If the date fields are initialized or set by the application program, it is up to this program to fill the year field with the full 4 digit year. On the other hand, if the field is populated or updated using a TRANSACT screen and Output Format, the date format on the screen becomes very important. As will be described in the next section, DSC supports a number of input and output date formats. Some of these formats only represent the year in 2 digits, with an assumption being made as to the value of the century. For all revisions less than Rev 11.6 of DSC products, this century is assumed to be 1900.

The important issue here is that the actual storage allocation and capability of DSC date fields conform to the Year 2000 solution, i.e. the capacity to store and process dates from more than one century.

However, some applications do not use the DSC date field data types when storing fields that contain date information. These typically use a character data type for storage purposes, usually because the fields may be entered directly from the screen into this data type, or the data has been retrieved or processed from another source in this character form. In COBOL, for example, the dates may be defined as 6 character fields, or as three consecutive decimal fields with pictures of 99. For Example:

These data types may pose significant problems when Year 2000 enters the picture. A typical date representation in character type may be in the following form: YYMMDD where YY represents a 2 character year field; MM, a 2 character month; and DD, a 2 character day. This format has been represented in every combination including MMDDYY, YYDDMM, etc. As information is stored in this form in database files, the processing and calculation of results from these character date fields will produce inaccurate results in Year 2000, unless the actual database is changed and the logic in the application programs is also altered.

Another form of a date representation which is application unique is a numeric value representing the number of days since a base date in history. Typical of this is an integer value representing the number of days since JANUARY 1, 1900. This type of date will certainly function as long as the application code can properly address the transition from the 20^th to the 21^st century. Dates of this form generally do not have to be converted in the data file to function within an application.

An even more onerous problem exists with certain computer manufacturers and revisions of their software. PRIME Computer, for instance, does not support Year 2000 even at the system initialization point in revisions on PRIMOS earlier than Rev 23. Thus, dates are unpredictable after Year 2000 at the operating system level. This problem has reportedly been addressed with revisions 23 and greater of PRIMOS. It is certain that all revisions of PRIMOS less than 23 do not support Year 2000. Therefore, DSC software running on these systems will likewise not support Year 2000. UNIX systems do support Year 2000 and beyond, as well as WINDOWS95/NT.

In summary, if an application program uses DSC date fields, NO FILE CONVERSION is necessary. However, if dates are represented as character fields, a FILE CONVERSION may be necessary if only 2 digits represent the year. If a numeric number of days method is used to store a date field in a file, NO FILE CONVERSION is generally required. System date problems will occur on PRIME Computers (Rev. 22 and earlier) after Year 2000.

DSC supports 15 different date formats for the output of date fields and 8 formats for the input of dates. Input date formats are of most concern, and only then if the year is represented as 2 characters. Output date formats, although potentially confusing to the application user, do not pose a significant problem because the century in most cases is obvious. If any potential confusion arises out of the use of a 2 character year output date format, the application code or DSC display, print, or report should be modified to use a different 4 character year output date format. REMEMBER: these formats only apply to the input of fields defined on a DSC screen as date types and to the output of fields in DSC displays, reports and files that are defined as date fields in the DSC dictionary.

The next 6 formats were added at Revision 11.6 based upon the number of input characters allowed on the screen date formats:

Thus, if the input format is 1 and the screen has 8 input character positions, the user can input a full 4 digit year on the screen.

The date formats annotated with an asterisk (*) are those formats that pose potential Year 2000 problems because each of these formats represents the year in only 2 characters. When passed through a DSC Output Format, the 2 character year is currently (Revision 11.5 and less) adjusted by converting it into a numeric integer and adding 1900 to it prior to storing it in a DSC file buffer. The following is an example of this process:

A screen is presented to the user with a field allowing 6 characters to be entered. The field is defined as a date type 1 (MMDDYY). The user enters the value ‘100298’ on the screen.

The DSC Output Format processes this input and converts this information to a date type field by storing the year as 98 + 1900 in the first short integer, the month as 10 in the second short integer, and the day as 2 in the third short integer. (1998, 10, 2).

Assume, however, that the user enters the following date on this same screen ‘100250’. Currently, the DSC Output Format assumes that the year is 1950 (50 + 1900). However, if the user really wanted to enter a year of 2050, there is no mechanism for this format to distinguish or determine the users intention. As mentioned above, the screen could be altered to 8 characters (MMDDYYYY), which would allow the user to enter a full 4 digit year.

To assist the users of DSC products to overcome some of the above year 2000 issues, several modifications have been made at Revision 11.6. Some of these are internal to DSC products, while another feature is a new Installation Control Parameter. The following summarizes these changes:

Contact Database System Corp. for further details about Revision 11.6 of our products. This revision has been installed at some of our customers since early 1998. Our customer support representatives are prepared to address any questions about this revision and how we can best upgrade your system.