Appendix: Administering PeopleSoft Databases on DB2 UDB for Linux, UNIX, and Windows

This appendix provides an overview of administration on DB2 UDB for Linux, UNIX, and Windows, and discusses:

• Instances.

• Configuration parameters.

• Tablespaces.

• Temporary table creation.

• Client database catalog.

• Meta-SQL %TruncateTable().

• DB2 UDB for Linux, UNIX, and Windows administration.

• Checklists and troubleshooting.

Note. PeopleSoft supports a number of versions of UNIX and Linux. Throughout this appendix, the word UNIX refers to all UNIX-like operating systems, including Linux.

For the sake of brevity, this appendix sometimes refers to DB2 UDB for Linux, UNIX, and Windows as DB2 LUW.

Understanding Administration on DB2 UDB for Linux, UNIX, and Windows

A PeopleSoft DB2 for UNIX database must be configured, monitored, and tuned to achieve optimum performance. In this section, we offer concepts, procedures, and tips to help you plan and implement the installation of PeopleSoft system and demonstration databases.

Recognizing that many DB2 LUW database administrators have DB2 z/OS backgrounds, this documentation includes references and comparisons to DB2 z/OS to help bridge understanding of concepts and procedures.

For UNIX systems, DB2 UDB publications can be accessed online from AIX using the system command db2help; this will bring up the browser and display the DB2 UDB information in HTML format.

Note. You need to install the supported browser and the DB2 UDB HTML information for the chosen language (locale) before running the db2help command. By default, the HTML files are copied from the CD-ROM to the hard disk in compressed form; you will have to decompress it using the db2insthtml command under the DB2 UDB-installed directory. In AIX, it is similar to /usr/lpp/db2_08_01/doc/db2insthtml. In Windows NT, the above steps are not needed because all the HTML files are uncompressed during normal DB2 UDB installation.

See Also

DB2 Administration Guide

DB2 System Monitor Guide and Reference

Instances

This section provides an overview of instances and discusses:

• SYSADM authority and security.

• Instances and Connectivity.

• Other considerations.

Understanding Instances

Operating System administrators (for UNIX logged in as root, for NT logged on as Administrator) may create one or more DB2 LUW instances to support their PeopleSoft environment. If you only have one computer to house both production and development database, PeopleSoft recommends that you create at least two DB2 UDB instances, one for your development database(s) and one for production. If you have more than one computer for the PeopleSoft environment use, PeopleSoft further recommends that the production instance be created on a separate machine for performance and security reasons.

A conceptual view of a DB2 LUW instance is shown in the graphic below. Each instance is a collection of databases sharing the same DB2 UDB engine and set of configuration tuning parameters called “database manager parameters.” These parameters control a variety of system resources such as communication buffer sizes, TCP/IP service name, and memory allocations. SYSADM authority controls all databases in an instance. DB2ADM controls the resources within a particular database.

One instance housing two databases

Each database within an instance is, to a large extent, self-contained, having its own set of system catalog tables, configuration tuning parameters, tablespaces, and log files.

Each PeopleSoft application is installed entirely into a single DB2 LUW database. To simplify administration, it is recommended that you created all the PeopleSoft nonproduction databases (such as Upgrade, Demo and Development) within one DB2 LUW instance. Setting up the production database in a separate instance by itself will provide you with greater flexibility in administration.

SYSADM Authority and Security

In DB2 LUW, SYSADM owns all databases in an instance. For this reason, to secure access to the production database, consider maintaining separate production and development instances. In this way, you can restrict SYSADM in production and be less restrictive in development.

If your site uses a single instance standard, you must restrict SYSADM authority—keeping in mind the additional burden this places on your DBA to support development and production environments.

Note. Administrators with DB2 UDB for z/OS experience should note the difference between DB2 z/OS and DB2 LUW in the way SYSADMs are created. In DB2 z/OS, an “Install SYSADM” is specified during DB2 UDB installation and other SYSADMs are granted using an SQL Grant SYSADM statement.

