Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Thumbs up Unanswered: Two ways to get DB2 EXPLAIN as Report

    First formula (For people which do not like the recursive SQL):

    Code:
    select 
    queryno, planno, method, tname,
    accesstype,matchcols, accessname
    ,  ifnull(k1.colname, '') 
    || ifnull(', ' || k2.colname, '')
    || ifnull(', ' || k3.colname, '')
    || ifnull(', ' || k4.colname, '')
    || ifnull(', ' || k5.colname, '')
    || ifnull(', ' || k6.colname, '')
    || ifnull(', ' || k7.colname, '') as "List of Index Columns",  
    ifnull(n1.colname, '') 
    || ifnull(', ' || n2.colname, '')
    || ifnull(', ' || n3.colname, '')
    || ifnull(', ' || n4.colname, '')
    || ifnull(', ' || n5.colname, '')
    || ifnull(', ' || n6.colname, '')
    || ifnull(', ' || n7.colname, '') as "Not Used Index Columns"
    , indexonly
    , creator
    from 
    fdwddba.plan_table  pl 
    left join sysibm.syskeys k1
    on    k1.ixname   = pl.accessname
      and k1.colseq   = 1
      and k1.colseq  <= pl.matchcols
      and pl.accessname > ' '
      and k1.ixcreator = pl.creator
    left join sysibm.syskeys k2
    on    k2.ixname = pl.accessname
      and k2.colseq  = 2
      and k2.colseq  <= pl.matchcols
      and pl.accessname > ' '
      and k2.ixcreator = pl.creator
    left join sysibm.syskeys k3
    on    k3.ixname  = pl.accessname
      and k3.colseq  = 3
      and k3.colseq  <= pl.matchcols
      and pl.accessname > ' '
      and k3.ixcreator = pl.creator
    left join sysibm.syskeys k4
    on    k4.ixname = pl.accessname
      and k4.colseq  = 4
      and k4.colseq  <= pl.matchcols
      and pl.accessname > ' '
      and k4.ixcreator = pl.creator
    left join sysibm.syskeys k5
    on    k5.ixname = pl.accessname
      and k5.colseq  = 5
      and k5.colseq  <= pl.matchcols
      and pl.accessname > ' '
      and k5.ixcreator = pl.creator
    left join sysibm.syskeys k6
    on    k6.ixname = pl.accessname
      and k6.colseq  = 6
      and k6.colseq  <= pl.matchcols
      and pl.accessname > ' '
      and k6.ixcreator = pl.creator
    left join sysibm.syskeys k7
    on    k7.ixname = pl.accessname
      and k7.colseq  = 7
      and k7.colseq  <= pl.matchcols
      and pl.accessname > ' '
      and k7.ixcreator = pl.creator
    left join sysibm.syskeys n1
    on    n1.ixname  = pl.accessname
      and n1.colseq  = pl.matchcols + 1
      and pl.accessname > ' '
      and n1.ixcreator = pl.creator
    left join sysibm.syskeys n2
    on    n2.ixname  = pl.accessname
      and n2.colseq  = pl.matchcols + 2
      and pl.accessname > ' '
      and n2.ixcreator = pl.creator
    left join sysibm.syskeys n3
    on    n3.ixname  = pl.accessname
      and n3.colseq  = pl.matchcols + 3
      and pl.accessname > ' '
      and n3.ixcreator = pl.creator
    left join sysibm.syskeys n4
    on    n4.ixname  = pl.accessname
      and n4.colseq  = pl.matchcols + 4
      and pl.accessname > ' '
      and n4.ixcreator = pl.creator
    left join sysibm.syskeys n5
    on    n5.ixname  = pl.accessname
      and n5.colseq  = pl.matchcols + 5
      and pl.accessname > ' '
      and n5.ixcreator = pl.creator
    left join sysibm.syskeys n6
    on    n6.ixname  = pl.accessname
      and n6.colseq  = pl.matchcols + 6
      and pl.accessname > ' '
      and n6.ixcreator = pl.creator
    left join sysibm.syskeys n7
    on    n7.ixname  = pl.accessname
      and n7.colseq  = pl.matchcols + 7
      and pl.accessname > ' '
      and n7.ixcreator = pl.creator
    WHERE queryno in (8877)
      and TNAME > ' '   
    order by queryno, planno
    with ur
    Second way to get Explains Report is easier and much more flexible then first one, but using the recursive SQL:

    Code:
    with 
    qrynbr(qry#) as 
    (select 8917 from sysibm.sysdummy1
    )
    ,
    expln as
    (select queryno, planno, method,tname,accesstype, matchcols, accessname,indexonly,creator,prefetch, tabno
    from fdwddba.plan_table, qrynbr 
    where queryno = qry#  
    )
    ,
    ix_tbl (ixn, seq, ix_cols, not_used_cols, maxcols, tabno) as
    (
    select sk.ixname, 
    1, 
    case when ex.matchcols > 0 then
    sk.colname
    else ifnull(nullif(sk.colname, sk.colname), '')
    end
    ,
    case when ex.matchcols > 0 then
    ifnull(nullif(sk.colname, sk.colname), '')
    else sk.colname
    end
    , si.colcount
    , tabno 
       from sysibm.syskeys    sk
    	 ,expln             ex
           ,sysibm.sysindexes si
    where sk.ixname    = ex.accessname
      and sk.ixcreator = ex.creator 
      and sk.ixname    = si.name
      and sk.ixcreator = si.creator 
      and sk.colseq    = 1  
      and ex.accessname > ' '
      and ex.matchcols <=  si.colcount 
    union all
     select sk.ixname, seq + 1, 
    case when seq  <  ex.matchcols
         then ix_cols || ',' || sk.colname
    else ix_cols 
    end
    , 
    case when seq >= ex.matchcols 
         then not_used_cols || ',' || sk.colname
    else not_used_cols 
    end
    , maxcols
    , ix.tabno
    from    ix_tbl         ix
    	, sysibm.syskeys sk
    	, expln ex
       where seq + 1       = sk.colseq 
    	and seq          <  maxcols 
    	and sk.ixname    = ex.accessname
          and sk.ixcreator = ex.creator 
    	and sk.ixname    = ixn 
          and ex.tabno     = ix.tabno  
        )  
    select ex.queryno, ex.planno,ex.method, 
           case when si.tbname is null 
                then ex.tname
    		else si.tbname
    	 end tname
    	,ex.accesstype, ex.matchcols
    	,ex.accessname
    	,ix.ix_cols "List of Index Columns" 
            ,strip(ix.not_used_cols, L, ',') "Not Used Index Columns"
    	,ex.indexonly, ex.prefetch, ex.creator	
      from  expln ex
    left join sysibm.sysindexes si
      on si.name = ex.accessname
      and si.creator = ex.creator
    left join ( select ixn,substr(max(char(seq)||ix_cols),12) ix_cols , 
                       substr(max(char(seq)|| not_used_cols ),12) not_used_cols,
                       tabno     
    		from  ix_tbl  
    	      group by ixn, tabno ) ix				
    on   ex.accessname = ix.ixn
     and ex.tabno     = ix.tabno 
    where ex.tname > ' '
    order by ex.queryno, ex.planno, ex.accesstype
    You can try both of them. These queries are very usefull.

    Thanks, Lenny
    Last edited by Lenny77; 05-13-10 at 17:35.

  2. #2
    Join Date
    Apr 2010
    Posts
    5
    i'd like to know the queries are for which version of db2

  3. #3
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Post Z/os v8 & v9

    Quote Originally Posted by guhongying View Post
    i'd like to know the queries are for which version of db2
    I am using V8 for z/os, but solution is good for V9 z/os as well:

    IBM Information Management Software for z/OS Solutions Information Center

    Lenny

  4. #4
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Lightbulb Looking for anothe table to JOIN (indexspace scan)

    Now we can find the table to join with table from Explain's report, which has
    index space scan.

    We are looking for another table (creator the same) which has one of the column from original index the first:


    Code:
    with query_str(query) as
    (select 
     'select				
            distributi0_.DOCUMENT_ID as DOCUMENT2_0_,				
            distributi0_.DISTRIBUTION_ID as DISTRIBU1_0_ 				
        from				
            FMIDDBA.MW_DOC_DIST distributi0_ 				
        where				
             distributi0_.DOCUMENT_ID = 590316' 
    from sysibm.sysdummy1 ) 
    select distinct e1.ixname, e1.colname, e1.tbname, xs.tbname orig_tbl
    from 
    sysibm.syskeys       ks , 
    sysibm.sysindexes   xs , 
    fdwddba.plan_table pl  , 
    query_str              qr
    ,
    table (select k2.ixname, k2.colname, x2.tbname      
             from sysibm.syskeys k2, sysibm.sysindexes  x2 
                 where k2.ixcreator = xs.creator
                   and k2.ixname   <> xs.name
                   and k2.colname   = ks.colname
                   and k2.ixname    = x2.name
                   and k2.ixcreator = x2.creator	
                   and k2.colseq    = 1  ) e1
    where pl.queryno    = 8901  
      and 
    (Locate(ks.colname, qr.query) > Locate ('WHERE', ucase(qr.query)) 
     or  
     Locate(ks.colname, qr.query) > Locate (' ON ', ucase(qr.query)) )
      and ks.ixcreator  = pl.creator
      and ks.colseq     > 1  
      and pl.accesstype = 'I'
      and pl.matchcols  = 0 
      and xs.name       = accessname  
    order by e1.ixname
    Lenny

  5. #5
    Join Date
    May 2010
    Posts
    21
    It seems that you use a with statement that takes a parameter. What is the syntax for creating a with statement with parameters and using it?

  6. #6
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Smile

    Quote Originally Posted by jkuyken View Post
    It seems that you use a with statement that takes a parameter. What is the syntax for creating a with statement with parameters and using it?
    There are no parameters, tables with colums, only.
    Lenny

  7. #7
    Join Date
    May 2010
    Posts
    21
    Quote Originally Posted by Lenny77 View Post
    There are no parameters, tables with colums, only.
    Lenny
    Ok, I see. But this is new to me:
    Code:
    with query_str(query) as
    (select 
     'select				
            distributi0_.DOCUMENT_ID as DOCUMENT2_0_,				
            distributi0_.DISTRIBUTION_ID as DISTRIBU1_0_ 				
        from				
            FMIDDBA.MW_DOC_DIST distributi0_ 				
        where				
             distributi0_.DOCUMENT_ID = 590316' 
    from sysibm.sysdummy1 )
    Not the with but the (query). What does this piece of code do?

  8. #8
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Talking

    Quote Originally Posted by jkuyken View Post
    Ok, I see. But this is new to me:
    Code:
    with query_str(query) as
    (select 
     'select				
            distributi0_.DOCUMENT_ID as DOCUMENT2_0_,				
            distributi0_.DISTRIBUTION_ID as DISTRIBU1_0_ 				
        from				
            FMIDDBA.MW_DOC_DIST distributi0_ 				
        where				
             distributi0_.DOCUMENT_ID = 590316' 
    from sysibm.sysdummy1 )
    Not the with but the (query). What does this piece of code do?
    You have to read DB2 manuals and references, which are available to everybody.

    Lenny

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •