&ANALYZE-SUSPEND _VERSION-NUMBER AB_v10r12
&ANALYZE-RESUME
&ANALYZE-SUSPEND _UIB-CODE-BLOCK _CUSTOM _DEFINITIONS Procedure 
/*------------------------------------------------------------------------
    File        : 
    Purpose     :

    Syntax      :

    Description :

    Author(s)   :
    Created     :
    Notes       :
  ----------------------------------------------------------------------*/
/*          This .W file was created with the Progress AppBuilder.      */
/*----------------------------------------------------------------------*/
/*------------------------------------------------------------------------------------------------

Copyright 2015 The Mad DBA

This program is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

---------------------------------------------------------------------------------------------------*/
/* ***************************  Definitions  ************************** */
/*--- Temp table definitions ---*/
 DEFINE TEMP-TABLE ttIssue NO-UNDO
    FIELD IssueNumber AS INTEGER
    FIELD IssueGroup  AS CHARACTER
    FIELD Issue       AS CHARACTER
    FIELD ShortDescr  AS CHARACTER
    FIELD Severity    AS CHARACTER
    FIELD DetailLink  AS CHARACTER
  INDEX IdxMain IS UNIQUE IssueNumber.

 DEFINE TEMP-TABLE ttBlock NO-UNDO
    FIELD BlockNumber AS DECIMAL
   INDEX IdxMain IS UNIQUE BlockNumber.

 DEFINE TEMP-TABLE ttArea NO-UNDO
    FIELD AreaNumber     AS INTEGER
    FIELD AreaName       AS CHARACTER 
    FIELD AreaVersion    AS INTEGER
    FIELD AreaRPB        AS INTEGER
    FIELD AreaBlockSize  AS INTEGER
    FIELD AreaBufferPool AS INTEGER
   INDEX IdxMain IS UNIQUE AreaNumber.

 DEFINE TEMP-TABLE ttTable NO-UNDO
    FIELD TableNumber       AS INTEGER
    FIELD TableName         AS CHARACTER 
    FIELD AreaNumber        AS INTEGER
    FIELD IsTracked         AS LOGICAL
    FIELD RecordCount       AS INTEGER
    FIELD RecordSize        AS INTEGER
    FIELD RPBPerformance    AS INTEGER
    FIELD RPBCalc           AS INTEGER
    FIELD OptRPBPerformance AS INTEGER
    FIELD OptRPBCalc        AS INTEGER
    FIELD ReadPercent       AS DECIMAL
    FIELD TotalReads        AS DECIMAL
    FIELD TotalUpdates      AS DECIMAL
    FIELD TotalCreates      AS DECIMAL
    FIELD TotalDeletes      AS DECIMAL
    FIELD TotalActivity     AS DECIMAL
    FIELD OSReads           AS DECIMAL
    FIELD HitRatio          AS DECIMAL
    FIELD PctReads          AS DECIMAL
    FIELD PctUpdates        AS DECIMAL
    FIELD PctCreates        AS DECIMAL
    FIELD PctDeletes        AS DECIMAL
    FIELD PctDBActivity     AS DECIMAL
    FIELD PctDBReads        AS DECIMAL
    FIELD PctDBUpdates      AS DECIMAL
    FIELD PctDBCreates      AS DECIMAL
    FIELD PctDBDeletes      AS DECIMAL
    FIELD BufferPool        AS INTEGER
    FIELD B2Candidate       AS LOGICAL
    FIELD MoveCandidate     AS LOGICAL
    FIELD DistinctBlocks    AS INTEGER
    FIELD OptCurrentBlocks  AS DECIMAL
    FIELD Opt8KBlocks       AS DECIMAL
    FIELD FileRecid         AS RECID
    FIELD Fragments         AS DECIMAL
    FIELD FragFactor        AS DECIMAL
    FIELD ScatterFactor     AS DECIMAL
  INDEX IdxMain IS UNIQUE TableNumber.

 DEFINE TEMP-TABLE ttIndex NO-UNDO
    FIELD TableNumber      AS INTEGER
    FIELD IndexNumber      AS INTEGER
    FIELD IndexName        AS CHARACTER 
    FIELD IsWord           AS LOGICAL
    FIELD IsUnique         AS LOGICAL
    FIELD AreaNumber       AS INTEGER
    FIELD IsTracked        AS LOGICAL
    FIELD ReadPercent      AS DECIMAL
    FIELD TotalReads       AS DECIMAL
    FIELD TotalCreates     AS DECIMAL
    FIELD TotalDeletes     AS DECIMAL
    FIELD TotalActivity    AS DECIMAL
    FIELD OSReads          AS DECIMAL
    FIELD HitRatio         AS DECIMAL
    FIELD PctReads         AS DECIMAL
    FIELD PctCreates       AS DECIMAL
    FIELD PctDeletes       AS DECIMAL
    FIELD PctDBActivity    AS DECIMAL
    FIELD PctDBReads       AS DECIMAL
    FIELD PctDBUpdates     AS DECIMAL
    FIELD PctDBCreates     AS DECIMAL
    FIELD PctDBDeletes     AS DECIMAL
    FIELD BufferPool       AS INTEGER
    FIELD B2Candidate      AS LOGICAL
    FIELD MoveCandidate    AS LOGICAL
    FIELD IndexBlocks      AS DECIMAL
    FIELD IndexUtil        AS DECIMAL
    FIELD IndexFactor      AS DECIMAL
    FIELD RebuildCandidate AS LOGICAL
  INDEX IdxMain IS UNIQUE IndexNumber.

/*--- Define Variables ---*/
 DEFINE VARIABLE iIssueNumber      AS INTEGER NO-UNDO.
 DEFINE VARIABLE MaxTableNumber    AS INTEGER NO-UNDO.
 DEFINE VARIABLE MaxIndexNumber    AS INTEGER NO-UNDO.
 DEFINE VARIABLE iTrueRecords      AS INTEGER NO-UNDO.
 DEFINE VARIABLE iBlocks           AS INTEGER NO-UNDO.
 DEFINE VARIABLE HaveTypeOneTables AS LOGICAL NO-UNDO.

/*--- DB Section Variables ---*/
 DEFINE VARIABLE DBReads          AS DECIMAL   NO-UNDO.
 DEFINE VARIABLE DBUpdates        AS DECIMAL   NO-UNDO.
 DEFINE VARIABLE DBCreates        AS DECIMAL   NO-UNDO.
 DEFINE VARIABLE DBDeletes        AS DECIMAL   NO-UNDO.
 DEFINE VARIABLE DBActivity       AS DECIMAL   NO-UNDO.
 DEFINE VARIABLE IndexReads       AS DECIMAL   NO-UNDO.
 DEFINE VARIABLE IndexCreates     AS DECIMAL   NO-UNDO.
 DEFINE VARIABLE IndexDeletes     AS DECIMAL   NO-UNDO.
 DEFINE VARIABLE DBBlockSize      AS INTEGER   NO-UNDO.
 DEFINE VARIABLE UntrackedTables  AS INTEGER   NO-UNDO.
 DEFINE VARIABLE UntrackedIndexes AS INTEGER   NO-UNDO.
 DEFINE VARIABLE HaveDBAnalys     AS LOGICAL   NO-UNDO.
 DEFINE VARIABLE HaveOSReads      AS LOGICAL   NO-UNDO.
 DEFINE VARIABLE RebuildIndexes   AS INTEGER   NO-UNDO.
 DEFINE VARIABLE MoveTables       AS INTEGER   NO-UNDO.
 DEFINE VARIABLE MoveIndexes      AS INTEGER   NO-UNDO.
 DEFINE VARIABLE DBUptime         AS DECIMAL   NO-UNDO.
 DEFINE VARIABLE NumAPWs          AS INTEGER   NO-UNDO.
 DEFINE VARIABLE Spin             AS INTEGER   NO-UNDO.
 DEFINE VARIABLE DBBuffers        AS INTEGER   NO-UNDO.
 DEFINE VARIABLE DBAltBuffers     AS INTEGER   NO-UNDO.

/*--- AI Variables ---*/
 DEFINE VARIABLE AIEnabled   AS LOGICAL NO-UNDO.
 DEFINE VARIABLE AIWEnabled  AS LOGICAL NO-UNDO.
 DEFINE VARIABLE AIBlockSize AS INTEGER NO-UNDO.
 DEFINE VARIABLE AIBuffers   AS INTEGER NO-UNDO.
 DEFINE VARIABLE AIWaits     AS INTEGER NO-UNDO.

/*--- BI Variables ---*/
 DEFINE VARIABLE BIWEnabled           AS LOGICAL NO-UNDO.
 DEFINE VARIABLE BIBlockSize          AS INTEGER NO-UNDO.
 DEFINE VARIABLE BIClusterSize        AS INTEGER NO-UNDO.
 DEFINE VARIABLE BIBuffers            AS INTEGER NO-UNDO.
 DEFINE VARIABLE BIWaits              AS INTEGER NO-UNDO.
 DEFINE VARIABLE DBFlushed            AS DECIMAL NO-UNDO.
 DEFINE VARIABLE DBCheckPoints        AS DECIMAL NO-UNDO.
 DEFINE VARIABLE FlushedPerCheckPoint AS DECIMAL NO-UNDO.

/*--- Define Streams ---*/
 DEFINE STREAM stFile.

/*--- Define Parameters ----*/
 DEFINE INPUT PARAMETER ipDBAnalys   AS CHARACTER NO-UNDO.
 DEFINE INPUT PARAMETER ipFileName   AS CHARACTER NO-UNDO.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME


&ANALYZE-SUSPEND _UIB-PREPROCESSOR-BLOCK 

/* ********************  Preprocessor Definitions  ******************** */

&Scoped-define PROCEDURE-TYPE Procedure
&Scoped-define DB-AWARE no



/* _UIB-PREPROCESSOR-BLOCK-END */
&ANALYZE-RESUME


/* ************************  Function Prototypes ********************** */

&IF DEFINED(EXCLUDE-fn_AI) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD fn_AI Procedure 
FUNCTION fn_AI RETURNS LOGICAL PRIVATE
  ( /* parameter-definitions */ )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_AIBlockSize) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD fn_AIBlockSize Procedure 
FUNCTION fn_AIBlockSize RETURNS LOGICAL PRIVATE
  ( /* parameter-definitions */ )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_AIBuffers) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD fn_AIBuffers Procedure 
FUNCTION fn_AIBuffers RETURNS LOGICAL PRIVATE
  ( /* parameter-definitions */ )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_AIWriter) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD fn_AIWriter Procedure 
FUNCTION fn_AIWriter RETURNS LOGICAL PRIVATE
  ( /* parameter-definitions */ )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_APWs) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD fn_APWs Procedure 
FUNCTION fn_APWs RETURNS LOGICAL PRIVATE
  ( /* parameter-definitions */ )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_B2) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD fn_B2 Procedure 
FUNCTION fn_B2 RETURNS LOGICAL PRIVATE
  ( /* parameter-definitions */ )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_BIBlockSize) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD fn_BIBlockSize Procedure 
FUNCTION fn_BIBlockSize RETURNS LOGICAL PRIVATE
  ( /* parameter-definitions */ )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_BIBuffers) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD fn_BIBuffers Procedure 
FUNCTION fn_BIBuffers RETURNS LOGICAL PRIVATE
  ( /* parameter-definitions */ )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_BIWriter) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD fn_BIWriter Procedure 
FUNCTION fn_BIWriter RETURNS LOGICAL PRIVATE
  ( /* parameter-definitions */ )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_Ceil) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD fn_Ceil Procedure 
FUNCTION fn_Ceil RETURNS INTEGER PRIVATE
  ( INPUT ip_Decimal AS DECIMAL )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_ConvertSeconds) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD fn_ConvertSeconds Procedure 
FUNCTION fn_ConvertSeconds RETURNS CHARACTER PRIVATE
  ( INPUT ip_Seconds AS DECIMAL) FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_DBBlockSize) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD fn_DBBlockSize Procedure 
FUNCTION fn_DBBlockSize RETURNS LOGICAL PRIVATE
  ( /* parameter-definitions */ )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_DBSpin) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD fn_DBSpin Procedure 
FUNCTION fn_DBSpin RETURNS LOGICAL PRIVATE
  ( /* parameter-definitions */ )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_DBUpTime) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD fn_DBUpTime Procedure 
FUNCTION fn_DBUpTime RETURNS LOGICAL PRIVATE
  ( /* parameter-definitions */ )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_FieldList) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD fn_FieldList Procedure 
FUNCTION fn_FieldList RETURNS CHARACTER PRIVATE
  (  )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_Header) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD fn_Header Procedure 
FUNCTION fn_Header RETURNS LOGICAL PRIVATE
  ( INPUT ip_Text   AS CHARACTER,
    INPUT ip_Header AS INTEGER,
    INPUT ip_ID     AS CHARACTER )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_HTMLHeader) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD fn_HTMLHeader Procedure 
FUNCTION fn_HTMLHeader RETURNS LOGICAL PRIVATE
  ( /* parameter-definitions */ )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_IndexActivity) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD fn_IndexActivity Procedure 
FUNCTION fn_IndexActivity RETURNS LOGICAL PRIVATE
  ( /* parameter-definitions */ )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_IndexMoves) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD fn_IndexMoves Procedure 
FUNCTION fn_IndexMoves RETURNS LOGICAL PRIVATE
  ( /* parameter-definitions */ )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_IndexReads) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD fn_IndexReads Procedure 
FUNCTION fn_IndexReads RETURNS LOGICAL PRIVATE
  ( /* parameter-definitions */ )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_IndexRebuilds) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD fn_IndexRebuilds Procedure 
FUNCTION fn_IndexRebuilds RETURNS LOGICAL PRIVATE
  ( /* parameter-definitions */ )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_Issue) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD fn_Issue Procedure 
FUNCTION fn_Issue RETURNS LOGICAL PRIVATE
  ( INPUT ip_Group    AS CHARACTER,
    INPUT ip_Issue    AS CHARACTER,
    INPUT ip_Descr    AS CHARACTER,
    INPUT ip_Severity AS CHARACTER,
    INPUT ip_Link     AS CHARACTER)  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_Pct) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD fn_Pct Procedure 
FUNCTION fn_Pct RETURNS DECIMAL PRIVATE
  ( INPUT ip_Dec1 AS DECIMAL,
    INPUT ip_Dec2 AS DECIMAL )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_ProTop) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD fn_ProTop Procedure 
FUNCTION fn_ProTop RETURNS CHARACTER PRIVATE
  ( /* parameter-definitions */ )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_Query) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD fn_Query Procedure 
FUNCTION fn_Query RETURNS LOGICAL PRIVATE
  ( INPUT ip_TableName AS CHARACTER,
    INPUT ip_Where     AS CHARACTER,
    INPUT ip_Sort      AS CHARACTER,
    INPUT ip_First     AS LOGICAL,
    INPUT ip_Handler   AS CHARACTER
    )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_TableActivity) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD fn_TableActivity Procedure 
FUNCTION fn_TableActivity RETURNS LOGICAL PRIVATE
  ( /* parameter-definitions */ )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_TableMoves) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD fn_TableMoves Procedure 
FUNCTION fn_TableMoves RETURNS LOGICAL PRIVATE
  ( /* parameter-definitions */ )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_TableReads) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD fn_TableReads Procedure 
FUNCTION fn_TableReads RETURNS LOGICAL PRIVATE
  ( /* parameter-definitions */ )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_UntrackedIndexes) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD fn_UntrackedIndexes Procedure 
FUNCTION fn_UntrackedIndexes RETURNS LOGICAL PRIVATE
  ( /* parameter-definitions */ )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_UntrackedTables) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD fn_UntrackedTables Procedure 
