My IBM DB2 Cheat Sheet

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 …

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 …

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 …

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 …