This manual documents Octo, the YottaDB Database Management System.
Octo is a SQL database engine whose tables are stored in YottaDB global variables (i.e., YottaDB hierarchical key-value nodes). Octo is installed as a YottaDB plugin.
It is quick and efficient in pulling data from the YottaDB datastore.
It is tightly integrated with the YottaDB object technology that allows for a mix of relational as well as object access to the YottaDB datastore seamlessly. It does not sacrifice one for the other.
Octo uses the PostgreSQL wire protocol, allowing SQL access to YottaDB databases via the PostgreSQL ODBC/JDBC/OLE DB driver.
It uses YottaDB local and global variables to hold mapping data, temporary tables, and cross references to provide an efficient relational schema overlay using an augmented SQL DDL language.
It uses a 3-phase architecture, consisting of parsing, logical-plan generation and optimization, and physical-plan generation and emission.
ROcto is the Remote Octo Server that can communicate with PostgreSQL Server clients.
Octo includes a full set of standard, relational features. These include:
The ability to define data structures, especially database schemas (Data Definition Language, or DDL).
The ability to retrieve data (Data Query Language, or DQL).
The ability to insert/update data (Data Manipulation Language, or DML).
Note
At the time of the release of this document, the features that manage transactions in the database (Transaction Control Language, or TCL), and control access to data stored in a database (Data Control Language, or DCL) are yet to be implemented.
YottaDB r1.34 or greater is required for successful installation of Octo.
Installing and configuring YottaDB is described on its own documentation page. With the --octo option of YottaDB's ydbinstall.sh script, you can install YottaDB and Octo with one command.
Note
Octo is a YottaDB application, not an application that runs on the upstream GT.M for which YottaDB is a drop-in upward-compatible replacement.
The YottaDB POSIX and AIM plugins are now installed when the --octo option is used with the ydbinstall script.
Installing the YottaDB encryption plugin enables TLS support (recommended for production installations). You will need to make sure TLS/SSL is enabled for the driver in the client software chosen.
The YottaDB encryption plugin can be installed by adding the --encplugin option when installing YottaDB with the ydbinstall script:
./ydbinstall--encplugin
Note
If YottaDB has already been installed, use the --plugins-only option with the ydbinstall.sh script to install the plugins.
Octo is a continuously updated YottaDB plugin that is distributed as source code. A CI (Continuous Integration) pipeline runs a considerable number of unit and system tests before allowing any source code to be merged. This ensures that the master branch is always current with the latest production-ready source code.
Octo can be installed by using the --octo option when installing YottaDB with the ydbinstall script.
To do this, start by installing the prerequisite packages:
# Ubuntu
sudoaptupdate&&sudoaptinstall-y--no-install-recommendsbisonbuild-essentialca-certificatescmakecurlfileflexgitlibconfig-devlibelf-devlibicu-devlibreadline-devlibssl-devpkg-configwget
# Rocky Linux
sudoyum--enablerepo=powertoolsinstall-ybisoncmakefilefindutilsflexgccgitlibconfig-devellibicu-develmakeopenssl-develpkg-configpostgresqlprocpsreadline-develwget
# RHEL 8
sudosubscription-managerrepos--enablecodeready-builder-for-rhel-8-x86_64-rpms
sudoyuminstall-ybisoncmakefilefindutilsflexgccgitlibconfig-devellibicu-develmakeopenssl-develpkg-configpostgresqlprocpsreadline-develwget
# SUSE Enterprise Linux / openSUSE Linux
zypperinstall-ybisoncmakefilefindutilsflexgccgitlibconfig-devellibicu-devellibopenssl-develmakepkg-configpostgresqlprocpsreadline-develwget
Then, install YottaDB, Octo, and the required POSIX plugin all together:
The following environment variables must be set for Octo to operate properly:
ydb_dist
ydb_gbldir
ydb_routines
ydb_xc_ydbposix
ydb_xc_octo
The environment variables ydb_dist, ydb_gbldir, ydb_routines, and ydb_xc_ydbposix can initially be set by sourcing ydb_env_set in your YottaDB installation directory.
Example setting of the environment variables (assuming default paths):
source/usr/local/lib/yottadb/r1.34/ydb_env_set
exportydb_xc_octo=$ydb_dist/plugin/octo/ydbocto.xc# Allow usage of some SQL functions, e.g. DATE_FORMAT()
There is no need to create databases manually if ydb_env_set has been sourced.
Octo uses several global variables for its operation, which start with %ydbocto and %ydbAIM. The %ydbAIM globals are intended to be ephemeral and are not recommended to be journaled; we also recommend that you use a memory mapped region with 2K blocks. Use GDE to map %ydbocto* and %ydbAIM global variables to a separate region. Global variables used by Octo and AIM must have NULL_SUBSCRIPTS=ALWAYS.
The following example creates an OCTO database region with the recommended setting in the $ydb_dir/$ydb_rel/g directory and assumes an existing application global directory at $ydb_dir/$ydb_rel/g/yottadb.gld. For more information on setting up a database in YottaDB, refer to the Administration and Operations Guide, and the YottaDB Acculturation Guide for self-paced exercises on YottaDB DevOps.
You can use the Northwind sample database to get started. The dummy data set can be found in the tests/fixtures subdirectory of the YDBOcto repository created by gitclonehttps://gitlab.com/YottaDB/DBMS/YDBOcto.git.
A dummy data set consists of a .zwr file and a .sql file. The former contains the actual data to be stored in YottaDB, while the latter contains a schema that maps relational SQL structures (tables and columns) to the NoSQL data contained in YottaDB. Assuming that /tmp/YDBOcto is the directory from the gitclonehttps://gitlab.com/YottaDB/DBMS/YDBOcto.git command :
# Source ydb_* variables:source/usr/local/etc/ydb_env_set
# ydb_dir can optionally be set to use a location other than $HOME/.yottadb for the working environment.
mupipload/tmp/YDBOcto/tests/fixtures/northwind.zwr
octo-f/tmp/YDBOcto/tests/fixtures/northwind.sql
Once loaded, you can run octo to start the Octo interactive shell and use SELECT queries to access the data.
The following query selects the first five records from the 'nwCustomers' table where the country is 'France'.
OCTO>SELECT*FROMnwCustomersOCTO>WHERECountry='France'OCTO>LIMIT5;7|Blondel père et fils|Frédérique Citeaux|24, place Kléber|Strasbourg|67000|France9|Bon app'|Laurence Lebihans|12, rue des Bouchers|Marseille|13008|France18|Du monde entier|Janine Labrune|67, rue des Cinquante Otages|Nantes|44000|France23|Folies gourmandes|Martine Rancé|184, chaussée de Tournai|Lille|59000|France26|France restauration|Carine Schmitt|54, rue Royale|Nantes|44000|France
The following query selects all products from the 'Products' table with a ProductName that starts with 'L'.
OCTO>SELECT*FROMProductsOCTO>WHEREProductNameLIKE'L%';65|Louisiana Fiery Hot Pepper Sauce|2|2|32 - 8 oz bottles|21.0566|Louisiana Hot Spiced Okra|2|2|24 - 8 oz jars|1767|Laughing Lumberjack Lager|16|1|24 - 12 oz bottles|1474|Longlife Tofu|4|7|5 kg pkg.|1076|Lakkalikööri|23|1|500 ml |18
The following query displays the average price of Products per Category.
The northwind data set can also be queried using ROcto (Remote Octo server).
SQuirreLSQL needs to be configured in order to use ROcto.
An alias needs to be created, including the server IP address and port number.
For example:
jdbc:postgresql://localhost:1337/
A username and password should also be added to the alias.
This username and password combination must first be added to Octo using the ydboctoAdmin utility:
Before running Octo/ROcto make sure that the required YottaDB variables are set either by creating your own script or running source$ydb_dist/ydb_env_set.
To use the command-line SQL interpreter run $ydb_dist/plugin/bin/octo.
To use the PostgreSQL protocol compatible server run $ydb_dist/plugin/bin/rocto.
If you use the octo command line interpreter, history is stored by default in ~/.octo_history. More information is provided in the History document.
The verbose option specifies the amount of additional information that is provided to the user when commands are run in Octo.
--verbose={number}
or equivalently,
-v{v{v}}
The number given to the option corresponds to the following levels:
Number
Level
Information
0
ERROR
Information about all errors
1
INFO
Additional information useful to log
2
DEBUG
Includes information useful for debugging
3
TRACE
Information logged stepping through actions
When a number level is specified, the verbose output contains all information corresponding to that level as well as the previous levels.
The default verbose level is set to zero(0) (ERROR).
A single -v in the command line puts the verbose level at one(1) (INFO), -vv puts the level at two(2) (DEBUG), and -vvv puts the level at three(3) (TRACE).
The dry-run option runs the parser, and performs checks and verifications on data types and syntax, but does not execute the SQL statements. The database is not altered when Octo is run with the --dry-run option.
The emulate option allows the user to specify which SQL database Octo should emulate. Database names should be in all caps. Currently supported emulations are MYSQL and POSTGRES. If you wish to emulate MariaDB, choose MYSQL.
Specifying the -p or --print-sql-query command line flag/option in the octo command line causes every sql query (e.g. SELECT query, INSERT command, CREATE TABLE command etc.) to be printed before displaying the result of that particular query when used with octo-f` or octo<inputfile.
The query line is prefixed with OCTO> just like one would see if one entered the query at the OCTO> prompt.
Note that the -p flag/option has a different meaning for the rocto executable (to specify a port number). The query printing option only works with octo.
octo-f/--input-file exits with a non-zero status if at least one query it ran encountered an error. octo invocation without -f/--input-file or rocto are not affected.
