I am currently using DB2 to do unit tests, but it is sometime quite slow. I would need a good in-memory database that would include all the feature of DB2. Does this type of in-memory database exist, or do they only allow standard SQL feature?
Thank you.
EDIT The DB2 Database is on a remote server, so I would need a solution to replicate the schema of that database on a local in-memory database to speed up the tests.
Why not to use tmpfs (Unix), or any conventional ramdrive solution for Windows? Or, you can get a fast SSD.
Having no idea what features DB2 has, but sqlite can create in-memory database. You might want to take a look at it.
I would need a good in-memory database that would include all the feature of DB2.
Derby (ex Cloudscape) is DB2's language compatible. And it has an in memory mode.
Maybe also have a look at H2 (with the DB2 compatibility mode). But even if H2 would be faster, I would consider Derby in your case.
If you make your bufferpool large enough, your DB2 database will be in-memory, too.
As mentioned by Pascal, Derby is almost identical syntactically to DB2. Having tried it for the same thing a while back we had an issue with Derby not supporting sequences, but the connection protocols are identical. Eg you can use the DB2 JDBC driver to connect to a Derby database.
If your application abstracts your database connection with say Hibernate, or NHibernate (if .Net) using H2 or HSQLDB could also work for unit tests assuming you aren't relying on stored procedures and the like.
Another tool that helps out with schema migration that targets multiple DBs is http://liquibase.org . For a test build you make it build your in memory DB and for a deployment you make it build your DB2 database or generate the migration script. It will build the database with the correct schema and offers conditional migrations (eg grant is not available in HSQL so you run the change set for just DB2).
I think the easiest option would be --- DB2.
Download the freebie express edition and install it on your PC. You slow speed is almost certainly down to network bottlenecks and limitations using the DB2 client, installing locally would eliminate these.
The next best thing would be JavaDB (used to be known as Derby!). Which is similar but not quite identical to DB2.
Using any other database will dump you in a quagmire of unsupported features, incompatable SQL, different names for the same function, different functions with the same function name etc.etc.
Two possible in-memory databases are: - timesTen from Oracle. - SolidDB from IBM. This one could send the transaction back to DB2, but the queries will have a really high performance
For more information about soliddb http://www-01.ibm.com/software/data/soliddb/