Using the database management utilities
The Database Migrate, Repair, and Delete utilities are intended to make it easier to manage databases. The utilities are particularly useful if you have encountered duplicate workload names in your databases. The new schema uses new, longer workload names.
The Utilities, installed when you install the product are executables to which you pass parameters such as the Data Source Name (DSN). The following executables are installed in the Visualizer product directory:
- dbmigrate.exe
- dbrepair.exe
- delnode.exe
If you plan to use both the Migrate and Repair utilities, we recommend running the Database Migrate Utility before you run the Database Repair utility.
Running the Database Migrate utility
The Database Migrate Utility makes it easier to migrate from one release of a description file to the next. The Utility reads the new description file, compares the tables in the existing database with the new description file, and brings the state of the existing tables up to that of the description file. You can run the Database Migrate Utility only against ODBC databases.
- From the DOS prompt, change directory to the current Visualizer directory.
- Run dbmigrate.exe to display the optional and required input parameters as shown in the following sample:
- Specify input parameters to the executable.
- For a list of applicable input parameters, see Running the Database Repair utility.
View the output log file to see what changes were made to the database. The following screen shows a sample MigrateLog.log file. The log file shows what has been done to the existing database to make it consistent with the new description files.
Starting dbmigrate ...Thu Jul 23 17:01:27 2020
Starting trace file...
c:\temp\dbmigrate.trace
Reading description files from C:\Program Files (x86)\BMC Software\CDB\CDBServices\bin\ Platform: MVS Meas/Smry
Migrating platform...
Updates required
... for table CPCPLFS
ALTER TABLE CPCPLFS ADD COLUMN "SysplexName" Text(8)
... for table CPCPLFDS
ALTER TABLE CPCPLFDS ADD COLUMN "System_type" Text(4)
ALTER TABLE CPCPLFDS ADD COLUMN "Modelled_sys" Text(1)
UPDATE CPCPLFDS SET Modelled_sys = 'N'
ALTER TABLE CPCPLFDS ADD COLUMN "MIPS_rate" Number
UPDATE CPCPLFDS SET MIPS_rate = 13.6999998093
ALTER TABLE CPCPLFDS ADD COLUMN "SysplexName" Text(8)
... for table CPCFSTRS
ALTER TABLE CPCFSTRS ADD COLUMN "SysplexName" Text(8)
ALTER TABLE CPCFSTRS ADD COLUMN "CFName_alias" Text(8)
UPDATE CPCFSTRS SET CFName_alias = LEFT(CF_Name,8)
Completed MVS Meas/Smry migration
End dbmigrate. Thu Jul 23 17:01:28 2020
Platform: MVS Meas/Smry
Migrating platform...
Updates required
... for table CPCPLFS
1 field(s) updated, 0 altered and 1 added
... for table CPCPLFDS
4 field(s) updated, 0 altered and 4 added
... for table CPCFSTRS
2 field(s) updated, 0 altered and 2 added
Completed MVS Meas/Smry migration
End dbmigrate. Thu Jul 23 17:08:38 2020
Running the Database Repair utility
The Repair utility expedites repairing existing databases. Use the Database Repair utility to:
- Delete un-referenced indexes in static tables and un-referenced references in dynamic tables.
- Delete dynamic duplicate records in databases with bulk loaders.
- Delete duplicate dynamic entries and dynamic entries with invalid static references
- Rebuild stored procedures and database index file in SQL Server, Oracle, or bulk databases.
- From the DOS prompt, change directory to the current Visualizer directory.
- Run dbrepair.exe to display the acceptable optional and required input parameters as shown in the following sample:
Specify input parameters to the executable. The following table describes the values to pass to the Database Repair and Migrate utilities:
Utility
Value
Required
Optional
Description
Repair
Migrate
-d: string
Yes
Specify the DSN name for the ODBC driver
Repair
Migrate
-u: string
May be required by database
Specify a username if required by the database
Repair
Migrate
-p: string
May be required by database
Specify a password if required by the database
Repair
Migrate
-f: string
Yes
Specify short platform name, like CPMVS or CPAIX. If you omit this value, the Utility repairs all platforms in the current database
Repair
Migrate
-l: string
Yes
Specify full log file name. If you omit this value, the Utility generates one by using the application name in %temp% directory and appending it with repeated runs.
Repair
Migrate
-v: preview mode with report
Yes
Creates a log file listing the actions that would be taken on your database if you were actually executing the utility, such deleting un-referenced and duplicate information. The log points to an additional file named log-file-name.short-platform -name.vis that lists the duplicate records will be retained.
Because preview mode is as detailed as a run, it can take as long to execute as actually running the utility.
Repair
Migrate
-x: string
Yes
Specify the directory containing the description files. If you omit this value, the utility assumes you want to use the same directory as the utilities
The following values refer to specific repair functions. The Database Repair utility performs all functions if none are specified.
Repair
-r
Yes
Repairs references
Repair
-m
Yes
Removes dynamic duplicate entries
Repair
-i
Yes
Rebuilds stored procedures and indexes
View the output log file to see what repairs were made to the database. The following is a sample RepairLog.log file, showing what has been done to repair the database:
Starting dbrepair...Thu Oct 03 11:58:48 2020 Starting trace file...
C:\Temp\dbrepair.trace
Reading description files from D:\Program Files\BMC Software\Visualizer\ Repair operations:
-Reference repair
-Duplicate removal
-Index/stored procedure repair
Platform: VM Meas/Smry Processing Reference repair...
Dynamic tables for invalid references... Static tables for unreferenced objects...
...table CVLSYSS unreferenced records: 1
...table CVDEVS unreferenced records: 54
...table CVWKLDS unreferenced records: 6
...table CVCHANS unreferenced records: 8 Processing Duplicate removal...
Bulk utility is in effect
Population of a table CVWKLDD at record 40 finished at 11:58:51 Population of a table CVDTOPD at record 121 finished at 11:58:51 Population of a table CVWTOPD at record 177 finished at 11:58:51 Processing stored procedure and index rebuild...
Bulk utility command line is east.east.CVWKLDD in C:\Temp\east_sql.257\VIS299D.tmp\CVWKLDD.OUT
-eC:\Temp\east_sql.257\VIS299D.tmp\CVWKLDD.ERR -m 666 -b 1000 -c
-oC:\Temp\east_sql.257\VIS299D.tmp\CVWKLDD.log -t, -Ueast -Peast -SVISSQL. Deleting duplicates(if any) started at 11:58:52
Deleting duplicates(if any) finished at 11:58:52
Bulk utility command line is east.east.CVWKLDD in C:\Temp\east_sql.257\VIS299D.tmp\CVWKLDD.OUT
-eC:\Temp\east_sql.257\VIS299D.tmp\CVWKLDD.ERR -m 666 -b 1000 -c
-oC:\Temp\east_sql.257\VIS299D.tmp\CVWKLDD.log -t, -Ueast -Peast -SVISSQL. Population of 36 rows into CVWKLDD table via bulk utility started at 11:58:52. Population of table CVWKLDD via bulk utility finished at 11:58:52.
Bulk utility command line is east.east.CVDTOPD in C:\Temp\east_sql.257\VIS299D.tmp\CVDTOPD.OUT
-eC:\Temp\east_sql.257\VIS299D.tmp\CVDTOPD.ERR -m 666 -b 1000 -c
-oC:\Temp\east_sql.257\VIS299D.tmp\CVDTOPD.log -t, -Ueast -Peast -SVISSQL. Deleting duplicates(if any) started at 11:58:53
Deleting duplicates(if any) finished at 11:58:54
Bulk utility command line is east.east.CVDTOPD in C:\Temp\east_sql.257\VIS299D.tmp\CVDTOPD.OUT
-eC:\Temp\east_sql.257\VIS299D.tmp\CVDTOPD.ERR -m 666 -b 1000 -c
-oC:\Temp\east_sql.257\VIS299D.tmp\CVDTOPD.log -t, -Ueast -Peast -SVISSQL. Population of 80 rows into CVDTOPD table via bulk utility started at 11:58:54. Population of table CVDTOPD via bulk utility finished at 11:58:54.
Bulk utility command line is east.east.CVWTOPD in C:\Temp\east_sql.257\VIS299D.tmp\CVWTOPD.OUT
-eC:\Temp\east_sql.257\VIS299D.tmp\CVWTOPD.ERR -m 666 -b 1000 -c
-oC:\Temp\east_sql.257\VIS299D.tmp\CVWTOPD.log -t, -Ueast -Peast -SVISSQL. Deleting duplicates(if any) started at 11:58:56
Deleting duplicates(if any) finished at 11:58:56
Bulk utility command line is east.east.CVWTOPD in C:\Temp\east_sql.257\VIS299D.tmp\CVWTOPD.OUT
-eC:\Temp\east_sql.257\VIS299D.tmp\CVWTOPD.ERR -m 666 -b 1000 -c
-oC:\Temp\east_sql.257\VIS299D.tmp\CVWTOPD.log -t, -Ueast -Peast -SVISSQL. Population of 56 rows into CVWTOPD table via bulk utility started at 11:58:56. Population of table CVWTOPD via bulk utility finished at 11:58:57.
Validation of execution of bulk utility for table CVWKLDD started at 11:58:57 Validation of execution of bulk utility for table CVWKLDD finished at 11:58:57 Validation of execution of bulk utility for table CVDTOPD started at 11:58:57 Validation of execution of bulk utility for table CVDTOPD finished at 11:58:57 Validation of execution of bulk utility for table CVWTOPD started at 11:58:57 Validation of execution of bulk utility for table CVWTOPD finished at 11:58:57 Completed process
End dbrepair. Thu Oct 03 11:58:57 2020
Running the Delete utility
The Delete utility deletes data to the collected object level. The utility requires the collected object name or name of a file containing collected object names. The definition of object varies by platform.
In contrast, you can use the Automator Delete Data event to remove various classes of Measurement and Summary data (System, Workload) for a specified time frame.
You can use the Automator Delete DataSource event to remove all data for one or more specific platforms (such as Distributed Systems Measurements) from the specified database.
Neither the Delete Data nor the Delete Data Source event is node-specific.
The following table lists the object granularity of deletion by platform that you can perform with this utility:
Platform | Granularity |
|---|---|
Distributed Systems | Node |
Oracle, Sybase, Informix, SQL Server | Node |
z/OS, MVS | Logical System |
CICS, IMS, Db2 subsystems | Logical System |
VM | Logical System |
iSeries (formerly AS /400) | System Serial number |
For example, when the Delete Utility is used against a database containing Distributed Systems data, it deletes the definition of the specified node or nodes, as well as all associated measurement and/or summary data. By default, the Delete utility performs an effective Database Repair ( dbrepair) run after removing the data.
The Delete utility operates from the Microsoft Windows DOS command line like the Migrate and Repair Utilities. The name of the executable is delnode.exe, located in the Visualizer directory.
The basic steps for using delnode are as follows (Foe more information, see Use case scenario: Creating a node list.):
- Backup your current database.
- Use the Delete Utility in query mode ( -n ?) to list the objects in the database.
- Use the query results to build a list of objects you want to delete.
Run the Delete Utility in Preview mode to confirm the requested deletion.
Run the Delete Utility to remove the listed objects.
This is the general format of a delnode command line. The flags and arguments are described in detail in the following table:
delnode -d DSN [ -u user_id ] [ -p password ] -n ( ? | object | file listing objects ) [ -f platform ] [ -l logfile ] [-v] [-i] [-s] [-t]Flag
Argument
Required
Description
-d
ODBC DSN name
yes
The ODBC or OCI DSN used to access the database from the current node.
-u
User id for DSN
no
Must be used unless user id and password are optional for the specified DSN.
-p
Password for DSN
no
Must be used unless user id and password are optional for the specified DSN.
-f
Visualizer platform
no
If omitted, the utility processes all supported platforms in the database - to prevent undesired deletions, examine preview results carefully.
If you want to restrict the utility's operations to just one platform, such as Distributed Systems, then you must provide the short name for Distributed Systems on the command line. (See the for the list of correspondences.)
-l
(none), filename, or file name with path
no
If you do not specify a log file, delnode.log is created in %TEMP%. If you specify just a filename, it is created in the local directory. If you specify a full path, it is created as specified. Use this argument for previewing or logging the Delete utility's actions. Refer to the -v flag.
-x
Path to description files
no
The Utility is typically run in the Visualizer directory, where platform description files ( *.txt) are located. If you want to run it elsewhere, specify a path similar to:
-x "c: \BMC Software \ Visualizer \ \"
Note use of quotes and slashes.
-v
(none)
no
This argument lets you preview the Utility's actions - results are written to the log file and, optionally, the trace file. There is no impact on the database until the -v flag is removed from the command line.
-t
(none)
no
Creates a detailed trace of database interaction, primarily for debugging purposes. If delnode_run.log is specified as the log file, delnode_run.trace is created as a trace file.
-n
Object or file listing objects
yes
There are the following options:
- ? to query the database for an object list
- single object to delete
- the name of the file containing list of objects to delete
You can use the -n flag with the single object you wish to delete. If specifying a long or instance name, you must to enclose it in double quotes. It is best to copy the value out of the query results. This is an example of the syntax:
-n "cmapprd30 AC868tOlfGz8n"
-s
(none)
no
Specifies that the database is case sensitive in its queries.
-i
(none)
no
Inhibits the normal Database Repair Utility run to remove measurement and summary data. This lets you defer repair until the last of several delnode runs, if desired.
Platform name lookup table
Use the following table to find long platform names and their corresponding short names:
Description | Platform Name |
|---|---|
AS/400 measurements | CPAS400 |
AS/400 predictions | B1AS400 |
DistSys measurements | CPAIX |
DistSys predictions | B1AIX |
Informix measurements | CPINFX |
MS SQL server measurements | CPMSSQL |
MVS/VM predictions | B1DC |
MVS measurements | CPMVS |
Oracle measurements | CPORCL |
Use case scenario: Creating a node list to submit a delnode
Before you can run the Delete Utility, you must determine which node(s) or list of nodes to specify on the command line. The following use case takes you through the process of building an accurate list - querying the entire database, identifying the objects you want to delete, and creating a file that contains only the objects you want to delete in the correct format.
The procedure assumes you have opened a DOS command prompt window, and navigated to the Visualizer directory.
- Obtain a full backup of the database(s) you want to modify. If necessary, work with your DBA on this task.
Run the Delete Utility to obtain a listing of objects in the database you want to modify. The following syntax uses -n with a question mark ( ?) to query the database for a list of nodes.
delnode -d my_dsn -u its_me -p really -f CPAIX -n ? -l first.txt
d = the data source name
u = the user id
p = password
f = platform
n = object
l = log file nameThe results of the query are written to first.txt (in the current directory). The expression -f CPAIX limits the scope of the query to the Distributed Systems
Measurements table. If the scope had not been limited, then first.txt would list all objects in the database subject to deletion.Open first.txt in Notepad to review the results of the query. (Using first.txt as a command line launches Notepad with first.txt loaded.)
Starting delnode for my_dsn ... Thu Mar 25 18:45:04 2020
Reading description files from C:\BMC Software\Visualizer\
Object Names:
?
Platform: DistSys Meas/Smry
Delete from platform ...
No match for ?
brsbnes01 : brsbnes01 d_nr[r)z&h4E
: brsbnes01 MfzOnj[8v0vW[A
brsbnes02 : brsbnes02 d_zr[r)[hGWK[
: brsbnes02 MfzO0jnQQSQvlp[
best1prd : best1prd 8jWhC44CtYUM
capprd : capprd xlhtbnjS00Uvz06
cmapdev01 : cmapdev01 A2x68tOlfGz8n
cmapprd01 : cmapprd01 ACf68tOlfGz8n
cmapprd30 : cmapprd30 AC868tOlfGz8n
No matching names
End delnode. Thu Mar 25 18:45:05 2020- Determine if the database with which you are working is case sensitive. There are two questions to help you determine whether you need to take further action:
Do the query results contain one or more nodes you want to delete appearing with different versions of the short name, identical except for case? Here's an example:
fldp1csced01 : fldp1csced01 hsB2D2V8cyRAE
FLDP1CSCED01 : FLDP1CSCED01 W8GdhClUvEWp6A
FLDP1CSCED01 xTTYn2V8cyRAEIf so, are you trying to delete anything other than the complete node?
If you answered No to both, go to step 5.
If you answered Yes to both, use the following information to help you determine how to proceed.Databases often have a pair of qualities:
- Case preservation - a database is case preserving if it maintains the case attributes of the data being stored.
- Case sensitivity - a database is case sensitive if a query for abc or aBc does not find ABC.
delnode provides a case sensitivity flag, -s. We recommend using the -s flag if your database is case sensitive, and omitting it if the database is case insensitive. If you have a mismatch between the database case sensitivity and the -s flag (used or not), there are a few potential consequences:
- delnode may not delete any data for the selected node.
- It may select and delete the wrong instance for a node.
- It may completely delete a node when only a single long name is specified.
Start creating a list of objects to delete.
The rest of this use case illustrates a short name or complete node deletion. The next step is to trim from the file anything that is not part of the object list. This is a sample of what the edited file looks like:brsbnes01 : brsbnes01 d_nr[r)z&h4E .
: brsbnes01 MfzOnj[8v0vW[A .
brsbnes02 : brsbnes02 d_zr[r)[hGWK[ .
: brsbnes02 MfzO0jnQQSQvlp[ .
best1prd : best1prd 8jWhC44CtYUM .
capprd : capprd xlhtbnjS00Uvz06 .
cmapdev01 : cmapdev01 A2x68tOlfGz8n .
cmapprd01 : cmapprd01 ACf68tOlfGz8n .
cmapprd30 : cmapprd30 AC868tOlfGz8n .- Select File > Save As and save the file as second.txt.
- Edit the file to create a valid list of objects to delete. You have two options:
- You can edit your node list manually. The file listing the nodes to delete must be in a very specific format.
- Each line in the file should contain either a short or long name (not both).
Object names must appear in the file without leading or trailing spaces:
capprd
cmapprd01 ACf68tOlfGz8n
- You can not have blank or extraneous lines.
- If delnode encounters anything it does not recognize, the effect is identical to running it with -n ?.
If you are deleting nodes, rather than node configurations, you can access a utility from BMC's ftp site to create a new file, containing only the short names.
The file is first_word.bat , located in /pub/visualizer/util/delnode at ftp.bmc.com . When you download the file , perform a binary transfer.
Assuming that you have obtained first_word.bat , and it is in your local directory or your PATH, use second.txt as input to create third.txt, containing only the short node names:
first_word second.txt third.txt
The resulting third.txt file looks like:
brsbnes01
brsbnes02
best1prd
capprd
cmapdev01
cmapprd01
cmapprd30
Using Notepad, remove the nodes you want to keep in the database from third.txt. Save the resulting file as fourth.txt. fourth.txt contains only the nodes you want to remove from the database:
brsbnes01
capprd
cmapdev01
- You can edit your node list manually. The file listing the nodes to delete must be in a very specific format.
- Perform a preview run to determine if delnode has produced the desired result.
In the command line, specify fourth.txt as the list of nodes to delete, the -v flag to specify a preview run, and fifth.txt as the log file.
delnode -d my_dsn -u its_me -p really -f CPAIX -n fourth.txt -l fifth.txt -v Review the results in fifth.txt in Notepad. The goal of this step is to confirm that the objects in the delete list you created are recognized. You might also want to know which tables are being updated.
Starting delnode for my_dsn ... Tue Apr 37 28:49:14 2020
In preview mode...
Reading description files from C:\BMC Software\Visualizer\
Object Names:
brsbnes01
capprd
cmapdev01
Platform: DistSys Meas/Smry
Delete from platform ...
<NameList> pseudo-trace
'brsbnes01','capprd','cmapdev01'
Invalidating objects
DELETE FROM CAXNODE WHERE Node_Alias IN (<NameList>)
... from table CAXNODE
DELETE FROM CAXWCATS WHERE Node_Alias IN (<NameList>)
... from table CAXWCATS
DELETE FROM CAXWRESS WHERE Wkl_ID IN (<NameList>)
... from table CAXWRESS
(some content removed)
DELETE FROM CAXNF3SS WHERE Node_name IN (<NameList>)
... from table CAXNF3SS
DELETE FROM CAXRPCCS WHERE Node_name IN (<NameList>)
... from table CAXRPCCS
DELETE FROM CAXRPCSS WHERE Node_name IN (<NameList>)
... from table CAXRPCSS
DELETE FROM CAXNODES WHERE Computer_Name IN (<NameList>)
... from table CAXNODES
End delnode. Tue Apr 37 28:49:17 2020If you are satisfied with the results of the preview run, recall your last command line and edit it, removing the preview flag ( -v) and specifying a different log file.
delnode -d my_dsn -u its_me -p really -f CPAIX -n fourth.txt -l sixth.txtRelated topic