Creating a service instance for Db2 Warehouse with OpenShift command line

After you install Db2 Warehouse, you can optionally create more Db2 Warehouse service instances in the operands project. If you are a IBM® Software Hub user, you can use the oc create command to create service instances after you install Db2 Warehouse.

Who needs to complete this task?

Instance administrator To create a service instance by using the OpenShift oc create command, you must be an instance administrator. An instance administrator has permission to install software in any projects that are associated with an instance of IBM Software Hub.

When do you need to complete this task?
Complete this task only if you want to create a service instance by using the oc create command.
Alternative methods for creating a service instance

Information you need to complete this task

Review the following information before you create a service instance for Db2 Warehouse:

Version requirements

All of the components that are associated with an instance of IBM Software Hub must be installed or created at the same release. For example, if Db2 Warehouse is installed at Version 5.4.0, you must create the service instance at Version 5.4.0.

Environment variables

The commands in this task use environment variables so that you can run the commands exactly as written.

  • If you don't have the script that defines the environment variables, see Setting up installation environment variables.
  • To use the environment variables from the script, you must source the environment variables before you run the commands in this task. For example, run:
    source ./cpd_vars.sh

Before you begin

This task assumes that the following prerequisites are met:

Prerequisite Where to find more information
Db2 Warehouse is installed. If this task is not complete, see Installing Db2 Warehouse.
If you plan to deploy on a dedicated node, you must label the node. You will enter the label during the deployment process. If this task is not complete, see Setting up dedicated nodes for your Db2 Warehouse deployment.

Procedure

Complete the following tasks to create a service instance:

  1. Creating a service instance
  2. Validating that the service instance was created
  3. What to do next

Creating a service instance

Choose the type of database to create:
SMP (single database partition)
  1. Create a custom resource db2wh.yaml file to define the database for your environment.

    Refer the following custom resource example and specify the parameters in your custom resource:

    apiVersion: db2u.databases.ibm.com/v1
kind: Db2uInstance
metadata:
  labels:
    cpd_db2: db2wh
    db2u/cpdbr: db2u
    icpdsupport/addOnId: db2wh
    icpdsupport/app: db2wh-1654045646749323
  name: db2wh-1654045646749323
  namespace: zen
spec:
  account:
    imagePullSecrets:
    - <db2u_sa_pullsecret>
    securityConfig:
      privilegedSysctlInit: true
  advOpts:
    db2SecurityPlugin: cloud_gss_plugin
    zenControlPlaneNamespace: zen
  affinity:
    nodeAffinity: {}
  environment:
    authentication:
      ldap:
        enabled: false
    databases:
    - name: BLUDB
      settings:
        dftTableOrg: COLUMN
    dbType: db2wh
    instance:
      dbmConfig:
        SRVCON_PW_PLUGIN: IBMIAMauthpwfile
        group_plugin: IBMIAMauthgroup
        srvcon_auth: GSS_SERVER_ENCRYPT
        srvcon_gssplugin_list: IBMIAMauth
      password:
        value: 'password'
      registry:
        DB2_FMP_RUN_AS_CONNECTED_USER: "NO"
        DB2_WORKLOAD: ANALYTICS
        DB2AUTH: OSAUTHDB,ALLOW_LOCAL_FALLBACK,PLUGIN_AUTO_RELOAD
    partitionConfig:
      dataOnMln0: true
      total: 1
      volumePerPartition: true
    ssl:
      allowSslOnly: false
      certLabel: CN=zen-ca-cert
      secretName: 'db2wh-internal-tls'
  license:
    accept: true
  nodes: 1
  podTemplate:
    db2u:
      resource:
        db2u:
          limits:
            cpu: "4"
            memory: 16Gi
  storage:
  - name: meta
    spec:
      accessModes:
      - ReadWriteMany
      resources:
        requests:
          storage: 100Gi
      storageClassName: <yourStorageClass>
    type: create
  - name: backup
    spec:
      accessModes:
      - ReadWriteMany
      resources:
        requests:
          storage: 100Gi
      storageClassName: <yourStorageClass>
    type: create
  - name: archivelogs
    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: 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
  - visibility:
    - db2u
    volumeSource:
      configMap:
        name: management-ingress-ibmcloud-cluster-info
    Required format
    The name of the db2uinstance must have the following format: db2wh-<numeric_number>. The <numeric_number> must fit these criteria:
    • It must be unique and not copied from another instance.
    • It cannot start with 0.
    • The ID cannot begin with 0.

    For example, name: db2wh-1654045646749323

    Required labels
    • cpd_db2: db2wh
    • db2u/cpdbr: db2u
    • icpdsupport/addOnId: db2wh
    Optional labels
    The cpd_display_name label can be added if you want a custom display name for the database tile. The display name must be unique in order for the tile to show on the Databases page.

    For example, cpd_display_name: <unique_web_console_database_tile_name>

  2. Specify the following parameters in your custom resource.
    1. Replace <yourStorageClass> with a valid storage class for your cluster.
    2. Replace <database_version> with the version of your database instance.
    3. Replace <db2u_sa_pullsecret> with the imagePullSecrets associated with your db2u service account.
      Tip: You can run the following command to find your imagePullSecrets:
      oc get sa db2u -oyaml
    4. 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: for tolerations:
          
