Data connectors API request times out when connecting to external database with large datasets

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

I’m using the Cognos Analytics Data Connectors API to establish connections to our external Oracle database. The API request consistently times out after 30 seconds when attempting to connect to tables with large row counts (5M+ rows). Smaller tables connect successfully without issues.

The API timeout occurs during the initial connection validation:


POST /api/v1/data-connectors/validate
{"type": "oracle", "connectionString": "jdbc:oracle:thin:@dbserver:1521:PROD",
 "credentials": {...}, "timeout": 30000}

I’ve verified the database connection works fine from other tools, and the database server performance is normal. The data refresh process through the Cognos UI also works, though it takes several minutes. Is there a way to increase the API timeout for database connections or optimize the connection validation?

2

The issue might not be the API timeout but how the connection validation works. When validating a data connector, Cognos may be querying metadata or running sample queries that are slow on large tables. Check if your Oracle tables have proper indexes on key columns. Also, make sure statistics are up to date - run ANALYZE TABLE on your large tables. Poor statistics can cause the Oracle optimizer to choose inefficient execution plans for metadata queries.

3

If your timeout parameter isn’t being respected, there’s likely a server-side timeout configuration overriding it. Check the Cognos configuration files for data connector timeout settings. There’s usually a maximum timeout limit configured at the server level that can’t be exceeded by API requests. Your Cognos administrator needs to adjust this in the configuration.

4

I’ve dealt with similar timeout issues. The problem is often network latency between Cognos and the database server, especially if they’re in different data centers. The validation process involves multiple round trips - authentication, metadata retrieval, sample data fetch. Each round trip adds latency. Can you test the API call from a Cognos server that’s closer to your Oracle database? Also check if there are any firewalls or network security appliances between them that might be adding delays.