Data Prefetch

The Data Prefetch feature allows Test Data Engineers to find test data faster. When Data Prefetch is active, data from data sources used by test data models is cached in a TDM database. Testers' queries are evaluated in the cache, which reduces query time. For more information about Test Data Model creation, see .
tdm49
The Data Prefetch feature allows Test Data Engineers to find test data faster. When Data Prefetch is active, data from data sources used by test data models is cached in a TDM database. Testers' queries are evaluated in the cache, which reduces query time. For more information about Test Data Model creation, see Create and Edit a Find & Reserve Model.
Data Prefetch is supported for MS SQL and Oracle databases. See Supported Data Sources for a complete list of supported data sources.
When creating a new Data Model, you choose between three options:
  • data prefetch on demand (data synchronization)
  • periodic data prefetch (data synchronization)
  • off (no data synchronization)
A Data Model cannot switch betwen Synchronization and non-Synchronization after creation.
Overview
Data Prefetch is recommended for test data models that use multiple data sources, especially for smaller data sets with many entities and relationships between them. When Data Prefetch is active, data from all your data sources across all your environments is cached in a TDM database. When this data is cached, the query runs on the cached data.
When you disable Data Prefetch for a data model, all cached data is deleted.
Data Prefetch synchronizes data for all environments and data sources used by test data models.
For example, if your model uses 3 tables in 2 environments, 6 tables are created in the prefetch database (3 for each environment). See diagram below.
FR diagram
FR diagram
No Data Prefetch -- Create a Reservation Table
If you create a data model with
Data Prefetch: Off
(no data synchronization), TDM queries your source database directly, without prefetching a copy of the data into the TDM database. TDM does not change your source data, but the Test Data Engineer must create an additional reservation table in the source database in the second wizard step. TDM uses the Reservation Table to track reserved rows in the root table. The Reservation Table shares its primary key with the root table to identify each reserved row. This functionality supports only a single data source.
To create the Reservation Table:
  1. Click
    Modelling, Find & Reserve, Create Model
    and create a data model with
    Data Prefetch: Off
    .
  2. Select a table and click the right arrow button to add the first table to your data model.
    The
    Reservation Table
    dialog opens.
  3. All models which use the same root table must use the same Reservation Table. Perform
    one
    of the following options:
    • Click
      Add new Reservation Table
      , define a new table name, accept the defaults, and click
      Save
      .
    • Select a previously created text column which serves as reservation ID for this table.
  4. Click
    Finish
    .
    The data model is created. A new icon appears in the Find&Reserve table row with the tooltip "Click to View Reservation Table Details"
Table creation can fail if the Test Data Engineer lacks permissions. In this case, TDM displays additional manual steps in the dialog:
  1. Download the provided SQL files, one file per environment.
  2. Send these files to your Database Administrator to execute them.
  3. In the Portal, click
    Data Model, Actions, Rescan
    .
    The new table appears.
  4. Drill down into the Data Model again where you were previously interrupted due to lacking permissions.
    Now the reservation table exists.
  5. Select the $RESERVATION_ID column on the table that contains the relevant ID. Other, grayed-out tables are not reservation tables.
    The
    Save
    button becomes active.
  6. Click
    Save
    and
    Finish
    .
If Testers attempt to use an environment that does not have a reservation table, a validation error appears that tells them to contact the TDM Admin or Test Data Engineer to create the table. Testers themselves do not have permissions to create this table.
To create a missing table, follow these steps:
  1. Drill down into the Data Model, and look for a yellow warning icon "Reservation table is missing in Your_Database_Name" in the affected table row.
  2. Click the
    View Reservation Table Details
    button for the table.
  3. Click
    Propagate Table to All Environments
    .
    TDM creates the reservation table for this environment.
Configuration file
In a standard installation the Test Data Manager configuration file is located at
C:\Program Files\CA\CA Test Data Manager Portal\conf\application.properties.
Make changes to this file to adjust the following:
Synchronization triggers
CA TDM synchronises fetched data with the original data sources one of two ways:
Periodically
, and
On Demand
. You can choose this behaviour for F&R models on the
Models for Find & Reserve
page. See Create and Edit a Find & Reserve Model.
Periodic synchronization
Data Prefetch Periodic enables F&R model data synchronization into the TDM database. Data is synchronized immediately after model creation. TDM synchronizes data every day automatically, and you also have the option to start it manually. You can include tables from multiple databases in the model.
By default, every 2 hours CA TDM propagates all changes (deletions, updates, inserts) from original data sources to cached data. You can modify the sync period by editing the following line of the configuration file:
# delay between synchronization runs (in minutes)
tdmweb.findReserveService.sync.delay=120
In addition to the periodical updates, the fetch process starts (or restarts) on the following triggers:
  • When the toggle is switched on for the first time.
  • When models, environments or data sources are changed.
  • When you click 'Fetch Data' from the
    Options
    menu on the
    Models for Find & Reserve
    page. See Create and Edit a Find & Reserve Model.
