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. Oracle 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 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 displays 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 need to decompress it using the db2insthtml command under the DB2 UDB-installed directory. On AIX, it is similar to /usr/lpp/db2_xx_01/doc/db2insthtml. On Windows, 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 Windows 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.

The following graphic shows a conceptual view of a DB2 LUW instance. 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.

DB2 LUW instance housing a collection of databases sharing the same engine and database manager parameters yet with each database having its own DB2ADM, specific database configuration, catalog tables, and log files

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 create all the PeopleSoft non-production 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\systemXX\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:

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

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 HCM 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.51 Installation for DB2 UDB for Linux, UNIX, and Windows, “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)

On AIX, the COOKED File System refers to the Journal File System (JFS). On Windows, 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.

Optimizing Table Space Capacity With Auto-Resize

DB2 LUW offers the “AUTORESIZE” option for use with DMS table spaces so that the database system can automatically manage the allocation of additional space when a previous limit has been reached. PeopleTools supports the use of AUTORESIZE.

The AUTORESIZE option enables database administrators to create table spaces with an ample yet reasonable initial size and then specify the increment by which the system enlarges the table space when necessary. AUTORESIZE is transparent to any application connected to the database, and because it automatically manages table space size based on the specified configuration settings, database administrators do not need to enlarge table spaces manually on an ad hoc basis.

Note. AUTORESIZE is only available for table spaces within database-managed spaces (DMS). However, PeopleTools, as a standard, only creates DMS table spaces, so this restriction does not apply within the PeopleTools context. PeopleTools does not create system-managed spaces (SMS).

Enabling AUTORESIZE

The SQL parameters for enabling and configuring auto-resize are:

How you enable AUTORESIZE depends on the status of your current implementation, as in, whether you are creating or upgrading a database, or just working with an existing database.

If you are creating a new PeopleSoft database or performing an upgrade on a PeopleSoft database, you use the Database Creation Wizard and the delivered DDL scripts to enable auto-resize as per the instructions in your PeopleSoft upgrade and/or installation documentation.

The DDL of the provided scripts and those created using the Database Creation Wizard is similar to the following CREATE TABLESPACE example:

CREATE TABLESPACE PTTLRG MANAGED BY DATABASE USING
(
 FILE '/data1/psdb2/ptdbname/PTTLRG.DBF'  10 M
) EXTENTSIZE 16 PREFETCHSIZE 48 DROPPED TABLE RECOVERY OFF
AUTORESIZE YES INCREASESIZE 10 M MAXSIZE NONE ;

In this example, the system creates the table space PTTLRG with an initial size of 10 Mb, with the AUTORESIZE option on, specifying that the database system will increase the table space size by 10 Mb each time a limit is reached.

Note. If you have already determined the appropriate initial size, increase size, and maximum size for table spaces at your site, edit the delivered scripts to reflect those values.

If you are working with an existing PeopleSoft database and not performing any database creation or upgrade tasks on the entire database, you can submit a SQL ALTER command to update any existing table spaces for which you want to enable this feature.

The ALTER syntax is:

ALTER TABLESPACE <name> AUTORESIZE YES INCREASESIZE <size> MAXSIZE <size> ;

Disabling Auto-Resize

By default, AUTORESIZE is enabled for all new and upgraded PeopleSoft databases. Because of its convenience and because the PeopleSoft system assumes AUTORESIZE is on, disabling AUTORESIZEis not recommended.

To disable the auto-resize option for a table space, issue a SQL ALTER statement using the following syntax:

ALTER TABLESPACE <name> AUTORESIZE NO ;

Determining Increase Size

To determine the appropriate increase size value for your table spaces, Oracle recommends first categorizing your table spaces into the following categories: small, medium, and large. These categories reflect the growth potential of the table space. For example, a small table space is one that is not expected to increase at the same rate or reach the same size as a large table space.

The following table provides some suggested increase sizes by category.

Confirming that AUTORESIZE is Enabled

To confirm that AUTORESIZEis enabled and working as desired, use the DB2 table space monitor snapshot output. For example, assume you create the table space PSHRDATA with AUTORESIZEon. PSHRDATA, historically, is a table space that typically reached maximum space if not properly monitored and adjusted by database administrators. In this example, PSHRDATA is created with an initial space allocation of 1500 8k pages, using the following SQL:

CREATE TABLESPACE  PSHRDATA  PAGESIZE 8K MANAGED BY DATABASE USING
(
 FILE '/data1/psdb2/ptdbname/PTTREEIDX.DBF'  1500
) EXTENTSIZE 16 PREFETCHSIZE 48 BUFFERPOOL PSUBUFPOOL DROPPED TABLE RECOVERY OFF
AUTORESIZE YES INCREASESIZE 10 M MAXSIZE NONE ;

After a period of time in which you can assume large amounts of transactional data has been inserted into your database, connect to the database and show the detail report on the table spaces. Use the list tablespaces show detail command.

db2 connect to <db-name>
db2 list tablespaces show detail

A section of the output would appear similar to the following:

......
Tablespace ID                        = 45
 Name                                 = PSHRDATA
 Type                                 = Database managed space
 Contents                             = Any data
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 2768
 Useable pages                        = 2752
 Used pages                           = 2000
 Free pages                           = 752
 High water mark (pages)              = 2000
 Page size (bytes)                    = 8192
 Extent size (pages)                  = 16
 Prefetch size (pages)                = 48
 Number of containers                 = 1
......

The current footprint (Total Pages) of PSHRDATA is now 2768 8k pages, well over the initial allocation. This clearly indicates that DB2 detected a request for additional free pages beyond the initial space allocation for PSHRDATA and automatically extended the table space.

Monitoring Table Space Size Allocation

While the AUTORESIZEoption greatly reduces the amount of manual intervention, database administrators should continue to monitor the size and growth of the following elements of your database to ensure that you are optimizing space usage:

• File system

• Table space container

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 (beginning with version 8). This definition takes advantage of the DB2 optimizer’s enhanced capability to formulate efficient index access paths for those tables that hold volatile data, without relying on current table statistics. 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: ALTER Statement Using VOLATILE and LOCKSIZE TABLE

This example shows the additional DDL for ALTER statements generated for temporary tables that implements the VOLATILE andLOCKSIZE 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, such as the one found in Oracle. PeopleTools 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 negative effect of bufferpool flushing comes 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, you can convert %TruncateTable into a SQL DELETE FROM clause.

To enable this conversion there is a setting (DbFlags) in PSPRCS.CFG and PSAPPSRV.CFG (or PSADMIN). 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=2 will enable the workaround.

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

If you are running %TruncateTable on Windows, 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 SHRLEVELCHANGE

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.51 Installation for DB2 UDB for Linux, UNIX, and Windows, “Creating a Database”

Performing Queries on a Windows Workstation

Query capability on Windows workstations can be accomplished using 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 retrieved for later use from the Script Center.

• Third-party vendor tools such as Business Objects, Information Advantage, and so forth.

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 Java applet using a standard browser.

Connectivity Using ODBC/CLI

A PeopleTools development environment (two-tier client) establishes connectivity through these technology layers:

• PeopleTools layer.

• Microsoft ODBC layer.

• IBM ODBC Driver layer.

The following table describes the events occurring within each layer during a connectivity request.

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

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

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

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.

Monitoring Module Information

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.