2.11.1999  17:04            3 579 077 ApiGuide.pdf

22:50:36.271 trying to gather db statistics. . .
22:50:36.271 statistics gathered, dbstat[] length=33
22:50:36.271            reads                  241; diff:                  0
22:50:36.271            writes             3614655; diff:                703
22:50:36.271            fetches        -2118357188; diff:           11476403
22:50:36.271            marks            104009415; diff:              21165

22:50:41.271 trying to gather db statistics. . .
22:50:41.271 statistics gathered, dbstat[] length=33
22:50:41.271            reads                  241; diff:                  0
22:50:41.271            writes             3615358; diff:                263
22:50:41.271            fetches        -2106880785; diff:           11549457
22:50:41.271            marks            104030580; diff:               4749

22:50:46.271 trying to gather db statistics. . .
22:50:46.271 statistics gathered, dbstat[] length=33
22:50:46.271            reads                  241; diff:                  0
22:50:46.271            writes             3615621; diff:                490
22:50:46.271            fetches        -2095331328; diff:           11565988
22:50:46.271            marks            104035329; diff:              14400

      Map<Integer, String> tabs = new TreeMap<Integer, String>();
      Statement stmt = c.createStatement();
      String tqry="select rdb$relation_id, trim(rdb$relation_name) from rdb$relations"; // where coalesce(rdb$system_flag,0)=0";
      ResultSet rs = stmt.executeQuery(tqry); // smallint; chhar(31)
      while(rs.next()) {
          tabs.put(rs.getInt(1), rs.getString(2));
          //System.out.println("a1 = " + rs.getString(1) + ", a2 = " + rs.getString(2));
      }    
      stmt.close();
      System.out.println("tabs.size()="+tabs.size()+"; tabs="+tabs);

// compile: javac -cp .;jaybird-full-2.2.4.jar FbGatherStatistics.java
// run:     java -cp .;jaybird-full-2.2.4.jar FbGatherStatistics 1>FbGatherStatistics.log 2>FbGatherStatistics.err
import java.io.*;
import static java.lang.System.*;
import java.sql.*;
import java.text.*;
import java.util.*;
import org.firebirdsql.gds.*;
import org.firebirdsql.pool.*;


public final class FbGatherStatistics
{
  private static enum CounterType { PERF, DBOP, DUMMY }
  
