&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: 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: Notes: ------------------------------------------------------------------------------*/ OUTPUT STREAM stFile TO VALUE(ipFileName). /*--- Header ---*/ fn_HTMLHeader(). /*--- Output the issues ----*/ fn_Header("Database Advisor Findings",2,"TOP"). PUT STREAM stFile UNFORMATTED "
" "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.

" "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.

" "
" SKIP. PUT STREAM stFile UNFORMATTED "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" "" SKIP. FOR EACH ttIssue: PUT STREAM stFile UNFORMATTED "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP. END. /*--- EACH ttIssue ---*/ PUT STREAM stFile UNFORMATTED "
SeverityCategoryIssueDescription
" "" ttIssue.Severity "" ttIssue.IssueGroup "" ttIssue.Issue "" ttIssue.ShortDescr "
" SKIP. /*--- Run the detail sections ---*/ FOR EACH ttIssue: DYNAMIC-FUNCTION("fn_" + ttIssue.DetailLink) NO-ERROR. END. /*--- EACH ttIssue ---*/ /*--- Trailer ---*/ PUT STREAM stFile UNFORMATTED "" SKIP "" SKIP "" 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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 "
" "After imaging helps protect you from data loss and corruption by:" "
    " "
  • Tracking the database blocks that are modified.
    • " SKIP "
  • Allowing those tracked changes to be archived.
    • " SKIP "
  • Allowing the archived changes to be replayed against the last good backup of your database.
    • " SKIP "
" 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.

" SKIP "With AI enabled these are the steps you would follow:" SKIP "
    " "
  • Restore your database from the last good backup.
    • " SKIP "
  • Apply the archived AI files to your database (either all the files or to a certain point in time).
    • " SKIP "
  • Start your database.
    • " SKIP "
  • Relax in the knowledge that you have saved yourself and your company countless hours of grief.
    • " SKIP "
" 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).

" "Recommendations:" "
    " "
  • Use multi volume AI files.
    • " "
  • Set your AI blocksize to 16384 (16K).
    • " "
  • Set the aibufs parameter to 200 as a starting point.
    • " "
" "
" 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 "
" "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.

" "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.

" "
" 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 "
" "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 " "-aibufs " (IF AIBuffers < 100 THEN "200" ELSE STRING(MIN(500,AIBuffers * 1.5),">>>>>>9")) " to your database startup scripts or parameter file. " "
" 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 "
" "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.

" "You can start the AIW through OpenEdge Explorer or by modifying your startup scripts to " "include proaiw YourDB.

" "You will also need to make sure you add the aibufs parameter " "-aibufs 200 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." "
" 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 "
" "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.

" "You can start one or more APWs through OpenEdge Explorer or by modifying your startup scripts to " "include proapw YourDB for each APW you want to start.

" "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." "
" 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 "
" "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.

" "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.

" "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 Table Reads and Index Reads sections will " "provide you with a starting point to decide which objects should be placed in the Alternate pool." "
" 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 "
" "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.

" "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.

" "
" 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 "
" "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 " "-bibufs " (IF BIBuffers < 100 THEN "200" ELSE STRING(MIN(500,BIBuffers * 1.5),">>>>>>9")) " to your database startup scripts or parameter file. " "
" 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 "
" "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.

" "You can start the BIW through OpenEdge Explorer or by modifying your startup scripts to " "include probiw YourDB.

" "You will also need to make sure you add the bibufs parameter " "-bibufs 200 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." "
" 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 "
" "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.

" "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." "
" 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 "
" "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 " "-spin 1000 to your database startup scripts or parameter file.

" "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.

" "Download " fn_ProTop() " for much more detailed information on how to tune the spin setting for your " "environment." "
" 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 "
" "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.

