|
|
Communicator 3000 MPE/iX Release 6.0 (Platform Software Release C.60.00): HP 3000 MPE/iX Computer Systems > Chapter 10 Technical Articles New Features in ALLBASE/SQL Version G3 |
|
by Doug Myers This article includes information on the following features of ALLBASE/SQL included with this version G3 on MPE/iX 6.0 release (C.60.00):
The entire manual set for ALLBASE/SQL is now available on the new CD-ROM documentation system, "Instant Information." You can now look up information quickly in the on-line documentation. With the G3 release of ALLBASE/SQL and IMAGE/SQL, the supported SQL syntax has been enhanced to include the following string manipulation functions: UPPER, LOWER, POSITION, INSTR, TRIM, LTRIM and RTRIM. These string functions allow you to manipulate or examine the CHAR and VARCHAR values within the SQL syntax, allowing for more sophisticated queries and data manipulation commands to be formed. These string functions were designed to be compatible with functions specified in the ANSI SQL '92 standard and functions used in ORACLE. In cases where the ANSI SQL '92 standard and the ORACLE functions were not compatible (such as the LTRIM and RTRIM in ORACLE versus TRIM in the ANSI standard), both versions where implemented. The specifications for each of these functions follows. Searches for the presence of the string stringexpr1 in the string stringexpr2 and returns a numeric value that indicates the position at which stringexpr1 is found in stringexpr2 Searches char1 beginning with its nth character for the mth occurrence of char2 and returns the position of the character in char1 that is the first character of this occurrence. If n is negative, Instr counts and searches backward from the end of char1. The value of m must be positive. The default values of both n and m are 1, meaning Instr begins searching at the first character of char1 for the first occurrence of char2. The return value is relative to the beginning of char1 regardless of the value of n, and is expressed in characters. If the search is unsuccessful (if char2 does not appear m times after the nth character of char1) the return value is 0. If n and m are not specified the function is equivalent to the ANSI SQL-92 POSITION function, except that the syntax is slightly different. LTRIM function trims the characters specified in charset from the beginning of the string stringexpr. RTRIM function trims the characters specified in charset from the end of the string stringexpr.
Returns "purchdb .vendors "
Returns the numeric value 7
Returns the numeric value 18 (starting position of the second occurrence of the string 'world')
Returns the system table entry for PURCHDB.VENDORS ALLBASE/SQL, version G2, contains several major enhancements providing significant benefits in the following areas:
In addition, the following manuals were updated to fully document G1 and G2 features:
Two new authorities have been added to the G2 release of ALLBASE/SQL: MONITOR and INSTALL. These new authorities can be granted to users using the GRANT command, or can be revoked from users using the REVOKE command. MONITOR authority gives a user the ability to run SQLMON, an on-line diagnostic tool that monitors the activity of an ALLBASE/SQL DBEnvironment. Before this new authority, SQLMON could only be run by the creator of the DBEnvironment (also known as the DBECreator), someone who knows the maintenance word (maintword), or someone who has superuser capability. See the ALLBASE/SQL Reference Manual for more details. INSTALL authority gives a user the ability to use the SQL preprocessors to create an SQL module owned by someone other than themselves, or to use ISQL to install a module owned by someone other than themselves. This is an important capability for those needing to move modules from a development DBEnvironment to a production DBEnvironment. Before this new authority, only a DBA had this ability. See the ALLBASE/SQL Reference Manual for more details. As the end of the century quickly approaches, there has been much concern about dates being handled correctly in the twenty-first century. This is particularly an issue for systems and applications that are using a two-digit year to express dates. ALLBASE/SQL has always stored dates in a four-digit year format which largely eliminates the year 2000 compatibility problem. ALLBASE/SQL applications can be coded using four-digit year dates, thus avoiding any ambiguity. However, for existing applications that may allow dates to be entered using a two-digit year format, there is still an issue as to how these dates get converted to the ALLBASE/SQL internal format using the TO_DATE function or TO_DATETIME. For example, what date value would the following functions return?
Would these functions evaluate the year as 1935 or 2035? When YY is specified in the format specification of either TO_DATE or TO_DATETIME, and if its value of the year specified is less than 50, then the century part of the DATE or DATETIME defaults to 20, otherwise it is set to 19. This behavior can be overridden by setting the JCW HPSQLsplitcentury to a value between 0 and 100. If the year specified is less than the value of the JCW HPSQLsplitcentury then the century part is set to 20, otherwise it is set to 19. For the above example, Case 1: HPSQLsplitcentury is not set
Case 2: HPSQLsplitcentury is set to 0
Case 3: HPSQLsplitcentury is set to 70
With the G1.15 release, the CAST function enhancement for ALLBASE/SQL and IMAGE/SQL became available to customers. The CAST function is used to explicitly convert data from one data type to another. The CAST function not only allows conversion between compatible data types, such as between CHAR and BINARY or between INTEGER and DECIMAL, but it will also allow conversion between certain normally incompatible types, such as between CHAR and INTEGER. The CAST function is defined in the ANSI SQL2 standard. CAST in ALLBASE/SQL and IMAGE/SQL complies with that standard. In addition, several extensions to the standard specification have been added to make CAST even more powerful. The CAST function can be used anywhere a general expression is allowed. Also, as a part of this enhancement, the SQL parser has been enhanced to allow general expressions in more of the SQL syntax. For example, general expressions including nested functions are now allowed in all the date/time functions and string functions. Therefore, CAST will be supported inside functions that support expressions including aggregate functions. CAST will also take general expressions including nested functions as input.
by Poongodi R and Doug Myers This article provides information regarding the handling of pseudo-mapped files when installing ALLBASE/SQL. Pseudo-mapped files in MPE XL are disk files that are accessed without the usual file mapping by the operating system. Pseudo-mapped files are no longer supported by ALLBASE/SQL. If the pseudo-mapped files already exist in the system, they will not be supported on the G.0 (or later) versions. Serious system problems may occur because of the use of pseudo-mapped files.
ALLBASE/SQL files in pseudo-mapped mode must be converted into mapped mode using the MOVEFILE command in SQLUtil. Use the SHOWACCESS command in SQLUtil to show which files are pseudo-mapped. Use the following procedures :
|
|