Creating a New Data Connection

Data connections link HelpSystems One HelpSystems One Analytics to your enterprise data. Create a connection to access data from a variety of systems such as IBM i, MySQL, MS SQLServer, MS Access, Oracle (12g), Linux, Unix, Windows and others. Once created, similar single data connections (type and schema) can be bundled together by creating a Data Connection Group.

Creating a Single Data Connection

  1. Select Custom Data Sources \ Databases \ Connections from the Analytics home screen. A list of database connections displays. See Working with Data Connections for more information.

  2. 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 appears.

  3. Fill in the options (described below) to define the data 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.

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

    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.

  4. Press Save to create the connection.

Creating a Data Connection Group

  1. To create a new data connection group press the drop-down option at the top of the data connections panel and select the Add Data Connection Group button . The New Data Connection Group panel appears.

  2. Fill in the options (described below) to define the group connection.

    Name - Enter a name for the data connection group. 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).

    Select Data Connection - Use the drop-down to select data connections to add to the connection group. The list contains all defined data connections until a connection is selected. Once selected the list will filter out all dissimilar connections based on driver type.

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

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

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

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

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

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

  3. Press Save to create the connection.

 

Related Topics