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 manager.
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:
- A complete set of structures, tags, fields, and file information
- Templates for similar fields
- Well-chosen keys and relations
Before you define your repository
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
- definition files (also called include files). These files contain data definitions and can be added to your program with .INCLUDE statements.
- FDL or XDL files used to create the data files. If your data files were created with FDL or XDL files, you can print these files and use the information in them to manually define structures, fields, tags, and keys in your repository. For information on XDL and FDL, see ISAM definition language. On OpenVMS, you can use the following to generate an FDL file:
ANALYZE/RMS/FDL filename
- parameter files. You can use the ipar utility to generate parameter files, which are files that list record layout and key information for ISAM files. Although these files can be used as input for the bldism utility, they’re also useful when creating a repository; they list data file information in a readable format. You can use this information to manually define structures in Repository.
As you gather record layout information,
- list the primary key and all secondary keys for each record layout. Keep track of the key order. See step 8 for information on keys.
- list all tags for the records. See step 7 for information on tags.
- identify template candidates. See step 4 for information on templates.
| 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.
|
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.
|
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 have include files, you can use these to load field definitions directly into your repository. See Loading fields from a definition file.
- Assign the templates you created in step 4 to the fields they were created for.
- If you want a field to have a different name in the system catalog then it has in the repository, use the Alternate name option. You can specify an Alternate name in a template or in the field itself. This option enables you to normalize names and to replace cryptic names with names your customers can understand (which is strongly recommended). For example, if a field that contains a customer number has the name “cf_1” in the repository, you can use Alternate name to give it the name “Customer_number” in the system catalog. When users access the database with an ODBC-enabled application, they will see “Customer_number” as the column name.
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).
- To prevent xfODBC from accessing a field, set the Excluded by ReportWriter option for that field. This causes the field to be omitted from the system catalog. (For example, you can use this option to omit overlay fields, which present alternative views of a database.) For information on setting the Excluded by ReportWriter option, see Basic field information. (For information on how to ignore Excluded by ReportWriter settings, see Including and omitting fields.)
- If you create an overlay field, set it to read-only. (Set the Read-only option in Repository or use the READONLY repository schema keyword.) This is necessary because if a field is not in an INSERT statement’s column list, the INSERT statement will set the field to null unless it is read-only. And if an overlay is not in an INSERT statement’s column list and is not read-only, both the overlay and the fields that make up the overlay are set to null.
- If you use a format string to define a decimal point for a decimal field in Repository, you can either set SODBC_USEFORMAT or do the following.
| 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.
|
|
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:
- Foreign keys are used only if an ODBC-enabled application supports the ODBC API function SQLForeignKeys.
- The xfODBC driver considers the first unique access key to be the primary key.
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 client/server configurations, the path must be local to the server.
- If you use an environment variable for the path or the path and filename, be sure to set the environment variable in the connect file, in an environment setup file, or in the environment (not in synergy.ini). In a client/server configuration, set it on the server.
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:
- Validate your repository with the Validate Repository utility. This utility validates all values specified for repository definitions (field options, structure options, template options, and so forth).
- Verify your repository with the Verify Repository utility. This utility verifies the integrity (internal consistency) of your repository. The Verify Repository utility attempts to repair any problem it discovers.
- Use the Compare Repository to Files utility (fcompare) to compare the repository definitions to your Synergy database files. This is available from the Utilities menu in S/DE Repository and from the command line.
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:
- Specifying a filename (but no path) in the Open filename field. If generating a single system catalog suits your purpose, this method is generally the most convenient. You generate a single system catalog for all the databases, use the datasource line in each connect file to set the path for the data files, and use a separate connect file for each database.
- Using an environment variable in the Open filename field. For this method, you generate a single system catalog for all the databases, but instead of using the datasource line in the connect files, you set an environment variable to determine which database is accessed.
- Using a conversion setup file to change the Open filename field. If you want to create a separate system catalog for each database, this is generally the most convenient method. You enter whatever you want in the Open filename file and then use a conversion setup file to edit the data file locations before generating a system catalog. With this method, you also use a separate connect file for each database.
- Using USR_DD_FILNAM to change replaceable characters. If you use the RPS_FILNAM_METHOD in ReportWriter to interpret the Open filename field in your repository, this method is probably the best. You create a routine that interprets replaceable characters in the Open filename field, rebuild the DBA program to include the new routine, and then use a conversion setup file to invoke the routine as you generate a system catalog for each database.
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
| 2. | Make sure |
- PATH contains the location of dbcreate.
- DBLDIR is set in synergy.ini to your Synergy root directory.
- WND is set in synergy.ini to your UI Toolkit directory.
| 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: |
- Rebuild DBA by entering one of the following.
On UNIX and Windows:
xfdbabld
On OpenVMS:
$ @XFDBABLD
This command executes a batch file, script, or DCL command file.
- Use the dblink command at a command prompt. For example:
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.
