Integration: Raw Data Files

Omnidex Standalone Tables

Datatypes

The record layout described in the CREATE TABLE statement must exactly match the order, datatype, and length of columns in the Omnidex Standalone Table. If these record layouts do not match, Omnidex will return an error when the table is accessed.

Omnidex Standalone Tables support all of the Omnidex datatypes. The datatype restrictions that exist for fixed-length files and delimited files do not exist for Omnidex Standalone Tables.

The internal schema of the OST can be viewed using one of two methods. It is possible to view the column layout for an Omnidex Standalone Table by attaching it to an environment using the ATTACH statement and then using the SHOW TABLE statement.

> attach ost dat/states.ost as s
Table S attached
> show table s

                                   $TEMPDB.S
                                   ---------

Physical table name:         D:\class\lab2b\dat\states.ost
Type:                        OST
Role:                        TABLE
Options:                     deletes compression=auto null_indicators
Temporary:                   Yes
Record length:               44
Native record length:        44
Omnidex record length:       44
Data caching:                Dynamic
MDK data retrieval:          MDK indexes not installed
ASK data retrieval:          ASK indexes not installed
Omnidex installation:        NONE
Index Maintenance:           API
Num columns:                 6
Num native columns:          6
Cardinality:                 76
File Size:                   41K

Constraints:
   Unique key:               $ROWID



Column                               Datatype                   Usage
-----------------------------------------------------------------------------
STATE                                CHARACTER(2)
DESCRIPTION                          STRING(31)
STATE_CODE                           CHARACTER(2)
REGION                               CHARACTER(2)
COUNTRY                              CHARACTER(2)
TAX_RATE                             FLOAT



Type
  Owner
    Index                            G# RS KW CI EW SX PX BI DI BM RK
      Component[start:stop]
-----------------------------------------------------------------------------
Native Indexes
    STATE


No MDK Indexes


No ASK Indexes

>


More detailed information about the Omnidex Standalone Table can be seen using the DUMP OST statement in OdxSQL. This command will dump the metadata for the OST as well as the each row, printed in hex and character.

> dump ost dat/states.ost

OST File Meta-Data
 Filename:             dat/states.ost

 Header:
   Sentinel:           OST
   Locked:             No
   Endian:             Little
   Version:            30000
   Timestamp:          Thu Mar 10 13:49:33 2011
   Rowid Counter:      76
   Num Rows:           76
   Num Deleted:        0
   Page Counter:       1
   Num Pages:          1
   Page Size:          4096
   Lob Page Size:      0
   Tbl Offset:         392
   Tbl Length:         2308
   Comments Offset:    2700
   Comments Length:    32
   Data Offset:        2752
   Wastage:            1.6K
   Hash Tbl Size:      19
   Hash Offsets:       6848,0,0,0
   Hash Lengths:       684,0,0,0
   Hash Length:        684

 Comments:
   (select * from STATES)

 Table STATES:
   Endian:         L  Encryption:     N  Deletes:        Y  Compression: TRAIL
   Reclen Len:     2  Rowid Len:      4
   Null Ind:       Y  Null Off:       2  Null Len:       1
   Hdr Rec:        7  Data Rec:      44  Total Rec:     51  Card:           76
   Num Cols:       6  Num Keys:       0  Num Idxs:       1

   Column Name                      Datatype           Usage        Options
   ---------------------------------------------------------------------------
   STATE                            CHAR(2)                         TRUN VALUE EXP
   DESCRIPTION                      STRING(31)                      TRUN VALUE EXP
   STATE_CODE                       CHAR(2)                         TRUN VALUE EXP
   REGION                           CHAR(2)                         TRUN VALUE EXP
   COUNTRY                          CHAR(2)                         TRUN VALUE EXP
   TAX_RATE                         FLOAT                           VALUE EXP OVRFL

   Idx Name                         Type      Unique Case-Sensitive Data-Length
     Component           Offset Length
   ---------------------------------------------------------------------------
   STATE                            NATIVE        Y      Y              2
    STATE                0      2

OFX file for dat/states.ost:

 Header:
   Version: 20000  Timestamp: Thu Mar 10 13:49:33 2011

   Storage:  Offsets  Offset Len:     8  Incrementg:     N  Locked:         N
   Min Reclen:    20  Max Reclen:    51
   Page Sz:    32768  Entr/Page:   4095
   Subpg Sz:       0  Entr/Subpg:     0  Subpg/Page:     0
   Num Pages:      1  Num cols:       6  Num rows:      76  Highwater:     76
   Tbl Offset:   352  Tbl Length:  2308  Own Offset:     0  Own Length:     0
   Data Offst:  2672


OST dump of dat/states.ost


dat/states.ost is 7.4K and has 76 rows

Rec #, Previous, Next, First, Last or Quit:
Filename: dat/states.ost  Record 1
Page: 0, Row: 0
Null Indicators/Data Lengths:
 2/22/2/2/2/4
00000-00015:   41414172 6d656420 466f7263 65732041     AAArmed Forces A
00016-00031:   6d657269 63617300 cdcdcdcd cdcdcdcd     mericas.........
00032-00043:   cdcd2020 41465553 00000000              ..  AFUS....
Rec #, Previous, Next, First, Last or Quit:
Filename: dat/states.ost  Record 2
Page: 0, Row: 1
Null Indicators/Data Lengths:
 2/27/2/2/2/4
00000-00015:   41454172 6d656420 466f7263 65732045     AEArmed Forces E
00016-00031:   75726f70 652f4166 72696361 00cdcdcd     urope/Africa....
00032-00043:   cdcd2020 41465553 00000000              ..  AFUS....
Rec #, Previous, Next, First, Last or Quit:
Filename: dat/states.ost  Record 3
Page: 0, Row: 2
Null Indicators/Data Lengths:
 2/21/2/2/2/4
00000-00015:   41504172 6d656420 466f7263 65732050     APArmed Forces P
00016-00031:   61636966 69630066 72696361 00cdcdcd     acific.frica....
00032-00043:   cdcd2020 41465553 00000000              ..  AFUS....


Additional Resources

See also:

 
Back to top
integration/rawdata/ost/datatypes.txt ยท Last modified: 2016/06/28 22:38 (external edit)