If every application worked exactly as intended from the first, we would never have any need for test databases. Unfortunately, one often needs to builds test systems in order to both tune the application SQL, and to do capacity planning. In this section we shall illustrate how very large volumes of extremely complex test data can be created using relatively simple SQL statements.
Good Sample Data is
Create the set of integers between zero and one hundred. In this statement we shall use recursive coding to expand a single value into many more.
Use recursion to get list of 100 numbers
Instead of coding a recursion join every time, we use the table function to create the required rows. Assuming that the function exists, one would write the following:
Use user-defined-function to get list of 100 numbers
Make Reproducible Random Data
So far, all we have done is create sets of fixed values. These are usually not suitable for testing purposes because they are too consistent. To mess things up a bit we need to use the RAND function, which generates random numbers in the range of zero to one inclusive. In the next example we will get a (reproducible) list of five random numeric values:
Use RAND to create pseudo-random numbers
The initial invocation of the RAND function above is seeded with the value 1. Subsequent invocations of the same function (in the recursive part of the statement) use the initial value to generate a reproducible set of pseudo-random numbers.
Using the GENERATE_UNIQUE function
With a bit of data manipulation, the GENERATE_UNIQUE function can be used (instead of the RAND function) to make suitably random test data. The are advantages and disadvantages to using both functions:
See the description of the GENERATE_UNIQUE function (see page 131) for an example of how to use it to make random data. Make Random Data - Different Ranges There are several ways to mess around with the output from the RAND function: We can use simple arithmetic to alter the range of numbers generated (e.g. convert from 0 to 10 to 0 to 10,000). We can alter the format (e.g. from FLOAT to DECIMAL). Lastly, we can make fewer, or more, distinct random values (e.g. from 32K distinct values down to just 10). All of this is done below:
Make differing ranges of random numbers
Make Random Data - Varying Distribution
In the real world, there is a tendency for certain data values to show up much more frequently than others. Likewise, separate fields in a table usually have independent semi-random data distribution patterns. In the next statement we create three independently random fields. The first has the usual 32K distinct values evenly distributed in the range of zero to one. ond and third have random numbers that are skewed towards the low end of the range, and have many more distinct values:
Create RAND data with different distributions
Make Random Data - Different Flavours
The RAND function generates random numbers. To get random character data one has to convert the RAND output into a character. There are several ways to do this. The first method shown below uses the CHR function to convert a number in the range: 65 to 90 into the ASCII equivalent: "A" to "Z". The second method uses the CHAR function to translate a number into the character equivalent.
Converting RAND output from number to character
Make Test Table & Data
So far, all we have done in this chapter is use SQL to select sets of rows. Now we shall create a Production-like table for performance testing purposes. We will then insert 10,000 rows of suitably lifelike test data into the table. The DDL, with constraints and index definitions, follows.
The important things to note are:
Several other fields must be within certain numeric ranges.
Production-like test table DDL
Now we shall populate the table. The SQL shall be described in detail latter. For the moment, note the four RAND fields. These contain, independently generated, random numbers which are used to populate the other data fields.
Production-like test table INSERT
ome sample data follows:EMP# SOCSEC# JOB_ DEPT SALARY DATE_BN F_NME L_NME
In order to illustrate some of the tricks that one can use when creating such data, each field above was calculated using a different schema:
DB2 Using SQL Related Interview Questions
|PL/SQL Interview Questions||IBM DB2 Interview Questions|
|Oracle Interview Questions||COBOL Interview Questions|
|DB2 Using SQL Interview Questions||IBM Mainframe Interview Questions|
|MYSQL DBA Interview Questions||DB2 SQL Programming Interview Questions|
|IMS/DB Interview Questions||Mainframe DB2 Interview Questions|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.