IBM DB2

To install it, you'll need to:

• Have a graphical (gnome, KDE) environment. It shouldn't be strictly needed, IMHO, but it makes life a lot easier (in imitating an official-blessed-certified RHEL5 environment)

• Add a few libraries:

  yum install compat-libstdc++-33 libXp

• Disable SELinux

  setenforce 0

• Tune the kernel parameters as directed by the documentation

Then you can run the ./db2setup script, and enjoy the crapp^Wmarvelous and colourful java installer

DB2 express-C should be available from Ubuntu partner repository. Just uncomment the appropriate line from sources.list and install the db2exc package.

DB2 doesn't understand the SHA512 encryption used in /etc/shadow, and connecting via network you get a «Connection authorization failure occurred. Reason: local security service non-retryable error.».

To fix this, change (temporarily) the encryption scheme in

/etc/pam.d/common-password

and reset the instance user password.

• System Name: the unique label you'll see in your client
• Host name: the IP address or FQDN of the remote server
• Node name: [8 chars] the remote node name you get with db2 LIST ADMIN NODE DIRECTORY

• Instance name: the real name of the remote instance
• Instance node name: [8 chars] the unique label you'll see in your client

• Database name: the real name of the remote DB
• Alias: [8 chars] the unique label you'll see in your client

You can find the sequences in the system view SYSCAT.SEQUENCES

In Unix (Linux, Solaris) DB2 starts via the invokation of db2fmcd directly from inittab.

• To remove the DB2 fault monitor in inittab: db2fmcu -d
• To insert the DB2 fault monitor in inittab: db2fmcu -u -p <complete path to the db2fmcd object>

Define which instance should be active at boot time with db2iauto

# <db2 install dir>/bin/db2iauto -on insta2
# <db2 install dir>/bin/db2iauto -off insta4

To start the administration server (DAS):

dasusr1$ db2admin start

or (depending on the presence of SUID bits)

# db2admin start

if the DAS doesn't start see this.

To see the memory used (instance/database/private):

db2mtrk -i
db2mtrk -d
db2mtrk -p

To see which instances are defined:

# <db2 install dir>/bin/db2ilist

To stop an instance:

# su - insta3
$ db2 terminate
$ db2 force application all
$ db2stop
$ db2stop FORCE

To start an instance:

$ db2start

More memory, please!

Enlarge MON_HEAP_SZ (instance level) from default (90) to 256 (or more, as needed):

db2 UPDATE DBM CFG USING MON_HEAP_SZ 256 IMMEDIATE
db2 force application all ; db2stop ; db2start

Connect to the DB:

$ db2 CONNECT TO <database>
$ db2 CONNECT TO <database> USER <userID> USING <password>
[...]
$ db2 connect reset

Who is using the DB?

$ db2 list applications
$ db2 list applications show detail
# What are they doing?
$ db2 get snapshot for applications on <database>
# Kill'em all!
$ db2 force application all

List all instance DBs:

$ db2 list database directory
$ db2 list database directory on /path/to/db2instUserHome

Create or drop a DB:

$ db2 create database <mydb>
$ db2 drop database <mydb>

To set / disable autocommit (from the CLP):

update command options using c on
update command options using c off

To set the isolation level (CLP):

change isolation to <UR|CS|RS|RR>

Maybe you'll find useful this explanation of the Isolation levels, and how to debug Locks and Deadlocks

Show existing bufferpools:

SELECT BPNAME,PAGESIZE FROM SYSCAT.BUFFERPOOLS

Create a 32K bufferpool and some “large” tablespaces:

CONNECT TO <database>
CREATE BUFFERPOOL BP32K IMMEDIATE SIZE 1000 AUTOMATIC PAGESIZE 32 K
CREATE REGULAR TABLESPACE USERSPACE32K PAGESIZE 32 K  MANAGED BY AUTOMATIC STORAGE  BUFFERPOOL BP32K
CREATE SYSTEM TEMPORARY TABLESPACE TEMPSPACE32K PAGESIZE 32 K  MANAGED BY AUTOMATIC STORAGE  BUFFERPOOL BP32K
CONNECT RESET

If you get 0101N The statement is too long or too complex try to increase the DB STMTHEAP:

db2 UPDATE DB CFG FOR SWGGD001 USING STMTHEAP 16384 IMMEDIATE

Show existing tablespaces, tables and schema:

LIST TABLESPACES
--
LIST TABLES FOR ALL
SELECT TABNAME FROM SYSCAT.TABLES WHERE TABSCHEMA = 'MYSCHEMA'
--
SELECT SCHEMANAME FROM SYSCAT.SCHEMATA

Set/show the current schema:

SET SCHEMA <schemaname>
VALUES (CURRENT SCHEMA)

If using DB2 z/OS, set the schema with:

SET CURRENT SQLID = 'SCHEMANAME'

Show DB configuration:

db2 get db cfg for <database name>

Database available for connection (and their filesystem path):

db2 list active databases

Statement currently executing:

db2pd -db MYDB -dynamic

Detail of all tablespaces:

get snapshot for tablespaces on MYDBNAME

Objects contained in tablespace MYTBSPC

select TABSCHEMA, TABNAME from syscat.tables where TBSPACE = 'MYTBSPC' OR LONG_TBSPACE = 'MYTBSPC'

All DMS Long tablespaces:

select tbspace from  syscat.tablespaces where tbspacetype='D' and datatype='L'

Set tablespace as autoresizable:

ALTER TABLESPACE MYTBSPC AUTORESIZE YES INCREASESIZE 50 PERCENT

Resize a tablespace:

ALTER TABLESPACE MYTBSPC EXTEND (ALL CONTAINERS 32M)

Which triggers aren't valid (N=invalid, X=inoperative (must recreate))

select * from syscat.triggers where valid<>'Y'

Truncate Table ( <9.1)

ALTER TABLE T ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;
COMMIT;

Test the connection with a “dummy” select:

SELECT 1 FROM SYSIBM.SYSDUMMY1

Limit the result size:

SELECT blah FROM footbl FETCH FIRST <N> ROWS ONLY 

See all the sequences:

select SEQSCHEMA, SEQNAME from SYSIBM.SYSSEQUENCES;

In the examples, I use the at sign ( @ ) as a command delimiter.

If don't know which dump file was used for the load, the only options is to abort the load, “truncating” the table. Use:

LOAD FROM filename OF del TERMINATE INTO SCHEMA.TABLE

…where filename is an empty file.

$ db2look -d DBNAME -e -td @ -o exportfile.sql

if you get an error related to codepage conversion, set the environment variable DB2CODEPAGE to the same value used by the DB.

$ DB2OPTIONS="-v +o +c -s -td@"
$ export DB2OPTIONS
$ db2 -f inputfile.sql -z logfile.log

Note that:

• You should issue a connect to before importing (if the command isn't already included in the input file)
• Autocommit is disabled, and the command exits on error.

On Solaris, ps(1) show a lot of processes as db2sysc. To see the real process name use:

db2ptree

The administration notification log records the messages intended for DBAs. On Linux and UNIX, the log is written to a file named <instance_ID>.nfy that is located in the directory specified by the DIAGPATH instance level configuration parameter, in Windows it's written in the Event Log.

You can find the file in:

~/sqllib/db2dump/db2inst1.nfy

and roughly filter per database using:

perl -0036 -ne 'print if /Database:MYDBNAME\b/' < ~/sqllib/db2dump/db2inst1.nfy

The db2diag.log log file is found in ~/sqllib/db2dump/db2diag.log. It logs the events at instance level and si thought to be used by IBM customer support.

To filter the entries, you can use:

db2diag -g db=MYDBNAME

List the current active license:

db2licm -l

Add a license via a license file:

db2licm -a db2ese.lic

Connect to a DB2 DB via jruby/jdbc: jruby-db2.rb