INFORMIX-4GL, INFORMIX-SQL - Guide to 7.20 Feature Enhancements -------------------------------------------------------------- Table of Contents Chapter 1 Introduction Chapter 2 Enhancements in Connectivity- Stream Pipe Chapter 3 Support for Additional Environment Variables DBCENTURY FET_BUF_SIZE PDQPRIORITY Chapter 4 Support for 7.2 SQL Syntax in I4GL through Prepare Statement Chapter 5 Internationalized Product Directory Structure Chapter 6 GLS Environment Variables Chapter 7 Default values of GLS Environment Variables Chapter 8 Changes in Environment Configuration Files files for the ASIAN user Chapter 9 Compatibility of 4GL and 7x ESQL/C ----------------------------------------------------------------- Chapter 1 Introduction INFORMIX-4GL Version 7.20 and INFORMIX-SQL Version 7.20 are the latest addition to the Informix's family of character based Tools products in Unix environment. It is an upgrade to the 6x family of English/Asian 4GL and SQL products. INFORMIX-4GL Version 7.20 and INFORMIX-SQL Version 7.20 provide full Global Language Support (GLS) environment. Thus we have single products for entire global market, handling different languages, cultural conventions and code sets for Asian, European, Latin American, and Middle Eastern countries. The GLS Library version supported is 3.07.UC2. These products essentially have all the functional enhancements and bug-fixes of Informix 6x family of 4GL & SQL products upto and including version 6.05.UD1 of English and Ax6.05.UD1 of Asian products. In addition, the products support the following functional enhancements - Stream Pipe mode of communication between the Client and the OnLine Server - Support for new environment variables like DBCENTURY FET_BUF_SIZE PDQPRIORITY - Support for Version 7.2 SQL Syntax in I4GL/ISQL through 'Prepare' construct. - GLS environment variables like GL_DATE GL_DATETIME 'INFORMIX-4GL, INFORMIX-SQL - Guide to 7.20 Feature Enhancements' is a supplement that complements the various books on INFORMIX-4GL and INFORMIX-SQL products manual set. It provides information and the mode of usage of the enhancements, listed above. Chapter 2 Enhancements in Connectivity- Stream Pipe When INFORMIX-OnLine Server and the I4GL/ISQL products are installed in the same machine, earlier version of Tools products had the following modes of communication : Shared memory or Network (Local Loopback) between the Tools products and the Server. The current 7.2 version of INFORMIX-4GL and INFORMIX-SQL products, in addition, support 'Stream Pipe' IPC mechanism. Stream-pipe connections have the following advantage: Unlike shared-memory connections, stream pipes do not pose the security risk of being overwritten or read by other programs that explicitly access the same portion of shared memory. Stream-pipe connections have the following disadvantages: 1. Stream Pipe connections may be slower than shared memory connections 2. Stream Pipes are not available on all computers For stream-pipe connection, the entry in the 'nettype' field of 'sqlhosts' file is 'onipcstr' for OnLine connection Example ---------------------------------------------------------- Server nettype hostname Service ---------------------------------------------------------- alpha onipcstr idcsun33 service1 -------------------------------------------------------- For detailed information of the fields of other related files, user is referred to 'Guide to 7.1 Feature Enhancements', Version 7.1, May 1995, Part# 000-7748, PP 2-1 to 2-18 Chapter 3 Support for additional environment variables 3.1 DBCENTURY INFORMIX-4GL and INFORMIX-SQL products till the last release, used the present century to extend the year in a date or datetime value with less than 4 digits for the year field. With the DBCENTURY support it would be possible to choose either the present, previous or the future century to extend the year. New algorithms have been introduced for this purpose. A new environment variable 'DBCENTURY' has been introduced in this release which can be set to a single letter 'P', 'F', 'C', or 'R' to indicate the algorithm to be used for extension. 'P' - Past 'F' - Future 'C' - Closest 'R' - Current There are two ways to specify the algorithm for century extension a) Explicitly setting the DBCENTURY environment variable b) Using the default DBCENTURY variable which is 'R' (Current) 3.1.1 DBCENTURY=P (Past) The past and present centuries are used to extend the year value. These two dates are compared with the current date and the date which is prior to the current date is chosen. If both dates are prior to the current date, whichever is closer is chosen. Examples Example 1 : Data type: date Current date : 4/6/1996 User enters : 1/1/10 Previous century extension : 1/1/1810 Present century extension : 1/1/1910 Analysis : Both results are prior to the current date, but 1/1/1910 is closer. Result : 1/1/1910 Example 2: Data type: date Current date : 4/6/2010 User enters : 1/1/05 Previous century extension : 1/1/1905 Present century extension : 1/1/2005 Analysis : Both dates are prior to the current date, but 1/1/2005 is closer Result : 1/1/2005 Example 3: Data type : date Current date : 4/6/2010 User enters : 1/1/50 Previous century extension : 1/1/1950 Present century extension : 1/1/2050 Analysis : Only 1/1/1950 is prior to the current date Result : 1/1/1950 Example 4: Data type : datetime year to month Current date : 4/6/1996 User enters : datetime(1-1) year to month Previous century extension : 1801-1 Present century extension : 1901-1 Analysis: Both dates are prior to the current date, but 1901-1 is closer Result: 1901-1 3.1.2 DBCENTURY=F (Future) The present and next centuries are used to extend the year value. These two dates are compared with the current date and the date which is after the current date is chosen. If both dates are after the current date, whichever is closer is chosen. Examples Example 1 : Data type : date Current date : 4/6/1996 User enters : 1/1/90 Present century extension : 1/1/1990 Next century extension : 1/1/2090 Analysis : 1/1/2090 is after the current date Result : 1/1/2090 Example 2 : Data type : date Current date : 4/6/2051 User enters : 1/1/50 Present century extension : 1/1/2050 Next century extension : 1/1/2150 Analysis: 1/1/2150 is after the current date Result : 1/1/2150 Example 3: Data type : date Current date : 4/6/2010 User enters : 1/1/25 Present century extension : 1/1/2025 Next century extension : 1/1/2125 Analysis : Both dates are after the current date, but 1/1/2025 is closer. Result : 1/1/2025 Example 4: Data type : datetime year to month Current date : 4/6/1996 User enters : datetime(1-1) year to month Present century extension : 1901-1 Next century extension : 2001-1 Analysis : 2001-1 is after the current date. Result : 2001-1 3.1.3 DBCENTURY=C (Closest) The past, present and next centuries are used to extend the year value and the date which is closest to the current date is used. Examples Example 1: Data type : date Current date : 4/6/1996 User enters : 1/1/1 Previous century extension : 1/1/1801 to 4/6/1996 is 71318 days Present century extension : 1/1/1901 to 4/6/1996 is 34794 days Next century extension : 1/1/2001 to 4/6/1996 is 1731 days Analysis : 1/1/2001 is closer to the current date Result : 1/1/2001 Example 2 : Data type : date Current date : 2/1/2001 User enters : 4/1/95 Previous century extension : 4/1/1995 to 2/1/2001 is 2133 days Present century extension : 4/1/2095 to 2/1/2001 is 34392 days Next century extension : 4/1/2195 to 2/1/2001 is 70916 days Analysis : 4/1/1995 is closer to the current date Result : 4/1/1995 Example 3: Data type : datetime year to month Current date : 4/6/1996 User enters : datetime(1-1) year to month The number of months from the current date is calculated : Previous century extension : 1801-1 to 1996-4 is 2343 months Present century extension : 1901-1 to 1996-4 is 1143 months Next century extension : 2001-1 to 1996-4 is 57 months Analysis : 2001-1 is closer to the current date Result : 2001-1 3.1.4 DBCENTURY=R (Current) The current century will be used to extend the year Examples Example1 : Data type : date Current date : 4/6/1996 User enters : 12/10/1 Result : 12/10/1901 Example 2 : Data type : date Current date : 4/6/2010 User enters : 12/10/1 Result : 12/10/2001 Example 3 : Data type : datetime year to month Current date : 4/6/1996 User enters : datetime(1-12) year to month Result : 1901-12 3.2 FET_BUF_SIZE The 'fetch buffer' is the buffer that the server uses to send data, except blob data, to the client applications. The buffer resides in the client process. The bigger the buffer, the more data the server can send to the application, before returning control to the application. For large number of rows or for a few but large rows to be returned, the greater the size of the fetch buffer, the fewer number of times the application needs to wait while server retrieves the rows. INFORMIX-4GL and INFORMIX-SQL users can set the size of the fetch buffer by setting the FET_BUF_SIZE environment variable to the desired value at run time. Setting this environment variable sets the fetch buffer size for the duration of the application For example, the following command sets the variable in C-shell environment to 20,000 bytes (20kb) setenv FET_BUF_SIZE 20000 The size should be in bytes, up to a maximum of a small integer (short) of the system used. For most 32-bit systems, the value 32,767 bytes. If the parameter is not set externally, a default value is used. By increasing the size of the fetch buffer, you reduce the overhead of refilling the buffer. The server can buffer more data before sending it to the application. The feature can enhance the performance when fetching a large number of rows or when the size of a single row is large. However, for fetching only a few rows (unless the row size is large) the performance gain is not as apparent. For more details, the user is referred to 'INFORMIX-ESQL/C Programmer's Manual' Version 7.1, Dec 1994, Part# 000-7629 PP 10-21 to 10-22. 3.3 PDQPRIORITY INFORMIX-4GL and INFORMIX-SQL users can now define the PDQ priority value to determine the amount of resources IINFORMIX- OnLine allocates to process a query in parallel The syntax of the variable in c-shell environment is setenv PDQPRIORITY ----HIGH | |-- LOW | |-- OFF | |-percent-of-resources where HIGH - OnLine determines the operators and respective degree of parallelism LOW - default - Data is fetched from fragmented tables in parallel OFF - parallel data query is turned off percentage-of-resources - An integer between 0 and 100 indicating a query priority level For more information the user is referred to ' INFORMIX- OnLine Dynamic server Enhancement' Guide to Version 7.0 Functionality, Version 7.0, February 1994, Part# 000-7409 Chapter 4 Support for 7.2 SQL Syntax in I4GL through Prepare Statement Though the native SQL Syntax of INFORMIX-4GL Version 7.20 has not been enhanced, the users can now employ the 'Prepare' statement to support 7.2 version syntax of SQL in their 4GL applications. The support of SQL Syntax in I4GL will be as follows : 1. SQL Syntax supported in 6.05 release of I4GL will be available as is. 2. Syntax available in Version 7.2 SQL will be supported through REPARE statement 4.1 SQL Statements supported through PREPARE Data Definition Statements Alter Fragment Alter Table (7.2x Syntax) Create Index (7.2x Syntax) Create Procedure Create Role Create Schema Create Table (7.2x Syntax) Create Trigger Drop Procedure Drop Role Drop Trigger Rename Database Data Access Grant Fragment Revoke Fragment Set Role Set Session Authorization Set Transaction Data Integrity Set Dataskip Start Violations Table Stop Violations Table Query Optimization Set Explain(7.2x Syntax) Set Optimization Set Pdqpriority Update Statistics(7.2x Syntax) Stored Procedure Execute Procedure Set Debug File to OnLine/Optical Alter Optical Cluster Create Optical Cluster Drop Optical cluster Release Reserve Set Mounting Timeout 4.2 SQL Statements NOT supported through PREPARE Data Definition Statements Create Procedure From Cursor Manipulation Open Free Dynamic management Allocate Descriptor Deallocate Descriptor Execute Immediate Get Descriptor Set Descriptor Connection Connect Disconnect Set Connection Data Integrity Check table Repair Table Auxiliary Info Output Get diagnostics Whenever 4.3 References INFORMIX-4GL Reference Manual - Version 6.0 April 1994 Part# 000-7611 Informix Guide to SQL Syntax - Version 7.2, Dec 1995 Part# 000-7858 Chapter 5 Internationalized Product Directory Structure The directory structure of the INFORMIX-4GL and INFORMIX-SQL products have been modified so as to have the same structure as of any Informix GLS product The structure is as shown $INFORMIXDIR | | | |-------|---------|-----|--------|-----------| | | | | | | | | | | | demo gls etc forms release msg |_____________ | (similar to 'msg' sub-dir) | | | | | | sql ..... .... | | | | | | | | | | (similar to 'msg' sub-dir) | | | | | | | | | | | | | |-----------------------|------------| | | | | | | en_us .... .... .... | | | | | | 0333 ... ... ... | | | | | | * * * * | | |---------------------------------------| | | | lc11 cm3 cv9 | | | | *.cm *.cv | *.cmo *.cvo | | |---------------------------------------| | | | en_us .... .... | | | *.lc *.lc *.lc *.lco *.lco *.lco Chapter 6 GLS Environment Variables INFORMIX-4GL and INFORMIX-SQL products now provide full support of GLS environment variables. All GLS environment variables including GL_DATE GL_DATETIME are now supported. For the details and the syntax of the GLS environment variables, user is referred to the manual 'Guide to GLS Functionality ' Version 9.1, March 1997, Part# 000-4818. The 'glfiles' utility (Page A-11 of the above document) packaged with the Tools product, will enable user to generate list of 1. GLS locales available in the system 2. Informix code-set conversion files available 3. Informix code-set files available Chapter 7 Default values of GLS Environment Parameters As the default values of the environment parameters are different from those of ALS environment, the default values assumed by INFORMIX-4GL and INFORMIX-SQL products are discussed below 7.1 Default values of CLIENT_LOCALE and DB_LOCALE Following table shows the values assumed by the INFORMIX-4GL and INFORMIX-SQL products, when user defines only some of the required values of locales. (A value of 'ja-jp.ujis' is assumed in the example discussed below) CL -> CLIENT_LOCALE DL -> DB_LOCALE ----------------------------------------------------- User Defined | Values in Product ----------------------------------------------------- CL CL DL DL | CL DL defined Value defined Value | Value Value ------------------------------------------------------ No ----- No ----- | en_us.8859 en_us.8859 Yes ja_jp.ujis No ----- | ja_jp.ujis ja_jp.ujis Yes ja_jp.ujis Yes ja_jp.ujis | ja_jp.ujis ja_jp.ujis No ----- Yes ja_jp.ujis | en_us.8859 ja_jp.ujis -------------------------------------------------------- 7.2. Default value of DBLANG If DBLANG environment variable is not set, it is set to the value of CLIENT_LOCALE Chapter 8 Changes in Environment Configuration Files files for the ASIAN user Earlier version of Asian products of INFORMIX-4GL and INFORMIX-SQL allow the '=' or spaces or tabs as the separator between the variable and the value such as variable = value in the $INFORMIXDIR/etc/informix.rc or $HOME/.informix files Asian users are advised that 7.20 version of INFORMIX-4GL and INFORMIX-SQL products allow only tabs or spaces as the separator Chapter 9 Compatibility of 4GL and 7x ESQL/C As 7.20 version of INFORMIX-4GL and INFORMIX-SQL products are built with 7.2 Version of ESQL/C which is also in the 7.2x Server Products, the users can now link the object modules of 4GL of 7.20 Tools and ESQL/C of 7.2x servers to create the binaries. For example, assume a user has an application whose functionality is distributed in a 4GL module (file1.4gl) and ESQL/C module (file2.ec). The object modules created can be linked to create the executable as follows. Step 1 : file1.4gl ------------- file1.o (4gl compiler 7.20 Tools) Step 2 :file2.ec ---------------file2.o (ESQL/C Compiler of 7.2x Servers) Step 3 file1.o & file2.o ---------a.out (Through 7.20 Tools)