Last Updated: Tuesday, January 26, 1999 09:30 PM
See also the TurboImage/XL FAQ at Beechglen.com
aka Image or Image/3000; the database system "bundled" with every HP3000 (though you *can* order an HP3000 and have Image deleted, you do have to specifically request it -- and doing so is generally not a good idea as many third party packages depend on Image being on the system).
Image is a hierarchical database system (as opposed to a relational database). It has a two-tiered structure (master and detail datasets), which can be augmented by third party indexes (Omnidex/DISC) and external B-tree structures.
Image databases on the HP3000 are "protected" by the filesystem ("privileged files"). This means that Image databases cannot be accessed without going through the specific access routines designed for this purpose (Image Intrinsics). (Of course, privileged tools and utilities can bypass the normal filesystem, but this is not generally available to "application" programs).
The HP3000 comes with a utility to create databases (from schema files) - DBSCHEMA.PUB.SYS. A utility to create, purge, and set several control options in them - DBUTIL.PUB.SYS. A utility to enter data and do some reporting - QUERY.PUB.SYS (compatibility mode program) and QUERYNM.PUB.SYS (native mode version). There is also a freeware third-party query-like utility called "AQ/3000" (advanced query) which expands on some of Query's data-updating and reporting capabilities.
Image/SQL (an optional product which was provided to the HP3000 installed base for "free" in exchange for increased software support charges) adds SQL access to Image databases.
As of MPE/iX 5.5, Image also includes facilities to directly access "Image/SQL" databases from PC clients via the ODBC (Open DataBase Connectivity standard). Image SQL databases are SQL environments with Image databases "attached" as tables. (i.e. to access an Image database via ODBC, you must create an SQL environment, and "attach" one or more Image databases to the environment. The Image datasets then become "tables" accessible by the clients, appearing to be just another part of a host-based relational database).
Note that ODBC access is available "directly" to Image databases (and KSAM files and other file types) without having to use SQL environments via products available from a number of third party software vendors (M.B. Foster, Minisoft, and Linkway).
For ODBCLink from M.B. Foster:
--hardware on PC (net card/serial port/etc?)
one or more of the following:
- serial port (modem or hard wire connection)
- TCP/IP lan card
--software on HP3000 (product names, versions)
ODBCLink from MB Foster
ThinlanLink (part of MPE/iX 5.0 push) (only if you want lan connect
Image (Image/SQL not required, although can be used)
Allbase (optional)
Cognos PDL v 7.29 (optional, gets to KSAM and flat files)
host software for serial, PPL and winsock is supplied
--software on PC (product names, versions)
winsock.dll (WRQ's is nice, FTP Corp tested also) if you want
winsock connection
RNS from WRQ if you want lan connection via PPL
A client to use, we have tested with the following:
Impromptu v2.0 (Cognos)
PowerBuilder
MS Access v1 v1.1 or v2.0
Visual Basic v2 or v3
Visual C++ v1 or v1.5
MS Excel (really MS Query)
--any "bugs" or "features" to watch out for?
- with Image detail data sets, must have a unique key item for
updates
- most of the Microsoft products use a common interface called
the Jet Engine which causes updating problems in many cases
- using Visual Basic, if you code calls to the ODBC library, it
seems
that it works better than if you rely on the Experts that tend
to call the Jet Engine routines
--------------------------------------
For HP PC/API that comes with Image/SQL
--hardware on PC (net card/serial port/etc?)
- TCP/IP lan card
--software on HP3000 (product names, versions)
ThinlanLink (part of MPE/iX 5.0 push)
Image/SQL
Allbase (optional)
--software on PC (product names, versions)
RNS from WRQ is all I have used, don't know if alternatives exist
A client to use, we have tested with the following:
Impromptu v2.0 (Cognos)
PowerBuilder
MS Access v1 v1.1 or v2.0
Visual Basic v2 or v3
Visual C++ v1 or v1.5
MS Excel (really MS Query)
--any "bugs" or "features" to watch out for?
- versions prior to G.xx have been problematic, if you get involved,
best to get latest and greatest from RC before you begin
- with Image detail data sets, must have a unique key item for
updates
- most of the Microsoft products use a common interface called
the Jet Engine which causes updating problems in many cases
- using Visual Basic, if you code calls to the ODBC library, it
seems
that it works better than if you rely on the Experts that tend
to call the Jet Engine routines
None available yet.
[basename]GB - Created when the database is opened in batch or online.
Some concern because the file is released.
[basename]TC - Permanent file.
DB
The [basename]GB file is in actual fact the MPE/iX replacement for the DBB and DBG buffers of yore (MPE/V and IMAGE/V on MPE/XL) It is created when the first user accesses the database and purged when the last user closes the database. The buffer settings in DBUTIL have no effect on the size of this file.
The [basename]TC is a file that is created at ATTACH time. It is a small file and each record contains the name of the DBE to which the DB is attached. When the last DETACH is performed for that base, the TC file disappears.
There is also an ATCINFO file associated with the DBE that gets created at ATTACH time.
MWNote about Allbase (IMAGE/SQL) files
------------------------------------
I hope this clears up the confusion concerning
DBECon,DBEFiles,DBEFileSets etc..
An Allbase DBE consists of three types of file:
DBECon One only. This contains Allbase
startup parms such as logging
and other various controls
DBEFiles From one to thousands
User data is kept here
DBELogs For logging activity for recovery
and auditing
DBEFiles are collected together to form "DBEFileSets" There is no IMAGE counterpart to the "DBEFileSet", and is best imagined as a bunch of OSFiles grouped under a single heading. It is a "Set of DBEFiles", or DBEFileSet.
There may be many DBEFileSets created in an Allbase DBE. Remenber, a DBEFileSet is a logical thing, not physical
When an "Allbase native" table is created, its storage is allocated to a DBEFileSet, and not a single OS file which is the case when allocating a dataset to a file under turboimage. (IMAGE/SQL tables do not have any storage allocated to them out of ALLBASE DBEFileSet space, just a few rows in the Allbase system catalog used to describe it)
The Allbase Table may then grow to fill all DBEFiles in a DBEFileSet, at which point you may wish to increase the space you want made available to it by "increasing the capacity of the DBEFileSet". You do this simply by creating another DBEFile, then adding it to that DBEFileSet. Additionally, you do not have to allocate all the storage up front; you are able to create a DBFile with an initial allocation, an increment size and max size, Allbase will expand the file, by increment, when required.
A DBEFileSet may have just one or many tables assigned to it.
The Allbase system catalog (where image "table" and view definitions are kept) resides in a DBEFileSet called the SYSTEM DBEFileSet, and always includes the first DBEFile ever created (when the DBE is created in fact). The first file is called "DBEFLE0"....the same file as mentioned in previous postings to this thread.
The DBA would normally (and is recommended to) create other DBEFileSets having DBFiles to contain "user" data. This is because the SYSTEM DBEFileSet is the default location for temp tables used for results and sorting, etc. It is therefore important to keep contention on SYSTEM DBFiles as low as possible. There are other methods of controlling the allocation of, and contention for "SYSTEM" space such as defining tempspaces and moving stored sections etc, but are beyond this thread.
There have been so many requests for help about IMAGE/SQL lately, that I hope this might be some help:
1) Creating the DBE. Because this may be made up of a number of different files, you can never be absolutely certain that there will not be a name clash with a file you have already created. So I find that the easiest way to avoid clashes is simply to put your DBE into a group of its own, and not put anything else there at all. Some people prefer to let IMAGESQL create the DBE for them automatically, rather than doing it explicitly with ISQL. There are two drawbacks to this. The first is that the default SYSTEM filespace is not very big, and may very well be inadequate to accomodate sorts etc. The second is that the log files are not very big, and you may find you cannot execute an IMAGESQL "detach", without first deleting all the users one by one.
2) Maintenance words and access. When you create an IMAGESQL DBE (though I admit here that I'm just assuming it's the same as ALLBASE), it is released. So there is no MPE security on the DBE. Since you may want to attach Turbodb's from a number of different accounts, you may not be able to be logged in as the creator of both the DBE and the Turbodb. This is where maintenance words are useful, and if you are in this situation it will give much more flexibility if you set maintenance words on both the Turbodb and the DBE.
3) Set DDL off Once you have finished all the configuration via IMAGESQL, you can use SQLUTIL to switch off DDL (data definition language). This allows ALLBASE to assume that the system tables will not be changed when it opens the DBE, and keep them loaded in memory, which should give better response times.
PLPlease note that a PC Socket error 10054 is returned to PC API when users do not have write access to HPDAARLG.PUB.SYS. Please ensure that you have placed any ACD's on this file.
Try releasing HPDAARLG.PUB.SYS at least temporarily to test. If you have a very secure system, apply the following ACD to the log file:
@.@ = R,X,A,L
A truly "relational" DBMS for the HP3000 available from HP. A separate cost (purchased) product.
The popular relational DBMS from the "outside world" is available for the HP3000 (though not the "latest" release -- and no plans to bring Oracle 8.0 to the HP3000 at present; HP had been paying Oracle to port Oracle RDBMS releases to the HP3000 but apparently no longer plans to subsidize it). Available from Oracle Corp.
Was available for the HP3000 at one time. Reportedly not up-to-date and may not be purchaseable from Informix any longer.
We currently know of no other DBMSes available for the HP3000, though there are rumors that some Unix/Posix freeware DBMS ports are being looked into.
MPE has a built-in facility to log transaction data. This logging facility is utilized by TurboImage on the HP3000 to record transaction information and to facilitate reporting and recovery of database transactions.
Image logging can be used in creating reports of database transactions or in the recovery of database transactions. Though there is no built-in facility to generate "audit" reports from an Image log file, there are several third party packages which will do this.
The ability to recover transactions from an Image logfile on the other hand is "built in" to TurboImage.
The transaction manager is another facility built into MPE/iX that assists in maintaining the consistency of TurboImage databases.
The basic procedural difference is:
Roll forward recovery:
Roll Backward recovery:
Each method has it's advantages and disadvantages.
For instance, a database which is structurally "damaged" would likely need to be restored from a backup media, and transactions applied to it to "bring it up to date" (roll forward).
On the other hand, a database which is structurally intact but has been "damaged" by erroneous processing (perhaps someone accidentally deleted all of the week's orders) could use the log files to "roll back" the database, undoing the transaction(s) that corrupted the database, or back to a point in time before the error occurred.
Image logging (actually the MPE logging facility which Image logging uses) allows you to direct transaction log files to disc or to tape devices.
At present the Image logging facility is a CM (compatability mode) subsystem and utilizes some CM datastructures, which impose some performance penalties and a limitation on the number of concurrent processes that can access an Image database which is being logged. See below for details.
Some estimates of the performance impact for databases which have logging enabled run as high as a 10% throughput reduction.
Due to limits in MPE's internal tables, the maximum number of processes that can share a log process -- which in this case means the maximum number of processes that can open a database with modifiy-access -- is 1140. Beware of this on limit on large systems.
1) You must setup a system logging process (see :GETLOG command) where you'll specify the log process "identifier", password, and the fileset (or tapeset) you'll have it writing to. You'll also specify whether you want full log files to "roll over" to new (sequentially numbered) files automatically (which you will if you're using DISC files.)
2) You'll need to start the log process (:LOG name,START). This is also a good time to add the appropriate commands to your system startup procedures to make sure the log process(es) get restarted when you restart the system (this must be done explicitly each time the system is restarted). It should also (preferably) be stopped gracefully before a system shutdown.
3) You'll need to associate the database with a logid (in DBUTIL >>SET base LOGID=logidname )
4) You'll need to enable the database for logging (again in DBUTIL >>ENABLE base FOR LOGGING)
5) Perhaps before (4), you'll want to ensure that you have modified your system backup procedures appropriately to make sure the appropriate log files get backed up. See the next FAQ item.
At a minimum, for logging processes which log to disc, you'll likely want to do a :CHANGELOG command against each logging process when you start a system backup. This causes the log process to close the current file and open a new file; allowing you to ensure you have the maximum amount of recoverable data (right up to the point of the start of the backup) on your backup media. Without this, the current log file (which may have tens of thousands of transactions) will not get backed up because it is constantly "in use" by the logging process.
For users with "online" backup packages, a :CHANGELOG is probably sufficient (though consult the documentation with your backup product for their recommendations). This way your users can continue to access the database(s) while the backup runs.
For sites without online backups, you may actually want to STOP the various logging processes when you are about to start the backup (**Note that the :LOG xxx,STOP command, though it returns immediately, may take several seconds to complete, so factor that delay into your backup job stream/script). Once your backup completes, you can :LOG xxx,RESTART the log processes. A side effect of this; if the database is enabled for logging but the log process is not running, users/applications will not be able to open the database -- they get an Image error indicating the log process is not running. This can prevent someone from "sneaking in" and opening a database before the backup gets a chance to lock the file -- which causes your database to not get backed up.
Image logging records every change made to the logged database(s). Every database accessor that opens the database in a mode capable of modifying data is recorded in the log files, with information including the Job/Session number, logical device, logon info, program used, and date/time. Database information logged includes records added (with complete record image), records deleted (again with a copy of the entire record that was deleted), and changes (which records the key information, followed by the chaged fields with old and new values of each).
There are various software packages that can read these log files and produce very comprehensive records of database modifications.
Note for users attempting to write their own programs; the Image log file records are variable length record format, with the first 11 or so words being in a format documented under the "WRITELOG" Intrinsic (which Image uses to record the data); the remaining data in each record after that point is in the format as documented in the Image manuals. Accessing the data is somewhat tricky as the Image portion of each log record contains numerous "pointers" to fields farther along in the record. Also, Image fields are recorded by item numbers, so your program will need to have access to the real database and use DBINFO to get the actual set/item names (or you'll have to hard-code a list).