DB2 UDB for Linux, UNIX, and Windows Administration
This section discusses topics on administering the DB2 UDB for Linux, UNIX, and Windows.
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 SHRLEVELCHANGERunstats 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 the product documentation for PeopleSoft 9.2 Application Installation for DB2 for Linux, UNIX, and Windows for more information.
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.
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.
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.
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.
|
Technology Layer |
Description of Events |
|---|---|
|
PeopleTools |
A PeopleTools application, such as Application Designer, Issues a connect to database request (SQL Connect). The PeopleTools utility PSODBC.DLL processes the request and formats the SQL request in an ODBC-compliant format and invokes the ODBC SQLConnect function. Information passed includes:
|
|
Microsoft ODBC |
In the Microsoft ODBC Layer, ODBC.DLL reads the registry entry for the ODBC checking for the data source name (in this case, the database name). It finds this entry and loads the associated vendor driver, such as \WINDOWS\SYSTEM32\DB2CLI.DLL. Note: By reading ODBC.INI, ODBC.DLL determines which of several possible vendor ODBC-compliant drivers to load. In this case, it loads the IBM driver, DB2CLI.DLL. |
|
IBM ODBC Driver |
In the IBM ODBC Driver Layer, DB2CLI.DLL:
|
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 192.0.2.10.
|
Auth ID |
Application Name |
Application ID |
DB Name |
|---|---|---|---|
|
PTDVL |
PSIDE.EXE |
*TCPIP.C65D379E.960305015712 |
HR800DMO |
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.