IBM DB2

®

9.7

Troubleshooting
Hands-on Lab

I

Information Management Cloud Computing Center of Competence

IBM Canada Lab

2

Contents

1. INTRODUCTION ...........................................................................................3

2. OBJECTIVES ................................................................................................3

3. SUGGESTED READING...............................................................................3

4. WORKING WITH THE HELP (?) COMMAND...............................................3

4.1

T

RY IT

:

P

RACTICE WITH THE HELP

(?)

COMMAND

............................................4

5. WORKING WITH THE DB2DIAG.LOG FILE ................................................4

5.1

T

RY IT

:

D

IAGNOSE A PROBLEM USING THE DB

2

DIAG

.

LOG FILE

..........................4

6. WORKING WITH THE DB2 INFORMATION CENTER.................................5

6.1

T

RY IT

:

G

ET MORE INFORMATION USING THE

DB2

I

NFORMATION

C

ENTER

........5

7. SOLUTIONS..................................................................................................5

P

RACTICE WITH THE HELP

(?)

COMMAND

................................................................5

D

IAGNOSE A PROBLEM USING THE DB

2

DIAG

.

LOG

.....................................................6

G

ET MORE INFORMATION USING THE

DB2

I

NFORMATION

C

ENTER

.............................7

3

1. Introduction

In this lab you will practice with troubleshooting techniques.

2. Objectives

By the end of this lab, you will be able to:

 Work with the DB2 Help (?) command
 Diagnose a problem with the db2diag.log file
 Look for more information about a topic from the DB2 Information Center

3. Suggested

reading

Getting started with DB2 Express-C eBook (Appendix A)
https://www.ibm.com/developerworks/wikis/display/DB2/FREE+Book-
+Getting+Started+with+DB2+Express-C

A free eBook that can quickly get you up to speed with DB2

4.

Working with the help (?) command

When working with DB2, you may encounter problems, and a SQLCODE may be
returned. You can find out more information about this SQLCODE using the DB2 help
(?) command. For example, try the following for these SQLCODEs -104, -204
respectively:

From the DB2 Command Window or Linux shell type:

db2inst1@db2rules:~> db2 “? SQL0104N”

db2inst1@db2rules:~> db2 “? SQL0204N”

If you don’t remember a DB2 command, or the full syntax of the command, you can also
use the help (?) command. For example, let’s say you partially remember a command
that starts with “LIST”, but don’t remember what follows after. You can try from the DB2
Command Window or Linux shell the following:

db2inst1@db2rules:~> db2 “? LIST”

4

Note that the HELP command will not work with SQL Statements. Use the DB2
Information Center for more information about SQL Statements.

4.1

Try it: Practice with the help (?) command

1. Find out more information about these SQLCODEs: -805, +100

2. Find out the syntax of the BACKUP command using the help (?) command

5.

Working with the db2diag.log file

The db2diag.log file is another source of information for problems encountered while
working with the DB2 server. Find the location of this file and take a look at its contents.
The location of the file depends on the operating system:

ƒ Windows Vista and later



ProgramData\IBM\DB2\

ƒ Windows XP/2003



C:\Documents and Settings\All Users\Application

Data\IBM\DB2\DB2COPY1\<instance name>

ƒ Linux/UNIX



INSTHOME/sqllib/db2dump (INSTHOME is the home directory of

the instance owner)

5.1

Try it: Diagnose a problem using the
db2diag.log file

1. Rename the db2diag.log to db2diag.old.log. Next, issue these changes from the

DB2 Command Window or Linux Shell:

db2inst1@db2rules:~> db2 “update db cfg for sample using LOCKLIST

4”

db2inst1@db2rules:~> db2 “update db cfg for sample using MAXAPPLS

100”

Review the db2diag.log, and find the entries corresponding to the above
changes.

2. Issue the following from the DB2 command window:

db2inst1@db2rules:~> db2 “connect to SAMPLE”

db2inst1@db2rules:~> db2stop

5

Review the db2diag.log. What error message do you get?

3. This is the end of this section. Reset the configuration parameter changes using

these commands:

db2inst1@db2rules:~> db2 reset db cfg for sample

db2inst1@db2rules:~> db2stop force

db2inst1@db2rules:~> db2start

6.

Working with the DB2 Information
Center

The DB2 Information Center contains the DB2 online manuals. To access it, go to this
site:

http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp

6.1

Try it: Get more information using the DB2
Information Center

1. Let’s say you want to learn more details about the syntax of the CREATE TABLE

statement. Look for this information in the DB2 Information Center.

7. Solutions

Practice with the help (?) command

Solutions for section 4.1

1. Find out more information about these SQLCODEs: -805, +100

From the DB2 Command Window or Linux Shell type:

db2inst1@db2rules:~> db2 “? SQL0805N”

db2inst1@db2rules:~> db2 “? SQL0100W” (or db2 “? SQL0100”)

Find out the syntax of the BACKUP command using the help (?) command:

db2inst1@db2rules:~> db2 “? backup”

6

Diagnose a problem using the db2diag.log

Solutions for section 5.1

1.
Rename the db2diag.log to db2diag.old.log. Next, issue these changes from the DB2
Command Window or Linux Shell:

db2inst1@db2rules:~> db2 “update db cfg for sample using LOCKLIST 4”

db2inst1@db2rules:~> db2 “update db cfg for sample using MAXAPPLS 100”

A new db2diag.log file should have been generated. Take a look at the contents. What
do you see?

This is a sample of what you would see. Obviously, the timestamp will be different.

2011-05-07-23.03.38.359000-240 I1097H468 LEVEL: Event

PID : 2448 TID : 8048 PROC : db2syscs.exe

INSTANCE: DB2 NODE : 000 DB : SAMPLE

