Setting up the schema used for stubbing a physical database

Before you can create database stubs, you must specify in HCL DevOps Test Integrations and APIs (Test Integrations and APIs) which database schema is to be used for the simulation database.

About this task

If you are using Test Integrations and APIs 8.5.0 or later and you want to simulate an IBM IBM® Db2®, Oracle, Microsoft™ SQL Server, or MySQL database, you can use the integrated simulation database or a vendor-specific database. If you want to use the vendor-specific database, your database administrator (DBA) first needs to create a separate schema for the stub data. The schema must match the corresponding database. For example, to stub an Oracle database, you need an Oracle schema. Alternatively, to stub a Microsoft SQL Server database, you need a Microsoft SQL Server schema, and so on.

The integrated simulation database is an Apache Derby database that is included with Test Integrations and APIs. Although the Apache Derby database is based on the Java™, JDBC, and SQL standards, it cannot simulate the characteristics of all other databases. Therefore, SQL statements for unsupported databases will fail on an Apache Derby database. Hence, in the following situations, you must use a database of the same type as the original simulation database:

An application may be using SQL syntax that is not understood by the integrated simulation database. This will be evident if the console of the application displays messages about unexpected JDBC exceptions during the recording (learning) phase. If you are using the Test Integrations and APIs JDBC proxy, these SQL errors are also logged in the activity log of HCL DevOps Test Virtualization Control Panel (Test Virtualization Control Panel), which is viewable if you have Test Virtualization Control Panel administrator privileges.
A database might support a data type that the integrated simulation database does not support. This will be evident if you try to edit a database table of a database stub that includes a data type not supported by the Apache Derby database. Test Integrations and APIs will display SQL syntax error messages when saving the database table.

Note: If you elect to use the integrated simulation database, the instances of the databases that Apache Derby uses are stored in the workspace of Test Integrations and APIs or its Agent, whichever is applicable. The specific location in the workspace is unimportant because the locations where the instances are stored are transient.

Depending on the option you select, the database schema or the integrated simulation database will be used in the following cases:

While Test Integrations and APIs is "learning" the stubs
When a stub is started, that is, the database schema or the integrated database will be used to store the contents of a database stub

To grant the required permissions while using an Oracle database for simulations that need JDBC, run the following SQL code:

grant CREATE SESSION, ALTER SESSION, -
CREATE PROCEDURE, -
CREATE SEQUENCE, CREATE SYNONYM, CREATE TABLE, -
CREATE TYPE, UNLIMITED TABLESPACE -
to VIRT_USER;

The following table lists the live and simulation database combinations that are supported by different versions of Test Integrations and APIs.


Live and simulation database combination support	Test Integrations and APIs version
Db2®/Db2®	All
Db2®/Integrated simulation database (excluding support for JDBC virtualization on IBM® WebSphere® Application Server)	8.5.0 or later
Db2®/Integrated simulation database (including support for JDBC virtualization on WebSphere® Application Server)	8.5.1 or later
Microsoft™ SQL Server/Microsoft™ SQL Server	All
Microsoft™ SQL Server/Integrated simulation database (excluding support for JDBC virtualization on WebSphere® Application Server)	8.5.0 or later
Microsoft™ SQL Server/Integrated simulation database (including support for JDBC virtualization on WebSphere® Application Server)	8.5.1 or later
MySQL/MySQL	All
MySQL/Integrated simulation database (excluding support for JDBC virtualization on WebSphere® Application Server)	8.5.0 or later
MySQL/Integrated simulation database (including support for JDBC virtualization on WebSphere® Application Server)	8.5.1 or later
Oracle/Oracle	All
Oracle/Integrated simulation database (excluding support for JDBC virtualization on WebSphere® Application Server)	8.5.0 or later
Oracle/Integrated simulation database (including support for JDBC virtualization on WebSphere® Application Server)	8.5.1 or later

Procedure

In the Physical View of the Architecture School perspective of Test Integrations and APIs, double-click the physical database that you want to stub.
Click the Stub Settings tab.

Note: If you are using Test Integrations and APIs 8.5.0 or later, by default, the Use integrated database check box is selected.
Specify the database to use.
- To proceed with the integrated simulation database, click OK to close the window.
- To use a vendor-specific database, clear the Use integrated database check box and complete the remaining steps in this procedure.
In the Database URL field, enter the URL of the server that will host the database stub schema. For more information on specification of Database URLs when using IP literal addresses, see IP literal address.
In the User Name field, enter the name of the relevant database user.
In the Password field, enter the database users password.
In the Database Schema field, enter the name of the database stub schema.
Note: Test Integrations and APIs manages the content of the simulation database, including dropping and creating content when database stubs are executed. To avoid collisions between this management of the simulation database and the content of the live database, the database settings for the live and simulation databases must be distinct. The combination of database URL and schema must provide sufficient separation between the live database and the simulation database; for example, you could use different schemas within the same database or you could use completely different database instances.
Note: The user name, password, and schema name should be provided by a database administrator (DBA). The database user must also have sufficient privileges to be able to create and delete tables in the simulation database.
Optional: When working with database virtualization, if you observe that the data in the simulation database is not reset automatically, use the Clear simulation database option to manually clear the data.

Modify the default value of the Max Row Count field. The default value of the field is 10,000. You can decrease this default value to limit the maximum number of rows copied when learning from a live database or when copying rows into a database stub.
Modify the default value of the Max Stored Procedure Calls field. The default value of the field is 10,000. If the system under test is very active, you can decrease the value of this field, thus limiting the quantity of data captured by Test Integrations and APIs while learning.
Modify the default value of the Max Result Set Row Count field. The default value of the field is 10,000. You can decrease this default value to limit the maximum number of rows copied when learning a result set returned from a stored procedure and thus limit the amount of information that Test Integrations and APIs will learn while creating a new database stub. You might want to use this field if you cannot or do not want to modify the behavior of the system under test.
Limitation: If the result set returned by a stored procedure is scroll-sensitive and dynamic, then the JDBC virtualization cannot learn the stored procedure call.
Click the Unique option radio button (default) if you want to record only unique invocations of a stored procedure based on the input parameters. Alternatively, click the All option radio button to record all invocations of a stored procedure.

Click Test Stub Connection to verify the Test Integrations and APIs connection to the database stub.
Click OK.

Results

You are now ready to create database stubs.