IBM Db2

https://www.ibm.com/docs/en/db2/12.1.x
Admin
IBM Db2 is an AI-powered, next-generation enterprise relational database that supports low-latency...
Tokens:4,238,153
Snippets:35,292
Trust Score:10
Update:1 month ago
Show doc for...
Tokens:
Raw
### Example installation command with log and trace files

Source: https://www.ibm.com/docs/en/db2/12.1.x?topic=aix-installing-db2-purescale-feature-using-response-file

This example demonstrates installing the Db2 pureScale Feature using the response file db2dsf.rsp while specifying custom log and trace file locations.

```bash
db2setup -r /mydir/db2server.rsp -l /tmp/db2setup.log -t /tmp/db2setup.trc 
```

--------------------------------

### Start Db2 Setup Wizard

Source: https://www.ibm.com/docs/en/db2/12.1.x?topic=idpea-installing-db2-purescale-feature-using-db2-setup-wizard

Command to launch the Db2 Setup wizard with logging and tracing enabled.

```bash
./db2setup -l /tmp/db2setup.log -t /tmp/db2setup.trc
```

--------------------------------

### Example installation with response file and logs

Source: https://www.ibm.com/docs/en/db2/12.1.x?topic=linux-installing-db2-purescale-feature-using-response-file

This example demonstrates running the db2setup command while specifying the response file, a log file, and a trace file.

```bash
db2setup -r /mydir/db2server.rsp -l /tmp/db2setup.log -t /tmp/db2setup.trc 
```

--------------------------------

### Start Access Server processes

Source: https://www.ibm.com/docs/en/db2/12.1.x?topic=tables-installing-infosphere-cdc

Navigate to the installation bin directory and start the Access Server process in the background.

```bash
cd /access-server-installation-dir/bin/ 
nohup ./dmaccessserver &
```

--------------------------------

### Install and start the daemon on Windows

Source: https://www.ibm.com/docs/en/db2/12.1.x?topic=daemon-db2script-command-options-examples

Installs and starts the daemon service on a Windows server.

```cmd
db2script_daemon -a install
db2script_daemon -a start
```

--------------------------------

### Execute Db2 setup with a response file

Source: https://www.ibm.com/docs/en/db2/12.1.x?topic=windows-installing-db2-product-using-response-file

Run the setup command pointing to your response file to initiate the automated installation process.

```shell
   setup -u response_file
```

--------------------------------

### Start Db2 installation with db2setup

Source: https://www.ibm.com/docs/en/db2/12.1.x?topic=ifplu-installing-offline-fix-packs-add-db2-database-products

Execute the db2setup command from the directory containing the fix pack image to initiate the installation process.

```bash
./db2setup
```

--------------------------------

### Example db2nodes.cfg entry for ServerA

Source: https://www.ibm.com/docs/en/db2/12.1.x?topic=installation-updating-node-configuration-file

This example shows the content of the db2nodes.cfg file on the instance-owning server ServerA after it has been created by the Db2 Setup wizard.

```text
     0     ServerA     0
```

--------------------------------

### Setup sample_lines table

Source: https://www.ibm.com/docs/en/db2/12.1.x?topic=functions-st-removepoint

Creates the sample_lines table and inserts two linestrings for use in subsequent examples.

```sql
SET CURRENT FUNCTION PATH = CURRENT FUNCTION PATH, db2gse
CREATE TABLE sample_lines (id INTEGER, line ST_LineString)

INSERT INTO sample_lines
  VALUES (1,ST_LineString('linestring 
         (10 10, 5 5, 0 0, 10 0, 5 5, 0 10)', 0))

INSERT INTO sample_lines
  VALUES (2, ST_LineString('linestring z 
         (0 0 4, 5 5 5, 10 10 6, 5 5 7, 0 0 8)', 0))
```

--------------------------------

### WLM_GET_CONN_ENV query output example

Source: https://www.ibm.com/docs/en/db2/12.1.x?topic=wmr-wlm-get-conn-env-get-settings-activity-data-collection-connection

