Unexpected Results from Find and Reserve Query on SQL Server DB

book

Article ID: 224104

calendar_today

Updated On:

Products

CA Test Data Manager (Data Finder / Grid Tools)

Issue/Introduction

When creating a Find and Reserve Data model, sometime it returns unexpected results compared to the query run in SQL Server against the source DB tables.

For example, have Orders and OrderItems tables and the relationship between the two tables is Orders.OrderID=OrderItems.OrderID. 

In Find and Reserve model, defined columns in the Orders table as model keys, with the OrderItems table as a secondary component of the Find and Reserve model. Defined the relationship in the data model, and using it in the Find and Reserve model. This allows the end user to query on orders, or check "Show Related Tables" and show all orders and their order items (one row for each component associated to an order). However, seeing unexpected data (only 1 row) return from Find and Reserve model when queried in Self-Service Catalog where as the same query returned 4 rows against the DB tables. 

Cause

The issue is happening with the missing cached data. When the cache update is in the process and if any errors happen during the refresh or a query is run in that time for creating Find and Reserve model, unexpected results will be returned.

For example, Data Prefetch is set to Periodic and the property tdmweb.findReserveService.sync.delay=60 is set in application.properties, the data is going to refreshed every hour and the cashed data is going to be deleted before the refresh. If the data is huge and it takes 20 minutes or so to do the refresh and not all the data is available and returns unexpected results.

Environment

Release : 4.9.x

Component : TDM Portal

Resolution

Recommended to change the property tdmweb.findReserveService.sync.delay=240 instead of 60 or 120 minutes, so the data data refresh happens every 4 hours instead of every hour or two.

Also, for the large tables, recommended to use the Data Prefetch option as OFF, so Source tables can be used instead of the cached table and don't run into the refresh issue and unexpected results issue.

For the smaller tables use the  Data Prefetch option On Demand/Periodic.

Additional Information

Data Prefetch documentation is available at https://techdocs.broadcom.com/us/en/ca-enterprise-software/devops/test-data-management/4-9/provisioning-test-data/configure-test-data-reservation-service/configure-dynamic-test-data-reservation-service/data-prefetch.html

Below are the Data Prefetch options provided when creating the Find and Reserve Model.
. On Demand:
Data Prefetch On Demand enables F&R model data synchronization into TDM database. Data will be synchronized immediately after model creation. Further synchronizations must be started manually. Tables from multiple databases can be included in the model.
. Periodic:
Data Prefetch Periodic enables F&R model data synchronization into TDM database. Data will be synchronized immediately after model creation. Synchronization will be performed every day automatically and can also be started manually. Tables from multiple databases can be included in the model.
. OFF:
Data Prefetch OFF enables querying of source database directly, without the need to bring in a copy of the data into TDM database. However this functionality supports only a single data source and needs an additional reservation table to be created in the source database in the next wizard step.
NOTE: Data Prefetch option OFF is not compatible with On Demand or Periodic options. It can't be changed after definition of F&R model.
 
 

Attachments