" "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." "
" 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") ">" "" ip_Text "". IF ip_ID <> "" AND ip_ID <> "TOP" THEN PUT STREAM stFile UNFORMATTED "" "Back to Summary" SKIP. PUT STREAM stFile UNFORMATTED "" 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 "" SKIP "" SKIP "" SKIP "OpenEdge Database Advisor - The Mad DBA (TheMadDBA.com)" SKIP "" SKIP "" SKIP "" SKIP "
". 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 "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" 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 "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP. END. /*--- Untracked indexes ---*/ PUT STREAM stFile UNFORMATTED "
Table NameIndex Name% of DB ActivityTotal Activity% of DB ReadsTotal Reads% of DB CreatesTotal Creates% of DB DeletesTotal Deletes
" ttTable.TableName "" ttIndex.IndexName "" STRING(ttIndex.PctDBActivity,">>,>>9.9") "" STRING(ttIndex.TotalActivity,">>>,>>>,>>>,>>9") "" STRING(ttIndex.PctDBReads,">>,>>9.9") "" STRING(ttIndex.TotalReads,">>>,>>>,>>>,>>9") "" STRING(ttIndex.PctDBCreates,">>,>>9.9") "" STRING(ttIndex.TotalCreates,">>>,>>>,>>>,>>9") "" STRING(ttIndex.PctDBDeletes,">>,>>9.9") "" STRING(ttIndex.TotalDeletes,">>>,>>>,>>>,>>9") "
" 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 "
" "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." "
" SKIP. PUT STREAM stFile UNFORMATTED "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" 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 "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP. END. /*--- Untracked indexes ---*/ PUT STREAM stFile UNFORMATTED "
Table NameIndex NameArea NameArea TypeTotal Activity
" ttTable.TableName "" ttIndex.IndexName "" ttArea.AreaName "" ttArea.AreaVersion "" IF ttIndex.IsTracked THEN STRING(ttIndex.TotalActivity,">>>,>>>,>>>,>>9") ELSE "-" "
" 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 "
" "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.

" "Most Important Metrics" SKIP "
    " "
  • % of DB Reads: Which of your indexes are read the most frequently and what percentage of total " "database reads are consumed by each index.
    • " SKIP "
  • 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.
    • " SKIP "
" "
" SKIP. PUT STREAM stFile UNFORMATTED "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" 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 "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" "" SKIP. END. /*--- Untracked indexes ---*/ PUT STREAM stFile UNFORMATTED "
Table NameIndex Name% of DB ReadsTotal ReadsDisk ReadsCached Reads
Per Disk Read		Reads Per SecondReads Per MinuteArea NameArea Type% Reads of
Total Activity		Current
Buffer Pool		Recommended
Buffer Pool
" ttTable.TableName "" ttIndex.IndexName "" STRING(ttIndex.PctDBReads,">>,>>9.9") "" STRING(ttIndex.TotalReads,">>>,>>>,>>>,>>9") "" IF ttIndex.OSReads >= 0 THEN STRING(ttIndex.OSReads,">>>,>>>,>>>,>>9") ELSE IF ttArea.AreaVersion = 1 THEN "Not Supported" ELSE "Update VSTs" "" IF ttIndex.OSReads >= 0 THEN STRING(ttIndex.HitRatio,">>>,>>>,>>>,>>9.99") ELSE IF ttArea.AreaVersion = 1 THEN "Not Supported" ELSE "Update VSTs" "" STRING(ttIndex.TotalReads / DBUptime,">>>,>>>,>>>,>>9") "" STRING(ttIndex.TotalReads / (DBUptime / 60),">>>,>>>,>>>,>>9") "" ttArea.AreaName "" ttArea.AreaVersion "" ttIndex.ReadPercent "" IF ttIndex.BufferPool = 1 THEN "Primary" ELSE IF ttIndex.BufferPool = 2 THEN "Secondary" ELSE "Unknown" "" IF ttIndex.B2Candidate = TRUE THEN "Secondary" ELSE "Primary" "
" 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 "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" 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 "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP. END. /*--- Untracked indexes ---*/ PUT STREAM stFile UNFORMATTED "
Table NameIndex Name% of DB ReadsTotal ReadsReads Per SecondReads Per MinuteArea NameArea TypeIndex BlocksUtil %Factor
" ttTable.TableName "" ttIndex.IndexName "" STRING(ttIndex.PctDBReads,">>,>>9.9") "" STRING(ttIndex.TotalReads,">>>,>>>,>>>,>>9") "" STRING(ttIndex.TotalReads / DBUptime,">>>,>>>,>>>,>>9") "" STRING(ttIndex.TotalReads / (DBUptime / 60),">>>,>>>,>>>,>>9") "" ttArea.AreaName "" ttArea.AreaVersion "" STRING(ttIndex.IndexBlocks,">>>,>>>,>>>,>>9") "" STRING(ttIndex.IndexUtil,">>>,>>9.99") "" STRING(ttIndex.IndexFactor,">,>>9.9") "
" 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 'ProTop'. 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 "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP. FOR EACH ttTable WHERE ttTable.IsTracked = TRUE AND ttTable.TotalActivity > 0 BY ttTable.TotalActivity DESCENDING: PUT STREAM stFile UNFORMATTED "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP. END. /*--- Untracked indexes ---*/ PUT STREAM stFile UNFORMATTED "
Table Name% of DB ActivityTotal Activity% of DB ReadsTotal Reads% of DB CreatesTotal Creates% of DB UpdatesTotal Updates% of DB DeletesTotal Deletes
" ttTable.TableName "" STRING(ttTable.PctDBActivity,">>,>>9.9") "" STRING(ttTable.TotalActivity,">>>,>>>,>>>,>>9") "" STRING(ttTable.PctDBReads,">>,>>9.9") "" STRING(ttTable.TotalReads,">>>,>>>,>>>,>>9") "" STRING(ttTable.PctDBCreates,">>,>>9.9") "" STRING(ttTable.TotalCreates,">>>,>>>,>>>,>>9") "" STRING(ttTable.PctDBUpdates,">>,>>9.9") "" STRING(ttTable.TotalUpdates,">>>,>>>,>>>,>>9") "" STRING(ttTable.PctDBDeletes,">>,>>9.9") "" STRING(ttTable.TotalDeletes,">>>,>>>,>>>,>>9") "
" 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 "
" SKIP. IF HaveTypeOneTables = TRUE THEN PUT STREAM stFile UNFORMATTED "

