14.3 Database Characteristics

14.3.1 Database Features

Table 14-2 Database Features

Database

Schemas

Views

Identity Columns

Sequences

Stored Procedures

Functions

Triggers

Instead-Of-Triggers

IBM DB2 UDB 7

X

X

X

0

X1

X1

X

0

IBM DB2 UDB 8

X

X

X

0

X1

X1

X

X

Informix IDS 9

X

X

X2

0

X3

X

X

0

Informix IDS 10

X

X

X2

0

X3

X

X

X

MS SQL 7

X

X

X

0

X

0

X

0

MS SQL 2000, 2005

X

X

X

0

X

X

X

X

MySQL 4

0

0

X4

0

0

0

0

0

MySQL 5

X

X

X4

0

X

X

X

0

Oracle 8i, 9i, 10g

X

X

0

X

X

X

X

X

Postgres 7

X

X

X5

X

X

X

X6

X6

Sybase ASE 12

X

X

X

0

X

0

X

0

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.

14.3.2 Current Time Stamp Statements

The following table lists SQL statements used to retrieve the current date and time by database:

Table 14-3 Time Stamp Statements

Database

Current Time Stamp Statement

ANSI-Compliant

IBM DB2 UDB

SELECT (CURRENT TIMESTAMP) FROM SYSIBM.SYSDUMMY1 FETCH FIRST 1 ROW ONLY

No

Informix IDS

SELECT FIRST 1 (CURRENT YEAR TO FRACTION(5)) FROM INFORMIX.SYSTABLES

No

MSSQL

SELECT (CURRENT_TIMESTAMP)

Yes

MySQL

SELECT (CURRENT_TIMESTAMP)

Yes

Oracle

SELECT (SYSDATE) FROM SYS.DUAL

No

PostgreSQL

SELECT (CURRENT_TIMESTAMP)

Yes

Sybase ASE

SELECT GETDATE()

No

14.3.3 Syntaxes for Calling Stored Procedures and Functions

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

Database

Stored Procedure/Function JDBC Call Syntax

IBM DB2 UDB

{call schema-name.procedure-name(parameter-list)}

Informix IDS

EXECUTE [PROCEDURE | FUNCTION] schema-name.routine-name(parameter-list)

MSSQL

EXECUTE schema-name.procedure-name(parameter-list)

MySQL

[TODO]

Oracle1

CALL schema-name.procedure-name(parameter-list)

PostgreSQL

SELECT schema-name.procedure-name(parameter-list)

Sybase ASE

EXECUTE schema-name.procedure-name(parameter-list)

1 Oracle’s JDBC implementation does not support calling functions as a string.

14.3.4 Left Outer Join Operators

The following table lists outer join operators by database.

Table 14-5 Outer Join Operators

Database

Left Outer Join Operator

ANSI-Compliant

IBM DB2 UDB

LEFT OUTER JOIN

Yes

Informix IDS

LEFT OUTER JOIN

Yes

MSSQL 7.5, 2000

*=

No

MSSQL 2005

LEFT OUTER JOIN

Yes

MySQL

LEFT OUTER JOIN

Yes

Oracle

(+) [TODO]

No

PostgreSQL

LEFT OUTER JOIN

Yes

Sybase ASE

*=

No

NOTE:Oracle supports the ANSI-compliant left outer join operator LEFT OUTER JOIN as of version 10g.

14.3.5 Undelimited Identifier Case Sensitivity

Table 14-6 Case Sensitivity for Undelimited Identifiers

Database

Case-Sensitive?

IBM DB2 UDB

No

Informix IDS

No

MSSQL

No

MySQL

Yes

Oracle

No

PostgreSQL

No

Sybase ASE

Yes

14.3.6 Supported Transaction Isolation Levels

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

Setting JDBC Transaction Isolation Levels

MySQL (InnoDB Table Type)

0

X

X

X1

X

InnoDB Transaction Isolation Levels

Oracle

0

0

X1

0

X

JDBC Transaction Optimization

PostgreSQL

0

02

X1

02

X

Transaction Isolation

1 This is the default isolation level for this database. 2 Can be set, but it is aliased to a supported isolation level.

14.3.7 Commit Keywords

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.

14.3.8 IBM DB2 Universal Database (UDB)

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

Dynamic Defaults

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

Display Name

Tag Name

Value

Current Timestamp Statement:

current-timestamp-stmt

SELECT (CURRENT TIMESTAMP) FROM SYSIBM.SYSDUMMY1 FETCH FIRST 1 ROW ONLY

Timestamp Translator class:

time-translator-class

com.novell.nds.dirxml.driver.jdbc.db.DB2Timestamp

Known Issues

14.3.9 Informix Dynamic Server (IDS)

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.

Dynamic Defaults

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

Known Issues

  • 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.

14.3.10 Microsoft SQL Server

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

Dynamic Defaults

The following table lists database compatibility parameters that the Driver for JDBC implicitly sets at runtime. Do not explicitly overwrite these settings.

Table 14-14 Dynamically Configured Microsoft SQL Server Settings

Display Name

Tag Name

Value

Add default values on insert?

add-default-values-on-view-insert

true

Left outer-join operator (7, 2000):

left-outer-join-operator

*=

Left outer-join operator (2005):

left-outer-join-operator

LEFT OUTER JOIN

14.3.11 MySQL

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

Dynamic Defaults

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

Known Issues

  • 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.

14.3.12 Oracle

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

(+)

Dynamic Defaults

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

Display Name

Tag Name

Value

Left outer-join operator

left-outer-join-operator

(+)

Exclude filter expression

exclude-table-filter

BIN\$.{22}==\$0

Lock statement generator class

lock-generator-class

com.novell.nds.dirxml.driver.jdbc.db.lock.OraLockGenerator

NOTE:The default exclusion filter is intended to omit from the synchronization schema dropped tables that are visible in Oracle 10g.

Limitations

  • 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.

14.3.13 PostgreSQL

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

Known Issues

  • PostgreSQL does not support <check-object-password> events. You control authentication by manually inserting entries into the pg_hba.conf file.

14.3.14 Sybase Adaptive Server Enterprise (ASE)

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

*=

Dynamic Defaults

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

Display Name

Tag Name

Value

Current timestamp statement

current-timestamp-stmt

SELECT GETDATE()

Left outer-join operator

left-outer-join-operator

*=

Timestamp Translator class

time-translator-class

com.novell.nds.dirxml.driver.jdbc.db.SybaseTimestamp

Known Issues

  • 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.