Table 14-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 through OUT parameters.
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 14-3 Time Stamp Statements
The following table lists the syntaxes for calling a stored procedure or function by database vendor. There’s also a vendor-neutral JDBC escape syntax (see JDBC Escape Synax). Whenever possible, it is more secure to call a stored procedure or function by using the jdbc:call-function or jdbc:call-procedure syntax. Ssee Section 13.14, Calling Stored Procedures and Functions.) Other syntaxes should be used only when specifying procedure or function calls in driver parameters (for example, Post Polling Statements and Connection Initialization Statements).
Table 14-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 14-5 Outer Join Operators
NOTE:Oracle supports the ANSI-compliant left outer join operator LEFT OUTER JOIN as of version 10g.
Table 14-7 Supported Transaction Isolation Levels
|
Database |
None |
Read Uncommitted |
Read Committed |
Repeatable Read |
Serializable |
URL |
|---|---|---|---|---|---|---|
|
IBM DB2 UDB |
0 |
X |
X1 |
X |
X |
|
|
MySQL (InnoDB Table Type) |
0 |
X |
X |
X1 |
X |
|
|
Oracle |
0 |
0 |
X1 |
0 |
X |
|
|
PostgreSQL |
0 |
02 |
X1 |
02 |
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 14-8 Commit Keywords
|
Database |
Commit Keyword |
|---|---|
|
IBM DB2 UDB |
COMMIT |
|
Informix IDS |
COMMIT WORK1 |
|
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 14-9 Properties for IBM DB2 UDB
|
Property |
Value |
|---|---|
|
Current Timestamp Statement |
SELECT (CURRENT TIMESTAMP) FROM SYSIBM.SYSDUMMY1 FETCH FIRST 1 ROW ONLY |
|
Case-Sensitive? |
No |
|
Commit Keyword |
COMMIT |
|
Left Outer Join Operator |
LEFT OUTER JOIN |
The following table lists database compatibility parameters that the Driver for JDBC implicitly sets at runtime. Do not explicitly override these settings.
Table 14-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 14-11 Settings for Informix Dynamic Server
|
Property |
Value |
|---|---|
|
Current Timestamp Statement |
SELECT FIRST 1 (CURRENT YEAR TO FRACTION(5)) FROM INFORMIX.SYSTABLES |
|
Case-Sensitive? |
No |
|
Commit Keyword |
COMMIT WORK1 |
|
Left Outer Join Operator |
LEFT OUTER JOIN |
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 14-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.
DBAs cannot grant privileges to objects they don’t own.
The following table lists properties for this database:
Table 14-13 Settings for Microsoft SQL Server
|
Property |
Value |
|---|---|
|
Current Timestamp Statement |
SELECT (CURRENT_TIMESTAMP) |
|
Case-Sensitive? |
No |
|
Commit Keyword |
GO |
|
Left Outer Join Operator (7, 2000) |
*= |
|
Left Outer Join Operator (2005) |
LEFT OUTER JOIN |
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 14-15 Settings for MySQL
|
Property |
Value |
|---|---|
|
Current Timestamp Statement |
SELECT (CURRENT_TIMESTAMP) |
|
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 14-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 14-17 Settings for Oracle
|
Property |
Value |
|---|---|
|
Current Timestamp Statement |
SELECT (SYSDATE) FROM SYS.DUAL |
|
Case-Sensitive? |
No |
|
Commit Keyword |
COMMIT |
|
Left Outer Join Operator |
(+) |
The following table lists database compatibility parameters that the Driver for JDBC implicitly sets at runtime. Do not explicitly overwrite these settings.
Table 14-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 14-19 Settings for PostgreSQL
|
Property |
Value |
|---|---|
|
Current Timestamp Statement |
SELECT (CURRENT_TIMESTAMP) |
|
Case-Sensitive? |
No |
|
Commit Keyword |
COMMIT |
|
Left Outer Join Operator |
LEFT OUTER JOIN |
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 14-20 Settings for Sybase ASE
|
Property |
Value |
|---|---|
|
Current Timestamp Statement |
SELECT GETDATE() |
|
Case-Sensitive? |
Yes |
|
Commit Keyword |
GO |
|
Left Outer Join Operator |
*= |
The following table lists database compatibility parameters that the Driver for JDBC implicitly sets at runtime. Do not explicitly overwrite these settings.
Table 14-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/300th of a second as opposed to the nearest 1/1000th of a second (.001 seconds or 1 millisecond).
Timestamp formats are proprietary.