Database Advisor

The Database Advisor is intended to assist in identifying some of the most common database issues. For the most accurate results you will need to have a recent database analysis file and run the advisor against your production database.

Links

DB Advisor Source Code

Sample Output

Current Feature List

  • Validates table and index monitoring parameters
  • Validates database block size
  • Identifies checkpointing issues
  • Validates BI block size and buffer parameters
  • Validates AI block size and buffer parameters
  • Suggests tables that should be moved to a different storage area (including proper RPB settings)
  • Identifies indexes that need rebuilding or storage area changes
  • Lists tables by level of activity
  • List indexes by level of activity
  • Makes buffer pool assignment suggestions
  • Checks various other settings related to performance

Generating A Database Analysis File

The database analysis will read every block in your database and collect a number of statistics about the tables and indexes. Depending on the size of your database and the speed of your disk subsystem it might run from anywhere from a few minutes to many hours.

It can be run online with users accessing the database but it should be run during a relatively quiet time for your application or against a recently restored copy of your database.

Proutil Command

Log in to the server that hosts your database and run the following command from either a proenv prompt or a prompt that has the appropriate environment variables and path.

proutil Full_Path_To_DB -C dbanalys > Your_Output_File

If your database is named mydb and is stored in the /usr/database directory, your command would look like this:

proutil /usr/database/mydb -C dbanalys > myfile.txt

Usage

Connect to your production database and open the procedure editor and execute the code below

RUN Path_To_advisor.p (INPUT "Path_To_DB_Analysis_File", INPUT "Output_HTML_File").

Example command:

RUN c:\mydir\advisor.p (INPUT "c:\mydir\dbinfo.txt","c:\mydir\advisor.html").

Session Tracing Tools

These tools simplify application tuning by tracing table and index reads as well as detailed timings per line of code. If you aren't already using similar tools during application development you should download this quickly. There really isn't an easier way to determine why you are having performance issues.

Components

  • The SessionTrace class which supports various methods of tracing code and integration with your application
    • Detailed timings at a line of code level
    • Table activity for your session
    • Index activity for your session
    • Separation of activity by snapshots within a traced session
    • Ability to add custom debugging information to the trace file output
    • Conditional tracing by user list or program wildcards
    • Conditional output of tracing files by elapsed time or total activity
  • The GUI tool which supports easy access to tracing code and viewing the various metrics
    • Load previously generated trace files
    • Trace a program or programs directly from the tool (no code changes required)
    • Use the Ad Hoc Code option to write and trace code
    • Customize layouts to show specific metrics and columns
    • Compile and integrate the source code with the detailed line timings
    • Easily customize the application integration settings

GUI Screen Shots

Screen Shot Gallery

Using The SessionTrace Class Directly

To trace a session you can use the SessionTrace class directly in your application code. It can generate HTML reports or JSON files that can be loaded into the GUI tool.

Unconditional Tracing Example

/*--- Define Variables ---*/ DEFINE VARIABLE iCustNum LIKE customer.cust-num NO-UNDO. DEFINE VARIABLE cState LIKE customer.state NO-UNDO. DEFINE VARIABLE cName LIKE customer.NAME NO-UNDO. DEFINE VARIABLE cQuery AS CHARACTER NO-UNDO. DEFINE VARIABLE lWhere AS LOGICAL NO-UNDO. DEFINE VARIABLE hQuery AS HANDLE NO-UNDO. /*--- Update the values ---*/ UPDATE iCustNum cState cName WITH 1 COL NO-BOX FRAME a VIEW-AS DIALOG-BOX. /*--- Use the profiler to gather detailed line timings ---*/ SessionTrace:UseProfiler = TRUE. /*--- Start the session tracing ---*/ SessionTrace:StartTracing(). /*--- Construct the where clause ---*/ ASSIGN cQuery = "FOR EACH customer ". IF iCustNum <> 0 THEN ASSIGN cQuery = cQuery + " WHERE customer.cust-num = " + STRING(iCustNum) + " " lWhere = TRUE. IF cState <> "" THEN ASSIGN cQuery = cQuery + (IF lWhere = FALSE THEN "WHERE " ELSE "AND ") + "customer.state = '" + cState + "' " lWhere = TRUE. IF cName <> "" THEN ASSIGN cQuery = cQuery + (IF lWhere = FALSE THEN "WHERE " ELSE "AND ") + "customer.name begins '" + cName + "' " lWhere = TRUE. /*--- Save some of the debug info ---*/ SessionTrace:AddDebugInfo("Cust-Num",STRING(iCustNum)). SessionTrace:AddDebugInfo("State",cState). SessionTrace:AddDebugInfo("Name",cName). SessionTrace:AddDebugInfo("Query",cQuery). /*--- Build and execute the query ---*/ CREATE QUERY hQuery. hQuery:SET-BUFFERS(BUFFER Customer:HANDLE). hQuery:QUERY-PREPARE(cQuery). hQuery:QUERY-OPEN. REPEAT: hQuery:GET-NEXT(). IF hQuery:QUERY-OFF-END THEN LEAVE. END. hQuery:QUERY-CLOSE(). DELETE OBJECT hQuery NO-ERROR. /*--- Stop the tracing ---*/ SessionTrace:StopTracing(). /*--- Write the data out in HTML format ---*/ SessionTrace:WriteHTML("MySession.html"). /*--- Write the data out in JSON format for the GUI tool to load ---*/ SessionTrace:WriteTrace("MySession.trc").

