{"id":991,"date":"2011-02-20T10:02:22","date_gmt":"2011-02-20T04:02:22","guid":{"rendered":"http:\/\/techsatwork.com\/blog\/?p=991"},"modified":"2016-01-07T11:31:07","modified_gmt":"2016-01-07T05:31:07","slug":"optimization-profiles-in-db2-udb","status":"publish","type":"post","link":"https:\/\/techsatwork.com\/?p=991","title":{"rendered":"Optimization profiles in DB2 UDB"},"content":{"rendered":"

How many times have we spend hours trying to figure why DB2 is not choosing a particular index and access path ? Probably many ! \u00c2\u00a0 \u00c2\u00a0DB2 is one of the best cost based optimizer out there , but yet it some rare cases DB2 refuses to choose the optimal path. As a DBA we go through utilities like db2expln, runstats and \u00c2\u00a0db2advis to tune SQLs and find the best access path, but if none of them work you can resort to optimization profiles to force DB2 to use a particular index.\u00c2\u00a0Optimization profiles are similar to the “hints” in other RDBMS engines like Oracle and SQL Server.<\/p>\n

Optimization profiles in DB2 UDB is an XML document and is then imported into the SYSTOOLS.OPT_PROFILE table. You activate a profile by executing\u00c2\u00a0SET CURRENT OPTIMIZATION PROFIL<\/span>E<\/span> command \u00c2\u00a0when binding a package or executing a SQL.<\/p>\n

SYSTOOLS.OPT_PROFILE \u00c2\u00a0can be created either \u00c2\u00a0by running the sysinstallobjects procedure (recommended) or by creating the table directly :<\/p>\n

db2 “call sysinstallobjects(‘opt_profiles’, ‘c’, ‘toolstbsp’, ”)”
\nor
\ncreate table systools.opt_profile (
\nschema\u00c2\u00a0 varchar(128) not null,
\nname\u00c2\u00a0 \u00c2\u00a0 varchar(128) not null,
\nprofile blob (2m)\u00c2\u00a0 \u00c2\u00a0 not null,
\nprimary key (schema, name)
\n) in toolstbsp;<\/span><\/p>\n

Lets take a simple example where DB2 chose a particular access path, but you as a DBA know that a different access path is better , how can you force DB2 optimizer to chose a particular index :<\/p>\n

First ensure you have the SYSTOOLS.OPT_PROFILE table created, if not create the table using the method I wrote above. \u00c2\u00a0Lets explain the following sql that can be run against the SAMPLE database:<\/span><\/span><\/p>\n

SELECT FIRSTNME, LASTNAME, JOB, WORKDEPT, SALARY
\nFROM EMPLOYEE\u00c2\u00a0WHERE JOB IN (‘CLERK’, ‘SALESREP’) AND\u00c2\u00a0WORKDEPT=’A00’\u00c2\u00a0ORDER BY JOB, SALARY;<\/span><\/p>\n

Below is the explain :
\n\"\"<\/a><\/p>\n

For instance, if you want to force optimizer to choose another index – XEMP2, \u00c2\u00a0to do so create the following optimization profile in a file , lets say profile1.xml :<\/p>\n

?xml version=”1.0″ encoding=”UTF-8″?><\/span><\/span><\/span><\/span><\/p>\n

<OPTPROFILE VERSION=”9.5.1″>
\n<STMTPROFILE ID=”Example 1″><\/span><\/span><\/span><\/span><\/p>\n

<STMTKEY>
\n<![CDATA[SELECT FIRSTNME, LASTNAME, JOB, WORKDEPT, SALARY
\nFROM EMPLOYEE
\nWHERE JOB IN (‘CLERK’, ‘SALESREP’) AND\u00c2\u00a0WORKDEPT=’A00′
\nORDER BY JOB, SALARY]]>
\n<\/STMTKEY><\/span><\/span><\/span><\/span><\/p>\n

<OPTGUIDELINES>
\n<INLIST2JOIN OPTION=”DISABLE” TABLE=”EMPLOYEE” COLUMN=”JOB”\/>
\n<IXOR TABLE=”EMPLOYEE” INDEX=”XEMP2″\/>
\n<\/OPTGUIDELINES><\/span><\/span><\/span><\/span><\/p>\n

<\/STMTPROFILE>
\n<\/OPTPROFILE><\/span><\/span><\/span><\/span><\/p>\n

No<\/span><\/span><\/span>w<\/span> create a file called profile1.del with the following lines :
\n “DB2INST2″,”PROF1″,”profile1.xml”<\/span><\/p>\n

Now IMPORT the profile1.del file to systools.opt_profile table:<\/p>\n

IMPORT FROM profile1.del OF DEL\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 MODIFIED BY LOBSINFILE\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 INSERT INTO SYSTOOLS.OPT_PROFILE<\/span><\/p>\n

Its time to see how the optimizer reacts to the profile . Execute the following sqls :<\/p>\n

SET CURRENT OPTIMIZATION PROFILE=”PROF1″
\nSET CURRENT EXPLAIN MODE EXPLAIN
\nSELECT FIRSTNME, LASTNAME, JOB, WORKDEPT, SALARYFROM EMPLOYEEWHERE JOB IN (‘CLERK’, ‘SALESREP’) ANDWORKDEPT=’A00’ORDER BY JOB, SALARY
\nSET CURRENT EXPLAIN MODE NO
\ndb2exfmt -d SAMPLE -1 -g -o exfmt_query1.out<\/span><\/p>\n

The file exfmt_query1.out should contain a query access plan similar to the following :\u00c2\u00a0\"\"<\/a><\/span><\/p>\n

Note the index that the optimizer chose is XEMP2. \u00c2\u00a0You can add other profiles to the opt_profile table for different set of workload or add more STMTKEY to the same xml document. \u00c2\u00a0 You can find more about optimization profile <\/span>here<\/span><\/a> . <\/span><\/span>Using optimization profile should be your last resort, like I said earlier db2 optimizer is extremely wise and usually choose the optimal access path.<\/p>\n

Try out the optimization profile and let me know what your experience.<\/span><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

How many times have we spend hours trying to figure why DB2 is not choosing a particular index and access path ? Probably many ! \u00c2\u00a0 \u00c2\u00a0DB2 is one of the best cost based optimizer out there , but yet it some rare cases DB2 refuses to choose the optimal path. As a DBA we […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-container-style":"default","site-container-layout":"default","site-sidebar-layout":"default","site-transparent-header":"default","disable-article-header":"default","disable-site-header":"default","disable-site-footer":"default","disable-content-area-spacing":"default","footnotes":""},"categories":[303,3,358],"tags":[572,559,557,571,553,220,570,76,573,561,560,556,562,563,554,564,555,75,565,558,29],"class_list":["post-991","post","type-post","status-publish","format-standard","hentry","category-database","category-db2","category-how-to","tag-access-path","tag-current-optimization-profile","tag-db2-hints","tag-db2-index-usage","tag-db2-optimizer","tag-db2-performance","tag-db2-tuning","tag-explain","tag-explain-plan","tag-force-access-path","tag-force-index-usage","tag-hints","tag-opt-hint","tag-optguidelines","tag-optimization-profile","tag-optprofile","tag-opt_profile","tag-performance","tag-stmtprofile","tag-sysinstallobjects","tag-tuning"],"_links":{"self":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/991","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=991"}],"version-history":[{"count":24,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/991\/revisions"}],"predecessor-version":[{"id":1022,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/991\/revisions\/1022"}],"wp:attachment":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=991"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=991"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=991"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}