00001 <?php
00002 /* 
00003 V4.90 8 June 2006  (c) 2000-2006 John Lim (jlim#natsoft.com.my). All rights reserved.
00004   Released under both BSD license and Lesser GPL library license. 
00005   Whenever there is any discrepancy between the two licenses, 
00006   the BSD license will take precedence. See License.txt. 
00007   Set tabs to 4 for best viewing.
00008   
00009   Latest version is available at http://adodb.sourceforge.net
00010   
00011   Library for basic performance monitoring and tuning 
00012   
00013 */
00014 
00015 // security - hide paths
00016 if (!defined('ADODB_DIR')) die();
00017 
00018 // Simple guide to configuring db2: so-so http://www.devx.com/gethelpon/10MinuteSolution/16575
00019 
00020 // SELECT * FROM TABLE(SNAPSHOT_APPL('SAMPLE', -1)) as t
00021 class perf_db2 extends adodb_perf{
00022         var $createTableSQL = "CREATE TABLE adodb_logsql (
00023                   created TIMESTAMP NOT NULL,
00024                   sql0 varchar(250) NOT NULL,
00025                   sql1 varchar(4000) NOT NULL,
00026                   params varchar(3000) NOT NULL,
00027                   tracer varchar(500) NOT NULL,
00028                   timer decimal(16,6) NOT NULL
00029                 )";
00030                 
00031         var $settings = array(
00032         'Ratios',
00033                 'data cache hit ratio' => array('RATIO',
00034                         "SELECT 
00035                                 case when sum(POOL_DATA_L_READS+POOL_INDEX_L_READS)=0 then 0 
00036                                 else 100*(1-sum(POOL_DATA_P_READS+POOL_INDEX_P_READS)/sum(POOL_DATA_L_READS+POOL_INDEX_L_READS)) end 
00037                                 FROM TABLE(SNAPSHOT_APPL('',-2)) as t",
00038                         '=WarnCacheRatio'),
00039                         
00040         'Data Cache',
00041                 'data cache buffers' => array('DATAC',
00042                 'select sum(npages) from SYSCAT.BUFFERPOOLS',
00043                         'See <a href=http://www7b.boulder.ibm.com/dmdd/library/techarticle/anshum/0107anshum.html#bufferpoolsize>tuning reference</a>.' ),
00044                 'cache blocksize' => array('DATAC',
00045                 'select avg(pagesize) from SYSCAT.BUFFERPOOLS',
00046                         '' ),
00047                 'data cache size' => array('DATAC',
00048                 'select sum(npages*pagesize) from SYSCAT.BUFFERPOOLS',
00049                         '' ),
00050         'Connections',
00051                 'current connections' => array('SESS',
00052                         "SELECT count(*) FROM TABLE(SNAPSHOT_APPL_INFO('',-2)) as t",
00053                         ''),
00054 
00055                 false
00056         );
00057 
00058 
00059         function perf_db2(&$conn)
00060         {
00061                 $this->conn =& $conn;
00062         }
00063         
00064         function Explain($sql,$partial=false)
00065         {
00066                 $save = $this->conn->LogSQL(false);
00067                 if ($partial) {
00068                         $sqlq = $this->conn->qstr($sql.'%');
00069                         $arr = $this->conn->GetArray("select distinct sql1 from adodb_logsql where sql1 like $sqlq");
00070                         if ($arr) {
00071                                 foreach($arr as $row) {
00072                                         $sql = reset($row);
00073                                         if (crc32($sql) == $partial) break;
00074                                 }
00075                         }
00076                 }
00077                 $qno = rand();
00078                 $ok = $this->conn->Execute("EXPLAIN PLAN SET QUERYNO=$qno FOR $sql");
00079                 ob_start();
00080                 if (!$ok) echo "<p>Have EXPLAIN tables been created?</p>";
00081                 else {
00082                         $rs = $this->conn->Execute("select * from explain_statement where queryno=$qno");
00083                         if ($rs) rs2html($rs);
00084                 }
00085                 $s = ob_get_contents();
00086                 ob_end_clean();
00087                 $this->conn->LogSQL($save);
00088                 
00089                 $s .= $this->Tracer($sql);
00090                 return $s;
00091         }
00092         
00093         
00094         function Tables()
00095         {
00096                 $rs = $this->conn->Execute("select tabschema,tabname,card as rows,
00097                         npages pages_used,fpages pages_allocated, tbspace tablespace  
00098                         from syscat.tables where tabschema not in ('SYSCAT','SYSIBM','SYSSTAT') order by 1,2");
00099                 return rs2html($rs,false,false,false,false);
00100         }
00101 }
00102 ?>
perf-db2.inc.php
