Database Subsetting With Jailer Tutorial

Jailer is a tool for database subsetting, schema, and data browsing. 

It creates small slices from your database and lets you navigate through your database following the relationships. Jailer is ideal for creating small samples of test data or for local problem analysis with relevant production data.

In this tutorial, you will learn how to export consistent sets of rows from relational databases into topologically sorted SQL-DML, i.e., a sequence of INSERT statements ordered in a way that no foreign-key constraint will be violated during execution.

Objective

Exporting all the data related to the employee named 'Scott' into a SQL script.

Step 1. Setup the Database

Use the demo data model "Demo Scott"

Step 2. Export Employee Scott (Unrestricted)

Now, let's try to export the employee named Scott. To do that, we need an extraction model.

Select EMPLOYEE as a table to extract and type T. NAME = 'SCOTT' into the Where-field:             

This extraction model describes a set of entities containing (the) employee(s) named 'SCOTT,' entities associated with these employees, entities associated with these entities, and so forth.

Export this set (Export Data-Button or Tools->Export Data). Jailer first asks for a file name for the new extraction model. Call it scott.csv. After that, the Data Export dialogue appears:

Type scott.sql into the first field. This defines the name of the export file to be generated.

Click on Export Data. The Progress Panel shows:
Jailer has generated a file scott.sql  containing Insert-statements for Scott, for his boss, for the president, and for Scott's department, salary-grade, and project participations.

But why are there also statements for all other employees?

Click on "EMPLOYEE" in the "Rows per Table" overview. This displays all process steps leading to additional employee records.
 As you can see, all employees assigned to the same department as SCOTT are exported. Likewise, all the employees who belong to the same salary grade are also exported.

Step 3. Export Employee Scott (Restricted)

Exporting an employee requires one to export his boss and department too. Otherwise, the set of exported entities would not be consistent (due to the dependencies based on foreign key constraints). No constraint prevents us from excluding the salary grade and the bonus from export, but we don't do that because the resulting set would be incomplete.

On the other hand, we don't want to export all subordinates of an employee or all employees who work in the same department as Scott.

To exclude subordinates, department members, and 'same salary-grade'-employees, we must restrict some associations.

A restriction is an extension of the association join condition (in SQL syntax) for one direction of an association. disabled (or false) stands for an unsatisfiable condition.

Define restrictions:

• from DEPARTMENT to EMPLOYEE
• from EMPLOYEE to EMPLOYEE (inverse-BOSS)
• from SALARYGRADE to EMPLOYEE
• from PROJECT to PROJECT_PARTICIPATION
• from ROLE to PROJECT_PARTICIPATION

Use Tools->Export Data again. Jailer will now export only the data related to Scott.

 
-- generated by Jailer
--
-- extraction model: EMPLOYEE where NAME='SCOTT' (extractionmodel/scott.csv)
-- database URL: jdbc:db2://localhost/wisser
-- database user: scott
-- Exported Entities: 13
-- DEPARTMENT 2
-- EMPLOYEE 3
-- PROJECT 2
-- PROJECT_PARTICIPATION 2
-- ROLE 2
-- SALARYGRADE 2



Insert into SALARYGRADE(GRADE, LOSAL, HISAL) values (4, 2001, 3000), (5, 3001, 9999);
Insert into ROLE(ROLE_ID, DESCRIPTION) values (100, 'Developer'), (102, 'Project manager');
Insert into PROJECT(PROJECTNO, DESCRIPTION, START_DATE, END_DATE) values (1001, 'Development of Novel Magnetic Suspension System', '2006-01-01', '2007-08-13'), (1003, 'Foundation of Quantum Technology', '2007-02-24', '2008-07-31');
Insert into DEPARTMENT(DEPTNO, NAME, LOCATION) values (20, 'RESEARCH', 'DALLAS'), (10, 'ACCOUNTING', 'NEW YORK');
Insert into EMPLOYEE(EMPNO, NAME, JOB, BOSS, HIREDATE, SALARY, COMM, DEPTNO) values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000.00, null, 10);
Insert into EMPLOYEE(EMPNO, NAME, JOB, BOSS, HIREDATE, SALARY, COMM, DEPTNO) values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975.00, null, 20);
Insert into EMPLOYEE(EMPNO, NAME, JOB, BOSS, HIREDATE, SALARY, COMM, DEPTNO) values (7788, 'SCOTT', 'ANALYST', 7566, '1982-12-09', 3000.00, null, 20);
Insert into PROJECT_PARTICIPATION(PROJECTNO, EMPNO, START_DATE, END_DATE, ROLE_ID) values (1003, 7566, '2007-02-24', '2008-07-31', 102);
Insert into PROJECT_PARTICIPATION(PROJECTNO, EMPNO, START_DATE, END_DATE, ROLE_ID) values (1001, 7788, '2006-05-15', '2006-11-01', 100);