Table of Contents

• Overview
• License
• Description
• Setup
  • Requirements
  • Installing the db2_config module
• Usage
• Reference
• Limitations

Overview

This module allows you to configure and manage DB2 database. It is part of our family of Puppet modules to install, manage and secure DB2 databases with Puppet. Besides the db2_config module, this family also contains:

• db2_install For installing an DB2 databases.
• secured To secure your databases according to the CIS benchmarks.
• db2_profile The db2_profile module allows an easy path from first simple installation to a fully customized Enterprise setup.

All of these modules support DB2 versions 9, 10 and 11 all fixpack levels

License

This is a commercially licensed module. But you can use the module on VirtualBox based development systems for FREE. You can request a FREE trial license here

Check the License for details.

Description

This module allows you to manage everything there is to manage in an DB2 database. For example you can manage: users, tablespaces and schemas and much more. The module contains the following types:

• db2_database
• db2_exec
• db2_group
• db2_instance
• db2_object_grant
• db2_role
• db2_schema
• db2_schema_definition
• db2_tablespace
• db2_user
• db2_validation

When you need to configure a DB2 HADR setup, and you are using the Pacemaker cluster manager, the db2_config modules supports you as well. db2_config knows the following Pacemaker resources:

• pcmkr_cluster
• pcmkr_instance
• pcmkr_database
• pcmkr_vip
• pcmkr_qdevice

Check here to see the full documentation for this module.

Setup

Requirements

The db2_config module requires:

• Puppet module enterprisemodules-easy_type installed.
• Puppet version 4.0 or higher. Can be Puppet Enterprise or Puppet Open Source
• DB2 version 9 higher
• A valid DB2 license
• A valid Enterprise Modules license for usage.
• Runs on most Linux systems.

Installing the db2_config module

To install these modules, you can use a Puppetfile

mod 'enterprisemodules/db2_config'               ,'0.1.x'

Then use ther10K to install the software.

You can also install the software using the puppet module command:

puppet module install enterprisemodules-db2_config

Usage

Building a total DB2 setup requires quite some work. To help you, we have built some examples. The repo contains a demonstration of a simple database installation.

Reference

Here you can find some more information regarding this puppet module:

• The db2_config documentation

Limitations

This module runs on most Linux versions. It requires a puppet version higher than 4. Other configurations are not (yet) supported.

Reference

Table of Contents

Classes

• db2_config: db2_config See the file "LICENSE" for the full license governing this code.
• db2_config::clear_caches: This class contains the puppet code that implements the default facter cache invalidation rules.
• db2_config::fact_caching: This class ensure's that the db2_config facts that can be cached and are cached with the correct settings.
• db2_config::packages: Ensure the required packages (ruby gem) is installed.

Resource types

