Jailer - FAQ

Frequently Asked Questions

Why am I getting so much data back?

Each association will be traversed in both directions, unless there is a restriction defined. If, for example, the subject table is employee, and the association from department table to employee table is enabled, you will not only get all departments associated with any subject employee, but also all employees associated with one of these departments.

If you get lines that you do not want to get, you need to make a further restriction. The Progress Panel can help you in such a case. It shows the chain of associations for each exported row back to a subject row, where the export starts. I recommend reading the tutorial that explains these things.

Instead of adding all restrictions you need, starting from the unrestricted model, you can also go the opposit way: at first disable all associations (except dependencies) via the menu item "Edit->Disable all associations". Then remove restrictions until you get all the rows you are looking for.

It is easier to get a working extraction model in this way, but it is more difficult to ensure that the model is not too restrictive, i.e. that you get all the rows you want.

In my experience the best way to define an extraction model is to use the "Closure" view.

The closure view lists all tables that are directly or indirectly associated with the subject table (where the export starts), according to the restrictions, ordered by distance.

Check each table of this list from top to bottom. If you don't want to have rows from the table to be exported, select the table and disable the association which will be automatically selected (the context menu of the table is also available here). The table disapears from the list as soon as it is no longer associated with the subject.

You could also first check the tables with the highest degree (the degree of a table is the total number of adjacent tables that can be reached directly with an enabled association.) this way. These are often the tables that increase the closure significantly and therefore have many associations to be restricted.

In this way, you simply get an extraction model that extracts rows only from tables from which you want to extract data.

Finally, check all associations between the remaining tables.

What does $DISTANCE and $IS_SUBJECT mean?

$DISTANCE and $IS_SUBJECT are special expressions used in restriction conditions in order to define restriction models based on topological properties of rows.

Expression	Type
A.$DISTANCE or B.$DISTANCE	INTEGER	Minimum length of all paths from the row back to any subject row. The distance of a subject row is 0.
A.$BIRTHDAY	INTEGER	Synonym for A.$DISTANCE
A.$IS_SUBJECT	BOOLEAN	Synonym for A.$DISTANCE=0
$IN_DELETE_MODE	BOOLEAN	Is true during "Delete Reduction" stage, otherwise false

What is the preferred way to update an existing data model and extraction model, when the database has new schema changes?

To update the data model, open the extraction model editor and select "Data Model" -> "Analyze Database".
If necessary, several times for each schema, if there are several.

When the data model is updated, each extraction model will be migrated on loading.
Tables and associations that no longer exist after the database changes are silently removed from the extraction models as well.

Associations that are newly added and are relevant to the extraction model (that is: directly or indirectly connected to a subject table) are listed in the "Model Migration Tool" dialog. (There is a button "Migration pending" at the top to open this dialog again after you have closed it).

In the "Model Migration Tool" each new association can be examined and either restricted or - with the "Checked" button - marked as checked.
When all associations have been checked or restricted, the migration is finished.

if you save an extraction model before migration is finished, you can continue the migration after loading it again.

How do I anonymize the extracted data?

It's possible to anonymize the extracted data by defining filters (via "Edit" menu "Filter editor...", or table's context menu)

A filter is an assignment of an SQL expression to a table column. The column values will be replaced by the result of the expression when the export file is written.

For example:

In order to substitute the values of the column PERSON.SSN with the constant value '123', define the filter:
PERSON.SSN := '123'

The filter
PERSON.SSN := substr(${old-value}, 1, length(${old-value}) - 3) || '***'
replaces the last 3 characters of the SSN with '***'
${old-value} is a synonym for the filtered column.

The filter:
PERSON.SSN := (SELECT SSN FROM TEST_SSN ORDER BY RAND()
FETCH FIRST 1 ROWS ONLY)
would replace the SSN with a randomly chosen SSN from a table holding test SSNs (IBM DB2)

Is it possible to avoid the creation of JAILER_* tables in the database?

Set the working-table scope to "local database" ("Data Export" dialog) to avoid the creation of that tables in your database. The tables will then be created in a local database (H2).
For DB2, Oracle and PostgreSQL, it is also possible to create the tables as temporary tables.

I'm getting the message "Table has no primary key". What's wrong?

Jailer cannot handle tables without a primary key. Exporting data is not possible if the subject table is associated with a table without a primary key. In this case, you should manually define a primary key in the data model of the tool (not in the database!) using the Data Model Editor. Note that a key must be unique.

However, when working with Oracle or PostgreSQL, you can use the rowid respectively the ctid pseudo columns instead of primary keys.
See section "Row identification" in the "Data Export" dialog.

How can I safely export rows without their parents?

If a foreign key of a table is nullable, a "null" filter on it (resp. on all columns of a composite foreign key) causes only the foreign key columns of those rows to be filled with "null", whose parent row is not exported as well.
In the same way, only the foreign key columns of those rows are set to "null" when deleting, which are not deleted themselves but their parent.
See filter documentation

The GUI is slow, what can I do?

Due to the dynamic nature of the layout algorithm, the table-association graph is permanently redrawn. It seems that this consumes too much cpu time on some systems. However, there is a parameter called "animation step time" which determines the minimum time (in ms) for one animation step. Increasing that parameter (Settings->Animation step time) will reduce the cpu utilization.

How do I setup multi-user mode?

Unzip the "jailer-version.zip" file and create an (empty) file ".multiuser" in the directory "jailer". All model and settings files are then stored in a folder named ".jailer" in the user's home directory.

Why do text and buttons on my Full HD, UHD or 4K displays look so small?

Java JRE 8 does not support HiDPI graphics. Therefore it is strongly recommended to use Java JRE 11 (or above).