SQL Adapter

Executes custom SQL queries or function calls supported against single instance databases and instances on Real Application Clusters (RAC).

Properties

  • SQL Query - The SQL query to execute. Normal SQL statements should not be semi-colon terminated. For example, SQL Query = "select a.ename, (select count(*) from emp p where p.mgr=a.empno) directs from emp a". PL/SQL statements are also supported, and if used, the "Out Parameter Position" and "Out Parameter Type" properties should be populated.

  • SQL Query File - A SQL query file. Note that only one of "SQL Query" or "SQL Query File" should be used. For example, %scriptsDir%/myquery.sql. You can upload custom files to the agent, which will be accessible under the %scriptsDir% directory.

  • Transpose Result - Transpose the SQL query result.

  • Bind Variables - Declare bind variables used in normal SQL statements here. For example, if the SQL Query = "select a.ename from emp a where a.mgr = :1", then you can declare the bind variable as Name=1, Value=Bob.

  • Out Parameter Position - The bind variable used for PL/SQL output. Only integers can be specified.

    Example: If the SQL Query is

         DECLARE         l_output1 NUMBER;         l_output2 NUMBER;     BEGIN         .....         OPEN :1 FOR             SELECT l_output1, l_output2 FROM dual;     END;

    you can set Out Parameter Position = 1, and Out Parameter Type = SQL_CURSOR

  • Out Parameter Type - The SQL type of the PL/SQL output parameter. See comment for Out Parameter Position

Credentials

  • Database Credentials - The credential used to connect to the database.

Example

Overriding default monitoring credentials by creating and using a custom monitoring credential set for database target.

Creating host credentials for the database target type:

> emcli create_credential_set -set_name=myCustomDBCreds -target_type=oracle_database -auth_target_type=oracle_database -supported_cred_types=DBCreds -monitoring -description='My Custom DB Credentials'

When you go to the Credentials page of the Metric Extension wizard, and choose to "Specify Credential Set" for Database Credentials, you will see "My Custom DB Credentials" show up as an option in the drop down list.

Note that this step only creates the Monitoring Credential Set for the host target type, and you need to set the credentials on each target you plan on deploying this metric extension to. You can set credentials from Enterprise Manager by going to Setup, then selecting Security, then selecting Monitoring Credentials. Alternatively, this can be performed using the Enterprise Manager Command Line Interface. 

> emcli set_monitoring_credential -target_name=db1 -target_type=oracle_database -set_name=myCustomDBCreds -cred_type=DBCreds -auth_target_type=oracle_database -attributes='DBUserName:myusername;DBPassword:mypwd'