In DB2 LUW, the instance owner is the de facto “Install SYSADM” and other SYSADMs are created by assigning its group ID to the same primary group as the instance owner.

Instances and Connectivity

To create an instance, you can use the command db2icrt. Once the instance is created, you must assign different TCP/IP port numbers for the respective DB2 UDB instances.

To register the pair of TCP/IP ports, you edit the services file. Both the UNIX Server (/etc/services) and Windows (\windows\system32\drivers\etc\services) service files must specify the following:

db2dudb 50000/tcp#DB2 Client Application Enabler-Dev
db2pudb 50010/tcp#DB2 Client Application Enabler-Prod

Note. The names db2dudb and db2pudb are user-defined.

Each instance requires a SERVICENAME, which points to a unique entry in the service file.

On the DB2 LUW Server, update the Database Manager SVCENAME Configuration Parameter:

db2 update dbm cfg using SVCENAME db2dudb

If you created a second instance, you’d have to update the Database Manager SVCENAME Configuration Parameter on that instance using another service name:

db2 update dbm cfg using SVCENAME db2pudb

Other Considerations

The following are other considerations related to instances:

• For AIX, DB2 LUW Version 8 products are installed under the directory /usr/opt/db2_8_relnum where relnum is the release number. For Solaris, DB2 LUW products are installed in the /opt/IBM/db2/V8.relnum.

• sqllib is the root of the DB2 UDB directories created for each DB2 UDB instance. Enter echo $DB2INSTANCE to find the currently attached DB2 UDB instance.

• Use db2ilist to list all the instances configured for this machine.

• DB2 UDB system commands begin with the prefix db2 (for example, db2start and db2stop for starting and stopping DB2 UDB) and are entered at the command window.

• DB2 Command Line Processor (CLP) is a DB2 UDB interface for executing utilities, updating the system configuration, executing SQL, and for getting online help. CLP is the functional equivalent of SPUFI in the DB2 z/OS environment.

  • Type “db2 ?” at the command prompt for general CLP syntax.

  • Use db2 "? command” to get help on a particular CLP command.

  • If you need to execute several lengthy SQLs, you can create a text file which contains all the SQLs and invoke the db2 CLP with the –f switch, such as db2 –tvf job.txt.

• As an alternative to the Command Line Processor to issue SQL commands, IBM provides the Control Center, a graphical interface for database administrative tasks. The Control Center is a Java application or can be run as a Java applet within a browser. To start the Control Center, enter the command db2cc at a command prompt.

Configuration Parameters

This section discusses:

• Definition of configuration parameters.

• Useful configuration commands.

• Parameters overview.

Definition of Configuration Parameters

Database manager configuration parameters are those which apply for all databases managed by the current instance. You can update database manager configuration parameters using DB2 CLP or Control Center. New database manager configuration parameters take effect after DB2 UDB is stopped and restarted using DB2 UDB commands, db2stop and db2start, successively.

Most database configuration parameter changes take effect immediately. Some take effect only after all current users disconnect from a database, or after you forcefully disconnect them with the db2 force application all command, then execute the db2 terminate command to flush the database’s directory cache and remove the db2bp (backend process).

Useful Configuration Commands

Useful DB2 CLP commands for Database Manager configuration:

• get dbm cfg.

  Lists the current setting of database manager config parms.

• update dbm cfg using parm_name new_value.

  Updates the configuration parameter parm_name For example:

  update dbm cfg using numdb 4

Useful DB2 CLP commands for Database configuration:

• get db cfg for db_name.

  Lists the database config parms for db_name database.

• update db cfg for db_name using parm_name new_value.

  Updates the parm parm_name for database db_name. For example:

  db2 update db cfg for hr800dmo using  locktimeout  60 

Parameters Overview

Following is an overview of the more important configuration parameters with tips for tuning them. Fine tuning these parameters for optimal performance gain on your system requires careful benchmarking techniques.

