Connecting Jaspersoft and IBM Cognos Analytics to YugabyteDB with the JDBC Smart Driver

YugabyteDB may not appear as a native, certified data source in every BI and reporting tool, but that does not mean those tools cannot connect to it.

Because YugabyteDB’s YSQL API is PostgreSQL-compatible, many reporting platforms can connect to YugabyteDB using a PostgreSQL JDBC driver. For simple testing, the standard PostgreSQL JDBC driver is often enough. For production, especially when connecting to a multi-node or multi-region YugabyteDB universe, the YugabyteDB JDBC Smart Driver is usually the better choice.

In this tip, we will look at how to connect two common enterprise reporting tools to YugabyteDB:

● TIBCO Jaspersoft / JasperReports Server
● IBM Cognos Analytics

The focus here is on self-managed or on-prem deployments where you can control the JDBC driver files used by the BI server.

Why the YugabyteDB JDBC Smart Driver?

The standard PostgreSQL JDBC driver works because YugabyteDB speaks the PostgreSQL wire protocol through YSQL.

However, the standard PostgreSQL driver does not understand that YugabyteDB is a distributed database. It connects to the host you give it, or to an external load balancer if you place one in front of the cluster.

The YugabyteDB JDBC Smart Driver adds cluster-awareness. It can discover YugabyteDB nodes and spread new connections across the cluster.

Here is the practical difference:

Driver	Best For	Driver Class	JDBC URL Example
PostgreSQL JDBC Driver	Quick testing, simple lab setup, or external load balancer	`org.postgresql.Driver`	`jdbc:postgresql://yb-node1.example.com:5433/yugabyte`
YugabyteDB JDBC Smart Driver	Production, multi-node clusters, topology-aware routing, read-replica targeting	`com.yugabyte.Driver`	`jdbc:yugabytedb://yb-node1.example.com:5433/yugabyte?load-balance=true`

Need the JAR File?

If you need help downloading the latest YugabyteDB JDBC Smart Driver JAR file, see this previous YugabyteDB Tip:

👉 Download the Latest YSQL JDBC JAR File

Recommended Reporting User

Before configuring the BI tool, create a database user with read-only access to the reporting objects.

Example:

				
					CREATE USER bi_reporter
WITH PASSWORD 'change_me';

GRANT CONNECT
ON DATABASE yugabyte
TO bi_reporter;

GRANT USAGE
ON SCHEMA public
TO bi_reporter;

GRANT SELECT
ON ALL TABLES IN SCHEMA public
TO bi_reporter;

ALTER DEFAULT PRIVILEGES
IN SCHEMA public
GRANT SELECT
ON TABLES
TO bi_reporter;

For production, scope the grants to only the schemas, tables, or views needed by the reporting tool.

Connection Option 1: Standard PostgreSQL JDBC Driver

Use this option when you want the simplest possible configuration.

Driver class:

				
					org.postgresql.Driver

JDBC URL:

				
					jdbc:postgresql://yb-node1.example.com:5433/yugabyte

With username and password in the URL:

				
					jdbc:postgresql://yb-node1.example.com:5433/yugabyte?user=bi_reporter&password=change_me

This is usually the easiest way to prove basic connectivity.

Connection Option 2: YugabyteDB JDBC Smart Driver

Use this option when you want YugabyteDB-aware connection load balancing.

Driver class:

				
					com.yugabyte.Driver

Basic Smart Driver URL:

				
					jdbc:yugabytedb://yb-node1.example.com:5433/yugabyte?load-balance=true

Multiple initial contact points:

				
					jdbc:yugabytedb://yb-node1.example.com:5433/yugabyte?load-balance=true

Topology-aware example:

				
					jdbc:yugabytedb://yb-node1.example.com:5433/yugabyte?load-balance=true&topology-keys=aws.us-east-1.*

Read-replica-preferred example:

				
					jdbc:yugabytedb://yb-node1.example.com:5433/yugabyte?load-balance=prefer-rr

Read-replica-only example:

				
					jdbc:yugabytedb://yb-node1.example.com:5433/yugabyte?load-balance=only-rr

Use read-replica routing only when the reporting workload is compatible with read-replica behavior and the reports do not require the absolute freshest committed data from the primary cluster.

Production tip:
For heavy reporting workloads, consider using read replicas or a dedicated reporting universe through xCluster. BI tools can generate expensive SQL, and you probably do not want large report scans competing with your primary OLTP workload.

