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 …