Getting error with accessing the tables using ODATA
Article ID: 104953
Updated On:
Products
Clarity PPM SaaS
Clarity PPM On Premise
Issue/Introduction
The user is getting error with accessing the tables DWH_INV_TEAM , DWH_INV_TASK, DWH_INV_TASK, DWH_INV_ASSIGNMENT, DWH_INV_TASK_HIERARCHY using ODATA connector
The error is listed below.
Error: 0xC02090F5 at Data Flow Task, DWH_INV_TEAM [1134]: The DWH_INV_TEAM was unable to process the data. The remote server returned an error: (401) Unauthorized.
Error: 0xC02090F5 at Data Flow Task, DWH_INV_TASK [1023]: The DWH_INV_TASK was unable to process the data. The remote server returned an error: (401) Unauthorized.
The error is listed below.
Error: 0xC02090F5 at Data Flow Task, DWH_INV_TEAM [1134]: The DWH_INV_TEAM was unable to process the data. The remote server returned an error: (401) Unauthorized.
Error: 0xC02090F5 at Data Flow Task, DWH_INV_TASK [1023]: The DWH_INV_TASK was unable to process the data. The remote server returned an error: (401) Unauthorized.
Environment
CA PPM 15.3 and above ( PPM SAAS Only)
Cause
The problem comes when you try to fetch more than 10000 records at one request. Analyzing the logs we found
The schema logs shown this
02-Jul-2018 20:07:22.747 CONFIG [http-nio-8080-exec-2] [xxxddwhhdp][oracle][sc51762ddatasource][[email protected]][Ij3T+xjUbAo+qi6I].[odata_api][getEntities] Error condition
02-Jul-2018 20:07:57.656 CONFIG [http-nio-8080-exec-7] [xxxddwhhdp][oracle][sc51762ddatasource][[email protected]][Ij3T+xjUbAo+qi6I].[odata_api][getEntities] Error condition
02-Jul-2018 20:07:22.747 CONFIG [http-nio-8080-exec-2] [xxxddwhhdp][oracle][sc51762ddatasource][[email protected]][Ij3T+xjUbAo+qi6I].[odata_api][getEntities] Error condition
org.odata4j.exceptions.ForbiddenException: The top request exceeds the maximum entities per page limit of 10000
We co related with access logs for the same time, the stack in 1st line was success as it pulled 25 records and the stack in 2nd and 3rd line was failure because it pulled 3000 records.
10.xx.xx.9 - - [02/Jul/2018:20:07:56 +0000] "GET /api/odata/xxxxDataSource/DWH_INV_TEAM?$top=25 HTTP/1.1" 200 3592 8
10.xx.xx.9 - - [02/Jul/2018:20:07:57 +0000] "GET /api/odata/xxxxDataSource/DWH_INV_TEAM?$top=30000 HTTP/1.1" 401 187 0
10.xx.xx.9 - - [02/Jul/2018:20:07:57 +0000] "GET /api/odata/xxxxDataSource/DWH_INV_TEAM?$top=30000 HTTP/1.1" 403 232 3
The schema logs shown this
02-Jul-2018 20:07:22.747 CONFIG [http-nio-8080-exec-2] [xxxddwhhdp][oracle][sc51762ddatasource][[email protected]][Ij3T+xjUbAo+qi6I].[odata_api][getEntities] Error condition
02-Jul-2018 20:07:57.656 CONFIG [http-nio-8080-exec-7] [xxxddwhhdp][oracle][sc51762ddatasource][[email protected]][Ij3T+xjUbAo+qi6I].[odata_api][getEntities] Error condition
02-Jul-2018 20:07:22.747 CONFIG [http-nio-8080-exec-2] [xxxddwhhdp][oracle][sc51762ddatasource][[email protected]][Ij3T+xjUbAo+qi6I].[odata_api][getEntities] Error condition
org.odata4j.exceptions.ForbiddenException: The top request exceeds the maximum entities per page limit of 10000
We co related with access logs for the same time, the stack in 1st line was success as it pulled 25 records and the stack in 2nd and 3rd line was failure because it pulled 3000 records.
10.xx.xx.9 - - [02/Jul/2018:20:07:56 +0000] "GET /api/odata/xxxxDataSource/DWH_INV_TEAM?$top=25 HTTP/1.1" 200 3592 8
10.xx.xx.9 - - [02/Jul/2018:20:07:57 +0000] "GET /api/odata/xxxxDataSource/DWH_INV_TEAM?$top=30000 HTTP/1.1" 401 187 0
10.xx.xx.9 - - [02/Jul/2018:20:07:57 +0000] "GET /api/odata/xxxxDataSource/DWH_INV_TEAM?$top=30000 HTTP/1.1" 403 232 3
Resolution
Customers needs to retrieve less than 10000 records per request as 10000 is the limit set and cannot be increased
Additional Information
Note: If you are using Microsoft SSIS you need to use $top to fetch the records else the default is 25 and too many iteration can invalidate the session token.
Feedback
Yes
No
Powered by