• BUFFPAGE

  This database-level parameter controls the database buffer pool (cache) size. It is allocated for the respective database and shared among all the connected client applications. The rule for setting this parameter is: the more the better. Too high a value can cause the system to page (check with vmstat). Database system monitor can also give you hints about the "hit-ratio" and I/O elapsed times, that can help you set a proper value. This is a database level parm. The maximum BUFFPAGE for each database without using ESTORE is about 1.5 gigabytes. However, the total BUFFPAGEs allocated for all the databases running on the same computer should not exceed 75% of the amount of real memory available for the computer.

  Note. The BUFFPAGE is related to the SYSCAT.BUFFERPOOLS table entry. DB2 LUW has the capability to attach individual buffer pool for each tablespaces. For simplicity, the user can also attach a generic buffer pool named IBMDEFAULTBP for all the tablespaces use. To set up the size of the IBMDEFAULTBP to use the DBM configuration parameter, BUFFPAGE, the user can run the given script ALTRDB.SQL or the following command:
  
  db2 alter bufferpool ibmdefaultbp size -1

• SORTHEAP

  This database-level parameter is the capacity of in-memory sort. If a sort size exceeds this, the sort has to be written to a temp tables and merged. Make sure this and the SHEAPTHRES parameter (see next item) are large before any large index-creation, since it presorts the data. This parameter is also important for batch jobs, such as payroll, that do large sorts.

• SHEAPTHRES

  This is a database manager level parm. If all the sort-heaps in the system at any given time exceed this value, the available memory to any further sorts is reduced. Make sure it's greater than SORTHEAP*N, where N is the number of sorts you expect to occur at any given time.

• LOCKLIST

  This database-level parameter, along with MAXLOCKS, determines the maximum memory used for database locks. When this runs out (due to a large number of row locks acquired by transactions), lock escalation will occur to minimize the lock usage. Many Row locks will be escalated to one Table lock. The drawback of Table level lock is the reduction of concurrency among multiple applications, which need access to the same tables. Check the database event monitor for lock escalations.

• NUM_IOSERVERS

  This database-level parameter stores the Number of Processes/threads used for prefetch/parallel I/O, as well as for backup/restore. A good value is the number of physical disk drives uses to house the database plus 2.

• NUM_IOCLEANERS

  This database-level parameter stores the number of page-cleaners that do "write-behind" of dirty pages. The recommended value for this parameters is to match the number of CPUs inside the computer.

• RQRIOBLK

  This database-level parameter stores the size of the cache used for row blocking for remote, cursor-based applications. Rows are placed in this cache in anticipation of their use and retrieved from here for the next FETCH request.

• ASLHEAP

  Size of the cache used for row blocking for local applications.

• LOGPRIMARY

  This database-level parameter controls the number of DB2 UDB Log files that will be pre-allocated for regular database transaction logging use. Setting this number to a high value will minimize the need to allocate log files on demand, which will improve runtime performance.

  Note. You should separate log files on separate disks from the actual database data. You can use the database configuration parameter NEWLOGPATH to do this.

• DFT_QUERYOPT

  This database-level parameter determines the query optimization class used for the SQL compilation. The higher the level represents a more detailed study of the potential access path. Since PeopleSoft applications comprises dynamic SQLs only, setting this parameter to a high value will affect the compilation time for all SQLs. The recommended value for this parameter is the default value 5.

  Note. For SQL that is complex and can benefit from the use of higher optimization class, users can alter the optimization class for that SQL alone with the following SQL construct:
  
  SET CURRENT QUERY OPTIMIZATION = 7 [ complex SQL ] SET CURRENT QUERY OPTIMIZATION = 5

  Most of these memory-related parameters are allocated out of one of the many heaps. You may need to adjust the heap parameters accordingly.

  As a point of reference, PeopleSoft used the following database parameter values when running HRMS/Payroll benchmark tests processing a 100,000+ employee payroll: BUFFPAGE (125000); SORTHEAP (9000).