Jaspersoft Setup: Jaspersoft Studio

Jaspersoft Studio is often used by report developers to design and test reports locally before publishing them to JasperReports Server.

Option A: Use the PostgreSQL JDBC Driver

1. Open Jaspersoft Studio.
2. Go to Repository Explorer.
3. Right-click Data Adapters.
4. Select Create Data Adapter.
5. Choose Database JDBC Connection.
6. Select PostgreSQL from the JDBC driver list.
7. Set the JDBC URL:
- jdbc:postgresql://yb-node1.example.com:5433/yugabyte
8. Enter the database user and password.
9. Click Test Connection.
10. Save the data adapter.

Option B: Use the YugabyteDB JDBC Smart Driver

1. Download the YugabyteDB JDBC Smart Driver JAR file.
2. Open Jaspersoft Studio.
3. Go to Repository Explorer.
4. Right-click Data Adapters.
5. Select Create Data Adapter.
6. Choose Database JDBC Connection.
7. Open the Driver Classpath tab.
8. Add the YugabyteDB JDBC Smart Driver JAR file.
9. Set the driver class manually:
- com.yugabyte.Driver
10. Set the JDBC URL:
- jdbc:yugabytedb://yb-node1.example.com:5433,yb-node2.example.com:5433,yb-node3.example.com:5433/yugabyte?load-balance=true
11. Enter the database user and password.
12. Click Test Connection.
13. Save the data adapter.

Jaspersoft Setup: JasperReports Server

For server-side scheduled reports, dashboards, and shared reporting content, configure the JDBC driver and data source in JasperReports Server.

Option A: Use the PostgreSQL JDBC Driver

1. Log in to JasperReports Server as an administrator.
2. Go to View → Repository.
3. Right-click the folder where the data source should live.
4. Select Add Resource → Data Source.
5. Choose JDBC Data Source.
6.. Select the PostgreSQL JDBC driver.
7. Set the JDBC URL:
- jdbc:postgresql://yb-node1.example.com:5433/yugabyte
8. Enter the database username and password.
9. Test the connection.
10. Save the data source.

Option B: Use the YugabyteDB JDBC Smart Driver

1. Log in to JasperReports Server as an administrator.
2. Make sure JDBC driver uploads are enabled.
3. Go to View → Repository.
4. Right-click the folder where the data source should live.
5. Select Add Resource → Data Source.
6. Choose JDBC Data Source.
7. If the YugabyteDB Smart Driver is not listed, upload the YugabyteDB JDBC JAR file.
8. Use this driver class:
- com.yugabyte.Driver
9. Use a Smart Driver JDBC URL:
- jdbc:yugabytedb://yb-node1.example.com:5433,yb-node2.example.com:5433,yb-node3.example.com:5433/yugabyte?load-balance=true
10. Enter the database username and password.
11. Test the connection.
12. Save the data source.

IBM Cognos Analytics Setup: On-Prem / Self-Managed

IBM Cognos Analytics can also connect to YugabyteDB through PostgreSQL-compatible JDBC connectivity.

For on-prem or self-managed Cognos, the basic flow is:

1. Place the JDBC driver JAR file in the Cognos drivers directory.
2. Restart the query service.
3. Create a data server connection.
4. Use either the PostgreSQL JDBC driver or the YugabyteDB JDBC Smart Driver.

Option A: Use the PostgreSQL JDBC Driver

Copy the PostgreSQL JDBC driver JAR file into the Cognos drivers directory.

Example path:

				
					<cognos_install_location>/drivers

Then restart the Cognos query service.

In Cognos Analytics:

1. Go to Manage.
2. Open Data server connections.
3. Create a new data server connection.
4. Choose PostgreSQL if available.
5. Set the JDBC URL:
- jdbc:postgresql://yb-node1.example.com:5433/yugabyte
6. Enter the database username and password.
7. Test the connection.
8. Save the connection.

Option B: Use the YugabyteDB JDBC Smart Driver

Copy the YugabyteDB JDBC Smart Driver JAR file into the Cognos drivers directory.

Example:

				
					<cognos_install_location>/drivers

Restart the Cognos query service after adding the JAR.

Then create a new data server connection.

Use the YugabyteDB driver class:

				
					com.yugabyte.Driver

Use a Smart Driver JDBC URL:

				
					jdbc:yugabytedb://yb-node1.example.com:5433,yb-node2.example.com:5433,yb-node3.example.com:5433/yugabyte?load-balance=true