Example output showing the XML structure returned by the WLM_GET_CONN_ENV function.

```text
APPLICATION_HANDLE   DETAILS
-------------------- ------------------------------------------------------
                   7 <wlm_conn_env
                     xmlns=http://www.ibm.com/xmlns/prod/db2/mon
                     release="9070100">
                     <collectactdata>NONE</collectactdata>
                     <collectactpartition>COORDINATOR</collectactpartition>
                     <collectsectionactuals>NONE</collectsectionactuals>
                     </wlm_conn_env>
```

--------------------------------

### Drop a member subset

Source: https://www.ibm.com/docs/en/db2/12.1.x?topic=wmr-wlm-drop-member-subset-drop-member-subset-object-1

Example showing how to drop a member subset named MY_SUBSET using the SYSPROC.WLM_DROP_MEMBER_SUBSET procedure.

```sql
   CALL SYSPROC.WLM_DROP_MEMBER_SUBSET( 'MY_SUBSET' )
```

--------------------------------

### Grant USAGE privilege on a workload

Source: https://www.ibm.com/docs/en/db2/12.1.x?topic=workloads-granting-usage-privilege-workload

Example of granting the USAGE privilege on the ACCOUNTS workload to the CPA group.

```sql
GRANT USAGE ON WORKLOAD ACCOUNTS TO GROUP CPA
```

--------------------------------

### Setup and Populate SAMPLE_GEOMETRIES Table

Source: https://www.ibm.com/docs/en/db2/12.1.x?topic=functions-st-wkbtosql

This example sets the function path, creates a table to store geometries and their WKB representations, and populates the table with sample data before updating the WKB column.

```sql
SET CURRENT FUNCTION PATH = CURRENT FUNCTION PATH, db2gse
CREATE TABLE sample_geometries
  (id INTEGER, geometry ST_Geometry, wkb BLOB(32K) )

INSERT INTO sample_geometries (id, geometry)
  VALUES (10, ST_Point ( 'point (44 14)', 0 ) ),
         (11, ST_Point ( 'point (24 13)', 0 ) ),
         (12, ST_Polygon ('polygon ((50 20, 50 40, 70 30, 50 20))', 0 ) )
UPDATE sample_geometries AS temp_correlated
  SET wkb = ST_AsBinary(geometry)
  WHERE id = temp_correlated.id
```

--------------------------------

### Start the daemon with debugging level

Source: https://www.ibm.com/docs/en/db2/12.1.x?topic=daemon-db2script-command-options-examples

Installs and starts the daemon with a specified debugging level of 2.

```cmd
db2blast_daemon -a install -d 2
db2blast_daemon -a start
```

--------------------------------

### Start instance and connect to sample database

Source: https://www.ibm.com/docs/en/db2/12.1.x?topic=ppit-verifying-your-db2-purescale-installation-setup

Start the Db2 instance and establish a connection to the SAMPLE database.

```bash
   db2start 
   db2 connect to SAMPLE
```

--------------------------------

### Example of installFixPack with a specific directory

Source: https://www.ibm.com/docs/en/db2/12.1.x?topic=instance-uninstalling-mod-packs-fix-packs

This example demonstrates the installFixPack command targeting a specific Db2 installation directory.

```bash
./installFixPack -f level -b /opt/ibm/db2/V12.1
```

--------------------------------

### Install Tivoli SA MP license using samlicm

Source: https://www.ibm.com/docs/en/db2/12.1.x?topic=adl-installation-db2-license-certificates-in-db2-purescale-environment

Run this command on each host in the cluster to install the Tivoli SA MP license. This example shows the installation for version 3.2.

```bash
samlicm -i /opt/ibm/db2/V10.5/sd/db2/linuxamd64/tsamp/license/sam32.lic
```

--------------------------------

### Run setup.py for DB SQL Extension Toolkit