tolerations:
  - effect: NoSchedule
    key: icp4data
    operator: Equal
    value: <dedicated_specifier>

        Where <dedicated_specifier> is replaced with the node label that is entered in the Value for node label field of the web console.

      • A section under spec: for affinity:
        
affinity:
  nodeAffinity:
    requiredDuringSchedulingIgnoredDuringExecution:
      nodeSelectorTerms:
      - matchExpressions:
        - key: icp4data
          operator: In
          values:
          - <dedicated_specifier>

        Where <dedicated_specifier> is replaced with the node label that is entered in the Value for node label field of the web console.

    5. Set spec.environment.database.ssl.allowSslOnly to true to disable the TCP/IP port. This setting allows only SSL connections to the database.
    6. Set spec.environment.instance.registry.DB2_4K_DEVICE_SUPPORT: "ON" when you have a storage device that uses 4 KB sector size, as in Portworx storage for example. If it isn't set, Db2 Warehouse uses a default 512-byte sector size.
    7. Set spec.environment.instance.registry.DB2_WORKLOAD: "PUREDATA_OLAP" and spec.environment.databases.settings.dftTableOrg: "ROW" to enable Operational Analytics workloads for Db2 Warehouse.
    8. Set spec.environment.databases.settings.encrypt to "NO" to disable Db2 native encryption in your database.
    9. Deploy Db2 with no privileges in a restricted-v2 environment
      1. Set the UID/GID range and MCS label on the target namespace to annotate it in compliance with the restricted-v2 requirements.
        uidGidRange='1001000000/10000'
        oc annotate namespace ${namespace} --overwrite openshift.io/sa.scc.supplemental-groups=${uidGidRange} openshift.io/sa.scc.uid-range=${uidGidRange} openshift.io/sa.scc.mcs=s0:c27,c512
      2. Configure the deployment CR to request the restricted-v2 SCC and enforce non-root installation.
        metadata:
  annotations:
    openshift.io/required-scc: "restricted-v2"
spec:
  account:
    securityConfig:
      privilegedSysctlInit: false
      nonRootInstall: true

    For more information, see Deploying Db2 Warehouse with non-root access in a restricted-v2 SCC on IBM Software Hub.

  3. To create the database, run the following command :
    oc create -f db2wh.yaml
  4. Run the following command and wait for the state to be Ready, or you can monitor the status in the web console:
    oc get db2uinstance db2wh-<numeric_number>
MPP (multiple database partition)
  1. Create a custom resource db2wh.yaml file to define the database for your environment.

    Refer the following custom resource example and specify the parameters in your custom resource:

    apiVersion: db2u.databases.ibm.com/v1
kind: Db2uInstance
metadata:
  labels:
    cpd_db2: db2wh
    db2u/cpdbr: db2u
    icpdsupport/addOnId: db2wh
    icpdsupport/app: db2wh-1654083367196453
  name: db2wh-1654083367196453
  namespace: zen
