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 .
tdm46
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.
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
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
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
CA TDM only schronizes 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