On Demand Synchronization
Data Prefetch On Demand enables Find & Reserve model data synchronization into the TDM database. Data is synchronized immediately after model creation. You start further synchronizations manually. You can include tables from multiple databases in the model.
CA TDM only synchronizes Find & Reserve Models with On Demand Data Prefetch Synchronization when you click 'Fetch Data' from the
Options
menu on the
Models for Find & Reserve
page. See Create and Edit a Find & Reserve Model.
Specify location for prefetched data
By default, the Data Prefetch feature caches data in a Repository database (GTREP). The size of this database depends on the volume of data cached. For larger volumes of data, consider whether to use gtrep database as the storage for fetched data. You can specify a different database to store fetched data by changing the configuration file.
For the cleanest switch between prefetched databases, follow the Best Practice Guide below.
If you change the configuration file before you disable Data Prefetch on models, data is not removed from the original prefetch database, and it is necessary to perform a manual cleanup.
You can manually specify where CA TDM stores prefetched databases in the configuration file. The relevant values to change are highlighted in red (replace all of '${...}') and the possible replacement values are in bold on the preceding commented line:
# e.g
jdbc:oracle:thin:@//tdmserver:1521/tdm.ca.com
or
jdbc:sqlserver://tdmserver:1433;database=gtrep
tdmweb.TDMFindReserveService.db.spring.datasource.url=${spring.datasource.url}
tdmweb.TDMFindReserveService.db.spring.datasource.username=${spring.datasource.username}
tdmweb.TDMFindReserveService.db.spring.datasource.password=${spring.datasource.password}
#
oracle.jdbc.OracleDriver
or
com.microsoft.sqlserver.jdbc.SQLServerDriver
tdmweb.TDMFindReserveService.db.spring.datasource.driver-class-name=${spring.datasource.driver-class-name}
#
ORACLE
or
SQL_SERVER
tdmweb.TDMFindReserveService.db.spring.jpa.database=${spring.jpa.database}
For example, to cache the database at
jdbc:oracle:thin:@//tdmserver:1521/tdm.ca.com
, the lines of the configuration file would be:
# e.g jdbc:oracle:thin:@//tdmserver:1521/tdm.ca.com or jdbc:sqlserver://tdmserver:1433;database=gtrep
tdmweb.TDMFindReserveService.db.spring.datasource.url=
jdbc:oracle:thin:@//tdmserver:1521/tdm.ca.com
tdmweb.TDMFindReserveService.db.spring.datasource.username=
MyUsername
tdmweb.TDMFindReserveService.db.spring.datasource.password=
MyPassword123
# oracle.jdbc.OracleDriver or com.microsoft.sqlserver.jdbc.SQLServerDriver
tdmweb.TDMFindReserveService.db.spring.datasource.driver-class-name=
oracle.jdbc.OracleDriver
# ORACLE or SQL_SERVER
tdmweb.TDMFindReserveService.db.spring.jpa.database=
ORACLE
Required user privileges
If you specify an alternative storage location for the cached database, you must ensure that the user with which you access this database has the necessary privileges to access the specified database. The required privileges are as follows:
Oracle
Required privileges:
  • CONNECT
  • RESOURCE
  • CREATE ANY DIRECTORY
  • SELECT ANY TABLE
  • CREATE VIEW
  • UNLIMITED TABLESPACE
Sample command to give user privileges:
GRANT CONNECT, RESOURCE, CREATE ANY DIRECTORY, SELECT ANY TABLE, CREATE VIEW, UNLIMITED TABLESPACE TO <username>;
MS SQL Server
On MS SQL Server databases, for a user to access the prefetched data, they must be the database owner or a user with equivalent privileges.
Data Prefetch Troubleshooting
By default, CA TDM Portal's log are located at
C:\ProgramData\CA\CA Test Data Manager Portal\logs\TDMFindReserve.log
.
Best Practice Guide for Data Prefetch
To store prefetched databases somewhere other than in the gtrep database, follow these steps:
  1. Turn off prefetch feature for all models. Fetched data is deleted.
  2. Stop CA TDM Portal in the
    Windows Services
    dialog.
  3. Change configuration file (application.properties) to store cached data in the new DB.
  4. Start CA TDM Portal.
  5. Turn on Data Prefetch feature. The database specified in the configuration file is now used.