• db2_database: This resource allows you to manage an DB2 Database. Here is an example on how you can do this: db2_database {'db2inst1/MYDB': ensu
• db2_exec: This type allows you run a specific SQL statement or an sql file on a specified instance and database. db2_exec { "db2inst1/MYDB/drop ta
• db2_group: This resource allows you to manage a DB2 group. Here is an example on how to use this: db2_group { 'db2inst1/MYDB/PUBLIC': ensure
• db2_instance: This resource allows you to manage an DB2 instance. Here is an example on how to use this: db2_instance { 'db2inst2': ensure
• db2_object_grant: This puppet type allows you to manage grants/permissions on database objects. Here is an example: db2_object_grant { 'db2inst1/MYDB/USER
• db2_role: This resource allows you to manage a DB2 role. Here is an example on how to use this: db2_role { 'db2inst1/MYDB/MY_ROLE': ensure
• db2_schema: This resource allows you to manage a DB2 schema. Here is an example on how to use this: db2_schema { 'db2inst1/MYDB/MY_SCHEMA': en
• db2_schema_definition: This resource allows you to manage a schema definition. This includes all tables, indexes and other DDL that is needed for your application.
• db2_tablespace: This resource allows you to manage a DB2 tablespace. Here is an example on how to use this: db2_tablespace { 'db2inst1/MYDB/USERSPACE1':
• db2_user: This resource allows you to manage a DB2 user. Here is an example on how to use this: db2_user { 'db2inst1/MYDB/MYUSER': ensure
• db2_validation: This type allows you to validate the contents of your DB2 database. Here is an example on how you can use this: db2_validation { "db2ins
• pcmkr_cluster: This resource allows you to manage an Pacemaker cluster. Here is an example on how to use this: pcmkr_cluster { 'cluster': ensure
• pcmkr_database: This resource allows you to manage an Pacemaker cluster. Here is an example on how to use this: pcmkr_database { 'db2inst1/MYDB':
• pcmkr_instance: This resource allows you to manage an Pacemaker instance. Here is an example on how to use this: pcmkr_instance { 'db2inst1': ensu
• pcmkr_qdevice: This resource allows you to manage an Pacemaker qdevice. Here is an example on how to use this: pcmkr_qdevice { 'db1157c': ensure => '
• pcmkr_vip: This resource allows you to manage an Pacemaker VIP. Here is an example on how to use this: pcmkr_vip { 'db2inst1/MYDB/primary/10.20.21.4':

Functions

• db2_config::databases_in: See the file "LICENSE" for the full license governing this code. Function to detect the databases in a set of resources
• db2_config::fact_value_for: Create a random id based on letters.
• db2_config::is_standby: See the file "LICENSE" for the full license governing this code. Function to fetch the fact mq_managers and based on that information decid
• db2_config::resources_for: See the file "LICENSE" for the full license governing this code.
• db2_config::tablespaces_on_database: Create a random id based on letters.

Classes

db2_config

db2_config

See the file "LICENSE" for the full license governing this code.

db2_config::clear_caches

db2_config::clear_caches

Whenever a certain set of Puppet classes or defined types is applied, it will clear the facter caches for those facts that are implemented.

See the file "LICENSE" for the full license governing this code.

db2_config::fact_caching

db2_config::fact_caching

You can change all TTL settings for the facts if an other setting is more fitting for your setup.

See the file "LICENSE" for the full license governing this code.

Parameters

The following parameters are available in the db2_config::fact_caching class:

• enabled
• db2_databases_ttl
• db2_instances_ttl
• db2_schemas_ttl
• db2_tablespaces_ttl
• db2_version_ttl

enabled

Data type: Boolean

When set to true, this enables facter caching for the db2_config module facts. Beware that you need to have Facter version 4.1.0 or higher to enabled this. When you set this value to true, automatically TTL settings for most of the db2_config facts are set to a default value. For most installations, the default settings are fine. When you set this value to true, the facter setting force-dot-resolution is enabled. You have to ensure that all of your facts keep on working with this setting.

db2_databases_ttl

Data type: Optional[String[1]]

The Time to Live for the db2_databases fact.

db2_instances_ttl

Data type: Optional[String[1]]

The Time to Live for the db2_instances fact.

db2_schemas_ttl

Data type: Optional[String[1]]

The Time to Live for the db2_schemas fact.

db2_tablespaces_ttl

Data type: Optional[String[1]]

The Time to Live for the db2_tablespaces fact.

db2_version_ttl

Data type: Optional[String[1]]

The Time to Live for the db2_version fact.

db2_config::packages

db2_config::packages

See the file "LICENSE" for the full license governing this code.

Resource types

db2_database

This resource allows you to manage an DB2 Database. Here is an example on how you can do this:

db2_database {'db2inst1/MYDB':
  ensure => 'present',
  rec_his_retentn      => 90,
  section_actuals      => 'NONE',
  self_tuning_mem      => 'ON',
  seqdetect            => 'YES',
}

This statement will ensure a database MYDB on instance db2inst1.

Properties

The following properties are available in the db2_database type.

ensure

Valid values: present, absent

The basic property that the resource should be in.

Default value: present

Parameters

The following parameters are available in the db2_database type.

• provider

provider

The specific backend to use for this db2_database resource. You will seldom need to specify this --- Puppet will usually discover the appropriate provider for your platform.

db2_exec

This type allows you run a specific SQL statement or an sql file on a specified instance and database.

db2_exec { "db2inst1/MYDB/drop table application_users":
  username => 'app_user',
  password => 'password,'
}

This statement will execute the sql statement drop table application_users on the databse 'MYDB' on instance 'db2inst1'.

You can use the unless parameter to only execute the statement in certain states. If the query specified in the unless parameter returns one or more records, the main statement is skipped.

db2_exec{ "create synonym ${user}.${synonym} for USER.${synonym}":
  unless  => "select * from all_synonyms where owner=\'${user}\' and synonym_name=\'${synonym}\'",
}

You can also execute a script.

db2_exec{"db2inst1/MYDB/@/tmp/do_some_stuff.sql":
  username  => 'app_user',
  password  => 'password,'
  logoutput => on_failure,  # can be true, false or on_failure
}

This statement will run the sqlscript /tmp/do_some_stuff.sql on the database named MYDB on instance db2inst1. Use the unless parameter to just execute the script in certain situations.

When you don't specify the username and the password, the type will connect as sysdba.

Parameters

The following parameters are available in the db2_exec type.

• provider

provider

The specific backend to use for this db2_exec resource. You will seldom need to specify this --- Puppet will usually discover the appropriate provider for your platform.

db2_group

This resource allows you to manage a DB2 group. Here is an example on how to use this:

db2_group { 'db2inst1/MYDB/PUBLIC':
  ensure   => 'present',
  grants   => ['BINDADD', 'CONNECT', 'CREATETAB', 'IMPLICIT_SCHEMA'],
  provider => 'db2',
  roles    => ['SYSTS_USR'],
}

Puppet will NOT create the OS groups, but it will manage grants and roles.

Properties

The following properties are available in the db2_group type.

ensure

Valid values: present, absent

The basic property that the resource should be in.

Default value: present

Parameters

The following parameters are available in the db2_group type.

• provider

provider

The specific backend to use for this db2_group resource. You will seldom need to specify this --- Puppet will usually discover the appropriate provider for your platform.

db2_instance

This resource allows you to manage an DB2 instance. Here is an example on how to use this:

db2_instance { 'db2inst2':
  ensure              => 'present',
  agent_stack_sz      => 1024,
  agentpri            => 'SYSTEM',
  alternate_auth_enc  => 'AES_ONLY',
  aslheapsz           => 15,
  audit_buf_sz        => 1000,
  authentication      => 'SERVER_ENCRYPT',
  catalog_noauth      => 'NO',
}

Manage the OS user or not

If the manage_user parameter is set to true, Puppet will create the OS users required for an instance. If you need more control over the creation process, you can set the parameter to false and manage the user(s) yourself. Look at the parameters:

• instance_password
• instance_uid
• instance_group
• instance_home
• fence_user
• fence_password
• fence_uid
• fence_group
• fence_home

for more details.

When removing an instance, the parameter will determine if Puppet removes the OS users.

Properties

The following properties are available in the db2_instance type.

ensure

Valid values: present, absent

The basic property that the resource should be in.

Default value: present

Parameters

The following parameters are available in the db2_instance type.

• provider

provider

The specific backend to use for this db2_instance resource. You will seldom need to specify this --- Puppet will usually discover the appropriate provider for your platform.

db2_object_grant

This puppet type allows you to manage grants/permissions on database objects. Here is an example:

db2_object_grant { 'db2inst1/MYDB/USER/MYUSER->MYSCHEMA.MYTABLE':
  permissions            => ['CONTROL'],
  provider               => 'db2',
  with_grant_permissions => ['ALTER', 'DELETE', 'INDEX', 'INSERT', 'REFERENCE', 'SELECT', 'UPDATE'],
}

This code tell's Puppet that on database MYDB on instance dbs2inst1, the user MYUSER has CONTROL permission on the table MYTABLE in schema MYSCHEMA. It can also grant the rights ALTER', 'DELETE', 'INDEX', 'INSERT', 'REFERENCE', 'SELECT', 'UPDATE to other users.

Parameters

The following parameters are available in the db2_object_grant type.

• provider

provider

The specific backend to use for this db2_object_grant resource. You will seldom need to specify this --- Puppet will usually discover the appropriate provider for your platform.

db2_role

This resource allows you to manage a DB2 role. Here is an example on how to use this:

db2_role { 'db2inst1/MYDB/MY_ROLE':
  ensure   => 'present',
  grants   => ['DBADM', 'SECADM', 'ACCESSCTRL'],
  provider => 'db2',
  roles    => ['SYSTS_ADM', 'SYSTS_MGR', 'SYSDEBUG', 'SYSDEBUGPRIVATE'],
}

Properties

The following properties are available in the db2_role type.

ensure

Valid values: present, absent

The basic property that the resource should be in.

Default value: present

Parameters

The following parameters are available in the db2_role type.

• provider

provider

The specific backend to use for this db2_role resource. You will seldom need to specify this --- Puppet will usually discover the appropriate provider for your platform.

db2_schema

This resource allows you to manage a DB2 schema. Here is an example on how to use this:

db2_schema { 'db2inst1/MYDB/MY_SCHEMA':
  ensure      => 'present',
}

This will create the schema MY_SCHEMA in the database MYDB on instance db2inst1.

Properties

The following properties are available in the db2_schema type.

ensure

Valid values: present, absent

The basic property that the resource should be in.

Default value: present

Parameters

The following parameters are available in the db2_schema type.

• provider

provider

The specific backend to use for this db2_schema resource. You will seldom need to specify this --- Puppet will usually discover the appropriate provider for your platform.

db2_schema_definition

This resource allows you to manage a schema definition. This includes all tables, indexes and other DDL that is needed for your application.

db2_schema_definition{'db2inst1/MYDB':
    ensure      => '1.0.0',
    schema_name => 'MYAPP,
    password    => 'verysecret',
    source_path => '/opt/stage/myapp/sql',
}

In this example we tell Puppet, we need schema version 1.0.0 for MYAPP in the database. In layman's terms now the following things will happen:

• The Puppet type will log in to the database using username myapp and the very secret password and it will look at the version of the schema already available.
• If the current version is lower than the specified version, Puppet will execute the upgrade SQL scripts in the source path until the correct version is reached.
• If the current version is higher than the requested version, Puppet will execute the downgrade scripts until the requested version is reached.

Schema version?

What is this concept of a schema version? To administer the current version, Puppet uses a table called SCHEMA_VERSION. Here is the definition of this table:

CREATE TABLE schema_version
(  id                NUMERIC
  ,application       VARCHAR(255)
  ,version           VARCHAR(255)
  ,description       VARCHAR(255)
  ,installation_time TIMESTAMP(6)
);

Puppet uses this table to store the history of all schema versions applied. If the table doesn't exists, Puppet will create it for you. Using this information, Puppet can tell what the state of the current schema in the database is. Puppet creates this table for every single schema/user you manage. This means, you can have multiple schemas with different versions in your database.

What about these upgrade and downgrade scripts.

In order for Puppet to do it's magic, the upgrade and downgrade scripts, need to have specific names. Here is a listing of some upgrade scripts:

upgrades/0000_myapp_0.0.1_initial-schema.sql
upgrades/0001_myapp_0.0.2_add-user-table.sql
upgrades/0002_myapp_0.1.0_initial-release.sql

As you can see, all the file names have the following structure:

• a four-digit sequence number
• application name
• version number
• description

All fields are separated by an underscore. The upgrade scripts contain all SQL statements needed to upgrade the database schema to the desired state. In general upgrade scripts contain statements to create tables and indexes and add or remove columns, but you can also insert data into the lookup-tables or create database packages.

The downgrades directory contains scripts with the same names. The downgrade scripts contain the SQL statements needed to put the database in the state it was before. So if you add a column in an upgrade script, you'll have to remove this column in the downgrade script.

Parameters

The following parameters are available in the db2_schema_definition type.

• provider

provider

The specific backend to use for this db2_schema_definition resource. You will seldom need to specify this --- Puppet will usually discover the appropriate provider for your platform.

db2_tablespace

This resource allows you to manage a DB2 tablespace. Here is an example on how to use this:

db2_tablespace { 'db2inst1/MYDB/USERSPACE1':
  ensure                => 'present',
}

TODO: Extend the example

Properties

The following properties are available in the db2_tablespace type.

ensure

Valid values: present, absent

The basic property that the resource should be in.

Default value: present

Parameters

The following parameters are available in the db2_tablespace type.

• provider

provider

The specific backend to use for this db2_tablespace resource. You will seldom need to specify this --- Puppet will usually discover the appropriate provider for your platform.

db2_user

This resource allows you to manage a DB2 user. Here is an example on how to use this:

db2_user { 'db2inst1/MYDB/MYUSER':
  ensure   => 'present',
  grants   => ['DBADM', 'SECADM', 'DATAACCESS', 'ACCESSCTRL'],
  roles    => ['SYSTS_ADM', 'SYSTS_MGR', 'SYSDEBUG', 'SYSDEBUGPRIVATE'],
}

This will ensure a DB2 user named MYUSER in database MYDB on instance db2inst1. This type is mainly available to manage grants and roles. It will not create the os user's needed fro this.

Properties

The following properties are available in the db2_user type.

ensure

Valid values: present, absent

The basic property that the resource should be in.

Default value: present

Parameters

The following parameters are available in the db2_user type.

• provider

provider

The specific backend to use for this db2_user resource. You will seldom need to specify this --- Puppet will usually discover the appropriate provider for your platform.

db2_validation

This type allows you to validate the contents of your DB2 database. Here is an example on how you can use this:

db2_validation { "db2inst1/MYDB/No user data in system tablespaces":
  check                   => "select tabschema,tabname,tbspace from syscat.tables where tabschema not in ('ADMINISTRATOR','SYSIBM','SYSTOOLS') and tbspace in ('SYSCATSPACE','SYSTOOLSPACE','SYSTOOLSTMPSPACE','TEMPSPACE')",
  expected_number_of_rows => 0,
  fail_message            => 'User table %<tabschema>s.%<tabname>s not allowed system tablespace %<tbspace>s',
  report_as               => 'command',
  fail_command            => "/bin/send_to_montoring --node ${::fqdn} %<output>s",
}

The Puppet statement executes a query on the database and report a warning when more the 0 records are found. It uses the returned records of the query to generate a good reading error or warning message.

Parameters

The following parameters are available in the db2_validation type.

• provider

provider

The specific backend to use for this db2_validation resource. You will seldom need to specify this --- Puppet will usually discover the appropriate provider for your platform.

pcmkr_cluster

This resource allows you to manage an Pacemaker cluster. Here is an example on how to use this:

pcmkr_cluster { 'cluster':
  ensure     => 'present',
  interfaces => ['eth0', 'eth0'],
  nodes      => ['db1157a', 'db1157b'],
}

All of these properties are read-only. If you need to add nodes, or change interfaces, the cluster must be recreated. At this point in time Puppet does not take care of this.

Properties

The following properties are available in the pcmkr_cluster type.

ensure

Valid values: present, absent

The basic property that the resource should be in.

Default value: present

Parameters

The following parameters are available in the pcmkr_cluster type.

• provider

provider

The specific backend to use for this pcmkr_cluster resource. You will seldom need to specify this --- Puppet will usually discover the appropriate provider for your platform.

pcmkr_database

This resource allows you to manage an Pacemaker cluster. Here is an example on how to use this:

pcmkr_database { 'db2inst1/MYDB':
  ensure     => 'present',
}

All of these properties are read-only. If you need to add nodes, or change interfaces, the cluster must be recreated. At this point in time Puppet does not take care of this.

Properties

The following properties are available in the pcmkr_database type.

ensure

Valid values: present, absent

The basic property that the resource should be in.

Default value: present

Parameters

The following parameters are available in the pcmkr_database type.

• provider

provider

The specific backend to use for this pcmkr_database resource. You will seldom need to specify this --- Puppet will usually discover the appropriate provider for your platform.

pcmkr_instance

This resource allows you to manage an Pacemaker instance. Here is an example on how to use this:

pcmkr_instance { 'db2inst1':
  ensure     => 'present',
  nodes      => ['db1157a', 'db1157b'],
}

All of these properties are read-only. If you need to add nodes, or change interfaces, the resource must be recreated. At this point in time Puppet does not take care of this.

Properties

The following properties are available in the pcmkr_instance type.

ensure

Valid values: present, absent

The basic property that the resource should be in.

Default value: present

Parameters

The following parameters are available in the pcmkr_instance type.

• provider

provider

The specific backend to use for this pcmkr_instance resource. You will seldom need to specify this --- Puppet will usually discover the appropriate provider for your platform.

pcmkr_qdevice

This resource allows you to manage an Pacemaker qdevice. Here is an example on how to use this:

pcmkr_qdevice { 'db1157c': ensure => 'present', }

Properties

The following properties are available in the pcmkr_qdevice type.

ensure

Valid values: present, absent

The basic property that the resource should be in.

Default value: present

Parameters

The following parameters are available in the pcmkr_qdevice type.

• provider

provider

The specific backend to use for this pcmkr_qdevice resource. You will seldom need to specify this --- Puppet will usually discover the appropriate provider for your platform.

pcmkr_vip

This resource allows you to manage an Pacemaker VIP. Here is an example on how to use this:

pcmkr_vip { 'db2inst1/MYDB/primary/10.20.21.4': ensure => 'present', }

Properties

The following properties are available in the pcmkr_vip type.

ensure

Valid values: present, absent

The basic property that the resource should be in.

Default value: present

Parameters

The following parameters are available in the pcmkr_vip type.

• provider

provider

The specific backend to use for this pcmkr_vip resource. You will seldom need to specify this --- Puppet will usually discover the appropriate provider for your platform.

Functions

db2_config::databases_in

Type: Ruby 4.x API

See the file "LICENSE" for the full license governing this code.

Function to detect the databases in a set of resources

db2_config::databases_in(Hash $resources)

See the file "LICENSE" for the full license governing this code.

Function to detect the databases in a set of resources

Returns: Array

resources

Data type: Hash

db2_config::fact_value_for

Type: Ruby 4.x API

See the file "LICENSE" for the full license governing this code.

db2_config::fact_value_for(String[1] $instance, String[1] $database, String[1] $fact)

See the file "LICENSE" for the full license governing this code.

Returns: Any

instance

Data type: String[1]

database

Data type: String[1]

fact

Data type: String[1]

db2_config::is_standby

Type: Ruby 4.x API

See the file "LICENSE" for the full license governing this code.

Function to fetch the fact mq_managers and based on that information decide if the specfied queue manager is running in standby mode or not.

db2_config::is_standby(String $databade)

See the file "LICENSE" for the full license governing this code.

Function to fetch the fact mq_managers and based on that information decide if the specfied queue manager is running in standby mode or not.

Returns: Any

databade

Data type: String

db2_config::resources_for

Type: Ruby 4.x API

See the file "LICENSE" for the full license governing this code.

db2_config::resources_for(Hash[String,Hash] $resources, String $database)

See the file "LICENSE" for the full license governing this code.

Returns: Any

resources

Data type: Hash[String,Hash]

database

Data type: String

db2_config::tablespaces_on_database

Type: Ruby 4.x API

See the file "LICENSE" for the full license governing this code.

db2_config::tablespaces_on_database(String[1] $database)

See the file "LICENSE" for the full license governing this code.

Returns: Any

database

Data type: String[1]

History

Version 1.0.1

• [core] Fix sql error on Ruby 3 systems

Version 1.0.0

• [core] Add support for ruby 3.2 and Puppet 8
• [core] Beter way of execiting the sql statement
• [core] Fix loading order issues on Puppet Server
• [db2_validation] Add support for InControl
• [facts] Add extra properties to db2_databases and db2_instances

Version 0.5.0

• [core] Add support for EL 9
• [core] Change standby message to make it more clear
• [db2_validation] Skip validation when database not primary
• [db2_tablespaces] Enable fact

Version 0.4.0

• [core] Invalidate db cache after db modify
• [core] Add support for skipping operations on standby db’s
• [pcmkr_*] Make location parameter more robust

Version 0.3.0

• [docs] Add reference
• [pcmkr_*] Added pacemaker custom types
• [core] More flexibility in data types
• [db2_validation] Add support for fail_command

Version 0.2.0

• [core] Add support for handling hadr databases

Version 0.1.3

• [packages] Initial version ensure required gems are installed

Version 0.1.2

• [core] Fix sql execution in some cases

Version 0.1.1

• [core] Fix fact resolution
• [db2_database] Fix case sensitivity issue

29-06-2022 version 0.1.0

Initial release