New Database Connection Pane

How to get here

Select Custom Data Sources \ Databases \ Connections from the Analytics home screen. Once the list of database connections displays press the Add button .

What it does

Use this pane to set values for a new database connection. See Creating a New Data Connection.

Options (single connection)

Name - Enter a name for the data connection. Only alphanumeric characters (a-z, A-Z, 0-9)—not including punctuation or symbols—are allowed. Spaces are not allowed (an underscore _ is a good substitute).

Driver - Select the driver based on the type of database you wish to use.

Data Connection Groups - (optional) Use the drop-down to add the connection to an existing group connection. New connections will be created individually and added to the selected group. Only group connections containing similar connection types based on the driver are displayed. Leave this blank to create an individual connection only.

URL - This is a string that defines the location of the server and the database you want to connect to. The syntax of the string is very specific depending on the type of database defined by the Driver value specified. Review the list below for the syntax of each Driver (database) type.

Access

Syntax Example:

jdbc:access://<COMPUTER_NAME>/<PATH>/<FILE_NAME>?maxScanRows=<NN>

Where:

<COMPUTER_NAME> = The name of PC, as identified to the network where the Access or Excel file is located. This value is mandatory.

<PATH> = The full path (including drive letter) to the directory containing Excel or Access files. On Windows, there must be a "Share" to the path on <COMPUTER NAME> that the IBM i profile running the job has authority to.

<FILE_NAME> = Add the specific name of the file to use with the connection. This value is mandatory.

<NN> = The maximum number of rows to return.

DB2/400

Syntax Example:

jdbc:as400://<HOSTNAME>;prompt=false;extended metadata = true;translate binary = true;libraries=<LIB_LIST>

Where:

<HOSTNAME> = The name of the IBM i host.

<LIB_LIST> = Specify libraries to override the user's default library list.

DB2/LUW - Use this to connect to DB2 installed on Linux, Unix, or Windows (LUW) servers.

Syntax Example:

jdbc:db2://:[host][:port][/databaseName]:retrieveMessagesFromServerOnGetMessage=true;
useJDBC4ColumnNameAndLabelSemantics=false;
cursorSensitivity=2[;property=value...]

Where:

host = The name of the database server.

port = The port for the specified server.

Derby (Apache)

Syntax Example:

jdbc:derby:[host][:port][/databaseName][;create=false]

Where:

<HOSTNAME> = The name of IBM i host.

<LIB_LIST> = Specify libraries to override the user's default library list.

Excel

Syntax Example:

jdbc:excel://<COMPUTER_NAME>/<PATH>/<FILE_NAME>?maxScanRows=<NN>

Where:

<COMPUTER_NAME> = The name of PC, as identified to the network where the Access or Excel file is located. This value is mandatory.

<FILE_NAME> = Add the specific name of the file to use with the connection. This value is mandatory.

<NN> = The maximum number of rows to return.

MS SQL Server

Syntax Example:

jdbc:sqlserver://<HOSTNAME>:<PORTNUMBER>;<INSTANCENAME>

Where:

<HOSTNAME> = The network resolvable name or address of the SQLServer host.

<PORTNUMBER> = The TCP/IP port for the server. The listener port number is usually 1433.

<INSTANCENAME> = The network resolvable name or address of the SQL Server instance to be used. Omit the instance name portion if you are connecting to the default (usually MSSQLServer) instance.

MySQL

Syntax Example:

jdbc:mysql://<HOSTNAME>:<PORTNUMBER>/<DATABASENAME>

Where:

<HOSTNAME> = The network resolvable name or address of the MySQL host.

<PORTNUMBER> = The TCP/IP port for the server. The listener port number is usually 3306.

<DATABASENAME> = The name of the database to be accessed.

Oracle 11g/12g

Syntax Example:

jdbc:oracle:thin:@<HOSTNAME>:<PORTNUMBER>/<SIDNAME>

Where:

<HOSTNAME> = The network resolvable name or address of the Oracle host.

<PORTNUMBER> = The TCP/IP port for the server. The listener port number is usually 1521 and is defined in the network\admin\listener.ora file in the oracle product library.

<SIDNAME> = The name of the service (often ORCL).

PostgreSQL

Syntax Example:

jdbc:postgresql://<HOSTNAME>:<PORTNUMBER>/<DATABASENAME>

Where:

<HOSTNAME> = The network resolvable name or address of the PostgreSQL host.

<PORTNUMBER> = The TCP/IP port for the server. The listener port number is usually 3306.

<DATABASENAME> = The name of the database to be accessed.

Timezone - Specify the timezone to override the server default for query date/time columns. This is especially useful for group connections to ensure all date/time data from the different servers defined by the group connection are consistent.

Edit Permissions - By default, permission to edit a data connection is limited to the Admin user and the creator of the data connection. This option defines who else is allowed to edit the data connection.

Users: Choose any of the listed users to allow edit rights to the data connection.

Groups: Choose any of the listed groups to allow edit rights to the data connection.

Permit All - Select whether to allow all users modification access to the data connection.

Yes: Select to allow all users to edit the data connection.

No: Default. Select to allow only the Admin, creator, and any specified users or groups to edit the data connection.

Execute Permissions - By default, permission to use a data connection is limited to the Admin user and the creator of the data connection. This option defines who else is allowed to use the data connection.

Users: Choose any of the listed users to allow usage rights to the data connection.

Groups: Choose any of the listed groups to allow usage rights to the data connection.

Permit All - Select whether to allow all users usage access to the data connection.

Yes: Select to allow all users to use the data connection.

No: Default. Select to allow only the Admin, creator, and any specified users or groups to use the data connection.

Credentials - For the host/server defined in the URL above, enter a valid Username and Password of the user that will process requests by this data connection.

Username: Enter a valid username to process requests on the selected server.

Password: Enter the password for the user.

Test: Press the Test button to test the connection.

NOTE:

This credential can be overridden by defining an Override Credential in the query definition.

TIP:

If you want to define different users (with different levels of access) for connection to the same server it is better to create multiple data connections to the server—each with a different defined credential. Be sure to use descriptive names to differentiate between the multiple connections. Although the user can be overridden in the query, this should be used sparingly. In the case where passwords expire you will only have to update the data connection vs. updating many queries that have user overrides.