Tablespaces

This section discusses:

• DDL scripts.

• Using the PeopleSoft DMS tablespace DDL.

• DMS tablespaces: Cooked or raw.

• System catalog tablespace and other initial tablespaces.

• Capacity planning.

DDL Scripts

PeopleSoft provides DDL scripts to create a database, and set database manager and database tuning parameters. These scripts are on the PeopleSoft installation file server in the \scripts directory. Run the following scripts:

\scripts\createdb.sql -- creates DB2 LUW database.
.
\scripts\xxddldms.sql -- creates DMS (Data Managed Storage) tablespaces 

Where xx is the product identifier, such as HR for PeopleSoft HRMS or FS for PeopleSoft Financials and Supply Chain Management.

Using the PeopleSoft DMS Tablespace DDL

Create all tables and indexes in Data Managed Storage (DMS) tablespaces using PeopleSoft standard tablespace names as described in the installation guide. This storage option, as oppose to System Managed Storage (SMS), is appropriate for a database that you plan to change and grow. DMS is appropriate for a system test or production database.

Note. DROPPED TABLE RECOVERY feature is turned off in the xxddldms.sql script to avoid performance issue when dropping large number of tables. This feature can be turn on again with ALTER TABLESPACE command.

Here are some installation guidelines for manually creating your PeopleSoft database and tablespaces:

• On the database server, edit and run CREATEDB.SQL to create a database and default tablespace USERSPACE1. Note that this script assumes you will use Circular Logging; if archival logging is desired, you must make the necessary changes.

• On the database server, edit the DMS script /sql/hrddldms.sql. Instructions for editing this file are contained inside the file. This script creates all the PeopleSoft standard tablespaces.

• In Windows, use Data Mover to create and populate tables and indexes. In Data Mover, the command line below—if it exists in the Data Mover script—should either be removed or commented out (disabled) in the Data Mover script (the ‘;’ in position 1 disables the command):

  ; set space * as USERSPACE1 ;

  Note. Disabling the above command causes Data Mover to use PeopleSoft’s standard tablespace grouping strategy.

See Also

PeopleTools 8.49 Installation for DB2 UDB for UNIX/NT, “Preparing for Installation”

DMS Tablespaces: Cooked or Raw

DMS tablespaces may be created as either COOKED Files System or RAW Storage Devices. PeopleSoft provides DDL script /sql/hrddldms.sql to support DMS COOKED Files System.

PeopleSoft does not provide a tablespace script to support the Raw device, but you can create the RAW device with the proper Operating System command and the following DB2 UDB command:

CREATE TABLESPACE PSAPP MANAGED BY DATABASE USING
         (device '/dev/data1_lv' 20000)

In AIX, the COOKED File System refers to the Journal File System (JFS). In NT, the COOKED Files System refers to NTFS.

Note. There is a roughly 5-10% performance gain on RAW device over COOKED file system on tablespaces which are frequently being updated. However, it is generally much easier to administer a COOKED file system than a RAW device.

System Catalog Tablespace and Other Initial Tablespaces

For system test and production databases, PeopleSoft recommends that you consider tailoring the Create Database statement to override the DB2 LUW default tablespace definitions for SYSCATSPACE and TEMPSPACE1. An example of this is provided below, where CATALOG Tablespace defines the SYSCATSPACE and TEMPORARY Tablespace defines TEMPSPACE1:

CREATE DATABASE db2-database-name ON dir-name|drive COLLATE USING IDENTITY  \
		CATALOG TABLESPACE MANAGED BY SYSTEM USING 
			(‘/cat-dir-name‘ )
			EXTENTSIZE 16  PREFETCHSIZE 32
		TEMPORARY TABLESPACE MANAGED BY SYSTEM USING 
			(‘/temp-dir-name’)
         		EXTENTSIZE 8

