Lately, we've been building new IBM i ILE RPG apps for customers quite a bit here at ASNA. In most cases, these RPG apps provide the back-end for an ASNA Mobile RPG front-end for use on Apple and Android mobile devices. In nearly every case, we're building a new application that needs new data files (or at least some new files).
Faced with the need to quickly populate several test files for a project the other day, we did what smart programmers do and started Googling around. A "generate test data" query quickly introduced us to a great test generator site at mockaroo.com. There is a paid plan available for generating large test data sets, but we found the free plan did all we need. The free plan lets you generate up to 1000 rows of test data per schema. There are also $50 and $500 annual plans. The free plan is also performance throttled, but we didn't find that an issue at all.
The data Mockaroo creates for you is good to have for building new applications. Beyond writing the code, having a good representation of data makes for a much better demo of the app. Also, using SQL Mockaroo generates to populate test data, it's easy to write SQL scripts that let you easily reset your test data to a known state for integration testing purposes.
This article may seem a little SQL-centric, but the data generated works just fine in RPG with either embedded SQL or old-school record level access.
Consider this DOCTOR file format defined with DDS:
A R RCUSTOMER A CMCUSTNO 9P 0 A CMNAME 40 A CMADDR 35 A CMCITY 30 A CMSTATE 2 A CMPOSTCODE 10 A CMACTIVE 1 A CMFAX 10P 0 A CMPHONE 20 A CMEMAIL 80 A K CMCUSTNO
Or, if you prefer, as we do, defined with SQL:
CREATE TABLE doctor ( CMCUSTNO Dec(9,0), CMNAME Char(40), CMADDR Char(35), CMCITY Char(30), CMSTATE Char(2), CMPOSTCODE Char(10), CMACTIVE Char(1), CMFAX Dec(10,0), CMPHONE Char(20), CMEMAIL Char(80), Primary Key(CMCUSTNO) );
We needed to populate this DOCTOR file with 50 or so rows with test data, as well as create several other related tables. In most cases, these test tables needed working relations defined (that is, the CLINIC table needed rows with a foreign key linking a clinic back to a DOCTOR).
Using Mockaroo to create test data
Mockaroo lets you quickly build a physical schema. For each column you specify one of Mockaroo's 141 data value types. For example, the "Full Name" type generates random names and the "SSN" type generates random US Social Security Numbers. The screenshot below shows Mockaroo's data type help panel.
Our DOCTOR table was created with types specified as shown below in Figure 1a:
Figure 1a. The DOCTOR table defined with Mockaroo's types.
Mockaroo can show a preview of its generated data. The preview below in Figure 1b is for the DOCTOR table shown above in Figure 1a.
Figure 1b. Mockaroo's preview panel.
Types out the wazzoo!
With 141 types, Mockaroo covers a broad spectrum of data types you're likely to need. Beyond the obvious city, state, zip, country types, it has a slew of specialty types for that cover health, location, IT, and many others. Beyond its canned types, you can customize Mockaroo types with formulas, regular expressions, and even Ruby code. You can see Mockaroo in action with the link below.
Most of the data types in the DOCTOR example are self-explanatory, but the CMActive field deserves special attention. That field is a one-character value that indicates if the doctor is active. Valid values are 'O', 'C', and 'X', for Open, Closed, and Suspended. The challenge with a data type like this is that, to echo the real world, the data should include mostly 'O' records, with just a few 'C' and 'X' records. Mockaroo has a few tricks up its sleeve to help direct "random" data assignment, but in the case of the CMActive code, we used a Custom List with a value of 'O,O,O,O,O,C,O,O,O,O,O,O,O,O,O,O,X', assigned sequentially. That meant that for every five 'O' rows there is a 'C' row, and for every ten 'O' rows there is an 'X' row.
If you need more data distribution sophistication, Mockaroo "Scenarios" feature lets you shape your test data according to numeric distributions based on other columns in your schema. If you need to ensure foreign key integrity, the simple answer is parked away in a handy little four minute video. There are also other videos available.
Mockaroo has an import facility to bootstrap schema building, but unlike the rest of Mocharoo's features, it is not very well implemented and doesn't provide much help. It requires a specific Json format and pretty much needs more effort than it's worth. That said, if we needed to create test data for one of those JD Edwards-like monster record formats, we'd probably use Mockaroo's import facility with a little Python effort on our side.
Generating the data
Mockaroo can generate a variety of test data outputs, including CSV, Json, Excel, XML, and SQL. The SQL generated works great with the IBM i. Line breaks are added for publication purposes, but otherwise, the SQL below is what Mockaroo generates for a single row of test data:
insert into Doctor (CMCustNo, CMName, CMAddr, CMCity, CMState, CMPostCode, CMActive, CMFax, CMPhone, CMEmail) values (1, 'Ellen Dorbin', '0308 John Wall Lane', 'Pittsburgh', 'PA', '15220', 'O', '4127569156', '706-479-2186', 'firstname.lastname@example.org');
Figure 2. SQL Insert statement that Mockaroo generates.
The SQL output plugs right into the IBM i's Navigator SQL console and works great. Alas, except for the very smallest of record formats, the line is too long to use in the IBM i's STRSQL green-screen panel without modification. (To use it there you need to put the generated SQL in a text editor and replace every comma with a comma and a carriage return).
After running the provided SQL statements, you can see the data in either the Navigator SQL panel:
or over on the green-screen:
If you're building new apps give Mockaroo a look. We reach for it often for IBM i application development.