DB2 syntax (limit and dirty read) when running H2 (in DB2 mode)

I have a few methods that I'm currently trying to unit tests. These methods is running fine (in production) against a DB2 v8 using Spring JdbcTemplates. Each SQL allows "dirty reads" by appending "WITH UR" to the statement. Also, some of them uses "limit" by adding e.g. "FETCH FIRST 1 ROWS ONLY". This works fine against a real DB2, but I want to unit test these methods against a in-memory database - this is where H2 enters. Everything works fine if I remove "WITH UR" and the "FETCH FIRST..", but I don't want to change the methods, but simply change the backend database. As far as I can see this isn't directly possible using H2, as the syntax is different (although I use MODE=DB2). Now, what to do? Should/could I use another in-memory database? I do not want to change the methods nor add "test" features/hacks, so that's a no-go. Ideas and likewise is much appreciated! EDIT I'm not sure if its me or what, but I get the following error. Notice, I'm using Spring 3.1 and H2 1.3.166 and the url for the database is "jdbc:h2:~/testdb;MODE=DB2". I have no idea why it fails, because the sql works when running in the H2 Console but not from my unit test (guess its not the H2 that is broken): ``` Tests run: 3, Failures: 0, Errors: 1, Skipped: 0, Time elapsed: 0.109 sec <<< FAILURE! getAdvisor(impl.AdvisorServiceDaoImplTest) Time elapsed: 0.078 sec <<< ERROR! org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT * FROM ADVISOR FETCH FIRST 1 ROWS ONLY ]; nested exception is org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "SELECT * FROM ADVISOR FETCH[*] FIRST 1 ROWS ONLY "; SQL statement: SELECT * FROM ADVISOR FETCH FIRST 1 ROWS ONLY [42000-166] at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:233) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:603) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:637) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:662) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:702) at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.queryForObject(NamedParameterJdbcTemplate.java:178) at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.queryForObject(NamedParameterJdbcTemplate.java:185) at impl.AdvisorServiceDaoImpl.getAdvisor(AdvisorServiceDaoImpl.java:150) at impl.AdvisorServiceDaoImplTest.getAdvisor(AdvisorServiceDaoImplTest.java:69) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:45) at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15) at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:42) at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20) at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:28) at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:74) at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:83) at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:72) at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:231) at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:47) at org.junit.runners.ParentRunner$3.run(ParentRunner.java:231) at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:60) at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:229) at org.junit.runners.ParentRunner.access$000(ParentRunner.java:50) at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:222) at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61) at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:71) at org.junit.runners.ParentRunner.run(ParentRunner.java:300) at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:174) at org.apache.maven.surefire.junit4.JUnit4TestSet.execute(JUnit4TestSet.java:62) at org.apache.maven.surefire.suite.AbstractDirectoryTestSuite.executeTestSet(AbstractDirectoryTestSuite.java:140) at org.apache.maven.surefire.suite.AbstractDirectoryTestSuite.execute(AbstractDirectoryTestSuite.java:127) at org.apache.maven.surefire.Surefire.run(Surefire.java:177) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at org.apache.maven.surefire.booter.SurefireBooter.runSuitesInProcess(SurefireBooter.java:338) at org.apache.maven.surefire.booter.SurefireBooter.main(SurefireBooter.java:997) Caused by: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "SELECT * FROM ADVISOR FETCH[*] FIRST 1 ROWS ONLY "; SQL statement: SELECT * FROM ADVISOR FETCH FIRST 1 ROWS ONLY [42000-166] at org.h2.message.DbException.getJdbcSQLException(DbException.java:329) at org.h2.message.DbException.get(DbException.java:169) at org.h2.message.DbException.get(DbException.java:146) at org.h2.message.DbException.getSyntaxError(DbException.java:181) at org.h2.command.Parser.getSyntaxError(Parser.java:484) at org.h2.command.Parser.prepareCommand(Parser.java:233) at org.h2.engine.Session.prepareLocal(Session.java:415) at org.h2.engine.Session.prepareCommand(Session.java:364) at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1111) at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:71) at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:266) at org.springframework.jdbc.core.PreparedStatementCreatorFactory$PreparedStatementCreatorImpl.createPreparedStatement(PreparedStatementCreatorFactory.java:245) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:581) ... 40 more ``` EDIT #2 In case anyone have the same problem here is what I found out. I was using the following: ``` <jdbc:embedded-database id="dataSource" type="H2"> <jdbc:script location="classpath:sql/create_schemas.sql"/> <jdbc:script location="classpath:sql/create_tables.sql"/> <jdbc:script location="classpath:sql/create_test_data.sql"/> </jdbc:embedded-database> ``` This means that Spring will re-instantiate the data source with a generic bean, thus my settings was lost. The solution (in my case) was to simply load the database programmatically with the following: ``` EmbeddedDatabaseBuilder builder = new EmbeddedDatabaseBuilder(); EmbeddedDatabase db = builder.setName("salgdb").setType(EmbeddedDatabaseType.H2) .addScript("sql/create_schemas.sql") .addScript("sql/create_tables.sql") .addScript("sql/create_test_data.sql") .build(); ```