Suprtool and PowerHouse Datatypes

One of the recurring themes in Tech Support calls we receive is that of identifying datatypes when defining fields in Suprtool. This is particularly true of PowerHouse sites, as PowerHouse views the data through it's dictionary definitions, while Suprtool's vocabulary is based on the Image terminology. However both products read the same underlying data structures, so the problem is one of semantics only. This document aims to assist the user in correctly declaring data structures being passed between Suprtool and PowerHouse processes.

Translation table of datatypes

Image Type PowerHouse Type Suprtool Type
U or X Character Byte (Character for NLS)
R Float (non-IEEE)* Real or Long
E Float (IEEE)* IEEE
K Integer Unsigned Logical
J Integer Signed Integer or Double
P Packed Packed
Z Zoned Display
* IEEE or Non-IEEE depends on whether "float IEEE" or "float NONIEEE" was declared under "System Options" in your System Dictionary

Back to the top .....

Tip 1: Use QSHOW to examine PowerHouse data structures

Many users are familiar with QUIZ' "show items" command:

> access d-sales
> show items
                                     INPUT OUTPUT
D-SALES                         TYPE SCALE SCALE  DEC PICTURE
* CUST-ACCOUNT                  NUM     0     0    0  ^^^^^^^^
  DELIV-DATE                    NUM     0     0    0  ^^^^^^^^^^
* PRODUCT-NO                    NUM     0     0    0  ^^^^^^^^
  PRODUCT-PRICE                 NUM     0     0    0  ^^^^^^^^^^
  PURCH-DATE                    NUM     0     0    0  ^^^^^^^^^^
  SALES-QTY                     NUM     0     0    0  ^^^^^
  SALES-TAX                     NUM     0     0    0  ^^^^^^^^^^
  SALES-TOTAL                   NUM     0     0    0  ^^^^^^^^^^

This listing shows you the attributes and sequence of the fields, but doesn't give any clues as to their storage format.

QSHOW gives a much more useful listing:

> show subfile salesub
98/05/13                    SUBFILE DICTIONARY              Page    1
                           SUBFILE RECORD REPORT

    Subfile:            SALESUB
    Type:               Permanent
    Organization:       Direct
    Format:             7
    Record Size:        38 Bytes

 Item                           Type            Offset Size  Occurs

 CUST-ACCOUNT                   ZONED UNSIGNED   0      8
 DELIV-DATE                     INTEGER SIGNED   8      4
 PRODUCT-NO                     ZONED UNSIGNED   12     8
 PRODUCT-PRICE                  INTEGER SIGNED   20     4
 PURCH-DATE                     INTEGER SIGNED   24     4
 SALES-QTY                      INTEGER SIGNED   28     2
 SALES-TAX                      INTEGER SIGNED   30     4
 SALES-TOTAL                    INTEGER SIGNED   34     4

Notice that this shows the offset of the fields within the record (with the first field starting at offset 0), the length (size) of the fields, as well as the storage datatype. In most cases, these map directly onto the Image/Suprtool datatypes. (See Translation Table for mappings)
For example, the above subfile can be defined in Suprtool as follows:

> define cust-account,1,8,display
> define deliv-date,9,4,integer
> define product-no,13,8,display
> define product-price,21,4,integer
> define purch-date,25,4,integer
> define sales-qty,29,2,integer
> define sales-tax,31,4,integer
> define sales-total,35,4,integer

Note that PowerHouse's ZONED UNSIGNED datatype is equivalent to Suprtool's DISPLAY datatype, and the Suprtool's define offset starts at 1, so is one higher than the offset in the QSHOW listing.

Back to the top .....

Tip 2: Where is the Data Coming From?

When a PowerHouse Subfile contains complete records from an Image Dataset, you can have Suprtool read the record structure directly from the Image Rootfile:

>base store,1,WRITER
>input salesub = d-sales


    D-SALES          Detail                  Set# 5
       Entry:                     Offset
          CUST-ACCOUNT         Z8      1  (!M-CUSTOMER)
          DELIV-DATE           J2      9
          PRODUCT-NO           Z8     13  (M-PRODUCT)
          PRODUCT-PRICE        J2     21
          PURCH-DATE           J2     25
          SALES-QTY            J1     29
          SALES-TAX            J2     31
          SALES-TOTAL          J2     35
    Capacity: 602 (14)  Entries: 8  Highwater: 8  Bytes: 38

Of course, if the subfile contains only some of the fields, then you'll need to define them individually. But you can still use Suprtool's FORM command to examine their storage structure in the datasets they've been extracted from ......

Back to the top .....

Tip 3: Defined Fields' Datatypes

In some cases, subfiles may contain fields that were created within a previous Quiz task, namely:

Defined fields will have whatever storage type was specified when they were created. If storage type "numeric" was specified, PowerHouse will create a FLOAT Size 8 field by default. It is also important to note also that if the define is not explicit about the field size, PowerHouse will assume a size based on the requested number of digits:
      define D-PRICE integer*4 = 25
.... creates a field capable if storing 4 digits, i.e. a 2-byte integer, while
      define D-PRICE integer size4 = 25
will quarantee a 4-byte integer field. When in doubt, use QSHOW to examine the structure of the subfile.

Back to the top .....

Tip 4: Fields Created by Summary Operations

Here is a list of Datatypes for PowerHouse summary functions written to subfiles:
CountInteger size 4
Subtotal Float Size 8
Minimum Float Size 8 for numerics, Character size nn for char fields
Maximum Float Size 8 for numerics, Character size nn for char fields
Average Float size 8 subtotal, Integer size 4 count, and an Integer size 4 filler of binary zeros.
Percent two Float Size 8 subtotals
Ratio two Float Size 8 subtotals
Sysname Character size 40
Sysdate Phdate integer size 2 or 4 (if century included)
Systime Integer size 2 (HHMM format)

[Hans Hendriks]

....Back to the top
....Back to the Suprtool Q&A Page