HP3000/HP 3000 FAQ

Last Updated: Thursday, February 01, 2007 02:38 PM

TurboImage/ImageSQL

13.1. TurboImage/ImageSQL

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 file system ("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 file system, 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).

13.1.1. What do I need to access Image/SQL databases from a PC?

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

13.1.1.2. From a Mac?

None available yet.

13.1.1.3. What are these new files that appear on my (MPE/iX or XL) system related to my database? (basenameGB and basenameTC)

  [basename]GB - Created when the database is opened in batch or online.
                 Some concern because the file is released.
  [basename]TC - Permanent file.

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.

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

13.1.1.4. Setup hints and common problems with ImageSQL access

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.

Please 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