Tandem Reload Analyzer Manual Abstract This manual describes how to use Tandem Reload Analyzer (TRA) to identify fragmented files or tables in Enscribe, NonStop SQL/MP and NonStop SQL/MX so they benefit from an online reorganization. Product Version Reload Analyzer H01 Supported Release Version Updates (RVUs) This manual supports H06.21, J06.10 and all subsequent releases until otherwise indicated in a new edition.
Document History Part Number Product Version Published 626779-001 Reload Analyzer H01 August 2010 129830 Reload Analyzer D42 November 1996 Tandem Reload Analyzer Manual Page 2 of 42
Table of Contents Tandem Reload Analyzer Manual ........................................................................................ 1 Abstract ........................................................................................................................... 1 Product Version ............................................................................................................... 1 Supported Release Version Updates (RVUs) ..................................................................
When to Reload ................................................................................................................ 36 Appendix A: Error Messages ............................................................................................. 38 Glossary ..............................................................................................................................
List of Figures and Tables Figure 1.0 Benefits of Reload ............................................................................................... 16 Figure 1-1. Key-Sequenced File Structure............................................................................ 18 Figure 1-2. An Organized File............................................................................................... 20 Figure 1-3. A Disorganized File ............................................................................
What’s New in This Manual New and Changed Information This is the third edition of the Tandem Reload Analyzer Manual. This section identifies the information that has been added, deleted, or revised since the previous edition. The H01 version of Tandem Reload Analyzer supports 32kb block sizes. References to NonStop SQL have been changed throughout this document to reference NonStop SQL/MP. References to NonStop SQL/MX have been added throughout this document.
This version of the manual was produced using FrameMaker. All Titles of the manuals you can read for more information have been updated to match published Tandem CD Read and TIM titles. Section 1: Tandem Reload Analyzer Overview and Section 2: Using Tandem Reload Analyzer Examples and figures have been updated. Section 1: Tandem Reload Analyzer Overview and Section 2: Using Tandem Reload Analyzer Cause, Effect, and Recovery texts have been expanded.
About This Manual This manual describes Tandem Reload Analyzer, a database management productivity tool that analyzes audited, key-sequenced Enscribe files, NonStop SQL/MP objects (tables or indexes only) and NonStop SQL/MX objects. Tandem Reload Analyzer (Reload Analyzer) helps you determine whether a file, NonStop SQL/MP or NonStop SQL/MX object needs to be reloaded.
HP NonStop SQL/MX Installation and Management Guide Enscribe Programmer’s Guide Notation Conventions Hypertext Links Blue underline is used to indicate a hypertext link within text. By clicking a passage of text with a blue underline, you are taken to the location described. For example: This requirement is described under Backup DAM Volumes and Physical Disk Drives on page 3-2. General Syntax Notation This list summarizes the notation conventions for syntax presentation in this manual.
TERM [\system-name.]$terminal-name INT[ERRUPTS] A group of items enclosed in brackets is a list from which you can choose one item or none. The items in the list can be arranged either vertically, with aligned brackets on each side of the list, or horizontally, enclosed in a pair of brackets and separated by vertical lines. For example: FC [ num ] [ -num ] [ text ] K [ X | D ] address { } Braces A group of items enclosed in braces is a list from which you are required to choose one item.
error := NEXTFILENAME ( file-name ) ; LISTOPENS SU $process-name.#su-name Quotation marks around a symbol such as a bracket or brace indicate the symbol is a required character that you must type as shown. For example: "[" repetition-constant-list "]" Item Spacing Spaces shown between items are required unless one of the items is a punctuation symbol such as a parenthesis or a comma. For example: CALL STEPMOM ( process-id ) ; If there is no space between two items, spaces are not permitted.
error := FILENAME_COMPARE_ ( filename1:length , filename2:length ) ; !i:i !i:i !o:i In procedure calls, the !o:i notation follows an output buffer parameter that has a corresponding input parameter specifying the maximum length of the output buffer in bytes. For example: error := FILE_GETINFO_ ( filenum , [ filename:maxlen ] ) ; !i !o:i Notation for Messages This list summarizes the notation conventions for the presentation of displayed messages in this manual.
horizontally, enclosed in a pair of brackets and separated by vertical lines. For example: proc-name trapped [ in SQL | in SQL file system ] { } Braces A group of items enclosed in braces is a list of all possible items that can be displayed, of which one is actually displayed. The items in the list can be arranged either vertically, with aligned braces on each side of the list, or horizontally, enclosed in a pair of braces and separated by vertical lines.
lowercase letters Words in lowercase letters are words that are part of the notation, including Data Definition Language (DDL) keywords. For example: token-type !r The !r notation following a token or field name indicates that the token or field is required. For example: ZCOM-TKN-OBJNAME token-type ZSPI-TYP-STRING. !r !o The !o notation following a token or field name indicates that the token or field is optional. For example: ZSPI-TKN-MANAGER Tandem Reload Analyzer Manual token-type ZSPI-TYP-FNAME32.
Section 1: Tandem Reload Analyzer Overview The File Utility Program (FUP) RELOAD command physically reorganizes audited, key-sequenced Enscribe files, NonStop SQL/MP objects and SQL/MX objects that have become fragmented or disorganized. Because a FUP RELOAD is not a procedure that you should use indiscriminately, determining when to reload is difficult.
Generates FUP RELOAD command when percentage of average blocks per chain is below user specified threshold. Performs both Vertical index-block and Horizontal data-block chain analysis. Diagnoses too many vertical index-blocks which cause poor random access. Diagnoses too many horizontal data block chains which cause very poor sequential access (up to 128x slower).
Key-Sequenced Files To help you understand why a FUP RELOAD is necessary for a file, this section describes key-sequenced file structure. You will also learn how a file becomes disorganized. If you want more information, see the ENSCRIBE Programmer’s Guide. A key-sequenced file consists of a set of fixed or variable-length records. Each record in a key-sequenced file is uniquely identified by the value of its primary key.
Figure 1-1. Key-Sequenced File Structure Each data block contains a header plus one or more data records, depending on the record size and data block size. For each data block, an index block entry contains the value of the primary-key field for the first record in the data block and the address of that data block. The data blocks in Figure 1-1 have a certain percentage of empty space or slack.
Index blocks function much like data blocks in that they contain records. Those records are actually pointers to respective data blocks. These index blocks are the mechanism for key-sequenced data retrieval. When an index block becomes full, it splits, a new index block is created, and some of the pointers are moved from the old index block to the new one. The first time this occurs in a file, the disk process must generate a new level of indexes.
Figure 1-2. An Organized File The data blocks are both physically and logically contiguous on disk. The next pointer from each data block points to the adjacent data block. In Figure 1-2, the dashed lines with arrowheads point from the block to its adjacent block. The next and previous pointers are the only differences between Figure 1-2 and Figure 1-1. Note that the records within the data and index blocks are in logical order by the primary record key.
A file does not have to be misused to become disorganized. Over the life of a file, these operations can cause it to become disorganized, namely: Random inserts that cause block splits Deletions of the last record in the data block that cause the block to collapse Updates with record length changes that cause the record to no longer fit in its current data block In Figure 1-2, suppose all records in the data block starting with Carol’s record are deleted, causing it to collapse.
Figure 1-3. A Disorganized File The data blocks in Figure 1-3 are physically adjacent but are not logically contiguous. That is, the next pointer from the first data block does not point to its adjacent block, but points to a nonadjacent data block instead. The file lacks physical continuity and proper space utilization. Physical discontinuity occurs when the physical order of the data blocks does not match the blocks’ logical order. This file also suffers from improper space utilization.
block has no records. When the records from that block were deleted, it became empty. Remember that a condition of an organized file is to have the fewest possible index levels. If you compare Figure 1-3 and Figure 1-2, you will notice that there is one more index level in Figure 1-3. (The dashed line above the second level index block points to the other index blocks on this index level.) The more index levels, the longer the disk access time and the disk space required.
Section 2: Using Tandem Reload Analyzer This section explains what you need to install Reload Analyzer, run it, and analyze the output. Once you understand the output, you can decide whether reorganizing a file is necessary. System Requirements The H01 Reload Analyzer version requires the HP NonStop operating system, version H06.21/J06.
Since Reload Analyzer opens a file for unstructured read-only access, it will not damage or interfere with a file, for example, by changing the file or record locks or damaging the file’s contents. In addition, Reload Analyzer does not interfere with other operations on the system. Although Reload Analyzer does not interfere with operations, it does generate I/O; so you might want to run it during off hours. OUT reptfile is the designated output device for displaying the output.
directed to the home terminal. If you use the NOWAIT option with Reload Analyzer and direct the output from another program to the home terminal, that program’s output and the status indicator will be interspersed on the home terminal. This makes it hard to distinguish the output. For more information about the TACL command-line options, see the Tandem Advanced Command Language (TACL) Reference Manual.
Note. If the file is empty and you press Break, the process will not be aborted. Status Indicator The status indicator, a period (.), tells you that Reload Analyzer is working. The status indicator is displayed on the home terminal when you direct the output to the home terminal. For each 1000 blocks that Reload Analyzer reads, another period is displayed, with ten periods per line.
Example 2-2. Sample Reload Analyzer Output Tandem Reload Analyzer - T9516H01-AAF (31MAR2010)Start time - 16:47.35 $DATA.INSURNC.
General Information The General Information section displays properties of the file being analyzed. The information is derived from the FUP INFO filename, DETAIL command format for NonStop SQL/MP and NonStop SQL/MX objects and key-sequenced Enscribe files. Example 2-3 shows the format of the General Information section. Example 2-3.
Audited shows whether or not the file is audited. EOF is the end-of-file pointer containing the relative byte address of the byte following the last significant data byte.
The variables that appear in Example 2-4 are: Total blocks read is the total of index blocks, data blocks, free blocks, bitmap blocks, and unrecognizable blocks. Free blocks is the total unused blocks. A file with 0 free blocks is unusual. Index blocks read Bitmap blocks Data blocks read is the total index blocks. is the total bitmap blocks. Bitmap blocks are used to keep track of block usage in a file. is the total data blocks. Unrecognizable is the total unrecognizable blocks.
Data Chain Information The Data Chain Information section is the most significant. This section shows the number of data chains, a histogram of the chains, and the average blocks in each chain. A data chain is the number of contiguous data blocks. Example 2-5 shows the format of the Data Chain Information section. Example 2-5.
blocks in average chain organized file, the file will have one long chain. This number is calculated by taking the average blocks in a chain and dividing it by the total data blocks. Table 2-1. Values in Chain Length Histogram File < 100 MB File > 100 MB 1 1 - 25 2 - 10 26 - 50 11 - 25 51 - 100 26 - 50 101 - 250 51 - 100 251 - 500 > 100 > 500 Counting Data Chains A data chain is a set of logically contiguous data blocks.
Data Chain Information: Number of chains 100 Longest chain 2 Chain length histogram 1 0 2 - 10 100 11 - 25 0 26 - 50 0 51 - 100 0 > 100 0 Avg blocks/chain % of total blks in avg chain 2 1% File A has one hundred chains, each 2 blocks long. The average number of data blocks per chain is 2, which is 200 total blocks divided by the number of chains, 100. The percentage of total blocks in the average chain, the last value in the Data Chain Information section, is 1 percent.
The average blocks per chain The percentage of total blocks in the average chain Reload Analyzer cannot tell you whether or not to reload a file. Instead, you must take the output, analyze it, and consider the conditions on your system to determine whether it is advantageous to reload a file. Reload Analyzer only gives you only the information you need to make an informed decision about reloading. Whether you reload a file and when you do so is up to you.
Longest chain Previously 15 Chain length histogram 1 2 - 10 11 -25 26 - 50 51 - 100 > 100 Avg blocks/chain Previously 1 % of total blks in avg Previously 1% 197 <-- 0 0 0 0 0 1 197 chain < 100% <-<-- \NONSTOP.$DATA.INSURNC.SALESDB completed at - 17:05.48 Reload Analyzer is stopping. The values in the General Information section have not changed. In the Block Information section, the number of index blocks and data blocks has decreased significantly.
You need to weigh the benefits of reloading the file against the costs of reloading. The cost of reloading a file is significant and includes the following: Performance degradation of processes that are using the file while the file is being reloaded Rapid generation of large amounts of Transaction Management Facility (TMF) audit data The extreme cases of good and bad file organization are easy to identify: If the average chain contains 100 percent of the blocks, the file’s organization is ideal.
Appendix A: Error Messages This appendix gives each Tandem Reload Analyzer (Reload Analyzer) error message, what caused the error, and how to recover from it. filename is not an edit file. Cause. An Edit file name was not entered when you were running Reload Analyzer. Effect. Reload Analyzer abends. Recovery. Make sure that the file name you enter is an Edit file. Reload Analyzer can tell if the file you entered is an Edit file name or single file name. filename Edit file Error: nnn. Cause.
Recovery. Enter a key-sequenced file or NonStop SQL table as input. Tandem Reload Analyzer only analyzes key-sequenced files or NonStop SQL tables. outfile failed on Open, error nnn. Cause. A file-system error occurred when Reload Analyzer tried to write to the designated output file. Effect. Reload Analyzer does not start. Recovery. Refer to the Guardian Procedure Errors and Messages Manual for information about the error number indicated by nnn. filename does not exist, error nnn. Cause.
infile must be an existing disk file. Cause. The specified input file is not an existing disk file. Effect. Reload Analyzer does not start. Recovery. Specify an existing key-sequenced file or Edit file as the input file. fupin file must be a disk file. Cause. The specified fupin file is not a disk file. Effect. Reload Analyzer does not start. Recovery. Specify an existing Guardian disk file or the name of the file you would like Reload Analyzer to create. The file name must have the form \system.$volume.
Glossary data chain A set of physically and logically consecutive data blocks in a file or table. disorganized file A file or table where the physical order of the data blocks does not match their logical order, and the index and data blocks do not contain the optimal number of records. Disk Process 2 (DP2) The disk process used by NonStop SQL. Enscribe A HP NonStop software facility that provides high-level access to, and manipulation of, records in a relational database.
Online Transaction Processing (OLTP) Data processing that involves immediate access and updates to a database while the user waits for the results. The database is always up to date. organized file The data blocks in the file or table are in contiguous order, and the index and data blocks have little or no wasted disk space. primary key A unique field in a record in a key-sequenced file.