FUNCTION fn_UntrackedTables RETURNS LOGICAL PRIVATE
  ( /* parameter-definitions */ )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_UpdateVSTs) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD fn_UpdateVSTs Procedure 
FUNCTION fn_UpdateVSTs RETURNS LOGICAL PRIVATE
  ( /* parameter-definitions */ )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF


/* *********************** Procedure Settings ************************ */

&ANALYZE-SUSPEND _PROCEDURE-SETTINGS
/* Settings for THIS-PROCEDURE
   Type: Procedure
   Allow: 
   Frames: 0
   Add Fields to: Neither
   Other Settings: CODE-ONLY
 */
&ANALYZE-RESUME _END-PROCEDURE-SETTINGS

/* *************************  Create Window  ************************** */

&ANALYZE-SUSPEND _CREATE-WINDOW
/* DESIGN Window definition (used by the UIB) 
  CREATE WINDOW Procedure ASSIGN
         HEIGHT             = 15
         WIDTH              = 60.
/* END WINDOW DEFINITION */
                                                                        */
&ANALYZE-RESUME

 


&ANALYZE-SUSPEND _UIB-CODE-BLOCK _CUSTOM _MAIN-BLOCK Procedure 


/* ***************************  Main Block  *************************** */

/*--- Check a few things out ---*/
 IF NUM-DBS = 0 THEN DO:
   MESSAGE "You must have a database connected." VIEW-AS ALERT-BOX ERROR.
   RETURN.
 END.
 
 IF NUM-DBS > 1 THEN DO:
   MESSAGE "Only one database connection is supported." VIEW-AS ALERT-BOX ERROR.
   RETURN.
 END.

 IF ipFileName = "" THEN DO:
   MESSAGE "Output file name is required!" VIEW-AS ALERT-BOX ERROR.
   RETURN.
 END.

 IF ipDBAnalys = "" OR SEARCH(ipDBAnalys) = ? THEN
    MESSAGE "You haven't supplied a dbanalys file. Some metrics will not be available."
       VIEW-AS ALERT-BOX WARNING.
 ELSE
    ASSIGN HaveDBAnalys = TRUE.

/*--- Get the DB Activity information ---*/
 fn_Query ("_ActSummary","","",TRUE,"ip_ProcessDBActivity").

/*--- Get the Index Activity information ---*/
 fn_Query ("_ActIndex","","",TRUE,"ip_ProcessIndexActivity").

/*--- Get information about the BI and AI ---*/
 fn_Query ("_Logging","","",TRUE,"ip_ProcessLogging").
 fn_Query ("_ActAILog","","",TRUE,"ip_ProcessAILog").
 fn_Query ("_ActBILog","","",TRUE,"ip_ProcessBILog").

/*--- Get information about which background writers are running ---*/
 fn_Query ("_Connect","","",FALSE,"ip_ProcessConnect").

/*--- Get the startup parameters we care about (Change for 11.5) ---*/
 fn_Query ("_Startup","","",TRUE,"ip_ProcessStartup").

/*--- Get the area information ---*/
 fn_Query ("_Area","","",FALSE,"ip_ProcessArea").
               
/*--- Get the basic table information... also builds index list---*/
 fn_Query ("_File"," FIELDS(_File._File-Number _File._File-Name) WHERE _File._Tbl-Type = 'T'",
           "",FALSE,"ip_ProcessTable").
                                                               
/*--- Get the table storage area information ---*/
 fn_Query ("_StorageObject","WHERE _storageobject._object-type = 1","",FALSE,"ip_ProcessTableObject").

/*--- Get the index storage area information ---*/
 fn_Query ("_StorageObject","WHERE _storageobject._object-type = 2","",FALSE,"ip_ProcessIndexObject").

/*--- Get the table stats ---*/
 fn_Query ("_TableStat","","",FALSE,"ip_ProcessTableStat").

/*--- Get the index stats ---*/
 fn_Query ("_IndexStat","","",FALSE,"ip_ProcessIndexStat").

/*--- Did we get a DBAnalys file? ---*/
 IF ipDBAnalys <> "" THEN DO:
   RUN ip_ProcessTableAnalys.
   RUN ip_ProcessIndexAnalys.
 END.

/*--- Which issues did we find? ---*/
 RUN ip_CheckIssues.

/*--- Output the HTML ----*/
 RUN ip_GenerateHTML.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME


/* **********************  Internal Procedures  *********************** */

&IF DEFINED(EXCLUDE-ip_CheckIssues) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE ip_CheckIssues Procedure 
PROCEDURE ip_CheckIssues PRIVATE :
/*------------------------------------------------------------------------------
  Purpose:     
  Parameters:  <none>
  Notes:       
------------------------------------------------------------------------------*/
/*--- Is AI running? ----*/
 IF AIEnabled = FALSE THEN
  fn_Issue("After Imaging",
           "After Imaging is not enabled",
           "After Imaging helps prevent data loss between database backups and should be running for all production databases.",
           "HIGH",
           "AI").

 IF AIEnabled = TRUE AND AIWEnabled = FALSE THEN  
  fn_Issue("After Imaging",
           "AI Writer is not running",
           "The AI Writer helps offload work from other processes for writes to the AI files.",
           "HIGH",
           "AIWriter").

 IF AIEnabled = TRUE AND AIWaits > 1000 AND AIBlockSize < 16 THEN  
  fn_Issue("After Imaging",
           "AI Block Size is set to " + STRING(AIBlockSize),
           "An AI block size of 16k (16384) usually results in better performance.",
           "Information",
           "AIBlockSize").

 IF AIEnabled = TRUE AND AIWaits > 1000 AND AIBuffers < 400 THEN   
  fn_Issue("After Imaging",
           "AI Buffers is set to " + STRING(AIBuffers),
           "Increase the size of the aibufs startup parameter.",
           "HIGH",
           "AIBuffers").

/*--- Before Image Section ---*/
 IF BIWEnabled = FALSE THEN
   fn_Issue("Before Image",
            "BI Writer is not running",
            "The BI Writer helps offload work from other processes for writes to the BI files.",
            "HIGH",
            "BIWriter").
 
 IF BIWaits > 1000 AND BIBuffers < 400 THEN    
  fn_Issue("Before Image",
           "BI Buffers is set to " + STRING(BIBuffers),
           "Increase the size of the bibufs startup parameter.",
           "HIGH",
           "BIBuffers").
 
 IF BIBlockSize < 16 AND BIWaits > 1000 THEN
  fn_Issue("Before Image",
           "BI Block Size is set to " + STRING(BIBlockSize),
           "A BI block size of 16k (16384) usually results in better performance.",
           "Warning",
           "BIBlockSize").

 IF FlushedPerCheckpoint >= 10 THEN
   fn_Issue("Before Image",
            "Buffers flushed per checkpoint is " +
            STRING(FlushedPerCheckPoint,">>>,>>>,>>>,>>9"),
            "Excessive buffers flsuhed can cause serious performance issues, usually resulting in reports of the " +
            "system hanging or freezing.",
            "HIGH",
            "BICheckPoints").

 ELSE IF DBFLushed > 0 THEN
     fn_Issue("Before Image",
              "Buffers flushed during checkpoints is " +
              STRING(DBFlushed,">>>,>>>,>>>,>>9"),
              "When buffers are flushed at checkpoint this causes write activity to the database to stall until all" +
              " flushed buffers are written to disk.",
              "Warning",
              "BICheckPoints").

/*---- DB Section ----*/
 IF DBAltBuffers = 0 THEN  
   fn_Issue("Alternate Buffer Pool",
            "Alternate buffer pool not enabled",
            "The alternate buffer allows you to keep frequently read tables and " +
            "indexes in memory instead of reading them from disk.",
            "Warning",
            "B2").
 

/*--- How many table issues do we have? ---*/
 FOR EACH ttTable:

    IF ttTable.IsTracked = FALSE THEN
      ASSIGN UntrackedTables = UntrackedTables + 1.

    IF ttTable.MoveCandidate = TRUE THEN
      ASSIGN MoveTables = MoveTables + 1.

    /*--- How many index issues do we have? ---*/
     FOR EACH ttIndex OF ttTable:
        
        IF ttIndex.IsTracked = FALSE THEN
          ASSIGN UntrackedIndexes = UntrackedIndexes + 1.
        
        IF ttIndex.MoveCandidate = TRUE THEN
          ASSIGN MoveIndexes = MoveIndexes + 1.
     
        IF ttIndex.RebuildCandidate = TRUE THEN
          ASSIGN RebuildIndexes = RebuildIndexes + 1.
     END.

 END. /*--- EACH ttTable ---*/
 

 IF NumAPWs = 0 THEN
   fn_Issue("Database",
            "Database Writers not running",
            "Database Writers (APWs) help improve performance by offloading database writes from other sessions.",
            "HIGH",
            "APWs").

 IF Spin = 0 THEN 
    fn_Issue("Database",
             "The spin parameter is not set",
             "Spin helps reduce wait times by letting processes retry for database resources instead of sleeping.",
             "HIGH",
             "DBSpin").

 IF DBUptime < 432000 THEN
   fn_Issue("Database",
            "Database Uptime: " + fn_ConvertSeconds(DBUptime),
            "Longer samples will generally provide better results from this tool. A minimum of 5 business days is recommended.",
            "Warning",
            "DBUpTime").

 IF HaveDBAnalys = FALSE THEN
    fn_Issue("Database",
             "Database Analysis Missing",
             "Without a database analysis file several key metrics cannot be evaluated. " +
             "Including the checks to see if your storage area setup is optimal and which indexes " +
             "need to be rebuilt. These values are also taken into account when trying to decide "  + 
             "which tables can benefit from being placed in the alternate buffer pool.",
             "Warning",
             "DBAnalysis").

 IF DBBlockSize <> 8 THEN
   fn_Issue("Database",
            "Database Block Size is " + STRING(DBBlockSize) + "K",
            "An 8K block size is highly recommended for most databases.",
            "Warning",
            "DBBlockSize").

 IF UntrackedTables > 0 THEN
   fn_Issue("Database",
            STRING(UntrackedTables,">>,>>9") + " Untracked Table" +
            (IF UntrackedTables > 1 THEN "s" ELSE ""),
            "Unless all tables are enabled for detailed tracking some of the suggestions will be incomplete and " +
            "misleading.",
            "HIGH",
            "UntrackedTables").

 IF UntrackedIndexes > 0 THEN
    fn_Issue("Database",
             STRING(UntrackedIndexes,">>,>>9") + " Untracked Index" +
             (IF UntrackedIndexes > 1 THEN "es" ELSE ""),
             "Unless all indexes are enabled for detailed tracking some of the suggestions will be incomplete and " +
             "misleading.",
             "HIGH",
             "UntrackedIndexes").
 
 IF HaveOSReads = FALSE AND CAN-FIND(FIRST ttArea WHERE ttArea.AreaVersion = 2) THEN 
    fn_Issue("Database",
             "Disk read tracking not supported",
             "The virtual system tables that track disk reads per table need to be updated in your database.",
             "HIGH",
             "UpdateVSTs").

 IF MoveTables > 0 THEN
   fn_Issue("Database",
            STRING(MoveTables,">>,>>9") + " Table" +
            (IF MoveTables > 1 THEN "s" ELSE "") + " with storage issues",
            "Some of your tables are either in Type I areas and/or do not have proper record per " +
            "block settings.",
            "HIGH",
            "TableMoves").

 IF MoveIndexes > 0 THEN
   fn_Issue("Database",
            STRING(MoveIndexes,">>,>>9") + " Index" +
            (IF MoveIndexes > 1 THEN "es" ELSE "") + " with storage issues",
            "Some of your indexes are in Type I areas.",
            "HIGH",
            "IndexMoves").

 IF RebuildIndexes > 0 THEN 
   fn_Issue("Database",
            STRING(RebuildIndexes,">>,>>9") + " Index" +
            (IF RebuildIndexes > 1 THEN "es" ELSE "") + " flagged for rebuild",
            "Some of your indexes need to be rebuilt or compacted to improve performance.",
            "HIGH",
            "IndexRebuilds").

/*--- General Information that we always put out ---*/
 fn_Issue("DB Activity",
          "Table Reads",
          "Detailed information on which tables are " +
          "the most active for reads.",
          "Information",
          "TableReads").

  fn_Issue("DB Activity",
          "Index Reads",
          "Detailed information on which indexes are " +
          "the most active for reads.",
          "Information",
          "IndexReads").

 fn_Issue("DB Activity",
          "Table Activity",
          "Detailed information on which tables are " +
          "the most active.",
          "Information",
          "TableActivity").
 
 fn_Issue("DB Activity",
          "Index Activity",
          "This section provides detailed information on which indexes are " +
          "the most active for your database.",
          "Information",
          "IndexActivity").

END PROCEDURE.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-ip_GenerateHTML) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE ip_GenerateHTML Procedure 
PROCEDURE ip_GenerateHTML PRIVATE :
/*------------------------------------------------------------------------------
  Purpose:     
  Parameters:  <none>
  Notes:       
------------------------------------------------------------------------------*/
 OUTPUT STREAM stFile TO VALUE(ipFileName).

/*--- Header ---*/
 fn_HTMLHeader().
 
/*--- Output the issues ----*/
 fn_Header("Database Advisor Findings",2,"TOP"). 

 PUT STREAM stFile UNFORMATTED 
    "<div>"
    "The OpenEdge Database Advisor is intended to give you a head start on identifying "
    "the most common database issues. The suggestions below are based on best practices "
    "and years of experience tuning numerous OpenEdge deployments of various sizes. There is "
    "also an assumption that you have at least a basic knowledge of database administration "
    "but does not assume you are a tuning expert.<br><br>"
    "This tool is not intended as a replacement for qualifed help or as a real time performance monitoring "
    "tool, but it will help you resolve some of the most common issues before you start digging "
    "into the details required to fully tune your system. "
    "After you have resolved some of the common issues I highly recommend that you download and install " 
    fn_Protop() 
    ". It will provide much more detailed information as well as provide the real time "
    "monitoring required to analyze your system during peak times.<br><br>"
    "</div>" SKIP.
     

 PUT STREAM stFile UNFORMATTED 
    "<table>" SKIP
    "<tr>" SKIP
    "<th>Severity</th>" SKIP
    "<th>Category</th>" SKIP
    "<th>Issue</th>" SKIP
    "<th>Description</th>"
    "</tr>" SKIP.

 FOR EACH ttIssue:

     PUT STREAM stFile UNFORMATTED 
        "<tr>" SKIP
        "<td>" "<a href=#" ttIssue.DetailLink ">" ttIssue.Severity   "</a></td>" SKIP
        "<td>" ttIssue.IssueGroup "</td>" SKIP
        "<td>" ttIssue.Issue "</td>" SKIP
        "<td>" ttIssue.ShortDescr "</td>" SKIP
        "</tr>" SKIP.

 END. /*--- EACH ttIssue ---*/

 PUT STREAM stFile UNFORMATTED "</table>" SKIP.

/*--- Run the detail sections ---*/
 FOR EACH ttIssue:
    DYNAMIC-FUNCTION("fn_" + ttIssue.DetailLink) NO-ERROR.
 END. /*--- EACH ttIssue ---*/

/*--- Trailer ---*/
 PUT STREAM stFile UNFORMATTED
    "</div>"  SKIP
    "</body>" SKIP
    "</html>" SKIP.
 OUTPUT STREAM stFile CLOSE.

