Examples of the dbfhadmin Command Line Utility

Note: On UNIX platforms, the command is dbfhadmin32 or dbfhadmin64, depending on your working mode.

Database creation

Use the following commands to create databases from a script file. Running these commands ahead of time ensures processing is not delayed when access is required and the databases are yet to be created.

The following command creates an ANSI-encoded MSSQL datastore database by running the mydb.sql script file:
```
dbfhadmin -createdb -file:mydb.sql -type:datastore -provider:ss
```
The following command creates an EBCDIC-encoded Db2 datastore database by running the mydb.sql script file:
```
dbfhadmin -createdb -file:mydb.sql -type:datastore -provider:db2 -ebcdic
```
The following command creates a PostgreSQL datastore database, on the MYREMOTEHOST server/port, by running the mypgdb.sql script file:
```
dbfhadmin -createdb -file:mypgdb.sql -type:datastore -provider:pg -host:MYREMOTEHOST -port:33445
```

Data files

Use the following commands to view data files still open after an abnormal termination of an enterprise server, and to recover/release them. Also, there are a number of commands to administer the column types of indexed keys.

The following command lists the data files still open for the VSAM datastore hosted on svr-mydb. For each file, there could be associated outstanding record locks:
```
dbfhadmin -openfiles -list -datastore:sql://svr-mydb/VSAM -host:svr-01
```
The command will return a table, similar to that below, containing the status, host name, file and folder name, and process ID:
```
Status    Host        Name         Folder   PID
------    ----        ----         ------   ---
Inactive  svr-mydb    catalog.dat  /ESDEMO  27180
Inactive  svr-mydb    SPLDSN.dat   /ESDEMO  27180
Inactive  svr-mydb    SPLJOB.dat   /ESDEMO  34132
Inactive  svr-mydb    SPLMSG.dat   /ESDEMO  27180
In doubt  svr-brs     catalog.dat  /ESDEMO  20300
In doubt  svr-brs     SPLDSN.dat   /ESDEMO  20300
In doubt  svr-brs     SPLJOB.dat   /ESDEMO  20300
In doubt  svr-brs     SPLMSG.dat   /ESDEMO  20300
Ok        svr-sdn     catalog.dat  /ESDEMO  34144
Ok        svr-sdn     SPLDSN.dat   /ESDEMO  34144
```
Warning: Caution should be exercised with the following two commands as files are forced closed. You should ensure that the process(es) on the associated host are no longer running before deciding to close files associated with them - refer to the Status column displayed as the result of the previous command:
- OK: the file is open within an active process.
- Inactive: the file had been opened by a process on the same machine as the one running dbfhadmin, but is no longer running.
- In doubt: the file has been opened by a process on a remote machine. You should check these manually before deciding on a course of action.
The following command recovers the data files with an Inactive or In doubt status, by closing them and then removing any associated record locks for all processes, for the VSAM datastore hosted on svr-mydb:
```
dbfhadmin -openfiles -recover -datastore:sql://svr-mydb/VSAM -host:svr-01
```
Files with an Ok status are left untouched - to also close files with an Ok status, include the -force option.
The following command is similar to that above but removes any associated record locks for the specified process (27180), for the VSAM datastore hosted on svr-mydb:
```
dbfhadmin -openfiles -recover -datastore:sql://svr-mydb/VSAM -host:svr-01 -pid 27180
```
The following command specifies that the column type for the primary key of the MYFILE.dat file is alphanumeric, and the column type for the first alternate key is signed COMP-X:
```
dbfhadmin -keytypes -add -file:sql://svr-mydb/VSAM/MYFILE.dat -types:AN,SCOMPX
```

The following command replaces existing key types for the specified data file:

dbfhadmin -keytypes -add -replace -file:sql://svr-mydb/VSAM/MYFILE.dat -types:AN,SCOMPX

