{"id":255,"date":"2022-01-20T09:03:13","date_gmt":"2022-01-20T08:03:13","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/2022\/01\/20\/getting-explain-plans-out-of-db2\/"},"modified":"2022-04-06T08:26:48","modified_gmt":"2022-04-06T06:26:48","slug":"getting-explain-plans-out-of-db2","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/getting-explain-plans-out-of-db2\/","title":{"rendered":"Getting explain plans out of Db2"},"content":{"rendered":"<p>The <a href=\"https:\/\/www.dbi-services.com\/blog\/setting-up-ibm-db2-on-linux\/\" target=\"_blank\" rel=\"noopener\">last post about Db2<\/a> was about getting it up and running. As the issue we had to solve was about performance, getting an explain plan for the problematic statement(s) was the obvious step to do. In PostgreSQL you can just use <a href=\"https:\/\/www.postgresql.org\/docs\/current\/sql-explain.html\" target=\"_blank\" rel=\"noopener\">EXPLAIN<\/a> for that, and you&#8217;re done. In Db2 the procedure is a bit different, but once you know the tools, it is quite easy as well.<\/p>\n<p><!--more--><\/p>\n<p>Let&#8217;s start by creating a new database:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1; highlight: [1,2,23]\">\ndb2@sles15db2:~&gt; . sqllib\/db2profile \ndb2@sles15db2:~&gt; db2\n(c) Copyright IBM Corporation 1993,2007\nCommand Line Processor for DB2 Client 11.5.6.0\n\nYou can issue database manager commands and SQL statements from the command \nprompt. For example:\n    db2 =&gt; connect to sample\n    db2 =&gt; bind sample.bnd\n\nFor general help, type: ?.\nFor command help, type: ? command, where command can be\nthe first few keywords of a database manager command. For example:\n ? CATALOG DATABASE for help on the CATALOG DATABASE command\n ? CATALOG          for help on all of the CATALOG commands.\n\nTo exit db2 interactive mode, type QUIT at the command prompt. Outside \ninteractive mode, all commands must be prefixed with 'db2'.\nTo list the current command option settings, type LIST COMMAND OPTIONS.\n\nFor more detailed help, refer to the Online Reference Manual.\n\ndb2 =&gt; create database db1\nDB20000I  The CREATE DATABASE command completed successfully.\n<\/pre>\n<p>As we need something to explain, we&#8217;ll copy the &#8220;tables&#8221; catalog table without any data:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1; highlight: [1,8,10]\">\ndb2 =&gt; connect to db1\n\n   Database Connection Information\n\n Database server        = DB2\/LINUXX8664 11.5.6.0\n SQL authorization ID   = DB2\n Local database alias   = DB1\ndb2 =&gt; create table t like syscat.tables\nDB20000I  The SQL command completed successfully.\ndb2 =&gt; select count(*) from t;\n\n1          \n-----------\n          0\n\n  1 record(s) selected.\n<\/pre>\n<p>Generate some data:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\ndb2 =&gt; insert into t select * from syscat.tables\nDB20000I  The SQL command completed successfully.\ndb2 =&gt; insert into t select * from syscat.tables\nDB20000I  The SQL command completed successfully.\ndb2 =&gt; insert into t select * from t\nDB20000I  The SQL command completed successfully.\ndb2 =&gt; insert into t select * from t\nDB20000I  The SQL command completed successfully.\ndb2 =&gt; insert into t select * from t\nDB20000I  The SQL command completed successfully.\ndb2 =&gt; insert into t select * from t\nDB20000I  The SQL command completed successfully.\ndb2 =&gt; insert into t select * from t\nDB20000I  The SQL command completed successfully.\ndb2 =&gt; select count(*) from t\n\n1          \n-----------\n      14016\n\n  1 record(s) selected.\n<\/pre>\n<p>The structure of the table is like this:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\ndb2 =&gt; describe table t\n\n                                Data type                     Column\nColumn name                     schema    Data type name      Length     Scale Nulls\n------------------------------- --------- ------------------- ---------- ----- ------\nTABSCHEMA                       SYSIBM    VARCHAR                    128     0 No    \nTABNAME                         SYSIBM    VARCHAR                    128     0 No    \nOWNER                           SYSIBM    VARCHAR                    128     0 No    \nOWNERTYPE                       SYSIBM    CHARACTER                    1     0 No    \nTYPE                            SYSIBM    CHARACTER                    1     0 No    \nSTATUS                          SYSIBM    CHARACTER                    1     0 No    \nBASE_TABSCHEMA                  SYSIBM    VARCHAR                    128     0 Yes   \nBASE_TABNAME                    SYSIBM    VARCHAR                    128     0 Yes   \nROWTYPESCHEMA                   SYSIBM    VARCHAR                    128     0 Yes   \nROWTYPENAME                     SYSIBM    VARCHAR                    128     0 Yes   \nCREATE_TIME                     SYSIBM    TIMESTAMP                   10     6 No    \nALTER_TIME                      SYSIBM    TIMESTAMP                   10     6 No    \nINVALIDATE_TIME                 SYSIBM    TIMESTAMP                   10     6 No    \nSTATS_TIME                      SYSIBM    TIMESTAMP                   10     6 Yes   \nCOLCOUNT                        SYSIBM    SMALLINT                     2     0 No    \nTABLEID                         SYSIBM    SMALLINT                     2     0 No    \nTBSPACEID                       SYSIBM    SMALLINT                     2     0 No    \nCARD                            SYSIBM    BIGINT                       8     0 No    \nNPAGES                          SYSIBM    BIGINT                       8     0 No    \nMPAGES                          SYSIBM    BIGINT                       8     0 No    \nFPAGES                          SYSIBM    BIGINT                       8     0 No    \nNPARTITIONS                     SYSIBM    BIGINT                       8     0 No    \nNFILES                          SYSIBM    BIGINT                       8     0 No    \nTABLESIZE                       SYSIBM    BIGINT                       8     0 No    \nOVERFLOW                        SYSIBM    BIGINT                       8     0 No    \nTBSPACE                         SYSIBM    VARCHAR                    128     0 Yes   \nINDEX_TBSPACE                   SYSIBM    VARCHAR                    128     0 Yes   \nLONG_TBSPACE                    SYSIBM    VARCHAR                    128     0 Yes   \nPARENTS                         SYSIBM    SMALLINT                     2     0 Yes   \nCHILDREN                        SYSIBM    SMALLINT                     2     0 Yes   \nSELFREFS                        SYSIBM    SMALLINT                     2     0 Yes   \nKEYCOLUMNS                      SYSIBM    SMALLINT                     2     0 Yes   \nKEYINDEXID                      SYSIBM    SMALLINT                     2     0 Yes   \nKEYUNIQUE                       SYSIBM    SMALLINT                     2     0 No    \nCHECKCOUNT                      SYSIBM    SMALLINT                     2     0 No    \nDATACAPTURE                     SYSIBM    CHARACTER                    1     0 No    \nCONST_CHECKED                   SYSIBM    CHARACTER                   32     0 No    \nPMAP_ID                         SYSIBM    SMALLINT                     2     0 Yes   \nPARTITION_MODE                  SYSIBM    CHARACTER                    1     0 No    \nLOG_ATTRIBUTE                   SYSIBM    CHARACTER                    1     0 No    \nPCTFREE                         SYSIBM    SMALLINT                     2     0 No    \nAPPEND_MODE                     SYSIBM    CHARACTER                    1     0 No    \nREFRESH                         SYSIBM    CHARACTER                    1     0 No    \nREFRESH_TIME                    SYSIBM    TIMESTAMP                   10     6 Yes   \nLOCKSIZE                        SYSIBM    CHARACTER                    1     0 No    \nVOLATILE                        SYSIBM    CHARACTER                    1     0 No    \nROW_FORMAT                      SYSIBM    CHARACTER                    1     0 No    \nPROPERTY                        SYSIBM    VARCHAR                     32     0 No    \nSTATISTICS_PROFILE              SYSIBM    CLOB                  10485760     0 Yes   \nCOMPRESSION                     SYSIBM    CHARACTER                    1     0 No    \nROWCOMPMODE                     SYSIBM    CHARACTER                    1     0 No    \nACCESS_MODE                     SYSIBM    CHARACTER                    1     0 No    \nCLUSTERED                       SYSIBM    CHARACTER                    1     0 Yes   \nACTIVE_BLOCKS                   SYSIBM    BIGINT                       8     0 No    \nDROPRULE                        SYSIBM    CHARACTER                    1     0 No    \nMAXFREESPACESEARCH              SYSIBM    SMALLINT                     2     0 No    \nAVGCOMPRESSEDROWSIZE            SYSIBM    SMALLINT                     2     0 No    \nAVGROWCOMPRESSIONRATIO          SYSIBM    REAL                         4     0 No    \nAVGROWSIZE                      SYSIBM    SMALLINT                     2     0 No    \nPCTROWSCOMPRESSED               SYSIBM    REAL                         4     0 No    \nLOGINDEXBUILD                   SYSIBM    VARCHAR                      3     0 Yes   \nCODEPAGE                        SYSIBM    SMALLINT                     2     0 No    \nCOLLATIONSCHEMA                 SYSIBM    VARCHAR                    128     0 No    \nCOLLATIONNAME                   SYSIBM    VARCHAR                    128     0 Yes   \nCOLLATIONSCHEMA_ORDERBY         SYSIBM    VARCHAR                    128     0 No    \nCOLLATIONNAME_ORDERBY           SYSIBM    VARCHAR                    128     0 Yes   \nENCODING_SCHEME                 SYSIBM    CHARACTER                    1     0 No    \nPCTPAGESSAVED                   SYSIBM    SMALLINT                     2     0 No    \nLAST_REGEN_TIME                 SYSIBM    TIMESTAMP                   10     6 Yes   \nSECPOLICYID                     SYSIBM    INTEGER                      4     0 No    \nPROTECTIONGRANULARITY           SYSIBM    CHARACTER                    1     0 No    \nAUDITPOLICYID                   SYSIBM    INTEGER                      4     0 Yes   \nAUDITPOLICYNAME                 SYSIBM    VARCHAR                    128     0 Yes   \nAUDITEXCEPTIONENABLED           SYSIBM    CHARACTER                    1     0 No    \nDEFINER                         SYSIBM    VARCHAR                    128     0 No    \nONCOMMIT                        SYSIBM    CHARACTER                    1     0 No    \nLOGGED                          SYSIBM    CHARACTER                    1     0 No    \nONROLLBACK                      SYSIBM    CHARACTER                    1     0 No    \nLASTUSED                        SYSIBM    DATE                         4     0 No    \nCONTROL                         SYSIBM    CHARACTER                    1     0 No    \nTEMPORALTYPE                    SYSIBM    CHARACTER                    1     0 No    \nTABLEORG                        SYSIBM    CHARACTER                    1     0 No    \nEXTENDED_ROW_SIZE               SYSIBM    CHARACTER                    1     0 No    \nPCTEXTENDEDROWS                 SYSIBM    REAL                         4     0 No    \nREMARKS                         SYSIBM    VARCHAR                    254     0 Yes   \n\n  85 record(s) selected.\n<\/pre>\n<p>If we create an index on the &#8220;tabname&#8221; column and later filter on that column we should get an index access:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\ndb2 =&gt; create index i on t(tabname)\nDB20000I  The SQL command completed successfully.\ndb2 =&gt; quit\nDB20000I  The QUIT command completed successfully.\n<\/pre>\n<p>You have several options to create explain plans in Db2 and one of the options is to use the <a href=\"https:\/\/www.ibm.com\/docs\/en\/db2\/11.5?topic=registers-current-explain-mode\" target=\"_blank\" rel=\"noopener\">CURRENT EXPLAIN MODE special register<\/a>, so let&#8217;s try that:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\ndb2@sles15db2:~&gt; echo \"select count(*) from t where tabname='t';\" &gt; 1.sql\ndb2@sles15db2:~&gt; db2 set current explain mode explain\nDB20000I  The SQL command completed successfully.\ndb2@sles15db2:~&gt; db2 -tvf  1.sql     \nselect count(*) from t where tabname='t'\nSQL0219N  The required Explain table \"DB2.EXPLAIN_INSTANCE\" does not exist.  \nSQLSTATE=42704\n<\/pre>\n<p>The error message is pretty clear, somehow we need to create the explain tables. There are <a href=\"https:\/\/www.ibm.com\/docs\/en\/db2\/11.5?topic=information-creating-explain-tables\" target=\"_blank\" rel=\"noopener\">two options<\/a> for this and we&#8217;ll use the EXPLAIN.DDL script which comes with the Db2 installation:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1; highlight: [1,3]\">\ndb2@sles15db2:~&gt; ls -l .\/sqllib\/misc\/EXPLAIN.DDL\n-r--r--r-- 1 db2 db2 48371 Jun 11  2021 .\/sqllib\/misc\/EXPLAIN.DDL\ndb2@sles15db2:~&gt; grep -i \"create table\" .\/sqllib\/misc\/EXPLAIN.DDL\nCREATE TABLE EXPLAIN_INSTANCE ( EXPLAIN_REQUESTER VARCHAR(128 OCTETS) NOT NULL,\nCREATE TABLE EXPLAIN_STATEMENT ( EXPLAIN_REQUESTER VARCHAR(128 OCTETS) NOT NULL,\nCREATE TABLE EXPLAIN_ARGUMENT ( EXPLAIN_REQUESTER   VARCHAR(128 OCTETS)  NOT NULL,\nCREATE TABLE EXPLAIN_OBJECT ( EXPLAIN_REQUESTER    VARCHAR(128 OCTETS) NOT NULL,\nCREATE TABLE EXPLAIN_OPERATOR ( EXPLAIN_REQUESTER VARCHAR(128 OCTETS) NOT NULL,\nCREATE TABLE EXPLAIN_PREDICATE ( EXPLAIN_REQUESTER VARCHAR(128 OCTETS) NOT NULL,\nCREATE TABLE EXPLAIN_STREAM ( EXPLAIN_REQUESTER VARCHAR(128 OCTETS) NOT NULL,\nCREATE TABLE EXPLAIN_DIAGNOSTIC ( EXPLAIN_REQUESTER VARCHAR(128 OCTETS) NOT NULL,\nCREATE TABLE EXPLAIN_DIAGNOSTIC_DATA ( EXPLAIN_REQUESTER VARCHAR(128 OCTETS) NOT NULL,\nCREATE TABLE OBJECT_METRICS ( EXECUTABLE_ID     VARCHAR(32 OCTETS) FOR BIT DATA NOT NULL,\nCREATE TABLE ADVISE_INSTANCE (\nCREATE TABLE ADVISE_INDEX(\nCREATE TABLE ADVISE_WORKLOAD (\nCREATE TABLE ADVISE_MQT (\nCREATE TABLE ADVISE_PARTITION (\nCREATE TABLE ADVISE_TABLE (\nCREATE TABLE EXPLAIN_ACTUALS ( EXPLAIN_REQUESTER VARCHAR(128 OCTETS) NOT NULL,\n<\/pre>\n<p>Quite a few tables get created by this script and we&#8217;ll not go into the details of which table contains what (this is out of the scope of this post), but just execute it:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\ndb2@sles15db2:~&gt; db2 -tvf .\/sqllib\/misc\/EXPLAIN.DDL\n\n******* IMPORTANT ********** \n\nUSAGE: db2 -tf EXPLAIN.DDL   \n\n******* IMPORTANT ********** \n\n\nUPDATE COMMAND OPTIONS USING C OFF\nDB20000I  The UPDATE COMMAND OPTIONS command completed successfully.\n...\nCOMMIT WORK\nDB20000I  The SQL command completed successfully.\n<\/pre>\n<p>All done, lets try again:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1; highlight: [1,3]\">\ndb2@sles15db2:~&gt; db2 set current explain mode explain\nDB20000I  The SQL command completed successfully.\ndb2@sles15db2:~&gt; db2 -tvf  1.sql\nselect count(*) from t where tabname='t'\nSQL0217W  The statement was not executed as only Explain information requests \nare being processed.  SQLSTATE=01604\n<\/pre>\n<p>Looks better, but how do we get the explain plan? One option is to use <a href=\"https:\/\/www.ibm.com\/docs\/en\/db2\/11.5?topic=commands-db2exfmt-explain-table-format\" target=\"_blank\" rel=\"noopener\">db2exfmt<\/a>:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1; highlight: [1,11]\">\ndb2@sles15db2:~&gt; db2exfmt -d DB1 -# 0 -w -1 -g TIC -n % -s % -o explain.txt\nDB2 Universal Database Version 11.5, 5622-044 (c) Copyright IBM Corp. 1991, 2017\nLicensed Material - Program Property of IBM\nIBM DATABASE 2 Explain Table Format Tool\n\nConnecting to the Database.\nConnect to Database Successful.\nBinding package - Bind was Successful\nOutput is in explain.txt.\nExecuting Connect Reset -- Connect Reset was Successful.\ndb2@sles15db2:~&gt; db2 set current explain mode no\nDB20000I  The SQL command completed successfully.\n<\/pre>\n<p>This generates the &#8220;explain.txt&#8221; file with lots of information. What we are looking for is the explain plan and there is a graphical representation in the file, which is exactly what we need:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\nAccess Plan:\n-----------\n        Total Cost:             6.77696\n        Query Degree:           1\n\n\n      Rows \n     RETURN\n     (   1)\n      Cost \n       I\/O \n       |\n        1 \n     GRPBY \n     (   2)\n     6.77689 \n        1 \n       |\n     35.8378 \n     IXSCAN\n     (   3)\n     6.77614 \n        1 \n       |\n      14016 \n INDEX: DB2     \n        I\n       Q1\n<\/pre>\n<p>Another option is to use <a href=\"https:\/\/www.ibm.com\/docs\/en\/db2\/11.5?topic=commands-db2expln-sql-xquery-explain\" target=\"_blank\" rel=\"noopener\">db2expln<\/a>:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\ndb2@sles15db2:~&gt; db2expln -database db1 -statement \"select count(*) from t where tabname = 't'\" -terminal\n\nDB2 Universal Database Version 11.5, 5622-044 (c) Copyright IBM Corp. 1991, 2017\nLicensed Material - Program Property of IBM\nIBM DB2 Universal Database SQL and XQUERY Explain Tool\n\nDB2 Universal Database Version 11.5, 5622-044 (c) Copyright IBM Corp. 1991, 2017\nLicensed Material - Program Property of IBM\nIBM DB2 Universal Database SQL and XQUERY Explain Tool\n\n******************** DYNAMIC ***************************************\n\n==================== STATEMENT ==========================================\n\n        Isolation Level          = Cursor Stability\n        Blocking                 = Block Unambiguous Cursors\n        Query Optimization Class = 5\n\n        Partition Parallel       = No\n        Intra-Partition Parallel = No\n\n        SQL Path                 = \"SYSIBM\", \"SYSFUN\", \"SYSPROC\", \"SYSIBMADM\", \n                                   \"DB2\"\n\n\nStatement:\n  \n  select count(*)\n  from t \n  where tabname ='t' \n\n\nSection Code Page = 1208\n\nEstimated Cost = 6.775021\nEstimated Cardinality = 1.000000\n\nAccess Table Name = DB2.T  ID = 2,4\n|  Index Scan:  Name = DB2.I  ID = 1\n|  |  Regular Index (Not Clustered)\n|  |  Index Columns:\n|  |  |  1: TABNAME (Ascending)\n|  #Columns = 0\n|  Skip Inserted Rows\n|  Avoid Locking Committed Data\n|  Currently Committed for Cursor Stability\n|  #Key Columns = 1\n|  |  Start Key: Inclusive Value\n|  |  |  1: 't' \n|  |  Stop Key: Inclusive Value\n|  |  |  1: 't' \n|  Index-Only Access\n|  Index Prefetch: Sequential(1), Readahead\n|  Lock Intents\n|  |  Table: Intent Share\n|  |  Row  : Next Key Share\n|  Sargable Index Predicate(s)\n|  |  Predicate Aggregation\n|  |  |  Column Function(s)\nAggregation Completion\n|  Column Function(s)\nReturn Data to Application\n|  #Columns = 1\n\nEnd of section\n<\/pre>\n<p>Compared to db2exfmt this gives a much more compact output.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>The last post about Db2 was about getting it up and running. As the issue we had to solve was about performance, getting an explain plan for the problematic statement(s) was the obvious step to do. In PostgreSQL you can just use EXPLAIN for that, and you&#8217;re done. In Db2 the procedure is a bit [&hellip;]<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[56,76,77],"type_dbi":[],"class_list":["post-255","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-db2","tag-explain-plans","tag-postgresql"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>Getting explain plans out of Db2 - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/getting-explain-plans-out-of-db2\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Getting explain plans out of Db2\" \/>\n<meta property=\"og:description\" content=\"The last post about Db2 was about getting it up and running. As the issue we had to solve was about performance, getting an explain plan for the problematic statement(s) was the obvious step to do. In PostgreSQL you can just use EXPLAIN for that, and you&#8217;re done. In Db2 the procedure is a bit [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/getting-explain-plans-out-of-db2\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2022-01-20T08:03:13+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-04-06T06:26:48+00:00\" \/>\n<meta name=\"author\" content=\"Daniel Westermann\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@westermanndanie\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Daniel Westermann\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"8 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/getting-explain-plans-out-of-db2\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/getting-explain-plans-out-of-db2\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"Getting explain plans out of Db2\",\"datePublished\":\"2022-01-20T08:03:13+00:00\",\"dateModified\":\"2022-04-06T06:26:48+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/getting-explain-plans-out-of-db2\/\"},\"wordCount\":304,\"commentCount\":0,\"keywords\":[\"DB2\",\"Explain plans\",\"PostgreSQL\"],\"articleSection\":[\"Database Administration &amp; Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/getting-explain-plans-out-of-db2\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/getting-explain-plans-out-of-db2\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/getting-explain-plans-out-of-db2\/\",\"name\":\"Getting explain plans out of Db2 - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2022-01-20T08:03:13+00:00\",\"dateModified\":\"2022-04-06T06:26:48+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/getting-explain-plans-out-of-db2\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/getting-explain-plans-out-of-db2\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/getting-explain-plans-out-of-db2\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Getting explain plans out of Db2\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\",\"name\":\"Daniel Westermann\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"caption\":\"Daniel Westermann\"},\"description\":\"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &amp;\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.\",\"sameAs\":[\"https:\/\/x.com\/westermanndanie\"],\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Getting explain plans out of Db2 - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/getting-explain-plans-out-of-db2\/","og_locale":"en_US","og_type":"article","og_title":"Getting explain plans out of Db2","og_description":"The last post about Db2 was about getting it up and running. As the issue we had to solve was about performance, getting an explain plan for the problematic statement(s) was the obvious step to do. In PostgreSQL you can just use EXPLAIN for that, and you&#8217;re done. In Db2 the procedure is a bit [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/getting-explain-plans-out-of-db2\/","og_site_name":"dbi Blog","article_published_time":"2022-01-20T08:03:13+00:00","article_modified_time":"2022-04-06T06:26:48+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"8 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/getting-explain-plans-out-of-db2\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/getting-explain-plans-out-of-db2\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"Getting explain plans out of Db2","datePublished":"2022-01-20T08:03:13+00:00","dateModified":"2022-04-06T06:26:48+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/getting-explain-plans-out-of-db2\/"},"wordCount":304,"commentCount":0,"keywords":["DB2","Explain plans","PostgreSQL"],"articleSection":["Database Administration &amp; Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/getting-explain-plans-out-of-db2\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/getting-explain-plans-out-of-db2\/","url":"https:\/\/www.dbi-services.com\/blog\/getting-explain-plans-out-of-db2\/","name":"Getting explain plans out of Db2 - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2022-01-20T08:03:13+00:00","dateModified":"2022-04-06T06:26:48+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/getting-explain-plans-out-of-db2\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/getting-explain-plans-out-of-db2\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/getting-explain-plans-out-of-db2\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Getting explain plans out of Db2"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66","name":"Daniel Westermann","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","caption":"Daniel Westermann"},"description":"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &amp;\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.","sameAs":["https:\/\/x.com\/westermanndanie"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/255","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/29"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=255"}],"version-history":[{"count":5,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/255\/revisions"}],"predecessor-version":[{"id":714,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/255\/revisions\/714"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=255"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=255"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=255"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=255"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}