optimizer override [was: Re: When to shut down a database]
Are you sure you are looking at the correct override (in case you have several)? I tried the one below and it didn't give me any syntax error. A similar sample below also works as expected: $ java -jar ~/java/sb/sb0/jars/sane/derbyrun.jar ij foo.sh ij version 10.11 ij connect 'jdbc:derby:memory:wombat;create=true'; ij create table t(i int); 0 rows inserted/updated/deleted ij create index my_index on t(i); 0 rows inserted/updated/deleted ij SELECT i FROM app.t --DERBY-PROPERTIES index = MY_INDEX where ( i 0 ) ; I --- 0 rows selected On 11. april 2014 18:16, Frank Rivera wrote: BTW the error is this Invalid syntax for optimizer overrides. The syntax should be -- DERBY-PROPERTIES propertyName = value [, propertyName = value]* Sincerely, Frank Rivera LogicBit Software - Research Triangle Park, North Carolina On Apr 11, 2014, at 12:15 PM, Frank Rivera frank.riv...@houdiniesq.com mailto:frank.riv...@houdiniesq.com wrote: We have a simple query using LIKE but is very slow. We need to tell the optimizer which index to use but the override DERBY-PROPERTIES statement returns an error. here is the SQL. SELECT esq.attachment.guid FROM esq.attachment --DERBY-PROPERTIES index = ATTACHMENT_LINKNAMES_INDEX where ( esq.attachment.file_name LIKE ‘%drisco%' OR esq.attachment.file_index_name LIKE ‘%drisco%' ) AND ( esq.attachment.link_guid IS NOT NULL AND esq.attachment.link_table='mat' AND esq.attachment.link_guid IN ( SELECT esq.matter.guid FROM esq.matter WHERE esq.matter.guid=esq.attachment.link_guid AND ( ( esq.matter.grp_guid=0 OR esq.matter.grp_guid IS NULL ) OR ( esq.matter.grp_guid0 AND esq.matter.grp_guid IN ( 2,1,6,4,3 ) ) ) ) ) ; Sincerely, Frank Rivera LogicBit Software - Research Triangle Park, North Carolina
Re: When to shut down a database
Hi Chux, The default (and maximum) size for a CLOB is 2,147,483,647 characters. See http://db.apache.org/derby/docs/10.10/ref/rrefclob.html. If you have found a place in the documentation where some other limit is stated, could you let us know where that is so that we can correct that misleading statement? Thanks, -Rick On 4/10/14 9:09 PM, Chux wrote: Awesome insights guys, thanks for all your help. BTW, I could not access the online documentation for some reason. Although I read somewhere that 64k is the maximum size you can allocate a clob on embedded mode. Is this correct? I would like to know what the limit is. variable clob(64 K) Thanks, Chux On Thu, Apr 10, 2014 at 5:32 AM, Dag H. Wanvik dag.wan...@oracle.com mailto:dag.wan...@oracle.com wrote: On 09. april 2014 17:51, Rick Hillegas wrote: On 4/8/14 2:00 AM, Chux wrote: Hey Dag, Thanks for your insight. I'm using this as an embedded DB in a Java FX desktop application. This is a dumb question but would you recommend shutting down the database ever after a transaction? Like after you create a record then you shut it down after commit. Depends on the application. If the database holds some kind of infrequently referenced metadata, so that say, it is only queried or updated once a day, then you could consider an on demand model where the database is booted for each query/update, then the query results are returned, then the database is shut down so that it doesn't consume any resources. The big extra cost of an on demand database would be this: query/update time would be substantially longer since every query/update involves booting the database, compiling the query/update, and gracefully closing the database; that cost is on top of the steady-state cost of running a pre-compiled query/update. In such a scenario one might want to shut down the engine, too, not just the database. Note that shutting down the database will resources, but if the engine is still running, one can further release resources by shutting that down as well. Cf. http://db.apache.org/derby/docs/10.10/devguide/tdevdvlp20349.html (engine shutdown) and http://db.apache.org/derby/docs/10.10/devguide/tdevdvlp40464.html (shutdown database) Thanks, Dag Hope this helps, -Rick Best, Chux On Tue, Apr 8, 2014 at 12:32 AM, Dag H. Wanvik dag.wan...@oracle.com mailto:dag.wan...@oracle.com mailto:dag.wan...@oracle.com mailto:dag.wan...@oracle.com wrote: On 06. april 2014 21:02, George Toma wrote: Hi Chux, In my opinion the example from app. referred at commit the transaction OR close the connection ( a connection could be transacted too ), and not to shutdown the db. If the business rule specifies that the db. needs to be shutdown when the app. is shutdown, then so be it. Normally the db is not shutdown, not even when the app is down. This is true for a client/server application. For use with embedded Derby, one would normally close down the database (and the database engine) before exiting the application. If one neglects to do so, one would see longer start-up times as Dyre indicated. Thanks, Dag Cheers, George On Sunday, April 6, 2014 7:14 PM, Chux chu...@gmail.com mailto:chu...@gmail.com mailto:chu...@gmail.com mailto:chu...@gmail.com wrote: Hello guys, I read in a sample app that you've got to shutdown a database. I was just confused if you need to shut it down on every connection transaction or just shut it down on application close, in my case a desktop applicaiton. Best, Chux
Re: When to shut down a database
Hello Rick, Thanks for the clarification. This really helped me a lot. I;m trying to do a mobile client synchronizing data with a server side DB. Yeah I think I saw it in a forum somewhere. I come across it I will let you know. Thanks again. Best, Chux On Fri, Apr 11, 2014 at 9:41 PM, Rick Hillegas rick.hille...@oracle.comwrote: Hi Chux, The default (and maximum) size for a CLOB is 2,147,483,647 characters. See http://db.apache.org/derby/docs/10.10/ref/rrefclob.html. If you have found a place in the documentation where some other limit is stated, could you let us know where that is so that we can correct that misleading statement? Thanks, -Rick On 4/10/14 9:09 PM, Chux wrote: Awesome insights guys, thanks for all your help. BTW, I could not access the online documentation for some reason. Although I read somewhere that 64k is the maximum size you can allocate a clob on embedded mode. Is this correct? I would like to know what the limit is. variable clob(64 K) Thanks, Chux On Thu, Apr 10, 2014 at 5:32 AM, Dag H. Wanvik dag.wan...@oracle.commailto: dag.wan...@oracle.com wrote: On 09. april 2014 17:51, Rick Hillegas wrote: On 4/8/14 2:00 AM, Chux wrote: Hey Dag, Thanks for your insight. I'm using this as an embedded DB in a Java FX desktop application. This is a dumb question but would you recommend shutting down the database ever after a transaction? Like after you create a record then you shut it down after commit. Depends on the application. If the database holds some kind of infrequently referenced metadata, so that say, it is only queried or updated once a day, then you could consider an on demand model where the database is booted for each query/update, then the query results are returned, then the database is shut down so that it doesn't consume any resources. The big extra cost of an on demand database would be this: query/update time would be substantially longer since every query/update involves booting the database, compiling the query/update, and gracefully closing the database; that cost is on top of the steady-state cost of running a pre-compiled query/update. In such a scenario one might want to shut down the engine, too, not just the database. Note that shutting down the database will resources, but if the engine is still running, one can further release resources by shutting that down as well. Cf. http://db.apache.org/derby/docs/10.10/devguide/tdevdvlp20349.html (engine shutdown) and http://db.apache.org/derby/docs/10.10/devguide/tdevdvlp40464.html (shutdown database) Thanks, Dag Hope this helps, -Rick Best, Chux On Tue, Apr 8, 2014 at 12:32 AM, Dag H. Wanvik dag.wan...@oracle.com mailto:dag.wan...@oracle.com mailto:dag.wan...@oracle.com mailto:dag.wan...@oracle.com wrote: On 06. april 2014 21:02, George Toma wrote: Hi Chux, In my opinion the example from app. referred at commit the transaction OR close the connection ( a connection could be transacted too ), and not to shutdown the db. If the business rule specifies that the db. needs to be shutdown when the app. is shutdown, then so be it. Normally the db is not shutdown, not even when the app is down. This is true for a client/server application. For use with embedded Derby, one would normally close down the database (and the database engine) before exiting the application. If one neglects to do so, one would see longer start-up times as Dyre indicated. Thanks, Dag Cheers, George On Sunday, April 6, 2014 7:14 PM, Chux chu...@gmail.com mailto:chu...@gmail.com mailto:chu...@gmail.com mailto:chu...@gmail.com wrote: Hello guys, I read in a sample app that you've got to shutdown a database. I was just confused if you need to shut it down on every connection transaction or just shut it down on application close, in my case a desktop applicaiton. Best, Chux
Fwd: When to shut down a database
We have a simple query using LIKE but is very slow. We need to tell the optimizer which index to use but the override DERBY-PROPERTIES statement returns an error. here is the SQL. SELECT esq.attachment.guid FROM esq.attachment --DERBY-PROPERTIES index = ATTACHMENT_LINKNAMES_INDEX where ( esq.attachment.file_name LIKE ‘%drisco%' OR esq.attachment.file_index_name LIKE ‘%drisco%' ) AND ( esq.attachment.link_guid IS NOT NULL AND esq.attachment.link_table='mat' AND esq.attachment.link_guid IN ( SELECT esq.matter.guid FROM esq.matter WHERE esq.matter.guid=esq.attachment.link_guid AND ( ( esq.matter.grp_guid=0 OR esq.matter.grp_guid IS NULL ) OR ( esq.matter.grp_guid0 AND esq.matter.grp_guid IN ( 2,1,6,4,3 ) ) ) ) ) ; Sincerely, Frank Rivera LogicBit Software - Research Triangle Park, North Carolina
Re: When to shut down a database
BTW the error is this Invalid syntax for optimizer overrides. The syntax should be -- DERBY-PROPERTIES propertyName = value [, propertyName = value]* Sincerely, Frank Rivera LogicBit Software - Research Triangle Park, North Carolina On Apr 11, 2014, at 12:15 PM, Frank Rivera frank.riv...@houdiniesq.com wrote: We have a simple query using LIKE but is very slow. We need to tell the optimizer which index to use but the override DERBY-PROPERTIES statement returns an error. here is the SQL. SELECT esq.attachment.guid FROM esq.attachment --DERBY-PROPERTIES index = ATTACHMENT_LINKNAMES_INDEX where ( esq.attachment.file_name LIKE ‘%drisco%' OR esq.attachment.file_index_name LIKE ‘%drisco%' ) AND ( esq.attachment.link_guid IS NOT NULL AND esq.attachment.link_table='mat' AND esq.attachment.link_guid IN ( SELECT esq.matter.guid FROM esq.matter WHERE esq.matter.guid=esq.attachment.link_guid AND ( ( esq.matter.grp_guid=0 OR esq.matter.grp_guid IS NULL ) OR ( esq.matter.grp_guid0 AND esq.matter.grp_guid IN ( 2,1,6,4,3 ) ) ) ) ) ; Sincerely, Frank Rivera LogicBit Software - Research Triangle Park, North Carolina
Re: When to shut down a database
Awesome insights guys, thanks for all your help. BTW, I could not access the online documentation for some reason. Although I read somewhere that 64k is the maximum size you can allocate a clob on embedded mode. Is this correct? I would like to know what the limit is. variable clob(64 K) Thanks, Chux On Thu, Apr 10, 2014 at 5:32 AM, Dag H. Wanvik dag.wan...@oracle.comwrote: On 09. april 2014 17:51, Rick Hillegas wrote: On 4/8/14 2:00 AM, Chux wrote: Hey Dag, Thanks for your insight. I'm using this as an embedded DB in a Java FX desktop application. This is a dumb question but would you recommend shutting down the database ever after a transaction? Like after you create a record then you shut it down after commit. Depends on the application. If the database holds some kind of infrequently referenced metadata, so that say, it is only queried or updated once a day, then you could consider an on demand model where the database is booted for each query/update, then the query results are returned, then the database is shut down so that it doesn't consume any resources. The big extra cost of an on demand database would be this: query/update time would be substantially longer since every query/update involves booting the database, compiling the query/update, and gracefully closing the database; that cost is on top of the steady-state cost of running a pre-compiled query/update. In such a scenario one might want to shut down the engine, too, not just the database. Note that shutting down the database will resources, but if the engine is still running, one can further release resources by shutting that down as well. Cf. http://db.apache.org/derby/docs/10.10/devguide/tdevdvlp20349.html(engine shutdown) and http://db.apache.org/derby/docs/10.10/devguide/tdevdvlp40464.html(shutdown database) Thanks, Dag Hope this helps, -Rick Best, Chux On Tue, Apr 8, 2014 at 12:32 AM, Dag H. Wanvik dag.wan...@oracle.commailto: dag.wan...@oracle.com wrote: On 06. april 2014 21:02, George Toma wrote: Hi Chux, In my opinion the example from app. referred at commit the transaction OR close the connection ( a connection could be transacted too ), and not to shutdown the db. If the business rule specifies that the db. needs to be shutdown when the app. is shutdown, then so be it. Normally the db is not shutdown, not even when the app is down. This is true for a client/server application. For use with embedded Derby, one would normally close down the database (and the database engine) before exiting the application. If one neglects to do so, one would see longer start-up times as Dyre indicated. Thanks, Dag Cheers, George On Sunday, April 6, 2014 7:14 PM, Chux chu...@gmail.com mailto:chu...@gmail.com wrote: Hello guys, I read in a sample app that you've got to shutdown a database. I was just confused if you need to shut it down on every connection transaction or just shut it down on application close, in my case a desktop applicaiton. Best, Chux
Re: When to shut down a database
Where did you read that? If you declare your column to be CLOB(64K) than you have restricted its size. CLOB data type CLOB data type A CLOB (character large object) value can be up to 2,147,483,647 characters long. A CLOB is used to store unicode character-based data, such as large documents in any character set. View on db.apache.org Preview by Yahoo String limitations The following table lists limitations on string values in Derby. Table 1. String limitations Value Maximum Limit Length of CHAR 254 characters Length of VARCHAR 32,672 characters View on db.apache.org Preview by Yahoo and String limitations Peter On Friday, 11 April 2014, 6:09, Chux chu...@gmail.com wrote: Awesome insights guys, thanks for all your help. BTW, I could not access the online documentation for some reason. Although I read somewhere that 64k is the maximum size you can allocate a clob on embedded mode. Is this correct? I would like to know what the limit is. variable clob(64 K) Thanks, Chux On Thu, Apr 10, 2014 at 5:32 AM, Dag H. Wanvik dag.wan...@oracle.com wrote: On 09. april 2014 17:51, Rick Hillegas wrote: On 4/8/14 2:00 AM, Chux wrote: Hey Dag, Thanks for your insight. I'm using this as an embedded DB in a Java FX desktop application. This is a dumb question but would you recommend shutting down the database ever after a transaction? Like after you create a record then you shut it down after commit. Depends on the application. If the database holds some kind of infrequently referenced metadata, so that say, it is only queried or updated once a day, then you could consider an on demand model where the database is booted for each query/update, then the query results are returned, then the database is shut down so that it doesn't consume any resources. The big extra cost of an on demand database would be this: query/update time would be substantially longer since every query/update involves booting the database, compiling the query/update, and gracefully closing the database; that cost is on top of the steady-state cost of running a pre-compiled query/update. In such a scenario one might want to shut down the engine, too, not just the database. Note that shutting down the database will resources, but if the engine is still running, one can further release resources by shutting that down as well. Cf. http://db.apache.org/derby/docs/10.10/devguide/tdevdvlp20349.html (engine shutdown) and http://db.apache.org/derby/docs/10.10/devguide/tdevdvlp40464.html (shutdown database) Thanks, Dag Hope this helps, -Rick Best, Chux On Tue, Apr 8, 2014 at 12:32 AM, Dag H. Wanvik dag.wan...@oracle.com mailto:dag.wan...@oracle.com wrote: On 06. april 2014 21:02, George Toma wrote: Hi Chux, In my opinion the example from app. referred at commit the transaction OR close the connection ( a connection could be transacted too ), and not to shutdown the db. If the business rule specifies that the db. needs to be shutdown when the app. is shutdown, then so be it. Normally the db is not shutdown, not even when the app is down. This is true for a client/server application. For use with embedded Derby, one would normally close down the database (and the database engine) before exiting the application. If one neglects to do so, one would see longer start-up times as Dyre indicated. Thanks, Dag Cheers, George On Sunday, April 6, 2014 7:14 PM, Chux chu...@gmail.com mailto:chu...@gmail.com wrote: Hello guys, I read in a sample app that you've got to shutdown a database. I was just confused if you need to shut it down on every connection transaction or just shut it down on application close, in my case a desktop applicaiton. Best, Chux
Re: When to shut down a database
On 4/8/14 2:00 AM, Chux wrote: Hey Dag, Thanks for your insight. I'm using this as an embedded DB in a Java FX desktop application. This is a dumb question but would you recommend shutting down the database ever after a transaction? Like after you create a record then you shut it down after commit. Depends on the application. If the database holds some kind of infrequently referenced metadata, so that say, it is only queried or updated once a day, then you could consider an on demand model where the database is booted for each query/update, then the query results are returned, then the database is shut down so that it doesn't consume any resources. The big extra cost of an on demand database would be this: query/update time would be substantially longer since every query/update involves booting the database, compiling the query/update, and gracefully closing the database; that cost is on top of the steady-state cost of running a pre-compiled query/update. Hope this helps, -Rick Best, Chux On Tue, Apr 8, 2014 at 12:32 AM, Dag H. Wanvik dag.wan...@oracle.com mailto:dag.wan...@oracle.com wrote: On 06. april 2014 21:02, George Toma wrote: Hi Chux, In my opinion the example from app. referred at commit the transaction OR close the connection ( a connection could be transacted too ), and not to shutdown the db. If the business rule specifies that the db. needs to be shutdown when the app. is shutdown, then so be it. Normally the db is not shutdown, not even when the app is down. This is true for a client/server application. For use with embedded Derby, one would normally close down the database (and the database engine) before exiting the application. If one neglects to do so, one would see longer start-up times as Dyre indicated. Thanks, Dag Cheers, George On Sunday, April 6, 2014 7:14 PM, Chux chu...@gmail.com mailto:chu...@gmail.com wrote: Hello guys, I read in a sample app that you've got to shutdown a database. I was just confused if you need to shut it down on every connection transaction or just shut it down on application close, in my case a desktop applicaiton. Best, Chux
Re: When to shut down a database
On 09. april 2014 17:51, Rick Hillegas wrote: On 4/8/14 2:00 AM, Chux wrote: Hey Dag, Thanks for your insight. I'm using this as an embedded DB in a Java FX desktop application. This is a dumb question but would you recommend shutting down the database ever after a transaction? Like after you create a record then you shut it down after commit. Depends on the application. If the database holds some kind of infrequently referenced metadata, so that say, it is only queried or updated once a day, then you could consider an on demand model where the database is booted for each query/update, then the query results are returned, then the database is shut down so that it doesn't consume any resources. The big extra cost of an on demand database would be this: query/update time would be substantially longer since every query/update involves booting the database, compiling the query/update, and gracefully closing the database; that cost is on top of the steady-state cost of running a pre-compiled query/update. In such a scenario one might want to shut down the engine, too, not just the database. Note that shutting down the database will resources, but if the engine is still running, one can further release resources by shutting that down as well. Cf. http://db.apache.org/derby/docs/10.10/devguide/tdevdvlp20349.html (engine shutdown) and http://db.apache.org/derby/docs/10.10/devguide/tdevdvlp40464.html (shutdown database) Thanks, Dag Hope this helps, -Rick Best, Chux On Tue, Apr 8, 2014 at 12:32 AM, Dag H. Wanvik dag.wan...@oracle.com mailto:dag.wan...@oracle.com wrote: On 06. april 2014 21:02, George Toma wrote: Hi Chux, In my opinion the example from app. referred at commit the transaction OR close the connection ( a connection could be transacted too ), and not to shutdown the db. If the business rule specifies that the db. needs to be shutdown when the app. is shutdown, then so be it. Normally the db is not shutdown, not even when the app is down. This is true for a client/server application. For use with embedded Derby, one would normally close down the database (and the database engine) before exiting the application. If one neglects to do so, one would see longer start-up times as Dyre indicated. Thanks, Dag Cheers, George On Sunday, April 6, 2014 7:14 PM, Chux chu...@gmail.com mailto:chu...@gmail.com wrote: Hello guys, I read in a sample app that you've got to shutdown a database. I was just confused if you need to shut it down on every connection transaction or just shut it down on application close, in my case a desktop applicaiton. Best, Chux
Re: When to shut down a database
Hey Dag, Thanks for your insight. I'm using this as an embedded DB in a Java FX desktop application. This is a dumb question but would you recommend shutting down the database ever after a transaction? Like after you create a record then you shut it down after commit. Best, Chux On Tue, Apr 8, 2014 at 12:32 AM, Dag H. Wanvik dag.wan...@oracle.comwrote: On 06. april 2014 21:02, George Toma wrote: Hi Chux, In my opinion the example from app. referred at commit the transaction OR close the connection ( a connection could be transacted too ), and not to shutdown the db. If the business rule specifies that the db. needs to be shutdown when the app. is shutdown, then so be it. Normally the db is not shutdown, not even when the app is down. This is true for a client/server application. For use with embedded Derby, one would normally close down the database (and the database engine) before exiting the application. If one neglects to do so, one would see longer start-up times as Dyre indicated. Thanks, Dag Cheers, George On Sunday, April 6, 2014 7:14 PM, Chux chu...@gmail.comchu...@gmail.comwrote: Hello guys, I read in a sample app that you've got to shutdown a database. I was just confused if you need to shut it down on every connection transaction or just shut it down on application close, in my case a desktop applicaiton. Best, Chux
Re: When to shut down a database
There are a number of issues that for the most part are distinct 1) Commit a transaction when you're sure you don't have to abort (or when you need to release resources). 2) Close the connection when you no longer need it/it is about to go out of scope. 3) Shutdown a database to a) speed up the next boot (as the db does not have to recover from its log) b) to release the database (files) for another jvm 4) Shutdown the Derby engine to release resources to the rest of your application when you don't need access to any databases. On 04/07/2014 03:49 AM, Chux wrote: Hello George, Thanks for your comment. This situation however is, I'm using java DB as an embedded mode. Best, Chux On Mon, Apr 7, 2014 at 3:02 AM, George Toma toma.georg...@yahoo.com mailto:toma.georg...@yahoo.com wrote: Hi Chux, In my opinion the example from app. referred at commit the transaction OR close the connection ( a connection could be transacted too ), and not to shutdown the db. If the business rule specifies that the db. needs to be shutdown when the app. is shutdown, then so be it. Normally the db is not shutdown, not even when the app is down. Cheers, George On Sunday, April 6, 2014 7:14 PM, Chux chu...@gmail.com mailto:chu...@gmail.com wrote: Hello guys, I read in a sample app that you've got to shutdown a database. I was just confused if you need to shut it down on every connection transaction or just shut it down on application close, in my case a desktop applicaiton. Best, Chux -- Regards, Dyre
Re: When to shut down a database
On 06. april 2014 21:02, George Toma wrote: Hi Chux, In my opinion the example from app. referred at commit the transaction OR close the connection ( a connection could be transacted too ), and not to shutdown the db. If the business rule specifies that the db. needs to be shutdown when the app. is shutdown, then so be it. Normally the db is not shutdown, not even when the app is down. This is true for a client/server application. For use with embedded Derby, one would normally close down the database (and the database engine) before exiting the application. If one neglects to do so, one would see longer start-up times as Dyre indicated. Thanks, Dag Cheers, George On Sunday, April 6, 2014 7:14 PM, Chux chu...@gmail.com wrote: Hello guys, I read in a sample app that you've got to shutdown a database. I was just confused if you need to shut it down on every connection transaction or just shut it down on application close, in my case a desktop applicaiton. Best, Chux
When to shut down a database
Hello guys, I read in a sample app that you've got to shutdown a database. I was just confused if you need to shut it down on every connection transaction or just shut it down on application close, in my case a desktop applicaiton. Best, Chux
Re: When to shut down a database
Hi Chux, In my opinion the example from app. referred at commit the transaction OR close the connection ( a connection could be transacted too ), and not to shutdown the db. If the business rule specifies that the db. needs to be shutdown when the app. is shutdown, then so be it. Normally the db is not shutdown, not even when the app is down. Cheers, George On Sunday, April 6, 2014 7:14 PM, Chux chu...@gmail.com wrote: Hello guys, I read in a sample app that you've got to shutdown a database. I was just confused if you need to shut it down on every connection transaction or just shut it down on application close, in my case a desktop applicaiton. Best, Chux
