Slow performance with NamedParameterJdbcTemplate

Today I tried inserting 256 rows in a single, empty PostgreSQL table which has only one index on it using Spring's NamedParameterJdbcTemplate . To my surprise the single transaction took over 3 minutes to complete, over 500ms per INSERT statement. To make things worse, the same inserts during integration testing on an H2 database completed … Continue reading Slow performance with NamedParameterJdbcTemplate

Advertisements

Using alternative credentials for Liquibase in Spring Boot

One of the projects I'm working uses Spring Boot to handle all database changes for each micro-service. One of the obvious requirements to make this work is a database user with DBA rights, otherwise it can not create, alter or drop tables. You could configure the default datasource to use such user, but this would … Continue reading Using alternative credentials for Liquibase in Spring Boot

Delete from multiple tables in DB2 in a single query

Consider following, simple, relational database model: We have a list with documents and a table linking these document to something unimportant. The relation between DOCUMENT and LINK is one-to-one or one-to-none. Now imaging that we'd have to delete all documents where the type column has the value 'TEMPORARY'. If we'd execute this query, we will … Continue reading Delete from multiple tables in DB2 in a single query

The fullselect specified for the materialized query table “SCHEMA.TABLE” is not valid

These are some common issues you can get while creating an MQT (Materialized Query Table) in DB2, especially when using a 'REFRESH IMMEDIATE' type of MQT. Error: The fullselect specified for the materialized query table "PW999.PW999" is not valid. Reason code = "6".. SQLCODE=-20058, SQLSTATE=428EC Cause: The query that causes this error has a distinct … Continue reading The fullselect specified for the materialized query table “SCHEMA.TABLE” is not valid

Update sequence with max value from table

Last week we were facing a problem while working on a database (DB2) migration project. The migration is 90% one-to-one which meant we could easily re-use the existing primary keys. This did however pose a problem because all our sequence would return values that already exist in the database. We were thus looking for a … Continue reading Update sequence with max value from table