User Tools

Site Tools


middleware:db2

IBM DB2

IBM DB2 LUW v8.2-v9.5 in CentOS5

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 Control Center configuration

Add System

  • 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

Add Instance

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

Add Database

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

DB2 Control Center Usage

Sequences

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

DB2 command line cheatsheet

System

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

For RHEL6 (upstart) see http://www-01.ibm.com/support/docview.wss?uid=swg21209001

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

Instance

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
# Verify
db2 GET DBM CFG | grep MON_HEAP_SZ

DBs

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>

Transactions and Isolation Levels

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

ANSI SQL Isolation Level DB2 UDB equivalent
SERIALIZABLE Repeatable read (RR)
REPEATABLE READ Read stability (RS)
READ COMMITTED (default) Cursor stability (CS)
READ UNCOMMITTED Uncommitted read (UR)

BufferPools

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

More memory for STMTHEAP

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

Inquiry

Show existing tablespaces, tables and schema - LUW:

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

Show existing tablespaces, tables and schema - z/OS:

select CREATOR, NAME, DBNAME,TSNAME from SYSIBM.SYSTABLES where CREATOR='MYSCHEMA';

Show the grants

SELECT granteetype, controlauth, alterauth, deleteauth, indexauth, insertauth, selectauth, refauth, updateauth FROM syscat.tabauth WHERE grantee = 'MYUSER' AND tabname = 'MYTABLE'

Set/show the current schema:

SET SCHEMA <schemaname>
VALUES (CURRENT SCHEMA)

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

Tablespaces

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)

Schemata

To completely drop a schema:

call ADMIN_DROP_SCHEMA('MYSCHEMA', NULL, 'ERRORSCHEMA', 'ERRORTABLE')

If the return status is not zero check which objects gave error:

select * from ERRORSCHEMA.ERRORTABLE

Catalog

To add a remote node and a database to a catalog:

CATALOG TCPIP NODE MYNODE REMOTE db2.example.com SERVER 50000
CATALOG DATABASE MYDB AS MYDB at node MYNODE AUTHENTICATION SERVER

SQL

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;

Execute DDL

Via command line, recommended syntax:

  export DB2OPTIONS="-v +o +c -s -t"
  db2 connect to MYDB
  db2 -f script.sql -z script.log
  db2 connect reset

Load, Export and Import

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

Recover from an aborted load

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.

Export DDLs

$ 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.

Import DDLs

$ 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.

Call System Command from JDBC

Use CALL SYSPROC.ADMIN_CMD('command')

Example:

CALL SYSPROC.ADMIN_CMD('REORG TABLE FOO.BAR');

OS Specific

Solaris

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

db2ptree

z/OS

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

SET CURRENT SQLID = 'SCHEMANAME'

Get the tables in a schema:

select NAME from SYSIBM.SYSTABLES where CREATOR = 'SCHEMANAME';

Debugging

Administration notification log

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

db2diag.log

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

License management

List the current active license:

db2licm -l

Add a license via a license file:

db2licm -a db2ese.lic

Attachments

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

middleware/db2.txt · Last modified: 2015/06/08 14:18 by ap

Informativa sui cookie