Home
Blog
About
Database administration
Operating systems
Development
Links


Following require login:
ScratchPad



Locations of visitors to this page


DB2 SAP Workload

When SAP is installed the 'SAP' workload will be created.

Documentation for some of these variables is … sparse.
Where '????' appears I haven't so far been able to find any documentation on the parameter and/or the value it is being set to.

-- Request either reduced optimization features or rigid use of optimization features at the specified optimization level
DB2_REDUCED_OPTIMIZATION=4,INDEX,JOIN,NO_TQ_FACT,NO_HSJN_BUILD_FACT,STARJN_CARD_SKEW,NO_SORT_MGJOIN,CART OFF,CAP OFF

-- List prefetch is a special table access method that involves retrieving the qualifying RIDs from the index,
-- sorting them by page number and then prefetching the data pages
DB2_MINIMIZE_LISTPREFETCH=YES

-- Enable rewrite of an IN list predicate to a join
DB2_INLIST_TO_NLJN=YES

-- EXTEND=optimizer searches for opportunities to transform both "NOT IN" and "NOT EXISTS" subqueries into anti-joins
DB2_ANTIJOIN=EXTEND

-- Java is a unicode system and database might not be - this prevents any unwanted codepage conversions
DB2_IMPLICIT_UNICODE=YES

-- Allows, where possible, scans to defer or avoid row locking until the data is known to satisfy predicate evaluation.
-- With this variable enabled, predicate evaluation may occur on uncommitted data.
DB2_EVALUNCOMMITTED=YES

-- Limits the number of execution plans generated by the DB2 optimizer
DB2_INTERESTING_KEYS=YES

-- Allows statements using either Cursor Stability or Read Stability isolation levels to skip uncommitted inserted rows 
-- as if they had not been inserted.
-- Not compatible with tables that have pending rollout cleanup.
-- As a result, scanners might wait for locks on a RID only to discover that the RID is part of a rolled out block.
DB2_SKIPINSERTED=YES

-- Enables a performance enhancement known as "rollout" for deletions from MDC tables.
-- Rollout is a faster way of deleting rows in an MDC table, when entire cells (intersections
-- of dimension values) are deleted in a search DELETE statement.
-- The benefits are reduced logging and more efficient processing.
-- DEFER=Rollout with deferred index cleanup
-- MDC=Multi Dimensional Cluster
DB2_MDC_ROLLOUT=DEFER

-- Expected number of objects in a table space
-- 65532 = max possible value
DB2_OBJECT_TABLE_ENTRIES=65532

-- **Can't information about what this does**
DB2NOTIFYVERBOSE=YES

-- Enable use ofoptimisation profiles
-- http://www.ibm.com/developerworks/data/library/techarticle/dm-0612chen/index.html
DB2_OPTPROFILE=YES

-- Enables all users to store the cached values of a reoptimized SQL or XQuery statement in the EXPLAIN_PREDICATE table 
-- when the statement is explained.
-- When this variable is set to NO, only DBADM is allowed to save these values in the EXPLAIN_PREDICATE table
DB2_VIEW_REOPT_VALUES=YES

-- Megabytes that can be used by a query in all temporary table spaces
-- Queries using more than this will not fail, but you receive a warning that its performance may be suboptimal
DB2_OPT_MAX_TEMP_SIZE=10240

-- Used to resolve lock contention between the IMPORT with REPLACE command and the BACKUP ... ONLINE command.
DB2_TRUNCATE_REUSESTORAGE=IMPORT

-- Specifies the communication managers that are started when the database manager is started
DB2COMM=TCPIP

-- Undocumented registry variable which can be used to capture a copy of the section whenever a detailed activity record
-- is written to activity event monitor for SQL
-- Ref: http://www.idug.org/conferences/EU2008/data/EU08C05.pdf
DB2_DUMP_SECTION_ENV=YES

-- Disable progressive streaming
-- "Progressive streaming provides several performance benefits to both the server and the client.
-- However, they do come at the expensive of shortening the lifespan of the lob to the positioning of the cursor"
DB2_RESTRICT_DDF=YES

-- Enables the administrative task scheduler, tasks will be run
DB2_ATS_ENABLE=YES

-- Improves insert performace - speeds up the search for a block containing free space for large MDC cells
-- Ref: http://forums.sdn.sap.com/thread.jspa?threadID=1539241
DB2_TRUST_MDC_BLOCK_FULL_HINT=YES

-- ???? details
DB2COMPOPT=327685,704

-- Disable active db2fmp processes for each connection
--  where db2fmp processes are part of DB2 content management
DB2_AGENT_CACHING_FMP=OFF

-- Self Tuning memory Manager - ???? details
DB2STMM=APPLY_HEURISTICS:YES

-- Related to automatic collection of statistics - ???? details
DB2_ATM_CMD_LINE_ARGS=-include-manual-tables

-- ???? details
DB2_CREATE_INDEX_COLLECT_STATS=YES

-- Use named optimisation extensions
DB2_EXTENDED_OPTIMIZATION=NLJOIN_KEYCARD,IXOR

-- ???? details
DB2_BLOCKING_WITHHOLD_LOBLOCATOR=NO

-- Restoring to a new database, then SECADM, DBADM, DATAACCESS, and ACCESSCTRL authorities are granted to the user that 
-- issues the restore operation
-- Supports: Online+Offline tablespace restore, Split mirror backups, ACS snapshot backups
DB2_RESTORE_GRANT_ADMIN_AUTHORITIES=YES

Note that the SAP workload prevents the creation of public synonyms:

create public synonym dba_tables for systools.dba_tables

create public synonym dba_tables for systools.dba_tables
The SQL statement failed because of a non-severe system error. Subsequent SQL statements can be processed.
(Reason "Public synonym not supported in SAP workload".). SQLCODE=-901, SQLSTATE=58004, DRIVER=4.8.86

Failed queries => 1

Total execution time => 0 ms

It's not clear which of the above registry variables is responsible for this, no documentation …


Copyright HandyDBA 2012