|
|
|
|
|
|
|
|
Communicator 3000 MPE/iX Release 6.0 (Platform Software Release C.60.00): HP 3000 MPE/iX Computer Systems > Chapter 10 Technical Articles
IMAGE/SQL with TurboIMAGE/XL Enhancements |
|
by IMAGE/SQL Team Commercial Systems Division This article discusses enhancements as well as other important information commencing with the Express 3 release of MPE/iX 5.5. Express 3 (and later releases) also includes the revised editions of some selected manuals. Highlights of this release of IMAGE/SQL, version B.G3.00 or later, and TurboIMAGE/XL, version C.07.14 or later, are the following:
The following manuals are included with IMAGE/SQL:
If you are updating from an earlier release of IMAGE/SQL and you have databases which are already ATTACHed to one or more DBEnvironments, you must DETACH and ATTACH again in order to benefit from the enhancements. Also, it is recommended that you issue the UPDATE STATISTICS command of ISQL for each of the tables you access frequently. This may enable you to attain performance improvement. Furthermore, if you have created SQL data using an earlier release of IMAGE/SQL and are now updating to a latter release, you must perform the ALLBASE/SQL migration using SQLINSTL or SQLMigrate. For more information, refer to the ALLBASE/SQL Database Administration Guide and the Read Before Installing for this release. The following are TurboIMAGE/XL enhancements, independent of SQL access:
Dynamic data set expansion feature for non-jumbo (file size within 4 GB limit) detail data sets (DDX) was first released in MPE/iX 5.0. With this release, this feature is available for non-jumbo master data sets (MDX or DDXM) as well. The feature allows dynamic expansion of a data set during DBPUT when the data set has approximated its current capacity and DBPUT would fail unless the data set is expanded. As in DDX, the capacity parameters which are maximum capacity, initial capacity, and increment (optional), used for dynamic expansion, must be set prior to the actual expansion. For new databases, these parameters can be specified in the CAPACITY statement of the schema definition to be processed by DBSCHEMA. For existing databases, third-party tools which support MDX need to be employed. The capacity parameters are:
The enhanced syntax for the CAPACITY statement of DBSCHEMA is same for both DDX and MDX, and is as follows:
The instant when the expansion is triggered and how record address is assigned to the new entry in a master data set varies greatly from detail data set. In a detail data set, expansion takes place during DBPUT when the free entry count for the set is zero. In case of a master data set, expansion is triggered during DBPUT when there is no free (unoccupied) entry in the close proximity (within an internally-maintained number of blocks) of the calculated primary address of the new entry and the set is expandable. This means that an expansion takes place even when the free entry count is greater than zero. Following the first expansion, the master data set can be perceived as having two areas, original area and expanded area, managed differently. The original area is managed in the same way as in releases prior to MDX, that is, the primary address for the new entry is calculated using the hashing algorithm and the secondary address is assigned according to the availability of the free entry, but with modifications in searching for a free entry. The use of the expanded area is controlled using the pointer to a delete chain head and the last entry used in the file (high-water mark) as in detail data sets. Subsequent expansions belong to the expanded area. The original area can have primary entries and secondary entries, while the expanded area can only have the secondary entries. Note that the primary capacity (also hashing or initial capacity) used to calculate the primary address does not change. In essence, the dynamic expansion results in allocating additional space for secondary entries which implicitly makes room for additional primary entries in the original area. The primary address of a new entry is calculated using the value of the entry's key item and primary capacity in the hashing algorithm. The new entry can reside either at its primary address in the original area, or in the close vicinity of the primary address in the original area, or in the expanded area. To elaborate further, when a DBPUT for a manual master, or implied DBPUT to an automatic master, is processed, the address where the new entry resides is based on the following:
Following the expansion, TurboIMAGE/XL updates various fields related to the expansion such as current capacity, including the expansion, in the root file. Also, the data set user label is updated for the last entry used in the file (high-water mark), free entry count, and pointer to the put-delete chain head. The following intrinsics and utilities are enhanced to support MDX:
TurboIMAGE/XL version C.07.14 includes an enhancement which will allow jumbo data sets to be up to 80 GB in size, twice as big as the old limit. There are no new externals/options added for this enhancement. For new databases, DBSCHEMA will allow data sets to be up to 80 GB in size. As for jumbo data sets, the control option '$CONTROL JUMBO' is required prior to specifying a data set greater than the default of 4 GB. For existing databases, use third-party tools which support this feature. If you use third-party indexing software, you may need the version of the software which supports this feature. When creating a jumbo data set of this magnitude using DBUTIL, you will experience some delay. TurboIMAGE/XL intrinsics DBGET and DBFIND are enhanced for improved performance in the following circumstances when the database is enabled for TPI:
In order to attain the performance gain, you must obtain the version of the third-party software which supports this enhancement. In the absence of either one of the needed software products (TurboIMAGE/XL or third-party product), DBFIND and DBGET will continue to work as they do in the absence of this enhancement. That is, TurboIMAGE/XL and the third-party products are backward compatible for this enhancement. DBSCHEMA is enhanced to generate an error when the sub-item length of the datatype P is odd, even if the product of sub-item count and sub-item length is divisible by 4. For example, DBSCHEMA will flag the data items 4P1 or 8P3 as invalid. Although syntactically correct, none of the major languages can use them. This change will not affect existing databases. That is, if you currently have databases with P type defined using odd sub-item length and sub-item count such that their product is divisible by 4, you may continue to use it without any problem when creating a new database. The default for DBSCHEMA is to generate an error in this situation. If you want to create a new database with P data type having odd sub-item length, you will need to use the new CONTROL option, $CONTROL ODDPALLOWED. You can create a B-Tree index on the master data set's key item and perform B-Tree index searches using the key item as well as all of its corresponding detail data set search items. The B-Tree index searches are done using DBFIND with a master or detail data set and its key or search item respectively. The qualified entries can be retrieved using DBGET mode 5 or 6. Following are key features of B-Tree indices:
If you are interested in generic trailing-@ searches only and want to get started quickly without making any application changes, you may use the following steps.
If your database is new, you will need to add data. You are now ready to perform B-Tree index searches. You can include the wildcard character in your DBFIND argument and observe the results. Prior to this enhancement, the modification intrinsics, DBPUT, DBDELETE, and DBUPDATE (Critical Item Update feature ON) were serialized for the database. That is, only one such intrinsic could execute at a time for the database. In order to scale with the high-end machines, TurboIMAGE/XL is enhanced to increase the concurrency of these modification intrinsics. The increased concurrency is based strictly on the database design. The database is internally grouped into independent SUB-DATABASES based on the physical relationships of master and detail data sets. Subsequently, the modification intrinsics can execute concurrently for these independent subdatabases. The throughput is dependent on the number of subdatabases, the more the better. The worst case is a database with only one subdatabase, one detail linked to 16 masters. The best case is when there are numerous stand-alone masters or details. To use this enhancement, your database must be activated to use the Dependency Semaphore. To achieve this, the ENABLE and DISABLE commands of DBUTIL introduce a new option, DSEM. The default for DSEM is DISABLED. New syntax
After enabling your database for DSEM, if you feel that your database design cannot attain expected concurrency, you may DISABLE the feature as there is some overhead when the database is enabled for DSEM. (SR# 5003-367607) The DDX feature of TurboIMAGE/XL has a known problem which may cause you to lose some of your new data entries added following the dynamic detail dataset expansion. This defect has been in the feature since its introduction, but has surfaced recently and has been reported by a few customers. The problem has been fixed both for MPE/iX 5.5 and MPE/iX 6.0. The patch ID for MPE/iX 6.0 is TIXKX62 (or superseded patch id) and the TurboIMAGE/XL version is C.07.10. This patch is contained in this release. The problem is that, following a dynamic expansion, the addition of the new data entries (DBPUTs), which exceed the INCREMENT in number, are written to the data set beyond the physical end-of-file (MPE EOF). When the database is closed and reopened, those new entries exceeding the INCREMENT are inaccessible. If a program attempts to read that data or add more data entries in that area, it gets an error -212, Database is Corrupt. The following diagram illustrates the problem.
The real problem is that, for certain expansions, the new CAPACITY is inadvertently adjusted by adding the INCREMENT more than once instead of adjusting only once. This results in incorrect calculation of IMAGE EOF and subsequently allows new data entries to be placed in the area which really does not belong to the data set file. Therefore, when the data set is closed, the new entries added beyond the MPE EOF are not retained as part of the data set file by the MPE file system and are subsequently lost. The circumstances in which such an erroneous adjustment could take place are as follows:
How to tell if you already have this problem. If you use DDX, check each detail data set enabled for DDX. Remember, the problem is associated only with the detail data sets which have undergone dynamic expansion. You can check for this condition as follows:
If you use one of the IMAGE/SQL structure maintenance tools (such as Adager, DBCPLUS, or DBGENERAL), it may have the capability to locate instances of this problem, and if you discover that one of your data sets is so afflicted, it may be possible to correct the problem using the same tool. (See the documentation for your tool.) How to avoid experiencing this problem on your IMAGE database. The simplest answer is to install and use the TurboIMAGE/XL patch mentioned above. This is the option that HP recommends. However, if you are unable to acquire and install the patch immediately, consider the following alternative:
Here are some ways you might accomplish this; each way is independent of the others:
Information regarding enhancements to IMAGE/SQL for SQL access is described in this section:
IMAGE/SQL version B.G2.07 is enhanced to apply predicate level locks (row level locks), instead of a set level lock as in prior versions, when updating selected entries in the manual master data set. When IMAGE/SQL is used as the SQL interface to TurboIMAGE/XL database, the responsibility of enforcing appropriate locks rests with IMAGE/SQL. These locks should conform to the locking schemes present in TurboIMAGE/XL. Accordingly, locks are set by IMAGE/SQL depending on the DBOPEN modes and isolation level requirements. At present, for a master dataset, for all modify intrinsics, that is, insert, delete and updates, and for DBOPEN modes 1 to 4, IMAGE/SQL puts a set level lock (or a pseudo set level lock covering all the rows in the set). But for update function in DBOPEN mode 1, setting a set level lock (or pseudo set lock) is not mandatory, a row level lock would be sufficient. Therefore, IMAGE/SQL will now lock at the predicate level if a 'where' clause is specified, otherwise a set lock will be given. This feature is actually transparent to the user. When multiple users are operating on the same data set, it is difficult to update different entries with set level lock in place. But when a predicate level lock is in effect, the unnecessary wait time is avoided, thus allowing multiple updates of different entries on the same data set. For an example, User A has an update statement:
The kind of lock that is placed can be seen by invoking DBUTIL as shown:
Now, User B wants to update row(s) in the same data set via an update statement:
The kind of lock that is applied by User B (JOBNUM #S21) is shown in the following example:
In this example, User A updates 2 entries and User B updates 5 entries simultaneously. With this enhancement, the locking scheme in IMAGE/SQL is exactly as required by TurboIMAGE/XL (as described in TurboIMAGE/XL Database Management System Reference Manual, Aug 1997). All applications conforming to this will not have any problems, and may actually see improvements in performance, when updating manual masters. The B-Tree indices can be created using DBUTIL for existing databases and DBSCHEMA for new databases. More details about creating B-Trees indices is given under the heading, "TurboIMAGE/XL Enhancements," earlier in this article. In order to realize the benefit of the B-Tree indices for SQL access, you must first DETACH the database, if already attached, and ATTACH the database for the DBEnvironment. IMAGE/SQL enters definitions for the B-Tree indices in the system catalog of the DBEnvironment. The index on the key item of the master, except for P and Z types, is entered as 'unique' index. Other definitions, by default, are non-unique. Although the B-Tree index on the related search item of the detail set is not explicitly created using DBUTIL or DBSCHEMA, its definition is entered in the SQL catalog. For example, if a B-Tree index is created on a key item with paths to 16 detail data sets, a definition for a B-Tree index for all 16 data sets will be entered as well. That is, this will result into 17 (1+16) definitions, one for each data set. If your key item or its related search item is split using the SPLIT command of IMAGESQL, the definition for a B-Tree index on the split item will not be entered. For an example, if a key item is split but the related search item is not, the definition for the key item of the master data set will not be entered, but the one for the search item will be. The B-Tree indices can be viewed in the views, SYSTEM.INDEX and CATALOG.INDEX, of the system catalog of the DBEnvironment. Multiple index definitions on the same column can coexist and the SQL optimizer derives the access plan based on the statistics present in the system catalog. In other words, the key or search item of the set can have a maximum of three index definitions. One will be a hash index (only "=" operator permitted) automatically done by IMAGESQL at ATTACH time, another can be a B-Tree index, and the third can be a third-party index. It is recommended that both B-Tree index and third-party index be not created on the same item as it will unnecessary impact the performance (Optimizer calculates cost for each index). The Optimizer derives an access plan and decides which index to use and the proper order of operations. The version of third-party software that supports the new B-Tree index modes for DBFIND, DBCONTROL, and DBINFO, is required from both third parties. IMAGES/QL is enhanced to enter definitions for third-party composite indices in the system catalog of the DBEnvironment at ATTACH time. These composite indices can be on mixed data types, but must be on FULL items. Information about all third-party indices, including composite, is obtained from the third-party product, which is subsequently used during ATTACH. Both SUPERDEX and OMNIDEX do not provide information on the composite indices on substrings (partial item) of items to IMAGE/SQL. These indices can be viewed in SYSTEM.TPINDEX along with other third-party indices. At run-time, IMAGE/SQL may employ DBFIND mode 1 with "@;" appended to the argument, or mode 11 with start and stop values, as deemed appropriate. Note that if you have multiple indices for the table, the SQL optimizer has the control on the type of access, and the specific index to be used. For SQL access, the only step necessary to use the above enhancements pertaining to relational access, is to perform DETACH, if attached, and ATTACH.
In this release, IMAGE/SQL is bundled with a new 16-bit and 32-bit ODBC driver, ODBCLink/SE, which is a replacement for the ODBC driver HP PC API. ODBCLink/SE is an implementation of Microsoft's Open Database Connectivity (ODBC) interface that enables Microsoft Windows based applications and tools to access TurboIMAGE/XL data on the HP3000 in a client/server environment. In such an environment, the application developers and end-users can take advantage of the PC's graphical user interface (GUI) and processing power, while relying on the security, integrity, and database management capabilities of IMAGE/SQL. The client can run under Microsoft Windows 3.1 or 3.11, Windows95, or Windows NT V3.51 or V4.0. Connection via Winsock is available in 16-bit and the new 32-bit version. The driver can be used in two ways: either by direct calls from a Windows program or through an ODBC-compliant application such as Microsoft Access and Visual Basic. A data migration tool is provided to migrate data sources created for the current HP PC API to ODBCLink/SE. Once the data sources have been identified, the translation is performed automatically. The ODBCLink/SE server runs on MPE/iX 5.0 or later releases. ODBCLink/SE is ODBC Level 2 compliant with a few exceptions. For more information, refer to the article, "Introducing ODBCLink/SE," in this Communicator 3000. The ODBCLink/SE Reference Manual is bundled with IMAGE/SQL. |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||
|
|
|
|||||||||
|
|||||||||