If you change the configuration file before you disable Data Prefetch on models, data is not removed from the original prefetch database, and it is necessary to perform a manual cleanup.
How long does the prefetch take?
These performance statistics were measured with TDM Portal running on a machine with 4-Core CPU @ 2.7 GHz, 16 GB RAM, the target database was on a machine with 2 x virtual 2 GHz CPU, 4GB RAM. These are valid only for the gtrep repository.
Size of database
MS SQL DB to MS SQL DB
or
Oracle DB to Oracle DB
Oracle DB to MS SQL DB
1 table with 100,000 rows
26 seconds
1 min 39 secs
1 table with 1,000,000 rows
4 mins 8 secs
5 mins 49 secs
Data not fetched
If a tester receives empty or incomplete search results from a Test Data Model with prefetched database(s), check
TDMFindReserve.log
for error messages.
Why was data not fetched?
  • If the Find&Reserve database was changed (
    tdmweb.TDMFindReserveService.db.spring.datasource.*
    properties in the configuration file, see Specify where prefetched databases are stored) and this database connection does not work there will likely be a DB-related exception in TDMFindReserve.log immediately after TDM Portal startup. If this is due to the user lacking necessary privileges, see Required user privileges.
  • If there is a data type conversion failure (i.e. value cannot be stored in data type), and the source database was Oracle, and the target (i.e. cached) database was another database type, then use an Oracle database as the target database.
    For example, dates from BC are formatted differently in Oracle, and these cannot convert to other database types.
  • If there is a database error (due to storage, database configuration etc) then resolve this as the Administrator user.
Remove prefetched data from gtrep database
If you specify a location for the prefetched data after the first prefetch, the gtrep database still contains these tables from the first prefetch. To remove them, run the following scripts on the gtrep schema/database as schema/database owner:
MS SQL Server
DROP TABLE test_data_model;
DROP TABLE data_view_property;
DROP TABLE data_view_instance;
DROP TABLE data_view;
DROP TABLE schema_version_find_reserve;
DECLARE @cmd varchar(4000)
DECLARE cmds CURSOR FOR
SELECT 'drop table [' + Table_Name + ']'
FROM INFORMATION_SCHEMA.TABLES
WHERE Table_Name LIKE 'dvid_%'
OPEN cmds
WHILE 1 = 1
BEGIN
FETCH cmds INTO @cmd
IF @@fetch_status != 0 BREAK
EXEC(@cmd)
END
CLOSE cmds;
DEALLOCATE cmds
Oracle
DROP TABLE TEST_DATA_MODEL;
DROP TABLE DATA_VIEW_PROPERTY;
DROP TABLE DATA_VIEW_INSTANCE;
DROP TABLE DATA_VIEW;
DROP TABLE "schema_version_find_reserve";
BEGIN
FOR c IN ( SELECT table_name FROM user_tables WHERE table_name LIKE 'dvid_%' )
LOOP
EXECUTE IMMEDIATE 'DROP TABLE "' || c.table_name || '"';
END LOOP;
END;
Truncation of Databases
CA TDM truncates prefetched (i.e. target) tables when it is unable to guarantee the same sort order of the source and target databases. This truncation occurs on synchronisation of the prefetched tables with the source tables.
CA TDM never truncates source databases.
Oracle and MS SQL
CA TDM truncates target tables only when the following conditions are true for either the source and target databases:
  1. The primary key is of one of the following types:
    • CHAR
    • VARCHAR
    • NCHAR
    • NVARCHAR
      In Oracle and MS SQL databases, NCHAR and NVARCHAR data types always have encoding UTF-16.
  2. AND
    one of the following conditions is met:
    1. EITHER
      The primary key column character set is
      not
      one of the following character sets:
      • ASCII
      • ISO8559-1
      • UTF-8
      • UTF-16
    2. OR
      the primary key column's sort order is
      not
      binary
DB2
CA TDM truncates prefetched tables from DB2 source tables, if the primary key is of one of the following types:
  • CHAR
  • VARCHAR