Setting up a repository

To generate a system catalog for a Synergy database, xfODBC must have access to a repository, which is a set of files that define the schema of the database. Repositories are created with Synergy/DE Repository and contain definitions for structures, fields, keys, relations, files, and tags.

You can use the following procedure to set up your repository, or you can enlist the help of Synergex Professional Services to optimize an existing repository for ODBC access or to create a new repository from your data files. For information, contact your Synergex account executive.

The following steps outline the process for creating a repository for xfODBC. (For information on setting up a repository to use multiple databases, see Handling a repository shared by multiple databases.) When you’re finished, your repository should contain the following:

Plan and define your keys when you create your data files. Then, when you create the repository, create an access key for each of the data file keys. For more information on keys, see Optimizing with keys.

1. Gather record layout information

Start by gathering information about the database. You’ll need all the record layout, key, and tag information for the data files. Sources of information include

ANALYZE/RMS/FDL filename

As you gather record layout information,

2. Create a new repository

To create a new repository, open Synergy/DE Repository and select Create New Repository from the Utilities menu. For more information, see Create New Repository utility.

Tip

A single repository can be used to define multiple databases if the files, structures, and fields in the databases are identical. For information, see Handling a repository shared by multiple databases.

3. Create structures

Structures are record definitions or compilations of field and key characteristics for a particular file or files. Create a structure for each record layout in your data files. If you have a file with multiple record layouts, you must create a structure for each. See Working with Structures.

4. Create templates

A template is a set of characteristics that can be applied to multiple fields. Templates simplify maintenance and enable you to maintain consistency. For example, if several fields share some characteristics including Alternate name, you can create a template for the fields. Then, when you change the Alternate name for the template, the Alternate name is automatically updated for all the template’s fields. For more information, see Defining field templates.

Note

If a template specifies an Alternate name, that template can be applied to only one field per structure.

5. Define fields and import field definitions

Use record layout information from step 1 to define fields for each structure. Note the following:

If you specify an Alternate name, it will be used only if SODBC_ODBCNAME is set when the system catalog is generated. For information on the Alternate name field, see Display information. For information on SODBC_ODBCNAME, see Renaming columns for clarity (SODBC_ODBCNAME).

1. Create an implied decimal overlay field that matches the precision of the format string. For example, if you have a d6 field with a format string that specifies the XXXX.XX format, create an overlay field with a d6.2 data type.
2. Use the Repository Alternate name option to specify an Alternate name that’s identical to the original field. For example, if the field is named in_price, set the Alternate name for the overlay field to in_price.
3. Set the Repository Excluded by ReportWriter option for the original field (not the overlay field). This will prevent the original field from becoming part of the system catalog, unless the SODBC_CNVOPT environment variable is set. For information on this variable, see Including and omitting fields.

See Using decimal information in the repository format string (SODBC_USEFORMAT) for more information.

Note

If the field is a key segment, overlaying it with multiple overlay fields will prevent dbcreate from creating an index for the overlay fields. See Create keys for information.

6. Change .INCLUDE statements in code

If you loaded data definitions from an include file into your repository, you will want to change the .INCLUDE statements in your code to include the repository definitions rather than the files.

7. Create tags to describe multiple structures in a file

If some of your structures are related, you may have them grouped in one file. (You’ll assign structures to files in step 10.) If you have more than one structure in a file, create a tag for each structure, a tag that uniquely identifies the structure in the file, and make sure you construct the tags so they can be optimized. See Tags and optimization for information.

For information on how to define a tag, see Defining tags.

8. Create keys

Keys are portions of a record structure that individually identify records and enable records to be quickly accessed and sorted. Your data files may already have keys. However, for xfODBC to use keys, they must be defined in the repository before the system catalog is generated. You can define two types of keys in a repository: access keys, which mirror keys defined in the database, and foreign keys, which are keys defined only in the repository. Foreign keys enable you to define additional relations that can’t be defined with access keys.

Keep in mind that the keys you choose will greatly affect the performance of SQL queries, so choose them carefully, and note the following:

See Optimizing with keys for more information on what keys are, how xfODBC uses keys, and how to define keys for optimal performance.

For information on preventing xfODBC from including keys, see Omitting keys.

9. Define files in Repository

File definitions are the Repository mechanism for storing information (name, file type, etc.) for data files associated with a repository. Create file definitions for all data files that are described by a repository’s structures. See Working with Files.

Note that for file definitions, you must specify the filename in the Open filename field (which is in the Repository’s File Definition window). However, you can specify the location of the data file in either the Open filename field or the datasource line of the connect file. (You cannot specify a filename in the datasource line.) The Open filename field has precedence; xfODBC uses the datasource line only if the Open filename field does not specify a path or environment variable. For example, if a data file for the repository is c:\datafiles\mydata.ism and you enter only mydata.ism in the Open filename field, you must have the following datasource line in the connect file:

datasource ;c:\\datafiles;

Note the following:

For information on the datasource line, see Creating the connect file.

10. Assign structures to files

Once you’ve created file definitions for the data files described by your repository, assign structures to those files. Every structure that you want included as a table in the system catalog must be assigned to a file definition. If a file contains multiple record layouts, assign all corresponding structures to that file.

For more information, see Assigning structures to files.

11. Define relations between structures

Relations enable you to link the keys for one structure to the keys for other structures. For example, if you create a relation between a customer ID key for a transaction structure with a customer ID key for a customer structure, you can create SQL statements that retrieve transaction information and associated customer information. (For an example of a relation to a table with a literal tag, see Keys with literals.)

When the system catalog is generated, structure relations are imported as table relations. Note that most ODBC-enabled applications aren’t able to interpret table relations.

12. Validate, verify, and compare

When you have finished defining your repository, do the following in order:

Be sure to run these utilities and fix all errors and warnings before you generate the system catalog. Once the system catalog is generated, there is no connection between the system catalog and the repository. If you make changes to the repository after you generate the system catalog, the changes will not be reflected in the system catalog unless you regenerate it.

Handling a repository shared by multiple databases

A single repository can be used to define multiple databases if the file, structure, and field definitions for the databases are identical. The names of data files, however, do not need to be the same for each database. You could, for example, create data files named companyA for one database and companyB for another database. However, if the databases have the same names for the data files, each database’s files must be in a separate location.

There are four common ways to handle a repository shared by multiple databases:

For information on the Repository “Open filename” field, see Defining files.

Specifying a filename (but no path) in the Open filename field

With this method, you’ll generate a single system catalog that will be used for all the databases defined by the repository. Then, when you use an ODBC-enabled application to access one of the databases, the datasource line in the database’s connect file will determine which database to access.

1. For each file in the repository, enter only a filename in the Open filename field. Do not include a path. For example:
customer.ism
2. Generate the system catalog. The system catalog will include data file specifications, but these will include only the filenames, no paths.
3. Create a connect file and DSN for each set of data files. In each connect file, set the datasource line to the directory that contains the data files for the database. For example:
datasource   ;c:\\databases\\company4\\dat;
4. Test by accessing the databases. Make sure each DSN uses the correct connect file and accesses the correct database.

Using an environment variable in the Open filename field

With this method, you’ll generate a single system catalog that will be used for all the databases defined by the repository. Then, when you select a DSN in an ODBC-enabled application, an environment variable setting will determine which database is accessed.

1. Enter an environment variable in the Open filename field for each file in the repository. The environment variable can take the place of the path, the filename, or both. For example:
COMPANY:plants.ism

Make sure the environment variable is followed by a colon—even if the environment variable is for both the path and filename (which is a good way to handle data files whose names and locations are different for each database). For example:

ACME_PLANTS:
2. Generate the system catalog. The environment variable will take the place of hard-coded paths in the system catalog.
3. Create a connect file and DSN for each database that the repository describes.
4. Before accessing the database, set the environment variables you entered in the Open filename field.

Note that a good place to set this environment variable is in the connect files. This way the variable is redefined each time you use one of the connect files to access a database. For example, one of the connect files might have the following:

COMPANY=c:\databases\companyabc\dat

The other connect files would have different settings. For example:

COMPANY=c:\databases\companyxyz\dat
5. Test by accessing the databases. Make sure each DSN uses the correct connect file and accesses the correct database.

Using a conversion setup file to change the Open filename field

With this method, you’ll generate a conversion setup file and then use this file to generate a different system catalog for each database.

1. In the Open filename field for each file in the repository, enter a value. Later in this procedure (step 5), you’ll replace all or part of this value.

For example:

&&&&&&customer.ism
2. Generate a system catalog for a database.
3. Create a connect file for each database that the repository describes. Set the dictsource line in each connect file to the directory that will contain the system catalog files.
4. Create a conversion setup file for the system catalog.
5. Open the conversion setup file in a text editor and edit the data file settings. For example, if a data file setting is
&&&&&&customer.ism

you could change the setting to something like

c:\companyabc\customer.ism
6. Regenerate the system catalog. If you use DBA to regenerate, be sure to use the “Clear and re-create catalog”, “Initialize users and groups”, and “Conversion setup” options. If you use dbcreate, be sure to use the -c option and -i options. Use the conversion setup file you edited in step 5.
7. Open the system catalog in DBA. Then open the Table list (Maintenance > Tables). The tables should have the correct path for the database. (See the Open filename column of the Table List.)
8. Repeat step 5 through step 7 for each database.
9. Test by accessing the databases. Make sure each DSN uses the correct connect file, and make sure the dictsource line in each connect file accesses the correct system catalog.

Using USR_DD_FILNAM to change replaceable characters

With this method, you’ll create a routine that interprets replaceable characters in the Open filename field. Then you’ll rebuild DBA, create a conversion setup file, and use the conversion setup file to invoke the USR_DD_FILNAM routine as you generate a system catalog for each database.

This method is particularly useful if your repository already has replaceable characters used by the RPS_FILNAM_METHOD in ReportWriter. For information on RPS_FILNAM_METHOD, see Modifying filenames at runtime.

1. In the Open filename field for each file in the repository, enter a value with replaceable characters. (If your repository was designed to use RPS_FILNAM_METHOD, skip this step. The Open filename field will already have replaceable characters.)

For example:

c:\####\customer.ism
2. Create a routine to interpret the replaceable characters in the Open filename field, and then rebuild DBA. This routine will replace the default USR_DD_FILNAM routine; see Replacing the default USR_DD_FILNAM routine.
3. Generate a system catalog.
4. Create a connect file for the system catalog.
5. Open the system catalog in DBA, and generate a conversion setup file. As the conversion setup file is generated, replaceable characters in the Open filename field are interpreted by the USR_DD_FILNAM routine you wrote. Check the generated conversion setup file to make sure the paths are correct.
6. Regenerate the system catalog using the conversion setup file as input.

Replacing the default USR_DD_FILNAM routine

1. Write your USR_DD_FILNAM routine and save it as a .dbl file (for example, my_usr.dbl). See xfdbusr.dbl, a file included in your Connectivity Series distribution, for an example of a customized USR_DD_FILNAM routine.
2. Make sure
3. Move to the directory that contains the DBA program, which is xfdba.dbr (on Windows and UNIX) or xfdba (on OpenVMS). Typically this file is in the connect\synodbc\dba directory.
4. From the command line, compile the file you created in step 1. For example:
dbl my_usr
5. Replace the USR_DD_FILNAM routine in the DBA program’s object library. For example:
dblibr -r xfdbalib.olb my_usr.dbo
6. Do one of the following:

On UNIX and Windows:

xfdbabld

On OpenVMS:

$ @XFDBABLD

This command executes a batch file, script, or DCL command file.

dblink -o xfdba.dbr xfdbaprc.dbo xfdbalib.olb WND:tklib.elb

When you use DBA to generate the conversion setup file and the system catalog, DBA will now use the USR_DD_FILNAM you wrote to convert open filenames in the conversion setup file.