END PROCEDURE.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-ip_ProcessAILog) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE ip_ProcessAILog Procedure 
PROCEDURE ip_ProcessAILog PRIVATE :
/*------------------------------------------------------------------------------
  Purpose:     
  Parameters:  <none>
  Notes:       
------------------------------------------------------------------------------*/
 DEFINE INPUT PARAMETER ip_Handle AS HANDLE NO-UNDO.

 ASSIGN AIWaits = ip_Handle:BUFFER-FIELD("_AiLog-BBuffWaits"):BUFFER-VALUE NO-ERROR.

END PROCEDURE.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-ip_ProcessArea) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE ip_ProcessArea Procedure 
PROCEDURE ip_ProcessArea PRIVATE :
/*------------------------------------------------------------------------------
  Purpose:     
  Parameters:  <none>
  Notes:       
------------------------------------------------------------------------------*/

 DEFINE INPUT PARAMETER ip_Handle AS HANDLE NO-UNDO.
 
 CREATE ttArea.
 ASSIGN ttArea.AreaNumber     = ip_Handle:BUFFER-FIELD("_Area-Number"):BUFFER-VALUE
        ttArea.AreaName       = ip_Handle:BUFFER-FIELD("_Area-Name"):BUFFER-VALUE
        ttArea.AreaVersion    = (IF ip_Handle:BUFFER-FIELD("_Area-Version"):BUFFER-VALUE = 7 THEN 2 ELSE 1)
        ttArea.AreaRPB        = EXP(2,ip_Handle:BUFFER-FIELD("_Area-RecBits"):BUFFER-VALUE)
        ttArea.AreaBlockSize  = ip_Handle:BUFFER-FIELD("_Area-Blocksize"):BUFFER-VALUE / 1024
        ttArea.AreaBufferPool = (GET-BITS(ip_Handle:BUFFER-FIELD("_Area-Attrib"):BUFFER-VALUE,7,1) + 1).
                                                                                                   
IF DBBlockSize = 0 THEN
  ASSIGN DBBlockSize = ttArea.AreaBlockSize.
                                                                                                         
END PROCEDURE.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-ip_ProcessBILog) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE ip_ProcessBILog Procedure 
PROCEDURE ip_ProcessBILog PRIVATE :
/*------------------------------------------------------------------------------
  Purpose:     
  Parameters:  <none>
  Notes:       
------------------------------------------------------------------------------*/
 DEFINE INPUT PARAMETER ip_Handle AS HANDLE NO-UNDO.

  ASSIGN BIWaits = ip_Handle:BUFFER-FIELD("_BiLog-BBuffWaits"):BUFFER-VALUE NO-ERROR.

END PROCEDURE.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-ip_ProcessBlock) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE ip_ProcessBlock Procedure 
PROCEDURE ip_ProcessBlock PRIVATE :
/*------------------------------------------------------------------------------
  Purpose:     
  Parameters:  <none>
  Notes:       
------------------------------------------------------------------------------*/
 DEFINE INPUT PARAMETER ip_Handle AS HANDLE NO-UNDO.

 DEFINE VARIABLE iDBBlock AS INTEGER NO-UNDO.

 ASSIGN iDBBlock     = ip_Handle:RECID
        iDBBlock     = TRUNCATE(iDBBlock / ttArea.AreaRPB,0)
        iTrueRecords = iTrueRecords + 1.

 FIND ttBlock WHERE
      ttBlock.BlockNumber = iDBBlock NO-ERROR.

 IF NOT AVAILABLE ttBlock THEN DO:
    CREATE ttBlock.
    ASSIGN ttBlock.BlockNumber = iDBBlock
           iBlocks             = iBlocks + 1.
 END.

END PROCEDURE.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-ip_ProcessConnect) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE ip_ProcessConnect Procedure 
PROCEDURE ip_ProcessConnect PRIVATE :
/*------------------------------------------------------------------------------
  Purpose:     
  Parameters:  <none>
  Notes:       
------------------------------------------------------------------------------*/
DEFINE INPUT PARAMETER ip_Handle AS HANDLE NO-UNDO.

IF ip_Handle:BUFFER-FIELD("_Connect-Type"):BUFFER-VALUE = "AIW" THEN
  ASSIGN AIWEnabled = TRUE.

ELSE IF ip_Handle:BUFFER-FIELD("_Connect-Type"):BUFFER-VALUE = "BIW" THEN
  ASSIGN BIWEnabled = TRUE.

ELSE IF ip_Handle:BUFFER-FIELD("_Connect-Type"):BUFFER-VALUE = "APW" THEN
  ASSIGN NumAPWs = NumAPWs + 1.

END PROCEDURE.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-ip_ProcessDBActivity) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE ip_ProcessDBActivity Procedure 
PROCEDURE ip_ProcessDBActivity PRIVATE :
/*------------------------------------------------------------------------------
  Purpose:     
  Parameters:  <none>
  Notes:       
------------------------------------------------------------------------------*/

DEFINE INPUT PARAMETER ip_Handle AS HANDLE NO-UNDO.

ASSIGN DBReads       = ip_Handle:BUFFER-FIELD("_Summary-RecReads"):BUFFER-VALUE
       DBUpdates     = ip_Handle:BUFFER-FIELD("_Summary-RecUpd"):BUFFER-VALUE
       DBCreates     = ip_Handle:BUFFER-FIELD("_Summary-RecCreat"):BUFFER-VALUE
       DBDeletes     = ip_Handle:BUFFER-FIELD("_Summary-RecDel"):BUFFER-VALUE
       DBUptime      = ip_Handle:BUFFER-FIELD("_Summary-Uptime"):BUFFER-VALUE
       DBFlushed     = ip_Handle:BUFFER-FIELD("_Summary-Flushed"):BUFFER-VALUE
       DBCheckpoints = ip_Handle:BUFFER-FIELD("_Summary-Chkpts"):BUFFER-VALUE
     NO-ERROR.

ASSIGN 
   DBActivity           = DBReads + DBUpdates + DBCreates + DBDeletes
   FlushedPerCheckpoint = DBFlushed / DBCheckPoints NO-ERROR.

END PROCEDURE.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-ip_ProcessIndex) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE ip_ProcessIndex Procedure 
PROCEDURE ip_ProcessIndex PRIVATE :
/*------------------------------------------------------------------------------
  Purpose:     
  Parameters:  <none>
  Notes:       
------------------------------------------------------------------------------*/

DEFINE INPUT PARAMETER ip_Handle AS HANDLE NO-UNDO.

CREATE ttIndex.
ASSIGN ttIndex.TableNumber = ttTable.TableNumber
       ttIndex.IndexName   = ip_Handle:BUFFER-FIELD("_Index-Name"):BUFFER-VALUE
       ttIndex.IndexNumber = ip_Handle:BUFFER-FIELD("_Idx-Num"):BUFFER-VALUE
       ttIndex.IsWord      = (ip_Handle:BUFFER-FIELD("_Wordidx"):BUFFER-VALUE <> ?)
       ttIndex.IsUnique    = ip_Handle:BUFFER-FIELD("_Unique"):BUFFER-VALUE
       MaxIndexNumber      = MAX(MaxIndexNumber,ttIndex.IndexNumber).

END PROCEDURE.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-ip_ProcessIndexActivity) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE ip_ProcessIndexActivity Procedure 
PROCEDURE ip_ProcessIndexActivity PRIVATE :
/*------------------------------------------------------------------------------
  Purpose:     
  Parameters:  <none>
  Notes:       
------------------------------------------------------------------------------*/
DEFINE INPUT PARAMETER ip_Handle AS HANDLE NO-UNDO.

ASSIGN IndexReads       = ip_Handle:BUFFER-FIELD("_Index-Find"):BUFFER-VALUE
       IndexCreates     = ip_Handle:BUFFER-FIELD("_Index-Create"):BUFFER-VALUE
       IndexDeletes     = ip_Handle:BUFFER-FIELD("_Index-Delete"):BUFFER-VALUE.

END PROCEDURE.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-ip_ProcessIndexAnalys) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE ip_ProcessIndexAnalys Procedure 
PROCEDURE ip_ProcessIndexAnalys PRIVATE :
/*------------------------------------------------------------------------------
  Purpose:     
  Parameters:  <none>
  Notes:       
------------------------------------------------------------------------------*/

/*--- Define Local Variables ---*/
 DEFINE VARIABLE cTemp    AS CHARACTER NO-UNDO.
 DEFINE VARIABLE iIndex   AS INTEGER   NO-UNDO.
 DEFINE VARIABLE cExtra   AS CHARACTER NO-UNDO.
        
/*--- Read from the file ---*/
 INPUT STREAM stFile FROM VALUE(ipDBAnalys) NO-ECHO.
 $main$:
 REPEAT:

     cTemp = "".
     IMPORT STREAM stFile UNFORMATTED cTemp.
     ASSIGN cTemp = TRIM(cTemp).

     IF NOT cTemp MATCHES "*% Util*" THEN NEXT.

     $inner$:
     REPEAT:
         cTemp = "".
         IMPORT STREAM stFile UNFORMATTED cTemp.
         ASSIGN cTemp = TRIM(cTemp).

         IF cTemp = "" THEN LEAVE $inner$.

         IF cTemp BEGINS "PUB." THEN NEXT $inner$.

         IF cTemp BEGINS "_" THEN NEXT $inner$.

        /*--- Handle line splits from long index names ---*/
         IF NUM-ENTRIES(cTemp," ") = 1 THEN DO:
            IMPORT STREAM stFile UNFORMATTED cExtra.
            ASSIGN cTemp = cTemp + " " + cExtra.
         END.

         ASSIGN cTemp = TRIM(SUBSTRING(cTemp,INDEX(cTemp," "))).

         ASSIGN iIndex = INTEGER(ENTRY(1,cTemp," ")) NO-ERROR.

         FIND ttIndex WHERE ttIndex.IndexNumber = iIndex NO-ERROR.

         IF NOT AVAILABLE ttIndex THEN NEXT.

         FIND ttTable OF ttIndex NO-ERROR.
         FIND ttArea  OF ttIndex NO-ERROR.

        /*--- Get the blocks count ---*/ 
         ASSIGN cTemp = TRIM(SUBSTRING(cTemp,INDEX(cTemp," "))).
         ASSIGN cTemp = TRIM(SUBSTRING(cTemp,INDEX(cTemp," "))).
         ASSIGN cTemp = TRIM(SUBSTRING(cTemp,INDEX(cTemp," "))).

         ASSIGN ttIndex.IndexBlocks = DECIMAL(ENTRY(1,cTemp," ")) NO-ERROR.

        /*--- Get the util% ---*/
         ASSIGN cTemp = TRIM(SUBSTRING(cTemp,INDEX(cTemp," "))).
         ASSIGN cTemp = TRIM(SUBSTRING(cTemp,INDEX(cTemp," "))).

         ASSIGN ttIndex.IndexUtil = DECIMAL(ENTRY(1,cTemp," ")) NO-ERROR.
        
        /*--- Get the factor ---*/
         ASSIGN cTemp = TRIM(SUBSTRING(cTemp,INDEX(cTemp," "))).

         ASSIGN ttIndex.IndexFactor = DECIMAL(ENTRY(1,cTemp," ")) NO-ERROR.

         IF ttIndex.ReadPercent > 95 
            AND ttIndex.PctRead > 5
            AND ttIndex.IndexBlocks <= 200000 THEN
           ASSIGN ttIndex.B2Candidate = TRUE.

         IF ttIndex.IndexBlocks >= 2000 AND
           (ttIndex.IndexUtil <= 75 OR
            ttIndex.IndexFactor >= 2.5) THEN
           ASSIGN ttIndex.RebuildCandidate = TRUE.

         IF ttArea.AreaVersion = 1 AND ttTable.RecordCount > 0 THEN
           ASSIGN ttIndex.MoveCandidate = TRUE.

     END. /*--- $inner$ ---*/

 END. /*--- $main$ ---*/
 INPUT STREAM stFile CLOSE.

END PROCEDURE.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-ip_ProcessIndexObject) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE ip_ProcessIndexObject Procedure 
PROCEDURE ip_ProcessIndexObject PRIVATE :
/*------------------------------------------------------------------------------
  Purpose:     
  Parameters:  <none>
  Notes:       
------------------------------------------------------------------------------*/
DEFINE INPUT PARAMETER ip_Handle AS HANDLE NO-UNDO.

FIND ttArea WHERE 
     ttArea.AreaNumber = ip_Handle:BUFFER-FIELD("_Area-Number"):BUFFER-VALUE
    NO-ERROR.

IF NOT AVAILABLE ttArea THEN RETURN.

FIND ttIndex WHERE 
     ttIndex.IndexNumber = ip_Handle:BUFFER-FIELD("_Object-Number"):BUFFER-VALUE
    NO-ERROR.

IF NOT AVAILABLE ttIndex THEN RETURN.

ASSIGN ttIndex.AreaNumber    = ttArea.AreaNumber
       ttIndex.MoveCandidate = (IF ttArea.AreaVersion = 1 THEN TRUE ELSE ttIndex.MoveCandidate)
       ttIndex.BufferPool    = (IF ttArea.AreaVersion = 1 THEN ttArea.AreaBufferPool 
                                ELSE IF ttArea.AreaVersion = 2 AND ttArea.AreaBufferPool = 2 THEN 2
                                ELSE GET-BITS(ip_Handle:BUFFER-FIELD("_Object-Attrib"):BUFFER-VALUE,7,1) + 1).

END PROCEDURE.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-ip_ProcessIndexStat) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE ip_ProcessIndexStat Procedure 
PROCEDURE ip_ProcessIndexStat PRIVATE :
/*------------------------------------------------------------------------------
  Purpose:     
  Parameters:  <none>
  Notes:       
------------------------------------------------------------------------------*/
DEFINE INPUT PARAMETER ip_Handle AS HANDLE NO-UNDO.

DEFINE VARIABLE hHandle AS HANDLE NO-UNDO.

FIND ttIndex WHERE 
     ttIndex.IndexNumber = ip_Handle:BUFFER-FIELD("_IndexStat-ID"):BUFFER-VALUE
    NO-ERROR.

IF NOT AVAILABLE ttIndex THEN RETURN.

FIND ttArea OF ttIndex NO-ERROR.

ASSIGN ttIndex.IsTracked     = TRUE
       ttIndex.TotalReads    = ip_Handle:BUFFER-FIELD("_IndexStat-Read"):BUFFER-VALUE
       ttIndex.TotalCreates  = ip_Handle:BUFFER-FIELD("_IndexStat-Create"):BUFFER-VALUE
       ttIndex.TotalDeletes  = ip_Handle:BUFFER-FIELD("_IndexStat-Delete"):BUFFER-VALUE
       ttIndex.TotalActivity = ttIndex.TotalReads + ttIndex.TotalCreates + ttIndex.TotalDeletes
       ttIndex.ReadPercent   = fn_Pct(ttIndex.TotalActivity,ttIndex.TotalReads) 
       ttIndex.PctReads      = fn_Pct(ttIndex.TotalReads,IndexReads)
       ttIndex.PctCreates    = fn_Pct(ttIndex.TotalCreates,IndexCreates)
       ttIndex.PctDeletes    = fn_Pct(ttIndex.TotalDeletes,IndexDeletes)
       ttIndex.PctDBReads    = fn_Pct(ttIndex.TotalReads,IndexReads)  
       ttIndex.PctDBCreates  = fn_Pct(ttIndex.TotalCreates,IndexCreates)
       ttIndex.PctDBDeletes  = fn_Pct(ttIndex.TotalDeletes,IndexDeletes)
       ttIndex.PctDBActivity = fn_Pct(ttIndex.TotalActivity,IndexReads + IndexDeletes + IndexCreates).

ASSIGN hHandle = ip_Handle:BUFFER-FIELD("_IndexStat-OSRead") NO-ERROR.

