Bookshelf Home | Contents | Index | Search | PDF

Implementing Siebel eBusiness Applications on DB2 UDB for OS/390 and z/OS > Preparing for Implementation on the DB2 Host > Setting Up the Subsystem >

DSNZPARM Parameters for Setting Up the Subsystem

---

For optimum operation of Siebel eBusiness Applications, use the recommended settings in Table 5 to configure the parameters within DSNZPARM.

The following parameter values are required by Siebel eBusiness Applications:

CACHEDYN=YES
CDSSRDEF=1
EXTSEC=YES
MXTBJOIN=128
PARTKEYU=YES
RETVLCFK=NO

Additional parameter settings in Table 5 are either recommendations or are set by the DBA. You can configure some parameters online, but other parameters require you to shut DB2 down to configure them. For information on which parameters can be updated online, refer to the IBM document Command Reference for DB2 Universal Database for OS/390 and z/OS (version 7).

Table 5.  Database Manager Configuration Parameters (DSNZPARM)

Parameter	Explanation	Recommended Setting
DSN6SPRM
CACHEDYN	Turns on dynamic statement caching. This is a required value for Siebel eBusiness Applications.	YES (required)
CDSSRDEF	Turns off parallelism for dynamic statements. This is a required value for Siebel eBusiness Applications.	1 (required)
CONTSTOR	Compresses storage on a regular basis. Set this value to YES if DBM1 storage is an issue.	YES
EVALUNC	Allows predicate evaluation on uncommitted data. This is a recommended Siebel eBusiness Applications value.	YES
IRLMRWT	Time in seconds before a time-out occurs. This is a recommended Siebel eBusiness Applications value.	300
MINSTOR	Compresses storage on a regular basis. Set this value to YES if DBM1 storage is an issue.	YES
MXTBJOIN	Sets the maximum number of tables in a join. The default (15) is not sufficient for Siebel eBusiness Applications. NOTE: Unlike other DSNZPARMs that cannot be updated through the installation panels, this parameter must be updated manually.	128 (required)
NPGTHRSH	Allows small tables to use indexes. This is a recommended Siebel eBusiness Applications value.	10
NUMLKUS	Number of locks per user. This value should be monitored and set by the DBA. If you experience persistent locking problems, consider setting the parameter to 0 (unlimited number of locks).	100000
OPTCCOS1	Allows multiple predicate optimizer enhancements.	ON
OPTSUBQ1	Allows non-correlated subquery optimizer enhancements.	YES
PARTKEYU	Allows update of partitioning keys. This is a required Siebel eBusiness Applications value.	YES (required)
RETVLCFK	Allows index-only access of varying-length characters. This value should be set to NO due to padding. This is a recommended Siebel eBusiness Applications value.	NO (required)
DSN6SYSP
CHKFREQ	Avoids frequent checkpoints in a high-update environment. DBAs should monitor and set this value for between 10 and 20 minutes.	500000 (or 15 when FREQUENCY TYPE = MINUTES)
CONDBAT	Maximum number of concurrent remote connections. The DBA should monitor and set this value.	10000
EXTSEC	Allows DB2 Connect to receive more complete error messages. Allows the changing of passwords from DB2 Connect. This is a required Siebel eBusiness Applications value.	YES (required)
MAXDBAT	Maximum number of database threads (DBAT). The DBA should monitor and set this value.	500
DSN6FAC
CMTSTAT	Allows a greater number of remote threads without affecting storage. Enables DDF thread pooling. This is a recommended Siebel eBusiness Applications value.	INACTIVE
IDTHTOIN	Number of seconds before an idle thread is canceled. Prevents long-running threads from holding resources. The DBA should monitor and set this value.	600
POOLINAC	Number of seconds an inactive thread remains in the DDF pool. The DBA should monitor and set this value.	120

Verifying DSNZPARM Settings

DB2 version 7 is shipped with a stored procedure call DSNWZP. This stored procedure returns all the DSNZPARM values for the DB2 on which it is executed. This may be used to compare recommended values with current settings.

