DBTune for Rdb Version 6.2 __________________________________________________________________ Rdb 7.3 requires a min of EV56 processor on AXP/Alpha systems to run. This same requirement applies to DBTune 6.2 in order to access and use the Rdb software library. The Rdb 7.3 documentation says that it will work under an emulation mode with processors < EV56, but this is a typo that Oracle says they are correcting. We also STRONGLY recommend a min of OpenVMS 8.4. DBTune for Rdb Version 6.1 __________________________________________________________________ DBTune V6.1 has extensive support for partitioning. In order to determine the proper tuning/sizing parameters for each partition, the exact number of EXISTING records for each partition must be determined. This process takes significantly more time in V61 than in previous versions of DBTune where partitions were only partially supported. For heavily partitioned databases, running the DBTune scrpt generator as a batch job using the available sample "batch" script may want to be considered. When using the DBTUne pad/modpad file for partitins, the order of the partitins listed in the file is the same as what you would see issuing the SQL "show table xxx" command. DBTune for Rdb Version 6.0 __________________________________________________________________ Logical names have changed for control and running of DBTune. Previously, logicals were designated by "FREND$...". This has been changed to "ALI_...", and the use of "$"s has been replaced by underscores ("_"). For example, "FREND$DBTUNE$HOME" is now "ALI_DBTUNE_HOME". There is a new logical for the different SQL rules in effect. "Databases which have been created using the earlier SQL92 standard, or have been successfully tuned with earlier versions of DBTune, should consider/test defining the VMS logical ALI_SQL_RULES to ÒSQL92Ó prior to running DBTune V6. This is most commonly required when reserved words were used as identifiers (ie column names for example), and were enclosed in Ò Ó when originally defined (ie using ÒyearÓ as a column name for example)." Some cosmetic corrections were made to report/screen displays. The Kanji character set is now processed and displayed correctly, although screens and reports still use English "headings" and descriptive text. To increase import/export tuning speed, make sure to assign "set RMS_DEFAULT/EXTEND_QUANTITY=50000" before the export executes to reduce the file extension time (the default extensino is 2 blocks). If you are tuning a system that was created using RDO syntax, DBTune WILL NOT generate proper SQL for restoring views since the internally stored "creation text" in Rdb is not in SQL. However, the "RMU/extract/language=RDO" command can give you the RDO syntax for views, etc that you may need in restoring these to the database. __________________________________________________________________ __________________________________________________________________ Changes Since Last Release: 1) Ranked indices supported 2) Vertical and horizontal partitioning supported 3) DBDISKs are now resolved all the way to the physical device when determining available disk space for file placement 4) Support for multiple SQL Rules 5) Compression params file default has changed to reflect Rdb default value of "Yes" for sizing calculations. Check your previous params file for appropriate value in your specific case. 6) Logical names for disks are now retained whenever possible. New logical names are more descriptive. 7) "E"xisting and "R"elocate tuning methods are now functioning as originally intended. 8) Improved file size and Rdb parameter computations 9) Multiple overflow directories supported10) Complete rewrite of the DBTune product into native "C" Other Notes: Mulltiple ALI_DBTUNE_SCRATCH logicals In order to generate scripts for MULITPLE databases at the SAME TIME, each executing image of DBTune should have a different ALI_DBTUNE_SCRATCH logical defined and pointing to a different directory. This eliminates the "possibility" of one job deleting the files needed by another during the "clean up" phase. Former Usage of RDO May Cause SQL Scripts to Fail If you choose to use RMU/UNLOADs and RMU/LOADs to tune selected tables of a database, items which are defined on those tables (views, constraints, triggers, comments) must be dropped and then re-created when the tables are dropped and re-created. If any views, constraints, or triggers were previously defined with RDO, the tuning scripts may fail when the items are re-created using SQL statements (DBTune uses the original definition of the item to re-create it). If database items were formerly created using RDO, the user should review the SQL scripts generated by DBTune BEFORE executing the MAIN_DRIVER command file to ensure that no syntactical violations exist for views, constraints, and/or triggers. Cannot Access a Database Over a Network Using DBTune DBTune cannot be used to generate SQL scripts for a database over the network because of limitations of Rdb and RMU. Scripts can be generated on a local node, however, and then copied over the network to be executed against a database on a remote node. Abbreviations for Non-English Languages For those users applying DBTune to databases defined with a language other than English, a version of DBTune's abbreviation file for your language may be provided. As of DBTune V3.0, abbreviation files have been provided for the following languages: FRANCAIS, DEUTSCH, NEDERLANDS, and ENGLISH. For example, if users in France want to use French abbreviations, they can do the following: $ EDIT ALI_DBTUNE_HOMEHOME:DBTUNE.COM - search for "ENGLISH" - comment out the line 'FREND_LANGUAGE :== "ENGLISH"' - uncomment the line 'FREND_LANGUAGE :== "FRANCAIS"' - exit and save the file The user only needs to perform this step once and DBTune will use the French abbreviation file for every subsequent execution. Similar steps can be performed for the other languages mentioned above. Maintenance Logicals Following are logicals which can be used to override some of DBTune's default values or to get more detailed output. It is generally recommended that these logicals NOT be changed except under the advice of a support person from ALI. ALI_FAST_COMMIT If AIJ journaling is enabled, this logical is used to determine if Fast Commit processing should be enabled. Valid values: "Y" - enable Fast Commit "N" - do NOT enable Fast Commit Default: "N" - do NOT enable ALI_IDX_DUP_LIM Sets the upper limit for flagging excessive index duplicates in the REVIEW_AND_GUIDE report. Any index with more than this number of average duplicates will be printed to the report. Valid values: 0 to 100000 Default: 10 ALI_TARGET_BUFFER Sets a "target" buffer size (in blocks) for DBTune to tune towards. For example, if the disk blocking factor for a database system has been set to 18 blocks, the user may wish for the database buffer size to match the disk blocking factor to increase performance. Valid values: 6 to 64 Default: 12 ALI_BUFFER_PAGE Points to a data file used by DBTune to determine "valid" buffer size and page size combinations. See the file BUFFER_PAGE.DAT located in the ALI_DBTUNE_HOME directory for more information on how this logical is used. Valid values: A valid file specification Default: ALI_DBTUNE_ HOME:BUFFER_ PAGE.DAT ALI_BIAS_SCALE Points to a data file used by DBTune to adjust page sizes and sorted index node sizes during tuning calculations. The adjustments are based on the individual access bias settings for database tables. See the file BIAS_SCALE.DAT located in the ALI_DBTUNE_HOME directory for more information on how this logical is used. Valid values: A valid file specification Default: ALI_DBTUNE_ HOME:BIAS_ SCALE.DAT ALI_SEGSTR_MB Sets a threshold (in megabytes) to determine how many storage areas are needed to store segmented strings. This logical is only used for SQL EXPORT/IMPORTs. For example, assume that for a particular database, segmented string storage requires 3 gigabytes of disk space. How many storage areas do you wish this to be distributed over and how large do you wish those areas to be? If this logical is assigned the value 512 megabytes, then at least six 'LIST' storage areas will be created with a maximum size of 512 megabytes each. If the segmented string storage requirements for a database are high, this logical can be reduced to create more 'LIST' storage areas to further distribute them across available disks. If segmented string storage requirements are low, this value can be left at the default value to reduce the number of storage areas. This logical sets the upper limit for how large a single 'LIST' storage area can be. Valid values: 10 to 1024 megabytes Default: 256 ALI_DISKIO_RATE Sets the "standard" disk I/O rate used to calculate the amount of time required to tune a database using RMU UNLOAD/LOADs. Valid values: 10 to 99999 IOs_per_second Default: 30 IOs_per_second ALI_SYS_LOAD Sets the system usage level for the machine on which tuning will occur. If system usage will be heavy during database tuning, this value can be increased. If the system load will be light, this value can be decreased. Valid values: 1.0 to 3.0 where 1.0 = Low usage and 3.0 = High usage Default: 2.0 = Medium system usage ALI_IMPORT_DEBUG_FLAG Used for debugging views, constraints, and triggers as they are read into DBTune. Debug files are named with a ".*DEBUG" extension and are placed in the directory assigned to the SQL_DIR parameter. Valid values: "N", "Y" Default: "N" ALI_STOR_BUFFERS Number by which local buffers are incremented per storage area. Default: 2 ALI_IDX_BUFFERS Number by which local buffers areincremented per write-biased sorted index. Default: 3 ALI_COMPUTED_BY_FLAG If you wish to tune a databaseusing RMU UNLOAD/LOADs and some of the database tables contain 'COMPUTED BY' columns, the RUM/UNLOAD commands and the RMU/LOAD commands must be altered. Assigning a value of "Y" to this logical prior to running DBTune will cause DBTune to generate the necessary changes. Valid values: "N", "Y" Default: "N" ALI_VARCHAR_COMPRESSION Used to compress large "VARCHAR" fields when tuning. All VARCHAR fields greater than or equal to ALI_COMPRESSION_CUTOFF bytes will be compressed by the value entered for this logical in DBTune's calculations. Valid values: 1% to 100% Default: 33% ALI_CHAR_COMPRESSION Used to compress large "CHAR" fields when tuning. All CHAR fields greater than or equal to ALI_COMPRESSION_CUTOFF bytes will be compressed by the value entered for this logical in DBTune's calculations. Valid values: 1% to 100% Default: 50% ALI_COMPRESSION_CUTOFF Used to set the minimum size (in bytes) of a "CHAR" or "VARCHAR" field for which ALI_CHAR_COMPRESSION and ALI_VARCHAR_COMPRESSION will apply. For example, if this logical is assigned the value of 1024, no "CHAR" or "VARCHAR" fields with a length of less than 1024 will be affected by the compression values above. Valid values: 100 to 65269 bytes Default: 256 bytes ALI_BYPASS_FLAG If this logical is defined PRIOR to running DBTune, you can override certain features or error checking generated by DBTune in its SQL and DCL scripts. Following are the values that can be assigned to the ALI_BYPASS_FLAG logical, they can be assigned either singly ("D" or "L") or combined ("DLR", "A42", etc): "D" - Causes the MAIN_DRIVER procedure to skip disk space checks for the RMU/BACKUP and EXPORT files. Following is a scenario where this might be utilized: If you are using a volume set made up of multiple disks, the disk checks in MAIN_DRIVER will only detect the space on the first disk of the volume set. If this is not ample for the backup or export files, MAIN_DRIVER will exit even though the volume set may have more than enough space. By setting the BYPASS logical to "D", you will cause this space check to be skipped. "L" - Causes the MAIN_DRIVER procedure to skip disk space checks for the SQL LOG file. During execution of the MAIN_DRIVER, the device on which the SQL LOG file is placed is checked for adequate disk space. If there is insufficient space, the MAIN_DRIVER will halt. By setting the BYPASS logical to "L", you will cause this space check to be skipped, for reasons similar to those expressed for the "D" setting above. "R" - Causes the MAIN_DRIVER procedure to use an unparsed version of the Rdb directory in generated SQL scripts, when no DBDISK entries have been added via the DBTune parameter file. As of V3.0 of DBTune, all logicals and directories are parsed to their raw device names to avoid confusion between PROCESS and SYSTEM logicals. However, if you are using CONCEALED device names and wish to create ALL the database files on the same concealed device, you can bypass the logical parsing performed by DBTune. The location that will be used to place database files in this case will be assigned to the logical ALI_RDB_ORIG and will be displayed by DBTune. "A" - Causes the MAIN_DRIVER procedure to skip the RMU/BACKUP of the AIJ (After-Image Journal). If tuning an Rdb V6.0 or higher database with multiple AIJs using RMU UNLOADS/LOADS, sometimes a backup of the AIJ is required to re-enable AIJs after tuning. If the user wishes to skip this AIJ backup, the BYPASS logical can be set to "A." Be warned, however, that this may cause the post-tuning SQL scripts to fail. "42" - If the version of Rdb cannot be determined and you wish for DBTune to assume that the database is an Rdb 4.2 database, set the BYPASS logical to "42". Otherwise, DBTune will use the correct Rdb version setting or will assume that it is working with an Rdb 6.0 or higher database. Do not set this value without first consulting with Customer Support. The following logicals can be set before DBTune is run to affect selection ranges for a cardinality distribution in DBTune's report output. “S” - Causes DBDISK available (free) space to be set to essentially “unlimited” ( ie 999,999,998 blocks) per disk, unless a specific number of blocks to be used is specified in the params, pad, or modpad file. If a specific number of blocks is specified, then that number will still be used. ALI_CARD_1 Upper range #1 for a cardinality distribution of tables and indices. Default: 99 ALI_CARD_2 Upper range #2 for a cardinality distribution of tables and indices. Default: 249 ALI_CARD_3 Upper range #3 for a cardinality distribution of tables and indices. Default: 499 ALI_CARD_4 Upper range #4 for a cardinality distribution of tables and indices. Default: 999 ALI_CARD_5 Upper range #5 for a cardinality distribution of tables and indices. Default: 2499 ALI_CARD_6 Upper range #6 for a cardinality distribution of tables and indices. Default: 4999 ALI_CARD_7 Upper range #7 for a cardinality distribution of tables and indices. Default: 9999 ALI_CARD_8 Upper range #8 for a cardinality distribution of tables and indices. Default: 49999 ALI_CARD_9 Upper range #9 for a cardinality distribution of tables and indices. Default: 99999 ALI_CARD_10 Upper range #10 for a cardinality distribution of tables and indices. Default: 999999 Note All tables and indices with a cardinality greater than ALI_CARD_10 are reported in a final range. Multischema Databases Not Supported DBTune does not currently support Rdb databases with the MULTISCHEMA attribute turned on. Worm Areas Not Supported DBTune V6 does not currently support storage areas with the WRITE ONCE (WORM) qualifier. Snapshot Files May Extend During RMU/LOADS When tuning a database using RMU UNLOAD/LOADs, snapshots may become extended. Snapshot files are initially allocated by DBTune based on the size of the .RDA files and parameter entries you make. When an RMU/LOAD is performed after tuning, however, the snapshot files may grow larger than their initial allocation. This is a common occurrence due to the way in which snapshot files work. To reset the snapshot files, simply create an SQL script for the database that respecifies the original allocation for each storage area. For example: SQL>> ALTER DATABASE FILENAME database_name cont>> ALTER STORAGE AREA area_name_1 cont>> SNAPSHOT ALLOCATION IS nnn PAGES cont>> ALTER STORAGE AREA area_name_2 cont>> SNAPSHOT ALLOCATION IS nnn PAGES cont>> etc... ; Rdb License If using RMU UNLOAD/LOADs to tune a database, you must have an interactive or development license for Rdb/VMS. If you only have a RUNTIME license for Rdb/VMS, you can only tune your database via SQL Export/Import. 512 File Limit for Rdb Some older versions of Rdb/VMS (V4.1, V4.2) have an internal file limit of 512 files that can be opened simultaneously. This limitation was removed for higher versions of Rdb (V4.2a and higher). However, if you have an older version of Rdb, you will want to set the SA_MIN_CARD parameter so that the resulting tuned database has less than 250 storage areas (each storage area has 2 files). Otherwise, SQL scripts may fail.