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
- Tip 1: Use QSHOW to examine PowerHouse data structures
- Tip 2: Where is the Data Coming From?
- Tip 3: Defined Fields' Datatypes
- Tip 4: Fields Created by Summary Operations
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 |
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.
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
>form
Database: STORE.DEMO.ROBELLE
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 ......
Tip 3: Defined Fields' Datatypes
In some cases, subfiles may contain fields that were created within a previous Quiz task, namely:
- Defined fields
- Results of summary functions (Count, subtotal, etc ...)
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.
Tip 4: Fields Created by Summary Operations
Here is a list of Datatypes for PowerHouse summary functions written to subfiles:
| Count | Integer 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]