Derby driver class name v10.15.1.3
Hi, I've been trying to configure a Spring boot application to work with version 10.15.1.3 of Derby client and I need to specify the driver name in the applications.properties file (spring.datasource.driver-class-name= *). Prior to this version I would have used org.apache.derby.jdbc.ClientDriver in the derbyclient.jar .. but it not longer exists in 10.15.1.3 of Derby? I've looked through all the other Derby artifacts for this version and cannot find the class. Should I be doing something different with this version of Derby? (because of the modularisation introduced in Java 9?) I couldn't see anything in the documentation for v10.15.1.3. Thanks Kerry
RE: Derby driver class name v10.15.1.3
Kerry, Looks like it moved to derbytools.jar. lib\derbytools.jar->class=org/apache/derby/jdbc/ClientDriver.class Jerry -Original Message- From: Kerry Sent: Tuesday, October 22, 2019 3:56 PM To: derby-user@db.apache.org Subject: Derby driver class name v10.15.1.3 Hi, I've been trying to configure a Spring boot application to work with version 10.15.1.3 of Derby client and I need to specify the driver name in the applications.properties file (spring.datasource.driver-class-name= *). Prior to this version I would have used org.apache.derby.jdbc.ClientDriver in the derbyclient.jar .. but it not longer exists in 10.15.1.3 of Derby? I've looked through all the other Derby artifacts for this version and cannot find the class. Should I be doing something different with this version of Derby? (because of the modularisation introduced in Java 9?) I couldn't see anything in the documentation for v10.15.1.3. Thanks Kerry
Re: Derby driver class name v10.15.1.3
Thanks Jerry, The one artefact I didn't check, but that doesn't seem right somehow now .. seeing as there is still derbyclient? Should documentation be updated too to reflect that?: https://db.apache.org/derby/docs/10.15/getstart/rgslib46043.html I can see if I can raise a ticket if that;s the case Kerry On 22/10/2019 22:24, Jerry Lampi wrote: > Kerry, > Looks like it moved to derbytools.jar. > lib\derbytools.jar->class=org/apache/derby/jdbc/ClientDriver.class > Jerry > > -Original Message- > From: Kerry > Sent: Tuesday, October 22, 2019 3:56 PM > To: derby-user@db.apache.org > Subject: Derby driver class name v10.15.1.3 > > Hi, > > I've been trying to configure a Spring boot application to work with version > 10.15.1.3 of Derby client and I need to specify the driver name in the > applications.properties file (spring.datasource.driver-class-name= *). > Prior to this version I would have used org.apache.derby.jdbc.ClientDriver in > the derbyclient.jar .. but it not longer exists in 10.15.1.3 of Derby? I've > looked through all the other Derby artifacts for this version and cannot find > the class. > > Should I be doing something different with this version of Derby? (because of > the modularisation introduced in Java 9?) I couldn't see anything in the > documentation for v10.15.1.3. > > Thanks > > Kerry > >
RE: Avoid locking on DELETE
Peter, We have the same situation. We solved the DB locking issue by doing many smaller deletes instead of one large one. Like you, we have a date column to base our delete off of. Let’s say you want to delete all rows whose created_at is < yesterday. What you can do is take the date representing yesterday and add an hour or two to it. Now, starting at the new date, do a delete. You will delete fewer (or no rows), but then you do another delete after adding one hour; maybe you only add 10 minutes – whatever, but the point is to delete fewer rows each time to prevent locking. Of course this will result in a longer overall “purge” duration, but at least in our case, we didn’t care – the goal is to not LOCK out everyone else while a delete is being run. The finest granularity we required was 15 minutes. It sounds like you need more granularity. Here’s sample code. This is meant to get a feel for what to do. It is working code, but custom to our sue case. I imagine the main thing you would tweak is method getTimeToAddToIntervalBasedOnRowsdDeleted’s return value. Ignore convertTimeFromSystem390ToMilliseconds(); hopefully you just have a Date object and can work with milliseconds directly. /** * * @param stckTimestamp - Purge rows older than this date * @param tableName - mytable * @param timestampName - TIMESTAMP or SESSTERMTIME */ private static long doIncrementalTimePurge(long stckTimestamp, String tableName, String timestampName) { long totalRowsDeleted = 0; try { Class.forName(driver); //System.out.println("STCK before (purge everything older than this date): " + stckTimestamp + " = " + DateUtil.convertTimeFromSystem390ToIsoString(stckTimestamp)); long stckTimestampMs = DateUtil.convertTimeFromSystem390ToMilliseconds(stckTimestamp); int daysToGoBack = 5; stckTimestampMs-=daysToGoBack*24)+1)*60)*60)*1000; // Subtract daysToGoBack days plus 1 hour 49*60)*60)*1000)=176,400,000) in milliseconds to target "older than" date long startingTimestamp = DateUtil.convertTimeFromMillisecondsToSystem390(stckTimestampMs); //System.out.println("STCK after (begin at this date & increment upward): " + startingTimestamp + " = " + DateUtil.convertTimeFromSystem390ToIsoString(startingTimestamp)); Connection deleteConnection = DriverManager.getConnection(url, user, psw); Statement deleteStatement = deleteConnection.createStatement(); //int cnt = 0; String sqlDeletePrefix = "delete from " + schema + "." + tableName + " where " + timestampName + " < "; int prevIterationRowsDeleted = 1; // non-zero so as not to report two consecutive zero row deletion intervals boolean finalTimeStampProcessed = false; while(startingTimestamp <= stckTimestamp) { String sqlDelete = sqlDeletePrefix + startingTimestamp; int rowsDeletedThisIteration = deleteStatement.executeUpdate(sqlDelete); totalRowsDeleted+=rowsDeletedThisIteration; // System.out.println("rowsDeletedThisIteration="+nbrFormatter.format(rowsDeletedThisIteration)); if(finalTimeStampProcessed == true) { break; // we're done } // System.out.println("STCK before ("+cnt+"): " + startingTimestamp + " = " + DateUtil.convertTimeFromSystem390ToIsoString(startingTimestamp)); long timeToAdd = getTimeToAddToIntervalBasedOnRowsdDeleted(rowsDeletedThisIteration, prevIterationRowsDeleted); stckTimestampMs+=timeToAdd; startingTimestamp = DateUtil.convertTimeFromMillisecondsToSystem390(stckTimestampMs); prevIterationRowsDeleted = rowsDeletedThisIteration; if(startingTimestamp > stckTimestamp) { //System.out.println("Set final purge Interval"); startingTimestamp = stckTimestamp; // final purge to honor actual timestamp finalTimeStampProcessed = true; } // System.out.println("STCK after ("+cnt+"): " + startingTimestamp + " = " + DateUtil.convertTimeFromSystem390ToIsoString(startingTimestamp)); // cnt++; } deleteStatement.close(); deleteConnection.close(); // System.out.println("totalRowsDeleted="+nbrFormatter.format(totalRowsDeleted)); } catch (SQLException sqle) { sqle.printStackTrace();
Re: Derby driver class name v10.15.1.3
Thanks for this discussion, Kerry and Jerry. Please file a docs bug and suggest how this can be described better. You are right, it is the modularization of Derby which broke this behavior for you. Before modularization, the two drivers (embedded and network) lived in the same package but separate jar files (derby.jar and derbyclient.jar). That had to be untangled in order to achieve jigsaw-ready partitioning of packages across jars. If you rely on driver autoloading and simply connect via DriverManager.getConnection() using a JDBC URL, then the tools jar is NOT needed. But if you connect via a DataSource, then you need derbytools.jar, which contains the old drivers. You also need derbytools.jar if you boot the drivers the very old-fashioned way via Class.forName(). I believe that the original Spring framework pre-dates the driver autoloading introduced by Java 6. It seems that you still have to tell Spring the driver class name. Our documentation should say more about this old usage pattern. Thanks in advance for the doc issue and for your recommendations. -Rick On 10/22/19 2:48 PM, Kerry wrote: Thanks Jerry, The one artefact I didn't check, but that doesn't seem right somehow now .. seeing as there is still derbyclient? Should documentation be updated too to reflect that?: https://db.apache.org/derby/docs/10.15/getstart/rgslib46043.html I can see if I can raise a ticket if that;s the case Kerry On 22/10/2019 22:24, Jerry Lampi wrote: Kerry, Looks like it moved to derbytools.jar. lib\derbytools.jar->class=org/apache/derby/jdbc/ClientDriver.class Jerry -Original Message- From: Kerry Sent: Tuesday, October 22, 2019 3:56 PM To: derby-user@db.apache.org Subject: Derby driver class name v10.15.1.3 Hi, I've been trying to configure a Spring boot application to work with version 10.15.1.3 of Derby client and I need to specify the driver name in the applications.properties file (spring.datasource.driver-class-name= *). Prior to this version I would have used org.apache.derby.jdbc.ClientDriver in the derbyclient.jar .. but it not longer exists in 10.15.1.3 of Derby? I've looked through all the other Derby artifacts for this version and cannot find the class. Should I be doing something different with this version of Derby? (because of the modularisation introduced in Java 9?) I couldn't see anything in the documentation for v10.15.1.3. Thanks Kerry