The Datamaker UI allows you to generate rich sets of data directly into your database. After you create the data shapes you need, you can add more data (data bulking) using the Data Multiplier script. This method is useful when you want to increase transactional data for performance testing. You would not use this method for reference tables, such as customer or products, though. This method is, however, useful for building a 100 million orders and items, for instance.
The Data Multiplier script contains SQL statements that you can run directly against the database to double the amount of data each time you run it. So, if you have generated a million rows, you can double this number to 2 million, then to 4 million, and so forth. Therefore, by running the script 5 times (for example), you can create an extra 63 million rows. This method allows you to create data quickly as there is no external file activity to slow the creation.
Save and run the generated script against the database. The script handles assigning new IDs and creating rows with referentially intact relationships. You can also add defined Transformation Maps to the generated scripts. These function maps allow you to add extra randomization or data conditioning to the multiplied data. You can, for example, quickly create many customers based on your existing customers, but assign new random names and addresses to the new customers.
To build the script, follow these steps:
Click the forward arrow icon next to the drop-down list.
A script is created.
An example of a script that is generated is as follows:
-- SQL Data Muliplier Script - 2009/09/02 - 11:43:00
whenever sqlerror exit
COLUMN ORDER_ID_ORDERS__RANGE NEW_VALUE ORDER_ID_ORDERS__MOD
BREAK ON ORDER_ID_ORDERS__RANGE
SELECT MAX(ORDER_ID) - MIN(ORDER_ID) + 1 ORDER_ID_ORDERS__RANGE FROM ORDERS;
INSERT INTO ORDERS (
SELECT ORDER_ID + &ORDER_ID_ORDERS__MOD,
SELECT to_char (COUNT(*)) || ' ORDERS rows' ORDERS_COUNT FROM ORDERS;
COLUMN LINE_ITEM_ID_ORDER_ITEM__RANGE NEW_VALUE LINE_ITEM_ID_ORDER_ITEMS__MOD
BREAK ON LINE_ITEM_ID_ORDER_ITEM__RANGE
SELECT MAX(LINE_ITEM_ID) - MIN(LINE_ITEM_ID) + 1 LINE_ITEM_ID_ORDER_ITEM__RANGE
INSERT INTO ORDER_ITEMS (
SELECT ORDER_ID + &ORDER_ID_ORDERS__MOD,
LINE_ITEM_ID + &LINE_ITEM_ID_ORDER_ITEMS__MOD,
SELECT to_char (COUNT(*)) || ' ORDER_ITEMS rows' ORDER_ITEMS_COUNT FROM
The script varies from database type to database type. This example is an Oracle script that you must run using SQLPLUS. Other database types create temporary tables to identify the increment to any primary key columns.
The generated script calculates the difference between the minimum and maximum values of a key column. For example, if ORDER_ID has a minimum value of 10 and a maximum of a 100, the difference is 90. The INSERT clause selects the existing Orders, but adds 91 to the ORDER_ID, so you get values of 101 to 191 added. The ORDER_ITEMS similarly has the LINE_ITEM_ID incremented; however, the ORDER_ID is incremented by 91.
The result of this is that the new orders are created with a new set of Order_Lines. The new Orders and Order_Lines refer to the original products and have the exact same data apart from the primary keys and any key values linking the data.
If you are running a query on products, you now have twice as many orders for the product. So if you have created a rich set of order types for each product, you can then bulk up the number of orders using this technique.
The following segment includes an example of a script output:
data_multiplier.LSTORDER_ID_ORDERS__RANGE----------------------482old 26: SELECT ORDER_ID + &ORDER_ID_ORDERS__MOD,new 26: SELECT ORDER_ID + 482,423 rows created.Commit complete.ORDERS_COUNT-----------------------------------------846 ORDERS rowsLINE_ITEM_ID_ORDER_ITEM__RANGE------------------------------670old 14: SELECT ORDER_ID + &ORDER_ID_ORDERS__MOD,new 14: SELECT ORDER_ID + 482,old 15: LINE_ITEM_ID + &LINE_ITEM_ID_ORDER_ITEMS__MOD,new 15: LINE_ITEM_ID + 670,646 rows created.Commit complete.ORDER_ITEMS_COUNT----------------------------------------------1292 ORDER_ITEMS rows