Table 10-2 Database Features
1 DB2 natively supports stored procedures or functions written in Java. To write procedures by using the native SQL procedural language, install a C compiler on the database server.
2 The Informix identity column keyword is SERIAL8.
3 Informix stored procedures cannot return values.
4 The MySQL identity column keyword is AUTO_INCREMENT.
5 You can use a Postgres sequence object to provide default values for primary key columns, effectively simulating an identity column.
Postgres has a native construct called rules. This construct can be used to effectively simulate triggers and instead-of-triggers. It also supports the use of triggers or instead-of-triggers written in a variety of procedural programming languages.
The following table lists SQL statements used to retrieve the current date and time by database:
Table 10-3 Time Stamp Statements
The following table lists the SQL syntax for calling a stored procedure or function. This is useful for formatting procedure and function calls in embedded SQL statements.
Table 10-4 Calling a Stored Procedure or Function
1 Oracle’s JDBC implementation does not support calling functions as a string.
The following table lists outer join operators by database.
Table 10-5 Outer Join Operators
NOTE:Oracle supports the ANSI-compliant left outer join operator LEFT OUTER JOIN as of version 10g.
Table 10-7 Supported Transaction Isolation Levels
Database |
None |
Read Uncommitted |
Read Committed |
Repeatable Read |
Serializable |
URL |
---|---|---|---|---|---|---|
IBM DB2 UDB |
0 |
X |
X 1 |
X |
X |
|
MySQL (InnoDB Table Type) |
0 |
X |
X |
X 1 |
X |
|
Oracle |
0 |
0 |
X 1 |
0 |
X |
|
PostgreSQL |
0 |
0 2 |
X 1 |
0 2 |
X |
1 This is the default isolation level for this database. 2 Can be set, but it is aliased to a supported isolation level.
The following table identifies the commit keywords for supported databases:
Table 10-8 Commit Keywords
Database |
Commit Keyword |
---|---|
IBM DB2 UDB |
COMMIT |
Informix IDS |
COMMIT WORK 1 |
MSSQL |
GO |
MySQL |
COMMIT |
Oracle |
COMMIT |
PostgreSQL |
COMMIT |
Sybase ASE |
GO |
1 For logging and ANSI-compliant databases. Non-logging databases do not support transactions.
The following table lists properties for this database.
Table 10-9 Properties for IBM DB2 UDB
The following table lists database compatibility parameters that the Driver for JDBC implicitly sets at runtime. Do not explicitly override these settings.
Table 10-10 Dynamically Configured IBM DB2 Universal Database Settings
The timestamp format is proprietary. See Known Issues.
The following table lists properties for this database.
Table 10-11 Settings for Informix Dynamic Server
1 For logging and ANSI-compliant databases. Nonlogging databases do not support transactions.
The following table lists database compatibility parameters that the Driver for JDBC implicitly sets at runtime. Do not explicitly overwrite these settings.
Table 10-12 Dynamically Configured Informix Dynamic Server Settings
Display Name |
Tag Name |
Value |
---|---|---|
Current Timestamp Statement: |
current-timestamp-stmt |
SELECT FIRST 1 (CURRENT YEAR TO FRACTION(5)) FROM INFORMIX.SYSTABLES |
NUMERIC or DECIMAL columns cannot be used as primary keys unless the scale (the number of digits to the right of the decimal point) is explicitly set to 0 when the table is created. By default, the scale is set to 255.
The following table lists properties for this database:
Table 10-13 Settings for Microsoft SQL Server
The following table lists database compatibility parameters that the Driver for JDBC implicitly sets at runtime. Do not explicitly overwrite these settings.
The following table lists properties for this database.
Table 10-15 Settings for MySQL
Property |
Value |
---|---|
Current Timestamp Statement |
SELECT (CURRENT_TIMESTAMP) |
Stored Procedure/Function Call Syntax |
(NA) |
Case-Sensitive? |
Yes |
Commit Keyword |
COMMIT |
Left Outer Join Operator |
LEFT OUTER JOIN |
The following table lists database compatibility parameters that are dynamically configured at runtime for this database.
Table 10-16 Dynamically Configured MySQL Settings
Display Name |
Tag Name |
Value |
---|---|---|
Supports schemas in metadata retrieval? |
supports-schemas-in-metadata-retrieval |
false |
TIMESTAMP columns, when updated after being initially set to 0 or NULL, are always set to the current date and time. To compensate for this behavior, we recommend that you map Identity Vault Time and Timestamp syntaxes to DATETIME columns.
The following table lists properties for this database:
Table 10-17 Settings for Oracle
The following table lists database compatibility parameters that the Driver for JDBC implicitly sets at runtime. Do not explicitly overwrite these settings.
Table 10-18 Dynamically Configured Oracle Settings
NOTE:The default exclusion filter is intended to omit from the synchronization schema dropped tables that are visible in Oracle 10g.
LONG, LONG RAW and BLOB columns cannot be referenced in a trigger. You can’t reference columns of these types by using the :NEW qualifier in a trigger, including instead-of-triggers.
The following table lists properties for this database:
Table 10-19 Settings for PostgreSQL
PostgreSQL does not support <check-object-password> events. You control authentication by manually inserting entries into the pg_hba.conf file.
The following table lists properties for this database:
Table 10-20 Settings for Sybase ASE
The following table lists database compatibility parameters that the Driver for JDBC implicitly sets at runtime. Do not explicitly overwrite these settings.
Table 10-21 Dynamically Configured Sybase ASE Settings
Padding and truncation of binary values.
To ensure ANSI-compliant padding and truncation behavior for binary values, make sure that binary column types (other than IMAGE) meet the following criteria:
They are exactly the size of the eDirectory™ attribute that maps to them.
They are constrained NOT NULL.
They are added to the Publisher and Subscriber Creation policies.
If they are constrained NULL, trailing zeros, which are significant to eDirectory, are truncated. If binary columns exceed the size of their respective eDirectory attributes, extra 0s are appended to the value.
The recommended solution is to use only the IMAGE data type when synchronizing binary values.
DATETIME fractions of a second are rounded. Sybase Timestamps are at best accurate to 1/300th of a second (approximately.003 seconds). The database server rounds to the nearest 1/300 th of a second as opposed to the nearest 1/1000 th of a second (.001 seconds or 1 millisecond).
Timestamp formats are proprietary.