  private static BufferedReader stdin = new BufferedReader(new InputStreamReader(System.in));
  private static DateFormat df = new SimpleDateFormat("HH:mm:ss.SSS");
  private static final String DB_URL="192.168.0.201/3252:empty25";
  //private static final String DB_URL="192.168.0.220/3330:idx_test";
  private static final String DB_USER="sysdba";
  private static final String DB_PSWD="masterke";
  static public void main (String args[]) throws Exception
  {
    // todo: use args for: db_name, db_user, db_password, sleep_delay  
    try {

        
      org.firebirdsql.gds.GDS gdso = org.firebirdsql.gds.GDSObjectFactory.createJavaGDSImpl();
      // Factory method to create a new IscDbHandle instance specific to the implementation of this interface.
      IscDbHandle dh =  gdso.createIscDbHandle();

      DatabaseParameterBuffer dpb = gdso.createDatabaseParameterBuffer();
      dpb.addArgument(DatabaseParameterBuffer.USER, DB_USER);
      dpb.addArgument(DatabaseParameterBuffer.PASSWORD, DB_PSWD);
      dpb.addArgument(DatabaseParameterBuffer.CONNECT_TIMEOUT, 2000);

      out.println( df.format( System.currentTimeMillis() )+ " trying to attach. . .");
      gdso.iscAttachDatabase(DB_URL, dh, dpb);
      out.println( df.format( System.currentTimeMillis() )+" attached to "+DB_URL);
      //stdin.readLine();         
      
      // strange but... need 2nd attach ?! could not find 'chain' between GDS object and Connection interface :((
      out.println( df.format( System.currentTimeMillis() )+ " trying to attach2. . .");
      FBWrappingDataSource ds = new FBWrappingDataSource();
      ds.setDatabase(DB_URL);
      // ds.setLoginTimeout (10);
      java.sql.Connection c = ds.getConnection(DB_USER,  DB_PSWD);
      out.println( df.format( System.currentTimeMillis() )+" attached2 to "+DB_URL);
      //stdin.readLine();         
      

      Map<Integer, String> tabs = new TreeMap<Integer, String>();
      Statement stmt = c.createStatement();
      String tqry="select rdb$relation_id, trim(rdb$relation_name) from rdb$relations"; // where coalesce(rdb$system_flag,0)=0";
      ResultSet rs = stmt.executeQuery(tqry); // smallint; chhar(31)
      while(rs.next()) {
          tabs.put(rs.getInt(1), rs.getString(2));
          //System.out.println("a1 = " + rs.getString(1) + ", a2 = " + rs.getString(2));
      }    
      stmt.close();
      System.out.println("tabs.size()="+tabs.size()+"; tabs="+tabs);
      
      Map<Integer, StatValue> cntx = new LinkedHashMap<Integer, StatValue>();
      cntx.put( ISCConstants.isc_info_reads,   new StatValue(CounterType.PERF, "reads",7));
      cntx.put( ISCConstants.isc_info_writes,  new StatValue(CounterType.PERF, "writes",7));
      cntx.put( ISCConstants.isc_info_fetches, new StatValue(CounterType.PERF, "fetches",7));
      cntx.put( ISCConstants.isc_info_marks,   new StatValue(CounterType.PERF, "marks",7));

      // add 10.12.2013: database operations counters
      cntx.put( ISCConstants.isc_info_read_idx_count, new StatValue(CounterType.DBOP, "idxReads",8));
      cntx.put( ISCConstants.isc_info_read_seq_count, new StatValue(CounterType.DBOP, "natReads",8));
      cntx.put( ISCConstants.isc_info_insert_count,   new StatValue(CounterType.DBOP, "inserts", 8));
      cntx.put( ISCConstants.isc_info_insert_count,   new StatValue(CounterType.DBOP, "inserts", 8));
      cntx.put( ISCConstants.isc_info_update_count,   new StatValue(CounterType.DBOP, "updates", 8));
      cntx.put( ISCConstants.isc_info_delete_count,   new StatValue(CounterType.DBOP, "deletes", 8));
      cntx.put( ISCConstants.isc_info_backout_count,  new StatValue(CounterType.DBOP, "backouts",8));
      cntx.put( ISCConstants.isc_info_purge_count,    new StatValue(CounterType.DBOP, "purges",  8));
      cntx.put( ISCConstants.isc_info_expunge_count,  new StatValue(CounterType.DBOP, "expunges",8));

      cntx.put( ISCConstants.isc_info_end,     new StatValue(CounterType.DUMMY, "end"));
      
      byte[] items = new byte[ cntx.size() ];
      int ii=0, buf_total=2;
      for(Integer k : cntx.keySet()) {
         items[ii++] = k.byteValue();
         buf_total += 8;
      }
      //System.out.println(cntx);
      //System.out.println("buf_total="+buf_total);
      
      int len;
      byte item;
      short i;
      byte[] dbstat; // stores result values of counters
      int prevVal, currVal, tabsIdx;
      while(true) {
        try {  
            out.println(df.format( System.currentTimeMillis() )+" trying to gather db statistics. . .");
            dbstat=gdso.iscDatabaseInfo(dh, items, buf_total);
            out.print(df.format( System.currentTimeMillis() )+" statistics gathered, dbstat[] length="+dbstat.length);
            //out.println(dbstat[0]);
            String dts;
            i=0;
            while( dbstat[i]!=ISCConstants.isc_info_end) { 
                // API guide: A one-byte item return type. 
                // There are compile-time constants defined for all the item return
                // types in ibase.h (inf_pub.h ?)
                item=dbstat[i++];
                // API guide:  A two-byte number specifying the number of bytes that 
                // follow in the remainder of the cluster.          
                len = gdso.iscVaxInteger(dbstat, i, 2);
                i+=2;
                dts=df.format( System.currentTimeMillis() );
                if  (cntx.containsKey( (int)item) ) {
                    if ( cntx.get((int)item).cType == CounterType.DBOP ) {
                        // API Guide, pg 56 (Database operation counts):
                        // values are calculated on a per-table basis <...>
                        // 2 bytes specifying the table ID
                        // 4 bytes listing the number of operations (for example, inserts)
                        // done on that table.

                  // WHY len > 0  only for TWO relations (RDB$DATABASE and RDB$RELATIONS)
                        if (len > 0) { // ==> len = 2+4 (rel_id, counter_value)
                            out.println();
                            tabsIdx=dbstat[i]; ///gdso.iscVaxInteger(dbstat, i, 2);
                            out.print(   dts+"\t\t"+cntx.get((int)item).title+", len="+len+", tabsIdx="+tabsIdx);
                            out.print("\trel_id="+gdso.iscVaxInteger(dbstat, i, 2));
                            out.print("\ttab_name="+tabs.get(gdso.iscVaxInteger(dbstat, i, 2)) );
                            out.print("\tcounter="+gdso.iscVaxInteger(dbstat, i+2, len-2)); // why counter value ALWAYS =1 ???
                            //out.print("\tcounter="+dbstat[i+2]+" "+dbstat[i+3]+" "+dbstat[i+4]+" "+dbstat[i+5] );
                        }
                    }
                    if ( cntx.get((int)item).cType == CounterType.PERF ) { // this case works OK
                        out.println();
                        prevVal = cntx.get((int)item).val;
                        currVal = gdso.iscVaxInteger(dbstat, i, len);

                        out.print(   dts+"\t\t"+cntx.get((int)item).title+" "
                                     +String.format( "%12d; diff: %12d", 
                                                     currVal, 
                                                     prevVal != 0 ? currVal - prevVal : -1
                                                   ) 
                                );
                        cntx.get( (int)item).val = currVal;
                    }
                }

                i+=len;
            }
            out.println("\n");
exit(0);            
            Thread.sleep(5000);

        } catch (GDSException e)   {
            break;
        }
      } // while (true)

      out.println ("\n"+df.format( System.currentTimeMillis() )+" detaching from database. . .");
      gdso.iscDetachDatabase(dh);
      out.println (df.format( System.currentTimeMillis() )+" detached.");
    }
    catch (GDSException e) {
        out.println(df.format( System.currentTimeMillis() )+" EXCEPTION: SQLSTATE="+e.getSQLState()+", fb_error="+e.getFbErrorCode()+", msg="+e.getMessage());
    }
    catch (SQLException e) {
        e.printStackTrace();
        out.println ("sql exception: " + e.getMessage ());
    }    
  }
  
  static class StatValue {
      //int isc_const;
      final String title;
      final CounterType cType;
      int val=0;
      
      StatValue(CounterType c, String s) { title=s; cType = c; }
      StatValue(CounterType c, String s, int rPad) { title=String.format("%1$-" + rPad + "s", s); cType = c; }
      
      @Override
      public String toString() { return title; }
      // contract for Map or Set:
      @Override
      public boolean equals(Object o) {
          return (o instanceof StatValue)
                  && ((StatValue)o).title.equalsIgnoreCase(title);
      }
     
      @Override
      public int hashCode() {
          return title.hashCode();
      }
  }
}