How to export tables from Derby to SQL Server? Is that possible?

In the documentation to Apache Derby I've seen documentation on functions to administrate user accounts. However, I did not manage to find documentation on how to get a listing of all registered users. After failing to find a respective answer on a Web search likewise I tried all methods suggested by perplexity.ai, which just failed altogether:

SELECT * FROM SYS.SYSUSERS

> "Table/View 'SYS.SYSUSERS' does not exist."

SHOW ROLES

> "Syntax error: Encountered "SHOW" at line 1, column 1." and

SELECT * FROM SYS.SYSCS_UTIL.SYSCS_GET_ALL_PROPERTIES()
WHERE PROPERTY_NAME LIKE 'derby.user.%'

> "Syntax error: Encountered "." at line 1, column 29.". There is **no** derby.properties file either. How can I get a listing of all known users?

If I make a unique constraint on a field, do I also need to make an index on that field in order to get a scalable insert time? Or is this done for me (even if the index it uses isn't publicly accessible?) Specifically, I'm working with Apache Derby for prototyping, although I will probably be moving it to MySQL in the semi-near future. I'm also hoping there might be something in the SQL standard that says something about this. I will never have a need to search by this field, so I would rather not make a useless index. But I'd rather have a useless index than have an O(n) insert time.

I would like to build a distributed system. I need to store data in databases and it would be helpful to use an UUID or a GUID as a primary key on some tables. I assume it's a drawbacks with this design since the UUID/GUID is quite large and they are almost random. The alternative is to use an auto-incremented INT or LONG. What are the drawbacks with using UUID or GUID as a primary key for my tables? I will probably use Derby/JavaDB (on the clients) and PostgreSQL (on the server) as DBMS.

I'm checking out some newish features of Apache Derby for my Java EE app. IntelliJ provides helpful shortcuts for creating a simple DB schema. After a few clicks I have create table "Names" ( id int generated always as identity constraint NAMES_PK primary key ); This should, to me, create a proper lookup table, hash or map, for the PK. If I add the criteria that PK needs to be unique (?) create unique index NAMES_ID_UINDEX on "Names" (id); So my Q is, does this change the how the PK lookup is implemented? The docs are quite unclear on this. This is Derby 10.15.2.0.

I used below mention query to cerate report in My Project with MYSQL with Java NetBeans. It works successfully. SELECT CASE WHEN sortId = 1 THEN CAST(orderId AS CHAR(10)) ELSE '' END AS orderId, CASE WHEN sortId = 1 THEN orderName ELSE '' END AS orderName, itemName, itemUnit, itemRate FROM ( SELECT orderId, orderName, itemName, itemUnit, itemRate, ROW_NUMBER() OVER (PARTITION BY orderId ORDER BY itemId) AS sortId FROM ( SELECT orderId, orderName, itemName, itemUnit, itemRate, itemId FROM tblitem INNER JOIN tblorder on tblorder.orderId = tblitem.orderRef ) orderItems ) orderItemsSorted ORDER BY orderItemsSorted.orderId, orderItemsSorted.sortId Later I wanted to use Derby Database in Netbeans. When I attempted to use that Query makes error message Syntax error: Encountered "PARTITION" at line 4, column 81. To avoid this problem can I use "The result offset and fetch first clauses" in Derby? Thanks in advance to help me to convert or change this query to work in Derby. I Below mentioned table creation and join Quires TBLORDER (Order Table) CREATE TABLE TBLORDER ( ORDERID INTEGER, ORDERNAME VARCHAR (50), PRIMARY KEY (ORDERID) ); TBLITEM (Item Table) CREATE TABLE TBLITEM ( ITEMID INTEGER, ITEMNAME VARCHAR (50), ITEMUNIT VARCHAR (10), ITEMRATE DOUBLE, ORDERREF INTEGER, PRIMARY KEY (ITEMID) ); CREATE FOREIGN KEY ALTER TABLE NAME.TBLITEM ADD FOREIGN KEY (ORDERREF) REFERENCES NAME.TBLORDER (ORDERID) JOIN QUERY SELECT ORDERID, ORDERNAME, ITEMNAME, ITEMUNIT, ITEMRATE FROM TBLITEM INNER JOIN TBLORDER ON TBLORDER.ORDERID = TBLITEM.ORDERREF

I created table using Derby database. CREATE TABLE PROJECT (PID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, PSDATE DATE, PDURATION INTEGER, PEDATE DATE, PRIMARY KEY (PID) ); I attempted to update end date in this project table, adding duration to start date. UPDATE PROJECT SET PEDATE = ADD_MONTHS (PROJECT.PSDATE, PROJECT.PDURATION) WHERE PID = PROJECT.PID; But I am getting below error "[Exception, Error code 30,000, SQLState 42Y03] 'ADD_MONTHS' is not recognized as a function or procedure. Line 1, column 1" Later I tried with below code select {fn TIMESTAMPADD(SQL_TSI_DAY, +pduration, psdate)} from name.PROJECT where pid = project.PID Can I use TIMESTAMPADD function to update project table in Derby database? Thanks in advance to help me to solve this problem.

I have some flat files with the following columns; 3 integers, 3 reals, and 1 varchar(20). For querying I need an index that contains both 1 of the integer columns and the varchar column. Each file is around 1.8GB in size with around 38 million rows. Currently I am using a HSQL(Standalone) database to load a file for processing; one database per file. It is very slow to load (120+ min) the file and results in a 4.7GB database file when the database is created with the following options. "Properties" -> { "check_props" -> "true", "shutdown" -> "true", "hsqldb.default_table_type" -> "cached", "sql.syntax_mss" -> "true", "hsqldb.log_data" -> "false", "hsqldb.inc_backup" -> "false" } The file read in batches of 100k records. The read is very fast (almost instant) so I do not think it is the read that is slowing things down. It also takes a very long time to close the connection to the database. I have the option to use Derby, H2, or SQLite. Will any of these result in faster load time and/or smaller database file size in this scenario? If so what are the connection string options that should be used to achieve this? Alternatively, are there different connection string options I can use with HSQL(Standalone) that will reduce the load time and/or database file size? Driver information added. JDBCDriver[ "Name" -> "HSQL(Standalone)", "Driver" -> "org.hsqldb.jdbcDriver", "Protocol" -> "jdbc:hsqldb:file:", "Version" -> 3.1, "Description" -> "HSQL Database Engine (In-Process Mode) - Version 2.3.3 - This ...", "Location" -> "C:\... "] Driver information for the other options available to me. Derby JDBCDriver[ "Name" -> "Derby(Embedded)", "Driver" -> "org.apache.derby.jdbc.EmbeddedDriver", "Protocol" -> "jdbc:derby:", "Version" -> 3.1, "Description" -> "Derby Database Engine (Embedded Mode) - Version 10.12.1.1 - This...", "Location" -> "C:\... "] H2 JDBCDriver[ "Name" -> "H2(Embedded)", "Driver" -> "org.h2.Driver", "Protocol" -> "jdbc:h2:", "Version" -> 3.1, "Description" -> "H2 Database Engine (Embedded Mode) - Version 1.3.176 - This...", "Location" -> "C:\... "] SQLite JDBCDriver[ "Name" -> "SQLite", "Driver" -> "org.sqlite.JDBC", "Protocol" -> "jdbc:sqlite:", "Version" -> 3.1, "Description" -> "SQLite using Zentus-derived JDBC Driver - Version 3.8.11.2", "Location" -> "C:\..."] Additional variants include the below. However, I need it all to run on the client's computer. I believe this excludes server and webserver modes. {"Derby(Embedded)", "Derby(Server)", "H2(Embedded)", "H2(Memory)", "H2(Server)", "HSQL(Memory)", "HSQL(Server)", "HSQL(Standalone)", "SQLite", "SQLite(Memory)"}

Through trial and error I found that if I do a left join such as: SELECT firsttable.id, secondtable.id, secondtable.varcharColumn FROM firsttable LEFT JOIN secondtable ON firsttable.id=secondtable.id The performance is terrible for larger tables. ***If I EITHER remove secondtable.varcharColumn as a column in the resultset OR change the type then the performance is about an order of magnitude better.*** The column is a varchar 255, so it should not make that much of a difference. If I change the type of column to integerColumn or dateColumn than I also see an order of magnitude difference in the performance. Even a varchar of a few characters seems to result in the same performance degradation. Please note that the WHERE clause is NOT the issue here. I've omitted it because the issue is the same whether or not a WHERE clause is included. **The issue has to do with the type of data in the column and not the quantity of data returned.** I could also use LIMIT 1000 and have the very same issue. Indexing the column will not help as the column is not used for joining. It is also not part of the where clause if there was one. Any suggestions on how to improve the performance would be greatly appreciated!!

Can someone please explain to me how SYSCS_UTIL.SYSCS_CREATE_USER() works? I tried adding a new user like this: SYSCS_UTIL.SYSCS_CREATE_USER('user', 'someP@ss'), and it gave an exception *Error code 30000, SQL state 4251K: The first credentials created must be those of the DBO.* I found the function in the Derby Docs, but it showed the Fred example, which I also tried, but it gave me the same exception. I created the database via NetBeans.

I've spent a good amount of time on this but, still have no idea as to the sql query I can use to get the student name, and class name from the payments_per_student table. edit: This using JPA work with this DB. Knowing the JPA way as well SQl way would be interesting. I'm currently researching the JPA way. payments_per_student: PAYMENTS_PER_STU_ID STUDENTS_STUDENT_ID FAMILY_ACCOUNT_FAMILY_ID REASON_FOR_PAYMENT AMOUNT DATE_PAID CLASSES_CLASS_ID ACTIVE_IN_CLASS NOTES The second is, Classes: CLASS_ID CLASS_NAME DAY_OF_CLASS TIME_OF_CLASS DURATION_OF_CLASS PRICE_PER_CLASS PRICE_PER_TERM PRICE_PER_YEAR NOTES The third table is, Students: STUDENT_ID FIRST_NAME LAST_NAME FAMILY_ACCOUNT_ID SEX_TYPE NAME_PREF DATE_OF_BIRTH PERFORMANCE_STU HEALTH_ISSUES NOTES

I know this question has been asked probably a million times, so please forgive me asking it again. I am not a DBA and have very little knowledge of SQL and Derby, but need some kind of a solution as I am the only one responsible for getting this done: Recursive query in Apache Derby 10.10 There is a table, storing basically a tree: +----+-----------+-----+ | id | parent_id | ... | +----------------------+ | 0 | null | ... | | 5 | 0 | ... | ...................... | N | K | ... | +----+-----------+-----+ I need to make it look like this: +----+-----------+-----+ | id | parent_id | ... | +----------------------+ | 0 | null | ... | | 1 | 0 | ... | | 15 | 1 | ... | | 16 | 1 | ... | | 2 | 0 | ... | | 10 | 2 | ... | | 11 | 2 | ... | ...................... | N | K | ... | +----+-----------+-----+ Or a way to traverse and enumerate the tree in place. Thank you.

In Derby (an embedded database written in Java which is mostly used for testing or prototyping) there are ["freeze" and "unfreeze" commands which can be used during an online backup](http://db.apache.org/derby/docs/10.0/manuals/admin/hubprnt43.html#HDRSII-BUBBKUP-75469) . "Freeze" simply causes all database accesses to block until "unfreeze" is called. This is useful for backing up using an external program, which you might do if the external program is much faster than using Derby's internal backup solution. For my use case, I can take a snapshot almost instantaneously using some built-in filesystem utilities, so it is a constant-time operation (not O(length of DB files)). I'm migrating an application which has outgrown Derby to PostgreSQL, and I was wondering if there's anything comparable there which I can use to quiesce all connections. Also, I'd prefer to know what my serialization point is from inside my application so that I don't get caught in some awkward state, so being able to pause/resume all other accesses is really nice-to-have for me. Since PostgreSQL has a transaction log, I could just take a snapshot without "freeze"ing, but the snapshot would need to be run through PostgreSQL's recovery mechanism before I can use it because otherwise what's stored on disk would be the same as if I pulled the plug on a normal filesystem. This solution is not ideal. **EDIT** I learned that pg_start_backup() is close, but it doesn't cause incoming transactions to block until a matching call to pg_stop_backup(), forcing me to do a point-in-time-recovery back to the transaction id pg_start_backup() returns from a filesystem snapshot. It would be nice not to have to *actually* shutdown PostgreSQL to get this (perhaps there is a pseudo-shutdown command that keeps connections open?).

I need to migrate an existing Oracle Database into a Derby one. I want to know if there's a tool, a script or another way to do that work. It is using any of the interesting features of Oracle, as I can see from the database information from SQL Developer, except sequences and indexes. Thanks!

For a project in school I'm designing a website that sells items via auctions. Think eBay, but on the complexity scale of school project. We went through the process of making an ER Diagram and planning things out but we still ran into a snag with the table creation statements in SQL. It's a problem I've never had before. I know what the problem is now after a bit of digging. There is a table with two foreign keys to two different tables. One of those two tables has a foreign key into the other. That's all fine and great. Every foreign key has an ON DELETE CASCADE except for one, which has ON DELETE SET NULL because it makes business sense. Derby is baffing at the creation of the table with the two keys, because deleting a record in one table would cause two different actions in that table. A clearer example, in SQL. CREATE TABLE A ( PrimaryA BIGINT, PRIMARY KEY (PrimaryA) ); CREATE TABLE B ( PrimaryB BIGINT, ForeignA BIGINT NOT NULL, PRIMARY KEY (PrimaryB), FOREIGN KEY (ForeignA) REFERENCES A(PrimaryA) ON DELETE CASCADE ); CREATE TABLE C ( PrimaryC BIGINT, ForeignA BIGINT DEFAULT NULL, ForeignB BIGINT NOT NULL, PRIMARY KEY (PrimaryC), FOREIGN KEY (ForeignA) REFERENCES A(PrimaryA) ON DELETE SET NULL, FOREIGN KEY (ForeignB) REFERENCES B(PrimaryB) ON DELETE CASCADE ); So if those three statements went through (#3 wont) and someone deleted a row in A, there is the possibility that a row in C would want to be deleted and set null at the same time. While I respect the issue of the concurrency, shouldn't the deletion win? It isn't technically against the business logic for that to happen, but if it did I'd expect the row to be deleted. So I obviously need to restructure the schema to avoid this pitfall but I can't really think of a way to do it and was hoping there was some magic keyword that would tell Derby to just delete the row and move on with life. If I do need to restructure, the context is that table C is the auction. Each auction has exactly one feedback entry, which is B. It also has exactly one user who is selling the item (A). Feedback (B) entries keep track of the user (A) who places them. In this respect it is the buyer who leaves the feedback. Now it makes perfect sense to delete the auction (C) if the user (A) who is selling the item has their account deleted. However it doesn't make sense for the auction to be deleted if a rouge user (A) leaves foul feeback (B) on an auction (C) and gets their account deleted. It also doesn't make sense to populate the foreign key to B from C until after the auction is closed and the buyer (A) can leave feedback, which is why it is set to NULL initially. Like I said before, it is technically possible for a user (A) to create an auction (C) and then buy the item from themselves and leave feedback (B) and then have their account deleted. In that case the ON DELETE CASCADE should win from a business logic point of view. I read through this question and the resolution was to normalize, which I've done already. In that question this was linked, and there they offered a solution to the multiple cascade path that I don't fully understand, but I get enough to believe it isn't a solution to this because I don't actually want to delete along both paths. Another thing mentioned was triggers. I have a rough understanding of triggers at a high level but have never designed or coded one before.

I have a Table Enterpries with Primary Key 'RCNO'. All other tables have a one to many relationship with Enterprise via it's RCNO column. I make a Join of all the tables and i get nothing. Below is the query. I would like to attach the tables(sql). I can't find a place to upload. SELECT enterprise.RCNO AS enterprise_RCNO, enterprise.ADDRESS AS enterprise_ADDRESS, enterprise.NAME AS enterprise_NAME, enterprise.WEBSITE AS enterprise_WEBSITE, enterpriseinfo.ACCTYEAR AS enterpriseinfo_ACCTYEAR, enterpriseinfo.BASISFORCOMPLETION AS enterpriseinfo_BASISFORCOMPLETION, enterpriseinfo.HASEQUITYINVESTMENTABROAD AS enterpriseinfo_HASEQUITYINVESTMENTABROAD, enterpriseinfo.ISPARTOFGROUP AS enterpriseinfo_ISPARTOFGROUP, enterpriseinfo.NOOFSTAFF AS enterpriseinfo_NOOFSTAFF, foreignenterprises.ENTERPRISENAME AS foreignenterprises_ENTERPRISENAME, nigerianenterprises.ENTERPRISENAME AS nigerianenterprises_ENTERPRISENAME, principalactivityturnover.ACCTYEAR AS principalactivityturnover_ACCTYEAR, principalactivityturnover.AREAOFACTIVITY AS principalactivityturnover_AREAOFACTIVITY, principalactivityturnover.PERCENTAGECONTRIBUTION AS principalactivityturnover_PERCENTAGECONTRIBUTION, bookvalueofshareholderscapital.ACCTYEAR AS bookvalueofshareholderscapital_ACCTYEAR, bookvalueofshareholderscapital.BOOKVALUE AS bookvalueofshareholderscapital_BOOKVALUE, bookvalueofshareholderscapital.COUNTRY AS bookvalueofshareholderscapital_COUNTRY, bookvalueofshareholderscapital.NAME AS bookvalueofshareholderscapital_NAME, bookvalueofshareholderscapital.PERCENTAGEOWNERSHIP AS bookvalueofshareholderscapital_PERCENTAGEOWNERSHIP, bookvalueofshareholderscapital.REVALUATION AS bookvalueofshareholderscapital_REVALUATION, bookvalueofshareholderscapital.SHARESPURCHASED AS bookvalueofshareholderscapital_SHARESPURCHASED, bookvalueofshareholderscapital.SHARESSOLD AS bookvalueofshareholderscapital_SHARESSOLD, inwarddirectinvestment.ACCTYEAR AS inwarddirectinvestment_ACCTYEAR, inwarddirectinvestment.BOOKSHARECAPITAL AS inwarddirectinvestment_BOOKSHARECAPITAL, inwarddirectinvestment.COUNTRY AS inwarddirectinvestment_COUNTRY, inwarddirectinvestment.NAME AS inwarddirectinvestment_NAME, inwarddirectinvestment.PURCHASEDSHAREFROMINVESTOR AS inwarddirectinvestment_PURCHASEDSHAREFROMINVESTOR, inwarddirectinvestment.SALESOFSHARETOINVESTOR AS inwarddirectinvestment_SALESOFSHARETOINVESTOR, inwarddirectinvestment.SHARECAPITALPERCENTAGE AS inwarddirectinvestment_SHARECAPITALPERCENTAGE, inwarddirectinvestmentsubsidiary.ACCTYEAR AS inwarddirectinvestmentsubsidiary_ACCTYEAR, inwarddirectinvestmentsubsidiary.BOOKSHARECAPITAL AS inwarddirectinvestmentsubsidiary_BOOKSHARECAPITAL, inwarddirectinvestmentsubsidiary.COUNTRY AS inwarddirectinvestmentsubsidiary_COUNTRY, inwarddirectinvestmentsubsidiary.NAME AS inwarddirectinvestmentsubsidiary_NAME, inwarddirectinvestmentsubsidiary.PURCHASEDSHAREFROMINVESTOR AS inwarddirectinvestmentsubsidiary_PURCHASEDSHAREFROMINVESTOR, inwarddirectinvestmentsubsidiary.SALESOFSHARETOINVESTOR AS inwarddirectinvestmentsubsidiary_SALESOFSHARETOINVESTOR, inwarddirectinvestmentsubsidiary.SHARECAPITALPERCENTAGE AS inwarddirectinvestmentsubsidiary_SHARECAPITALPERCENTAGE, inwarddirectinvestmentsubsidiaryp.ACCTYEAR AS inwarddirectinvestmentsubsidiaryp_ACCTYEAR, inwarddirectinvestmentsubsidiaryp.BOOKSHARECAPITAL AS inwarddirectinvestmentsubsidiaryp_BOOKSHARECAPITAL, inwarddirectinvestmentsubsidiaryp.COUNTRY AS inwarddirectinvestmentsubsidiaryp_COUNTRY, inwarddirectinvestmentsubsidiaryp.NAME AS inwarddirectinvestmentsubsidiaryp_NAME, inwarddirectinvestmentsubsidiaryp.PURCHASEDSHAREFROMINVESTOR AS inwarddirectinvestmentsubsidiaryp_PURCHASEDSHAREFROMINVESTOR, inwarddirectinvestmentsubsidiaryp.SALESOFSHARETOINVESTOR AS inwarddirectinvestmentsubsidiaryp_SALESOFSHARETOINVESTOR, inwarddirectinvestmentsubsidiaryp.SHARECAPITALPERCENTAGE AS inwarddirectinvestmentsubsidiaryp_SHARECAPITALPERCENTAGE, inwardportfolioinvestmentsubsidiary.ACCTYEAR AS inwardportfolioinvestmentsubsidiary_ACCTYEAR, inwardportfolioinvestmentsubsidiary.COUNTRY AS inwardportfolioinvestmentsubsidiary_COUNTRY, inwardportfolioinvestmentsubsidiary.MARKETVALUE AS inwardportfolioinvestmentsubsidiary_MARKETVALUE, inwardportfolioinvestmentsubsidiary.NAME AS inwardportfolioinvestmentsubsidiary_NAME, inwardportfolioinvestmentsubsidiary.PURCHASEOFSHARES AS inwardportfolioinvestmentsubsidiary_PURCHASEOFSHARES, inwardportfolioinvestmentsubsidiary.SALESOFSHARES AS inwardportfolioinvestmentsubsidiary_SALESOFSHARES, inwardportfolioinvestmentsubsidiary.SHARECAPITALPERCENTAGE AS inwardportfolioinvestmentsubsidiary_SHARECAPITALPERCENTAGE, inwardportfolioinvestmentsubsidiaryp.ACCTYEAR AS inwardportfolioinvestmentsubsidiaryp_ACCTYEAR, inwardportfolioinvestmentsubsidiaryp.COUNTRY AS inwardportfolioinvestmentsubsidiaryp_COUNTRY, inwardportfolioinvestmentsubsidiaryp.MARKETVALUE AS inwardportfolioinvestmentsubsidiaryp_MARKETVALUE, inwardportfolioinvestmentsubsidiaryp.NAME AS inwardportfolioinvestmentsubsidiaryp_NAME, inwardportfolioinvestmentsubsidiaryp.PURCHASEOFSHARES AS inwardportfolioinvestmentsubsidiaryp_PURCHASEOFSHARES, inwardportfolioinvestmentsubsidiaryp.SALESOFSHARES AS inwardportfolioinvestmentsubsidiaryp_SALESOFSHARES, inwardportfolioinvestmentsubsidiaryp.SHARECAPITALPERCENTAGE AS inwardportfolioinvestmentsubsidiaryp_SHARECAPITALPERCENTAGE, externaldebtnonresident.ACCTYEAR AS externaldebtnonresident_ACCTYEAR, externaldebtnonresident.INTERESTPAYABLE AS externaldebtnonresident_INTERESTPAYABLE, externaldebtnonresident.NETTRASACTIONS AS externaldebtnonresident_NETTRASACTIONS, externaldebtnonresident.OTHERCHARGES AS externaldebtnonresident_OTHERCHARGES, externaldebtnonresident.STOCKOFDEBT AS externaldebtnonresident_STOCKOFDEBT, externaldebtnonresidentsubsidiary.ACCTYEAR AS externaldebtnonresidentsubsidiary_ACCTYEAR, externaldebtnonresidentsubsidiary.INTERESTPAYABLE AS externaldebtnonresidentsubsidiary_INTERESTPAYABLE, externaldebtnonresidentsubsidiary.NETTRASACTIONS AS externaldebtnonresidentsubsidiary_NETTRASACTIONS, externaldebtnonresidentsubsidiary.OTHERCHARGES AS externaldebtnonresidentsubsidiary_OTHERCHARGES, externaldebtnonresidentsubsidiary.STOCKOFDEBT AS externaldebtnonresidentsubsidiary_STOCKOFDEBT, externaldebtnonresidentsubsidiaryp.ACCTYEAR AS externaldebtnonresidentsubsidiaryp_ACCTYEAR, externaldebtnonresidentsubsidiaryp.INTERESTPAYABLE AS externaldebtnonresidentsubsidiaryp_INTERESTPAYABLE, externaldebtnonresidentsubsidiaryp.NETTRASACTIONS AS externaldebtnonresidentsubsidiaryp_NETTRASACTIONS, externaldebtnonresidentsubsidiaryp.OTHERCHARGES AS externaldebtnonresidentsubsidiaryp_OTHERCHARGES, externaldebtnonresidentsubsidiaryp.STOCKOFDEBT AS externaldebtnonresidentsubsidiaryp_STOCKOFDEBT, externaldebtunrelated.ACCTYEAR AS externaldebtunrelated_ACCTYEAR, externaldebtunrelated.CLOSINGBALANCE AS externaldebtunrelated_CLOSINGBALANCE, externaldebtunrelated.DEBTINSTRUMENT AS externaldebtunrelated_DEBTINSTRUMENT, externaldebtunrelated.RECIEPTS AS externaldebtunrelated_RECIEPTS, externaldebtunrelated.REPAYMENTS AS externaldebtunrelated_REPAYMENTS, externaldebtunrelated.REVALUATIONS AS externaldebtunrelated_REVALUATIONS, externaldebtunrelated.INTERESTPAYABLE AS externaldebtunrelated_INTERESTPAYABLE, externaldebtliabilitiescountry.ACCTYEAR AS externaldebtliabilitiescountry_ACCTYEAR, externaldebtliabilitiescountry.COUNTRY AS externaldebtliabilitiescountry_COUNTRY, externaldebtliabilitiescountry.DEBTAMOUNT AS externaldebtliabilitiescountry_DEBTAMOUNT, externaldebtliabilitiescountry.DEBTINSTRUMENT AS externaldebtliabilitiescountry_DEBTINSTRUMENT, otherincomefromnonresidents.ACCTYEAR AS otherincomefromnonresidents_ACCTYEAR, otherincomefromnonresidents.INCOMEITEM AS otherincomefromnonresidents_INCOMEITEM, otherincomefromnonresidents.INCOMEVALUE AS otherincomefromnonresidents_INCOMEVALUE, shareholderscapitalofsubsidiary.ACCTYEAR AS shareholderscapitalofsubsidiary_ACCTYEAR, shareholderscapitalofsubsidiary.BOOKVALUEOFSHAREHOLDERS AS shareholderscapitalofsubsidiary_BOOKVALUEOFSHAREHOLDERS, shareholderscapitalofsubsidiary.CHANGEINPAIDUPSHARECAPITAL AS shareholderscapitalofsubsidiary_CHANGEINPAIDUPSHARECAPITAL, shareholderscapitalofsubsidiary.CHANGEINRETAINEDEARNING AS shareholderscapitalofsubsidiary_CHANGEINRETAINEDEARNING, shareholderscapitalofsubsidiary.COUNTRY AS shareholderscapitalofsubsidiary_COUNTRY, shareholderscapitalofsubsidiary.NAME AS shareholderscapitalofsubsidiary_NAME, shareholderscapitalofsubsidiary.REVALUATION AS shareholderscapitalofsubsidiary_REVALUATION, enterpriseinvestments.ACCTYEAR AS enterpriseinvestments_ACCTYEAR, enterpriseinvestments.INVESTMENTAMOUNT AS enterpriseinvestments_INVESTMENTAMOUNT, enterpriseinvestments.INVESTMENTITEM AS enterpriseinvestments_INVESTMENTITEM, subsidiaryincomeaccount.ACCTYEAR AS subsidiaryincomeaccount_ACCTYEAR, subsidiaryincomeaccount.DIVIDENSANDPROFITSPAID AS subsidiaryincomeaccount_DIVIDENSANDPROFITSPAID, subsidiaryincomeaccount.ENTERPRISENAME AS subsidiaryincomeaccount_ENTERPRISENAME, subsidiaryincomeaccount.NETINCOMEAFTERTAX AS subsidiaryincomeaccount_NETINCOMEAFTERTAX, subsidiaryincomeaccount.RETAINEDEARNING AS subsidiaryincomeaccount_RETAINEDEARNING, equityclaimsofparent.COUNTRY AS equityclaimsofparent_COUNTRY, equityclaimsofparent.EQUITYVALUEHELD AS equityclaimsofparent_EQUITYVALUEHELD, equityclaimsofparent.NAME AS equityclaimsofparent_NAME, equityclaimsofparent.NETPURCHASES AS equityclaimsofparent_NETPURCHASES, equityclaimsofparent.NETSALES AS equityclaimsofparent_NETSALES, equityclaimsofparent.OTHERCHARGESREVALUATION AS equityclaimsofparent_OTHERCHARGESREVALUATION, equityclaimsofparent.PERCENTAGESHARES AS equityclaimsofparent_PERCENTAGESHARES, equityclaimsofparent.ACCTYEAR AS equityclaimsofparent_ACCTYEAR, equityclaimsofparentsub.COUNTRY AS equityclaimsofparentsub_COUNTRY, equityclaimsofparentsub.EQUITYVALUEHELD AS equityclaimsofparentsub_EQUITYVALUEHELD, equityclaimsofparentsub.NAME AS equityclaimsofparentsub_NAME, equityclaimsofparentsub.NETPURCHASES AS equityclaimsofparentsub_NETPURCHASES, equityclaimsofparentsub.NETSALES AS equityclaimsofparentsub_NETSALES, equityclaimsofparentsub.OTHERCHARGESREVALUATION AS equityclaimsofparentsub_OTHERCHARGESREVALUATION, equityclaimsofparentsub.PERCENTAGESHARES AS equityclaimsofparentsub_PERCENTAGESHARES, equityclaimsofparentsub.ACCTYEAR AS equityclaimsofparentsub_ACCTYEAR, equityclaimsofparentsubp.COUNTRY AS equityclaimsofparentsubp_COUNTRY, equityclaimsofparentsubp.EQUITYVALUEHELD AS equityclaimsofparentsubp_EQUITYVALUEHELD, equityclaimsofparentsubp.NAME AS equityclaimsofparentsubp_NAME, equityclaimsofparentsubp.NETPURCHASES AS equityclaimsofparentsubp_NETPURCHASES, equityclaimsofparentsubp.NETSALES AS equityclaimsofparentsubp_NETSALES, equityclaimsofparentsubp.OTHERCHARGESREVALUATION AS equityclaimsofparentsubp_OTHERCHARGESREVALUATION, equityclaimsofparentsubp.PERCENTAGESHARES AS equityclaimsofparentsubp_PERCENTAGESHARES, equityclaimsofparentsubp.ACCTYEAR AS equityclaimsofparentsubp_ACCTYEAR, externallendingtosubsidiary.ACCTYEAR AS externallendingtosubsidiary_ACCTYEAR, externallendingtosubsidiary.AGGOFEXTERNALLENDING AS externallendingtosubsidiary_AGGOFEXTERNALLENDING, externallendingtosubsidiary.ENTITYNAME AS externallendingtosubsidiary_ENTITYNAME, externallendingtosubsidiary.INTERESTRECIEVABLE AS externallendingtosubsidiary_INTERESTRECIEVABLE, externallendingtosubsidiary.NETLENDING AS externallendingtosubsidiary_NETLENDING, externallendingtosubsidiary.OTHERCHANGESANDREVALUATION AS externallendingtosubsidiary_OTHERCHANGESANDREVALUATION, externallendingrelatedparent.ACCTYEAR AS externallendingrelatedparent_ACCTYEAR, externallendingrelatedparent.AGGEXTERNALLENDING AS externallendingrelatedparent_AGGEXTERNALLENDING, externallendingrelatedparent.INTERESTRECIEVABLE AS externallendingrelatedparent_INTERESTRECIEVABLE, externallendingrelatedparent.NETLENDING AS externallendingrelatedparent_NETLENDING, externallendingrelatedparent.REVALUATIONS AS externallendingrelatedparent_REVALUATIONS, externallendingrelatedsubparent.ACCTYEAR AS externallendingrelatedsubparent_ACCTYEAR, externallendingrelatedsubparent.AGGEXTERNALLENDING AS externallendingrelatedsubparent_AGGEXTERNALLENDING, externallendingrelatedsubparent.INTERESTRECIEVABLE AS externallendingrelatedsubparent_INTERESTRECIEVABLE, externallendingrelatedsubparent.NETLENDING AS externallendingrelatedsubparent_NETLENDING, externallendingrelatedsubparent.REVALUATIONS AS externallendingrelatedsubparent_REVALUATIONS, debtinstrumentsheldunrelated.ACCTYEAR AS debtinstrumentsheldunrelated_ACCTYEAR, debtinstrumentsheldunrelated.CLOSINGBALANCE AS debtinstrumentsheldunrelated_CLOSINGBALANCE, debtinstrumentsheldunrelated.DEBTINSTRUMENT AS debtinstrumentsheldunrelated_DEBTINSTRUMENT, debtinstrumentsheldunrelated.INTERESTRECIEVABLE AS debtinstrumentsheldunrelated_INTERESTRECIEVABLE, debtinstrumentsheldunrelated.OPENINGBALANCE AS debtinstrumentsheldunrelated_OPENINGBALANCE, debtinstrumentsheldunrelated.PRINCIPALREPAYMENT AS debtinstrumentsheldunrelated_PRINCIPALREPAYMENT, debtinstrumentsheldunrelated.PURCHASE AS debtinstrumentsheldunrelated_PURCHASE, debtinstrumentsheldunrelated.REVALUATION AS debtinstrumentsheldunrelated_REVALUATION, debtinstrumenturcountry.ACCTYEAR AS debtinstrumenturcountry_ACCTYEAR, debtinstrumenturcountry.DEBTAMOUNT AS debtinstrumenturcountry_DEBTAMOUNT, debtinstrumenturcountry.DEBTINSTRUMENT AS debtinstrumenturcountry_DEBTINSTRUMENT, debtinstrumenturcountry.DEBTORCOUNTRY AS debtinstrumenturcountry_DEBTORCOUNTRY, otherexpenditurenr.ACCTYEAR AS otherexpenditurenr_ACCTYEAR, otherexpenditurenr.EXPENSEITEM AS otherexpenditurenr_EXPENSEITEM, otherexpenditurenr.EXPENSEVALUE AS otherexpenditurenr_EXPENSEVALUE, depositwithnonresidentbanks.ACCTYEAR AS depositwithnonresidentbanks_ACCTYEAR, depositwithnonresidentbanks.BANKNAME AS depositwithnonresidentbanks_BANKNAME, depositwithnonresidentbanks.CHANGESDUETOEXCHANGE AS depositwithnonresidentbanks_CHANGESDUETOEXCHANGE, depositwithnonresidentbanks.CURRENTOUTSTANDINGBALANCE AS depositwithnonresidentbanks_CURRENTOUTSTANDINGBALANCE, depositwithnonresidentbanks.TRANSACTIONSTHISYEAR AS depositwithnonresidentbanks_TRANSACTIONSTHISYEAR, finderivativecontractwithnr.ACCTYEAR AS finderivativecontractwithnr_ACCTYEAR, finderivativecontractwithnr.CASHRECIEPTSANDDERIVATIVEPAYMENT AS finderivativecontractwithnr_CASHRECIEPTSANDDERIVATIVEPAYMENT, finderivativecontractwithnr.NONRESIDENTNAME AS finderivativecontractwithnr_NONRESIDENTNAME, finderivativecontractwithnr.OUTSTANDINGMARKETPOSITION AS finderivativecontractwithnr_OUTSTANDINGMARKETPOSITION, foriegngovtdebtsecuritycountry.ACCTYEAR AS foriegngovtdebtsecuritycountry_ACCTYEAR, foriegngovtdebtsecuritycountry.CHANGESINVALUEDUETOEXCHANGE AS foriegngovtdebtsecuritycountry_CHANGESINVALUEDUETOEXCHANGE, foriegngovtdebtsecuritycountry.INTERESTRECIEVABLE AS foriegngovtdebtsecuritycountry_INTERESTRECIEVABLE, foriegngovtdebtsecuritycountry.NAME AS foriegngovtdebtsecuritycountry_NAME, foriegngovtdebtsecuritycountry.OTHERCHANGESINCLUDINGREVALUATION AS foriegngovtdebtsecuritycountry_OTHERCHANGESINCLUDINGREVALUATION, foriegngovtdebtsecuritycountry.OUTSTANDINGMARKETVALUE AS foriegngovtdebtsecuritycountry_OUTSTANDINGMARKETVALUE, dividendearningsprofit.ACCTYEAR AS dividendearningsprofit_ACCTYEAR, dividendearningsprofit.DIVIDENDSPAID AS dividendearningsprofit_DIVIDENDSPAID, dividendearningsprofit.INTERESTPAID AS dividendearningsprofit_INTERESTPAID, dividendearningsprofit.INTERESTRECIEVED AS dividendearningsprofit_INTERESTRECIEVED, dividendearningsprofit.NETINCOMEAFTERTAX AS dividendearningsprofit_NETINCOMEAFTERTAX, dividendearningsprofit.REALISEDHOLDINGS AS dividendearningsprofit_REALISEDHOLDINGS, dividendearningsprofit.RETAINEDEARNING AS dividendearningsprofit_RETAINEDEARNING FROM enterprise enterprise INNER JOIN enterpriseinfo enterpriseinfo ON enterprise.RCNO = enterpriseinfo.RCNO INNER JOIN foreignenterprises foreignenterprises ON enterprise.RCNO = foreignenterprises.RCNO INNER JOIN nigerianenterprises nigerianenterprises ON enterprise.RCNO = nigerianenterprises.RCNO INNER JOIN principalactivityturnover principalactivityturnover ON enterprise.RCNO = principalactivityturnover.RCNO INNER JOIN bookvalueofshareholderscapital bookvalueofshareholderscapital ON enterprise.RCNO = bookvalueofshareholderscapital.RCNO INNER JOIN inwarddirectinvestment inwarddirectinvestment ON enterprise.RCNO = inwarddirectinvestment.RCNO INNER JOIN inwarddirectinvestmentsubsidiary inwarddirectinvestmentsubsidiary ON enterprise.RCNO = inwarddirectinvestmentsubsidiary.RCNO INNER JOIN inwarddirectinvestmentsubsidiaryp inwarddirectinvestmentsubsidiaryp ON enterprise.RCNO = inwarddirectinvestmentsubsidiaryp.RCNO INNER JOIN inwardportfolioinvestmentsubsidiary inwardportfolioinvestmentsubsidiary ON enterprise.RCNO = inwardportfolioinvestmentsubsidiary.RCNO INNER JOIN inwardportfolioinvestmentsubsidiaryp inwardportfolioinvestmentsubsidiaryp ON enterprise.RCNO = inwardportfolioinvestmentsubsidiaryp.RCNO INNER JOIN externaldebtnonresident externaldebtnonresident ON enterprise.RCNO = externaldebtnonresident.RCNO INNER JOIN externaldebtnonresidentsubsidiary externaldebtnonresidentsubsidiary ON enterprise.RCNO = externaldebtnonresidentsubsidiary.RCNO INNER JOIN externaldebtnonresidentsubsidiaryp externaldebtnonresidentsubsidiaryp ON enterprise.RCNO = externaldebtnonresidentsubsidiaryp.RCNO INNER JOIN externaldebtunrelated externaldebtunrelated ON enterprise.RCNO = externaldebtunrelated.RCNO INNER JOIN externaldebtliabilitiescountry externaldebtliabilitiescountry ON enterprise.RCNO = externaldebtliabilitiescountry.RCNO INNER JOIN otherincomefromnonresidents otherincomefromnonresidents ON enterprise.RCNO = otherincomefromnonresidents.RCNO INNER JOIN shareholderscapitalofsubsidiary shareholderscapitalofsubsidiary ON enterprise.RCNO = shareholderscapitalofsubsidiary.RCNO INNER JOIN enterpriseinvestments enterpriseinvestments ON enterprise.RCNO = enterpriseinvestments.RCNO INNER JOIN subsidiaryincomeaccount subsidiaryincomeaccount ON enterprise.RCNO = subsidiaryincomeaccount.RCNO INNER JOIN equityclaimsofparent equityclaimsofparent ON enterprise.RCNO = equityclaimsofparent.RCNO INNER JOIN equityclaimsofparentsub equityclaimsofparentsub ON enterprise.RCNO = equityclaimsofparentsub.RCNO INNER JOIN equityclaimsofparentsubp equityclaimsofparentsubp ON enterprise.RCNO = equityclaimsofparentsubp.RCNO INNER JOIN externallendingtosubsidiary externallendingtosubsidiary ON enterprise.RCNO = externallendingtosubsidiary.RCNO INNER JOIN externallendingrelatedparent externallendingrelatedparent ON enterprise.RCNO = externallendingrelatedparent.RCNO INNER JOIN externallendingrelatedsubparent externallendingrelatedsubparent ON enterprise.RCNO = externallendingrelatedsubparent.RCNO INNER JOIN debtinstrumentsheldunrelated debtinstrumentsheldunrelated ON enterprise.RCNO = debtinstrumentsheldunrelated.RCNO INNER JOIN debtinstrumenturcountry debtinstrumenturcountry ON enterprise.RCNO = debtinstrumenturcountry.RCNO INNER JOIN otherexpenditurenr otherexpenditurenr ON enterprise.RCNO = otherexpenditurenr.RCNO INNER JOIN depositwithnonresidentbanks depositwithnonresidentbanks ON enterprise.RCNO = depositwithnonresidentbanks.RCNO INNER JOIN finderivativecontractwithnr finderivativecontractwithnr ON enterprise.RCNO = finderivativecontractwithnr.RCNO INNER JOIN foriegngovtdebtsecuritycountry foriegngovtdebtsecuritycountry ON enterprise.RCNO = foriegngovtdebtsecuritycountry.RCNO, dividendearningsprofit dividendearningsprofit WHERE enterprise.RCNO = 'RC 10000'

I'm using a Derby database, mass insertions slow down to 1/4 to 1/6 the speed once there are 1.2 million records (about 6 GB database size, one major table, other tables are tiny). Is that normal? Is there something I can do to tweak it and make it run faster? I compressed the main table. # Questions - Should I consider other databases? - How could I compare Derby's performance, with, say, MySQL in this kind of scenario? - Are there such comparisons already? (Running on Windows 7-based server R2, 16GB RAM, 8-core machine).

I am working with an Apache Derby/JavaDB database via JDBC. Before have I been working with MySQL and used phpMyAdmin as a good Database Management tool. Is there any good desktop application for managing databases over JDBC?