databases.ad

[id="databases-compatibility",reftext="Database Compatibility"]
=== Compatibility

Each database management system has its own characteristics that results in
feature coverage in SymmetricDS.  The following table shows which features are available
by database.

.Support by Database
[cols="15,13,^12,^12,^12,^12,^12,^12,^12,^12", options="header"]
|====
|Database
|Versions
|Transaction Identifier
|Data Capture
|Conditional Sync
|Update Loop Prevention
|BLOB Sync
|CLOB Sync
|Transactional DDL
|Load Only

|BigQuery *
|All
|
|
|
|✔
|
|
|
|✔

|DB2
|9.5
|
|✔
|✔
|✔
|✔
|✔
|✔  
|

|DB2
|10,11
|✔
|✔
|✔
|✔
|✔
|✔ 
|✔                   
|

|DB2 for IBM i *
|6
|
|✔
|✔
|
|✔
|✔
|                    
|

|DB2 for IBM z/OS *
|10
|
|✔
|✔
|
|✔
|✔
|                    
|

|Derby
|10.3.2.1
|✔
|✔
|✔
|✔
|✔
|✔
|
|


|Elasticsearch
|7.13.2
|
|
|
|✔
|
|
|
|✔

|Firebird
|2.0
|✔
|✔
|✔
|✔
|✔
|✔
|
|

|Greenplum
|8.2.15 and above
|
|
|
|✔
|
|
|
|

|H2
|1.x
|✔
|✔
|✔
|✔
|✔
|✔
|
|

|HSQLDB
|1.8
|✔
|✔
|✔
|✔
|✔
|✔
|
|
 
|HSQLDB
|2.0
|
|✔
|✔
|✔
|✔
|✔
|
|

|Informix
|11
|
|✔
|✔
|✔
|
|
|                    
|                    

|Ingres
|11 and above
|✔
|✔
|✔
|✔
|✔
|✔
|
|

|Interbase
|9.0
|
|✔
|✔
|✔
|✔
|✔
|
|

|MySQL
|5.0.2 and above
|✔
|✔
|✔
|✔
|✔
|✔
|
|

|MariaDB
|5.1 and above
|✔
|✔
|✔
|✔
|✔
|✔
|
|

|uoDB
|2.6 and above
|✔
|✔
|✔
|✔
|
|✔
|
|
                    
|Oracle
|10g and above
|✔
|✔
|✔
|✔
|✔
|✔
|
|
 
|PostgreSQL
|8.2.5 and above
|✔ (8.3 and above only)
|✔
|✔
|✔
|✔
|✔
|✔
|

|Redshift
|1.0
|
|
|
|✔
|
|
|
|

|SQL Anywhere
|9
|✔
|✔
|✔
|✔
|✔
|✔
|
|

|SQL Server
|2005 and above
|✔
|✔
|✔
|✔
|✔
|✔
|✔
|
 
|SQL Server Azure
|Tested on 11.00.2065
|✔
|✔
|✔
|✔
|✔
|
|                    
|
 
|SQLite
|3.x
|
|✔
|✔
|✔
|✔
|✔
|
|
 
|Sybase ASE
|12.5
|✔
|✔
|✔
|✔
|✔
|✔
|
|
 
|Teradata
|15.10 and above
|✔
|✔
|✔
|
|
|
|  
|✔
   
|Tibero
|6 and above
|✔
|✔
|✔
|✔
|✔
|✔
| 
|
                   
|====

Database *:: Asterisk (*) next to database indicates the database support is available in https://www.jumpmind.com/products/symmetricds/symmetricds-overview[SymmetricDS Pro] only.

Transaction Identifier:: A transaction identifier is recorded in the SYM_DATA table along with changes, which allows changes in the same transaction to be grouped
together for commit within a batch.

Data Capture:: Changes to tables can be captured using database triggers.

Conditional Sync:: Conditions can be specified on SYM_TRIGGER, which are compiled into the trigger to decide if a change should be captured.

Update Loop Prevention:: The remote node is recorded on data that is captured, so the system can prevent the changes from being sent back to the same node. 

BLOB Sync:: Binary large object data can be captured or streamed from the database.

CLOB Sync:: Character large object data can be captured or streamed from the database.

=== Catalog and Schema

A relational database may be divided into catalogs that contain sub-databases called schemas, which contain tables.
Each database management system can implement the concepts of catalog and schema differently or not at all.
When locating a table, SymmetricDS uses the default catalog and schema unless the user specifies one.

.Catalog and Schema Support by Database
|===
|Database |Version |Catalog Support |Catalog Default |Schema Support |Schema Default

|DB2
|
|
|
|✔
|values current schema

|Derby
|
|
|
|✔
|values current schema   
 
|Elasticsearch
|
|
|
|
|   
 
|Firebird
|
|
|
|
|

|Greenplum
|
|
|
|✔
|select current_schema()                   

|H2
|
|✔
|select database()
|✔
|select schema()

|HSQLDB
|1.0
|
|
|
|
 
|HSQLDB
|2.0
|✔
|select value from information_schema.system_sessioninfo where key = 'CURRENT SCHEMA'
|✔
|select value from information_schema.system_sessioninfo where key = 'CURRENT SCHEMA'

|Informix
|
|
|
|✔
|select trim(user) from sysmaster:sysdual

|Ingres
|
|
|
|✔
|select dbmsinfo('username')

 
|Interbase
|
|
|
|
|

|MySQL
| 
|✔
|select database()
|
| 
 
|MariaDB
|
|✔
|select database()
|
|

|uoDB
|
|
|
|✔
|select current_schema from system.dual

|Oracle
| 
|
| 
|✔
|select sys_context('USERENV', 'CURRENT_SCHEMA') from dual

|PostgreSQL
|
|
|
|✔
|select current_schema()                   

|SQL Anywhere
|
|✔
|select db_name()
|✔
|select user_name()
 
|SQL Server
|2000
|✔
|select db_name()
|✔
|select 'dbo'

|SQL Server
|2005+
|✔
|select db_name()
|✔
|select schema_name()

|SQL Server
|
|✔
|select db_name()
|✔
|select schema_name()
 
|SQL Server Azure
|
|✔
|select db_name()
|✔
|select schema_name()
 
|SQLite
|
|
|
|
|      

|Sybase ASE
|
|✔
|select db_name()
|✔
|select user_name()
 
|Redshift
|
|
|
|✔
|select current_schema()


|Tibero
| 
|
| 
|✔
|select sys_context('USERENV', 'CURRENT_SCHEMA') from dual

|===
include::bigquery.ad[]
include::db2.ad[]
include::derby.ad[]
include::elasticsearch.ad[]
include::firebird.ad[]
include::greenplum.ad[]
include::h2.ad[]
include::hbase.ad[]
include::hsqldb.ad[]
include::ignite.ad[]
include::informix.ad[]
include::ingres.ad[]
include::interbase.ad[]
include::kafka.ad[]
include::mariadb.ad[]
include::mongodb.ad[]
include::mssqlserver.ad[]
include::mysql.ad[]
include::opensearch.ad[]
include::oracle.ad[]
include::postgresql.ad[]
include::redshift.ad[]
include::snowflake.ad[]
include::sqlite.ad[]
include::sybase-ase.ad[]
include::sybase-sqlanywhere.ad[]
include::teradata.ad[]
include::tibero.ad[]