IF VALID-HANDLE(hHandle) AND ttArea.AreaVersion = 2 THEN
  ASSIGN ttIndex.OSReads  = ip_Handle:BUFFER-FIELD("_IndexStat-OSRead"):BUFFER-VALUE NO-ERROR.
ELSE ASSIGN ttIndex.OSReads  = -1.

IF ttIndex.OSReads > 0 THEN
   ASSIGN ttIndex.HitRatio = (ttIndex.TotalReads / ttIndex.OSReads).

END PROCEDURE.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-ip_ProcessLogging) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE ip_ProcessLogging Procedure 
PROCEDURE ip_ProcessLogging PRIVATE :
/*------------------------------------------------------------------------------
  Purpose:     
  Parameters:  <none>
  Notes:       
------------------------------------------------------------------------------*/
DEFINE INPUT PARAMETER ip_Handle AS HANDLE NO-UNDO.

ASSIGN AIEnabled     = (IF ip_Handle:BUFFER-FIELD("_Logging-AiOpen"):BUFFER-VALUE BEGINS "--" THEN FALSE
                        ELSE TRUE)
       AIBlockSize   = ip_Handle:BUFFER-FIELD("_logging-AiBlkSize"):BUFFER-VALUE / 1024
       BIBlockSize   = ip_Handle:BUFFER-FIELD("_Logging-BiBlkSize"):BUFFER-VALUE / 1024
       BIClusterSize = ip_Handle:BUFFER-FIELD("_Logging-BiClSize"):BUFFER-VALUE / 1024.

END PROCEDURE.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-ip_ProcessStartup) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE ip_ProcessStartup Procedure 
PROCEDURE ip_ProcessStartup PRIVATE :
/*------------------------------------------------------------------------------
  Purpose:     
  Parameters:  <none>
  Notes:       
------------------------------------------------------------------------------*/
DEFINE INPUT PARAMETER ip_Handle AS HANDLE NO-UNDO.

ASSIGN DBBuffers    = ip_Handle:BUFFER-FIELD("_Startup-Buffs"):BUFFER-VALUE
       DBAltBuffers = ip_Handle:BUFFER-FIELD("_Startup-Alternate_Buffs"):BUFFER-VALUE 
       AIBuffers    = ip_Handle:BUFFER-FIELD("_Startup-AiBuffs"):BUFFER-VALUE
       BIBuffers    = ip_Handle:BUFFER-FIELD("_Startup-BiBuffs"):BUFFER-VALUE
       Spin         = ip_Handle:BUFFER-FIELD("_Startup-Spin"):BUFFER-VALUE
      NO-ERROR.

END PROCEDURE.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-ip_ProcessTable) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE ip_ProcessTable Procedure 
PROCEDURE ip_ProcessTable PRIVATE :
/*------------------------------------------------------------------------------
  Purpose:     
  Parameters:  <none>
  Notes:       
------------------------------------------------------------------------------*/

DEFINE INPUT PARAMETER ip_Handle AS HANDLE NO-UNDO.

CREATE ttTable.
ASSIGN ttTable.TableNumber = ip_Handle:BUFFER-FIELD("_File-Number"):BUFFER-VALUE
       ttTable.TableName   = ip_Handle:BUFFER-FIELD("_File-Name"):BUFFER-VALUE
       ttTable.FileRecid   = ip_Handle:RECID
       MaxTableNumber      = MAX(MaxTableNumber,ttTable.TableNumber).

/*--- Get the basic index information ---*/
 fn_Query ("_Index","WHERE _Index._File-Recid = " + STRING(ip_Handle:RECID),"",FALSE,"ip_ProcessIndex").

END PROCEDURE.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-ip_ProcessTableAnalys) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE ip_ProcessTableAnalys Procedure 
PROCEDURE ip_ProcessTableAnalys PRIVATE :
/*------------------------------------------------------------------------------
  Purpose:     
  Parameters:  <none>
  Notes:       
------------------------------------------------------------------------------*/

/*--- Define Local Variables ---*/
 DEFINE VARIABLE cTemp    AS CHARACTER NO-UNDO.
 DEFINE VARIABLE cExtra   AS CHARACTER NO-UNDO.
 DEFINE VARIABLE cTable   AS CHARACTER NO-UNDO.
 DEFINE VARIABLE iRPB     AS INTEGER   NO-UNDO.
 DEFINE VARIABLE cField   AS CHARACTER NO-UNDO.
 DEFINE VARIABLE cVersion AS CHARACTER NO-UNDO.
        
/*--- Read from the file ---*/
 INPUT STREAM stFile FROM VALUE(ipDBAnalys) NO-ECHO.

/*--- Get the OE version for the dbanlys file ---*/
 IMPORT STREAM stFile ^ ^ cVersion.
 $main$:
 REPEAT:

     cTemp = "".
     IMPORT STREAM stFile UNFORMATTED cTemp.
     ASSIGN cTemp = TRIM(cTemp).

     IF NOT cTemp BEGINS "-Record Size (B)-" THEN NEXT.

     $inner$:
     REPEAT:
         cTemp = "".
         IMPORT STREAM stFile UNFORMATTED cTemp.
         ASSIGN cTemp = TRIM(cTemp).

         IF cTemp = "" THEN LEAVE $inner$.

         IF NOT cTemp BEGINS "PUB" THEN NEXT.

        /*--- Handle the wrapping with long table tables ---*/
         IF NUM-ENTRIES(cTemp," ") = 1 THEN DO:
           IMPORT STREAM stFile UNFORMATTED cExtra.
           ASSIGN cTemp = cTemp + " " + cExtra.
         END.

         ASSIGN cTable = ENTRY(2,ENTRY(1,cTemp," "),".").
         
         FIND ttTable WHERE ttTable.TableName = cTable NO-ERROR.

         IF NOT AVAILABLE ttTable THEN NEXT.

         FIND ttArea OF ttTable NO-ERROR.

        /*--- Get the record count ---*/ 
         ASSIGN cTemp = TRIM(SUBSTRING(cTemp,INDEX(cTemp," "))).

         ASSIGN ttTable.RecordCount = DECIMAL(ENTRY(1,cTemp," ")) NO-ERROR.

        /*--- Get the record size ---*/
         ASSIGN cTemp = TRIM(SUBSTRING(cTemp,INDEX(cTemp," "))).
         ASSIGN cTemp = TRIM(SUBSTRING(cTemp,INDEX(cTemp," "))).
         ASSIGN cTemp = TRIM(SUBSTRING(cTemp,INDEX(cTemp," "))).
         ASSIGN cTemp = TRIM(SUBSTRING(cTemp,INDEX(cTemp," "))).

         ASSIGN ttTable.RecordSize = DECIMAL(ENTRY(1,cTemp," ")) NO-ERROR.
        
        /*--- Get the number of fragments ---*/
         ASSIGN cTemp = TRIM(SUBSTRING(cTemp,INDEX(cTemp," "))).
         
         ASSIGN ttTable.Fragments = DECIMAL(ENTRY(1,cTemp," ")) NO-ERROR.

        /*--- Get the fragment factor ---*/
         ASSIGN cTemp = TRIM(SUBSTRING(cTemp,INDEX(cTemp," "))).
         
         ASSIGN ttTable.FragFactor = DECIMAL(ENTRY(1,cTemp," ")) NO-ERROR.

        /*--- Get the scatter factor ---*/
         ASSIGN cTemp = TRIM(SUBSTRING(cTemp,INDEX(cTemp," "))).
         
         ASSIGN ttTable.ScatterFactor = DECIMAL(ENTRY(1,cTemp," ")) NO-ERROR.

        /*--- guess at the number of blocks ---*/
         ASSIGN ttTable.DistinctBlocks = fn_Ceil((ttTable.Fragments / ttArea.AreaRPB) * ttTable.FragFactor).

        /*--- Calculate the "best" RPB for the current blocksize ---*/
         ASSIGN iRPB            = (ttArea.AreaBlockSize * 1024) / (ttTable.RecordSize + 20)
                ttTable.RPBCalc = iRPB.
         
         ASSIGN ttTable.RPBPerformance = 
             (IF      iRPB <= 1 THEN 1
              ELSE IF iRPB <= 2 THEN 2
              ELSE IF iRPB <= 4 THEN 4
              ELSE IF iRPB <= 8 THEN 8
              ELSE IF iRPB <= 16 THEN 16
              ELSE IF iRPB <= 32 THEN 32
              ELSE IF iRPB <= 64 THEN 64
              ELSE IF iRPB <= 128 THEN 128
              ELSE 256).

         IF ttTable.RecordCount = 0 THEN
            ASSIGN ttTable.MoveCandidate = FALSE.

         ELSE IF ttTable.RecordCount * (ttTable.RecordSize + 20) < (ttArea.AreaBlockSize * 1024) THEN
            ASSIGN ttTable.MoveCandidate = FALSE.

         ELSE IF ttTable.RPBPerformance <> ttArea.AreaRPB THEN
           ASSIGN ttTable.MoveCandidate = TRUE.
        
        /*--- Calculate the "best" RPB for an 8k blocksize ---*/
         ASSIGN iRPB               = 8192 / (ttTable.RecordSize + 20)
                ttTable.OptRPBCalc = iRPB.
         
         ASSIGN ttTable.OptRPBPerformance = 
             (IF      iRPB <= 1 THEN 1
              ELSE IF iRPB <= 2 THEN 2
              ELSE IF iRPB <= 4 THEN 4
              ELSE IF iRPB <= 8 THEN 8
              ELSE IF iRPB <= 16 THEN 16
              ELSE IF iRPB <= 32 THEN 32
              ELSE IF iRPB <= 64 THEN 64
              ELSE IF iRPB <= 128 THEN 128
              ELSE 256).

         IF ttTable.ReadPercent       > 95 
            AND ttTable.PctRead       > 3
            AND ttTable.RecordCount  <= 1000000 THEN
           ASSIGN ttTable.B2Candidate = TRUE.

        /*--- Guess at the optimal block storage ---*/
         ASSIGN ttTable.OptCurrentBlocks = MAX(1,fn_Ceil(ttTable.RecordCount / ttTable.RPBPerformance))
                ttTable.Opt8KBlocks      = MAX(1,fn_Ceil(ttTable.RecordCount / ttTable.OptRPBPerformance)).

     END. /*--- $inner$ ---*/

 END. /*--- $main$ ---*/
 INPUT STREAM stFile CLOSE.

END PROCEDURE.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-ip_ProcessTableObject) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE ip_ProcessTableObject Procedure 
PROCEDURE ip_ProcessTableObject PRIVATE :
/*------------------------------------------------------------------------------
  Purpose:     
  Parameters:  <none>
  Notes:       
------------------------------------------------------------------------------*/

DEFINE INPUT PARAMETER ip_Handle AS HANDLE NO-UNDO.

FIND ttArea WHERE 
     ttArea.AreaNumber = ip_Handle:BUFFER-FIELD("_Area-Number"):BUFFER-VALUE
    NO-ERROR.

IF NOT AVAILABLE ttArea THEN RETURN.

FIND ttTable WHERE 
     ttTable.TableNumber = ip_Handle:BUFFER-FIELD("_Object-Number"):BUFFER-VALUE
    NO-ERROR.

IF NOT AVAILABLE ttTable THEN RETURN.

ASSIGN ttTable.AreaNumber    = ttArea.AreaNumber
       ttTable.MoveCandidate = (IF ttArea.AreaVersion = 1 THEN TRUE ELSE ttTable.MoveCandidate)
       ttTable.BufferPool    = (IF ttArea.AreaVersion = 1 THEN ttArea.AreaBufferPool 
                                ELSE IF ttArea.AreaVersion = 2 AND ttArea.AreaBufferPool = 2 THEN 2
                                ELSE GET-BITS(ip_Handle:BUFFER-FIELD("_Object-Attrib"):BUFFER-VALUE,7,1) + 1).

IF ttArea.AreaVersion = 1 THEN
  ASSIGN HaveTypeOneTables = TRUE.

END PROCEDURE.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-ip_ProcessTableStat) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE ip_ProcessTableStat Procedure 
PROCEDURE ip_ProcessTableStat PRIVATE :
/*------------------------------------------------------------------------------
  Purpose:     
  Parameters:  <none>
  Notes:       
------------------------------------------------------------------------------*/
DEFINE INPUT PARAMETER ip_Handle AS HANDLE NO-UNDO.

DEFINE VARIABLE hHandle AS HANDLE NO-UNDO.

FIND ttTable WHERE 
     ttTable.TableNumber = ip_Handle:BUFFER-FIELD("_TableStat-ID"):BUFFER-VALUE
    NO-ERROR.

IF NOT AVAILABLE ttTable THEN RETURN.

FIND ttArea OF ttTable NO-ERROR.

ASSIGN ttTable.IsTracked     = TRUE
       ttTable.TotalReads    = ip_Handle:BUFFER-FIELD("_TableStat-Read"):BUFFER-VALUE
       ttTable.TotalUpdates  = ip_Handle:BUFFER-FIELD("_TableStat-Update"):BUFFER-VALUE
       ttTable.TotalCreates  = ip_Handle:BUFFER-FIELD("_TableStat-Create"):BUFFER-VALUE
       ttTable.TotalDeletes  = ip_Handle:BUFFER-FIELD("_TableStat-Delete"):BUFFER-VALUE
       ttTable.TotalActivity = ttTable.TotalReads + ttTable.TotalUpdates + ttTable.TotalCreates + ttTable.TotalDeletes
       ttTable.ReadPercent   = fn_Pct(ttTable.TotalActivity,ttTable.TotalReads)
       ttTable.PctReads      = fn_Pct(ttTable.TotalReads,DBReads)
       ttTable.PctUpdates    = fn_Pct(ttTable.TotalUpdates,DBUpdates)
       ttTable.PctCreates    = fn_Pct(ttTable.TotalCreates,DBCreates)
       ttTable.PctDeletes    = fn_Pct(ttTable.TotalDeletes,DBDeletes) 
       ttTable.PctDBActivity = fn_Pct(ttTable.TotalActivity,DBActivity) 
       ttTable.PctDBReads    = fn_Pct(ttTable.TotalReads,DBReads) 
       ttTable.PctDBCreates  = fn_Pct(ttTable.TotalCreates,DBCreates) 
       ttTable.PctDBUpdates  = fn_Pct(ttTable.TotalUpdates,DBUpdates) 
       ttTable.PctDBDeletes  = fn_Pct(ttTable.TotalDeletes,DBDeletes).
                                                                                                                       
ASSIGN hHandle = ip_Handle:BUFFER-FIELD("_TableStat-OSRead") NO-ERROR.

IF VALID-HANDLE(hHandle) AND ttArea.AreaVersion = 2 THEN
  ASSIGN ttTable.OSReads  = ip_Handle:BUFFER-FIELD("_TableStat-OSRead"):BUFFER-VALUE 
         HaveOSReads      = TRUE NO-ERROR.
ELSE ASSIGN ttTable.OSReads  = -1.

IF ttTable.OSReads > 0 THEN
   ASSIGN ttTable.HitRatio = (ttTable.TotalReads / ttTable.OSReads).

END PROCEDURE.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

/* ************************  Function Implementations ***************** */

&IF DEFINED(EXCLUDE-fn_AI) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION fn_AI Procedure 
FUNCTION fn_AI RETURNS LOGICAL PRIVATE
  ( /* parameter-definitions */ ) :
