Partitions held by a table

How to find the total number of database partitions and range (data) partitions held by a table? Here is the query: select t.tabschema SCHEMA, t.tabname TABLE, (select count(*) from syscat.dbpartitiongroupdef pgs where pgs.dbpgname = ts.dbpgname) DB_PARTITIONS,...

Table in Quiesce Mode

For example: I get an error creating a table or accessing a table space. create table tpcds.vikram ( c1 int not null primary key, tx_date date not null, c2 char(10), c3 timestamp(9) default current timestamp implicitly hidden, c4 smallint default current member...

Database Up Time

Usually we run uptime command in Unix to find the uptime of the server. But, what about DB2 database? How long it had been working? db2 “select db_conn_time from table (mon_get_database(-2))” The db2pd -d <database> – –> will also tell...

Database Size and Table Sizes

The size of the database can be determined by calling CALL GET_DBSIZE_INFO(?, ?, ?, 0); When we call above Db2 procedure, it also populates information in SYSTOOLS.STMG_DBSIZE_INFO table. We can write the following query to properly format the results and get the info...

Count Rows When Loading

Using HPU to unload data from source and transferring that data over sockets and using native LOAD to load the data. Also, partitioning is changing. A simple script that counts the number of rows and rate at which the LOAD is going. #!/bin/bash prevRows=0 while true...
IBM Provided Utility Scripts for Db2

IBM Provided Utility Scripts for Db2

The Db2 Warehouse client container provides a toolkit that has very nice utility scripts that can be downloaded from http://ibm.biz/db2warehousetools . The list of tools available in the tool kit are shown as below: dbsql – This is like Db2 CLP but based upon Netezza...
Utility Scripts for pureScale

Utility Scripts for pureScale

Some utility scripts that are handy when managing pureScale or DPF cluster. runall scripts Use runall script to commands on all hosts. This way, it is easier to run commands in a single command rather than to type many different lines. #!/bin/bash...
Big Data Hype and Reality

Big Data Hype and Reality

I can not put a name to a customer but they spent millions to port their data warehouse to Hadoop and 1 year later rolled everything back to the Db2 again since their results were not matching and the data was getting lost. This is a classic example of misuse of the...
Db2 pureScale netname does not exist

Db2 pureScale netname does not exist

ERROR: DBI20122E The instance was not created or updated because the following netname does not exist: "node01.zinox.com". The above error might come from different reasons but our case was due to misplaced entries in the /etc/hosts file. The natural convention for...
IBM Claims Big Breakthrough in Deep Learning

IBM Claims Big Breakthrough in Deep Learning

A Fortune article by Barb Darrow. Few interesting highlights: IBM used 64 of its own Power 8 servers—each of which links both general-purpose Intel microprocessors with Nvidia graphical processors with a fast NVLink interconnection to facilitate fast data flow between...
DB2 Backup Tuning

DB2 Backup Tuning

One of my colleague Mukesh V Desai did backup tuning for a pureScale customer and just by making the following change, reduced the backup time from from 4.5 hours to 30 minutes. Original config: Number of buffers = 8, Parallelism = 4 and Compress = YES New Config:...
DB2 pureScale Utility Scripts

DB2 pureScale Utility Scripts

Most of the utility scripts here are given for Linux using bash shell. You can use them after making syntax changes from bash to ksh. Most of the script require that you create a node information file in /root/bin/backup/ip.txt. A sample /root/bin/backup/ip.txt is...
DB2 11.1 BLU with DPF Monitoring scripts

DB2 11.1 BLU with DPF Monitoring scripts

I just wrapped a very large PoC for BLU with DPF with very high ingestion rate (110K messages per second) and as well as very high volume point queries (1500 queries per second) and I used the following queries to measure the various parameters and optimum tuning. The...
db2haicu disable / enable and scripting

db2haicu disable / enable and scripting

It is recommended that you always update RSCT / TSA from db2 software media and not from IBM Fix Central. The reason – DB2 is tested and certified with the version that ships with the DB2 software. If you go to server_t/db2/linuxamd64/tsamp and run db2cktsa...
DB2 pureScale –  No Product License found

DB2 pureScale – No Product License found

In db2diag, you may see this message – No product license found. Sample output:  2017-05-19-13.00.58.511626-240 E2575E1007 LEVEL: Severe PID : 35363 TID : 70366589481392 PROC : db2start INSTANCE: purf01 NODE : 000 HOSTNAME: va33dlvudb001 FUNCTION: DB2 UDB, high...
DB2 pureScale instance creation hangs after GPFS

DB2 pureScale instance creation hangs after GPFS

Hang Issue due to SMAP While creating DB2 pureScale instance, it appears that the node becomes unresponsive under RHEL 7.2. If you reboot the node and look at the /var/log/messages, you may notice these several messages: kernel:BUG: soft lockup - CPU#1 stuck for 23s!...
DB2 pureScale Health Check

DB2 pureScale Health Check

More and more customers are now asking for some way to check the health of the DB2 pureScale system. Let’s just focus on few early diagnostics before we jump to the deep exploration at the SQL statement level. This is just akin going to primary health care...
How to detect log full condition

How to detect log full condition

I have seen working with hundreds of customers a chronic LOG FULL condition and the shortcut people use is to increase the LOGPRIMARY and LOGSECOND. This solution works well for a single unit of work that can not fit into the total log space available. So, first...