Database Subsetting With Jailer
In this tutorial, you will learn how to export consistent sets of rows from relational databases to topologically sorted SQL-DML.
Join the DZone community and get the full member experience.
Join For FreeIntroduction
In this tutorial, you will learn how to export consistent sets of rows from relational databases to 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.
The Jailer Tool can be found at:
Objective
Exporting all the data related to the employee named 'Scott
' into a SQL-script.
Step 1. Setup the Database
Create a new database schema and execute script/scott-tiger.sql. Make sure that the script runs successfully.
$ db2 connect to wisser user wisser using <password>
$ db2 -tvf script/scott-tiger.sql
Step 2. Building the Data Model
Start the Tool (Jailer.exe or jailerGUI.bat on Windows platform).
$ sh jailerGUI.sh
and create a new data model. Press New and enter "Tutorial" as the name for the new model.
A data model holds information about the tables in the database and all associations between them.
Most of the information can be retrieved automatically by analyzing the database schema.
Click on Analyze Database
You will be asked for the database connection information
Create a new connection
Select the DBMS you are using from the displayed list, and fill out the following form:
Finally, click on Connect. Jailer finds seven tables and five associations:
Two associations are still missing:
- an
employee
is classified into asalarygrade
depending on hissalary
employees
may receivebonus
es
Add the definitions manually (Add-Button under Associations):
The data model is now complete.
Step 3. 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 the table to extract from 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 employee
s, 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 dialog 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 employee
s?
Click on "EMPLOYEE
" in the "Rows per Table
" overview. This displays all process steps leading to additional employee
records.
As you can see, all employee
s assigned to the same department as SCOTT
are exported. Likewise, all employee
s who belong to the same salary-grade
.
Step 4. Export Employee Scott (Restricted)
Exporting an employee
requires exporting 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 employee
s 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 associations join
condition (in SQL-syntax) for one direction of an association. Disabled (or false
) stands for an unsatisfiable condition.
Define restrictions:
- from
DEPARTMENT
toEMPLOYEE
- from
EMPLOYEE
toEMPLOYEE
(inverse-BOSS
) - from
SALARYGRADE
toEMPLOYEE
- from
PROJECT
toPROJECT_PARTICIPATION
- from
ROLE
toPROJECT_PARTICIPATION
Use Tools->Export Data again. Jailer will now export only the data related with Scott
.
scott.sql:
-- generated by Jailer at Sat May 03 12:38:33 CEST 2019 from wisser@desktop
--
-- 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),
(7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975.00, null, 20),
(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),
(1001, 7788, '2006-05-15', '2006-11-01', 100);
Thanks for reading! Let me know your thoughts in the comments.
Opinions expressed by DZone contributors are their own.
Comments