Update the Configuration File for Microsoft SQL Server Datastores

You should only create or update a configuration file using the dbfhconfig command line utility.

  1. Open an Enterprise Developer command prompt.
    Note: On Windows, the command prompt must be the same bitism as the ODBC data source that you intend to use.
  2. Set the MFDBFH_CONFIG environment variable to locate the configuration file to be created or updated:
    set MFDBFH_CONFIG=<location-and-name-of-file>

    If you do not set this variable, a file named MFDBFH.cfg is assumed to be located in the current directory.

    Note: The MFDBFH_SCRIPT_DIR environment variable is also required to point to the stored procedures used to interact with a datastore; however, this variable is implicitly set already when using an Enterprise Developer command prompt.
  3. Add a database server instance that you intend to connect to:
    dbfhconfig -add -server:<server-name> -provider:ss

    where <server-name> is the name of the database server instance. An entry of (local) or localhost is also valid, which defaults to your machine name.

    Next you need to specify the databases within the instance.

  4. Firstly, you need to create a reference to the master database - this is the primary configuration database supplied with MSSQL:
    Note: If the <server> element for your database server instance already exists, this reference should already exist.
    dbfhconfig -add -server:<server-name> -dsn:<dsn-name> -type:database -name:master [-connect:<connection-string>]

    where <dsn-name> is the ODBC data source for the MSSQL master database, or if you are using a connection string (instead of an ODBC data source), it is a unique name within the configuration in which to reference the database; and <connection-string> is the database connection string if you are establishing a database connection without the use of an ODBC data source - see Database Connection Strings for the database-specific syntax.

  5. Repeat the following step for each required datastore:
    dbfhconfig -add -server:<server-name> -dsn:<dsn-name> -type:datastore -optio:<opts> -name:<dsname> [-connect:<connection-string>]

    where <dsn-name> is the ODBC data source for the datastore, or if you are using a connection string (instead of an ODBC data source), it is a unique name within the configuration in which to reference the datastore; <opts> are the optimizations to be applied to I/O operations; and <dsname> is the actual name of the datastore (as configured in the data source properties).

    <opts> can be one of:
    option description
    all All optimizations applied, as if +<opt> had been specified for each available option. You can then selectively disable certain options using -<opt>.
    none No optimizations applied.
    {+|-}oiseq Enables (+) or disables (-) optimizations for line-sequential files opened for input.
    {+|-}ooseq Enables (+) or disables (-) optimizations for line-sequential files opened for output.
    {+|-}oi Enables (+) or disables (-) optimizations for ESDS, KSDS, and RRDS files opened for input.
    {+|-}oo Enables (+) or disables (-) optimizations for ESDS, KSDS, and RRDS files opened for output.

Example configuration file

The configuration file is written to after each command. The following is an example of a configuration file that contains two datastores, a region database and a cross-region database. The MSSQL server instance is also configured for Windows authentication, hence the omission of the userid and password attributes.

<datastores usevault="false">
   <server name="(local)" type="sqlserver" access="odbc">
      <dsn name="SS.MYMASTER" type="database" dbname="master"/>
      <dsn name="SS.VSAM" type="datastore" dsname="VSAM"/>
      <dsn name="SS.SEQ" type="datastore" dsname="SEQ" optio="all -ooseq"/>
      <dsn name="SS.CAS.ESDEMO" type="region.cas" region="ESDEMO" feature="all"/>
      <dsn name="SS.CROSSREGION" type="crossregion.cas"/>
   </server>
</datastores>

Next, you must create the datastores using the dbfhadmin command line utility; after which, you can use the dbfhdeploy command line utility to add files to them.