Novell Documentation: Nsure Identity Manager Drivers

Database Characteristics

Database Features

The following table is a summary of database features:

Database	Schemas	Views	Identity Columns	Sequences	Stored Procedures	Functions	Triggers	Instead-Of-Triggers
IBM DB2 UDB 7	X	X	X	0	X¹	X¹	X	0
IBM DB2 UDB 8	X	X	X	0	X¹	X¹	X	X
Informix IDS 9	X	X	X²	0	X³	X	X	0
MS SQL 7	X	X	X	0	X	0	X	0
MS SQL 8	X	X	X	0	X	X	X	X
MySQL 4	0	0	X⁴	0	0	0	0	0
Oracle 8i, 9i, 10g	X	X	0	X	X	X	X	X
Postgres 7	X	X	X⁵	X	X	X	X⁶	X⁶
Sybase ASE 12	X	X	X	0	X	0	X	0

DB2 natively supports stored procedures/functions written in Java. To write procedures using the native SQL procedural language, install a C compiler on the database server.

The Informix identity column keyword is SERIAL8.

Informix stored procedures cannot return values.

The MySQL identity column keyword is AUTO_INCREMENT.

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/instead-of-triggers written in a variety of procedural programming languages.

Current Time Stamp Statements

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

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

Stored Procedure and Function JDBC Call Syntaxes

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.

Database	Stored Procedure/Function JDBC Call Syntax
IBM DB2 UDB	{call schema-name.procedure-name(parameter-list)}
Informix IDS	EXECUTE [PROCEDURE \| FUNCTION] schema-name.procedure-name(parameter-list)
MSSQL	EXECUTE schema-name.procedure-name(parameter-list)
MySQL	(NA)
Oracle	CALL schema-name.procedure-name(parameter-list)
PostgreSQL	SELECT schema-name.procedure-name(parameter-list)
Sybase ASE	EXECUTE schema-name.procedure-name(parameter-list)

Left Outer Join Operators

The following table lists outer join operators by database.

Database	Left Outer Join Operator	Ansi-Compliant
IBM DB2 UDB	LEFT OUTER JOIN	Yes
Informix IDS	LEFT OUTER JOIN	Yes
MSSQL	*=	No
MySQL	LEFT OUTER JOIN	Yes
Oracle	(+)	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.

Undelimited Identifier Case-Sensitivity

Database	Case-Sensitive?
IBM DB2 UDB	No
Informix IDS	No
MSSQL	No
MySQL	Yes
Oracle	No
PostgreSQL	No
Sybase ASE	Yes

Supported Transaction Isolation Levels

Database	Read Uncommitted	Read Committed	Repeatable Read	Serializable	URL
IBM DB2 UDB	X	X¹	X	X	Setting JDBC Transaction Isolation Levels
MySQL (InnoDB Table Type)	X	X	X¹	X	InnoDB Transaction Isolation Levels
Oracle	0	X¹	0	X	JDBC Transaction Optimization
PostgreSQL	0²	X¹	0²	X	Transaction Isolation

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

Commit Keywords

The following table identifies the commit keywords for supported databases:

Database	Commit Keyword
IBM DB2 UDB	COMMIT
Informix IDS	COMMIT WORK¹
MSSQL	GO
MySQL	COMMIT
Oracle	COMMIT
PostgreSQL	COMMIT
Sybase ASE	GO

¹For logging and ANSI-compliant databases. Non-logging databases do not support transactions.

IBM DB2 Universal Database (UDB)

The following table lists properties for this database.

Property	Value
Current Timestamp Statement	SELECT (CURRENT TIMESTAMP) FROM SYSIBM.SYSDUMMY1 FETCH FIRST 1 ROW ONLY
Stored Procedure/Function Call Syntax	{call schema-name.procedure-name(parameter-list)}
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.

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

The timestamp format is proprietary.

See .

Informix Dynamic Server (IDS)

The following table lists properties for this database.

Property	Value
Current Timestamp Statement	SELECT FIRST 1 (CURRENT YEAR TO FRACTION(5)) FROM INFORMIX.SYSTABLES
Stored Procedure/Function Call Syntax	EXECUTE [PROCEDURE \| FUNCTION] schema-name.procedure-name(parameter-list)
Case-Sensitive?	No
Commit Keyword	COMMIT WORK¹
Left Outer Join Operator	LEFT OUTER JOIN

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

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.

Microsoft SQL Server

The following table lists properties for this database:

Property	Value
Current Timestamp Statement	SELECT (CURRENT_TIMESTAMP)
Stored Procedure/Function Call Syntax	EXECUTE schema-name.procedure-name(parameter-list)
Case-Sensitive?	No
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.

Display Name	Tag Name	Value
Add default values on insert?	add-default-values-on-view-insert	true
Left outer-join operator:	left-outer-join-operator	*=

MySQL

The following table lists properties for this database.

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

Dynamic Defaults

The following table lists database compatibility parameters that are dynamically configured at runtime for this database.

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.

Oracle

The following table lists properties for this database:

Property	Value
Current Timestamp Statement	SELECT (SYSDATE) FROM SYS.DUAL
Stored Procedure/Function Call Syntax	CALL schema-name.procedure-name(parameter-list)
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.

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 visible in Oracle 10g (database objects visible to the Driver for JDBC at runtime).

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.

PosgreSQL

The following table lists properties for this database:

Property	Value
Current Timestamp Statement	SELECT (CURRENT_TIMESTAMP)
Stored Procedure/Function Call Syntax	SELECT schema-name.procedure-name(parameter-list)
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.

Sybase Adaptive Server Enterprise (ASE)

The following table lists properties for this database:

Property	Value
Current Timestamp Statement	SELECT GETDATE()
Stored Procedure/Function Call Syntax	EXECUTE schema-name.procedure-name(parameter-list)
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.

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, will be truncated. If binary columns exceed the size of their respective eDirectory attributes, extra 0s will be 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.

See