APPHDL : 0-588 APPID: *LOCAL.DB2.080907152844

AUTHID : ARFCHONG

EDUID : 8048 EDUNAME: db2agent (SAMPLE)

FUNCTION: DB2 UDB, config/install, sqlfLogUpdateCfgParam, probe:20

CHANGE : CFG DB SAMPLE: "Locklist" From: "50" To: "4"

2011-05-07-23.04.16.468000-240 I1567H482 LEVEL: Event

PID : 2448 TID : 8048 PROC : db2syscs.exe

INSTANCE: DB2 NODE : 000 DB : SAMPLE

APPHDL : 0-588 APPID: *LOCAL.DB2.080907152844

AUTHID : ARFCHONG

EDUID : 8048 EDUNAME: db2agent (SAMPLE)

FUNCTION: DB2 UDB, config/install, sqlfLogUpdateCfgParam, probe:20

CHANGE : CFG DB SAMPLE: "Maxappls" From: "40" <automatic> To: "100"

2. Issue the following from the DB2 command window:

db2inst1@db2rules:~> db2 “connect to SAMPLE”

db2inst1@db2rules:~> db2stop

Review the db2diag.log. What error message do you get?

2011-05-04-23.05.18.515000-240 I5006H302 LEVEL: Error

PID : 2200 TID : 528 PROC : db2syscs.exe

INSTANCE: DB2 NODE : 000

EDUID : 528

FUNCTION: DB2 UDB, base sys utilities, DB2StopMain, probe:502

MESSAGE : EntryId[000][Reason:GATEWAY_INUSE]

7

The message says “GATEWAY_INUSE” which maps to what you get from the DB2
Command window: SQL1025N The database manager was not stopped because
databases are still active.

Get more information using the DB2 Information Center

Solutions for section 6.1

1. Type “create table” in the Search Field of the DB2 Information Center and click

“Go”. From the left tree, click on the item that best meets the criteria of looking
for the syntax of this statement. Normally, if it refers to the syntax of a statement,
if will be represented in upper case. So from all the hits, look for CREATE
TABLE in upper case. Click on that item, and on the right side scroll down until
you find the complete syntax. Below we show part of this syntax:

>>-CREATE TABLE--table-name------------------------------------->

>--+-| element-list |----------------------------+--●----------->

+-OF--type-name1--+-------------------------+-+

| '-| typed-table-options |-' |

+-LIKE--+-table-name1-+--+------------------+-+

| +-view-name---+ '-| copy-options |-' |

| '-nickname----' |

+-| as-result-table |--+------------------+---+

| '-| copy-options |-' |

+-| materialized-query-definition |-----------+

'-| staging-table-definition |----------------'

>--+-------------------------------------------------------------------

---+-->

| .-,-------------------------.

|

| .-DIMENSIONS-. V |

|

'-ORGANIZE BY--+-+------------+--(----+-column-name-----------+-+--

)-+-'

| | .-,-----------. |

|

| | V | |

|

| '-(----column-name-+--)-'

|

'-KEY SEQUENCE--| sequence-key-spec |----------------

-'

.-DATA CAPTURE NONE----.

>--●--+----------------------+--●--+------------------------+--->

'-DATA CAPTURE CHANGES-' '-| tablespace-clauses |-'

…

8

© Copyright IBM Corporation 2011
All Rights Reserved.

IBM Canada
8200 Warden Avenue
Markham, ON
L6G 1C7
Canada


IBM, IBM (logo), and DB2 are trademarks or registered trademarks
of International Business Machines Corporation in the United
States, other countries, or both.

Linux is a trademark of Linus Torvalds in the United States, other
countries, or both

UNIX is a registered trademark of The Open Group in the United
States, other countries, or both

Windows is a trademark of Microsoft Corporation in the United
States, other countries, or both.

VMware is a trademark or VMware Inc. in the United States, other
countries, or both.

Other company, product, or service names may be trademarks or
service marks of others.

References in this publication to IBM products or services do not
imply that IBM intends to make them available in all countries in
which IBM operates. The following paragraph does not apply to the
United Kingdom or any other country where such provisions are
inconsistent with local law:

INTERNATIONAL BUSINESS MACHINES CORPORATION
PROVIDES THIS PUBLICATION "AS IS" WITHOUT WARRANTY
OF ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING,
BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF NON-
INFRINGEMENT, MERCHANTABILITY OR FITNESS FOR A
PARTICULAR PURPOSE.

Some states do not allow disclaimer of express or implied
warranties in certain transactions, therefore, this statement may not
apply to you.

This information could include technical inaccuracies or
typographical errors. Changes are periodically made to the
information herein; these changes will be incorporated in new
editions of the publication. IBM may make improvements and/or
changes in the product(s) and/or the program(s) described in this
publication at any time without notice.

Any performance data contained herein was determined in a
controlled environment. Therefore, the results obtained in other
operating environments may vary significantly. Some
measurements may have been made on development-level
systems and there is no guarantee that these measurements will be
the same on generally available systems. Furthermore, some
measurement may have been estimated through extrapolation.
Actual results may vary. Users of this document should verify the
applicable data for their specific environment.

Information concerning non-IBM products was obtained from the
suppliers of those products, their published announcements or
other publicly available sources. IBM has not tested those products
and cannot confirm the accuracy of performance, compatibility or
any other claims related to non-IBM products. Questions on the
capabilities of non-IBM products should be addressed to the
suppliers of those products.

The information in this publication is provided AS IS without
warranty. Such information was obtained from publicly available
sources, is current as of July 2009, and is subject to change. Any
performance data included in the paper was obtained in the specific
operating environment and is provided as an illustration.
Performance in other operating environments may vary. More
specific information about the capabilities of products described
should be obtained from the suppliers of those products.