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
MXTBJOIN=128
CDSSRDEF=1
EXTSEC=YES
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).
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 toYES
in macro DSN6SPRM when generating DSNZPARMs. Another method of enabling global statement caching is to set Dynamic Cache SQL toYES
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.
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 |
Implementing Siebel eBusiness Applications on DB2 UDB for OS/390 and z/OS Published: 18 April 2003 |