SQL Datasource Mappings

From Informer 4 Wiki

Jump to: navigation, search

A datasource mapping represents a connection to any database on your network accessible from the web server. The following datasources are available as of the 4.0 release, with more coming in future maintenance releases.


To create a new datasource mapping, click the new datasource action in the action bar of the datasource home page. After choosing the 'SQL' datasource type, you will be prompted to enter the connection properties for that specific type of datasource. Each datasource type has different requirements for creating a connection. If you are unfamiliar with connection requirements for a specific database, please review the tables below and speak with your database administrator for the appropriate values.

Contents

Create a Microsoft SQL Server Datasource

  1. Click on the “Mappings” section.
  2. Click “Add Datasource”.
  3. Click "SQL"
  4. Select “Microsoft SQL Server” from the drop down combo box next to “Database Type”.
  5. Enter the connection properties:
    1. Name : This is the identifier of your datasource within Informer. It can contain any string of letters or numbers. For example “My Datasource”, “Database1”, and “Backup Account” are all acceptable.
    2. Description : This is the description of the datasource that will be displayed under the datasource name in the mappings section of Informer.
    3. Server Name : This is the host name, or IP Address of the machines that stores your datasource. The web server that runs Informer must be able to see this machine on your network.
    4. JDBC Port : This is the port that your database system uses to be accessed from Java. The default port for Microsoft SQL Server is 1433.
    5. Schema Name : This is the schema name of the tables within your database. Default for Microsoft SQL Server is “dbo” (without quotations).
    6. Minimum Pool Size : The minimum number of connections Informer should keep in the connection pool at all times. Default is 0. If this number is 1, for example, Informer will take a connection at startup and never let it go, thereby guaranteeing there will always be at least one connection available for Informer users.
    7. Maximum Pool Size : The maximum number of connections Informer is allowed to use at any one point in time. This has no direct correlation whatsoever to the number of Informer users logged into the system at one time, but may affect performance in some cases. Informer will not always maintain this number of connections, but is allowed to if necessary.
    8. Timeout : This is the amount of time an unused connection will remain in the connection pool before it is released. Default is 0 minutes. Having a value greater than 0 is particularly useful for enhancing performance, as there is some startup cost associated with creating a connection from scratch.
    9. Username : This is the Username Informer uses to connect to your database. It is recommended that you use the owner of the database to connect to your database.
    10. Password : The password associated with the username used to connect to your database.
    11. Additional Properties :
      1. For Microsoft SQL Server you must list the name of your database in this box, using the format “database=database_name” (without quotations). Where “database_name” is replaced with the actual name of your database.
      2. If you intend to connect to a named instance of Microsoft SQL then you must specify that here by adding “instance=instance_name”. Where “instance_name” is the name of your instance.
      3. If you need a trusted connection, then you must specify that here by adding "trustedConnection=true" on a separate line.


Create a Microsoft Access Datasource

  1. Create an ODBC data source that points to your Microsoft Access Database.
    1. Go to Control Panel -> Administrative Tools -> Data Sources (ODBC)
    2. Click “Add”
    3. Select “Microsoft Access Driver (.mdb)”
    4. Click “Finish”
    5. Enter a Data Source Name (this will be the same name you use for “Schema” below in connection properties)
    6. Under “Database” click “Select” and browse to your Microsoft Access file.
    7. Click “Ok”
  2. In Informer, click on the “Mappings” section.
  3. Click “Add Datasource”.
  4. Click "SQL"
  5. Select “Access 2003/2007” from the drop down combo box next to “Database Type”.
  6. Enter the connection properties:
    1. Name: This is the identifier of your datasource within Informer. It can contain any string of letters or numbers. For example “My Datasource”, “Database1”, and “Backup Account” are all acceptable.
    2. Description: This is the description of the datasource that will be displayed under the datasource name in the mappings section of Informer.
    3. Server Name: For Microsoft Access data sources you must have the ODBC driver installed on the web server that runs Informer. So this field will always be "localhost" (without quotations) when connecting to Microsoft Access.
    4. JDBC Port: This value can be left at 0, for Microsoft Access we use an ODBC connection that does not require a port number.
    5. Schema Name: Leave this blank.
    6. Minimum Pool Size: The minimum number of connections Informer should keep in the connection pool at all times. Default is 0. If this number is 1, for example, Informer will take a connection at startup and never let it go, thereby guaranteeing there will always be at least one connection available for Informer users.
    7. Maximum Pool Size: The maximum number of connections Informer is allowed to use at any one point in time. This has no direct correlation whatsoever to the number of Informer users logged into the system at one time, but may affect performance in some cases. Informer will not always maintain this number of connections, but is allowed to if necessary.
    8. Timeout: This is the amount of time an unused connection will remain in the connection pool before it is released. Default is 0 minutes. Having a value greater than 0 is particularly useful for enhancing performance, as there is some startup cost associated with creating a connection from scratch.
    9. Username: This is the Username Informer uses to connect to your database.
    10. Password: The password associated with the username used to connect to your database.
    11. Additional Properties: You must specify that you intend to connect to an access datasource by typing “access=yes” (without quotations) into additional properties. You must also specify the name of the ODBC data source (Step 1.e above). One another line type "database=" and then the name of the ODBC data source.