Note. The above tablespaces may be defined as DMS tablespaces. If you omit these tablespace definitions, DB2 LUW will create these tablespaces in the file system directory denoted by dir-name.

Capacity Planning

If significant growth is expected as you conduct functional, system, and volume testing, you must plan to accommodate such growth when using DMS tablespaces. High growth tables will be created in tablespace xxLARGE, where xx represents an application product code, such as TLLARGE for Time and Labor, HRLARGE for HRMS, and so on.

Temporary Table Creation

For each temporary table you define, a base table structure and a number of its instances are created in the database as ordinary tables with ordinary table structures. The number of temporary table instances is determined by the value of the Temp Table Instances setting in PeopleTools options Utilities, Administration, PeopleTools Options added to the number of PeopleSoft Application Engine temporary tables. These temporary tables are used as work tables that hold transient data, and because they are real tables, they are permanent structures in the database and remain until an explicit drop table command is executed against them.

The nature of a temporary table means that the amount of data that each temporary work table holds varies significantly after each use. Therefore, when RUNSTATS are executed against them, there is a good chance that the statistics captured may not apply and will negatively influence the DB2 optimizer access path selection the next time you use the temporary work table.

Each record of the type Temporary Table is defined as a VOLATILE table in DB2 Version 8. This definition takes advantage of DB2 version 8 optimizer’s enhanced capability to formulate efficient index access paths for those tables that hold volatile data without relying on current table statistics. There is no VOLATILE table in DB2 Version 7. Additionally, because each temporary work table can only be assigned to a single process, the temporary work table is defined with the LOCKSIZE TABLE attribute to reduce the number of lock resources to be managed by DB2. Both the VOLATILE andLOCKSIZE TABLE attributes are be implemented using ALTER table statements.

Example

This example shows the additional DDL for ALTER statements generated for temporary tables that implements the VOLATILE and LOCKSIZE TABLE attributes.

CREATE TABLE PS_AC_CSTSEQ_TAO1 (
PROCESS_INSTANCE DECIMAL(10) NOT NULL,
   	DEPOSIT_BU CHAR(5) NOT NULL,
   	DEPOSIT_ID CHAR(15) NOT NULL,
   	PAYMENT_SEQ_NUM INTEGER NOT NULL,
   	BUSINESS_UNIT CHAR(5) NOT NULL,
   	CUST_ID CHAR(15) NOT NULL,
   	ID_SEQ_NUM INTEGER NOT NULL)
IN ARWORK INDEX IN ARWORKIDX
NOT LOGGED INITIALLY;

ALTER TABLE PS_AC_CSTSEQ_TAO1 VOLATILE;
ALTER TABLE PS_AC_CSTSEQ_TAO1 LOCKSIZE TABLE;

Client Database Catalog

When cataloging databases on a client machine, always use AUTHENTICATION clause and match the authentication algorithm with the one specified on the server by the database manager parameter (AUTHENTICATION). For example:

db2 catalog database database_name at node node_name AUTHENTICATION SERVER

This will avoid additional network traffic between client and server generated to resolve the authentication algorithm discrepancy.

Meta-SQL %TruncateTable()

In DB2 LUW, there's no SQL implementation of a Truncate Table command as the one found in Oracle. PeopleSoft has implemented a DB2 UDB utility to achieve the same effect as the Truncate Table command. This utility is available through the PeopleCode function %TruncateTable().

You might wish to disable this meta-SQL function because of the performance overhead incurred by bufferpool flushing. The effect of bufferpool flushing is that when you truncate large tables using the DB2 LUW API, the process can run much longer than a SQL “Delete From” clause. If you're experiencing this problem, a workaround is now available to convert %TruncateTable into a SQL “Delete From” clause.

To enable this workaround, there is a new flag setting in psprcs.cfg and psappsrv.cfg. If DbFlags is a bitmap value and if it contains the value of 2, then SQL is used rather than the DB2 UDB API to set the table to zero rows. The default value for DbFlags is zero.