Source: https://www.ibm.com/docs/en/db2/12.1.x?topic=nps-installing-db-sql-extension-toolkit

Use the setup.py script to install or clean up the toolkit. The command supports various flags for authentication, database connection details, and schema configuration.

```bash
./setup.py [-user USER -schema SCHEMA] {[-dsn DSN] | [-pw PASS -port PORT -db DATABASE -host HOST_IP]} [-cleanup]

  -h, --help      show this help message and exit
  -user USER      Username with which you want to register udx. Default is bluadmin.
  -pw PW          Password of -u user; prompts user if -u is provided.
  -host HOST      Host IP if you're using Db2WoC. Default is current host's IP.
  -port PORT      Port number in case of SSL connections. Default is 50000.
  -db DB          Database name. Default is BLUDB.
  -dsn DSN        Used in case of SSL connection mostly.
  -schema SCHEMA  Schema name in which user wants to register the UDXes. Default is $USER.
  -cleanup        Cleanup the sqltoolkit funtions created.
```

--------------------------------

### Navigate to toolkit directory and list contents

Source: https://www.ibm.com/docs/en/db2/12.1.x?topic=nps-installing-db-sql-extension-toolkit

Change into the extracted directory and list the files to confirm the presence of setup.py and other components.

```bash
[bluadmin@host - Db2wh ga_1.2]$ cd db_sqltoolkit_x86_64
        [bluadmin@host - Db2wh db_sqltoolkit_x86_64]$ ll
        total 4100
        -rw-r--r-- 1 bluadmin bluadmin   40011 Sep  3 12:39 create_sql_function.base
        -rw-r--r-- 1 bluadmin bluadmin     182 Sep  5 13:09 deploy.base
        -rw-r--r-- 1 bluadmin bluadmin     536 Aug 14 14:19 deploy_toolkit.cpp
        -rw-r--r-- 1 bluadmin bluadmin    8556 Sep  5 13:33 drop_sql_function.base
        -rw-r--r-- 1 bluadmin bluadmin      22 Sep  5 10:10 get_string_units.base
        -rw-r--r-- 1 bluadmin bluadmin      80 Aug 29 14:05 get_upload_path.sql
        -rw-r--r-- 1 bluadmin bluadmin    2069 Aug 14 14:19 README
        -rwxr-xr-x 1 bluadmin bluadmin   11932 Sep  5 14:31 setup.py
        -rw-r--r-- 1 bluadmin bluadmin 4106240 Aug 14 14:19 toolkit_libs.tar
        -rw-r--r-- 1 bluadmin bluadmin      36 Aug 29 14:04 upload.sql
```

--------------------------------

### Start instances

Source: https://www.ibm.com/docs/en/db2/12.1.x?topic=ppit-enabling-scsi-3-persistent-reserve-db2-purescale-shared-file-systems

Run this command as the instance user on all hosts to start instances.

```bash
db2start instance on <hostname>
```

--------------------------------

### Run setup script

Source: https://www.ibm.com/docs/en/db2/12.1.x?topic=nps-installing-db-sql-extension-toolkit

Execute the setup.py script to register functions, providing the schema name as an argument.

```bash
[bluadmin@host - Db2wh db_sqltoolkit_x86_64]$ ./setup.py -schema sqltk
        [INFO] : Checking required files ...
        [INFO] : Connecting user bluadmin with 172.16.176.74:50000/BLUDB
        Enter password for bluadmin :
        [INFO] : STRING_UNITS detected as : SYSTEM
        [INFO] : The files have been uploaded.
        [INFO] : Registering the functions ...
        [INFO] : Successfully operated ..
        [bluadmin@host - Db2wh db_sqltoolkit_x86_64]$
```

--------------------------------

### Remove geocoding setup for a spatial column

Source: https://www.ibm.com/docs/en/db2/12.1.x?topic=commands-db2se-remove-gc-setup-command

This example removes the geocoding setup for the column named MYCOLUMN in the table MYTABLE within the database mydb.