Type 1 Areas Explained

" 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).

" "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.

" "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.

" SKIP. PUT STREAM stFile UNFORMATTED "

Type 2 Areas Explained

" "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.

" "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." "

Records Per Block (RPB) Explained

" "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 "

Moving Tables Between Areas

" "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.

" "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.

" "
" SKIP. PUT STREAM stFile UNFORMATTED "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP IF DBBlockSize = 8 OR HaveDBAnalys = FALSE THEN "" ELSE "" SKIP "" SKIP "" SKIP IF DBBlockSize = 8 OR HaveDBAnalys = FALSE THEN "" ELSE "" SKIP "" SKIP "" SKIP "" SKIP. FOR EACH ttTable WHERE ttTable.MoveCandidate = TRUE BY ttTable.TotalActivity DESCENDING: FIND ttArea OF ttTable NO-ERROR. PUT STREAM stFile UNFORMATTED "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" "" IF DBBlockSize = 8 OR HaveDBAnalys = FALSE THEN "" ELSE "" IF ttTable.DistinctBlocks = 0 THEN "" ELSE "" IF ttTable.OptCurrentBlocks = 0 THEN "" ELSE "" IF DBBlockSize = 8 OR HaveDBAnalys = FALSE THEN "" ELSE IF ttTable.Opt8KBlocks = 0 THEN "" ELSE "" "" SKIP "" SKIP "" SKIP. END. /*--- Untracked indexes ---*/ PUT STREAM stFile UNFORMATTED "
Table NameArea NameArea TypeRecord CountRecord FragmentsMean Record SizeArea RPBSuggested RPB
(Current DB Block Size)		Suggested RPB
(8K Blocks)		Estimated BlocksOptimal Blocks
for Suggested RPB		Optimal Blocks
for 8K DB		Total Activity% of DB Activity
" ttTable.TableName "" ttArea.AreaName "" ttArea.AreaVersion "" IF HaveDBAnalys = TRUE THEN STRING(ttTable.RecordCount,">>>,>>>,>>>,>>9") ELSE "-" "" IF HaveDBAnalys = TRUE THEN STRING(ttTable.Fragments,">>>,>>>,>>>,>>9") ELSE "-" "" IF HaveDBAnalys = TRUE THEN STRING(ttTable.RecordSize,">>>,>>>,>>>,>>9") ELSE "-" "" ttArea.AreaRPB "" IF HaveDBAnalys THEN STRING(ttTable.RPBPerformance,">>9") ELSE "-" "" + STRING(ttTable.OptRPBPerformance,">>9") + "-" + STRING(ttTable.DistinctBlocks,">>>,>>>,>>>,>>9") + "-" + STRING(ttTable.OptCurrentBlocks,">>>,>>>,>>>,>>9") + "-" + STRING(ttTable.Opt8KBlocks,">>>,>>>,>>>,>>9") + "" IF ttTable.IsTracked THEN STRING(ttTable.TotalActivity,">>>,>>>,>>>,>>9") ELSE "-" "" IF ttTable.IsTracked AND ttTable.PctDBActivity <> 0 THEN STRING(ttTable.PctDBActivity,">>>,>>>,>>>,>>9.9") ELSE "-" "
" 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 "
" "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.

" "Most Important Metrics" SKIP "
    " "
  • % of DB Reads: Which of your tables are read the most frequently and what percentage of total " "database reads are consumed by each table.
    • " SKIP "
  • % 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.
    • " SKIP "
  • 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.
    • " SKIP "
