Data connectors API request times out when connecting to external Oracle database

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

I’m using the Cognos Analytics REST API to test connectivity to an external Oracle database before creating a data connector. The API request consistently times out after about 30 seconds, even though the database is accessible and responds quickly when tested with SQL Developer.

API call structure:


POST /api/v1/connectors/test
dbType: "oracle"
host: "oracle-prod.company.com"
port: 1521
serviceName: "PRODDB"

The timeout occurs during the connection test phase, before any actual data queries are executed. I’ve verified network connectivity, firewall rules allow traffic on port 1521, and the database credentials are correct. The same connection parameters work fine when creating the connector manually through the Cognos UI. Is there a way to increase the API timeout setting or diagnose what’s causing the delay?

2

The timeout could be related to Oracle listener configuration. When connecting via API, Oracle might be trying to resolve the client hostname which can cause delays if DNS is slow or misconfigured. Add (ENABLE=BROKEN) to your Oracle connection string to bypass some of these checks. Also verify that your Oracle listener is configured to handle connections from the Cognos API server’s IP address.

3

I’ve seen this behavior when the API server’s connection pool is exhausted or misconfigured. The 30-second timeout is the default for connection acquisition from the pool, not the actual database connection. Check your Cognos configuration for connection pool settings related to external data sources. You might need to increase the pool size or adjust the connection acquisition timeout.

4

API requests often use different network routes than UI connections. Check if your Cognos server’s API process has proper network access to the Oracle server. Sometimes the UI runs in a different network context with different routing rules. Verify DNS resolution from the API server and test with telnet to confirm port accessibility.

5

I’ve encountered this exact issue with Oracle connectors via the Cognos API. The problem involves all three focus areas and requires a comprehensive solution:

API Timeout Configuration: The 30-second timeout you’re seeing is the default connection test timeout. You need to adjust it in the API request:


POST /api/v1/connectors/test
{
  "dbType": "oracle",
  "host": "oracle-prod.company.com",
  "port": 1521,
  "serviceName": "PRODDB",
  "timeout": 60000,
  "connectionProperties": {
    "oracle.net.CONNECT_TIMEOUT": "60000"
  }
}

The timeout parameter is in milliseconds. Additionally, set the Oracle-specific connection timeout property.

Database Connection: The root cause is often Oracle’s connection validation process. The API performs more thorough validation than the UI:

  1. Configure Oracle connection pooling properties in your request
  2. Add testQuery: "SELECT 1 FROM DUAL" to speed up validation
  3. Include validationTimeout: 10000 to limit validation time
  4. Use connectionPooling: true to leverage existing connections

For Oracle-specific optimization:

  • Add oracle.jdbc.ReadTimeout: "30000" to connection properties
  • Set oracle.net.ns.SQLnetDef.TCP_CONNTIMEOUT: "30000" for network-level timeout
  • Include defaultRowPrefetch: "20" to improve initial connection performance

Data Refresh Optimization: The API connection test includes a data refresh simulation which can be slow:

  • Add skipDataRefresh: true parameter to test only connectivity without data sampling
  • Configure maxRows: 1 to limit the test query result set
  • Set queryTimeout: 30000 to prevent long-running test queries
  • Include useServerSideCursor: false for faster initial connection

Additional critical settings:

  1. Verify Cognos server’s tnsnames.ora includes your Oracle service
  2. Check that Oracle JDBC driver version matches your Oracle database version (minimum 19.x for Oracle 19c)
  3. Configure connection pool in Cognos: navigate to Configuration > Data Sources > Connection Pools and increase max connections
  4. Add ENABLE=BROKEN to Oracle connection string to disable dead connection detection during initial connect
  5. Set RECV_TIMEOUT=30 in sqlnet.ora on Cognos server to handle slow network responses

The complete connection string should look like:


jdbc:oracle:thin:@(DESCRIPTION=(ENABLE=BROKEN)(RECV_TIMEOUT=30)(CONNECT_TIMEOUT=60)(ADDRESS=(PROTOCOL=TCP)(HOST=oracle-prod.company.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=PRODDB)))

After implementing these changes, the API connection test should complete within the configured timeout period. Monitor the Cognos logs for any remaining connection issues - they often reveal Oracle-specific errors that aren’t surfaced in the API response.

6

I tested network connectivity with telnet and it’s working fine - can connect to port 1521 without issues. DNS resolution is also fast. The strange thing is that when I monitor the Oracle listener logs, I don’t see any connection attempts from the API requests, which suggests the timeout is happening before the connection even reaches the database.

7

This sounds like a JDBC driver issue. The Cognos API might be using a different Oracle JDBC driver version than the UI. Check which driver version is configured for API operations and ensure it’s compatible with your Oracle database version. Also, the default connection timeout for the data connectors API is usually 30 seconds, which matches what you’re seeing.