DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Last call! Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • Why I Built the Ultimate Text Comparison Tool (And Why You Should Try It)
  • Designing a Blog Application Using Document Databases
  • Relational DB Migration to S3 Data Lake Via AWS DMS, Part I
  • NoSQL for Relational Minds

Trending

  • Beyond ChatGPT, AI Reasoning 2.0: Engineering AI Models With Human-Like Reasoning
  • Cookies Revisited: A Networking Solution for Third-Party Cookies
  • The Cypress Edge: Next-Level Testing Strategies for React Developers
  • Measuring the Impact of AI on Software Engineering Productivity
  1. DZone
  2. Data Engineering
  3. Databases
  4. Database Subsetting With Jailer

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.

By 
Ralf Wisser user avatar
Ralf Wisser
·
Updated Nov. 13, 24 · Tutorial
Likes (3)
Comment
Save
Tweet
Share
15.6K Views

Join the DZone community and get the full member experience.

Join For Free

Jailer is a great tool to consider for database subsetting, schema, and data browsing. It creates small slices from your database and lets you navigate through it by following the relationships. Jailer is ideal for creating small test data samples or for local problem analysis with relevant production data.

A Quick Look at Jailer's Features

  • The Data Browser lets you navigate through your database, following the relationships (foreign key-based or user-defined) between tables.
  • The Subsetter creates small slices from your productive database and imports the data into your development and test environment (consistent and referentially intact).
  • The tool improves database performance by removing and archiving obsolete data without violating integrity.
  • It generates SQL (topologically sorted), JSON, YAML, XML (hierarchically structured), and DbUnit datasets.
  • A demo database is included, and you can get a first impression without any configuration effort.

How to Export Related Data Using Jailer

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.

In the following scenario, we will export all the data related to the employee named 'Scott' into an 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:             

export the employee named Scott


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:

Data Export Dialogue


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

Click on Export Data.

Progress Panel 

The progress panel will then show a generated scott.sql file containing INSERT statements for Scott, his boss, the president, and Scott's department, salary grade, and project participation.

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.

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 employees belonging to the same salary grade are exported.

Step 3: 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 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.

Apply the following 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

With Restrictions

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

Plain Text
-- 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);


Conclusion

After going through all the steps and intricacies of Jailer, it is helpful to remember these key points that make it a versatile tool for managing databases:

  1. Purpose and Functionality: Jailer is a powerful tool specifically designed to efficiently streamline database schemas.
  2. Key Features: Its standout features include automated database refactoring, seamless data migration, and robust schema evolution support.
  3. Benefits: It enhances productivity by reducing manual errors, ensuring consistency, and supporting agile development practices.
By leveraging Jailer in your database workflows, you can significantly optimize your database management processes and improve overall workflow efficiency.
Relational database Database Tool

Opinions expressed by DZone contributors are their own.

Related

  • Why I Built the Ultimate Text Comparison Tool (And Why You Should Try It)
  • Designing a Blog Application Using Document Databases
  • Relational DB Migration to S3 Data Lake Via AWS DMS, Part I
  • NoSQL for Relational Minds

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!