Create a Oracle Datasource

  1. Click on the “Mappings” section.
  2. Click “Add Datasource”.
  3. Click "SQL"
  4. Select “Oracle” from the drop down combo box next to “Database Type”.
  5. Enter the connection properties:
    1. Name: This is the identifier of your datasource within Informer. It can contain any string of letters or numbers. For example “My Datasource”, “Database1”, and “Backup Account” are all acceptable.
    2. Description: This is the description of the datasource that will be displayed under the datasource name in the mappings section of Informer.
    3. Server Name: This is the host name, or IP Address of the machines that stores your datasource. The web server that runs Informer must be able to see this machine on your network.
    4. JDBC Port: This is the port that your database system uses to be accessed from Java. The default port for Oracle is 1521.
    5. Schema Name: This is the schema name of the tables within your database.
    6. Minimum Pool Size: The minimum number of connections Informer should keep in the connection pool at all times. Default is 0. If this number is 1, for example, Informer will take a connection at startup and never let it go, thereby guaranteeing there will always be at least one connection available for Informer users.
    7. Maximum Pool Size: The maximum number of connections Informer is allowed to use at any one point in time. This has no direct correlation whatsoever to the number of Informer users logged into the system at one time, but may affect performance in some cases. Informer will not always maintain this number of connections, but is allowed to if necessary.
    8. Timeout: This is the amount of time an unused connection will remain in the connection pool before it is released. Default is 0 minutes. Having a value greater than 0 is particularly useful for enhancing performance, as there is some startup cost associated with creating a connection from scratch.
    9. Username: This is the Username Informer uses to connect to your database. It is recommended that you use the owner of the database to connect to your database.
    10. Password: The password associated with the username used to connect to your database.
    11. Additional Properties: For Oracle datasources you must list the name of your system identifier (SID) in this box, using the format “SID=oracle_sid” (without quotations). Where “oracle_sid” is replaced with the actual system identifier.


Create a MySQL Datasource

  1. Click on the “Mappings” section.
  2. Click “Add Datasource”.
  3. Click "SQL"
  4. Select “MySQL” from the drop down combo box next to “Database Type”.
  5. Enter the connection properties:
    1. Name: This is the identifier of your datasource within Informer. It can contain any string of letters or numbers. For example “My Datasource”, “Database1”, and “Backup Account” are all acceptable.
    2. Description: This is the description of the datasource that will be displayed under the datasource name in the mappings section of Informer.
    3. Server Name: This is the host name, or IP Address of the machines that stores your datasource. The web server that runs Informer must be able to see this machine on your network.
    4. JDBC Port: This is the port that your database system uses to be accessed from Java. The default port for MySQL is 3306.
    5. Schema Name: This is actually the name of your database in MySQL.
    6. Minimum Pool Size: The minimum number of connections Informer should keep in the connection pool at all times. Default is 0. If this number is 1, for example, Informer will take a connection at startup and never let it go, thereby guaranteeing there will always be at least one connection available for Informer users.
    7. Maximum Pool Size: The maximum number of connections Informer is allowed to use at any one point in time. This has no direct correlation whatsoever to the number of Informer users logged into the system at one time, but may affect performance in some cases. Informer will not always maintain this number of connections, but is allowed to if necessary.
    8. Timeout: This is the amount of time an unused connection will remain in the connection pool before it is released. Default is 0 minutes. Having a value greater than 0 is particularly useful for enhancing performance, as there is some startup cost associated with creating a connection from scratch.
    9. Username: This is the Username Informer uses to connect to your database.
    10. Password: The password associated with the username used to connect to your database.
    11. Additional Properties: You do not need any additional properties for MySQL.


