Data connectors API request times out when connecting to external databases

BA/BI IBM Cognos Analytics
, , , , , , , ,
1

I’m using the Cognos Analytics REST API to create and test database connections programmatically. The API request consistently times out when trying to connect to our external Oracle database. Manual connection through the UI works fine with the same credentials.

My API timeout configuration:

POST /api/v1/connections/test
{"type": "jdbc", "url": "jdbc:oracle:thin:@dbhost:1521:orcl", "timeout": 30}

The database connection is definitely reachable - I can connect via SQL Developer and through the Cognos UI. But the API request just hangs for about 60 seconds then returns a timeout error. The data refresh is taking way too long through API compared to UI. Any ideas what could cause this API-specific timeout issue?

2

I’ve dealt with this before - it’s usually DNS resolution causing the delay. When you use a hostname in the JDBC URL, the API server needs to resolve it. If DNS is slow or the hostname isn’t in the local hosts file, you’ll get timeouts. Try using the IP address instead of hostname in your connection URL. Also add connection pooling parameters to your JDBC URL: ‘?oracle.net.CONNECT_TIMEOUT=10000’ to set a 10 second connection timeout at the driver level.

3

Excellent diagnosis - DNS resolution was indeed the culprit! Here’s what fixed the API timeout issue:

Solution for API Timeout:

  1. Database Connection - Use IP Address:
POST /api/v1/connections/test
{
  "type": "jdbc",
  "url": "jdbc:oracle:thin:@192.168.1.50:1521:orcl",
  "driverClass": "oracle.jdbc.OracleDriver"
}

Replacing the hostname with IP eliminated the DNS lookup delay entirely.

  1. API Timeout - Driver-Level Configuration: Added Oracle-specific timeout parameters to the JDBC URL:

jdbc:oracle:thin:@192.168.1.50:1521:orcl?oracle.net.CONNECT_TIMEOUT=10000&oracle.jdbc.ReadTimeout=15000

This sets 10-second connection timeout and 15-second read timeout at the driver level, which overrides server defaults.

  1. Data Refresh - Connection Pooling: Enhanced the connection definition to use pooling for better performance:
{
  "type": "jdbc",
  "url": "jdbc:oracle:thin:@192.168.1.50:1521:orcl?oracle.net.CONNECT_TIMEOUT=10000",
  "driverClass": "oracle.jdbc.OracleDriver",
  "pooling": {
    "enabled": true,
    "minConnections": 2,
    "maxConnections": 10,
    "connectionTimeout": 10
  }
}

Root Cause Analysis: The UI connections were fast because Cognos caches DNS lookups for UI sessions. API requests don’t benefit from this cache, so each API call triggered a fresh DNS lookup. Our corporate DNS server had a 30-second timeout for unresolved queries, which explained the exact delay we were seeing.

Additional Recommendations:

  • If you must use hostnames, add them to the Cognos server’s hosts file (/etc/hosts on Linux, C:\Windows\System32\drivers\etc\hosts on Windows)
  • Monitor connection pool usage through Cognos admin console to ensure pools aren’t exhausted
  • Set reasonable timeout values - too short and you’ll get false failures, too long and you’ll wait unnecessarily
  • For production deployments, use connection pooling to avoid establishing new connections for each API request

After implementing these changes, API connection tests complete in under 2 seconds, matching UI performance. The data refresh through API is now consistently fast.

4

This sounds like a connection pool issue. The API might be trying to establish a new connection from scratch each time, while the UI reuses pooled connections. Check if your database has connection limits and whether the API is hitting them. Also, Oracle databases sometimes have firewall rules that block connections from certain processes. Verify that the Cognos API service account has network access to the database.