" "
" SKIP. PUT STREAM stFile UNFORMATTED "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" 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 "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP "" "" SKIP. END. /*--- Untracked indexes ---*/ PUT STREAM stFile UNFORMATTED "
Table Name% of DB ReadsTotal ReadsDisk ReadsCached Reads
Per Disk Read		Reads Per SecondReads Per Minute% of Table
Read Per Minute		Record CountArea NameArea Type% Reads of
Total Activity		Current
Buffer Pool		Recommended
Buffer Pool
" ttTable.TableName "" STRING(ttTable.PctDBReads,">>,>>9.9") "" STRING(ttTable.TotalReads,">>>,>>>,>>>,>>9") "" IF ttTable.OSReads >= 0 THEN STRING(ttTable.OSReads,">>>,>>>,>>>,>>9") ELSE IF ttArea.AreaVersion = 1 THEN "Not Supported" ELSE "Update VSTs" "" IF ttTable.OSReads >= 0 THEN STRING(ttTable.HitRatio,">>>,>>>,>>>,>>9.99") ELSE IF ttArea.AreaVersion = 1 THEN "Not Supported" ELSE "Update VSTs" "" STRING(ttTable.TotalReads / DBUptime,">>>,>>>,>>>,>>9") "" STRING(ttTable.TotalReads / (DBUptime / 60),">>>,>>>,>>>,>>9") "" IF ttTable.RecordCount > 0 THEN STRING(((ttTable.TotalReads / (DBUptime / 60)) / ttTable.RecordCount) * 100,">>>,>>>,>>>,>>9.9") ELSE "-" "" IF HaveDBAnalys = TRUE THEN STRING(ttTable.RecordCount,">>>,>>>,>>>,>>9") ELSE "-" "" ttArea.AreaName "" ttArea.AreaVersion "" ttTable.ReadPercent "" IF ttTable.BufferPool = 1 THEN "Primary" ELSE IF ttTable.BufferPool = 2 THEN "Secondary" ELSE "Unknown" "" IF ttTable.B2Candidate = TRUE THEN "Secondary" ELSE "Primary" "
" 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 "
" "OpenEdge has the ability to track reads, creates and deletes on an index level. " "This is extremely valuable information to help identify:
  • Where you have index design or " "coding issues in your application.
  • Which buffer pool (primary or alternate) an index " "should belong to.
  • If you have unused indexes in your database.
" "You can correct this by setting the indexrangesize parameter " "-indexrangesize " STRING(MaxIndexNumber) "" " in your database startup script or parameter file and restarting your database." "
" SKIP. PUT STREAM stFile UNFORMATTED "" SKIP "" SKIP "" SKIP "" SKIP "" SKIP. FOR EACH ttIndex /*WHERE ttIndex.IsTracked = FALSE */, EACH ttTable OF ttIndex BY ttTable.TableName BY ttIndex.IndexName: PUT STREAM stFile UNFORMATTED "" SKIP "" SKIP "" SKIP "" SKIP. END. /*--- Untracked indexes ---*/ PUT STREAM stFile UNFORMATTED "
Table NameIndex Name
" ttTable.TableName "" ttIndex.IndexName "
" 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 "
" "OpenEdge has the ability to track reads, creates, updates and deletes on a table level. " "This is extremely valuable information to help identify:
  • Where you have index design or " "coding issues in your application.
  • Which buffer pool (primary or alternate) a table " "should belong to.
  • If a high volume table deserves to have a dedicated storage area.
" SKIP "You can correct this by setting the tablerangesize parameter " "-tablerangesize " STRING(MaxTableNumber) "" " in your database startup script or parameter file " " and then restarting your database." "
" SKIP. PUT STREAM stFile UNFORMATTED "" SKIP "" SKIP "" SKIP "" SKIP. FOR EACH ttTable /*WHERE ttTable.IsTracked = FALSE */ BY ttTable.TableName: FIND ttArea OF ttTable NO-ERROR. PUT STREAM stFile UNFORMATTED "" SKIP "" SKIP "" SKIP. END. /*--- Untracked tables ---*/ PUT STREAM stFile UNFORMATTED "
Untracked Table
" ttTable.TableName "
" 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 "
" "The Virtual System Tables (VSTs) for your database do not match your OpenEdge version " "and need to updated to use this feature.

" "You will need to shut down your database and run the following command from the " "database server proutil YourDB -C updatevst

" "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." "
" SKIP. RETURN FALSE. /* Function return value. */ END FUNCTION. /* _UIB-CODE-BLOCK-END */ &ANALYZE-RESUME &ENDIF