SQL Providers

The following are the options available when defining a SQL provider:

• Classpath: Classpath pointing to the JDBC driver code.
• Class loader id: The id of the class loader to use; this is an important parameter if your custom agent will have multiple SQL providers as sharing one class loader allows the SQL providers to share one database connection; the value is usually a string equal to the cutsom agent id to guarantee a unique name in the scope of this monitoring session.
• JDBC driver class: The name of the JDBC driver class.
• Username: Authenticate using this username.
• Password: Authenticate using this password.
• JDBC connection string: Connection string used to establish the database connection.
• SQL query: The SQL query to execute for data collection.

The followings are JDBC connection strings for some common databases:

• The Oracle "thin" driver connection string looks like "jdbc:oracle:thin:@[hostname]:[port]:[oracle sid]"
• The DB2 "App" driver connection string looks like "jdbc:db2:[db_name]"
• The DB2 "Net" driver connection string looks like "jdbc:db2://[hostname]:[port]/[db_name]"
• The Pointbase driver connection string looks like "jdbc:pointbase:server://[hostname]:[port]/[db_name]"
• The MS/SQL driver connection string looks like "jdbc:microsoft:sqlserver://[hostname]:[port]/[db_name]"
• The Cloudscape driver connection string looks like "jdbc:cloudscape:[db_name];create=true;autocommit=false"
• The PostgreSQL driver connection string looks like "jdbc:postgresql://[hostname]:[port]/[db_name]"
• Sybase connection strings take the form "jdbc:sybase:Tds:[hostname]:[port]/[db_name]"
• The MySQL Connector/J driver connection string looks like "jdbc:mysql://[hostname]:[port]/[db_name]"

In order to install an SQL agent you must follow these steps:

• To install the agent: Navigate to "Tools" - "Agent Installer" - "Install" - "Custom Agent Installation" - "Agent Template SQL" which will install an agent which has the follwing configuration data available:
  • Username: the username used to connect to the database
  • Password: the password used to connect to the database
  • Database: the name of the database to connect to
  • Port: the port to connect to
  • JDBCDriverClass: the class name for the JDBC driver
  • Classpath: Comma separated list of paths to code supporting the JDBC driver
• Add a SQL provider to your agent created above. Navigate to "Tools" > "Provider Managers". In this dialog select your agent and press "Add". A dialog opens where you define your provider. Select the provider with the name "SQL", you probably want to disable the "Optional" checkbox so that your provider is selected by default when you activate your custom agent. The following is an example of configuration data for an SQL provider:
  • Classpath: the classpath to the JDBC driver jars e.g. C:/MyDB/mydbdriver.jar or {home}/lib/mydbdriver.jar if you want to copy the driver into IxoraRMS_install_folder/lib folder
  • Classloader id: the id of the classloader to use for this provider; the value would be MyCustomAgent
  • JDBC driver class name: com.mysql.jdbc.Driver
  • Username: {agent.Username} to inherit the value in the agent's configuration
  • Password: {agent.Password} to inherit the value in the agent's configuration
  • JDBC connection string: jdbc:mysql://{host}:{agent.Port}/{agent.Database}

Now you need to add a parser that knows how to process the data returned by the SQL provider. Select the "Parser" tab and press the "New" button in order to start defining your parsing rules.(the way you define this depends on what data your SQL returns)

You should be able to activate and run your agent once you do the last step.