Create a PostgreSQL Datasource

  1. Click on the “Mappings” section.
  2. Click “Add Datasource”.
  3. Click "SQL"
  4. Select “PostgreSQL ” from the drop down combo box next to “Database Type”.
  5. Enter the connection properties:
    1. Name: This is the identifier of your datasource within Informer. It can contain any string of letters or numbers. For example “My Datasource”, “Database1”, and “Backup Account” are all acceptable.
    2. Description: This is the description of the datasource that will be displayed under the datasource name in the mappings section of Informer.
    3. Server Name: This is the host name, or IP Address of the machines that stores your datasource. The web server that runs Informer must be able to see this machine on your network.
    4. JDBC Port: This is the port that your database system uses to be accessed from Java. The default port for PostgreSQL is 5432.
    5. Schema Name: This is the schema name of your tables ("public" for example).
    6. Minimum Pool Size: The minimum number of connections Informer should keep in the connection pool at all times. Default is 0. If this number is 1, for example, Informer will take a connection at startup and never let it go, thereby guaranteeing there will always be at least one connection available for Informer users.
    7. Maximum Pool Size: The maximum number of connections Informer is allowed to use at any one point in time. This has no direct correlation whatsoever to the number of Informer users logged into the system at one time, but may affect performance in some cases. Informer will not always maintain this number of connections, but is allowed to if necessary.
    8. Timeout: This is the amount of time an unused connection will remain in the connection pool before it is released. Default is 0 minutes. Having a value greater than 0 is particularly useful for enhancing performance, as there is some startup cost associated with creating a connection from scratch.
    9. Username: This is the Username Informer uses to connect to your database.
    10. Password: The password associated with the username used to connect to your database.
    11. Additional Properties : For PostgreSQL you must list the name of your database in this box, using the format “database=database_name” (without quotations). Where “database_name” is replaced with the actual name of your database.


Create a Derby Datasource

  1. Click on the “Mappings” section.
  2. Click “Add Datasource”.
  3. Click "SQL"
  4. Select “Derby” from the drop down combo box next to “Database Type”.
  5. Enter the connection properties:
    1. Name: This is the identifier of your datasource within Informer. It can contain any string of letters or numbers. For example “My Datasource”, “Database1”, and “Backup Account” are all acceptable.
    2. Description: This is the description of the datasource that will be displayed under the datasource name in the mappings section of Informer.
    3. Server Name: This is the host name, or IP Address of the machines that stores your datasource. The web server that runs Informer must be able to see this machine on your network.
    4. JDBC Port: This is the port that your database system uses to be accessed from Java. The default port for Derby is 1527.
    5. Schema Name: This is the schema name of the tables within your database.
    6. Minimum Pool Size: The minimum number of connections Informer should keep in the connection pool at all times. Default is 0. If this number is 1, for example, Informer will take a connection at startup and never let it go, thereby guaranteeing there will always be at least one connection available for Informer users.
    7. Maximum Pool Size: The maximum number of connections Informer is allowed to use at any one point in time. This has no direct correlation whatsoever to the number of Informer users logged into the system at one time, but may affect performance in some cases. Informer will not always maintain this number of connections, but is allowed to if necessary.
    8. Timeout: This is the amount of time an unused connection will remain in the connection pool before it is released. Default is 0 minutes. Having a value greater than 0 is particularly useful for enhancing performance, as there is some startup cost associated with creating a connection from scratch.
    9. Username: This is the Username Informer uses to connect to your database. It is recommended that you use the owner of the database to connect to your database.
    10. Password: The password associated with the username used to connect to your database.
    11. Additional Properties: For Derby datasources you must list the path to your derby database. Enter “database=path” (without quotations), where “path” is the absolute path to the derby database directory on the server.


Create a IBM UniVerse or IBM UniData Datasource

  1. Click on the “Mappings” section.
  2. Click “Add Datasource”.
  3. Click "SQL"
  4. Click “Create a new datasource”.
  5. Enter the connection properties:
    1. Name: This is the identifier of your datasource within Informer. It can contain any string of letters or numbers. For example “My Datasource”, “Database1”, and “Backup Account” are all acceptable.
    2. Description: This is the description of the datasource that will be displayed under the datasource name in the mappings section of Informer.
    3. Server Name: This is the host name, or IP Address of the machines that stores your datasource. The web server that runs Informer must be able to see this machine on your network.
    4. Account Path: This is the absolute path to your UniData or UniVerse account on the server.
    5. Username: This is the Username Informer uses to connect to your database. If you were to execute the LISTUSER command at your database prompt, all connections held by Informer would display as connected via this username. Entrinsik recommends you create a database user named 'informer' in order to easily identify which connections belong to Informer.
    6. Password: The password associated with the username used to connect to your database.
    7. Minimum Pool Size: The minimum number of connections Informer should keep in the connection pool at all times. Default is 0. If this number is 1, for example, Informer will take a connection at startup and never let it go, thereby guaranteeing there will always be at least one connection available for Informer users.
    8. Maximum Pool Size: The maximum number of connections Informer is allowed to use at any one point in time. This has no direct correlation whatsoever to the number of Informer users logged into the system at one time, but may affect performance in some cases. Informer will not always maintain this number of connections, but is allowed to if necessary.
    9. Timeout: This is the amount of time an unused connection will remain in the connection pool before it is returned back to the standard U2 connection manager. Default is 0 minutes. Having a value greater than 0 is particularly useful for enhancing performance, as there is some startup cost associated with creating a connection from scratch.
    10. Initialization Subroutines: Informer does not run any login script when it starts. If you have a login script that you use normally, you must list the subroutines run by that script here.


Create a IBM DB2 Datasource

  1. Click on the “Mappings” section.
  2. Click “Add Datasource”.
  3. Click "SQL"
  4. Select “IBM DB2” from the drop down combo box next to “Database Type”.
  5. Enter the connection properties:
    1. Name: This is the identifier of your datasource within Informer. It can contain any string of letters or numbers. For example “My Datasource”, “Database1”, and “Backup Account” are all acceptable.
    2. Description: This is the description of the datasource that will be displayed under the datasource name in the mappings section of Informer.
    3. Server Name: This is the host name, or IP Address of the machines that stores your datasource. The web server that runs Informer must be able to see this machine on your network.
    4. JDBC Port: This is the port that your database system uses to be accessed from Java. The default port for IBM DB2 is 50000.
    5. Schema Name: This is the schema name of the tables within your database.
    6. Minimum Pool Size: The minimum number of connections Informer should keep in the connection pool at all times. Default is 0. If this number is 1, for example, Informer will take a connection at startup and never let it go, thereby guaranteeing there will always be at least one connection available for Informer users.
    7. Maximum Pool Size: The maximum number of connections Informer is allowed to use at any one point in time. This has no direct correlation whatsoever to the number of Informer users logged into the system at one time, but may affect performance in some cases. Informer will not always maintain this number of connections, but is allowed to if necessary.
    8. Timeout: This is the amount of time an unused connection will remain in the connection pool before it is released. Default is 0 minutes. Having a value greater than 0 is particularly useful for enhancing performance, as there is some startup cost associated with creating a connection from scratch.
    9. Username: This is the Username Informer uses to connect to your database. It is recommended that you use the owner of the database to connect to your database.
    10. Password: The password associated with the username used to connect to your database.
    11. Additional Properties: For IBM DB2 you must list the name of your database in this box, using the format “database=database_name” (without quotes). Where “database_name” is replaced with the actual name of your database.


Adding a Informix Datasource

  1. Click on the “Mappings” section.
  2. Click “Add Datasource”.
  3. Click "SQL"
  4. Select “IBM Informix” from the drop down combo box next to “Database Type”.
  5. Enter the connection properties:
    1. Name: This is the identifier of your datasource within Informer. It can contain any string of letters or numbers. For example “My Datasource”, “Database1”, and “Backup Account” are all acceptable.
    2. Description: This is the description of the datasource that will be displayed under the datasource name in the mappings section of Informer.
    3. Server Name: This is the host name, or IP Address of the machines that stores your datasource. The web server that runs Informer must be able to see this machine on your network.
    4. JDBC Port: This is the port that your database system uses to be accessed from Java. The default port for Informix is 1533.
    5. Schema Name: This is the schema name of the tables within your database.
    6. Minimum Pool Size: The minimum number of connections Informer should keep in the connection pool at all times. Default is 0. If this number is 1, for example, Informer will take a connection at startup and never let it go, thereby guaranteeing there will always be at least one connection available for Informer users.
    7. Maximum Pool Size: The maximum number of connections Informer is allowed to use at any one point in time. This has no direct correlation whatsoever to the number of Informer users logged into the system at one time, but may affect performance in some cases. Informer will not always maintain this number of connections, but is allowed to if necessary.
    8. Timeout: This is the amount of time an unused connection will remain in the connection pool before it is released. Default is 0 minutes. Having a value greater than 0 is particularly useful for enhancing performance, as there is some startup cost associated with creating a connection from scratch.
    9. Username: This is the Username Informer uses to connect to your database. It is recommended that you use the owner of the database to connect to your database.
    10. Password: The password associated with the username used to connect to your database.
    11. Additional Properties: For Informx datasources you must list the database name and the service name. Enter “database=databasename” (without quotations), where “databasename” is the name of the Informix database. You must also enter on a new line “informixserver=servicename” (without quotations), here “servicename” is the name of the default database server, this attribute is listed in your instance’s ONCONFIG file.
Retrieved from "http://www.entrinsik.com/support/informer/40wiki/index.php/SQL_Datasource_Mappings"
Personal tools