The following command deletes the key types that have been specified for MYFILE.dat, and the column types for indexed keys revert to type BINARY:
```
dbfhadmin -keytypes -delete -file:sql://svr-mydb/VSAM/MYFILE.dat
```
The following command exports the list of key types for the VSAM datastore to the MYKEYTYPES.xml file:
```
dbfhadmin -keytypes -export:MYKEYTYPES.xml -datastore:sql://svr-mydb/VSAM
```
The following command imports a list of key types to the VSAM datastore from the MYKEYTYPES.xml file:
```
dbfhadmin -keytypes -import:MYKEYTYPES.xml -datastore:sql://svr-mydb/VSAM
```
The following command imports a list of key types to the VSAM datastore from the MYKEYTYPES.xml file, replacing any existing types that correspond to the ones in the file:
```
dbfhadmin -keytypes -import:MYKEYTYPES.xml -datastore:sql://svr-mydb/VSAM -replace
```
The following command lists the key types set for each data file in the datastore:
```
dbfhadmin -keytypes -list -datastore:sql://svr-mydb/VSAM
```

Script file creation

Use the following commands to create script files that can be used to create databases.

The following command creates a script file (createdb.sql) that can be run to create a PostgreSQL datastore called MYFILES:
```
dbfhadmin -script -type:datastore -name:MYFILES -provider:pg -file:createdb.sql
```
The following command creates a script file (createinmydb.sql) that can be run to create a Db2 datastore called mySEQ, which will be stored in the existing Db2 database called masterSEQDB:
```
dbfhadmin -script -type:datastore -name:mySEQ -provider:db2 -file:createinmydb.sql -existdb:masterSEQDB
```
The following command creates a script file (create_datastore_MYFILES_ora.sql) that can be run to create an Oracle datastore called MYFILES, which will be stored in an existing Oracle database - the existing database is specified (using the -existdb option) when running the -createdb action on the script file once it is created:
```
dbfhadmin -script -type:datastore -name:MYFILES -provider:ora
```

Upgrading datastores

Use the following command to generate the script file upgradeds.sql, required to manually upgrade the VSAM datastore. If you omit the -file option, a file named upgrade_datastore_VSAM_db-type.sql is generated (db-type is determined by the settings in the database configuration file).
```
dbfhadmin -upgrade -datastore:sql://localhost/VSAM -file:upgradeds.sql
```
If the datastore does not need to be upgraded, dbfhadmin outputs ADM0073I Datastore '<datastore-url>' does not need to be upgraded, and no script file is created. When an upgrade is required, dbfhadmin outputs ADM0045I Creating SQL script file '<sql-script-name>'.

The resulting script should be run as a user with sysadmin privileges using the sqlcmd (SQL Server), psql (PostgreSQL), db2 (Db2), or sqlplus (Oracle) command-line tool.

Datastore integrity

Use the following commands to verify the integrity of a datastore, and if required, recover it from any integrity issues.

The following command runs a datastore integrity check of the VSAM datastore:
```
dbfhadmin -verify -list -datastore:sql://localhost/VSAM
```
Currently this check only detects orphaned record locks (that is, those record locks that exist without an associated file handle). Record locks would typically be orphaned if the associated file handle had been manually deleted from the database. When orphaned record locks are detected, dbfhadmin lists the associated file name for non-sequential files, or lists <Sequential files> for sequential files (the actual file name cannot be determined as a global record lock table is used for these files), with a count of the orphaned locks; for example:
```
Orphaned record locks:  Name                Folder  Count
                        ----                ------   -----
                        SPLJOB.dat          /MyDir   3
                        <Sequential files>           1
```
The following command recovers the VSAM datastore from any orphaned record locks:
```
dbfhadmin -verify -recover -datastore:sql://localhost/VSAM
```
The following command generates a diagnostic report names diags.txt, which contains details about ESDEMO region database (and associated cross-region database) in the DBHOST database:
```
dbfhadmin -diags -usedb:DBHOST -name:ESDEMO -file diags.txt
```
The following command dumps lock information for the specified datastore to an MFDBFH_Diagnostics.txt file:
```
dbfhadmin -diags -usedb:DBHOST -name:ESDEMO -datastore:sql://localhost/VSAM
```

See dbfhadmin Return Codes for a list of possible return codes used by the command: