Create a New Data Connection
Creating new data connections is simple with Insite Analytics. You can create a connection to data on many systems such as IBM i, MySQL, MS SQLServer, MS Access, Oracle (12g), and others.
To create a new data connection press the Add Data Connection button 
at the top of the data connections panel.
The New Data Connection panel opens on the right.
Fill in the options (described below) to define the data connection, and press Save 
to continue.
Options
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.
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 (below). 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.
BRMS
Syntax Example:
jdbc:as400://<HOSTNAME>;prompt=false;extended metadata = true;translate binary = true;libraries=<LIB_LIST>
Where:
<HOSTNAME> = The name of IBM i host.
<LIB_LIST> = Specify libraries to override the user's default library list.
DB2/400
Syntax Example:
jdbc:as400://<HOSTNAME>;prompt=false;extended metadata = true;translate binary = true;libraries=<LIB_LIST>
Where:
<HOSTNAME> = The name of IBM i host.
<LIB_LIST> = Specify libraries to override the user's default library list.
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.
<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.
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.
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 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.
This credential can be overridden by defining an Override Credential in the query definition.
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.
See Also: