Beechglen Development Inc.

Beechglen Development Inc.
3862 Race Road - Cincinnati, Ohio 45211 - (513) 922-0509 - Fax (513) 347-2834

Query for Fun and Profit

The data contained in your Image databases is one of your biggest assets. Making this available to other systems is often desired. Many papers have been written detailing different methodologies for sharing data between systems including ODBC, extract to tab or comma delimited files, bridgeware utilities, etc. I don't intend to address the methods for moving the data. I want to discuss the data itself.

Image does not apply any constraints on the data, it simply stores and organizes bits. So it is possible to have alphabetic characters in numeric fields and binary data in alphanumeric fields. Moreover, it is possible to have fields that are supposed to contain dates that have invalid date values, often times intentionally. For example, systems where special values have specific meanings, such as a blanket order is defined as an order with a due date of 99/99/99. Sharing this data with an SQL database is going to be a problem. This will be rejected as an invalid date.

Before you undertake a project to either share or export your data to other system you must identify what kind of data you have. A simple method is to use Query to provide a histogram of each data value and the number of occurrences.

:query
>b=sales
>PASSWORD = >> password
>MODE = >> 5
>assign lockoption = off
>f all customers.cust-num
USING SERIAL READ
nnn ENTRIES QUALIFIED
>report
>s1,zipcode
>h1,"Zip Count",20
>t1,zipcode,10
>t1,zipcode,20,count
>end
Zip            Count
99501              1
99503              2
99508              1
?                742
A1B 4A4            1
B1P 6K5            1
B1P 6L2            1

You can easily load this output into a spreadsheet and sort by the "count" to bring all of the values with high counts to the top. And if you prefer to view the data in graphical form a spreadsheet can readily create a histogram.

Even if you have no plans to share or move your data you can learn much about your system by reporting the population of data within your databases. In the case listed above I immediately see two potential problems. First, there are Canadian style postal codes in the zip code field. Second, customer service reps are entering a "?" when the zip code is unknown, and nobody has ever gone back to clean up the data. Both situations will cause problems for a system that has zip code defined as a numeric entry.

If you have other tools at your disposal, you can produce the report with those as well. Here are examples using QUIZ and SUPRTOOL.

:quiz
>access customers
>sort on zipcode
>footing at zipcode &
> zipcode &
> zipcode subtotal
>go

:suprtool
>base sales
Database password [;]? password
>get customers
>sort zipcode
>duplicate none keys count
>extract zipcode
>list norec noskip
>xeq

One of my other favorite things to do with Query is to create scripts on the fly taking advantage of command redirection to pipe MPE variables into the FIND command. Here is a script that produces a report from the ESP database on datasets that are expected to fill up within a specified number of days, which is passed on the command line and defaults to 5 days.

parm CUTOFFDAYS=5
setvar days,rht(" !CUTOFFDAYS",4)
echo B=ESP.PUB.ESP >qsin
echo ; >>qsin
echo 1 >>qsin
echo FIND DAYS-OF-ENTRIES IB " 1","!DAYS" >>qsin
echo REPORT >>qsin
echo S1,DAYS-OF-ENTRIES >>qsin
echo H1,"Beechglen Development Inc.",52 >>qsin
echo H3,"DATA BASE",10 >>qsin
echo H2,"Datasets expected to fill in !CutoffDays Days",57 >>qsin
echo H3,"DATASET",38 >>qsin
echo H3,"TP",46 >>qsin
echo H3,"CAPACITY",57 >>qsin
echo H3,"%",62 >>qsin
echo H3,"ENTRY/DAY",72 >>qsin
echo H3,"DAYS",79 >>qsin
echo D1,BASE-NAME,26 >>qsin
echo D1,SET-NAME,43 >>qsin
echo D1,SET-TYPE,46 >>qsin
echo D1,CAPACITY,57 >>qsin
echo D1,PERCENT-FULL,62 >>qsin
echo D1,ENTRIES-PER-DAY,73 >>qsin
echo D1,DAYS-OF-ENTRIES,79 >>qsin
echo END >>qsin
echo EXIT >>qsin
query <qsin


:dbcap 15
                       Beechglen Development Inc.
                    Datasets expected to fill in 15 Days
 DATA BASE                     DATASET      TP   CAPACITY    % ENTRY/DAY   DAYS
SALES.DATABASE.PROD        CUSTOMERS        D       73413   73      1102     12
MFG.DATABASE.PROD          ORDERS           D      135981   89      2137      7
EXIT

Using Query as a Diagnostic Tool

Query makes a great tool for categorizing questionable behavior as either a database problem or an application issue. Whenever I hear of an Image error from an application program I ask "Can you reproduce the problem in Query?" If a problem cannot be reproduced in Query, in all likelihood it is an application problem rather than a database problem. Because Query works directly at the database it also isolates issues such as data dictionaries in 3rd generation languages like Quiz and Transact, and intermediate data servers like ODBC drivers. Data interpretation issues are easily confirmed by looking at the raw data with Query.

Another useful diagnostic tool is a recent enhancement to Query where you can retrieve a database entry by its record number. This is handy when an application aborts and includes a record number in the abort message, but no other information is supplied or the information is unreadable. You can select the record by its number and either fix it or delete it.

>f customers.#27481
1 ENTRIES QUALIFIED
>replace OPEN-AR="0";end

Query and B-Tree Indexes

The latest version of Query can also take advantage of Image B-trees in selecting data. To enable this feature you must set the BTREEMODE1 flag to ON in DBUTIL. Once this is set you can use wildcard retrieval in Query (and in your own application programs if they allow it.)

:dbutil
HP30391C.09.05 TurboIMAGE/XL: DBUTIL (C) COPYRIGHT HEWLETT-PACKARD COMPANY 1987

>>addindex SALES for CUST-NAMES-AUTO
Adding index to set# 2 (#entries = 15159, capacity = 10196)
Done
>>set SALES btreemode1=on
>>exit
:query
HP32216D.03.20 QUERY/NM FRI, MAR 1, 2002, 4:46 PM
COPYRIGHT HEWLETT-PACKARD CO. 1976

>b=SALES
PASSWORD = >> password
MODE = >>5
>s=CUSTOMERS
>f CUST-NAME="ACME@"
3 ENTRIES QUALIFIED

Some of these newer features are only available in the Query version D.03.17 and higher. This version is included in Release 7.0 and is available in the latest Query patch for 6.0 and 6.5. Applying a Query patch does not require any system downtime. And because Query is a standalone program and is fully backward compatible it can also be loaded on 5.5 systems.