Conference ulysse::rdb_vms_competition

1047.0. "VPG for RDBexpert Volume PDL Generator" by JENEVR::RLEE () Fri Dec 20 1991 06:36

    Found the following while doing a deep browse of the VTX TOOLSHED:
    
(The information in this file is for INTERNAL USE ONLY.)         JUN-91

TOOL NAME:  VPG

FUNCTION:   Extended RDBexpert Volume PDL Generator 
            

VERSION:    1.0 
SYSTEM:     VMS 
LANGUAGE:   C,  
            SQLMOD 
LOCATION:   
            METOO""::TOOLS$DIRECTORY:[VPG]vpg010.bck 
            
MORE INFO:  
            README.TXT (available in saveset) 
            
SOURCE:     
            @GNPIKE::DTU 
            

                               Page 1 of 4                       More --> 

GENERAL DESCRIPTION:

VPG is a utility that generates RDBexpert Volume PDL for an RDB 
database.   The more accurate the volume information is that you provide 
to RDBexpert, the better the output design that RDBexpert will 
generate.     

The Volume PDL generator provided with RDBexpert does not recommend 
scanning the whole table when the tables are large.  VPG works around this 
problem by the way its queries against the database are performed.  
Providing individual  column cardinalities helps RDBexpert to make the 
right decisions during index definition.  If only Table cardinalities are 
used, RDBexpert might put a low or zero cardinality column in the first 
segment of a BTREE index, or fail to append a high cardinality segment to 
a low cardinality required BTREE index. Supplying complete column 
cardinalities will produce the best index designs. 


ADVANTAGES:   

 <-- More                      Page 2 of 4                       More --> 
              VPG is intended to complement the existing Volume PDL 
              generator provided by RDBexpert.  RDBexpert has an option to 
              generate volumes based on table  cardinalities and an option 
              to scan all tables/columns when the tables are small.   
              
              VPG provides an option to scan columns in the tables that 
              are currently part of an index definition (any segment).  It 
              also provides a scan table/column option that works for 
              large tables. 
              
              We have used VPG to automate the process of manually 
              updating the relevant  column cardinalities to improve index 
              design. 
              
LIMITATIONS:  
              While it is not required, we recommend running VPG when 
              there are no other users updating the database.   
              

OTHER:

 <-- More                      Page 3 of 4                       More --> 
See also ... 




















                    .KIT file follows on the next page
$!	VMS Installation kit for VPG
$ VPG_VER= F$VERIFY(0)
$ IF F$EXTRACT(0,2,F$GETSYI("VERSION")) .EQS. "V3" THEN GOTO install
$ WRITE SYS$OUTPUT "Installing VPG"
$ GOTO 'F$MODE()
$ INTERACTIVE:
$ INQUIRE ans "Do you wish to install in batch mode? [Y]"
$ IF ans .EQS. "" THEN ans = "Y"
$ IF .NOT. ans THEN GOTO install
$ default := "''F$LOGICAL("SYS$DISK")'''F$DIRECTORY()'"
$ SUBMIT/notify/noprint/par='default' 'F$ENVIRONMENT("PROCEDURE")
$ GOTO exit
$ BATCH:
$ set nover
$ set default 'p1'
$ install:
$ COPY/LOG METOO""::TOOLS$DIRECTORY:[VPG]vpg010.bck *.* ! 
$ ASSIGN /USER NL: SYS$OUTPUT:
$ DIR SYS$SHARE:NM_MAILSHR.EXE
$ NMAIL_STATUS = $STATUS
$ IF NMAIL_STATUS THEN DEFINE STC "NM%METOO::STC"
$ IF .NOT. NMAIL_STATUS THEN DEFINE STC "METOO::STC"
$ IF NMAIL_STATUS THEN DEFINE STC_VPG_SOURCE NM%GNPIKE::DTU
$ IF .NOT. NMAIL_STATUS THEN DEFINE STC_VPG_SOURCE GNPIKE::DTU
$ ASSIGN /USER NL: SYS$OUTPUT:
$ MAIL /SUBJ="VPG installed ''f$log("SYS$DISK")'''f$dir()'" -
	NL: STC 
$ ASSIGN /USER NL: SYS$OUTPUT:
$ MAIL /SUBJ="VPG installed ''f$log("SYS$DISK")'''f$dir()'" -
	NL: STC_VPG_SOURCE
$! ASSIGN /USER NL: SYS$OUTPUT:
$! MAIL /SUBJ="Has the tool VPG been installed on this system before?"  -
!	NL: SYSTEM
$exit:
$ VPG_VER=F$VER(VPG_VER)


This capability is in the RMU/EXTRACT facility in V4.1 of Rdb/VMS.

    Thanks, Jay!