Conditional Tracing Example

/*--- Define Variables ---*/ DEFINE VARIABLE iCustNum LIKE customer.cust-num NO-UNDO. DEFINE VARIABLE cState LIKE customer.state NO-UNDO. DEFINE VARIABLE cName LIKE customer.NAME NO-UNDO. DEFINE VARIABLE cQuery AS CHARACTER NO-UNDO. DEFINE VARIABLE lWhere AS LOGICAL NO-UNDO. DEFINE VARIABLE hQuery AS HANDLE NO-UNDO. /*--- Update the values ---*/ UPDATE iCustNum cState cName WITH 1 COL NO-BOX FRAME a VIEW-AS DIALOG-BOX. /*--- Start the session tracing? ---*/ /*--- This will decide based on the configuration settings if we should enable tracing based on the ---*/ /*--- current user, the program name and if tracing is already enabled at a higher level. Depending ---*/ /*--- configuration settings it may also create a new snapshot for this section. ---*/ SessionTrace:StartFromConfig(). /*--- Construct the where clause ---*/ ASSIGN cQuery = "FOR EACH customer ". IF iCustNum <> 0 THEN ASSIGN cQuery = cQuery + " WHERE customer.cust-num = " + STRING(iCustNum) + " " lWhere = TRUE. IF cState <> "" THEN ASSIGN cQuery = cQuery + (IF lWhere = FALSE THEN "WHERE " ELSE "AND ") + "customer.state = '" + cState + "' " lWhere = TRUE. IF cName <> "" THEN ASSIGN cQuery = cQuery + (IF lWhere = FALSE THEN "WHERE " ELSE "AND ") + "customer.name begins '" + cName + "' " lWhere = TRUE. /*--- Save some of the debug info if tracing is enabled ---*/ IF SessionTrace:TracingActive = TRUE THEN DO: SessionTrace:AddDebugInfo("Cust-Num",STRING(iCustNum)). SessionTrace:AddDebugInfo("State",cState). SessionTrace:AddDebugInfo("Name",cName). SessionTrace:AddDebugInfo("Query",cQuery). END. /*--- Build and execute the query ---*/ CREATE QUERY hQuery. hQuery:SET-BUFFERS(BUFFER Customer:HANDLE). hQuery:QUERY-PREPARE(cQuery). hQuery:QUERY-OPEN. REPEAT: hQuery:GET-NEXT(). IF hQuery:QUERY-OFF-END THEN LEAVE. END. hQuery:QUERY-CLOSE(). DELETE OBJECT hQuery NO-ERROR. /*--- Stop the tracing? ---*/ /*--- This section will decide if tracing should be stopped if it was started in the ---*/ /*--- section above. It will also write the output file conditionally based on the ---*/ /*--- timing and activity filters from the configuration file ---*/ SessionTrace:StopFromConfig().

Links

Zip File that contains all of the tools and example integration code

HTML Sample Output

Type I Area Block Mapper

I strongly advise you to run the Database Advisor before running the mapper utility. It will provide much more information about your database and which tables reside in Type I areas along with the area names.

Be advised that this tool will read every record for every table that you specify (either by area name or table name). If you have a recently restored copy of your production database it is perfectly acceptable to run this tool against the copy. It does not depend on the same internal statistcs as the Database Advisor.

In most cases it makes little sense to run this against larger tables because smaller tables are usually much more scattered than tables with frequent record creation.

Links

Block Mapper Source Code

Sample Output

Usage

After downloading the code, connect to the database and open a procedure editor. From the editor execute this code:

RUN Path_To_tableblocks.p (INPUT "Output_HTML_File", INPUT "AreaList", INPUT "TableList").

You can specify CAN-DO style comma delimited lists or wild cards for the AreaList and the TableList parameters.

CAN-DO options

  • Match all items: "*"
  • Specific list of items: "Area Name1,Area Name2"
  • All items except for specific items: "*,!bigtable1,!bigtable2"

Suggestions

Start with a few small tables and see how long the mapper takes to run on your environment. Every effort has been made to make sure this tool runs as efficiently as possible but the more scattered your data the longer it will take.

RUN Path_To_tableblocks.p (INPUT "Output_HTML_File", INPUT "*", INPUT "Table1,Table2,Table3").

Once you are familiar with the tool and how your application performs, run the mapper against a specific area using exclusions in the table list for any large tables that are stored in that area.

RUN Path_To_tableblocks.p (INPUT "Output_HTML_File", INPUT "MyArea", INPUT "*,!bigtable1,!bigtable2").