/*------------------------------------------------------------------------------
  Purpose:  
    Notes:  
------------------------------------------------------------------------------*/
 fn_Header("After Imaging",2,"AI").

 PUT STREAM stFile UNFORMATTED
     "<div>"
     "After imaging helps protect you from data loss and corruption by:"
     "<ul>"
     "<li>Tracking the database blocks that are modified.</li>" SKIP
     "<li>Allowing those tracked changes to be archived.</li>"  SKIP
     "<li>Allowing the archived changes to be replayed against the last good backup of your database.</li>" SKIP
     "</ul>" SKIP
     "If you have a hardware or software issue that causes data corruption "
     "or the complete loss of one or more of your database files you will have to restore from the "
     "last good backup. All work done since the last backup will have to be reentered and verified.<br><br>" SKIP
     "With AI enabled these are the steps you would follow:" SKIP
     "<ul>"
     "<li>Restore your database from the last good backup.</li>" SKIP
     "<li>Apply the archived AI files to your database (either all the files or to a certain point in time).</li>" SKIP
     "<li>Start your database.</li>" SKIP
     "<li>Relax in the knowledge that you have saved yourself and your company countless hours of grief.</li>" SKIP
     "</ul>" SKIP
     "You can also use AI to replicate databases for disaster recovery and/or create a read only reporting copy "
     "of your database. There is very minimal overhead to implement AI. You can read more about AI in the " 
     "OpenEdge documentation (Database Administration - Protecting Your Data).<br><br>"
     "Recommendations:"
     "<ul>"
     "<li>Use multi volume AI files.</li>"
     "<li>Set your AI blocksize to 16384 (16K).</li>"
     "<li>Set the aibufs parameter to 200 as a starting point.</li>"
     "</ul>"
     "</div>" SKIP.

  RETURN FALSE.   /* Function return value. */

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_AIBlockSize) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION fn_AIBlockSize Procedure 
FUNCTION fn_AIBlockSize RETURNS LOGICAL PRIVATE
  ( /* parameter-definitions */ ) :
/*------------------------------------------------------------------------------
  Purpose:  
    Notes:  
------------------------------------------------------------------------------*/
  fn_Header("After Image Block Size",2,"AIBlockSize").

  PUT STREAM stFile UNFORMATTED
      "<div>"
      "The AI block size determines how much data is buffered before written to disk as well as "
      "how much data is written to disk at one time. Larger values usually result in better "
      "performance depending on how write intensive your database is.<br><br>"
      "This is a general suggestion and you should monitor your database at peak times with "
      "ProTop to decide if this change would help your performance or not.<br><br>"
      "</div>" SKIP.
  RETURN FALSE.   /* Function return value. */

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_AIBuffers) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION fn_AIBuffers Procedure 
FUNCTION fn_AIBuffers RETURNS LOGICAL PRIVATE
  ( /* parameter-definitions */ ) :
/*------------------------------------------------------------------------------
  Purpose:  
    Notes:  
------------------------------------------------------------------------------*/
 fn_Header("After Image Buffers",2,"AIBuffers").

  PUT STREAM stFile UNFORMATTED
      "<div>"
      "AI buffers are used to cache and group writes to the AI files. When you run out of AI buffers "
      "processes have to spend time writing to the AI files instead of leaving that to the AI Writer. "
      "You can increase the number of AI buffers by adding "
      "<span class=param>-aibufs "
      (IF AIBuffers < 100 THEN "200" 
       ELSE STRING(MIN(500,AIBuffers * 1.5),">>>>>>9"))
      "</span> to your database startup scripts or parameter file. "
      "</div>" SKIP.

  RETURN FALSE.   /* Function return value. */

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_AIWriter) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION fn_AIWriter Procedure 
FUNCTION fn_AIWriter RETURNS LOGICAL PRIVATE
  ( /* parameter-definitions */ ) :
/*------------------------------------------------------------------------------
  Purpose:  
    Notes:  
------------------------------------------------------------------------------*/
  fn_Header("After Image Writer",2,"AIWriter").

  PUT STREAM stFile UNFORMATTED
      "<div>"
      "The After Image Writer (AIW) is responsible for writing filled AI buffers to disk so that "
      "other processes don't have to. If the AIW isn't running then your other processes are spending "
      "time writing those buffers.<br><br>" 
      "You can start the AIW through OpenEdge Explorer or by modifying your startup scripts to "
      "include <span class=param>proaiw YourDB</span>.<br><br>"
      "You will also need to make sure you add the aibufs parameter "
      "<span class=param>-aibufs 200</span> to your database startup scripts or parameter file. "
      "This will be a decent starting point for most databases but might need to be tuned "  
      "on highly active databases."
      "</div>" SKIP.

  RETURN FALSE.   /* Function return value. */

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_APWs) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION fn_APWs Procedure 
FUNCTION fn_APWs RETURNS LOGICAL PRIVATE
  ( /* parameter-definitions */ ) :
/*------------------------------------------------------------------------------
  Purpose:  
    Notes:  
------------------------------------------------------------------------------*/
  fn_Header("Database Writers",2,"APWs").

  PUT STREAM stFile UNFORMATTED
      "<div>"
      "Database Writers (APWs) are responsible for writing changed DB buffers to disk so that "
      "other processes don't have to. Without APWs running your other processes are spending "
      "time writing those buffers.<br><br>" 
      "You can start one or more APWs through OpenEdge Explorer or by modifying your startup scripts to "
      "include <span class=param>proapw YourDB</span> for each APW you want to start.<br><br>"
      "A good starting point is betwen 2 and 4 APWs for most OpenEdge installs. Highly active may "
      "require additional APWs and/or an increase in the BI cluster size."
      "</div>" SKIP.

  RETURN FALSE.   /* Function return value. */

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_B2) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION fn_B2 Procedure 
FUNCTION fn_B2 RETURNS LOGICAL PRIVATE
  ( /* parameter-definitions */ ) :
/*------------------------------------------------------------------------------
  Purpose:  
    Notes:  
------------------------------------------------------------------------------*/
 fn_Header("Alternate Buffer Pool",2,"B2").

 PUT STREAM stFile UNFORMATTED
     "<div>"
     "Buffer pools cache recently used database blocks (not records) for the purpose of increasing "
     "performance by allowing future reads to come from memory instead of "
     "reading them from disk. Once the buffer reaches the allocated size the database "
     "will decide to evict the least frequently used blocks from memory to make space "
     "for new blocks that are read.<br><br>"
     "OpenEdge supports two buffer pools Primary (-B) and Alternate (-B2)."
     "By default all objects belong to the Primary buffer pool but you can assign objects to the "
     "Alternate buffer pool either through the Data Dictionary at a table/index level (Type 2 areas only) "
     "or by using proutil -C enableB2 on a storage area level.<br><br>"
     "Because you rarely have enough memory to store the entire database in memory the Alternate pool "
     "is useful to store smaller but frequently read tables/indexes. Make sure to size the B2 parameter large "
     "enough to make sure that blocks are never evicted from memory."
     "The <a href=#TableReads>Table Reads</a> and <a href=#IndexReads>Index Reads</a> sections will "
     "provide you with a starting point to decide which objects should be placed in the Alternate pool."
     "</div>" SKIP.


  RETURN FALSE.   /* Function return value. */

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_BIBlockSize) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION fn_BIBlockSize Procedure 
FUNCTION fn_BIBlockSize RETURNS LOGICAL PRIVATE
  ( /* parameter-definitions */ ) :
/*------------------------------------------------------------------------------
  Purpose:  
    Notes:  
------------------------------------------------------------------------------*/
  fn_Header("Before Image Block Size",2,"BIBlockSize").

  PUT STREAM stFile UNFORMATTED
      "<div>"
      "The BI block size determines how much data is buffered before written to disk as well as "
      "how much data is written to disk at one time. Larger values usually result in better "
      "performance depending on how write intensive your database is.<br><br>"
      "This is a general suggestion and you should monitor your database at peak times with "
      "ProTop to decide if this change would help your performance or not.<br><br>"
      "</div>" SKIP.
  RETURN FALSE.   /* Function return value. */

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_BIBuffers) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION fn_BIBuffers Procedure 
FUNCTION fn_BIBuffers RETURNS LOGICAL PRIVATE
  ( /* parameter-definitions */ ) :
/*------------------------------------------------------------------------------
  Purpose:  
    Notes:  
------------------------------------------------------------------------------*/
 fn_Header("Before Image Buffers",2,"BIBuffers").

 PUT STREAM stFile UNFORMATTED
      "<div>"
      "BI buffers are used to cache and group writes to the BI file. When you run out of BI buffers "
      "processes have to spend time writing to the BI instead of leaving that to the BI Writer. "
      "You can increase the number of BI buffers by adding "
      "<span class=param>-bibufs "
      (IF BIBuffers < 100 THEN "200" 
       ELSE STRING(MIN(500,BIBuffers * 1.5),">>>>>>9"))
      "</span> to your database startup scripts or parameter file. "
      "</div>" SKIP.

  RETURN FALSE.   /* Function return value. */

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_BIWriter) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION fn_BIWriter Procedure 
FUNCTION fn_BIWriter RETURNS LOGICAL PRIVATE
  ( /* parameter-definitions */ ) :
/*------------------------------------------------------------------------------
  Purpose:  
    Notes:  
------------------------------------------------------------------------------*/
  fn_Header("Before Image Writer",2,"BIWriter").

  PUT STREAM stFile UNFORMATTED
      "<div>"
      "The Before Image Writer (BIW) is responsible for writing filled BI buffers to disk so that "
      "other processes don't have to. If the BIW isn't running then your other processes are spending "
      "time writing those buffers.<br><br>" 
      "You can start the BIW through OpenEdge Explorer or by modifying your startup scripts to "
      "include <span class=param>probiw YourDB</span>.<br><br>"
      "You will also need to make sure you add the bibufs parameter "
      "<span class=param>-bibufs 200</span> to your database startup scripts or parameter file. "
      "This will be a decent starting point for most databases but might need to be tuned "  
      "on highly active databases."
      "</div>" SKIP.

  RETURN FALSE.   /* Function return value. */

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_Ceil) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION fn_Ceil Procedure 
FUNCTION fn_Ceil RETURNS INTEGER PRIVATE
  ( INPUT ip_Decimal AS DECIMAL ) :
/*------------------------------------------------------------------------------
  Purpose:  
    Notes:  
------------------------------------------------------------------------------*/
 IF TRUNC(ip_Decimal,0) <> ip_Decimal THEN
   RETURN INTEGER(TRUNC(ip_Decimal,0) + 1).
 ELSE RETURN INTEGER(ip_Decimal).

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_ConvertSeconds) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION fn_ConvertSeconds Procedure 
FUNCTION fn_ConvertSeconds RETURNS CHARACTER PRIVATE
  ( INPUT ip_Seconds AS DECIMAL):
    
/*------------------------------------------------------------------------------
  Purpose:  
    Notes:  
------------------------------------------------------------------------------*/

/*--- Define Variables ---*/
 DEFINE VARIABLE cReturnString AS CHARACTER NO-UNDO.
 DEFINE VARIABLE iDays         AS INTEGER   NO-UNDO.
 DEFINE VARIABLE iHours        AS INTEGER   NO-UNDO.
 DEFINE VARIABLE iMinutes      AS INTEGER   NO-UNDO.
 DEFINE VARIABLE iSeconds      AS INTEGER   NO-UNDO.

 IF ip_Seconds >= 86400 THEN
  ASSIGN iDays         = TRUNC(ip_Seconds / 86400,0)
         cReturnString = STRING(iDays,">>,>>9") + " Day" + (IF iDays > 1 THEN "s " ELSE " ")
         ip_Seconds    = ip_Seconds - (iDays * 86400).

 IF ip_Seconds >= 3600 THEN
  ASSIGN iHours        = TRUNC(ip_Seconds / 3600,0)
         cReturnString = cReturnString + STRING(iHours,">9") + " Hour" + (IF iHours > 1 THEN "s " ELSE " ")
         ip_Seconds    = ip_Seconds - (iHours * 3600).
 
 IF ip_Seconds >= 60 THEN
  ASSIGN iMinutes      = TRUNC(ip_Seconds / 60,0)
         cReturnString = cReturnString + 
                         IF iDays > 0 THEN "" 
                         ELSE STRING(iMinutes,">9") + " Minute" + (IF iMinutes > 1 THEN "s " ELSE " ")
         ip_Seconds    = ip_Seconds - (iMinutes * 60).

 IF ip_Seconds >= 1 THEN
   ASSIGN iSeconds      = ip_Seconds
          cReturnString = cReturnString + 
                          IF iDays > 0 OR iHours > 0 THEN ""
                          ELSE STRING(iSeconds,">9") + " Second" + (IF iSeconds > 1 THEN "s " ELSE " ").

 RETURN cReturnString.

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_DBBlockSize) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION fn_DBBlockSize Procedure 
FUNCTION fn_DBBlockSize RETURNS LOGICAL PRIVATE
  ( /* parameter-definitions */ ) :
/*------------------------------------------------------------------------------
  Purpose:  
    Notes:  
------------------------------------------------------------------------------*/
  fn_Header("Database Block Size",2,"DBBlockSize").

  PUT STREAM stFile UNFORMATTED
      "<div>"
      "Larger database block sizes along with proper record per block settings will "
      "allow you to store your data more efficiently as well as improve performance. "
      "For Unix/Linux based databases an 8K block size is widely accepted as the best "
      "option. For Windows based databases there are differing opinions on whether "
      "4K or 8K is the optimal block size. I personally prefer 8K on all platforms. "
      "You may not see as large an improvement from 4K to 8K as you do on Unix based systems "
      "but the improvements do exist.<br><br>"
      "Note that this requires a complete dump and reload of the database since "
      "OpenEdge does not allow you to change the block size of a database. "
      "While this suggestion is still valid and can make substantial performance improvements "
      "you might consider fixing the other issues identified first."
      "</div>" SKIP.

  RETURN FALSE.   /* Function return value. */

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_DBSpin) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION fn_DBSpin Procedure 
FUNCTION fn_DBSpin RETURNS LOGICAL PRIVATE
  ( /* parameter-definitions */ ) :
/*------------------------------------------------------------------------------
  Purpose:  
    Notes:  
------------------------------------------------------------------------------*/
 fn_Header("Database Spin Retries",2,"DBSpin").

 PUT STREAM stFile UNFORMATTED
      "<div>"
      "The spin parameter allows database clients to retry accessing a database resource (Latches) "
      "instead of going to sleep when they are blocked."
      "You can enable spin by adding "
      "<span class=param>-spin 1000</span> to your database startup scripts or parameter file. <br><br>"
      "1000 is a safe starting point that allows you to benefit from the retries without "
      "wasting CPU cycles."
      "Tuning -spin to a ~"perfect~" value for your database is a much more involved process and "
      "usually involves a mixture of detailed latching information, knowledge of your hardware and "
      "your application. <br><br>"
      "Download "
      fn_ProTop()
      " for much more detailed information on how to tune the spin setting for your "
      "environment."
      "</div>" SKIP.

  RETURN FALSE.   /* Function return value. */

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_DBUpTime) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION fn_DBUpTime Procedure 
FUNCTION fn_DBUpTime RETURNS LOGICAL PRIVATE
  ( /* parameter-definitions */ ) :
