Checklists and Troubleshooting

This checklist is provided to help diagnose online connectivity problems.

• On the PeopleSoft signon dialog box, the database name must be specified in upper case.

• On the PeopleSoft signon dialog box, the user/password is case-sensitive (examine table PSOPRDEFN).

• Are the ConnectID and ConnectPSWD specified properly in the PeopleTools Configuration Manager?

• Is the DB2 LUW database running? To check it:

  Database Server, type "db2 connect to database-name"

• Does the PS.PSDBOWNER table contain the database name (in uppercase) and ownerid (in lowercase)? It should contain 1 row only. If it contains more than 1 row, drop it and recreate it using /sql/dbowner.sql.

• Can you ping the server? This will test to see if the network is operating successfully.

• Can you connect to the database using the client Command Line Processor?

• Did you specify an ODBC data source for your database? You can do this with CAE’s CLI-ODBC Administrator.

• DB2 Connect on Windows requires that the user ID, which is cataloging databases and nodes, be an Administrator (not just a user with administrative authorization). The Administrator’s user ID must not exceed 8 characters.

One way to check to see whether the SQL is hung or if it is still executing due to a long unit of work or bad access path is to use DB2 LUW’s Snapshot Monitor. Other diagnostic tools include vmstat and iostat to determine server CPU and I/O activity.

The “Snapshot Monitor” requires that database monitor switches be turned on. Unfortunately, these switches must be turned on before a process is started.

To use Snapshot Monitor:

1. Logon to the Command Line Processor on the server.

2. Issue the following statements:

   • db2 update monitor switches using bufferpool on

   • db2 update monitor switches using table on

   • db2 update monitor switches using uow on

3. Allow statistics to compile.

4. Issue the following statement(s):

   db2 "get  snapshot for database on hr910dmo" > snapsht1.dbx

5. Wait a minute to allow additional statistics to compile.

6. Issue the following statement:

   db2 "get  snapshot  for  database on hr910dmo" > snapsht2.dbx

7. Compare the two files and identify any changes, such as:

   • Bufferpool logical reads

   • Bufferpool physical reads

   • Commit Statistics

   • Dynamic SQL Statements Attempted

   • Rows Selected

If parameter values are the same for both snapshots, then the transaction may be hung. If the most logical explanation is that the transaction is hung, perform this step to retry the transaction:

db2 force application (agent-id)

For example:

db2 force application (3265)   (parenthesis required)

The instance-owner-home-dir/sqllib/db2dump/db2diag.log file contains diagnosis information related to instance, utility, and connectivity problems. The full name of the directory may also be obtained by issuing a get dbg cfg command in the command line processor on the database server, then checking the DIAGPATH configuration setting. This file contains diagnosis information related to instance, utility, and connectivity problems.

Go to Control Panel. Open ODBC Administrator, select the appropriate Data Source, and then press Options. Select the ODBC Trace option.

If the problem is repeatable, you can use db2trc to trace the database internal logic. Although this trace is mostly used by DB2 UDB service personnel, it may give you some clues. To obtain the help information of db2trc, type the following command:

db2trc -h

The following is a simple example of how to use db2trc to obtain DB2 LUW internal tracing information:

db2trc -l 1000000 on
[repeat the failing process]
db2trc flw > trc.flw
db2trc fmt > trc.fmt
db2trc off

DB2 UDB Message Reference can give you detailed information about your SQL error-code. A quick way to get similar information online is to do db2 "? sqlcode". For example:

db2 "?  Sql1042"

For select PeopleTools "modules", the system captures the Module identifier and stores it in the DB2 ACCOUNTING field, which you can query as part of your typical performance monitoring. This can help you to associate transactions with a particular module when monitoring or troubleshooting.

PeopleTools populates the ACCOUNTING field as follows:

You can use the GET SNAPSHOT command to view samples of the information passed per module type.