Populate h2 in memory database from a db2 database Spring Boot

Question

I'm currently building a Spring Boot Service with a h2 in-memory database. This Database acts as an cache for a part of the data on a central db2 database with a different database schema.

Now when the Spring boot service starts it needs to populate the h2 database with the latest data from the central database.

How can I do this in the best way performance wise? I'm currently looking to create an different data-source in my service to first get the data and then save the data to the h2.

This doesn't feel like a good solution and it would take quite a long time to populate the database.

halfer · Accepted Answer · 2024-04-20 21:33:26Z

2

If you want to use H2 instead of your DB2 database ... and if you don't want to re-create the database each time you run your app ...

... then consider using an H2 file, instead of in-memory:

http://www.h2database.com/html/features.html

jdbc:h2:[file:][<path>]<databaseName>
jdbc:h2:~/test
jdbc:h2:file:/data/sample
jdbc:h2:file:C:/data/sample (Windows only)

You can "initialize" the file whenever you want (perhaps just once).

Performance should be excellent.

Per your update:

I still need to access the central db to get the latest data in the fastest way possible. The central db needs to stay for other services also accessing this

The "fastest" way to get the very latest data ... is to query the central database directly. Period - no ifs/ands/buts.

But, if for whatever reason, you want to "cache" a subset of "recent" data ... then H2 is an excellent choice.

And if you don't want to "rebuild" each time you start your H2 database, then save H2 to a file instead of making it in-memory.

The performance difference between H2:mem and H2:file is small, compared to the network overhead of querying your central database.

edited Apr 20, 2024 at 21:33

halfer

20.2k20 gold badges110 silver badges207 bronze badges

answered Apr 6, 2020 at 15:09

paulsm4

123k23 gold badges175 silver badges245 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kevin Bos Over a year ago

I still need to access the central db to get the latest data in the fastest way possible. The central db needs to stay for other services also accessing this.

Ajay Kumar Over a year ago

You can go ahead with H2 in-mem or file either one as suggested by FoggyDay. But the question is why you need H2 if you have handle of your DB2 database already. Why you want to create an extra route? I would refrain myself using H2 (mem/file) in such condition. I would prefer H2 "ONLY" for my development purpose. IMHO Data-recovery/security/integrity && PERFORMANCE they all come hand-in-hand with additional effort and price. You might want to revisit your requirement/design again. Or if its not your design, question it.!

Collectives™ on Stack Overflow

Populate h2 in memory database from a db2 database Spring Boot

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related