Queries from API GW to oracle for auditing

Products

• CA API Gateway

Issue/Introduction

API Gateway 10.x - virtual appliance

Audit configured through audit sink policies to Oracle using a JDBC connection.

The audit sink policy are created using the default configuration.

Audit reports works properly.

Oracle DBAs did an assessment of the Oracle Db and they found a lot of strange queries.

The requested query is:

select * from audit_detail where 0=1

It seems to be a kind of keep-alive query. is it possible to remove it.

The DBAs found lots of this query which is impacting database performance

Environment

Release : 10.0

Component : API GATEWAY

Resolution

The select * from tablename where 0=1 is dynamically created in the driver .

It seems that Oracle dynamically generated the query and execute it when GW initiated update statement through JDBC Query Assertion for table with lots of columns.

It uses this query to get table's column count instead of the actual data and doing some validation. 

A workaround could be to use a native oracle jdbc driver . 

To add custom jdbc drivers to the gateway see 

https://techdocs.broadcom.com/us/en/ca-enterprise-software/layer7-api-management/api-gateway/10-1/security-configuration-in-policy-manager/tasks-menu-security-options/manage-jdbc-connections/update-the-jdbc-driver-support.html

Keep in mind that changing the driver could introduce other issue (performance , ) as it could behave differently than the original used driver.