Table 12-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 12-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 Syntax). Whenever possible, it is more secure to call a stored procedure or function by using the jdbc:call-function or jdbc:call-procedure syntax. See Section 11.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 12-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 12-5 Outer Join Operators
Oracle supports the ANSI-compliant left outer join operator LEFT OUTER JOIN as of version 10g.
Table 12-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 12-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 database compatibility parameters that the JDBC driver implicitly sets at runtime. Do not explicitly override these settings.
Table 12-10 Dynamically Configured IBM DB2 Universal Database Settings
The timestamp format is proprietary. See Known Issues.
Table 12-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 JDBC driver implicitly sets at runtime. Do not explicitly overwrite these settings.
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 database compatibility parameters that the JDBC driver implicitly sets at runtime. Do not explicitly overwrite these settings.
The following table lists database compatibility parameters that are dynamically configured at runtime for this database.
TIMESTAMP columns, when they are 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 database compatibility parameters that the JDBC driver implicitly sets at runtime. Do not explicitly overwrite these settings.
Table 12-18 Dynamically Configured Oracle Settings
The default exclusion filter omits dropped tables (that are visible in Oracle 10g) from the synchronization schema.
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.
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 database compatibility parameters that the JDBC driver implicitly sets at runtime. Do not explicitly overwrite these settings.
Table 12-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.