spec:
  account:
    imagePullSecrets:
    - <db2u_sa_pullsecret>
    securityConfig:
      privilegedSysctlInit: true
  addOns:
    opendataformats:
      enabled: false
  advOpts:
    db2SecurityPlugin: cloud_gss_plugin
    zenControlPlaneNamespace: zen
  affinity:
    nodeAffinity: {}
  environment:
    authentication:
      ldap:
        enabled: false
    databases:
    - name: BLUDB
      settings:
        dftTableOrg: COLUMN
    dbType: db2wh
    instance:
      dbmConfig:
        SRVCON_PW_PLUGIN: IBMIAMauthpwfile
        group_plugin: IBMIAMauthgroup
        srvcon_auth: GSS_SERVER_ENCRYPT
        srvcon_gssplugin_list: IBMIAMauth
      password:
        value: 'password'
      registry:
        DB2_FMP_RUN_AS_CONNECTED_USER: "NO"
        DB2_WORKLOAD: ANALYTICS
        DB2AUTH: OSAUTHDB,ALLOW_LOCAL_FALLBACK,PLUGIN_AUTO_RELOAD
    partitionConfig:
      dataOnMln0: true
      total: 2
      volumePerPartition: true
    ssl:
      allowSslOnly: false
      certLabel: CN=zen-ca-cert
      secretName: 'db2wh-internal-tls'
  license:
    accept: true
  nodes: 2
  podTemplate:
    db2u:
      resource:
        db2u:
          limits:
            cpu: "4"
            memory: 24Gi
  storage:
  - name: meta
    spec:
      accessModes:
      - ReadWriteMany
      resources:
        requests:
          storage: 100Gi
      storageClassName: <yourStorageClass>
    type: create
  - name: backup
    spec:
      accessModes:
      - ReadWriteMany
      resources:
        requests:
          storage: 100Gi
      storageClassName: <yourStorageClass>
    type: create
  - name: archivelogs
    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: 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
  - visibility:
    - db2u
    volumeSource:
      configMap:
        name: management-ingress-ibmcloud-cluster-info
    Required format
    The name of the db2uinstance must have the following format: db2wh-<numeric_number>. The <numeric_number> must fit these criteria:
    • It must be unique and not copied from another instance.
    • It cannot start with 0.
    • The ID cannot begin with 0.

    For example, name: db2wh-1654083367196453

    Required labels
    • cpd_db2: db2wh
    • db2u/cpdbr: db2u
    • icpdsupport/addOnId: db2wh
    Optional labels
    The cpd_display_name label can be added if you want a custom display name for the database tile. The display name must be unique in order for the tile to show on the Databases page.

    For example, cpd_display_name: <unique_web_console_database_tile_name>

    • Replace <yourStorageClass> with a valid storage class for your cluster.
    • Replace <database_version> with the version of your database instance.
    • Replace <db2u_sa_pullsecret> with the imagePullSecrets associated with your db2u service account.
      Tip: You can run the following command to find your imagePullSecrets:
      oc get sa db2u -oyaml
    • 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: for tolerations:
          
tolerations:
  - effect: NoSchedule
    key: icp4data
    operator: Equal
    value: <dedicated_specifier>

        Where <dedicated_specifier> is replaced with the node label that is entered in the Value for node label field of the web console.

      • A section under spec: for affinity:
        
affinity:
  nodeAffinity:
    requiredDuringSchedulingIgnoredDuringExecution:
      nodeSelectorTerms:
      - matchExpressions:
        - key: icp4data
          operator: In
          values:
          - <dedicated_specifier>

        Where <dedicated_specifier> is replaced with the node label that is entered in the Value for node label field of the web console.

    • Set spec.environment.database.ssl.allowSslOnly to true to disable the TCP/IP port. This setting allows only SSL connections to the database.
    • Set spec.environment.instance.registry.DB2_4K_DEVICE_SUPPORT: "ON" when you have a storage device that uses 4 KB sector size, as in Portworx storage for example. If it isn't set, Db2 Warehouse uses a default 512-byte sector size.
    • Set spec.environment.instance.registry.DB2_WORKLOAD: "PUREDATA_OLAP" and spec.environment.databases.settings.dftTableOrg: "ROW" to enable Operational Analytics workloads for Db2 Warehouse.
    • Set .spec.environment.database.settings.encrypt to "false" to disable Db2 native encryption in your database.
  2. To create the database, run the following command :
    oc create -f db2wh.yaml
  3. Run the following command and wait for the state to be Ready, or you can monitor the status in the web console:
    oc get db2uinstance db2wh-<numeric_number>

Validating that the service instance was created

To validate that the service instance was created, run the following command:

cpd-cli service-instance status ${INSTANCE_NAME} --profile=${CPD_PROFILE_NAME} --service-type=db2wh

What to do next

As a database administrator, you can: