Use parameter markers for catalogue queries

[MichaelKern-IVV]

Environment

Liquibase Version: 4.13

Liquibase Integration & Version: CLI,

Database Vendor & Version: DB2z 12

Operating System Type & Version: Windows 10 Pro

Description

To retrieve catalog information, queries are used (especially for DB2z) that include the schema and table names as literals.

For example `

SELECT 'NULL' AS TABLE_CAT,
SYSTAB.TBCREATOR AS TABLE_SCHEM,
SYSTAB.TBNAME AS TABLE_NAME,
COLUSE.COLNAME AS COLUMN_NAME,
COLUSE.COLSEQ AS KEY_SEQ,
SYSTAB.CONSTNAME AS PK_NAME
FROM SYSIBM.SYSTABCONST SYSTAB
JOIN SYSIBM.SYSKEYCOLUSE COLUSE
ON SYSTAB.TBCREATOR = COLUSE.TBCREATOR
HERE SYSTAB.TYPE = 'P'
AND SYSTAB.TBNAME='DMTID3O'
AND SYSTAB.TBCREATOR='ENTW'
AND SYSTAB.TBNAME=COLUSE.TBNAME
AND SYSTAB.CONSTNAME=COLUSE.CONSTNAME
ORDER BY COLUSE.COLNAME`

This floods the statement cache with similar queries and pushes out statements from other applications.
Also, statements need to be parsed and query plans need to be created unnecessarily.
Overall, the performance of Liquibase is reduced, but also that of the system as a whole.

It would be better to use queries with parameter markers.

Steps To Reproduce

Use a ChangeLog with many preconditions, e.g. primaryKeyExists.

Actual Behavior

Many, very similar queries are executed.

Expected/Desired Behavior

One query with parameter markers is executed several times.

[nvoxland]

Historically, Liquibase avoided prepared statements so that we could output "what we run" sql scripts that can be ran directly.

However, like you say, there are tons of "query the database state" calls which are never output to users and would best be done as prepared statements.

If someone was up for converting some of the calls to use prepared statements, that would be great. A good chunk are likely calling the ResultSetCache.executeAndExtract() function which should make them easy to find and replace with a new version of that method that takes the parameters and uses a prepared statement