```bash
db2se remove_gc_setup mydb -tableName mytable -columnName mycolumn
```

--------------------------------

### Start Database Instance

Source: https://www.ibm.com/docs/en/db2/12.1.x?topic=mirror-using-split-as-clone-database

Start the database instance on the secondary system.

```bash
db2start
```

--------------------------------

### Sample audit record

Source: https://www.ibm.com/docs/en/db2/12.1.x?topic=layouts-audit-record-layout-audit-events

An example of a raw audit record for a START event.

```text
timestamp=2007-04-10-08.29.52.000001;
category=AUDIT;
audit event=START;
event correlator=0;
event status=0;
userid=newton;
authid=NEWTON;
application id=*LOCAL_APPLICATION;
application name=db2audit.exe;
```

--------------------------------

### Syntax Examples for 10 Database Partitions

Source: https://www.ibm.com/docs/en/db2/12.1.x?topic=partitions-using-database-partition-expressions

Demonstrates various syntax patterns for container paths when 10 database partitions are present, showing the result for partition 5.

```text
     '/dbdir/node $N /cont1'
     '/ $N+1000 /file1'
     ' $N%10 /container'
     '/dir/ $N2000 /dmscont'
```

```text
     '/dbdir/node5/cont1'
     '/1005/file1'
     '5/container'
     '/dir/2000/dmscont'
```

--------------------------------

### Example netstat output for multiple partitions

Source: https://www.ibm.com/docs/en/db2/12.1.x?topic=fetching-verifying-interpartition-parallelism-nickname-parallel

Example output showing four socket connections to the same remote data source when partitions are installed on a single physical server.

```text
tcp        0      0 192.168.0.10:53110         192.168.0.18:50000        ESTABLISHED –
tcp        0      0 192.168.0.10:53111         192.168.0.18:50000        ESTABLISHED –
tcp        0      0 192.168.0.10:53113         192.168.0.18:50000        ESTABLISHED –
tcp        0      0 192.168.0.10:53115         192.168.0.18:50000        ESTABLISHED 
```

--------------------------------

### Add a database partition server example

Source: https://www.ibm.com/docs/en/db2/12.1.x?topic=environments-adding-database-partition-servers-instance-windows

Example command to add a new database partition server (partition 2) to the instance TESTMPP on the instance-owning computer MYMACHIN using logical port 1.

```text
   db2ncrt /n:2 /p:1 /u:my_id,my_pword /i:TESTMPP
      /M:TEST /o:MYMACHIN
```

--------------------------------

### Determine updated row count with GET DIAGNOSTICS

Source: https://www.ibm.com/docs/en/db2/12.1.x?topic=s-get-diagnostics

This example shows how to use GET DIAGNOSTICS to capture the number of rows updated by an UPDATE statement in an SQL procedure.

```sql
CREATE PROCEDURE sqlprocg (IN deptnbr VARCHAR(3))
   LANGUAGE SQL
   BEGIN
      DECLARE SQLSTATE CHAR(5);
      DECLARE rcount INTEGER;
      UPDATE CORPDATA.PROJECT
         SET PRSTAFF = PRSTAFF + 1.5
         WHERE DEPTNO = deptnbr;
      GET DIAGNOSTICS rcount = ROW_COUNT;
-- At this point, rcount contains the number of rows that were updated.
...
   END
```

--------------------------------

### Configure silent installation with DB2DSDRIVER_CFG_SOURCE_PATH

Source: https://www.ibm.com/docs/en/db2/12.1.x?topic=drivers-data-server-driver-package-windows

Example of specifying a custom db2dsdriver configuration file during silent installation. The mydsdriver.cfg file will be copied to the common application data path as db2dsdriver.cfg.

```text
DB2_COMMON_APP_DATA_TOP_PATH=C:\My Common App Data
DB2DSDRIVER_CFG_SOURCE_PATH = C:\Common Repository\mydsdriver.cfg
COPY_NAME = IBMDB2CL4
```
Tokens:
Raw