Creating a Db2 Warehouse database deployment on the cluster
You can create a database deployment on your cluster from the IBM Cloud Pak for Data web client.
Before you begin
- Ensure that you completed the steps in Installing Db2 Warehouse.
- If you plan to deploy on a dedicated node, label the node. You will enter the label as part of the deployment process. For more details, see Setting up dedicated nodes for your Db2 Warehouse deployment.
- You must have the Create service instances permission to complete this task.
About this task
Db2 Warehouse on Cloud Pak for Data supports symmetric multiprocessing (SMP) and massively parallel processing (MPP) architectures. You can use any of these configurations:
- Single physical node with one logical partition (the default). Nodes: 1; Value for Multiple logical nodes (MLN) on Advanced configuration page: 1. This configuration is also known as SMP.
- Single physical node with multiple logical partitions. Nodes: 1; MLN value: > 1. This configuration is also known as logical MPP.
- Multiple physical nodes with multiple logical partitions. Nodes: > 1; MLN value: > 1. This configuration is also known as physical MPP.
Procedure
Creating a deployment by using the Cloud Pak for Data web client:
- From the navigation, select .
- Click Create a database.
- Select the database type and version. Click Next.
- In the Configure area, specify the number of nodes, memory per node, and
CPU per node.Note: If you use Portworx storage, you must select 4K sector size.
The console checks your OpenShift® cluster for adequate memory and CPU resources to meet your specifications. An error message displays if inadequate resources are found. If this occurs, you must either reduce the amounts that you specified or add more resources to the cluster.
- If
you are using a dedicated node, click Deploy database on dedicated nodes and
enter the node label in the Value for node label box.Note: The following warning banner might show up:
A valid dedicated node with label "{label}" was not found. No available nodes are labeled and tainted.
On occasion, this warning banner might be translucent and the message hard to read. If so, refresh the page.
For more information, see Setting up dedicated nodes for your Db2 Warehouse deployment.
- On the Advanced configuration page, specify the following options:
- Select the Oracle compatibility option to specify whether Db2 Warehouse is deployed in Oracle compatibility mode (the DB2_COMPATIBILITY_VECTOR registry variable is set to ORA).
- What type of workload to use:
- Operational Analytics: Sets the registry variable
DB2_WORKLOAD
toPUREDATA_OLAP
to use Netezza® Performance Server for Operational Analytics. - Analytics: Sets the
DB2_WORKLOAD
value to Analytics to set the default table organization toCOLUMN
for column-organized tables.
- Operational Analytics: Sets the registry variable
- Select the Allow SSL port only option to disable the TCP/IP port, which will only allow SSL connections to be made to the database.
- Select the Disable Db2 encryption option to disable Db2 native encryption.
- If vault is enabled, the Credentials page will automatically appear.
Clusters without vault enabled default to generating a Kubernetes secret for each instance created. If you would like
to still deploy an instance that uses the default method, select Generate a Kubernetes
secret. If you would like to use existing credentials in a secret you have added to an external
HashiCorp or CyberArk vault, or existing credentials in a secret you have created in the internal
vault, select Use secrets from a vault.
Follow one of the below options that match your environment:
- You have no existing secrets, but you have an existing vault.
- An Add secret button will show with disabled dropdowns below. If you select Add secret, it will open a new tab and take you to the Configuration page where you should see a Vaults and secrets tab. Select Secrets on that tab, and then Add secret. Ask your administrator to share a secret with you.
- You have no existing secrets or existing vaults, but you do have permission to add vaults.
- In the first option, it does not matter if you have this permission because a vault already exists. In this option, you must either ask your administrator to share a secret with you and then click Reload, or you can select the Add vault button. This will open a new tab to the page where you can add an external HashiCorp or CyberArk vault, and then either add a secret on that tab or click Reload on the first tab, which will reload to the first situation.
- You have no existing secrets or existing vaults, and you do not have permission to add vaults.
- In this case, you can only ask your administrator to share a secret with you. Once a secret is shared, click Reload to enable dropdowns.
The dropdowns that might be visible are Password and/or SSL certificate, depending on what is enabled in your environment. All dropdowns available must select a secret in order to continue.
When creating or adding a new secret, ensure it follows these guidelines, which are also present in the informational tips next to each dropdown:- The Password dropdown is filtered to display only credential secrets. The selected credential must include a password key.
- The SSL certificate dropdown is filtered to display only generic secrets. The selected generic secret must include three name-value pairs with the keys: ca.crt, tls.crt, and tls.key.
On the Finalize page, ensure all the information under the Credentials section is correct.
- You can choose to keep your system data, user data, backup data, transaction logs, archive logs, and temporary table space data together in a single storage
location, or put them in separate locations. System data contains the information that is used by
Db2
Warehouse to manage and configure the database. User data is the main database data.
Backup data is the storage for saving Db2
Warehouse backup images. Transaction logs storage
is the location to save main database transaction logs. Archive logs
storage is the location to save database archive logs. Temporary table space storage is the
location where main database temporary table spaces are created.
- If you choose Separate locations for all data, you must specify a storage volume type, a name, and a size for all storage locations.
- If you choose Single locations for all data as the Storage structure, you must select File storage on the Storage page because the Db2 Warehouse instance pod and the built-in etcd pod mounts the same volume for metadata. Block storage cannot be mounted by multiple pods because block storage is in read write once (RWO) mode.
- If you use Portworx storage, you must specify 4K block size.
- Specify the storage to use for the database.
For the available options, see Configuring database storage for Db2 Warehouse.
- Click Next.
- Optional: Specify a new display name for the database. The new name must be unique amongst all existing database deployments.
- Ensure that the summary is correct and click Create.
You might have to wait 2 to 40 minutes, based on the number of worker nodes and amount of memory that were allocated to the deployment.
The database is ready when it shows up as Available on the Databases tab.
Creating an SMP deployment by using the Cloud Pak for Data command line interface:
- Create a custom resource db2wh.yaml file to define the
database for your environment. Ensure that you specify the following parameters in your custom resource:
- Required format
- The name of the db2ucluster must have the following format:
db2wh-<numeric_number>
. The <numeric_number> needs to be unique and not copied from another instance. For example:name: db2wh-1654045646749323
- Required labels
-
db2u/cpdbr: db2u
cpd_db2: db2wh
- Optional labels
- The
cpd_display_name
label can be added if you want a custom display name for the database tile.cpd_display_name: <web_console_database_tile_name>
Note: Audit logging can be configured for your new database deployment. See Configuring audit logging for Db2 Warehouse for more information.Refer to the following custom resource example to help define your database:apiVersion: db2u.databases.ibm.com/v1 kind: Db2uCluster metadata: labels: db2u/cpdbr: db2u cpd_db2: db2wh cpd_display_name: <web_console_database_tile_name> name: db2wh-1654045646749323 namespace: zen spec: account: imagePullSecrets: - db2u-dockercfg-xsnk8 privileged: true advOpts: db2SecurityPlugin: cloud_gss_plugin environment: database: name: BLUDB settings: dftTableOrg: COLUMN ssl: certLabel: CN=zen-ca-cert secretName: 'db2wh-internal-tls' dbType: db2wh instance: dbmConfig: SRVCON_PW_PLUGIN: IBMIAMauthpwfile group_plugin: IBMIAMauthgroup srvcon_auth: GSS_SERVER_ENCRYPT srvcon_gssplugin_list: IBMIAMauth password: 'password' registry: DB2_4K_DEVICE_SUPPORT: "ON" DB2_FMP_RUN_AS_CONNECTED_USER: "NO" DB2_WORKLOAD: ANALYTICS DB2AUTH: OSAUTHDB,ALLOW_LOCAL_FALLBACK,PLUGIN_AUTO_RELOAD mln: total: 1 license: accept: true podConfig: db2u: resource: db2u: limits: cpu: 5900m memory: 20Gi size: 1 storage: - name: share spec: accessModes: - ReadWriteMany resources: requests: storage: 105Gi storageClassName: <yourStorageClass> type: create version: <database_version> volumeSources: - visibility: - db2u volumeSource: secret: secretName: zen-service-broker-secret
- Replace <yourStorageClass> with a valid storage class for your cluster.
- Replace <database_version> with the version of the your database instance.
- If you are following the
instructions in Setting up dedicated nodes for your Db2 Warehouse deployment, the dedicated deployment must include the
following changes in the CR:
- A section under
spec:
fortolerations
:
wheretolerations: - effect: NoSchedule key: icp4data operator: Equal value: <dedicated_specifier>
<dedicated_specifier>
is replaced with the node label entered in the Value for node label field of the web console. - A section under
spec:
foraffinity
:
whereaffinity: nodeAffinity: requiredDuringSchedulingIgnoredDuringExecution: nodeSelectorTerms: - matchExpressions: - key: icp4data operator: In values: - <dedicated_specifier>
<dedicated_specifier>
is replaced with the node label entered in the Value for node label field of the web console.
- A section under
- Set
spec.environment.database.ssl.allowSslOnly
totrue
to disable the TCP/IP port. This will only allow SSL connections to the database. - Set
spec.environment.instance.registry.DB2_4K_DEVICE_SUPPORT: "ON"
when using a storage device that uses 4 KB sector size, as in Portworx storage for example. If it's not set, Db2 Warehouse uses a default 512-byte sector size. - Set
spec.environment.instance.registry.DB2_WORKLOAD: "PUREDATA_OLAP"
andspec.environment.database.settings.dftTableOrg: "ROW"
to enable Operational Analytics workloads for Db2 Warehouse. - Set
.spec.environment.database.settings.encrypt
to"NO"
to disable Db2 native encryption in your database.
- To create the database, run the following command
:
oc create -f db2wh.yaml
- Run the following command and wait for the state to be
Ready, or you can monitor the status in the web
console:
oc get db2ucluster db2wh-<numeric_number>
Creating an MPP deployment by using the Cloud Pak for Data command line interface:
- Create a custom resource db2wh.yaml file to define the
database for your environment. Ensure that you specify the following parameters in your custom resource:
- Required format
- The name of the db2ucluster must have the following format:
db2wh-<numeric_number>
. The <numeric_number> needs to be unique and not copied from another instance. For example:name: db2wh-1654083367196453
- Required labels
-
db2u/cpdbr: db2u
cpd_db2: db2wh
- Optional labels
- The
cpd_display_name
label can be added if you want a custom display name for the database tile.cpd_display_name: <web_console_database_tile_name>
Note: Audit logging can be configured for your new database deployment. See Configuring audit logging for Db2 Warehouse for more information.Refer to the following custom resource example to help define your database:apiVersion: db2u.databases.ibm.com/v1 kind: Db2uCluster metadata: labels: db2u/cpdbr: db2u cpd_db2: db2wh cpd_display_name: <web_console_database_tile_name> name: db2wh-1654083367196453 namespace: zen spec: account: imagePullSecrets: - db2u-dockercfg-xsnk8 privileged: true advOpts: db2SecurityPlugin: cloud_gss_plugin environment: database: name: BLUDB settings: dftTableOrg: COLUMN ssl: certLabel: CN=zen-ca-cert secretName: 'db2wh-internal-tls' dbType: db2wh instance: dbmConfig: SRVCON_PW_PLUGIN: IBMIAMauthpwfile group_plugin: IBMIAMauthgroup srvcon_auth: GSS_SERVER_ENCRYPT srvcon_gssplugin_list: IBMIAMauth password: 'password' registry: DB2_FMP_RUN_AS_CONNECTED_USER: "NO" DB2_WORKLOAD: ANALYTICS DB2AUTH: OSAUTHDB,ALLOW_LOCAL_FALLBACK,PLUGIN_AUTO_RELOAD mln: total: 2 license: accept: true podConfig: db2u: resource: db2u: limits: cpu: "4" memory: 24Gi size: 2 storage: - name: meta spec: accessModes: - ReadWriteMany resources: requests: storage: 100Gi storageClassName: <yourStorageClass> type: create - name: data spec: accessModes: - ReadWriteOnce resources: requests: storage: 100Gi storageClassName: <yourStorageClass> type: template - name: backup spec: accessModes: - ReadWriteMany resources: requests: storage: 100Gi storageClassName: <yourStorageClass> type: create - name: archivelogs spec: accessModes: - ReadWriteOnce resources: requests: storage: 100Gi storageClassName: <yourStorageClass> type: template - name: activelogs spec: accessModes: - ReadWriteOnce resources: requests: storage: 100Gi storageClassName: <yourStorageClass> type: template - name: tempts spec: accessModes: - ReadWriteOnce resources: requests: storage: 100Gi storageClassName: <yourStorageClass> type: template version: <database_version> volumeSources: - visibility: - db2u volumeSource: secret: secretName: zen-service-broker-secret
- Replace <yourStorageClass> with a valid storage class for your cluster.
- Replace <database_version> with the version of the your database instance.
- If you are following the
instructions in Setting up dedicated nodes for your Db2 Warehouse deployment, the dedicated deployment must include the
following changes in the CR:
- A section under
spec:
fortolerations
:
wheretolerations: - effect: NoSchedule key: icp4data operator: Equal value: <dedicated_specifier>
<dedicated_specifier>
is replaced with the node label entered in the Value for node label field of the web console. - A section under
spec:
foraffinity
:
whereaffinity: nodeAffinity: requiredDuringSchedulingIgnoredDuringExecution: nodeSelectorTerms: - matchExpressions: - key: icp4data operator: In values: - <dedicated_specifier>
<dedicated_specifier>
is replaced with the node label entered in the Value for node label field of the web console.
- A section under
- Set
spec.environment.database.ssl.allowSslOnly
totrue
to disable the TCP/IP port. This will only allow SSL connections to the database. - Set
spec.environment.instance.registry.DB2_4K_DEVICE_SUPPORT: "ON"
when using a storage device that uses 4 KB sector size, as in Portworx storage for example. If it's not set, Db2 Warehouse uses a default 512-byte sector size. - Set
spec.environment.instance.registry.DB2_WORKLOAD: "PUREDATA_OLAP"
andspec.environment.database.settings.dftTableOrg: "ROW"
to enable Operational Analytics workloads for Db2 Warehouse. - Set
.spec.environment.database.settings.encrypt
to"NO"
to disable Db2 native encryption in your database.
- To create the database, run the following command
:
oc create -f db2wh.yaml
- Run the following command and wait for the state to be
Ready, or you can monitor the status in the web
console:
oc get db2ucluster db2wh-<numeric_number>
What to do next
As the database administrator, you can: