Connection settings

On the Settings tab of a database connection point, specify these properties:

Property Description
Database driver: Select one of the supported database drivers. See the table with the Supported database drivers shown after this table.
Database Select how to specify the address of the database:
  • Basic - specify properties for the database. The properties to be specified depend on the selected Database Type. The URL to connect to the database is built automatically.
  • Advanced - specify the URL to connect to the database. Use this if you must use advanced settings that are not shown when selecting Basic.
Host Name Specify the host name of the server that hosts the database. This field is only enabled if Basic is selected.
Port Number Specify the number of the port that must be used to connect to the database. This field is only enabled if Basic is selected and database driver is not DB2/400.
Schema Name Specify the name of the database schema to be used. For Stored Procedure type, this field is only enabled if Basic is selected and the database driver is not Oracle.
Service Name Specify the name of the Oracle service name to be used. This field is only enabled if Basic is selected and the Database Type is Oracle.
SID Select SID (deprecated) check box if Service Name should be used as schema id instead of Service Name. This field is only enabled If Basic is selected and the Database Type is Oracle.
URL Specify the URL of the database that contains the In-box and Outbox tables. The URL is dependent of the database type. See the Supported Database table for details.

This field is only editable only if Advanced configuration is selected.
User Name: Specify the user name to connect to the database.
Note: When using SQL Server with Windows authentication, specify a valid Windows domain username instead of a SQL Server user name.
Password: Specify the password to connect to the database.

This table lists supported database types and the URL formats:

Supported Database drivers URL format
SQL Server (jTDS) Select this option to connect to a Microsoft SQL server database using the jTDS driver. When using SQL Server authentication on a default instance: jdbc:jtds:sqlserver://hostname:port/schemaname

When using Windows authentication: jdbc:jtds:sqlserver://hostname:port/schemaname;domain=WindowsDomain

When using a named instance there are two formats; the first format is more consistent with the URL format of the default SQL instance:

  • jdbc:jtds:sqlserver://hostname/schemaname;instance=NamedInstance
  • jdbc:jtds:sqlserver://hostname;instance=NamedInstance;databaseName=schemaname

When specifying the time-out and unit is second:

jdbc:jtds:sqlserver://server:port/database;loginTimeout=60;socketTimeout=60
SQL Server (Microsoft) Select this option to connect to a Microsoft SQL server database using the Microsoft JDBC Driver 4.0 driver. We recommend that you use this option when connecting to a Microsoft SQL Server setup in a cluster mode.

The URL is specified in this format:

jdbc:sqlserver://hostname\instanceName:port;property=value;property=value

For example, when using SQL Server authentication on a default instance with the MultiSubnetFailover property of a cluster setup:

jdbc:sqlserver://hostname:1433;databaseName=schemaName;MultiSubnetFailover=true

When specifying login time-out use the loginTimeout property. Unit is second, default value is 15 s.

When specifying lock time-out use the lockTimeout property. Unit is millisecond.

For a complete connection URL specification of this driver, see the Microsoft documentation at:

https://msdn.microsoft.com/en-us/library/ms378428(v=sql.110).aspx

Oracle jdbc:oracle:thin:@hostname:port number/ServiceName
DB2

jdbc:db2://hostname:port/schemaname

When specifying time-out and unit is second:

jdbc:db2://coralpib19a.torolab.ibm.com:56733/eComHQ:enableSysplexWLB=true;loginTimeout=60;blockingReadConnectionTimeout=60;
DB2/400

jdbc:as400://hostname;naming=system;libraries=schemaname;prompt=false

Postgres (PPAS) jdbc:postgresql://hostname:port/schemaname?stringtype=unspecified

When specifying time-out and unit is second:

jdbc:postgresql://localhost/test?user=fred&password=secret&ssl=true&loginTimeout=60&socketTimeout=60

For a complete connection URL specification of this driver, see the Postgres documentation at:

https://jdbc.postgresql.org/documentation/93/connect.html

To use this driver in ION, the parameter ?stringtype=unspecified is required.

Custom

Select any imported custom database driver. Use the URL format as documented by the driver provider.

AnySQL modeler type cannot work with all custom driver types. Only Oracle custom driver can be used. To have the custom Oracle driver available for AnySQL modeler the driver type name must start with Oracle_.

After setting the properties, click Test. You are informed whether the test is successful and if the connection details are correct.