The following is an example:

DbFlags=3 will enable the workaround, since 3=2+1.

DbFlags=1 doesn't enable the workaround and the Truncate is done similar to the Oracle's Truncate command.

Handling Errors

During the execution of the %TruncateTable() meta-SQL, error information is written to a disk file. The location of this disk file varies depending on which platform type is used.

Windows 2000

If you are running %TruncateTable on Windows 2000, then the directory name format is "%TEMP%\PS\DB2Truncate\PS_TruncateLogFile_pid_id.txt” , where pid_id is a variable depending on the process ID.

UNIX

If you are running %TruncateTable on UNIX, then the directory name format is “$PS_HOME/log/DB2Truncate/PS_TruncateLogFile_pid_id.txt”, where pid_id is a variable depending on the process ID.

In most cases, error files might be created under the following circumstances:

• %TruncateTable(Table_name), where Table_name doesn't exist.

• Internal errors in DB2 UDB.

DB2 UDB for Linux, UNIX, and Windows Administration

This section discusses:

• Updating statistics.

• Performing queries on a Windows client.

• Object restrictions.

• Administrative tools.

• Connectivity using ODBC/CLI.

Updating Statistics

We recommend that you update the database statistics on a periodic basis, typically weekly, to account for ongoing data changes. You do this by running runstats for tables and indexes in the database. This allows DB2 UDB’s cost based optimizer to generate efficient access plans for your stored and dynamic SQL statements. Using the SHRLEVEL CHANGE keywords together with the runstats command will enable the application to access the table while the statistics are being computed. An example of the command is shown below:

db2 runstats on table sysibm.systables with distribution and indexes all SHRLEVEL CHANGE

Runstats can be executed from the Database Control Center or DB2 CLP. Type “db2? runstats” for more information.

PeopleSoft provides an SQR program, RUNSTATS.SQR, to execute runstats on all your System and PeopleSoft tables. This script is located in the database server’s /SQR directory, and can be executed using the instructions found in installation guide. If desired, for efficiency’s sake, you can modify this script to limit running the runstats command against only those tables that experience high growth or high update. To identify such tables, modify RUNSTATS.SQR to join tables to SYSCAT.SYSTABLES and only select those tables belonging to tablespace xxLARGE.

Use explain to determine the access path chosen by the DB2 UDB optimizer. You can either use the Visual Explain utility or the db2expln tool to get access path information.

See Also

PeopleTools 8.49 Installation for DB2 UDB for UNIX/NT, “Creating a Database”

Performing Queries on a Windows Client

Query capability on Windows clients can be accomplished via multiple products:

• IBM’s DB2 Connect provides connectivity to a DB2 LUW database server (and other DB2 UDB Family servers) as well as SQL support.

• DB2 UDB “Command Window” or the graphical “Command Center”. SQL issued from the Command Center can be stored as Scripts and then be retrieved for later use via the Script Center.

• Third-party vendor tools such as Business Object (Forest and Trees), Information Advantage, and so forth.

• Use Lotus Approach 97 with DB2 LUW. Full GUI interface or most ODBC Query products, such as MS Query.

Object Restrictions

PeopleSoft applications contain many table and index objects. The number of objects in a DB2 LUW database does not pose a problem as it would in DB2 z/OS.

Unlike DB2 z/OS, which places a restriction on the number of database objects in a single DB2 z/OS database (not a subsystem), the number of objects in a DB2 LUW database is not of concern. The DB2 z/OS DBD (DataBase Descriptor), which limits the number of objects in a single database to 25% of the DBD memory allocation, has no exact counterpart in DB2 LUW.

Administrative Tools

Database Control Center is an easy to use, graphical interface that the DBA can use to configure database manager instances, databases, backup/recovery and media management. The Control Center is fully Java enabled and can be executed as a Java application or a Java applet via a standard browser.

Connectivity Using ODBC/CLI

