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
This is a set of DB2 snippets I often used at work. I'm glad to share them with you. Generate GRANT statements for a new DB2 user with name MYUSER user on all tables of a schema named MYSCHEMA: The following stored procedure will automatically generate and execute the grant statements Generate 'reorg index' and … Continue reading My IBM DB2 Cheat Sheet
For those people who are not familiar with Sonar, it's an opensource source code analysis tool which you can use to improve the quality of your coding. By default, Sonar comes with a simple Java Derby embedded database which is great for testing it out, but with at least 8 different projects with 25.000 - … Continue reading Migrating Sonar from Derby to MySQL
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
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
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
Yesterday I came across the following error message while testing a JPQL named query An alias is required for the schema name MyEntity in the expression... A quick Yahoo! search only returned me something like this which isn't very useful. Anyway, the solution was extremely simple: my name query was incorrect. Instead of … Continue reading An alias is required for the schema name MyEntity in the expression.