Copy to clipboard

checkBufferPool

checkBufferPool is a custom policy check that checks that the Buffer Pool specified in the tablespace matches the default Buffer Pool for the database.

Learn how to create and customize the checkBufferPool Liquibase Custom Policy Check using a Python script.

This example utilizes DB2 on zOS. You can use this check as it is or customize it further to fit your needs in your SQL database.

For a conceptual overview of this feature, see Liquibase Pro Custom Policy Checks.

Before you begin

Scope	Database
`changelog`	DB2 on zOS

Liquibase Prerequisites

Liquibase 4.29.0+
Python 3.10.14+
Configure a valid Liquibase Pro license key
create a Checks settings file
Ensure the Liquibase Checks extension is installed. In Liquibase 4.31.0+, it is already installed in the /liquibase/internal/lib directory, so no action is needed.
If the checks JAR is not installed, download liquibase-checks-<version>.jar and put it in the liquibase/lib directory.
- Maven users only:
  Add this dependency to your pom.xml
  
  file: <dependency> <groupId>org.liquibase.ext</groupId> <artifactId>liquibase-checks</artifactId> <version>2.0.0</version> </dependency>
Java Development Kit 17+ (available for Open JDK and Oracle JDK)
Linux, macOS, or Windows operating system

checkBufferPool

These steps describe how to create the Custom Policy Check. It does not exist by default in Liquibase Pro.

Add this code to your Checks Settings file:

checkBufferPool Python Script

    # # #
    # # # This script checks that the Buffer Pool specified in the tablespace
    # # # matches the
    default Buffer Pool
    for the Database.
    # # #
    # # #
    # # # This script throws the following errors:
        # # # 1) Multiple CREATE TABLESPACE statements found in changeset.Only one CREATE TABLESPACE allowed per changeset.
    # # # 2) Default Buffer Pool Not Found
    for Database {
        database_name
    }.
    # # # 3) Multiple BUFFER POOL statements found in CREATE TABLESPACE statement.Only one Buffer Pool can be specified.
    # # # 4) Buffer Pool Not Found in CREATE TABLESPACE script.
    # # # 5) CREATE TABLESPACE Buffer Pool(buffer_name) must match the
    default Buffer Pool(default_buffer_pool) for the database(database_name).
    # # #
    # # # Sample Tablespace:
        # # #
    # # # CREATE TABLESPACE SBA01003
    # # # IN DBA0001
    # # # USING STOGROUP SYSPOOL1
    # # # PRIQTY 720 SECQTY 720
    # # # ERASE NO
    # # # FREEPAGE 5 PCTFREE 15 FOR UPDATE 0
    # # # GBPCACHE CHANGED
    # # # TRACKMOD YES
    # # # MAXPARTITIONS 20
    # # # LOGGED
    # # # DSSIZE 8 G
    # # # SEGSIZE 32
    # # # BUFFERPOOL BP0
    # # # LOCKSIZE ANY
    # # # LOCKMAX SYSTEM....
    # # #
    # # # Query to find
    default Buffer Pool:
        # # #
    # # # SELECT BPOOL FROM SYSIBM.SYSDATABASE WHERE NAME = 'DBA0001';

    # # #
    # # # Helpers come from Liquibase
    # # #
    import liquibase_utilities
    import re
    import sys

    # # #
    # # # main
    # # #

    # # #
    # # # Retrieve log handler
    # # # Ex.liquibase_logger.info(message)
    # # #
    liquibase_logger = liquibase_utilities.get_logger()

    # # #
    # # # Retrieve status handler
    # # #
    liquibase_status = liquibase_utilities.get_status()

    # # #
    # # # Define regex patterns
    for a Tablespace 's DatabaseName and BufferPool
    # # #
    regex_pattern_database = f "(?is)CREATE\s+TABLESPACE\s+\w+\s+IN\s+(\w+)"
    regex_pattern_bufferpool = f "(?is)BUFFERPOOL\s+(\S+)"

    # # #
    # # # Retrieve all changes in changeset
    # # #
    changes = liquibase_utilities.get_changeset().getChanges()

    # # #
    # # # Loop through all changes
    # # #
    for change in changes:
        # # #
    # # # LoadData change types are not currently supported
    # # #
    if "loaddatachange" in change.getClass().getSimpleName().lower():
        continue
    # # #
    # # # Split sql into a list of strings to remove whitespace
    # # #
    raw_sql = liquibase_utilities.generate_sql(change)

    # # #
    # # # Look
    for database regex in SQL
    # # #
    database_list = re.findall(regex_pattern_database, raw_sql)

    if len(database_list) > 1:
        liquibase_status.fired = True
    status_message = f "Multiple CREATE TABLESPACE statements found in changeset. Only one CREATE TABLESPACE allowed per changeset."
    liquibase_status.message = status_message
    sys.exit(1)
    break
    else:
        database_name = ''.join(database_list)

    # # #
    # # # End check
    if script does not contain regex pattern
    # # #
    if database_name is None or database_name == '':
        break
    else:
        # # # print(f "Database Name: {database_name}")

    # # #
    # # # Execute query to get the
    default buffer pool
    for the database
    # # #
    sql_query = f "SELECT BPOOL FROM SYSIBM.SYSDATABASE WHERE NAME = '{database_name}'"
    default_buffer_pool_list = liquibase_utilities.query_for_list(sql_query, None, ";")

    if len(default_buffer_pool_list) == 0:
        # # # print(f "Default Buffer Pool Not Found for Database {database_name}")

    liquibase_status.fired = True
    status_message = f "Default Buffer Pool Not Found for Database {database_name}."
    liquibase_status.message = status_message
    sys.exit(1)
    break
    else:

        default_buffer_pool = default_buffer_pool_list[0]["BPOOL"].strip()
    # # # print(f "Default Buffer Pool: {default_buffer_pool}")

    # # #
    # # # Look
    for bufferpool regex in SQL
    # # #
    buffer_pool_list = re.findall(regex_pattern_bufferpool, raw_sql)

    if len(buffer_pool_list) > 1:
        liquibase_status.fired = True
    status_message = f "Multiple BUFFER POOL statements found in CREATE TABLESPACE statement. Only one Buffer Pool can be specified."
    liquibase_status.message = status_message
    sys.exit(1)
    break
    else:

        buffer_pool = ''.join(buffer_pool_list)

    if buffer_pool is None or buffer_pool == '':
        # # # print(f "Buffer Pool Not Found in script {buffer_pool}")

    liquibase_status.fired = True
    status_message = f "Buffer Pool Not Found in CREATE TABLESPACE script."
    liquibase_status.message = status_message
    sys.exit(1)
    break

    else:
        # # # print(f "Buffer Pool in script: {buffer_pool}")

    # # #
    # # # Check that the buffer pool values match
    # # #

    if buffer_pool != default_buffer_pool:

        liquibase_status.fired = True
    status_message = str(liquibase_utilities.get_script_message()).replace("__BUFFER_POOL__", f "{buffer_pool}")
    status_message = status_message.replace("__DEFAULT_BUFFER_POOL__", f "{default_buffer_pool}")
    status_message = status_message.replace("__DATABASE_NAME__", f "{database_name}")
    liquibase_status.message = status_message
    sys.exit(1)
    break

    # # #
    # # # Default
    return code
    # # #
    False

