How To: Choose Between CONVERT (ddl) and DDL_DIRECT (ddl_direct) Packaging Methods

The Difference Between CONVERT (ddl) and DDL_DIRECT (ddl_direct) Packaging

The CONVERT and DDL_DIRECT package methods work very differently. The CONVERT packaging method does not run the actual sql script during deploy. Instead, it creates an XML change set by comparing a “before” snapshot to the “after” snapshot and generating the change set based upon the difference between the two snapshots. In contrast, the DDL_DIRECT packaging method runs the actual script using the native database command line tools.

For additional information regarding the CONVERT and DDL_DIRECT (or DIRECT) packaging methods, see the following pages:

• https://datical-cs.atlassian.net/wiki/spaces/DDOC/pages/896570004#WritingSQLScriptsforLiquibaseEnterprise-CONVERT/ddlfolderversusDIRECT/sql_directfolder

• https://datical-cs.atlassian.net/wiki/spaces/DDKB/pages/644448284

• Processing Changes

• Packaging Overview

• Complex and Interdependent Changes

Since the CONVERT and DDL_DIRECT packaging methods operate differently, one method may be preferred over the other depending upon the situation.

Note that DDL_DIRECT is only available with versions 7.15 and later.

CONVERT

Pros

• The CONVERT packaging method provides the full forecasting and rules validation capabilities available with Datical.

• The change sets are very granular allowing failed deploys to be re-executed from the point of failure.

Cons

• The CONVERT packaging method takes longer because of the extra work required to generate the snapshots and perform the comparison.

• The order of statements within a SQL script may not be preserved when they are converted to XML change sets.

• SQL statements that will not generate a change set by diffing the before and after states of the database will not be deployed.

DDL_DIRECT

Pros

• The DDL_DIRECT packaging method is faster because it does not have to generate and compare database snapshots.

• The order of SQL statements within a script is maintained.

• The SQL script timeout setting can be used to terminate run-away or hung scripts.

Cons

• Scripts packaged using DDL_DIRECT are not forecasted, unless using SQL Parser for Oracle.

  • Note that not all objects can be forecasted with SQL Parser

  • See the topic “Limitations of SQL Parser” on this page: https://datical-cs.atlassian.net/wiki/spaces/DDOC/pages/896570188

Recommendations

• Package DDL using CONVERT to get the most comprehensive forecasting and rules validation.

• Package DDL using DDL_DIRECT when packaging is taking too long due to snapshot generation.

  • Performance may be significantly improved for managed schemas that contain thousands of objects.

• Use SQL Parser for Oracle when packaging using DDL_DIRECT to provide forecasting for supported object types. Remember that not all objects can be forecasted by SQL Parser.

• Package complex scripts where the order of SQL execution must be maintained using DDL_DIRECT.

• Make scripts packaged using DDL_DIRECT as granular as possible. Consider using naming convention or commit order to maintain the sequence of SQL statements instead of packaging them in a single script.

  • More granular scripts will allow you to recover from a failed deploy much easier. Scripts are considered a single change set. If the script fails mid-way, Datical will try to re-execute the entire script. If non-rerunnable changes were made, a second execution of the script will fail. Manual database cleanup will be required in order to complete the deploy.