NUMLKUS Considerations

If a "resource unavailable" error occurs because NUMLKUS has been exceeded while performing a Siebel operation, do one of the following:

Increase the NMLKLUS parameter value.
Issue COMMIT statements more frequently, and reduce the number of update and insert operations while using Siebel eBusiness Applications.

NOTE:  The NMLKLUS parameter value is important when running large EIM batches and during the use of Siebel Remote for the initial database extract. If this value is too small, EIM runs or the database extract can fail.

Should EIM fail due to NUMLKUS being exceeded, do one of the following:

Reduce the size of the batch.
Increase the value of NUMLKUS.

Siebel Dynamic SQL

Traditional applications are typically based on static, hard-coded SQL. However, Siebel eBusiness Applications generate dynamic SQL statements at run time, using the Siebel Application Object Manager and the program configuration in use by the customer. In this way, SQL statements can be as individual as the deployments in which they occur and, in some cases, dynamic SQL might become so complex, that it pushes the limits of DB2. For example, joins with 30-40 tables are not uncommon.

To reduce the complexity of generated SQL (such as the number of joins, the number of columns in SELECT statements, or the number of tables in FROM statements), consider eliminating columns from the standard Siebel applets that you do not require for your business needs, especially in the most frequently used screens and views. You should also keep this consideration in mind, when you customize the Siebel application by creating new objects.

DB2 Dynamic Statement Caching

Siebel eBusiness Applications take advantage of DB2 global statement caching. This allows dynamic SQL statements that are reexecuted to bypass the PREPARE phase after the first execution. Dynamic statement caching allows dynamic statements to be prepared once, stored on a global statement cache, then reused many times. The Siebel application is also designed to maximize the reuse of cached statement caching.

You enabled global statement caching when you set the CACHEDYN parameter to YES in macro DSN6SPRM when generating DSNZPARMs. Another method of enabling global statement caching is to set Dynamic Cache SQL to YES when installing DB2.

Because DB2 saves the cached statement in the Environmental Descriptor Manager (EDM) pool, proper sizing of this pool is very important. For guidance on sizing the EDM pool, see your IBM documentation.

NOTE:  The EDM pool should be monitored and adjusted based on your workload.

Other system parameters associated with dynamic statement caching are illustrated in Table 6.

Table 6.  System Parameters Associated with Dynamic Statement Caching

Parameter	Value	Description
EDMDSPAC	10,000	Overrides size of data space for EDM pool during statement caching.
EDMPOOL	32	EDM pool storage size.
CONTSTOR	YES	Contracts the EDM pool after 50 commits or >2 MB in use by one thread.

How to Invalidate Cached Statements

After DB2 prepares a SQL statement and places it in the global statement cache, it gets reused. If a customer is performing SQL tuning and it is desirable to force DB2 to prepare and reoptimize a cached statement, the global SQL statement cache must be invalidated.

To invalidate an entry in the global statement cache for a specific SQL statement, one of the tables referenced by the SQL statement or the table catalog statistics must be altered in some way.

Methods of invalidating a dynamically cached statement include the following:

Use the RUNSTATS utility to automatically invalidate cached statements that refer to objects against which RUNSTATS was executed. The RUNSTATS utility can be run on any table or table space referenced in the SQL statement.
Use the DB2 command STOP OBJECT or the SQL DROP, ALTER, or REVOKE statements (for example, REVOKE ALL FROM PUBLIC) on any object related to the plan to invalidate both global and local cache. While some ALTER statements might require an object to be stopped, others might not; for example, STOP OBJECT is not required for DROP or REVOKE.
Use the command ALTER TABLE tablename AUDIT NONE to purge statements that reference that table from the cache.
Use a CREATE INDEX statement on tables used in the DB2 access path.
Stop the DB2 subsystem and restart it.

Bookshelf Home | Contents | Index | Search | PDF