/*------------------------------------------------------------------------------
  Purpose:  
    Notes:  
------------------------------------------------------------------------------*/
  fn_Header("Database Uptime",2,"DBUpTime").

  PUT STREAM stFile UNFORMATTED
      "<div>"
      "This tool makes a number of assumptions based on the statistics gathered in the "
      "OpenEdge VSTs (Virtual System Tables). If the sample time is too small "
      "you are likely to miss certain issues unless your application runs a truly "
      "repeatable workload every day/hour. This is not the case for most applications "
      "since there are always peak times or jobs that only run on certain days.<br><br>"
      "If you are shutting down your database on a regular basis for backups you should seriously "
      "consider using probkup online instead of offline backups. "
      "Not for the sake of this tool but because you are starting from scratch "
      "every time you restart the database. Losing the benefits of the buffer pools and "
      "having to read more records from disk instead of memory."
      "</div>" SKIP.

  RETURN FALSE.   /* Function return value. */

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_FieldList) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION fn_FieldList Procedure 
FUNCTION fn_FieldList RETURNS CHARACTER PRIVATE
  (  ) :
/*------------------------------------------------------------------------------
  Purpose:  
    Notes:  
------------------------------------------------------------------------------*/
/*--- Define Variables ---*/
 DEFINE VARIABLE hQuery     AS HANDLE    NO-UNDO.
 DEFINE VARIABLE hBuffer    AS HANDLE    NO-UNDO.
 DEFINE VARIABLE cFieldList AS CHARACTER NO-UNDO.
 DEFINE VARIABLE cDate      AS CHARACTER NO-UNDO.
 DEFINE VARIABLE cInteger   AS CHARACTER NO-UNDO.
 DEFINE VARIABLE cDecimal   AS CHARACTER NO-UNDO.
 DEFINE VARIABLE cCharacter AS CHARACTER NO-UNDO.

 CREATE BUFFER hBuffer FOR TABLE "_Field" NO-ERROR.
 CREATE QUERY hQuery NO-ERROR.
 hQuery:SET-BUFFERS(hBuffer) NO-ERROR.
 hQuery:QUERY-PREPARE("FOR EACH _Field FIELDS(_Field._Data-Type _Field._Field-Name) " +
                      "WHERE _Field._File-Recid = " + STRING(ttTable.FileRecid) +
                      " NO-LOCK").
 hQuery:QUERY-OPEN() NO-ERROR.

 $loop$:
 REPEAT:
   hQuery:GET-NEXT() NO-ERROR.
   
   IF hQuery:QUERY-OFF-END THEN LEAVE $loop$.

  /*--- Find a nice small field to add to the fields list ---*/
   IF hBuffer:BUFFER-FIELD("_Data-Type"):BUFFER-VALUE = "LOGICAL" THEN DO:
      ASSIGN cFieldList = hBuffer:BUFFER-FIELD("_Field-Name"):BUFFER-VALUE NO-ERROR.
      LEAVE $loop$.
   END. /*--- Easiest ---*/

   ELSE IF cDate = "" AND 
           hBuffer:BUFFER-FIELD("_Data-Type"):BUFFER-VALUE = "DATE" THEN 
      ASSIGN cDate = hBuffer:BUFFER-FIELD("_Field-Name"):BUFFER-VALUE NO-ERROR.

   ELSE IF cInteger = "" AND 
          (hBuffer:BUFFER-FIELD("_Data-Type"):BUFFER-VALUE = "INTEGER" OR
           hBuffer:BUFFER-FIELD("_Data-Type"):BUFFER-VALUE = "INT64") THEN 
      ASSIGN cInteger = hBuffer:BUFFER-FIELD("_Field-Name"):BUFFER-VALUE NO-ERROR.

   ELSE IF cDecimal = "" AND 
           hBuffer:BUFFER-FIELD("_Data-Type"):BUFFER-VALUE = "DECIMAL" THEN 
      ASSIGN cDecimal = hBuffer:BUFFER-FIELD("_Field-Name"):BUFFER-VALUE NO-ERROR.

   ELSE IF cCharacter = "" AND 
           hBuffer:BUFFER-FIELD("_Data-Type"):BUFFER-VALUE = "CHARACTER" THEN 
      ASSIGN cCharacter = hBuffer:BUFFER-FIELD("_Field-Name"):BUFFER-VALUE NO-ERROR.
   
 END. /*--- $loop$ ---*/

 hQuery:QUERY-CLOSE()  NO-ERROR.
 DELETE OBJECT hQuery  NO-ERROR.
 DELETE OBJECT hBuffer NO-ERROR.

/*--- Which one? ---*/
 IF cFieldList = "" THEN DO:
    IF cDate           <> "" THEN ASSIGN cFieldList = cDate.
    ELSE IF cInteger   <> "" THEN ASSIGN cFieldList = cInteger.
    ELSE IF cDecimal   <> "" THEN ASSIGN cFieldList = cDecimal.
    ELSE IF cCharacter <> "" THEN ASSIGN cFieldList = cCharacter.
 END.

 RETURN cFieldList.

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_Header) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION fn_Header Procedure 
FUNCTION fn_Header RETURNS LOGICAL PRIVATE
  ( INPUT ip_Text   AS CHARACTER,
    INPUT ip_Header AS INTEGER,
    INPUT ip_ID     AS CHARACTER ) :
/*------------------------------------------------------------------------------
  Purpose:  
    Notes:  
------------------------------------------------------------------------------*/
  
  DEFINE VARIABLE cTag AS CHARACTER NO-UNDO.

  ASSIGN cTag = "h" + STRING(ip_Header).

  PUT STREAM stFile UNFORMATTED 
      "<" cTag " id="
      (IF ip_ID <> "" THEN ip_ID 
       ELSE "TOP")
      ">"
      "<span style=~"align: left;~">" ip_Text "</span>".

  IF ip_ID <> "" AND ip_ID <> "TOP" THEN
    PUT STREAM stFile UNFORMATTED
      "<span style=~"position: absolute;right: 40px;font-size: 0.8em;~">"
      "<a href=#TOP style=~"color: white;~">Back to Summary</a></span>" SKIP.
       
  PUT STREAM stFile UNFORMATTED
      "</" cTag ">" SKIP.

  RETURN FALSE.   /* Function return value. */

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_HTMLHeader) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION fn_HTMLHeader Procedure 
FUNCTION fn_HTMLHeader RETURNS LOGICAL PRIVATE
  ( /* parameter-definitions */ ) :
/*------------------------------------------------------------------------------
  Purpose:  
    Notes:  
------------------------------------------------------------------------------*/
 PUT STREAM stFile UNFORMATTED
    "<!DOCTYPE html>" SKIP
    "<html>" SKIP
    "<head>" SKIP
    "<title>OpenEdge Database Advisor - The Mad DBA (TheMadDBA.com)</title>" SKIP
    "<style>" SKIP
    "body ~{" SKIP
    "    background-color: #F0F0F5;" SKIP
    "    color: black;" SKIP
    "    margin-left: 25px; " SKIP
    "    margin-right: 25px; " SKIP
    "    font-family: Arial, Helvetica, sans-serif;" SKIP
    "~}" SKIP

    "a:hover ~{color:#007b9d; ~}" SKIP
    "a:visited ~{color: #007b9d; ~}" SKIP
    "a:link ~{color: #007b9d; ~}" SKIP


    "h1 ~{" SKIP
    "    color: #007b9d;" SKIP
    "    font-size: 1.5em;" SKIP
    "    font-weight: bold;" SKIP
    "    padding: 5px;" SKIP
    "    border-style: solid;" SKIP
    "    border-color: black;" SKIP
    "    border-width: 2px; " SKIP
    "    margin-bottom: 5px;" SKIP
    "~} " SKIP
     
    "h2 ~{" SKIP
    "    color: white;" SKIP
    "    background-color: #007b9d;" SKIP
    "    font-size: 1.2em;" SKIP
    "    font-weight: bold;" SKIP
    "    padding: 5px;" SKIP
    "    border: 2px solid black;" SKIP
    "~} " SKIP

    "h3 ~{" SKIP
    "    color: #007b9d;" SKIP
    "    font-size: 1.2em;" SKIP
    "    font-weight: bold;" SKIP
    "    padding: 2px;" SKIP
    "    margin-bottom: 5px;" SKIP
    "~} " SKIP

    "ul ~{" SKIP
    "    list-style-type: square;" SKIP
    "~}" SKIP

    "li ~{" SKIP
    "    padding: 3px;" SKIP
    "~}" SKIP

    "div ~{" SKIP
    "    padding: 5px;" SKIP
    "    margin-bottom: 5px;" SKIP 
    "    width: 75%;" SKIP
    "~}" SKIP

    "table ~{" SKIP
    "    border-collapse: collapse;" SKIP
    "    margin-left: 20px;" SKIP
    "~}" SKIP
    
    "th ~{" SKIP
    "    background-color:#d2f6ff;" SKIP
    "    color: black;" SKIP
    "    padding: 5px;" SKIP
    "    vertical-align: top;" SKIP
    "    font-size: 1em;" SKIP
    "    border: 2px solid black;" SKIP
    "    padding: 3px 7px 2px 7px;" SKIP
    "~} "  SKIP

    "td ~{" SKIP
    "    color: black;" SKIP
    "    padding: 5px;" SKIP
    "    vertical-align: top;" SKIP
    "    font-size: 1em;" SKIP
    "    border: 2px solid black;" SKIP
    "    padding: 3px 7px 2px 7px;" SKIP
    "~} "  SKIP

    ".param ~{" SKIP
    "    background-color: #E1E1E1;" SKIP
    "    color: black;" SKIP
    "    font-size: 1em;" SKIP
    "    font-family: monospace;" SKIP
    "    border-style: solid;" SKIP
    "    padding: 3px;" SKIP
    "~}" SKIP

    ".nowrap ~{" SKIP
    "    white-space: nowrap;" SKIP
    "~}" SKIP

     ".italic ~{" SKIP
     "    font-style: italic;" SKIP
     "~}" SKIP

    "</style>" SKIP
    "</head>" SKIP
    "<body>" SKIP
    "<div style=~"width: 100%;~">". 

    

  RETURN FALSE.   /* Function return value. */

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_IndexActivity) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION fn_IndexActivity Procedure 
FUNCTION fn_IndexActivity RETURNS LOGICAL PRIVATE
  ( /* parameter-definitions */ ) :
/*------------------------------------------------------------------------------
  Purpose:  
    Notes:  
------------------------------------------------------------------------------*/
 fn_Header("Index Activity",2,"IndexActivity").

 IF UntrackedTables > 0 THEN
   fn_Header("WARNING: Results are incomplete because " +
             STRING(UntrackedIndexes,">>,>>9")   +
             " index(es) are not being tracked.",
             3,
             "").


 PUT STREAM stFile UNFORMATTED
     "<table>" SKIP
     "<tr>"    SKIP
     "<th>Table Name</th>" SKIP
     "<th>Index Name</th>" SKIP
     "<th>% of DB Activity</th>" SKIP
     "<th>Total Activity</th>" SKIP
     "<th>% of DB Reads</th>" SKIP
     "<th>Total Reads</th>" SKIP
     "<th>% of DB Creates</th>" SKIP
     "<th>Total Creates</th>" SKIP
     "<th>% of DB Deletes</th>" SKIP
     "<th>Total Deletes</th>" SKIP
     "</tr>" 
     SKIP.

 FOR EACH ttIndex WHERE
          ttIndex.IsTracked = TRUE AND
          ttIndex.TotalActivity > 0 
       BY ttIndex.TotalActivity DESCENDING:
    
   FIND ttTable OF ttIndex NO-ERROR.

   PUT STREAM stFile UNFORMATTED
        "<tr>" SKIP
        "<td>" ttTable.TableName "</td>" SKIP
        "<td>" ttIndex.IndexName "</td>" SKIP
        "<td>" STRING(ttIndex.PctDBActivity,">>,>>9.9")        "</td>" SKIP
        "<td>" STRING(ttIndex.TotalActivity,">>>,>>>,>>>,>>9") "</td>" SKIP
        "<td>" STRING(ttIndex.PctDBReads,">>,>>9.9")           "</td>" SKIP
        "<td>" STRING(ttIndex.TotalReads,">>>,>>>,>>>,>>9")    "</td>" SKIP
        "<td>" STRING(ttIndex.PctDBCreates,">>,>>9.9")         "</td>" SKIP
        "<td>" STRING(ttIndex.TotalCreates,">>>,>>>,>>>,>>9")  "</td>" SKIP
        "<td>" STRING(ttIndex.PctDBDeletes,">>,>>9.9")         "</td>" SKIP
        "<td>" STRING(ttIndex.TotalDeletes,">>>,>>>,>>>,>>9")  "</td>" SKIP
        "</tr>" SKIP.

 END. /*--- Untracked indexes ---*/

 PUT STREAM stFile UNFORMATTED
     "</table>" SKIP.

  RETURN FALSE.   /* Function return value. */

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_IndexMoves) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION fn_IndexMoves Procedure 
FUNCTION fn_IndexMoves RETURNS LOGICAL PRIVATE
  ( /* parameter-definitions */ ) :
/*------------------------------------------------------------------------------
  Purpose:  
    Notes:  
------------------------------------------------------------------------------*/
 fn_Header("Index Storage Area Issues",2,"IndexMoves").

PUT STREAM stFile UNFORMATTED
    "<div>"
    "While slightly less important than moving tables from Type 1 to Type 2 areas, all indexes "
    "should be stored in Type 2 areas. Do not store indexes in the same storage area as tables. "
    "The records per block (RPB) should be set to 1 for all index only areas. If you decide to "
    "mix tables and indexes in the same area (not recommended) use the suggested RPB setting for the tables. "
    "Using a RPB of 1 for tables will result in extremely excessive database growth and extremely poor "
    "performance."
    "</div>" SKIP.

PUT STREAM stFile UNFORMATTED
     "<table>" SKIP
     "<tr>"    SKIP
     "<th>Table Name</th>"       SKIP
     "<th>Index Name</th>"       SKIP
     "<th>Area Name</th>"        SKIP
     "<th>Area Type</th>"        SKIP
     "<th>Total Activity</th>"   SKIP
     "</tr>" 
     SKIP.

 FOR EACH ttIndex WHERE
          ttIndex.MoveCandidate = TRUE
       BY ttIndex.TotalActivity DESCENDING:
    
   FIND ttTable OF ttIndex NO-ERROR.
   FIND ttArea  OF ttIndex NO-ERROR.

   PUT STREAM stFile UNFORMATTED
    "<tr>" SKIP
    "<td class=nowrap>" ttTable.TableName "</td>" SKIP
    "<td class=nowrap>" ttIndex.IndexName "</td>" SKIP
    "<td>" ttArea.AreaName "</td>"    SKIP
    "<td>" ttArea.AreaVersion "</td>" SKIP
    "<td>" IF ttIndex.IsTracked THEN
             STRING(ttIndex.TotalActivity,">>>,>>>,>>>,>>9")
           ELSE "-"
         "</td>" SKIP
    "</tr>" SKIP.

 END. /*--- Untracked indexes ---*/

 PUT STREAM stFile UNFORMATTED
     "</table>" SKIP.


  RETURN FALSE.   /* Function return value. */

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_IndexReads) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION fn_IndexReads Procedure 
FUNCTION fn_IndexReads RETURNS LOGICAL PRIVATE
  ( /* parameter-definitions */ ) :
