HPlogo   Communicator 3000 MPE/iX Release 6.0 (Platform Software Release C.60.00):
HP 3000 MPE/iX Computer Systems > Chapter 10 Technical Articles

10.4: New Features in ALLBASE/SQL Version G3

MPE documents

Complete PDF

 

Table of Contents

Index

 

⇓ Page Bottom

⇑ Page Top

  

IMAGE/SQL with TurboIMAGE/XL Enhancements

Year 2000 and Other Enhancements in VPLUS

by Doug Myers
Database Lab, Commercial System Division

Overview

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):
  • ALLBASE/SQL manual set is on Instant Information CD-ROM

  • String functions are a new feature for ALLBASE/SQL G3

  • Security features introduced with ALLBASE/SQL G2

  • Year 2000 compatibility introduced with ALLBASE/SQL G2

  • Manuals updated

  • CAST Function introduced with G1.15

Instant Information CD-ROM

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.

String Functions

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.

Function Specification

Lower

Converts all the characters in stringexpr to lower case
Syntax

 [LOWER ( stringexpr )]

Upper

Converts all the characters in stringexpr to upper case
Syntax

 [UPPER (stringexpr)]

Position

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
Syntax

 [POSITION ( stringexpr1, stringexpr2 )]

Instr

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.
Syntax

 [INSTR ( char1, char2 [,n [,m]] )]

Ltrim

LTRIM function trims the characters specified in charset from the beginning of the string stringexpr.
Syntax

 [LTRIM (charset, stringexpr)]

Rtrim

RTRIM function trims the characters specified in charset from the end of the string stringexpr.
Syntax
  
 [RTRIM (charset, stringexpr)]

Trim

TRIM function allows you to strip the characters specified in charset from the beginning and/or the end of the string stringexpr. If charset is not specified, then blank characters would be stripped from stringexpr.
Syntax

 [ TRIM ({ LEADING | TRAILING | BOTH} (,charset ,stringexpr)]

Examples:

Example 1


 SELECT LOWER (OWNER) || '.' || LOWER (NAME)
 FROM SYSTEM.TABLE
 WHERE NAME = UPPER ('vendors');
Returns "purchdb .vendors "

Example 2


 SELECT POSITION ('world', 'hello world')
 FROM SYSTEM.TABLE
 WHERE NAME = UPPER('vendors');
Returns the numeric value 7

Example 3


 SELECT INSTR ('hello world hello world', 'world', 5, 2)
 FROM SYSTEM.TABLE
 WHERE NAME = UPPER('vendors');
Returns the numeric value 18 (starting position of the second occurrence of the string 'world')

Example 4


 SELECT * FROM SYSTEM.TABLE
 WHERE NAME = LTRIM ('?*', 'VENDORS?*???***')
 AND OWNER = 'PURCHDB';
Returns the system table entry for PURCHDB.VENDORS

Example 5


 SELECT TRIM (BOTH '?*' FROM '??**?*hello ?* world???*')
 FROM SYSTEM.TABLE
 WHERE NAME = 'VENDORS';
Returns 'hello?* world'.

Features in ALLBASE/SQL Version G2

ALLBASE/SQL, version G2, contains several major enhancements providing significant benefits in the following areas:
  • Security

  • Year 2000 compatibility

  • CAST function (introduced in G1.15)
In addition, the following manuals were updated to fully document G1 and G2 features:
  • ALLBASE/SQL Reference Manual

  • ALLBASE/SQL Database Administration Guide

  • ALLBASE/SQL Message Manual

Security

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

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

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.

Year 2000 Compatibility

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? 

 TO_DATE('12/31/35','MM/DD/YY')
 TO_DATETIME('351231235959','YYMMDDHHMISS')
Would these functions evaluate the year as 1935 or 2035?

Solution

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 

 TO_DATE('12/31/35','MM/DD/YY') = 2035-12-31
 TO_DATETIME('351231235959','YYMMDDHHMISS') = 2035-12-31 23:59:59
Case 2: HPSQLsplitcentury is set to 0 

 TO_DATE('12/31/35','MM/DD/YY') = 1935-12-31
 >TO_DATETIME('351231235959','YYMMDDHHMISS') = 1935-12-31 23:59:59
Case 3: HPSQLsplitcentury is set to 70 

 TO_DATE('12/31/35','MM/DD/YY') = 2035-12-31
 TO_DATETIME('351231235959','YYMMDDHHMISS') = 2035-12-31 23:59:59

CAST Function

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.

CAST Syntax


 {CAST ({Expression NULL}{AS,}DataType[,FormatSpec])}

Parameters

Expression

Column, USER function, host variable, local variable, AddMonths function, aggregate function, date/time conversion function, dynamic parameter, or procedure parameter, constant, current function, long column function, string function, or any combination of these in an arithmetic or concatenation expression.

DataType

ALLBASE/SQL data type: CHAR(n), VARCHAR(n), DECIMAL(p[,s]), FLOAT, REAL, INTEGER, SMALLINT, DATE, TIME, DATETIME, INTERVAL, BINARY(n), VARBINARY(n).

The LONG BINARY(n) and LONG VARBINARY(n) cannot be used in the CAST operations.

FormatSpec

Format specification used for DATE, TIME, DATETIME, INTERVAL conversions. FormatSpec is the same as that used in the date/time conversion functions. See the ALLBASE/SQL Reference Manual for more details.

Pseudo-Mapped Files Removed from ALLBASE/SQL

by Poongodi R and Doug Myers
Commercial Systems Division

Introduction

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.

Note
NOTE: The conversion from pseudo-mapped to mapped MUST be done on an F.0 version before updating to G.x. You cannot do the conversion after the system has been updated.

Converting Pseudo-Mapped Files to 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:
  1. Check for pseudo-mapped files as shown in this example: 
    
 :sqlutil

 >> showaccess
 DBEnvironment Name: mydbe
 Maintenance Word:
 GGF0        mapped
 MYF1        pseudo-mapped
 MYF2        pseudo-mapped
 >>

  2. Disable the pseudo-mapped feature using the SQLUtil MOVEFILE command as shown in this example: 
    
 >> movefile
 DBEnvironment Name: mydbe
 File Name: MYF1
 Current Device: LDEV#2
 New Device (opt):LDEV#
 Access Mode (Mapped) (opt): mapped

 File moved.
    Repeat for other pseudo-mapped files listed by SHOWACCESS. The pseudo-mapped files are converted into mapped mode. Hence, there will be no problem involved with the converted files during the upgrade.


IMAGE/SQL with TurboIMAGE/XL Enhancements

Year 2000 and Other Enhancements in VPLUS