1 / 37

DBMS: Direct-Access and SQL File Types

DBMS: Direct-Access and SQL File Types. Nico Spence, Chief Marketing Officer Nick Decker, Engineering Supervisor BASIS International Ltd. Overview. BASIS DBMS Direct-result-set navigation and maintenance SQL-result-set navigation and maintenance New DBMS Objects BBjFileInfo

albina
Download Presentation

DBMS: Direct-Access and SQL File Types

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. DBMS: Direct-Access andSQL File Types Nico Spence, Chief Marketing Officer Nick Decker,Engineering Supervisor BASIS International Ltd.

  2. Overview • BASIS DBMS • Direct-result-set navigation and maintenance • SQL-result-set navigation and maintenance • New DBMS Objects • BBjFileInfo • BBjOpenFileInfo • IOLISTS vs. Templates • Extended File Types • DBMS Deployment Options

  3. New DBMS Objects • BBjFileInfo Object • Provides object-oriented way to access file information traditionally retrieved from the FID() and FIN() • Associated with an open channel, so it provides a dynamic view of the file (methods always return current information) • Retrieve information such as • Filename • Filesize • Filetype • Key information • Record information

  4. BBjFileInfo Demonstration

  5. BBjOpenFileInfo • Programmatic access to opened file information • Advisory Locking • Extracted • File name • Locked • Open Type • Read Only • User • ID String • Host Name • Force Close • Documentation Link

  6. BBjOpenFileInfoDemonstration

  7. Extracted Record Demonstration

  8. IOLISTS • IOLISTs - legacy style of input with drawbacks • Code using IOLISTs is less legible and more difficult to maintain 0100 IOLIST A$,B[ALL],C$,D,IOL=200 0200 IOLIST E$,F,G$ • IOLISTs are significantly slower than string templates

  9. String Templates • String templates – modern input style with advantages • Describes a record in a file or SQL result set • Field name • Data type (character, numeric, etc.) • Fixed or variable length • Terminators, padding characters

  10. String Templates • String templates (continued) • Easy access to fields • Required for SQL and SELECT verb access • Significantly faster than IOLISTs • Code is more legible and easier to maintain • Can be retrieved via • SQL • GET TABLE INFO • Text area in VKEYED File

  11. Extended File Types • XKEYED files • JKEYED files • VKEYED files • ESQL Files

  12. XKEYEDFile Type • BBj offers more file types • XKEYED files • Unlimited number of keys • Unlimited number of key segments • Unlimited key length • Smaller than MKEYED files • Faster record iteration

  13. XKEYED Demonstration

  14. XKEYED Demonstration

  15. JKEYEDFile Type • BBj offers more file types (continued) • JKEYED Files (Journaled Files) • All file modifications are: • Atomic – all operations completely succeed or completely fail • Logged to automatically restore a potentially corrupt file to its last known good state

  16. JKEYED File Type • BBj offers more file types (continued) • JKEYED Files (Journaled Files) • Transaction Tracking • Allows the programmer to define a set of operations on multiple files to be treated as a single operation • Allows programmer to commit or rollback the entire operation

  17. JKEYEDFile Type • BBj offers more file types (continued) • JKEYED Files (Journaled Files) • True variable length records • Perfect for BLOBs (Binary Large Objects) to store • Images • Audio • Video • Complete documents

  18. VKEYEDFile Type • BBj offers more file types (continued) • VKEYED Files • True variable length records • Reduced disk usage • Perfect for BLOBs (Binary Large Objects) to store • Images • Audio • Video • Complete documents

  19. VKEYEDFile Type • BBj offers more file types (continued) • VKEYED Files (continued) • Dynamic keys - add, remove, and change keys • Named keys - reduce maintenance coding • Text area - write string templates in the header

  20. VKEYEDFile Type • BBj offers more file types (continued) • VKEYED Files(continued) REM Create aVKEYEDfile REM 64 is the expected average record size erase p_file$,err=*next vkeyed p_file$,[1:1:6],[2:1:3],[3:1:2:"D"],0,64

  21. VKEYEDDetails Demonstration

  22. BBjFileInfo • BBjFileInfo Object (continued) Special VKEYED functionality • Add keys • Remove keys • Change existing keys • Set key names • Set associated text

  23. BASIS DBMS – New File Types

  24. BASIS DBMS – New File Types

  25. BASIS DBMS – New File Types

  26. VKEYEDFile Size Demonstration

  27. VKEYED File Speed Consideration • Disk Speed • Network Speed • File structure • Differences measured in milliseconds

  28. BASIS DBMS – New File Types • BBj offers more file types (continued) • ESQL Files • Exclusively for SQL access; not a BBj file • Created via SQL ‘Create Table’ syntax • Configured in the Database Properties section of the Enterprise Manager

  29. BASIS DBMS – New File Types • BBj offers more file types (continued) • ESQL File Features • Contains dictionary information in the file • Supports variable length records • Supports dynamic index creation • Offers true SQL data types • Numeric values with a precision and scale • Dates & Timestamps • Stored as a native type • No longer require mapping to a column extension

  30. BASIS DBMS – New File Types • BBj offers more file types (continued) • ESQL File Features (continued) • Optimizations • Performance gains • Faster inserts • Faster data retrieval • Able to read a single column instead of always reading a whole row • No longer byte array based

  31. BASIS DBMS – New File Types • BBj offers more file types (continued) • ESQL File Features (continued) • Future enhancement possibilities include • Identity columns (auto increment) • Column and row constraints • Foreign key relations • Not NULL constraint • Default values • Optional Case-Sensitivity • Support for advanced ALTER TABLE syntax

  32. ESQL Demonstration

  33. BBj File Type Comparisons

  34. DBMS Deployment Options • Single-tier • Thin Client • Interpreter • DBMS • Two-tier • Fat Client on different machines • DMS on separate machine • Three-tier • Thin Client on different machines and the Interpreters running on the same machine with the DBMS • Thin Client of different machines connecting to a centralized interpreter server that connects to a separate DBMS server

  35. DBMS RedundantDeployment Options • Load-balanced interpreter servers connecting to: • Storage area network SAN connected to RAID • High-availability servers • Highly Recoverable File System • Journaled File System

  36. Summary • BASIS DBMS • Direct-result-set navigation and maintenance • SQL-result-set navigation and maintenance • New DBMS Objects • BBjFileInfo • BBjOpenFileInfo • IOLISTS vs. Templates • Extended File Types • DBMS Deployment Options

More Related