This shows you the differences between two versions of the page.
|
integration:rawdata:ost:datatypes [2015/07/14 17:33] doc |
integration:rawdata:ost:datatypes [2016/06/28 22:38] |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | ~~NOTOC~~ | ||
| - | {{page>:top_add&nofooter&noeditbtn}} | ||
| - | |||
| - | ====== Integration: Raw Data Files ====== | ||
| - | |||
| - | ===== Omnidex Standalone Tables ===== | ||
| - | |||
| - | [[integration:rawdata:ost:home|Overview]] | | ||
| - | [[integration:rawdata:ost:environments|Environments]] | | ||
| - | [[integration:rawdata:ost:options|Options]] | | ||
| - | **[[integration:rawdata:ost:datatypes|Datatypes]]** | | ||
| - | [[integration:rawdata:ost:ofx|Offset Indexes]] | | ||
| - | [[integration:rawdata:ost:export|Exporting]] | | ||
| - | [[integration:rawdata:ost:limitations|Limitations]] | | ||
| - | [[integration:rawdata:ost:example|Example]] | ||
| - | |||
| - | ---- | ||
| - | |||
| - | ==== 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 [[dev:sql:statements:attach|ATTACH]] statement and then using the [[programs:odxsql:commands:show|SHOW TABLE]] statement. | ||
| - | |||
| - | <code> | ||
| - | > 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 | ||
| - | |||
| - | > | ||
| - | </code> | ||
| - | |||
| - | \\ | ||
| - | |||
| - | More detailed information about the Omnidex Standalone Table can be seen using the [[programs:odxsql:commands:dump_ost|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. | ||
| - | |||
| - | <code> | ||
| - | > 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.... | ||
| - | </code> | ||
| - | |||
| - | \\ | ||
| - | |||
| - | ===== ===== | ||
| - | |||
| - | **[[integration:rawdata:ost:options|Prev]]** | | ||
| - | **[[integration:rawdata:ost:ofx|Next]]** | ||
| - | |||
| - | ====== Additional Resources ====== | ||
| - | |||
| - | See also: | ||
| - | |||
| - | {{page>:integration:rawdata:see_also&nofooter&noeditbtn}} | ||
| - | |||
| - | {{page>:bottom_add&nofooter&noeditbtn}} | ||