Initiate the customization process

In the CLI, run this command:

liquibase checks customize --check-name=CustomCheckTemplate

The CLI prompts you to finish configuring your file. A message displays:

This check cannot be customized directly because one or more fields does not have a default value.

Liquibase will then create a copy of CustomCheckTemplate and initiate the customization workflow.

Give your check a short name so you can easily identify what Python script it is associated with

You may use up to 64 alpha-numeric characters only.

In this example we will name the check: checkBufferPool

Set the Severity to return a code of 0-4 when triggered.

These severity codes allow you to determine if the job moves forward or stops when this check triggers. Learn more here: Use Policy Checks in Automation: Severity and Exit Code options: 'INFO'=0, 'MINOR'=1, 'MAJOR'=2, 'CRITICAL'=3, 'BLOCKER'=4

Set SCRIPT_DESCRIPTION

In this example, we will set the description to:

This script checks that the Buffer Pool specified in the tablespace matches the default Buffer Pool for the database.

Set the SCRIPT_SCOPE

In this example, we will set the scope to:

changelog: for example, if your check looks for syntax patterns or attributes in your Liquibase Changelog (the changes you author in your repository). With this value, the check runs once per changeset.

Set the SCRIPT_MESSAGE

This message will display when the check is triggered. In this example we will use:

CREATE TABLESPACE Buffer Pool (__BUFFER_POOL__) must match the default Buffer Pool (__DEFAULT_BUFFER_POOL__) for the database (__DATABASE_NAME__).

Set the SCRIPT_PATH

This is the relative path where your script is stored in relation to the changelog specified in --changelog-file, whether it is stored locally or in a repository.

In this example, we will set the path to: scripts/collection-camel-case.py

This check does not require a SCRIPT_ARGUMENT, so leave this blank.

Set the REQUIRES_SNAPSHOT

If your script scope is changelog, set whether the check requires a database snapshot. Specify true if your check needs to inspect database objects.

If your script scope is database, Liquibase always takes a snapshot, so this prompt does not appear.

Note: The larger your database, the more performance impact a snapshot causes. If you cannot run a snapshot due to memory limitations, see Memory Limits of Inspecting Large Schemas.