Jailer

This tutorial shows you 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 contraint 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

Create a new database schema and execute script/scott-tiger.sql. Make sure that the script runs successfully.

$ db2 connect to wisser user scott using tiger
$ 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

The first time you start Jailer you will be asked for a data 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 introspecting the database schema.

Click on Introspect DB

Select the setting for your DBMS and enter the connection parameter.
You may change the setting name if you like.

Click on Connect. Jailer finds six tables and four associations:

Two associations are still missing:

an employee is classified into a salary grade depending on his salary
employees may receive bonuses

Add the definitions manually (Add-Button under Associations):

Note that Jailer now knows more about the data model than the DBMS.

Step 3. Export employee Scott (unrestricted)

Now lets try to export the employee named Scott. To do that we need an extraction-model.
Select EMPLOYEE as 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 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 dialog appears:

Type scott.sql into the first field. This defines the name of the export file to be generated.
Click on Export Data. After exportation the Export Statistic is shown:

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?

Let Jailer explain why:

Open the Explain.log:

The data of Adams and James, for example, has been exported, because Scott (7788) is Adams (7876) boss and James (7900) and Adams are both classified in the same salary-grade.

Step 4. Export employee Scott (restricted)

Exporting an employee requires 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 becauses 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 works 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 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.

scott.sql

-- generated by Jailer at Sat May 03 12:38:33 CEST 2008 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);
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);