Perform JDBC Query Assertion

The Perform JDBC Query assertion is used to query an external database and use the query results later. The query results are stored in context variables created by this assertion.
Perform JDBC Query 
assertion is used to query an external database and use the query results later. The query results are stored in context variables created by this assertion.
To support using multivalued context variables in the JDBC Query assertion, you can build up the values of such a variable using the Manipulate Multivalued Variable Assertion.
Before you can perform a JDBC query, be sure a JDBC connection has been configured through Manage JDBC Connections.
 Do not create connections to the Gateway's MySQL database in general. Any query which writes to this database may render the Gateway inoperable.
The Perform JDBC Query Assertion is able to write to a variety of databases, even during a "Test". Ensure you are aware of the changes you are making as they are irreversible through the Set JDBC Connection Properties.
Unsupported Functionality
Note the following functionality is not currently supported by the Perform JDBC Query assertion:
  • Functions and Procedures are not supported on DB2. 
  • Functions and Procedures are supported on MySQL only when the database name is provided in the JDBC connection URL. Only supported when using the native MySQL driver and not DataDirect. 
  • Functions and Procedures with nested function calls are not supported.
  • PL/SQL blocks are not supported.
  • Calling overloading procedures or functions is not supported. 
  • Calling functions on MySQL Enterprise Edition using the Data Direct MySQL driver is not supported. 
  • Calling functions on Oracle via the native driver is not supported. 
  • Calling functions that return a Boolean with the DataDirect driver is not supported. 
  • Procedures and functions with lowercase names are not supported in Oracle.
  • Functions and procedures in Oracle that return NCLOBs or NBLOBs with values greater than 32KB cannot be called from the CA API Gateway. 
  • The Boolean parameters BOOLEAN and BOOL are not supported for the native MySQL driver. 
Context Variables Created by This Assertion
The Perform JDBC Query assertion sets the following context variables with the query results. The default 
 is "jdbcQuery" and can be changed in the assertion properties.
Returns the column name specified in the SQL query. This variable is created when one result set is returned.
If a stored procedure returns multiple result sets, then "
" will be added to the name of the variable.
Returns the number of records returned by the query (if using a SELECT query) or the number of records affected by the query (if using a non-SELECT query). This variable is always created.
Returns the XML results and is created when the 
Generate XML Results
 check box is selected in the properties.
This variable is set when there is more than one result set. This will occur when a called procedure returns more than one result set. This variable is not set if there is only a single result set.
: If OUT parameters are also set, they will be counted as one result set.
This variable is only set when there is more than one result set. If it is set, it contains the number of results in total across all result sets.
: If OUT parameters are also set, these will count as one row.
Returns the name of an OUT parameter from a procedure.
These variables are set by calling a function, with the name of the variable depending on the DBMS.
During policy consumption, the Gateway will create one multivalued context variable per column name. The number of values in the multivalued context variable corresponds to the number of records returned.
: To use names other than the SQL column names in the context variables, specify a mapping in the assertion properties.
Understanding Result Set Variables and Multiple Result Sets 
The Perform JDBC Query assertion supports multiple result sets. When a SQL query is run (for example, "select * from my_table"), a "result set" is returned. This result set is a logical set of rows, with each row made up of a series of columns. When you (for example) select column_a and column_b from my_table, and there are 10 rows in my_table, the result set will contain 10 rows with each row having 2 columns.
When you call a procedure (for example, "CALL MY_PROC"), it is possible that the procedure may return more than one result set (uncommon but possible). When this happens, the 
 variable will be set to "2" if two result sets were returned. If the result set #1 has 10 rows and result set #2 has 5 rows, then the 
 variable will be set to "15".