The following is a conceptual view illustrating how a PeopleSoft client establishes connectivity with the DB2 LUW database server using ODBC and the Call Level Interface (CLI):

Connectivity using ODBC/CLI

In the PeopleTools Layer, a PeopleTools program (1) issues an SQL Connect request. PeopleTools program PSODBC.DLL (2) processes the request and formats the SQL request in an ODBC-compliant format and invokes the ODBC SQLConnect function (3).

In the Microsoft ODBC Layer, ODBC.DLL reads the registry entry for the ODBC (4) checking for the data source name (in our case, the database name). It finds this entry and loads the associated vendor driver (5), \WINDOWS\SYSTEM32\DB2CLI.DLL.

Note. ODBC.DLL determines by reading ODBC.INI which of several possible vendor ODBC-compliant drivers to load (e.g. Microsoft’s SQL Server (sqlsrv32.dll), IBM DB2 LUW, IBM DB2 z/OS, and so forth). In this case, it loads the IBM driver, DB2CLI.DLL.

In the IBM ODBC Driver Layer, (6) DB2CLI.DLL reads the WINDOWS Environment Variable (DB2PATH) to obtain the path for db2cae executables and (7) reads \db2 connect install dir\db2\sqldbdir\sqldbdir and \db2 connect install dir\db2\sqlnodir\sqlnodir to obtain database directory and node directory information, respectively, as it formats and submits its connect request to the database server (8).

IBM CLI (Call Level Interface) on the Client

IBM’s Call Level Interface (CLI) programs, unlike embedded SQL programs, are not precompiled and bound to a database and, therefore, do not produce PLANs or Packages.

PeopleSoft uses the Call Level Interface for online client connectivity (as well as database server batch processing). Both CLI interfaces operate in a similar manner, executing SQL statements one at a time, at runtime, caching prepared statements in a package cache buffer controlled by DB2 LUW. Again, no PLAN or Package is produced, as happens in a DB2 z/OS environment using embedded SQL.

Mapping Client and Server IP Addresses

In a two-tier architecture, processes on the database server displayed using the DB2 UDB list application command can be mapped back to particular clients using the Application ID field. The ability to map a server process to a client is important since all PeopleSoft client tasks are connected using the identical table owner ID.

To map a server process back to a client, issue a DB2 UDB list application on the database server, then convert the value in the Application ID to a client’s IP address. The Application ID is displayed in hexadecimal representation with each two characters representing a node in IP’s dotted notation format. In the example below, Auth ID PTDVL is connected from the client at x‘C65D379E’, or IP Address 198.93.55.158.

Note. In the preceding example, Auth ID shows PTDVL in uppercase, even though the table owner is defined in the respective operating system as a login ID in lowercase.

Checklists and Troubleshooting

This section discusses:

• Connectivity checklist.

• Diagnosing transaction hangs.

• DB2DIAG.LOG.

• ODBC Trace.

• db2trc.

• DB2 UDB Help facility.

Connectivity Checklist

This checklist is provided to help diagnose online connectivity problems.

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

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

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

• Is the DB2 LUW database on server 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 NT requires that the user ID which is cataloging databases and nodes be an NT Administrator (not just a user with administrative authorization). The Administrator’s user ID must not exceed 8 characters. Log in as the Windows NT administrator, go to Administrative Tools, User Manager, New User. Complete the New User Information, Click the Groups pushbutton, Add Administrators, OK.

Diagnosing Transaction Hangs

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. Wait a mninute to allow statistics to compile.

4. Issue the following statement(s):

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

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

6. Issue the following statement:

   db2 "get  snapshot  for  database on hr800dmo" > 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 parm 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)

Note. If an application is terminated using the above “force” command, the user will have to reconnect to the database server.

DB2DIAG.LOG

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.

ODBC Trace

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

db2trc

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 Help Facility

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"

Note. DB2 UDB requires a 4-digit error code suffix.