DB2 Table Scans

You may also like...

7 Responses

  1. Isaac Munoz says:

    Hi Ember,

    Could you share your experience regarding index definitions.
    what is better for the index heading columns: highest to lowest or lowest to highest distinct values?.

    Thanks

    • Ember Crooks says:

      Index column order depends first on the queries you’re indexing for. Putting columns in one order vs. another can allow one index to serve multiple queries. Even with the Jump Scans introduced in 10.1, db2 is still unlikely to use an index for a query that includes a column the query is not using in between columns that it is using. Indexing should depend on the SQL running more than the table itself. After that, it’s best to put the columns with the highest cardinality (number of distinct values) first. The reasoning for this is the structure of b-tree indexes – the more restrictive we are earlier in the index tree traversal, the fewer pages DB2 has to read. Even if the pages being read are in memory (and thus do not require I/Os), this can save CPU cycles. Does that help?

  2. Isaac Munoz says:

    Hi again Ember,

    I’m playing with read efficiency and some other calculations from your various blogs using MON_GET_PKG_CACHE_STMT but I just can’t manage to get consistency between this table function and db2top ‘D’ option or SNAP_GET_DYN_SQL_V95. The last two are consistent in the results but MON_GET_PKG_CACHE_STMT always shows me something different (i.e. something simple like ordering by by total exec time or num executions).

    Honestly I don’t get it why MON_GET_PKG_CACHE_STMT shows not the same amount of values. Can you shed some light on this?.

    Thanks and regards

    • Ember Crooks says:

      what specific differences are you seeing? differing numbers of statements, different statements, different values of counters/read efficency for the same statements?

  3. Isaac Munoz says:

    Sorry the text is not indented… i can send you images what ever email you want me to… but here’s the output of SNAP_GET_DYN_SQL_V95 versus MON_GET_PKG_CACHE_STMT.

    I hope am doing something really stupid here but at the moment DB2’s behavior is driving me nuts…

    Num executions –> See the difference for the insert statement on IBMQREP_DONEMSG

    $ db2 +w -v “select varchar(STMT_TEXT,130) AS STATEMENT,ROWS_READ,NUM_EXECUTIONS FROM TABLE(SNAP_GET_DYN_SQL_V95(NULL,-1)) AS T order by NUM_EXECUTIONS >
    select varchar(STMT_TEXT,130) AS STATEMENT,ROWS_READ,NUM_EXECUTIONS FROM TABLE(SNAP_GET_DYN_SQL_V95(NULL,-1)) AS T order by NUM_EXECUTIONS desc fetch first 10 rows only

    STATEMENT ROWS_READ NUM_EXECUTIONS
    ———————————————————————————————————————————- ——————– ——————–
    INSERT INTO “ASN_QREP”.IBMQREP_DONEMSG (RECVQ, MQMSGID) VALUES( ?, ?) 6 9645243
    INSERT INTO “SSMD00”.”ROUTEHISTORY”( “ID”, “ARCHFLAG”, “DESTNODEID”, “MESSAGEID”, “SOURCENODEID”, “TMSTAMP”, “USID”) VALUES (?, ?, 15 8399207
    UPDATE “SSMD00″.”MESSAGE” SET “TMSTAMP” = ? WHERE “ID” = ? 6951445 6951437
    INSERT INTO “SSMD00”.”MESSAGE”( “ID”, “ARCHDATE”, “ARCHFLAG”, “CONTROLFLAG”, “DATECREATED”, “EXTERNALREFERENCE”, “MESSAGEDATA”, “M 15 3948358
    UPDATE “SSMD00″.”MESSAGE” SET “NODEID” = ?, “NODETYPE” = ?, “SOURCENODEID” = ?, “TMSTAMP” = ?, “USID” = ? WHERE “ID” = ? 3559880 3559880
    UPDATE “SSMD00″.”MESSAGE” SET “NODETYPE” = ?, “TMSTAMP” = ? WHERE “ID” = ? 3237962 3237961
    INSERT INTO “SSMD00”.”CHANNELTRANSACTION”( “CHANNELTRANSACTIONID”, “APIMETHODNAME”, “APPLICATIONID”, “ATPREFERENCE”, “CHANNELID”, 9 2741375
    INSERT INTO “SSMD00”.”SINGLETRANS”( “SINGLETRANSID”, “TMSTAMP”, “APPLICATIONID”, “DESTINATIONCHANNELID”, “DIRECTION”, “ERRORCODE”, 0 1963527
    INSERT INTO “SSMD00”.”PROCESSDATA”( “PROCESSDATANO”, “FIELDNAME”, “OBJECTTYPE”, “PROCESSNO”, “TMSTAMP”, “USRID”, “VALUE”) VALUES ( 5 1515716
    INSERT INTO “SSMD00”.”STEPACTIVITY”( “STEPACTIVITYNO”, “ACTIVITYTYPE”, “REFNO”, “REFVAL”, “STEPNO”, “TMSTAMP”, “USRID”) VALUES (?, 0 1198216

    10 record(s) selected with 6 warning messages suppressed.

    $ db2 +w -v “select varchar(STMT_TEXT,130) AS STATEMENT,ROWS_READ,NUM_EXECUTIONS,NUM_EXEC_WITH_METRICS FROM TABLE(MON_GET_PKG_CACHE_STMT(NULL,NULL,NULL,>
    select varchar(STMT_TEXT,130) AS STATEMENT,ROWS_READ,NUM_EXECUTIONS,NUM_EXEC_WITH_METRICS FROM TABLE(MON_GET_PKG_CACHE_STMT(NULL,NULL,NULL,-1)) AS T order by NUM_EXECUTIONS desc fetch first 10 rows only

    STATEMENT ROWS_READ NUM_EXECUTIONS NUM_EXEC_WITH_METRICS
    ———————————————————————————————————————————- ——————– ——————– ———————
    VALUES(LENGTH(:H00003 )) INTO :H00018 :H00019 0 4630190 4630190
    VALUES(SUBSTR(:H00003 ,:H00016,:H00017 )) INTO :H00009:H00019 0 3917915 3917915
    INSERT INTO “ASN_QREP”.IBMQREP_DONEMSG (RECVQ, MQMSGID) VALUES( ?, ?) 0 3081224 3081224
    INSERT INTO “SSMD00”.”ROUTEHISTORY”( “ID”, “ARCHFLAG”, “DESTNODEID”, “MESSAGEID”, “SOURCENODEID”, “TMSTAMP”, “USID”) VALUES (?, ?, 0 1848732 1848732
    INSERT INTO “SSMD00”.”SINGLETRANS”( “SINGLETRANSID”, “TMSTAMP”, “APPLICATIONID”, “DESTINATIONCHANNELID”, “DIRECTION”, “ERRORCODE”, 0 1754622 1754622
    UPDATE “SSMD00″.”MESSAGE” SET “TMSTAMP” = ? WHERE “ID” = ? 1465019 1465019 1465019
    INSERT INTO “SSMD00”.”STEPACTIVITY”( “STEPACTIVITYNO”, “ACTIVITYTYPE”, “REFNO”, “REFVAL”, “STEPNO”, “TMSTAMP”, “USRID”) VALUES (?, 0 1181489 1181489
    INSERT INTO “SSMD00”.”PROCESSDATA”( “PROCESSDATANO”, “FIELDNAME”, “OBJECTTYPE”, “PROCESSNO”, “TMSTAMP”, “USRID”, “VALUE”) VALUES ( 0 968943 968943
    INSERT INTO “SSMD00”.”MESSAGE”( “ID”, “ARCHDATE”, “ARCHFLAG”, “CONTROLFLAG”, “DATECREATED”, “EXTERNALREFERENCE”, “MESSAGEDATA”, “M 0 797935 797935
    UPDATE “SSMD00″.”MESSAGE” SET “NODEID” = ?, “NODETYPE” = ?, “SOURCENODEID” = ?, “TMSTAMP” = ?, “USID” = ? WHERE “ID” = ? 740897 740897 740897

    10 record(s) selected with 5 warning messages suppressed.

    Rows read –> There are a lot of inconsistencies ordered by rows read but as a sample check the rows_read diff for the select statement on SSMD00.SingleTrans even though they have same num execs (131)

    $ db2 +w -v “select varchar(STMT_TEXT,130) AS STATEMENT,ROWS_READ,NUM_EXECUTIONS FROM TABLE(SNAP_GET_DYN_SQL_V95(NULL,-1)) AS T order by ROWS_READ desc >
    select varchar(STMT_TEXT,130) AS STATEMENT,ROWS_READ,NUM_EXECUTIONS FROM TABLE(SNAP_GET_DYN_SQL_V95(NULL,-1)) AS T order by ROWS_READ desc fetch first 10 rows only

    STATEMENT ROWS_READ NUM_EXECUTIONS
    ———————————————————————————————————————————- ——————– ——————–
    SELECT * FROM ( SELECT sourcenodeid,nodeid, ErrorCount, QueueCount, CASE when QueueCount != :L0 then DECIMAL((DECIMAL(ErrorCount)/ 7805498375 3
    select * from ssmd00.taxReturnData where cast(tmStamp as date) >= (select date(days(current date) – :L0 ) from sysibm.sysdummy1) 6768740396 128
    select count(*),nodeid,SourceNodeid ,max(Tmstamp) as TMSTAMP from ssmd00.MESSAGE where NODETYPE = :L0 and datecreated > :L1 group 1444765284 281
    SELECT * FROM ( SELECT sourcenodeid,nodeid, ErrorCount, QueueCount, CASE when QueueCount != :L0 then DECIMAL((DECIMAL(ErrorCount)/ 1351003503 5
    Select :L0 as ERRORCODE,count(Memory.ERRORCODE) as Count, :L1 as Unique from SSMD00.SingleTrans Memory where Memory.ERRORCODE = :L 415119022 131
    select * from SSMD00.IRP5IT3A i where CERTIFICATENO = :L0 142042080 1
    [ARCHIVE] SELECT A0.”CASENO”, A0.”TITLE”, A0.”REFVALUE”, A0.”AREACODE” FROM “SSMD00″.”CASESARCH” A0 WHERE (:H0
    select varchar(STMT_TEXT,130) AS STATEMENT,ROWS_READ,NUM_EXECUTIONS,NUM_EXEC_WITH_METRICS FROM TABLE(MON_GET_PKG_CACHE_STMT(NULL,NULL,NULL,-1)) AS T order by ROWS_READ desc fetch first 10 rows only

    STATEMENT ROWS_READ NUM_EXECUTIONS NUM_EXEC_WITH_METRICS
    ———————————————————————————————————————————- ——————– ——————– ———————
    SELECT * FROM ( SELECT sourcenodeid,nodeid, ErrorCount, QueueCount, CASE when QueueCount != :L0 then DECIMAL((DECIMAL(ErrorCount)/ 7805498369 2 2
    select * from ssmd00.taxReturnData where cast(tmStamp as date) >= (select date(days(current date) – :L0 ) from sysibm.sysdummy1) 6768739244 128 128
    SELECT * FROM ( SELECT sourcenodeid,nodeid, ErrorCount, QueueCount, CASE when QueueCount != :L0 then DECIMAL((DECIMAL(ErrorCount)/ 1076596461 4 4
    select count(*),nodeid,SourceNodeid ,max(Tmstamp) as TMSTAMP from ssmd00.MESSAGE where NODETYPE = :L0 and datecreated > :L1 group 814133152 121 121
    Select :L0 as ERRORCODE,count(Memory.ERRORCODE) as Count, :L1 as Unique from SSMD00.SingleTrans Memory where Memory.ERRORCODE = :L 415118891 131 131
    select * from SSMD00.IRP5IT3A i where CERTIFICATENO = :L0 142042079 1 1
    select * from ssmd00.attachmentarch att join ssmd00.scandocumentobject sdo on att.storagehandle = sdo.documentumid join ssmd00.pro 105039230 2 2
    SELECT AREACODEID ,CAPTUREDATE ,CAPTURERID ,FORMTYPEID ,FORMYEAR ,IDNUMBER ,ITSFILEID ,ITSFILESEQUENCE 52885536 1 1
    select list.ApplicationID as “Interface”, list.DestinationChannelID as “Destination”, coalesce(a.”Responses Accepted”,:L0 ) as “Re 49854824 2 2
    SELECT STEPNO ,PARENTSTEPNO ,PARENTPROCESS ,TASKNO ,TASKTYPE ,ASSIGNEDUSER ,STATUS ,STA 46372599 1 1

    10 record(s) selected with 8 warning messages suppressed.

    db2top is the same but the active/running statements don’t allow me to take a clean screenshot for you to see clearly the differences…

    Thanks!!

  1. February 4, 2014

    […] Now that might look a bit complicated. It starts with calculating some values over the set as a whole in a common table expression (the "with" section). It then goes on to query the read efficency for each two-hour period, and along with it the values from the common table expression for comparision's sake. They will be the same values for every row, and are presented in-line for the purposes of easy comparison. Finally, I use a case expression to flag any values that are high or low. I probably don't care to investigate the low periods, but the periods where read efficiency was high probably warrant me digging into my historical package cache data for those periods to find out what statements were problematic on Read Efficiency. Analyzing SQL based on Read Efficiency is better described in my past entry: DB2 Table Scans. […]

  2. March 17, 2014

    […] you are looking for another example of IREF SQL, Ember has a more robust version in her “DB2 Table Scans” […]

Leave a Reply

Your email address will not be published. Required fields are marked *