/*------------------------------------------------------------------------------
  Purpose:  
    Notes:  
------------------------------------------------------------------------------*/
 fn_Header("Index Read Activity",2,"IndexReads").

 IF UntrackedIndexes > 0 THEN
   fn_Header("WARNING: Results are incomplete because " +
             STRING(UntrackedIndexes,">>,>>9")   +
             " index(es) are not being tracked.",
             3,
             "").
 
 IF HaveOSReads = FALSE THEN
   fn_Header("WARNING: Disk read statistics for Type 2 areas are not supported " +
             "because the VSTs in your database are not correct for your current version of OpenEdge.",
             3,
             "").

 PUT STREAM stFile UNFORMATTED
     "<div>"
     "Most databases are heavily skewed towards read activity rather than changing data. "
     "Not surprisingly most performance issues are related to how quickly you can read data "
     "from the database. The metrics below combined with your knowledge of the application "
     "will give you an idea of which indexes need maintenance and/or application changes to "
     "improve performance.<br><br>"
     "Most Important Metrics" SKIP
     "<ul>"
     "<li>% of DB Reads: Which of your indexes are read the most frequently and what percentage of total "
     "database reads are consumed by each index.</li>" SKIP
     "<li>Recommended Buffer Pool: Which buffer pool should this index and the related table "
     "be assigned to. Best practice is to place smaller tables that are frequently read "
     "but rarely updated into the secondary buffer pool, along with all of the indexes for that table.</li>" SKIP
     "</ul>"
     "</div>" SKIP.

 PUT STREAM stFile UNFORMATTED
     "<table>" SKIP
     "<tr>"    SKIP
     "<th class=nowrap>Table Name</th>" SKIP
     "<th class=nowrap>Index Name</th>" SKIP
     "<th>% of DB Reads</th>" SKIP
     "<th>Total Reads</th>" SKIP
     "<th>Disk Reads</th>" SKIP
     "<th>Cached Reads<br>Per Disk Read</th>" SKIP
     "<th>Reads Per Second</th>" SKIP
     "<th>Reads Per Minute</th>" SKIP
     "<th>Area Name</th>" SKIP
     "<th>Area Type</th>" SKIP     
     "<th>% Reads of <br>Total Activity</th>" SKIP
     "<th>Current<br>Buffer Pool</th>" SKIP
     "<th>Recommended<br>Buffer Pool</th>" SKIP
     "</tr>" 
     SKIP.

 FOR EACH ttIndex WHERE
          ttIndex.IsTracked = TRUE AND
          ttIndex.TotalReads >  0 
       BY ttIndex.TotalReads DESCENDING:
    
   FIND ttTable OF ttIndex NO-ERROR.
   FIND ttArea  OF ttIndex NO-ERROR.

   PUT STREAM stFile UNFORMATTED
        "<tr>" SKIP
        "<td>" ttTable.TableName "</td>" SKIP
        "<td>" ttIndex.IndexName "</td>" SKIP
        "<td>" STRING(ttIndex.PctDBReads,">>,>>9.9") "</td>" SKIP
        "<td>" STRING(ttIndex.TotalReads,">>>,>>>,>>>,>>9") "</td>" SKIP
        "<td>" IF ttIndex.OSReads         >= 0 THEN STRING(ttIndex.OSReads,">>>,>>>,>>>,>>9")    
               ELSE IF ttArea.AreaVersion  = 1 THEN "Not Supported"
               ELSE "Update VSTs" "</td>" SKIP
        "<td>" IF ttIndex.OSReads >= 0 THEN STRING(ttIndex.HitRatio,">>>,>>>,>>>,>>9.99") 
               ELSE IF ttArea.AreaVersion  = 1 THEN "Not Supported"
               ELSE "Update VSTs" "</td>" SKIP
        "<td>" STRING(ttIndex.TotalReads / DBUptime,">>>,>>>,>>>,>>9")        "</td>" SKIP
        "<td>" STRING(ttIndex.TotalReads / (DBUptime / 60),">>>,>>>,>>>,>>9") "</td>" SKIP
       "<td>" ttArea.AreaName "</td>"    SKIP
       "<td>" ttArea.AreaVersion "</td>" SKIP
       "<td>" ttIndex.ReadPercent "</td>" SKIP
       "<td>" IF ttIndex.BufferPool = 1 THEN "Primary" 
              ELSE IF ttIndex.BufferPool = 2 THEN "Secondary"
              ELSE "Unknown" "</td>"
       "<td>" IF ttIndex.B2Candidate = TRUE THEN "Secondary"
              ELSE "Primary"
       "</tr>" SKIP.

 END. /*--- Untracked indexes ---*/

 PUT STREAM stFile UNFORMATTED
     "</table>" SKIP.

  RETURN FALSE.   /* Function return value. */

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_IndexRebuilds) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION fn_IndexRebuilds Procedure 
FUNCTION fn_IndexRebuilds RETURNS LOGICAL PRIVATE
  ( /* parameter-definitions */ ) :
/*------------------------------------------------------------------------------
  Purpose:  
    Notes:  
------------------------------------------------------------------------------*/
 fn_Header("Index Rebuilds",2,"IndexRebuilds").

 PUT STREAM stFile UNFORMATTED
     "<table>" SKIP
     "<tr>"    SKIP
     "<th class=nowrap>Table Name</th>" SKIP
     "<th class=nowrap>Index Name</th>" SKIP
     "<th>% of DB Reads</th>" SKIP
     "<th>Total Reads</th>" SKIP
     "<th>Reads Per Second</th>" SKIP
     "<th>Reads Per Minute</th>" SKIP
     "<th>Area Name</th>" SKIP
     "<th>Area Type</th>" SKIP     
     "<th>Index Blocks</th>" SKIP
     "<th>Util %</th>" SKIP
     "<th>Factor</th>" SKIP
     "</tr>" 
     SKIP.

 FOR EACH ttIndex WHERE
          ttIndex.RebuildCandidate = TRUE
       BY ttIndex.TotalReads DESCENDING:
    
   FIND ttTable OF ttIndex NO-ERROR.
   FIND ttArea  OF ttIndex NO-ERROR.

   PUT STREAM stFile UNFORMATTED
        "<tr>" SKIP
        "<td>" ttTable.TableName "</td>" SKIP
        "<td>" ttIndex.IndexName "</td>" SKIP
        "<td>" STRING(ttIndex.PctDBReads,">>,>>9.9") "</td>" SKIP
        "<td>" STRING(ttIndex.TotalReads,">>>,>>>,>>>,>>9") "</td>" SKIP
        "<td>" STRING(ttIndex.TotalReads / DBUptime,">>>,>>>,>>>,>>9")        "</td>" SKIP
        "<td>" STRING(ttIndex.TotalReads / (DBUptime / 60),">>>,>>>,>>>,>>9") "</td>" SKIP
        "<td>" ttArea.AreaName "</td>"    SKIP
        "<td>" ttArea.AreaVersion "</td>" SKIP
        "<td>" STRING(ttIndex.IndexBlocks,">>>,>>>,>>>,>>9") "</td>" SKIP
        "<td>" STRING(ttIndex.IndexUtil,">>>,>>9.99") "</td>" SKIP
        "<td>" STRING(ttIndex.IndexFactor,">,>>9.9") "</td>" SKIP

       "</tr>" SKIP.

 END. /*--- Untracked indexes ---*/

 PUT STREAM stFile UNFORMATTED
     "</table>" SKIP.


  RETURN FALSE.   /* Function return value. */

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_Issue) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION fn_Issue Procedure 
FUNCTION fn_Issue RETURNS LOGICAL PRIVATE
  ( INPUT ip_Group    AS CHARACTER,
    INPUT ip_Issue    AS CHARACTER,
    INPUT ip_Descr    AS CHARACTER,
    INPUT ip_Severity AS CHARACTER,
    INPUT ip_Link     AS CHARACTER) :
/*------------------------------------------------------------------------------
  Purpose:  
    Notes:  
------------------------------------------------------------------------------*/

ASSIGN iIssueNumber = iIssueNumber + 1.

CREATE ttIssue.
ASSIGN ttIssue.IssueNumber = iIssueNumber
       ttIssue.IssueGroup  = ip_Group
       ttIssue.Issue       = ip_Issue
       ttIssue.ShortDescr  = ip_Descr
       ttIssue.Severity    = ip_Severity
       ttIssue.DetailLink  = ip_Link.

RETURN FALSE.   /* Function return value. */

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_Pct) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION fn_Pct Procedure 
FUNCTION fn_Pct RETURNS DECIMAL PRIVATE
  ( INPUT ip_Dec1 AS DECIMAL,
    INPUT ip_Dec2 AS DECIMAL ) :
/*------------------------------------------------------------------------------
  Purpose:  
    Notes:  
------------------------------------------------------------------------------*/

 IF ip_Dec1 = 0 OR ip_Dec2 = 0 THEN RETURN 0.

 ELSE 
     RETURN MIN(100,ROUND((ip_Dec1 / ip_Dec2) * 100,1)).


END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_ProTop) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION fn_ProTop Procedure 
FUNCTION fn_ProTop RETURNS CHARACTER PRIVATE
  ( /* parameter-definitions */ ) :
/*------------------------------------------------------------------------------
  Purpose:  
    Notes:  
------------------------------------------------------------------------------*/
 RETURN '<a href="http://www.dbappraise.com/protop.html" target=_blank>ProTop</a>'.

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_Query) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION fn_Query Procedure 
FUNCTION fn_Query RETURNS LOGICAL PRIVATE
  ( INPUT ip_TableName AS CHARACTER,
    INPUT ip_Where     AS CHARACTER,
    INPUT ip_Sort      AS CHARACTER,
    INPUT ip_First     AS LOGICAL,
    INPUT ip_Handler   AS CHARACTER
    ) :

/*--- Define Variables ---*/
 DEFINE VARIABLE hQuery  AS HANDLE NO-UNDO.
 DEFINE VARIABLE hBuffer AS HANDLE NO-UNDO.

 CREATE BUFFER hBuffer FOR TABLE ip_TableName NO-ERROR.
 CREATE QUERY hQuery NO-ERROR.
 hQuery:SET-BUFFERS(hBuffer) NO-ERROR.
 hQuery:QUERY-PREPARE("FOR EACH " + 
                      ip_TableName + 
                      (IF ip_Where = "" THEN "" ELSE " " + ip_Where) + 
                      (IF ip_Sort  = "" THEN "" ELSE " " + ip_Sort)  +
                      " NO-LOCK") NO-ERROR.
 hQuery:QUERY-OPEN() NO-ERROR.

 $loop$:
 REPEAT:
   hQuery:GET-NEXT() NO-ERROR.
   
   IF hQuery:QUERY-OFF-END THEN LEAVE $loop$.

   RUN VALUE(ip_Handler) (INPUT hBuffer:HANDLE) NO-ERROR.

   IF ip_First = TRUE THEN LEAVE $loop$.

 END. /*--- $loop$ ---*/

 hQuery:QUERY-CLOSE()  NO-ERROR.
 DELETE OBJECT hQuery  NO-ERROR.
 DELETE OBJECT hBuffer NO-ERROR.
 
 RETURN FALSE.   /* Function return value. */

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_TableActivity) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION fn_TableActivity Procedure 
FUNCTION fn_TableActivity RETURNS LOGICAL PRIVATE
  ( /* parameter-definitions */ ) :
/*------------------------------------------------------------------------------
  Purpose:  
    Notes:  
------------------------------------------------------------------------------*/
 fn_Header("Table Activity",2,"TableActivity").

 IF UntrackedTables > 0 THEN
   fn_Header("WARNING: Results are incomplete because " +
             STRING(UntrackedTables,">>,>>9")   +
             " table(s) are not being tracked.",
             3,
             "").

 PUT STREAM stFile UNFORMATTED
     "<table>" SKIP
     "<tr>"    SKIP
     "<th>Table Name</th>" SKIP
     "<th>% of DB Activity</th>" SKIP
     "<th>Total Activity</th>" SKIP
     "<th>% of DB Reads</th>" SKIP
     "<th>Total Reads</th>" SKIP
     "<th>% of DB Creates</th>" SKIP
     "<th>Total Creates</th>" SKIP
     "<th>% of DB Updates</th>" SKIP
     "<th>Total Updates</th>" SKIP
     "<th>% of DB Deletes</th>" SKIP
     "<th>Total Deletes</th>" SKIP
     "</tr>" 
     SKIP.

 FOR EACH ttTable WHERE
          ttTable.IsTracked = TRUE AND
          ttTable.TotalActivity > 0 
       BY ttTable.TotalActivity DESCENDING:
    
   PUT STREAM stFile UNFORMATTED
        "<tr>" SKIP
        "<td>" ttTable.TableName "</td>" SKIP
        "<td>" STRING(ttTable.PctDBActivity,">>,>>9.9")        "</td>" SKIP
        "<td>" STRING(ttTable.TotalActivity,">>>,>>>,>>>,>>9") "</td>" SKIP
        "<td>" STRING(ttTable.PctDBReads,">>,>>9.9")           "</td>" SKIP
        "<td>" STRING(ttTable.TotalReads,">>>,>>>,>>>,>>9")    "</td>" SKIP
        "<td>" STRING(ttTable.PctDBCreates,">>,>>9.9")         "</td>" SKIP
        "<td>" STRING(ttTable.TotalCreates,">>>,>>>,>>>,>>9")  "</td>" SKIP
        "<td>" STRING(ttTable.PctDBUpdates,">>,>>9.9")         "</td>" SKIP
        "<td>" STRING(ttTable.TotalUpdates,">>>,>>>,>>>,>>9")  "</td>" SKIP
        "<td>" STRING(ttTable.PctDBDeletes,">>,>>9.9")         "</td>" SKIP
        "<td>" STRING(ttTable.TotalDeletes,">>>,>>>,>>>,>>9")  "</td>" SKIP
        "</tr>" SKIP.

 END. /*--- Untracked indexes ---*/

 PUT STREAM stFile UNFORMATTED
     "</table>" SKIP.

  RETURN FALSE.   /* Function return value. */

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_TableMoves) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION fn_TableMoves Procedure 
FUNCTION fn_TableMoves RETURNS LOGICAL PRIVATE
  ( /* parameter-definitions */ ) :
/*------------------------------------------------------------------------------
  Purpose:  
    Notes:  
------------------------------------------------------------------------------*/
 fn_Header("Table Storage Area Issues",2,"TableMoves").

 IF HaveDBAnalys = FALSE THEN
   fn_Header("WARNING: Suggestions based on records per block cannot " +
             "be provided because the DB Analysis was not provided.",
             3,
             "").

 PUT STREAM stFile UNFORMATTED "<div>" SKIP.

 IF HaveTypeOneTables = TRUE THEN
 PUT STREAM stFile UNFORMATTED
    "<h3>Type 1 Areas Explained</h3>" SKIP
    "Type 1 storage areas are the first generation of database storage implemented in "
    "the early 1980s. No major improvements have "
    "been made for Type 1 areas since the release of Type 2 areas in 2003, arguably even before then. "
    "Using Type 1 areas is highly discouraged for any area other than the Schema Area (required).<br><br>"
    "The biggest day to day issue with Type 1 areas is the fact that a single database block can "
    "contain data from multiple tables. This causes extra database reads to occur and reduces the "
    "effectiveness of the database buffer pools, especially for smaller tables that are heavily "
    "scattered. Instead of reading and caching a small number of database blocks you can end up "
    "reading a substantial number of extra database blocks.<br><br>"
    "Initially you should focus on smaller tables that are highly active. That is where you are most "
    "likely to see improvements. Larger tables or seldom used tables will usually not show "
    "as much improvement but they should still be on your list to move.<br><br>" SKIP.
 
   PUT STREAM stFile UNFORMATTED
    "<h3>Type 2 Areas Explained</h3>"
    "Type 2 areas should be the only storage area type defined for your database. With the exception of the "
    "Schema Area which is currently required to be Type 1 by Progress. You should never store your "
    "tables or indexes in the Schema Area.<br><br>"
    "Type 2 areas will never let a single database block store records from more than one table at once. "
    "This helps to keep your data organized and enables numerous performance enhancements. "
    "When creating storage areas you should avoid creating areas for logical groups of data (Orders, Customers, etc.) "
    "and instead create storage areas based on the RPB settings (1 for 32 RPB, 1 for 64 RPB, etc). Tables " 
    "1GB in size or larger might deserve their own storage area, tables 10GB in size or larger certainly do. "
    "Indexes should not be stored in the same storage area as tables. For clarity you should have a corresponding "
    "index area for each table area (Indexes for 32 RPB, Indexes for 64 RPB, etc.). This is not required but "
    "will make your administration life easier."
    
    "<h3>Records Per Block (RPB) Explained</h3>"
    "The RPB is set at a storage area level and controls how many records the database will attempt to "
    "store in a single database block. It is important to set RPB properly for the proper balance of "
    "space utilization and performance. Setting RPB too low means space is wasted and more database blocks "
    "have to be read to retrieve your records. Setting RPB too high is less of a danger but still has "
    "drawbacks. The calculations for RPB below are widely used and should result in the best settings for each "
    "table." SKIP.