For example, consider a stored procedure that returns the following result sets:
test value8
test value3
test value6
test value5
These will be the context variables that will be returned (using the default prefix "jdbcQuery"):
test value8,test value3
test value6,test value5
Using the Assertion
  1. Do one of the following:
    • To add the assertion to the Policy Development window, see Adding an Assertion.
    • To change the configuration of an existing assertion, proceed to step 2 below.
  2. When adding the assertion, the 
    JDBC Query Properties
     automatically appear; when modifying the assertion, right-click 
    Perform JDBC Query
     in the policy window and select 
    JDBC Query Properties
     or double-click the assertion in the policy window. The assertion properties are displayed. 
    : Use the split bar between the "SQL Query" and "Context Variables Naming" panels to adjust the relative panels sizes to suit your needs.
  3. Configure the properties as follows:
    JDBC Connection
    Choose one connection
    Choose the JDBC connection being queried from the drop-down list. If the connection isn't visible in the list, you may type the connection name in the box. You may reference context variables.
     If context variables are specified in the connection name, it will not be possible to test the connection using the [
    ] button.
    For information on defining these connections, see Manage JDBC Connections.
    SQL Query (see "SQL Query Tips" below for more information)
    Convert Variables to Strings
    This check box determines how context variables are processed before being sent to the JDBC driver:
    • Select this check box to convert context variable contents into a string. For Multivalued Context Variables, their contents are concatenated into a single value, with the values delimited by commas.
    • Clear this check box to add each value of a multivalue or single-value context variable as is. When the policy containing the Perform JDBC Query assertion is executed, the Gateway will construct a statement with the list of parameters per each value of the multivalued context variable. This setting is the default.
    Query Timeout
    Enter the length of time the assertion will wait (in seconds) for a response to the query before timing out.
     (zero, which means use the Gateway-wide timeout, described below)
    s: (1) The value entered here overrides the Gateway-wide timeout setting defined by the 
     cluster property. The intent is to supply a shorter timeout value than the Gateway default. If a 
     timeout value is entered, it will be ignored and the Gateway default is used instead. (2) The JDBC driver may be configured to ignore all calls to set a timeout. If it is so configured, then it is not possible for the Gateway to control the timeout for any queries using that JDBC Connection. For more information, please consult your JDBC administrator. (3) The connection property 
     set to "true" may be needed when using the DataDirect drivers, to ensure that cancel requests to an unresponsive DBMS do not wait indefinitely.
    SQL query box
    Enter the SQL query to perform. Be cautious about any usages of non-Select DML queries. There is no transaction management within the Perform JDBC Query assertion. Once this assertion executes, the results are permanent in the DBMS, regardless of the policy logic surrounding the assertion. You may replace values in the SQL query with context variables if necessary, but other parts of the query must remain clear text. For example:
    These are valid queries:
       SELECT column_name FROM table WHERE username = 'Bob' AND password = '123password'
    SELECT column_name FROM table WHERE username = ${request.user} AND password = ${request.password}
    These are NOT valid queries:
       SELECT ${columnName} FROM ${tableName} WHERE username = 'Bob' AND password = '123password'
    SELECT ${columnName} FROM ${tableName} WHERE username = ${request.user} AND password = ${request.password}
    Do not enclose context variables within quotes. This applies to both SELECT and INSERT statements.
    The maximum length of the query is 4 KB (4096 characters).
    For more information about SQL queries, refer to "SQL Query Tips" and "Examples Using Procedures and Functions" within this topic.
    Click [
    ] to verify whether the SQL query is valid on the chosen JDBC connection. You must confirm that you understand the consequences of the query before proceeding. You will see a message stating whether or not the SQL query is valid.
     Testing is not possible if context variables are used in either the connection name or SQL query.
    Specify Schema
    This check box is available only under the following conditions:
    • A procedure or function call is defined in the SQL query text box.
    • The database is Oracle or SQL Server.
    Since the SQL query text box does not accept a schema value as part of the query, select this check box if you need to specify a schema and then enter the name of the schema in the adjacent field (must be a string without spaces or a single-value context variable).This value is passed to the JDBC driver to allow it to obtain the correct metadata from the database.
    If the SQL query requires a schema value for a procedure or function call, select this check box and then enter the name of the schema.You may reference context variables. If an object is contained within a package, then the SQL query itself should reference the package; for example:
    CALL mypackage.myfunction
    : You may need to specify a schema if your query fails with this error message: 
    "The database object either does not exist or the SQL query contains the object's schema".
    Context Variables Naming
    Save results to context variables
    This check box is located above the "Context Variables Naming" table. It is used to quickly enable or disable the saving of SQL results to the context variables specified in the table. Tip: CA recommends leaving this check box enabled. But consider disabling the saving of results if you are experiencing any memory issues.
    • Select this check box to operate the table normally: you can add, edit, or remove context variables and the SQL results will be saved to the variables specified.
    • Clear this check box to disable the saving of SQL results to context variables. This will disable the table and its editing controls. Any variable defined in the table will remain.
     (1) This check box operates independently of the Generate XML Result check box below. This allows you to populate the ${
    .xmlResult} variable even when opting to not save results to context variables. (2) The following context variables are always created, regardless of the "Save results to context variables" check box: 
    , and 
    . These variables were described under "Context Variables Created by This Assertion".
    This table allows you to map the SQL column headings to different names. This will change the names of the context variables created. For example, you require more descriptive variable names or if you require the names to conform to naming standards at your organization. For more information, see "Context Variables Created by This Assertion".
    The assertion supports multiple result sets. For more information, see the variables under "Context Variables Created by This Assertion" and "Understanding Result Set Variables and Multiple Result Sets" above.
    To add a mapping:
    Click [
    ]. The Context Variable Naming dialog appears.
    1. Enter the SQL 
      Column Label
      . For example: "Column1".
    2. Enter the mapping destination in 
      Variable Name
      . For example: "Cust_Acct".
    3. Click [
      ]. This will create a context variable named 
       instead of 
    To edit a mapping:
    1. Select a row and click [
    2. Modify the fields as necessary.
    3. Click [
    To remove a mapping
    1. Select a row and click [
    2. Click [
      ] to confirm. The naming will revert to the SQL column name.
    Enter a prefix that will be added to the context variables created by this assertion. This prefix will ensure uniqueness and will prevent the variables from overwriting each other when multiple instances of this assertion appear in a policy.
    You may reference context variables.
    The default prefix is 
    For more information, see "Context Variables Created by This Assertion".
    Other Settings
    Query Name
    Optionally enter a name for the query. This name is used only for display purposes in the Policy Manager.
    Maximum records per query
    Specify the maximum number of records to be returned from the SQL query. The default is 
    ; this can be changed using the 
    cluster property.
    Fail assertion if no results
    Select this check box if you want the assertion to fail if the SQL query returns no results.
    Clear this check box to prevent an assertion failure on no results. (The assertion can still fail for other reasons—for example, an invalid query.)
    Generate XML Result
    You can place the XML results of the JDBC query in a context variable.
    • Select this check box to store the XML string representation of every variable that was set, in the context variable ${
    • Clear this check box to not place the XML result in the context variable. Note: If ${
      .xmlResult} was populated previously, that content will remain.
    The following is an illustration of the structure of the XML result:
    <?xml version="1.0" encoding="UTF-8"?> <L7j:jdbcQueryResult xmlns:L7j=""> <L7j:row> <L7j:col name="COLNAME" type= "JAVA_DATA_TYPE">DATA_VALUE</L7j:col> .... </L7j:row> .... <L7j:jdbcQueryResult>
     The type shown is the data type 
     the XML results have been retrieved, not the actual database field type.
  4.  Click [
    ] when done.
SQL Query Tips
Keep in mind the following when entering an SQL query:
  • All variables created by the Perform JDBC Query assertion are multivalued. As such, it not possible to use array syntax when a suffix (i.e., selector) is present. Similarly, the variable "${jdbcQuery.return_value.
    }" will not work ("millis" can be any suffix). 
  • For a more detailed description, see "Multivalued Variables and Selectors" in Working with Multivalued Context Variables.
  • If a query requires a null value, use the context variable 
    . This built-in variable always resolves to null.
    : For Oracle databases, null values are equivalent to the empty string for VARCHAR types. Thus, another way to pass null values in Oracle is to pass the empty string. For example, both of the following would result in the same, when 
     takes a VARCHAR parameter:
     func nullFunction ""
     func nullFunction ${policy.nullvalue}
  • If a query contains SQL functions such as AVG( ), MAX( ), MIN( ), or nested SELECT, you should use the "AS" keyword to alias the returned value; for example:
    SELECT max(column_name) AS alias_name FROM table; SELECT column_name, (SELECT COUNT(*) FROM table1 WHERE conditions) AS alias_name FROM table2 WHERE conditions;
  • The SQL query cannot reference a schema. If you need to specify a schema value, select the 
    Specify Schema
     check box and enter the value there. 
     The ability to specify a schema is available only for Oracle and SQL Server.
     The Policy Manager 
     prevent you from entering a destructive SQL query. Such a query may corrupt your database irrevocably, even during testing.
  • If you see an error message similar to the following:
    Query testing failed; Data truncation: Incorrect datetime value:
    for column 'date' at row 1
    Check that there are no quotes surrounding the variables being passed on in the query. The Gateway automatically includes quotes as it processes the JDBC query. (By comparison, if you enter values directly into a SQL client, quotes must be added manually where required.)
Converting Variables into Strings
When writing an SQL Query that will reference context variables, you need to decide whether to use the value 
converted to a string
 or to use the 
raw value
. When the raw value is used, it will be passed directly to the JDBC driver, which will then convert it as needed, if the value is supported.
If the type of your variable is supported by the JDBC driver but you wish to use its string value while at the same time using the raw value of other variables, then you will need to create a new variable first to convert the raw variable into a string variable. For more information, see the "Convert Variables to String" option in the table above.
Using Functions and Stored Procedures
The query statement may contain calls to functions and stored procedures. The Gateway will determine what parameters a function or procedure require as input, output, or both, by examining the database metadata for it.
To call a stored procedure, use either the CALL OR EXEC keyword followed by the name of the procedure and then the parameters for the procedure. To call a function, use the FUNC keyword.
 The procedure name must not contain a hyphen ('-'), otherwise errors will occur.
The procedure/function parameters can be supplied as literal values, single or multivalued context variables. These parameters can either be encoded within parentheses (for example, "CALL myproc (param1, param2,.....,paramN)") or without (for example, "CALL myproc param1, param2, ....., paramN").
The only parameters for a procedure that must be supplied are the IN or INOUT parameters. The Gateway will automatically handle correctly registering any OUT or INOUT parameters based on the metadata for the procedure.
 There is no method/syntax to bind a context variable in the 'SQL Query' text field with an OUT variable from a procedure (or any other SQL statement).
To call a function, use the FUNC keyword followed by the name of the function and its parameters. The same rules for how to supply parameters apply to functions.
The output of a procedure or function will be set automatically after it has been invoked. The following table lists the names of the output variable for each database when calling a function. 
Default variable name
MySQL (only with native driver)
MS SQL Server
DB2 (not supported)
All OUT/INOUT parameters are handled automatically. When calling either a procedure or a function, consider the types of the input parameters. For types such as Date, Timestamp, and BLOB to work correctly, you need to ensure the context variable is off the correct type (Date/Time or byte []). Also ensure that the Perform JDBC Query assertion is configured to not convert variables to Strings. This allows the raw type to be passed the JDBC Driver, which can then provide any conversions it supports.
Note that when calling a nonexistent function or procedure, an exception is triggered only if the package name was also specified. For example, calling the nonexistent "mypackage.myfunction" will trigger an exception, but using "myfunction" will not trigger an exception. 
 For Oracle databases, the function and procedure names should be in uppercase and must not contain spaces or special characters. 
 It is possible to call a function or procedure containing lowercase characters in its name provided that it was not created using quotes around the name.
Messages Returned for Application Users
When a user is connected to the database as an application user (not as the schema owner), the following messages will be returned when a valid stored procedure or function is called with no parameters:
Message returned
"query testing failed: [l7tech][MySQL JDBC Driver][MySQL]No database selected
"query testing failed: [l7tech][Oracle JDBC Driver][Oracle]ORA-06564: object <object name> does not exist ORA-06512: at "sys.dbms_utility", line 156 ORA-06512: at line 1
MS SQL Server
"query testing failed: [l7tech][SQL Server JDBC Driver][SQLServer]Could not find stored procedure 'sp name'."
n/a (procedures and functions not currently supported in DB2)
Known Issues
Note the following known issues:
  • There is a known issue in MS SQL databases where the OUT parameter is treated as INOUT, which may result in parameters being set improperly. To avoid this, set all the parameters (IN and OUT) explicitly in the query. 
  • Stored procedures that use the OUT/INOUT parameter will always return a value "1" or greater for the variable 
    . This is because the parameters are always returned in the results and the assertion will never fail. However, the assertion is configured to fail if there are no results. 
  • Functions and procedures in Oracle that return NCLOBs or NBLOBs with values greater than 32KB cannot be called from the CA API Gateway. 
Examples Using Procedures and Functions
The following are some examples showing how to use keywords to execute functions and procedures. 
 The name of the output variable from a function is determined by the database; "outParameter" shown below is just an example.
  • Using the FUNC keyword to execute functions:
    FUNC [package].[function]([IN parameters]...)Sets jdbcQuery.[<outParameter>]
  • Using the EXEC keyword to execute procedures:
    EXEC [package].[procedure]([IN and INOUT parameters]...)
  • Using the CALL keyword to execute procedures:
    CALL [package].[procedure]([IN and INOUT parameters]...)Sets jdbcQuery.[<outParameter>] etc.
  • The 'Specify Schema' text field:
    using multi-valued context variables
    FUNC [package].[function](${vars},${singleVar})
    is equivalent to
    FUNC [package].[function](${vars.1},${vars.2}....,${singleVar})
The following are examples of how to call functions and procedures including:
  • How to access out values from procedures
  • How to access one or more output result sets from a procedure
  • How to supply non primitive types as parameters
The examples below will use the following variables:
    "multivalue1", "multivalue2"
     - This multivalued variable contains two values.
    ${myvar1} = "singlevalue1"
     - This variable contains a single value.
    ${myvar2} = "singlevalue2"
     - This variable contains a single value.
Calling a function
Example function definition on Oracle, which takes two parameters and returns a varchar2 value:
After calling this function there will be a single output variable set. The name of the return variable depends on the DBMS.
In this example, if the prefix configured is 'jdbcQuery', then the output variable will be:
Call with a multivalued variable:
FUNC MY_FUNC(${myvars})
Call with single values:
FUNC MY_FUNC(${myvar1}, ${myvar2})
Call without parenthesis:
FUNC MY_FUNC ${myvars} OR FUNC MY_FUNC ${myvar1}, ${myvar2}
Call with literal values:
FUNC MY_FUNC "input1", 'input2' OR FUNC MY_FUNC ("input1", 'input2')
Calling a procedure
Example procedure definition on Oracle, which takes 3 parameters, two of which are OUT parameters.
After calling this function there will be two output variables set. The default values depend on the name of the OUT variables. If jdbcQuery is the prefix in use the following context variables will be set:
 CALL and EXEC are interchangeable. Neither has any specific meaning; they both indicate that your SQL Query will call a procedure equally.
Call with a multivalued variable and a literal value:
CALL MY_PROC (${myvars}, "d value")
Call with a multivalued variable and a single variable:
EXEC MY_PROC (${myvars}, ${myvar1})
After all variables are processed, the number of values must match the number of expected input parameters. The position of the OUT parameters do not matter. After each context variable has been evaluated, the number of values resolved must match the number of input parameters. The values will be applied based on the order they were resolved.
In the above example the procedure would be called with the following runtime values:
multivalue1, multivalue2, singlevalue1
Call with single value variables and a literal value:
CALL MY_PROC (${myvar1}, ${myvar2}, 'd value')
As with functions the parenthesis around the parameters is optional.
Using date type parameters
Example function definition on Oracle:
create or replace FUNCTION DATE_FUNC (param1 IN DATE) RETURN DATE
Invoke the function with a literal string date value:
func DATE_FUNC '2012-12-31 23:55:40.99'
Invoke the function using a Date/Time typed context variable:
Set Context Variable date as Date/Timefunc DATE_FUNC ${date}
Invoke the function sing a String typed context variable:
${dateStr} = "2012-12-31 23:55:40.99"func DATE_FUNC ${dateStr}
If the [Convert Variables to String] check box is selected, then the Date/Time variable will be converted into a string using the default formatting for a Date/Time variable. In order to work with the format required by your DBMS, you may need to explicitly format the Date/Time when referencing it in a function:
Set Context Variable date as Date/Time func DATE_FUNC ${date.yyyy-MM-dd HH:mm:ss.SS}
In the above example the Date/Time is actually converted into a String when it is resolved at runtime. This is only needed when "Convert Variables to Strings' is being used.
Using numeric types
Example function definition on Oracle:
Invoke the function with a literal numeric value:
func NUMBER_FUNC 1243
Invoke the function using an Integer typed context variable:
Set Context Variable integer as Integer to 5func NUMBER_FUNC ${integer}
Invoke the function using a String typed context variable:
${integerStr} = "12345"func NUMBER_FUNC ${integerStr}
Using Boolean values
Example function definition on SQL Server:
Invoke the function with a literal boolean value:
func BOOL_FUNC 'false', 'true'orfunc BOOL_FUNC 0, 1orfunc BOOL_FUNC '0', '1'
Invoke the function with an Integer typed context variable:
Set Context Variable a as Integer to 0Set Context Variable b as Integer to 1func BOOL_FUNC ${a}, ${b}
Invoke the function with a String typed context variable
${falseStr} = "false"${trueStr} = "true"func BOOL_FUNC ${falseStr},${trueStr}or${aStr} = "0"${bStr} = "1"func BOOL_FUNC ${aStr},${bStr}
Using byte[ ] and BLOB values
Example function definition:
create or replace function BLOB_FUNC (a in BLOB) return BLOB
Invoke using a literal hexadecimal string:
func BLOB_FUNC '0123456789abcdef'
Invoke using a String typed context variable:
${hexString} = "0123456789abcdef"func BLOB_FUNC ${hexString}
Invoke using a String value converted into hex using the 'Encode / Decode' assertion:
Base16 Encode ${myvar1} into ${hexString}func BLOB_FUNC ${hexString}
Invoke using a byte[] context variable created via an Encapsulated Assertion:
Output a byte[] variable called ${bytes} from an encapsulated assertionfunc BLOB_FUNC ${bytes}
Using null values
It is possible to supply a null value in a SQL Query via a special built in variable. It is also possible to supply a null value via any existing variable or multivalued variable that may contain a null value.
The predefined variable 
supports passing null values into SQL queries. This variable will only pass a null value into a SQL query when the [Convert Variables to Strings] check box is not selected.
Example function definition
create or replace function NULL_FUNC (a in NUMBER, b in VARCHAR2) return VARCHAR2
Invoke a function using a literal null value:
func NULL_FUNC null 'asdf'
Invoke a function using a context variable with a null value:
func BOOL_FUNC ${policy.nullvalue}, 'b value'
Supported Data Types
These DBMS types are supported via DML statements via the 'SQL Query' text box. When calling procedures or functions they are supported as either input or output values.
DBMS type
Context variable type
Char, Varchar, etc
Numeric (Integer, Long, etc.)
Integer or String
String with hex values or a byte [] (not settable via the set context variable)
String or Integer when supported by the DBMS (see "Boolean values" below)
A String value can be used to supply a value for many DBMS types—for example, all character types including CLOB, Numeric types, Date and Timestamps (if formatted correctly) and Blob (via hex strings).
Boolean values
The following are support for Boolean values for each database type:
Not supported by either the Data Direct driver or the native driver
0 = false, everything else = true
 Boolean does not work with MySQL with the native driver.
SQL Server
0 or "false" = false, 1 or "true" = true
No boolean type
Date values
It is possible to supply the value for a DATE or TIMESTAMP using either literal values, String context variables, or Date/Time context variables (recommended for easiest integration).
The formatting required for a literal string value which represents a date or timestamp is determined by a number of factors, including DBMS settings and connection properties. The following default formats are known to work:
    yyyy-mm-dd hh:mm:ss.fffffffff
     (for example, '1999-01-31 24:24:24:123456')
    Other DBMS:
    yyyy-mm-dd hh:mm:ss
     (for example, '1999-01-31 24:24:24')
BLOB values
To supply a BLOB value in SQL Query, either a context variable or a literal value may be used. You can also supply binary data as hexidecimal strings either via a String context variable or via a literal hex value (for example, "0123456789abcdef"). 
Known Oracle issues
  • Function and procedure names must be in uppercase
  • Binary_Float, Binary_Double, Binary_Integer, Pls_Integer are returned as strings
  • Functions which return NCLOB values greater than 32kB are not supported
Caching Metadata
This section provides some insight on how the Perform JDBC Query assertion queries the database for metadata via the JDBC driver.
Function and Procedure metadata are cached. Caching can be performed:
  • eagerly, via a background caching task
  • lazily, when metadata is downloaded at message traffic processing time (MTPT)
 (1) When caching is configured and no data is available in the cache, then the metadata will be downloaded by the message processing thread. (2) When a nonexistent function or procedure is referenced, an exception will be issued and cached only if a package name has been specified. For example, calling the nonexistent function "mypackage.myfunction" will trigger an exception. But calling the nonexistent "myfunction" will not trigger an exception.
The caching metadata is enabled by the 
 cluster property set to "true" (default). Note that this property does not affect existing cached data and does not prevent background tasks from caching metadata.
For more information on the caching cluster properties, see JDBC Cluster Properties.
Background Caching of Metadata
The Gateway has a background task that downloads metadata eagerly, to ensure that it will be available at message traffic processing time. This eager caching will occur when the Gateway starts or when a policy is saved and activated; it is possible only when no context variables are used for the JDBC connection name or for the schema.
The caching of data is based on tracking a set of unique keys. Each key is defined as:
Connection name + Procedure or Function name + Optional Schema name
Therefore, if two Perform JDBC Query assertions reference the same procedure or function, only a single copy of that metadata will be kept. This background task runs every 10 minutes by default and is enabled or disabled via the 
cluster property. The task interval is controlled by the 
 cluster property.
To improve the processing time of this background task, you can increase the number of background processing threads when a large volume of metadata is being managed. The number of processing threads is 10 by default but can be increased up to 200 via the 
 cluster property. 
 Increase the concurrent threads with caution.
When the background task is not able to obtain the metadata for a unique key, an exception is cached. When a policy containing a Perform JDBC Query assertion for this cache key executes, it will return this cached exception (for example, the assertion may fail) repeatedly until it is cleared out by the cleanup background task, or it is corrected by the background task downloading the metadata.
The cleanup cache task runs every minute by default and is controlled by the 
 cluster property.
Life cycle of a managed key
After the background task starts to manage a unique key, at some point it may need to stop managing it. This can happen when no Perform JDBC Query assertion is referencing the unique key, however this may be difficult to track when context variables are used (for example, the unique key being referenced is unknown until context variables are resolved during runtime).
To resolve this problem, the Gateway can track how often the data from the cache for a particular key is used. If it is over the threshold, then the background task will stop managing that meta data.
The value is defined in the 
 cluster property. The default is 31 days and the value is configured in seconds.
Automatic Lazy Caching
If the background task is not enabled but caching is allowed, then all metadata downloaded will be cached to avoid it being downloaded a second time. The unique key for this metadata will be added to the list of keys to manage.
Cache Expiration
Any cached item can be configured to expire, to prevent outdated meta from causing the Perform JDBC Query assertion to fail.
The expiration should be longer than the background task refresh interval. It should also be longer than the estimated time it takes the job to complete.
Cache expiration is controlled via 
 cluster property. The default is 30 minutes and is configured in seconds.
Manually Populating the Cache
If caching is enabled, you can manually invoke it by clicking the [Test] button in the Perform JDBC Query assertion. This will cause metadata to be downloaded and cached, if not already present in the cache.
Cache Logging
Items which indicate that background tasks are running or working are logged at a FINE level. Items which indicate that the cache is working at message traffic processing time are logged at the FINEST level.
FINE logging is used for:
  • When metadata is downloaded and added to the cache.
  • When metadata could not be downloaded and an exception is added to the cache.
  • When the task to maintain metadata in the background starts and when it finishes.
  • When a unique key representing a procedure to manage metadata for is removed from the cache by the clean up task.
FINEST logging is used for:
  • Metadata cache hit
  • Metadata cache hit but data has expired (it is stale)
  • Metadata cache miss