For a reporting workload that should prefer read replicas:

				
					jdbc:yugabytedb://yb-node1.example.com:5433/yugabyte?load-balance=prefer-rr

For a workload that should stay in a specific region:

				
					jdbc:yugabytedb://yb-node1.example.com:5433/yugabyte?load-balance=true&topology-keys=aws.us-east-1.*

After creating the connection, test it from Cognos before publishing reports or packages that depend on it.

Cloud Note

Cloud note:
IBM Cognos Analytics on Cloud Hosted and Jaspersoft cloud offerings may have different rules for uploading custom JDBC drivers, restarting services, and reaching private database endpoints. The YugabyteDB JDBC Smart Driver is easiest to use when you control the BI runtime, the driver directory, and the network path to the YugabyteDB nodes.

Performance Considerations for BI Reporting

BI tools can be dangerous if pointed directly at an operational database without guardrails.

Jaspersoft and Cognos users often create reports that look harmless in the UI but generate large joins, full table scans, broad aggregations, or repeated queries behind the scenes.

YugabyteDB is a distributed SQL database optimized for operational workloads. It can absolutely serve reporting queries, but you need to design for that access pattern.

1. Use Report-Friendly Views

Create database views that expose only the columns and joins needed by the reporting tool.

Example:

				
					CREATE SCHEMA IF NOT EXISTS reporting;

CREATE VIEW reporting.customer_order_summary AS
SELECT
    c.customer_id,
    c.customer_name,
    c.region,
    o.order_id,
    o.order_date,
    o.order_status,
    o.total_amount
FROM app.customers c
JOIN app.orders o
  ON o.customer_id = c.customer_id;

2. Index the Report Predicates

If most reports filter by date, customer, tenant, region, or status, make sure those filters are backed by useful indexes.

Example:

				
					CREATE INDEX idx_orders_report_date_status
ON app.orders (
    order_date,
    order_status
);

3. Avoid Unbounded Report Queries

A report query like this can be painful:

				
					SELECT *
FROM app.orders;

A better pattern is to require a time range or tenant filter:

				
					SELECT *
FROM app.orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days';

4. Use Read Replicas for Heavy Read Traffic

If reporting queries are large or frequent, consider directing BI traffic to read replicas using the YugabyteDB JDBC Smart Driver.

Example:

				
					jdbc:yugabytedb://yb-node1.example.com:5433/yugabyte?load-balance=prefer-rr

5. Consider a Dedicated Reporting Copy for Heavy Analytics

For very heavy OLAP-style workloads, consider using xCluster to maintain a separate read/reporting universe.

That keeps expensive reporting activity away from the primary application workload.

Quick Troubleshooting Checklist

Problem	What to Check
Driver class not found	Confirm the JAR file is uploaded or copied into the BI server driver directory.
Connection test fails	Check host, port `5433`, database name, username, password, firewall rules, and TLS settings.
Smart Driver does not load balance	Confirm the URL starts with `jdbc:yugabytedb://`, uses `com.yugabyte.Driver`, and includes `load-balance=true` or another supported load-balance value.
Reports are slow	Check the generated SQL, add indexes, reduce result size, use views, and consider read replicas or a reporting universe.
Cloud BI tool cannot reach the cluster	Validate private networking, allow lists, DNS, TLS certificates, and whether the cloud BI service allows custom JDBC drivers.

Final Takeaway

YugabyteDB does not need to be listed as a native data source in every BI tool to be usable from those platforms.

For quick tests, connect through the standard PostgreSQL JDBC driver.

For production reporting against a distributed YugabyteDB universe, use the YugabyteDB JDBC Smart Driver when your BI platform allows custom JDBC drivers. It gives you better alignment with the distributed nature of YugabyteDB, including cluster-aware load balancing, topology-aware routing, and read-replica-aware connection options.

BI reporting reminder:
Treat BI tools with respect. They can generate very expensive SQL. Use scoped reporting users, report-friendly views, proper indexes, and read replicas or dedicated reporting universes when the workload grows.

Have Fun!

Before I move to Dallas, I’ve been cleaning out old stuff and found this prom classic.

That’s me on the right, looking like I was about to either pick up my date or audition for a wedding band.

Had to tell my best friend, Doug Haniford, I’m moving, which was sad, so I’m cheering him up the only way a true friend would:
public embarrassment.