Version V8.4
Copyright © 2022 Transaction Software GmbH
ALL RIGHTS RESERVED.
While every precaution has been taken in the preparation of this document, the publisher assumes no responsibility for errors or omissions, or for damages resulting from the use of the information contained herein.
2022-11-30
Table of Contents
Transbase is enhanced with procedural elements, functions and procedures according to the SQL/PSM standard. They are seamlessly integrated into the TBSQL language.
For details refer to the TB/SQL Guide.
Stored procedures and user-defined functions written in Java or C (as supported in former versions of Transbase) are discontinued. They need to be rewritten as PSM procedures or functions or applied to data in the database using external application programs.
As a consequence the JDBCReader
function is no longer available.
This new trace event category applic
allows that application messages are recorded in the database trace file.
With the TCI function TCITraceApplication
an application can send messages to the database that are stored
in the database trace if the trace event APPLIC
is set.
A new database property lock_mode
sets the default for the corresponding session property.
Valid modes are pages
, tables
and mixed
.
The default is tables
for new databases an mixed
for database migrated from previous versions.
If a password is set for the tbadmin
login of a database
a request to drop this database needs to be specified as IMMEDIATE
.
drop database <dbname> immediate
Crowd queries are used to distribute queries across multiple databases, which may reside on different machines. The corresponding query results are automatically collected from all databases and transferred back to the node, which triggered the query.
A Description of the overall concept and detailed explanations can be found in the Crowd Queries manual.
Instead of starting a new thread for each connection, a thread of a thread pool is only assigned to a connection if the connection has an active transaction.
Database encryption mode AES_256_CBC supports hardware acceleration (AES instruction set), which improves the speed of disk encryption and increases the robustness against side-channel attacks.
For database environments which both are prone to power failures and have requirements concerning an upperbound of restart time, a FLUSH DATABASE statement has been implemented. It can be used like any other SQL command and triggers writing all changed data blocks from the buffer pool to files as well as writing the recovery log. Periodic launch of FLUSH DATABASE minimizes the boot time after power failure. Note, however, that the absence of these calls does not compromize the complete restauration and recovery of completed transactions after failure.
Numerical values are stored in disk files either in big endian or little endian format, depending on the CPU of the host system. The EXPORT DATABASE statement can be used to make a copy of all diskfiles in the desired format to provide proper diskfiles for another platform.
Tables whose data blocks reside in the main memory buffer pool exhibit better performance because no I/O operation is needed for access. An INMEMORY specification has been implemented to enforce that property either at creation time or at arbitrary times via an ALTER TABLE statement (the latter also provides for removing the buffer residence). A PRELOAD option also enables the loading of tables at boottime already.
Specifying many tables with the INMEMORY option requires the database buffer (cache) to be configured with appropriate size. SQL Views "systable_space" and "sysindex_space" have been installed to query for space occupation of tables and indexes.
The FIRST(n) clause (restricting the result of a SQL cursor to te first n result tuples) has been extended to accept a value of n which may be specified as the result of an SQL expression or of a parameter.
SET functions usable in GROUP and WINDOWS functions have been extended by variance and standard deviation functionality. Variance and standard deviation on population as well as sample population now are available.
One of the configuration parameters for creation of a database is the blocksize of the data pages. It is advantageous to choose the blocksize in harmony with the physical I/O size of the underlying file system. I/O also occurs on the logfiles which are written for recovery reasons. As the logfiles might reside on a different media with physical I/O unit also different from that of the data files, an independent configuration parameter for the blocking of logfiles has been realized.
In addition to formats DSV (Delimiter Separated Values) and XML (eXtensible Markup Language), Transbase® now supports data import and export (Transbase® Spooler) from/to files formatted as JSON (JavaScript Object Notation).
Transbase® is now available on the following platforms:
Windows (x86-64 x86)
Linux (x86-64 x86 ARM64 ARM PPC)
QNX (x86-64 x86 ARM64 ARM)
HPUX (IA‑64)
Solaris (SPARC64)
FreeBSD (x86-64 x86)
NetBSD (x86-64 x86)
OpenBSD (x86-64 x86)
macOS (x86-64)
iOS (x86-64 ARM64)
Android (x86-64 x86 ARM64 ARM)
Transbase® server has been implemented as a single process. For each database connection a new thread is created within the scope of this process. Shared memories and global semaphores have been replaced by main memory and mutex’s.
While public databases are served by a server process and can be accessed from remote clients, private databases can only be accessed locally.
They are identified via their homepath and can only be accessed from one application at a time. Private databases support multithreading, multiple connects, distributed queries and may be created by replication from a master database.
Databases can now be administrated via SQL like commands on an administration database admin that is available on each database server by default:
CREATE DATABASE dbname SET ENCRYPTION=RIJNDAEL_256, CONNECTION_LIMIT=100;
All relevant database configuration parameters can be reviewed with the following query:
SELECT * FROM sysdatabases WHERE database_name='dbname';
This allows the remote administration of databases.
The previously used command line tools 'tbadmin','tbmkrom' and 'tbrepl' are no longer needed and thus no longer available.
An Objective-C driver (Transbase.framework) is now available for macOS and iOS. This framework can also be imported directly into any Swift file.
Transbase® now supports SSL (TLS 1.0, 1.1 and 1.2) encryption for client–server communication. Explicit SSL certificates may be used on serverside and clients can optionally verify their integrity.
Transbase® now fully supports IPv6.
INSERT statements can now be extended with a RETURNING clause as shown for the a simple case:
INSERT INTO table (fieldname,...) VALUES (expr,...) RETURNING (expr1,expr2,...)
This produces one result record for each record inserted. It is particularily useful in cases where database-generated default values need to be retrieved, e.g. unique ids produced with the help of AUTO_INCREMENT fields or sequences.
Example:
INSERT INTO person (lastname, firstnames) VALUES (?, ?) RETURNING ( pid )
This SQL function delivers the time of the last successful commit of an update transction. It is also available as a function addressing a remote database.
Some variants of the standard INSERT statement have been introduced to handle key collisions in a special way. "INSERT OR IGNORE"" simply suppresses a corresponding error message. The other two variants are a simplified version of the more complex MERGE statement and turn the INSERT into an UPDATE in case of a key collision.
Secondary Indexes can be built on an expression of various fields instead on a simple field name. They can be used in a query by the query optimizer if the defining expression occurs in the WHERE Clause of a query.
A SOUND index is a special Functional Secondary Index. The defining expression in this case consists on a new function SOUND on a field like a person's name which maps strings to another string representing the sound of the name. Names which sound similarily can thus be found with a corresponding search argument.
SELECT queries with moderate result size run considerably faster as the number of roundtrips between the application and Transbase has been reduced.
Transbase is now available on Raspberry Pi, armhf (ARM hard float) and armel. Due to platform requirements database configuration defaults are set to appropriate values.
Note | |
---|---|
Cachesizes can be altered after database creation while pagesize must be set at database creation time. |
armel/armhf/raspberryPi others SharedCache(MB) 16 64 PrivateCache(KB) 256 2048 Pagesize(KB) 4 8
TransbaseFree is also available for new platforms Raspberry Pi, armhf (ARM hard float) and armel. Due to platform requirements database configuration values are restricted to appropriate values.
Note | |
---|---|
Due to TransbaseFree restrictions these parameters are not configurable. |
armel/armhf/raspberryPi others SharedCache(MB) 1 4 PrivateCache(KB) 256 2048 Pagesize(KB) 4 8 Diskfilesize(GB) 64 500
The SafeIO configuration controls how often the transaction log is flushed to disk.
Since log flush may be a lengthy operation, this parameter has significant impact on performance for update transactions.
The default value is 0 which means that the log is flushed upon commit of each update transaction. Thus committed transactions are always guaranteed to be persistent, no committed transaction will be lost due to a machine crash.
Additionally Transbase will perform a log flush on demand in order to guarantee the consistency of disk files. A log flush on demand will happen very seldom for large data caches and for short transactions. It will happen more often when long transactions are processed or when data cache is small.
A value of N=1 means that the log is not flushed upon commit of each transaction but on demand to avoid data corruption.
This setting means that transactions may be lost if committed shortly before a crash. Performance for update transactions may be improved significantly.
A value of N=2 means that the log is never flushed. The IO system must compensate this behaviour to avoid data corruption.
The specification of the SafeIO parameter can be given on the command line with the "safeio=.." option. For example:
CREATE DATABASE test SET safeio=2
creates a DB where the log is never flushed by Transbase.
An AUTO_INCREMENT field serves to generate unique key values. At most one AUTO_INCREMENT field is allowed in a table. Its data type must be one of TINYINT, SMALLINT, INTEGER, BIGINT.
CREATE TABLE T ( Id BIGINT AUTO_INCREMENT, Prop1 VARCHAR(*), Prop2 VARCHAR(*), PRIMARY KEY (Id) )
In table T, Id is the only key field. For each INSERT statement which does not assign a value for Id but uses a fieldlist (Prop1,Prop2), a unique value for the field Id automatically is generated. Value generation starts with the value 1.
An AUTO_INCREMENT field always must be specified either as the (single) PRIMARY KEY or as the lowest weighted component of a compound key. A start value may optionally be specified.
CREATE TABLE person ( FirstName VARCHAR(*) NOT NULL, SecondName VARCHAR(*) NOT NULL, Id INTEGER AUTO_INCREMENT=10, Birthday Date, .... PRIMARY KEY (FirstName, SecondName, Id) )
A schema is a container for a set of database objects such as tables, views or triggers. This feature offers the possibility to organize database objects and let users have objects with same identifiers.
Note | |
---|---|
Archives which were created by tbarc, tbtar or tbtape of older Transbase releases are not compatible with this Transbase version. |
The ALTER TABLE statement was revised to add, modify or drop more than one field within a statement. Furthermore it is now possible to rename an attribute or the table itself or to change the position of an attribute within the table.
Dataspaces now can be created with an AUTOEXTEND option. An AUTOEXTEND dataspace automatically is extended by a new file whenever it runs out of space. The size of the added file is specified within the AUTOEXTEND clause. For existing dataspaces, the AUTOEXTEND option can be modified, released or added by the AlterDataspaceStatement.
Files which are added to an existing dataspace may be specified with the LOBCONTAINER option. With this option, the new file is dedicated to BLOBs and CLOBs objects, i.e. no other pages than those of BLOB abd CLOB objects are stored in that file. If the dataspace has the AUTOEXTEND option and has at least one LOBCONTAINER file, then the AUTOEXTEND mechanism automatically adds a file with the correct type depending on whether space has run out for LOB or for non-LOB objects.
The development of CD/DVD databases has been simplified. The concept of Editorial Database has been removed. Romfiles now are produced directly from a Standard Database. Nevertheless, if the database to be flushed has received many updates and structural changes, it is still recommended to rebuild the complete database in a new (standard) database using tbarc or tbtar. The reason is to guarantee optimal adjacency and minimal space requirements in the data which then is also reflected in the romfiles.
The concept of "cluster" on an Editorial Database served to partition the data into several romfiles. Furthermore, the option "extsize=space" in the tbmkrom command served to split one big romfile into several smaller romfiles. Both concepts have been removed and must be replaced by using the Dataspace concept oin the standard database. Each dataspace in the Standard Database automatically is flushed into a separate romfile.
Furthermore, if a dataspace consists of more than one single file then each of these files becomes a separate romfile. As each dataspace can dynamically be extended, it is no more required to reserve enough space in the romfile address space at database creation time (as it has been necessary at creation time of the Editorial via parameter "Rom Size" or "rs=space" on the command line).
The Transbase multiplexing service was completely reworked to provide better performance and use less resources.
Now a slave database can also act as a master database for other slaves.
Semi-synchronous and synchronous replication to improve fail-safety and load balancing.
If disk recovery is switched on for a database, the expiration of the logfiles can be set via a new option. The logfiles are removed after the chosen amount of days, if they are no longer needed for transaction recovery. If omitted, expiration defaults to unlimited.
Transbase is now available on Mac OS X (requires 10.5 or later).
The CLOB datatype is used for storing large character data.
The concept of dataspace has been introduced to control the distribution of data on the database files. A dataspace can be created and then specified on the creation of tables. Each database file ("diskfile") is coupled to exactly one dataspace. Dataspaces can be extended arbitrarily by adding new diskfiles. A table, all its secondary indexes and its BLOB data are stored in diskfiles of the corresponding dataspace. Dataspaces can be set offline or online by the administrator.
The SQL language has been extended by the WITH Clause. This is a mechanism to create temporary tables as intermediate results for queries which have identical subqueries. By this feature, queries with common subqueries can be written more compact and run faster.
The GROUP BY Clause now may contain arbitrarily complex expressions and thus is no more restricted to single fields.
The FIRST Clause in SELECT queries has been extended by a mechanism to choose an arbitrary interval of the result set. Additionally it can also be used in subqueries when provided with an ORDER BY Clause.
Now sequences are of datatype BIGINT instead of INTEGER.
Mutual exclusion of processes and threads by semaphores has considerably been refined. Parallel access to the database buffers residing in different shared memory portions now is possible. The parallelism is enabled by an appropriate configuration of the "Data Caches" entry in tbadmin when a database is created or altered (see tbadmin documantation).
The flat table got an index part for loading pages in advance.
Transbase Replication provides functionality for creating copies of databases, which are frequently updated to ensure consistency between the original and the copies.
Transbase Database grids offers the ability to distribute queries for a (logical) database across a set of physical databases.
The tunnel feature of the Transbase multiplexing service replaces the firewall port window settings.
If a Transbase kernel error happens or if the database runs out of space, an administrator will be informed via email.
A new UPDATE statement has been introduced which can be used to adjust an existing table in the database with the result of a SELECT statement.
This statement will apply only minimal changes to the physical layout of the table, as opposed to the conventional tbdiff procedure. Thereby it can be ideally combined with Transbase CD for generating minimal delta ROM files.
The syntax of this command is: UPDATE <table> FROM <SELECT_Statement>
After the UPDATE statement is successfully performed the specified table contains exactly the records retrieved from the <SELECT_Statement>.
The UPDATE statement can be combined with remote access features of Transbase, e.g. Transbase D, table functions JDBCReader() and OraReader(), or direct access to flat files. The following examples illustrate some possibilities:
UPDATE t FROM t@sourcedb; UPDATE t FROM SELECT * FROM FUNCTION OraReader('//orasrv/oradb', 'scott', 'tiger', 'SELECT * FROM t'); CREATE FILE ('t.spl') TABLE tsrc (<Columns_description>); UPDATE t FROM tsrc;
Version 6.8 supports a new data type BIGINT. This type allows integer values with a size of 64 bits (including the sign).
A view definition may contain table names, referring to a tables residing in a remote database, accessible over Transbase D. When evaluating remote views, the privileges of the view owner apply for accessing remote tables. The current user must have at least ACCESS privilege for the remote database.
CREATE OR REPLACE VIEW r AS SELECT a,b,c FROM t@remotedb;
In Version 6.7, Window functions were introduced in Transbase. Now they have been extended offering powerful analytic capabilities including sliding windows with constant and dynamic offset, specified as absolute row offsets or as relative value offsets.
The number of replacements of tuples in the leaf nodes of the B-Tree within one page caused by inserts, updates, and deletes are now decreased. This leads to a higher performance of writing transactions of data bases with logging mechanisms. Furthermore, the delta iteration process of Transbase CD data bases is optimized.
The overall scalability of parallel query processing which was introduced in version 6.7 was improved. Synchronization primitives have been optimized for highly contagious tasks with many parallel threads of execution. The parallelization of very short running queries is more graceful.
The Transbase JDBC driver was restructured now yielding a significantly reduced code basis and improved performance. JDBC and JDBCX modules were repackaged into one centralized jar file. This jar is now available in two versions, as faster release build without debug and logging capabilities and as separate debug build with logging and additional debug information.
Delta Management deals with production and delivery of updates referring to a retrieval database stored on CD/DVD. Up to Transbase® Version 6.7, updates could be realized by delta romfiles which can be atached togetherwith the original romfiles at client site.
With Transbase® Version 6.8, so called "transitional romfiles" realize a new update technique which avoids the drawback of increasing data transfer when updates are produced and delivered iteratively. Thereby, client data updates can be realized in higher frequency and more economically.
In previous Transbase versions tables were typically created with internal key (IK) access path. This IK access path requires additional space of 6 to 8 bytes per tuple. It is used as row identifier, e.g. for referencing tuples in the base relation after accessing secondary indexes. Tables could be created explicitly WITHOUT IKACCESS to save space, but this inhibited the creation of secondary indexes on these tables.
Now it is possible to create secondary indexes on IK-less tables. In this case the base table's primary key is stored in all index tuples for referencing the base relation. This leads to a notable space reduction in many cases.
As the primary key can be rather extensive, Transbase will decide at table creation time whether to create a table WITH or WITHOUT IKACCESS. This guarantees optimal space efficiency. If the primary key occupies no more that the IK, then the table is created WITHOUT IKACCESS. Else an IK access path is added by default.
It is always possible to override this default mechanism by adding WITH or WITHOUT IKACCESS to the create table statement.
Flat tables are a new form of base relations. They store data in input order and do not have a primary key. Therefore they allow faster data loading. If a flat table was created WITH IKACCESS it is possible to add secondary indexes for efficient lookups. Typically secondary indexes are added once the load process is complete, so load performance is not compromised.
In addition, Flat Tables can be restricted to occupy no more than a certain maximum of space. If this maximum is exceeded, the oldest data will be replaced.
Thus Flat Tables are ideally suited for data staging during bulk load processes, as temporary storage and for logging facilities.
For details see the Transbase SQL Reference Manual .
Bitmap indexes have been introduced as a new form of secondary indexes. They are preferably used for non-selective columns having few different values (e.g. classifications). Bitmap indexes are innately very space efficient. With their additional compression in average they occupy less than on bit per index row. A bitmap index can be created on any base relation (B-Tree or Flat) having a single INTEGER field as primary key or an IKACCESS path.
Bitmap processing allows inexpensive calculation of logical combinations (AND/ OR/ NOT) of restrictions on multiple non-selective fields using bitmap intersection and unification.
For details see the Transbase SQL Reference Manual .
Tables receiving many random INSERTs, UPDATEs or DELETEs suffer a fragmentation of their logical page ordering. This can slow down sequential scans significantly.
By using the ALTER TABLE ... MOVE statement those tables can be reorganized with optimal page adjacency. During this process the database remains online and accessible.
For details see the Transbase SQL Reference Manual .
This new SQL command allows tuples to be inserted or updated within existing tables depending on user-specified conditions.
For details see the Transbase SQL Reference Manual .
In previous Transbase versions the datatype NUMERIC(p,s), s and p had to satisfy the equation 0<=s<=p<=30; it covered numbers having at least one and at most 30 digits and optionally up to s digits after the decimal point.
This datatype NUMERIC(p,s) has been generalized as follows: For the precision 0<=p<=30 still holds; NUMERICs with precision 0 are always 0, independently of their scale.
The scale s is restricted to the range -127<=s<=127.
A positive scale means the maximum number of digits after the decimal point. A negative scale analogously means a representation in tens, hundreds, thousands etc.
(p,s) may be omitted completely (denoted simply by NUMBER or NUMERIC(*,*)) to specify numbers having at most 30 digits with arbitrary scale.
The arithmetic routines of NUMERIC automatically round results to the maximum precision of 30 digits if necessary. Overflow happens if the scale becomes greater than 127 or less than -127.
Window functions have been introduced as extension to the SQL standard aggregation functions. They allow aggregation over a sliding window on a result set. This offers the possibility to calculate running totals, sliding averages and rankings over record sets. All aggregation functions (AVG, COUNT, MAX, MIN, SUM) are supported and additional RANK and DENSE_RANK functions have been added. The syntax and semantics follow the SQL 2003 specification.
The calculation of a running total and sliding average per deptno is expressed in the following
SELECT deptno, SUM(salary) OVER (PARTITION BY deptno ORDER BY emp_id), AVG(salary) OVER (PARTITION BY deptno ORDER BY emp_id) FROM employees
The details on this feature can be found in the Transbase SQL Reference Manual .
The implementation of Transbase bulk loading via the SPOOL command has been improved:
The input data is first sorted on the primary key of the destination table (if necessary), then the primary table is built. This guarantees optimal adjacency of base relation tuples. In parallel, for each secondary index, the tuples are stored temporarily.
When the primary table is built completely, each secondary index is built up one by one:
sort tuples into secondary key order
build up the index
As a result, the adjacency of pages is optimal for sequential scans on the table and all indexes.
The Transbase SPOOL command has been modified to support both delimiter separated files and XML structured files.
For details see the Transbase SQL Reference Manual .
Data stored in files may now integrated into the database schema as virtual tables. These FILE tables offer read-only access to those files via SQL commands. They can be used throughout SQL SELECT statements like any other base relation.
CREATE FILE (/usr/temp/data.csv) TABLE file_table WITHOUT IKACCESS (a INTEGER, b CHAR(*)) SELECT a+10, upper(b) from file_table SELECT b FROM file_table, regular_table WHERE file_table.a=regular_table.a
FILE tables are primary designed as an advanced instrument for bulk loading data into Transbase and applying arbitrary SQL transformations at the same time.
For details see the Transbase SQL Reference Manual .
A built-in table function JDBCReader provides read-only access to arbitrary JDBC data sources. The function may be used throughout SQL SELECT statements like any other base relation.
It requires four CHAR(*) parameters.
a JDBC connection string, i.e.
'jdbc:protocolname://hostname[:port][/dbname]'
the user name,
the password,
an arbitrary SQL SELECT query, e.g.
'SELECT x,y FROM S,T where S.a=T.a AND s.b IS NUT NULL'
It can be used for querying remote JDBC data sources or for data import.
INSERT INTO T SELECT * FROM FUNCTION JDBCReader('conn_string','user','passwd', 'SELECT * FROM jdbc_table')
The data retrieved from the external data source is converted into Transbase datatypes for further processing. Data types are mapped appropriately. Data truncation may happen at this point if the target data types is smaller in range. A few data types cannot be mapped; those data types should be converted on the remote site into mappable data types. BLOB data is currently not supported.
The appropriate JDBC driver for the designated JDBC data source has to be provided.
Similar to the JDBCReader, the OraReader provides read-only access to remote Oracle databases. For maximum efficiency, the function is implemented via a dynamic link library (in C programming language) using the OCI interface to access Oracle. Thus it will outperform the JDBCReader on Oracle data sources. The function may be used throughout SQL SELECT statements just like any other base relation.
It requires four CHAR parameters:
the name of the data source, e.g.
'//hostname:[port][/servicename]',
the user name,
the password,
an arbitrary Oracle SELECT query, e.g. 'SELECT * FROM ora_table'
It can be used for querying remote Oracle data sources for data import.
INSERT INTO T SELECT * FROM FUNCTION OraReader('//orasrv/oradb','scott','tiger', 'SELECT * FROM ora_table')
The data retrieved from Oracle is converted into Transbase datatypes for further processing. A few Oracle data types cannot be mapped; those data types should be converted within Oracle into mappable data types. In contrast to the JDBCReader, the OraReader also supports BLOB data.
An Oracle client installation is required on the Transbase host. Because of its ease of handling, the Oracle Instant client is recommended over the standard Oracle client installation for this purpose. The software is freely available from Oracle. Its version should match the version of the Oracle database server. On Windows Platforms, please make sure that the PATH environment variable includes the Oracle client before the Transbase service is started. For Unix platforms, the LD_LIBRARY_PATH variable must be set and include the Oracle client before the Transbase service is started. Also other Oracle environment variables should be set before Transbase is started, including ORACLE_HOME etc.
Transbase will split the calculation of complex and long running queries into several tasks and run these tasks in parallel using multiple threads of execution. This feature might also improve queries on single processor machines, but it is primarily intended for multiple-CPU computers.
By default, parallelization is switched off. It can be activated for a database with the tbadmin tool, by setting an option to the desired level of parallelization. The options are:
mt=max activates the full potential of multithreading; it establishes data pipelines in query plans that run in parallel, also using out-of-order execution, for improved overall performance.
mt=det offers fair parallelism while producing result sets in deterministic output order. Performance is likely to suffer somewhat compared to maximum parallelism, as data pipelines operate only in first-in, first out mode.
mt=min is a rather defensive strategy of parallel query execution; parallel execution is limited to I/O relevant nodes (e.g. REL or REMOTE) and activates work-ahead for the entire SELECT query,
mt=off means no parallelization at all (default),
Alternatively the setting can be manipulated on session level using a TBMODE statement similar to the tbadmin command:
TBMODE MULTITHREAD {MAX | DETERMINISTIC | MIN | OFF}
Note | |
---|---|
Without multithreading Transbase guarantees that data is always processed and returned in the same deterministic sort order, even if no ORDER BY was specified. I.e. the same query produced always the same result in the same order. The SQL specification does not demand any reproducible sort order if no ORDER BY is used. With multithreaded query processing switched to MAX it now likely that data is processed out-of-order. Thus a query will return the same result but possibly in different order if no ORDER BY is specified. Only the specification of an ORDER BY guarantees a result sort order. For supporting legacy applications while still enabling multithreaded query execution Transbase supports multithreading in DETERMINISTIC mode. This mode offers a fair degree of parallelism while conserving the behaviour of single threaded query evaluation. |
The transaction processing in multi-user environments has been improved by minimizing the time while the database is exclusively locked at the end of a transaction.
In particular, the I/O phases needed to write through committed pages or to write back aborted pages into the diskfile are non-exclusive.
The TCI interface is introduced to replace the TBX interface. TCI is thread safe and uses handles to internally synchronized data structures. Handles are provided in an object-oriented manner, e.g. for
environment objects,
database connection objects,
transaction objects,
statement objects,
query result objects,
error objects.
TCI is completely described in a separate manual Transbase Call Interface (TCI) .
Most of the database driver such as: ODBC, OleDb, .net, PHP internally use the TCI interface. Therefore theie implementation is also thread safe.
The Transbase kernel has been extended to provide an autocommit mode for transaction processing. This means that applications do not need to control transactions explicitly. Each cursor is processed in its own (separated) transaction which is closed with the cursor.
In particular, INSERT, DELETE and UPDATE statements are committed immediately.
Autocommit transactions are provided by TCI by setting environment or database properties. Driver APIs (including JDBC) use this kernel feature to implement autocommit transactions.
The command tbmkrom has been modified to produce incremental ROM files in a more compact form; in former versions, a modified page was compressed and then provided as part of the incremental ROM file. At client side, the page in the incremental ROM file replaced the original page of the ROM file.
This mechanism can be improved for pages that receive only minimal changes (e.g. a single tuple or a single value has changed). In such cases, a so-called XOR page is computed which compresses much better. At client side, XOR pages can be used to re-construct the modified page by applying this XOR page to the original page. Depending on the compressed XOR size, the decision is made for each page whether XOR pages are used or complete pages.
While this technique may save space significantly, it makes page access to modified pages a little bit slower at client side.
The command syntax has been extended as follows:
tbmkrom [-f] <database> [incremental [xor]]
The Transbase setup procedure has been modified for Windows Vista. In particular, Transbase installs into two different directories c:\Program Files\Transbase and c:\ProgramData\Transbase.
Program files that are read-only are placed in the first directory, while other installation files (such as dblist.ini) which are read and written are placed in the second directory. Make sure that the second directory provides read and write access to all Transbase users.
In addition, all Transbase users must be granted the explicit privilege to create and access global shared memories. We recommend that this privilege should be granted to a group which every Transbase user should belong to.
See details in our technical note on Windows Vista, which contains particular advice for older Transbase versions, too.
dump and restore functionality has been added to tbadmin library.
In addition to the SearchCondition in the WHERE clause hierarchical data can be queried using HierarchicalSearchConditions. Here a depth-first search is carried out starting with one of the root rows that satisfies the START WITH predicate. For this root row the first child rows satisfying the CONNECT BY condition is selected. Then the hierarchical search will proceed down through the generations of child rows until no more matching rows are found.
ORDER BY accepts expressions. The elements in an ORDER BY are no more restricted to simple fields but may contain any expressions composed of constants, fields, functions and subqueries. The denoted fields must have a resolution either against fields in the corresponding SELECT list or to fields resolvable by the corresponding FROM clause or (if occurring in subqueries) by the surrounding query blocks.
ORDER BY is accepted in inner blocks now. An ORDER BY in a subquery of a query syntactically is accepted. However, it is not guaranteed that such an ORDER BY has an effect on the ordering of the overall query result.
TRUNCATE <table> is equivalent to DELETE FROM <table>.
Support for query evalutation plans including sampling of elapsed times can by enabled by a tbmode statement. Support is enabled by tbmode profiles on and disabled by tbmode profiles off.
Transbase now supports large filesystems. Actually LFS is supported on Linux Solaris and Windows 2K/XP/2003 platforms. Only database diskfiles may be configured bigger than 2GB, all other files still are limited by 2GB.
The maximum diskfile size is limited by the filesystem.
Alternatively to the Full Dump a Differential Dump and Recovery method is provided. For differential dumping an initial dump consisting of diskfiles and logfiles is required. Afterwards only logfile changes since the last dump are moved to a safe place, making dumping in short intervals swifter. The database remains operational while the dump is performed.
The following command produces an initial dump into a directory 'dumpdir':
tbadmin -drec dump db=<dbname> dir=<dumpdir>
Whereas a differential dump is made with:
tbadmin -drec dump diff[erential] db=<dbname> dir=<dumpdir>
sets default codepage for spool statements. <codepagespec> is one of:
PROPRIET UTF8 SJIS EUC UCS UCS2 UCS2LE UCS2BE UCS4 UCS4LE UCS4BE 'locale string (valid on server)'
where UCS/UCS2 are mapped to UCS2LE and UCS4 is mapped to UCS4LE.
more codepage conversions supported. <codepagespec> is one of:
PROPRIET UTF8 SJIS EUC UCS UCS2 UCS2LE UCS2BE UCS4 UCS4LE UCS4BE 'locale string (valid on server)'
where UCS, UCS2 and UCS4 to the server supported little or big endian.
Loads all tables in cluster <i> with default algorithm. In cases where romfiles reside on CD-ROM it is useful to have a DEFAULT LOADing which is restricted to all tables/indexes of a certain cluster. This command replaces a sequence of LOAD commands for all tables/indexes of a cluster.
A ROUND function is provided in the form ROUND( <expression> [ , <scale> ) which makes a arithmetic rounding of the arithmetic value to the desired scale. This equivalent to a CAST function to a NUMERIC value with the desired scale.
Result tuples of a non-join query may contain the internal key provided the corresponding table has been created with the IKACCESS option. The syntax is SYSKEY which may appear in the outermost SELECT list. The application subsequently can make a fast direct access to the corresponding table by 'SELECT .. FROM <table> where SYSKEY = <syskeyvalue> The type of the SYSKEY field is CHAR(*).
A SELECT query may contain a FIRST clause at the end to limit the result tuples to a desired number. For example, to limit the result to 3 tuples, the syntax is 'SELECT ... FROM ... WHERE ... FIRST(3)'
The analytic function RANK is provided. It may appear inside a SELECT list and produces the ordinal number of the result tuple of the query block according to a specifed ORDER BY inside the RANK specification. Additional partitioning of the ORDERing may be specified.
The SET clause of an UPDATE statement may contain bracketed field sequences which obtain their values by a corresponding matching n-ary subquery.
'CREATE OR REPLACE <viewname> is now supported. If a view 'viewname' already exists and the new view definition is correct, then the old view definition is 'overwritten' by the new one.
Queries may consist of a query block which only contains one expression or a sequence of expressions without FROM clause. This is equivalent to the dummy table 'dual' used by other systems. For example, to get the current user of the current connection, a query 'SELECT USER' is appropriate.
TCI and JDBC support batch mode stored/prepared DML statements. A stored INSERT/UPDATE/DELETE statement with dynamic parameters '?' can be run several times with a set of different parameters. The parameters are cached on the client side and transferred to the server in one or few large packages when the batch is executed. This significantly reduces communications overhead.
tbdiff accepts option '-bfsize <size> for sampling several blobs within one file.
tbtar supports option 'locale=' for codeconversion when loading databases.
tbarc supports option 'cp=' for codeconversion when archiving databases.
Up to 64 sessions and up to 64 transactions for one application are accepted now.
The maximum tuplesize has been increased from 4kb - 96b bytes to 32kb - 96b. The maximum tuplesize is limited to the datbase's pagesize.
Transbase now comes with a new commandline interface UTBI. This application supports unicode data.
table columns are automatically arranged in a space optimal order. Manual optimization on DDL level is not longer needed.
Version 6 introduces the concept of distributed query. An application connected to one single database might reference a table on a remote database by suffixing the SQL table name with @dbname@hostname. Distributed joins can be run with that mechanism. Also INSERT statements (without BLOBs) can be processed against remote tables. BLOBs, however, can also be fetched via SELECT queries from remote tables.
DDL and VIEW statements against remote tables are not allowed. Also BLOB fields can only be fetched from local tables.
See details in distributed queries .
Transbase now supports database encryption. Database encryption must be enabled by using tbadmin .
The files 'f.db' and 'f.bak' have been replaced by 'dblist.ini'. The format has been changed to an INI-format which allows handling long filenames and filenames containing spaces.
The database description file 'tbdesc' has been replaced by 'dbconf.ini'.
Support for evalutation plans can by enabled by a tbmode-statement. Support is enabled by tbmode plans on and disabled by tbmode plans off.
tbi supports Evaluation Plans .
An administration password has been introduced for creating, deleting and administrating databases. As a consequence tbadmin-password is not longer needed for deleting a database.
A library is supplied for developing tbtar compliant applications.
A library is supplied for developing tbarc compliant applications.
User defined JAVA programs can be installed in the database. 3 different types of JAVA programs are distinguished.
A so called "Stored Procedure" is a JAVA procedure which is called by a "CALL proc(..)" statement on the TBX interface using the TbxRun function. If it has OUT parameters, it also delivers one result tuple which can be interpreted by the application exactly like a TbxRun on a SELECT statement.
A so called "Table Function" is a JAVA procedure with OUT parameters which can deliver a tuple set. It can be used in the FROM clause of a SQL query block like any other basetable or view.
A so called "User defined function" is a JAVA function which returns exactly one value at each call. It can be used in an arbitrary SQL DML statement at each place where an expression (delivering exactly one value) is accepted (i.e. SELECT, WHERE and HAVING clause).
Each of the above types may have arbitrary IN parameters. Actual parameters may be SQL expressions inside a SQL statement or dynamic parameters '?' which then are supplied by the application. In the latter case, the statement has to be stored by TbxStore like any other parameterized SQL statement.
Each JAVA program may itself open cursors to the database via the JDBC interface and may have side effects on database tables.
Database Triggers can be created by CREATE TRIGGER .. and dropped by DROP TRIGGER .. Functionality and syntax is closely related to the SQL3 standard.
Triggers can be specified as tuplewise or statement related.
For each trigger, a database event is specified which fires the trigger. Database events are INSERT, DELETE and UPDATE. UPDATE can be restricted to a subset of fields of the table.
Optionally, a SQL predicate (search condition) can be specified to restrict the firing of the trigger to a value dependent condition.
Firetime of the trigger can be specified as BEFORE or AFTER the database event.
The actions of a trigger can be one or several DML statements such as INSERT, UPDATE, DELETE. Also the execution of a JAVA stored procedure (CALL javaproc(..) ) can be an action.
A new construct SEQUENCE has been introduced which serves to generate database-wide unique numbers. A sequence is constructed with a user defined name. A start value and an increment value can optionally be specified. Per default a sequence starts with value 1 and increments by 1. A sequence with name S provides two functions, namely S.nextval to generate and deliver a new number and S.currval to refer to the most recently delivered number within the current application.
An existing SEQUENCE can be used as part of a DEFAULT clause for a field of a table. In this way, a field serves as an automatically generated synthetic key.
CREATE SEQUENCE si START 0; CREATE SEQUENCE sii START 0; CREATE TABLE invoice ( n INTEGER DEFAULT si.NEXTVAL, custid DECIMAL(5), ) KEY IS n CREATE TABLE invoice_item( n INTEGER REFERENCES invoice(N), item INTEGER DEFAULT sii.NEXTVAL, description VARCHAR(80), price DECIMAL(12,2), ... ) KEY IS n, item;
The following statements could be used to generate a new invoice with two items.
INSERT INTO invoice (n, custid) VALUES (si.NEXTVAL, 12345); INSERT INTO invoice_item(n, item, description, price) VALUES (si.CURRVAL, sii.NEXTVAL, 'First', 33.33); INSERT INTO invoice_item(n, item, description, price) VALUES (si.CURRVAL, sii.NEXTVAL, 'Second', 123.45);
In secondary indexes, participating fields and secondary keys now can be specified separately.
For example, assume a table with fields f1, f2, f3, f4 where f1 is the key:
CREATE TABLE f ( f1 INTEGER, f2 INTEGER, f3 INTEGER, f4 INTEGER) KEY IS f1 ;
Analogously, it is now possible to specify a KEY clause for secondary indexes that implies an automatic uniqueness restriction. So, the following two notations are equivalent:
CREATE UNIQUE INDEX findex ON f ( f2, f3, f4)
CREATE INDEX findex ON f ( f2, f3, f4) KEY IS f2, f3, f4
The semantic separation between index fields and unique-key fields, however, allows constructs like:
CREATE INDEX findex ON f ( f2, f3, f4) KEY IS f2, f3
where the uniqueness restriction is given by (f2, f3) only. Although a UNIQUE INDEX on (f2, f3) could have been defined, it may be very useful to have additional fields in a secondary index in order to omit the base tuple materialization. The query
SELECT f2, f4 FROM f WHERE f2=5
would need to access the base tuples when an index is defined only on (f2, f3) while it could be resolved solely from the secondary index when an index contains (f2, f3, f4).
For Transbase HyperCube, secondary indexes can be built on the numeric data types: TINYINT, SMALLINT, INTEGER and NUMERIC(P,S).
CREATE TABLE points ( id INTEGER, x NUMERIC (10,7) NOT NULL CHECK(x BETWEEN -180 AND 180), y NUMERIC (10,7) NOT NULL CHECK(x BETWEEN -90 AND 90), info CHAR(*), ... ) KEY IS id ; CREATE INDEX xpoints ON points(x,y,info) HCKEY IS x,y ;
The secondary index contains the fields x, y,and info. The hypercube key is defined on x and y only. Note that this example uses the new extended syntax for secondary indexes described above.
All fields used as hypercube key in the secondary index must be of appropriate type and be specified as NOT NULL and have a CHECK CONSTRAINT with range condition in the undelying table definition.
For SQL2 conformity, tuples can be constructed by parentheses as well as by brackets, e.g.
where (tuple) in (select ...).
Various ORACLE functions have been resembled with identical semantics and syntax, including SYSDATE, DECODE, TO_CHAR, NVL, TRUNC in order to alleviate the migration from ORACLE to Transbase.
Various optimizations improve the computation of predicates on B-tree layer. Projections are handeled by the B-tree layer, too, in order to save space and time of processing.
Space requirements of operator trees have been reduced for joins.
The UNICODE standard maps characters of all existing languages into a single coding schema. Characters are either denoted by 16-bit values (UCS-2) or by 32-bit values (UCS-4). For databases, however, to store each character as fixed 2 or even 4 bytes, would be too inefficient. Instead, Transbase uses the UTF-8 coding of UNICODE characters to store them on disk and to send and receive them over communication lines.
The table below shows valid UTF-8 codings:
0xxxxxxx 7 bits 110xxxxx 10xxxxxx 11 bits 1110xxxx 10xxxxxx 10xxxxxx 16 bits 11110xxx 10xxxxxx 10xxxxxx 10xxxxxx 21 bits 111110xx 10xxxxxx 10xxxxxx 10xxxxxx 10xxxxxx 26 bits 1111110x 10xxxxxx 10xxxxxx 10xxxxxx 10xxxxxx 10xxxxxx 31 bits
In particular, it can be seen that
ASCII characters in the range of 0..127 are mapped into themselves;
single-byte characters in the range 128..255 (i.e. those in the Latin-1 charset) are mapped into a sequence of two bytes;
other UCS-2 characters are mapped into sequences of at most three bytes;
UCS-4 characters are mapped into sequences of at most six bytes.
It can be seen, too, that
not every sequence of 8-bit bytes is a valid UTF-8 coded string,
the mapping is only unique if characters are mapped into sequences of minimal length and
the sort order is preserved by the coding.
The fact that a single character may consist of more than one byte, influences the string functions and string predicates. The SUBSTRING function e.g. assumes its parameters to be characters (not bytes) and the LIKE predicate expects an underscore to denote a single character (not byte).
Please note that the definition of fixed length characters like CHAR(N) still is given in bytes (not in characters) because a length of 2 characters would result in a (non-fixed) bytelength of between 2 and 12. A length restriction in characters could be formulated as: CONSTRAINT (CHARACTER_LENGTH(x) = 2).
At the communication layer, UTF-8 is used, too. In particular, TBX strings are assumed to be valid UTF-8, both on input (queries, parameters) and on output (result strings). UTF-8 should be mapped into either UNICODE or into an appropriate single-byte charset by the application.
For conversion of single-byte databases, it is recommended to export the data, convert it into UTF-8 or UNICODE and then to import it. The Transbase Spooler has been extended to accept single-byte, UTF-8 and UNICODE input.
In particular, the following important modifications apply for Unicode databases.
The coding of databases has to be specified upon creation. It cannot be changed later. Code pages can be: proprietary (upward-compatibel to former Transbase versions), ASCII (only ASCII characters below 128 are permitted), single-byte (arbitrary single-byte characters are permitted), and UTF-8 (arbitrary Unicode characters are permitted).
Along with the coding, a fixed locale setting can be defined which influences national character processing.
When defining fixed-length strings, a maximum number of bytes has to be specified rather than a maximum number of characters. The reason for this decision is that the specification of N is space-related. In particular, CHAR(5) strings must have fixed length of 5 bytes, while strings of five characters may vary in size between 5 and 15 bytes(for UCS-2).
A size restriction for a field COL in terms of characters can be specified by an additional CHECK CONSTRAINT (CHARACTER_LENGTH(COL) < 10).
In contrast, the SIZE OF operator for strings returns the number of bytes taken by the UTF-8 representation.
All string related functions work in terms of characters (not bytes).
In particular, the function CHARACTER_LENGTH(x) returns the number of Unicode characters in x, while SIZE OF x returns the number of bytes.
The function SUBSTRING(x from 1 for 2) returns the first two characters of x. SIZE OF (SUBSTRING(x from 1 for 2)) may return values between 0 and 6 for UCS-2 strings.
String literals containing UNICODE characters can be constructed by either of the following methods:
'München' 'M' + 0u00fc + 'nchen' 'M' + 0xc3bc + 'nchen'
The first method requires the ability to type the unicode character directly (which be not be possible in any environment).
The second method uses a new string literal variant which denote Unicode characters by their hexadecimal value. Note that always four hexadecimal digits must be specified per Unicode character. Thereby, more than one Unicode character representation can be concatenated, e.g. 0u00fc006e.
The third method provides a direct UTF-8 representation (syntactically a BINCHAR) of that part of the string that cannot be expressed literally. Again note, that for this representation a valid UTF-8 coding is required.
When data is loaded from or extracted into external files by the Transbase SPOOL command, it has to be specified in which format the file should be coded.
For this purpose, the syntax of the SPOOL statement has been extended. See SQL Reference Manual for more details.
TransbaseCD offers a new space saving possibility to propagate database updates for CD-ROM databases.
To propagate updates onto a CD-ROM database (which might be distributed in many copies) two alternatives have been offered in the past:
Distribute SQL scripts (and possibly spool files) which are executed on CD-ROM site.
Accumulate all updates on one master CD-ROM database (updates then are reflected in a corresponding diskfile). Then distribute copies of the diskfile to the target sites.
The first solution is complex and requires runtime equipment on the target sites. The second solution is platform dependent and tends to produce large data volumes.
A third method is provided now: Updates are prepared as in the second solution on one master site. Then a new kind of FLUSH operation ("incremental flush") is performed on that CD-ROM database. Thereby, so-called delta romfiles are produced. These delta romfiles then are distributed to the target sites. Blocks appearing in a delta romfile shadow the blocks in the original romfiles. Delta romfiles are automatically compressed and thus are ideally suited to be distributed over the Internet. Moreover, delta romfiles become platform independent, too, like standard romfiles.
Databases can be defined to be "case insensitive". In this case, all identifiers (field names, table names etc.) are compared by case-insensitive routines. When inserted into the data dictionary, those identifiers are mapped to capitals. This has to be considered when the data dictionary is searched explicitly by SQL queries on system tables.
Note that "delimiter identifiers" are still case sensitive.
By default, databases are case sensitive. Migrated old databases are also case sensitive. A case sensitive database can also be converted to a case insensitive database by an explicit tbadmin -a command. In rare cases, this conversion might fail if table or field names exist which would map to the same capital letter identifier.
The cache size (global shared memory) of a database now can be set up to a maximum value of 20 Gbyte.
Temporary files possibly needed by the kernel are no longer placed in the TMP or TMPDIR directory, but in the scratch directory of the database. Therefore, it should be made sure that sufficient space is available for scratch files.
The communication between Transbase kernel and applications is handeled by TBX. The communication can be declared to be crypted in order to increase the privacy of the client/server communication.
Cryptification is specified by the command tbmux -crypt ... and therefore applies to all communications with this database server.
A new programming interface, Transbase Call-Interface (TCI) has been implemented. TCI supports ANSI and UNICODE applications.