PUT STREAM stFile UNFORMATTED
    "<h3>Moving Tables Between Areas</h3>"
    "Tables with fewer than 100,000 records can usually be moved using the proutil tablemove command. Larger tables will "
    "usually require a dump and reload but there is some wiggle room based on the speed of your system " 
    "and how much downtime you can allocate.<br><br>" 
    "If your application stores RECIDs in the database you will have to develop your own methods "
    "to move the tables because this will cause all of the RECIDs and ROWIDs to change. "
    "Regardless of the approach it is always safer to test "
    "these table moves on a development or test environment before you attempt to modify production.<br><br>"
    "</div>" SKIP.

PUT STREAM stFile UNFORMATTED
     "<table>" SKIP
     "<tr>"    SKIP
     "<th>Table Name</th>"       SKIP
     "<th>Area Name</th>"        SKIP
     "<th>Area Type</th>"        SKIP
     "<th>Record Count</th>"     SKIP
     "<th>Record Fragments</th>" SKIP
     "<th>Mean Record Size</th>" SKIP
     "<th>Area RPB</th>" SKIP
     "<th>Suggested RPB<br>(Current DB Block Size)</th>" SKIP
      IF DBBlockSize = 8 OR HaveDBAnalys = FALSE THEN ""
      ELSE "<th>Suggested RPB<br>(8K Blocks)</th>" 
     SKIP
     "<th>Estimated Blocks</th>"  SKIP
     "<th>Optimal Blocks<br>for Suggested RPB</th>"   SKIP
          IF DBBlockSize = 8 OR HaveDBAnalys = FALSE THEN ""
          ELSE "<th>Optimal Blocks<br>for 8K DB</th>" 
         SKIP

     "<th>Total Activity</th>"   SKIP
     "<th>% of DB Activity</th>" SKIP
     "</tr>" 
     SKIP.

 FOR EACH ttTable WHERE
          ttTable.MoveCandidate = TRUE 
       BY ttTable.TotalActivity DESCENDING:
    
   FIND ttArea OF ttTable NO-ERROR.

   PUT STREAM stFile UNFORMATTED
    "<tr>" SKIP
    "<td class=nowrap>" ttTable.TableName "</td>" SKIP
    "<td>" ttArea.AreaName "</td>"    SKIP
    "<td>" ttArea.AreaVersion "</td>" SKIP
    "<td>" 
     IF HaveDBAnalys = TRUE THEN 
         STRING(ttTable.RecordCount,">>>,>>>,>>>,>>9") 
      ELSE "-"
    "</td>" SKIP
    "<td>" 
       IF HaveDBAnalys = TRUE THEN 
          STRING(ttTable.Fragments,">>>,>>>,>>>,>>9") 
       ELSE "-"
    "</td>" SKIP
    "<td>" 
       IF HaveDBAnalys = TRUE THEN 
          STRING(ttTable.RecordSize,">>>,>>>,>>>,>>9") 
       ELSE "-"
    "</td>" SKIP
    "<td>" ttArea.AreaRPB "</td>"
    "<td>" IF HaveDBAnalys THEN STRING(ttTable.RPBPerformance,">>9") 
           ELSE "-" "</td>"
     IF DBBlockSize = 8 OR HaveDBAnalys = FALSE THEN ""
     ELSE "<td>" + STRING(ttTable.OptRPBPerformance,">>9") + "</td>"

     IF ttTable.DistinctBlocks = 0 THEN
         "<td>-</td>"
     ELSE "<td>" + STRING(ttTable.DistinctBlocks,">>>,>>>,>>>,>>9") + "</td>"
     
     IF ttTable.OptCurrentBlocks = 0 THEN
         "<td>-</td>"
     ELSE "<td>" + STRING(ttTable.OptCurrentBlocks,">>>,>>>,>>>,>>9") + "</td>"

     IF DBBlockSize = 8 OR HaveDBAnalys = FALSE THEN ""
     ELSE IF ttTable.Opt8KBlocks = 0 THEN
         "<td>-</td>"
     ELSE "<td>" + STRING(ttTable.Opt8KBlocks,">>>,>>>,>>>,>>9") + "</td>"

    "<td>" IF ttTable.IsTracked THEN
             STRING(ttTable.TotalActivity,">>>,>>>,>>>,>>9")
           ELSE "-"
         "</td>" SKIP
    "<td>" IF ttTable.IsTracked AND ttTable.PctDBActivity <> 0 THEN
              STRING(ttTable.PctDBActivity,">>>,>>>,>>>,>>9.9") 
           ELSE "-"
        "</td>" SKIP

    "</tr>" SKIP.

 END. /*--- Untracked indexes ---*/

 PUT STREAM stFile UNFORMATTED
     "</table>" SKIP.


  RETURN FALSE.   /* Function return value. */

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_TableReads) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION fn_TableReads Procedure 
FUNCTION fn_TableReads RETURNS LOGICAL PRIVATE
  ( /* parameter-definitions */ ) :
/*------------------------------------------------------------------------------
  Purpose:  
    Notes:  
------------------------------------------------------------------------------*/
 fn_Header("Table Read Activity",2,"TableReads").

 IF UntrackedTables > 0 THEN
   fn_Header("WARNING: Results are incomplete because " +
             STRING(UntrackedTables,">>,>>9")   +
             " table(s) are not being tracked.",
             3,
             "").
 
 IF HaveDBAnalys = FALSE THEN
   fn_Header("WARNING: Percent of table read per minute cannot be calculated " +
             "because the DB Analysis was not provided.",
             3,
             "").
 
 IF HaveOSReads = FALSE THEN
   fn_Header("WARNING: Disk read statistics for Type 2 areas are not supported " +
             "because the VSTs in your database are not correct for your current version of OpenEdge.",
             3,
             "").

 PUT STREAM stFile UNFORMATTED
     "<div>"
     "Most databases are heavily skewed towards read activity rather than changing data. "
     "Not surprisingly most performance issues are related to how quickly you can read data "
     "from the database. The metrics below combined with your knowledge of the application "
     "will give you an idea of which tables need maintenance and/or application changes to "
     "improve performance.<br><br>"
     "Most Important Metrics" SKIP
     "<ul>"
     "<li>% of DB Reads: Which of your tables are read the most frequently and what percentage of total "
     "database reads are consumed by each table.</li>" SKIP
     "<li>% of Table Read Per Minute: What percentage of the total records per table are read per minute."
     " The higher the percentage the more likely you are to have a coding or design issue.</li>" SKIP
     "<li>Recommended Buffer Pool: Which buffer pool should this table and the indexes for "
     "this table be assigned to. Best practice is to place smaller tables that are frequently read "
     "but rarely updated into the secondary buffer pool.</li>" SKIP
     "</ul>"
     "</div>" SKIP.

 PUT STREAM stFile UNFORMATTED
     "<table>" SKIP
     "<tr>"    SKIP
     "<th class=nowrap>Table Name</th>" SKIP
     "<th>% of DB Reads</th>" SKIP
     "<th>Total Reads</th>" SKIP
     "<th>Disk Reads</th>" SKIP
     "<th>Cached Reads<br>Per Disk Read</th>" SKIP
     "<th>Reads Per Second</th>" SKIP
     "<th>Reads Per Minute</th>" SKIP
     "<th>% of Table<br>Read Per Minute</th>" SKIP
     "<th>Record Count</th>" SKIP
     "<th>Area Name</th>" SKIP
     "<th>Area Type</th>" SKIP
     "<th>% Reads of <br>Total Activity</th>" SKIP
     "<th>Current<br>Buffer Pool</th>" SKIP
     "<th>Recommended<br>Buffer Pool</th>" SKIP
     "</tr>" 
     SKIP.

 FOR EACH ttTable WHERE
          ttTable.IsTracked = TRUE AND
          ttTable.TotalReads >  0 
       BY ttTable.TotalReads DESCENDING:
    
   FIND ttArea OF ttTable NO-ERROR.

   PUT STREAM stFile UNFORMATTED
        "<tr>" SKIP
        "<td>" ttTable.TableName "</td>" SKIP
        "<td>" STRING(ttTable.PctDBReads,">>,>>9.9") "</td>" SKIP
        "<td>" STRING(ttTable.TotalReads,">>>,>>>,>>>,>>9") "</td>" SKIP
        "<td>" IF ttTable.OSReads         >= 0 THEN STRING(ttTable.OSReads,">>>,>>>,>>>,>>9")    
               ELSE IF ttArea.AreaVersion  = 1 THEN "Not Supported"
               ELSE "Update VSTs" "</td>" SKIP
        "<td>" IF ttTable.OSReads >= 0 THEN STRING(ttTable.HitRatio,">>>,>>>,>>>,>>9.99") 
               ELSE IF ttArea.AreaVersion  = 1 THEN "Not Supported"
               ELSE "Update VSTs" "</td>" SKIP
        "<td>" STRING(ttTable.TotalReads / DBUptime,">>>,>>>,>>>,>>9")        "</td>" SKIP
        "<td>" STRING(ttTable.TotalReads / (DBUptime / 60),">>>,>>>,>>>,>>9") "</td>" SKIP
        "<td>" IF ttTable.RecordCount > 0 THEN
                 STRING(((ttTable.TotalReads / (DBUptime / 60)) / ttTable.RecordCount) * 100,">>>,>>>,>>>,>>9.9")
               ELSE "-" "</td>" SKIP
        "<td>" 
         IF HaveDBAnalys = TRUE THEN 
             STRING(ttTable.RecordCount,">>>,>>>,>>>,>>9") 
          ELSE "-"
       "</td>" SKIP
       "<td>" ttArea.AreaName "</td>"    SKIP
       "<td>" ttArea.AreaVersion "</td>" SKIP
       "<td>" ttTable.ReadPercent "</td>" SKIP
       "<td>" IF ttTable.BufferPool = 1 THEN "Primary" 
              ELSE IF ttTable.BufferPool = 2 THEN "Secondary"
              ELSE "Unknown" "</td>"
       "<td>" IF ttTable.B2Candidate = TRUE THEN "Secondary"
              ELSE "Primary"
       "</tr>" SKIP.

 END. /*--- Untracked indexes ---*/

 PUT STREAM stFile UNFORMATTED
     "</table>" SKIP.

  RETURN FALSE.   /* Function return value. */

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_UntrackedIndexes) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION fn_UntrackedIndexes Procedure 
FUNCTION fn_UntrackedIndexes RETURNS LOGICAL PRIVATE
  ( /* parameter-definitions */ ) :
/*------------------------------------------------------------------------------
  Purpose:  
    Notes:  
------------------------------------------------------------------------------*/

 fn_Header("Untracked Indexes",2,"UntrackedIndexes").

 PUT STREAM stFile UNFORMATTED
    "<div>"
    "OpenEdge has the ability to track reads, creates and deletes on an index level. " 
    "This is extremely valuable information to help identify:<ul><li>Where you have index design or " 
    "coding issues in your application.</li><li>Which buffer pool (primary or alternate) an index " 
    "should belong to.</li><li>If you have unused indexes in your database.</li></ul>"
    "You can correct this by setting the indexrangesize parameter "
    "<span class=param>-indexrangesize " STRING(MaxIndexNumber) "</span>" 
    " in your database startup script or parameter file and restarting your database." 
    "</div>" SKIP.

 PUT STREAM stFile UNFORMATTED
     "<table>" SKIP
     "<tr>"    SKIP
     "<th>Table Name</th>" SKIP
     "<th>Index Name</th>" SKIP
     "</tr>" SKIP.

 FOR EACH ttIndex 
     /*WHERE
          ttIndex.IsTracked = FALSE */,
     EACH ttTable OF ttIndex
        BY ttTable.TableName BY ttIndex.IndexName:

    PUT STREAM stFile UNFORMATTED
        "<tr>" SKIP
        "<td>" ttTable.TableName "</td>" SKIP
        "<td>" ttIndex.IndexName "</td>" SKIP
        "</tr>" SKIP.
 END. /*--- Untracked indexes ---*/

 PUT STREAM stFile UNFORMATTED
     "</table>" SKIP.

RETURN FALSE.   /* Function return value. */

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_UntrackedTables) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION fn_UntrackedTables Procedure 
FUNCTION fn_UntrackedTables RETURNS LOGICAL PRIVATE
  ( /* parameter-definitions */ ) :
/*------------------------------------------------------------------------------
  Purpose:  
    Notes:  
------------------------------------------------------------------------------*/

 fn_Header("Untracked Database Tables",2,"UntrackedTables").

 
 PUT STREAM stFile UNFORMATTED
      "<div>"
      "OpenEdge has the ability to track reads, creates, updates and deletes on a table level. " 
      "This is extremely valuable information to help identify:<ul><li>Where you have index design or " 
      "coding issues in your application.</li><li>Which buffer pool (primary or alternate) a table " 
      "should belong to.</li><li>If a high volume table deserves to have a dedicated storage area.</li></ul>" SKIP
      "You can correct this by setting the tablerangesize parameter "
      "<span class=param>-tablerangesize " STRING(MaxTableNumber) "</span>" 
      " in your database startup script or parameter file " 
      " and then restarting your database."
      "</div>" SKIP.

 PUT STREAM stFile UNFORMATTED
     "<table>" SKIP
     "<tr>"    SKIP
     "<th>Untracked Table</th>" SKIP
     "</tr>" SKIP.

 FOR EACH ttTable 
     /*WHERE
          ttTable.IsTracked = FALSE */
        BY ttTable.TableName:

    FIND ttArea OF ttTable NO-ERROR.

    PUT STREAM stFile UNFORMATTED
        "<tr>" SKIP
        "<td>" ttTable.TableName "</td>" SKIP
        "</tr>" SKIP.
 END. /*--- Untracked tables ---*/

 PUT STREAM stFile UNFORMATTED
     "</table>" SKIP.

RETURN FALSE.   /* Function return value. */

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-fn_UpdateVSTs) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION fn_UpdateVSTs Procedure 
FUNCTION fn_UpdateVSTs RETURNS LOGICAL PRIVATE
  ( /* parameter-definitions */ ) :
/*------------------------------------------------------------------------------
  Purpose:  
    Notes:  
------------------------------------------------------------------------------*/
  fn_Header("Disk Read Tracking Not Supported",2,"UpdateVSTs").

  PUT STREAM stFile UNFORMATTED
      "<div>"
      "The Virtual System Tables (VSTs) for your database do not match your OpenEdge version "
      "and need to updated to use this feature.<br><br>"
      "You will need to shut down your database and run the following command from the "
      "database server <span class=param>proutil YourDB -C updatevst</span><br><br>"
      "Make sure that you have a proper backup of your database before running this command. "
      "Although problems with this process are extremely rare they have been known to happen."
      "</div>" SKIP.

  RETURN FALSE.   /* Function return value. */

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

