Enable JDBC Spy Logs for WSO2 API Manager 3.x.x with Log4jdbc
2 min readJul 23, 2020
If you want to take a peek in to database queries executed when using wso2 API Manager you can accomplish that with the help of Log4jdbc. Log4jdbc is a Java JDBC driver that can log SQL and/or JDBC calls.
Step 1 : Download log4jdbc driver and place it.
- Download the latest log4jdbc driver from here.
- Copy the driver in to <API_HOME>/repository/components/lib/ directory.
Step 2 : Configure deployment.toml with new driver
Navigate to <API_HOME>/repository/conf/ and do the following changes in deployment.toml file.
- Change JDBC URL like below by adding :log4jdbc.
url = “jdbc:log4jdbc:mysql://localhost:3306/AM_DB”- Add the driver
driver = “net.sf.log4jdbc.DriverSpy”
Step 3 : Configure log4j2.properties
As Wso2 API Manager 3.x.x versions are using log4j2 for logging, Following configurations needs to be added for log4jdbc.
- Open log4j2.properties file in <API_HOME>/repository/conf/ directory.
- Add the following lines.
logger.jdbc_logger.name=jdbc.sqlonly
logger.jdbc_logger.level=INFO- In the loggers section append jdbc_logger with a comma.
Other than sqlonly option you can use following options also.
- jdbc.sqltiming: Logs time taken by each SQL to execute.
- jdbc.audit: Logs all the activities of the JDBC (Except Result sets).
- jdbc.resultset: Includes the audit and plus Result set activities.
- jdbc.connection: Connection details like opening and closing the database connections will be logged.
That’s it! sql logs will be displayed as follows,
[2020–07–23 14:50:33,399] INFO — sqlonly SELECT API.API_ID FROM AM_API API WHERE API.API_PROVIDER = ‘admin’ AND API.API_NAME = ‘niron_api’
AND API.API_VERSION = ‘1’[2020–07–23 14:50:33,400] INFO — sqlonly SELECT CAST( SUM(RATING) AS DECIMAL)/COUNT(RATING) AS RATING FROM AM_API_RATINGS WHERE API_ID
=1 GROUP BY API_ID
