we have users by schema and the insert priveleges are not across schemas. Based on the recursive nature of relationships, one schema(schema-1) holds the most granular data and i have to traverse recursively in another schema(schema-2) to identify the entities that I have to extract from the first schema. In this scenario, schema-2 is the driver table helping narrow down the data from schema-1. I have no intention of loading data in schema-2.
The result of an extraction is always a single file. A segregation by schema is not possible because of the interdependencies of the contained statements. Does this cause any problems for you?
question for you, if we are building an extraction model across schemas in an Oracle database. can we have the extracted sql files segregated by schema and not be in one file
I'm afraid I didn't quite understand what kind of extraction models your approach generates. (I am not familiar with Go) I assume it is one with restrictions on all "to-child" associations except those that start from a subject table(?) Jailer currently only offers two initial models: * no restrictions * restrictions on all "to-child" associations which of course have to be supplemented manually. I found it difficult to find other useful presettings. But there may be some, of course. Ultimately,...
Hey, sorry for the delayed response, I've been a bit busy. I created this repository where I put the relevant code of the algorithm that selects which associations to restrict. I tried to explain the rationale in the readme, but feel free to ask any questions/ point out if it isn't clear. @rwisser do you think this could be part of Jailer in the future?
We would experiment this approach. Most of our schemas are independent and for the sake of modularity we would keep them separate, but there are 2 schemas where having a combined view would make the solution simpler. There are recursive relationships of the driving entity but lowermost recursive child resides in the other schema. Thanks for your quick response, we will give this a try.
Yes, of course. To do this, each relevant schema must be analyzed one by one ("Data Model" -> "Analysis Database"). The tables of all schemas and the associations between them - even across schema boundaries - are then stored in the data model. In the "Data Export" dialog, you can then specify for each schema from which schema of the source database the rows are to be read and into which schema of the target database the rows are to be inserted.
That worked. Thanks. One question is can we go across schemas in a single extraction model in oracle?
Yes, there is indeed a limit up to which a to_clob (or whatever the DBMS provides) is used. Longer clobs are written as special comments in the generated SQL script. It is then important that Jailer's import feature is used to read in the data, as only this takes these comments into account. However, you will also be informed of this fact after the export if there are such clobs.
Yes, there is indeed a limit up to which a to_clob (or whatever the DBMS provides) is used. Longer clobs are written as special comments in the generated SQL script. **It is then important that Jailer's import feature is used to read in the data, as only this takes these comments into account. ** However, you will also be informed of this fact after the export if there are such clobs.
yes I will share more details, is there a limit on the size of the CLOB by any chance? Do we convert CLOB to string and then do a to_clob...is there a limitation of 4000 characters?
Hi, CLOB columns are well supported. If you have problems with it, please let me know which DBMS you are using, what you tried and what happened. The more information the better. Thanks in advance
Hi, we are evaluating the data subsetting functionality and am very impressed with the capabilities. We have done some runs and it looks great but it looks like CLOB columns did not work. Is that not supported? Is there a workaround? We have some tables with CLOB data and we need those brought over to our target database as well. Please suggest how we can proceed. Thanks.
@Joao Pereira, would you please share your findings at some time in the future? I am about to embark on a similar journey.
Hi, if you want to generate an SQL script as a result, the two blocks mentioned above are already sufficient. In addition, the blocks: #! block additional subjects for further subject tables as well as: #! block filters and #! block filter templates for filters and filter templates are also taken into account, if required.
Hello, I've been working on automating the extraction model generation. I devised a little algorithm to select which associations to restrict, although it only supports a rather limited set of use cases. Now I just wanted to make sure, when one calls jailer.sh export (...), what blocks will jailer be looking at? Is it just these first two: # subject; condition; limit; limit-order and # association; ; restriction-condition or does it process any block below those? Thanks in advance
Hello, I've been working on automating the extraction model generation. I devised a little algorithm to select which associations to restrict, although it only supports a rather limited set of use cases. Now I just wanted to make sure, when one calls jailer.sh export (...), what blocks will jailer be looking at? Is it just these first two: # subject; condition; limit; limit-order and # association; ; restriction-condition or does it process any block below those? Thanks in advance
Hello, I've been working on automating the extraction model generation. I devised a little algorithm to select which associations to restrict, although it only supports a rather limited set of use cases. Now I just wanted to make sure, when one calls jailer.sh export (...), what blocks will jailer be looking at? Is it just these first two: subject; condition; limit; limit-order association; ; restriction-condition or does it process any block below those? Thanks in advance
Menu item "File" -> "Load"
How in the UI can I open an exsiting extraction model file?
Not via cli, but you could programmatically append association definitions to the file association.csv in the data model directory.
Great stuff! Is there any way for me to specify extra associations not based on foreign keys via the cli?
Please try setting the property "sslmode". To do this, add the following to the URL: "?sslmode=allow". So something like this: "jdbc:postgresql://localhost?sslmode=allow". For more information and other options, see: https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING
Any updates? Same error. Intellij idea with same creadentials and IP works well
I recommend that you first evaluate the tool using the user interface respectively the extraction model editor. This will give you an impression of what the tool can do and how it works. All functionalities are fully accessible via the GUI. You only need to deal with the API when you think that the tool is suitable for your use case. You are welcome to ask any questions here or on the github page.
Curious if we have used the API for large scale applications. I am exploring this for building a sophosticated data subsetting portal for an application that has multiple Orache schemas with 50-75 tables per schema. What would be the best way to evaluate this.
Version 15.7, which I have just released, no longer has a dependency on jaxb. I now use a json-binding based on jackson instead. This avoids all the problems that the jaxb dependency caused.
Hi, the problem with the newer jakarta-JAXB implementations is that they only support JDK 11 and above. But I need a solution for all JDKs from 8 onwards, so I had to use version 2.3.0. Is that a problem for you? Then you would have to switch back to jailer-engine 15.5 first. This should make it possible to use the newer jakarta implementations in parallel. JAXB is used only to read the jailer configuration. I will prospectively change this. Probably to a JSON binding to get rid of the dependency...
Thanks for the release. That fixes the problem with current JDKs, but unfortunately still uses the old javax.bind. namespaces instead of newer jakarta.bind. ones (https://jakarta.ee/specifications/xml-binding/4.0/). Is there a chance for a more current version of jaxb? 4.0.4 would be the newest one. Thanks and best regards
Thanx for release 15.6, I will try it out asap.
Thanks for the further analysis and the workaround. Using the system property solves the problem and I can use Jailer with a current JDK.
I have investigated this in more detail: using the engine via API (i.e. without setting the property to disable the problematic optimization) still works with JDK 19, but no longer with JDK 20.
I have investigated this in more detail: using the engine via API (i.e. without setting the property to disable the problematic optimization) still works with JDK 19, but no longer with JDK 20.
Works perfectly. Thank you very much!
Ok, I found the following interesting in the code (Jailer.java, line 115): try { // TODO 2: update jaxb implementation. See https://mvnrepository.com/artifact/org.glassfish.jaxb/jaxb-runtime System.setProperty("com.sun.xml.bind.v2.bytecode.ClassTailor.noOptimize", "true"); } catch (Exception e) { } This is most likely the reason for the problems. The property to disable the problematic optimization in jaxb-impl-2.3.0 is not set when using the engine via API! A workaround would be to set this property...
Ok, I found the following interesting in the code (Jailer.java, line 115): try { // TODO 2: update jaxb implementation. See https://mvnrepository.com/artifact/org.glassfish.jaxb/jaxb-runtime System.setProperty("com.sun.xml.bind.v2.bytecode.ClassTailor.noOptimize", "true"); } catch (Exception e) { } This is most likely the reason for the problems. The property to disable the problematic optimization in jaxb-impl-2.3.0 is not set when using the engine via API! A workaround would be to set this property...
That's strange: I have just tested it with JDK 22-ea+31-2314 and there were no problems. What error message do you get?
Oh, I didn't know that there are incompatibilities with jaxb-api. (I didn't discover any problems with JDK 21, but it may well be different with 22). Which alternative API does org.glassfish.jaxb need? Is it still compatible with the old one?
I have now added a "Checked" menu entry in the context menu for associations. You can find a pre-release with the new feature here: https://sourceforge.net/projects/jailer/files/stage15.5.1/ please try it out.
Hi, the problem is that jaxb-api 2.3.0 is rather old and doesn't work with current JDKs anymore (not sure if the problem already happens with with 17 or if 21 is the first one where it won't work anymore, but it uses sun.misc.Unsafe internally, which has been deprecated for external access for a long time). Jaxb moved to new maven coordinates after 2.3.0 "org.glassfish.jaxb", which is currently at 4.0.1 and should work with current JDKs. Maybe there's an option to switch to this? Thanks and best...
Ok, I understand. Thank you for the improvement suggestion. I will implement this as soon as I have time.
Hi, For JDK 11 and above you also need the jars: - jaxb-api-2.3.0-b170201.1204.jar - jaxb-core-2.3.0-b170127.1453.jar - jaxb-impl-2.3.0-b170127.1453.jar - activation-1.0.2.jar - slf4j-api-1.7.25.jar You will get these automatically if you use the engine from the Maven repository with a dependency management system (Maven etc.). Otherwise, you must include the jars in the class path manually (they are included in jailer-n.zip). HTH, Ralf
Hi, For JDK 11 and above you also need the jars: - jaxb-api-2.3.0-b170201.1204.jar - jaxb-core-2.3.0-b170127.1453.jar - jaxb-impl-2.3.0-b170127.1453.jar - activation-1.0.2.jar - slf4j-api-1.7.25.jar - You will get these automatically if you use the engine from the Maven repository with a dependency management system (Maven etc.). Otherwise, you must include the jars in the class path manually (they are included in jailer-n.zip). HTH, Ralf
Accept its current state as it is, as if checking the checkbox in the dockable window. So the use-case, if this sounds weird. On a large model update (say hundreds of unmigrated changes) in a database with well over 500 tables, if I click on a single relation to be migrated, it may (will) show half a dozen tables with a bunch of unmigrated relations. Some may be enabled where as they are disabled by default, some disabled where they are enabled. Some, as they appear disabled by default, I want to...
Which JRE version are supported by the jailer-engine? We migrate code that uses the jailer-engine from Java 11 to 17. Unfortunately this seems not to work, because Jailer does use an outdated jaxb-core version. Any hints to solve this problem? Thx. Andreas
Hi, what exactly do you mean by 'check' the relation?