Generating the system catalog

This topic includes the following sections:

Prerequisites
Using dbcreate to generate the system catalog
Using DBA to generate a system catalog with level-based users
Notes on regenerating the system catalog

A system catalog provides the information xfODBC needs to access a Synergy database. It is required for xfODBC access and must be generated from the repository for the Synergy database.

There are two ways to generate the system catalog:

From the command line with the dbcreate utility
From the xfODBC Database Administrator (DBA) program, which runs dbcreate in the background

A system catalog must support either privilege-based users or level-based users. To support privilege-based users, the system catalog must be generated using dbcreate from the command line. For level-based users, you can use either method, but we recommend using dbcreate from the command line.

When a system catalog is first generated, a table is created for each structure in the repository. System catalog files are also created for users and groups or privileges. For information on system catalog files, see System catalog.

Prerequisites

Before generating a system catalog, you must do the following:

Install and configure xfODBC. See xfODBC requirements and installation.
Define your database’s schema in a repository. See Setting up a repository. (For information on generating a system catalog or a set of system catalogs for databases that share a repository, see Handling a repository shared by multiple databases.)
Set needed environment variables for file locations, data conversion options, and other system catalog generation options. See System catalog generation issues, Specifying file locations, and Setting catalog generation options. (You can set some system catalog generation options when you generate. See Using dbcreate to generate the system catalog and Using DBA to generate a system catalog with level-based users.)
(Recommended) Compare repository definitions to the Synergy database files. See Validate, verify, and compare.
Decide which user type the system catalog will support: privilege-based or level-based. Users make it possible to access the database and the system catalog. See Managing access with users, groups, and table settings for more information.

To generate a system catalog, xfODBC must be able to locate the error message file. For information, see Specifying the error message file (GENESIS_MSG_FILE).

Using dbcreate to generate the system catalog

You can use dbcreate to generate a system catalog by running it from the command line on Windows, UNIX, or OpenVMS. The dbcreate utility has options to generate or regenerate a system catalog with privilege-based users or with level-based users. See dbcreate utility for information on syntax and for examples. For information on using dbcreate to regenerate the system catalog, see Notes on regenerating the system catalog below.

Using DBA to generate a system catalog with level-based users

Do not use DBA to regenerate a system catalog that supports privilege-based users. This may corrupt the system catalog.

DBA can be used to generate system catalogs that support level-based users. However, we recommend that you use dbcreate (from the command line) rather than DBA. Dbcreate enables you to see messages that document the generation process.

To use DBA to generate a system catalog,

Start DBA by opening Windows Control Panel, selecting Synergy Control Panel, and clicking "xfODBC DBA". Or start it by typing the following at a Windows or UNIX prompt:

dbr SODBC_DBA:xfdba.dbr

To open DBA from an OpenVMS prompt, type the following:

$ RUN SODBC_DBA:XFDBA.EXE

See Starting DBA (xfdba) for more information on starting DBA from the command line, including information on command line parameters, such as -c, which enables you to open an existing system catalog as you start DBA.

Select Catalog > Generate from the DBA menu. (For information on using the menus and windows in DBA, see DBA basics.)

A window will open with the message "No system catalog connected" (unless you opened a system catalog as you started DBA by using the -c option on the command line). Click OK or press Enter. The Generate System Catalog window will open (see figure 1).

1. The Generate System Catalog window.

Enter or modify data in the fields:

Main repository

Enter the name and location of your repository main file (for example, rpsmain.ism). If the RPSMFIL environment variable is set, this field is automatically populated with the RPSMFIL setting (a path and filename). If RPSMFIL is not set, but RPSDAT is, this field is automatically populated with the RPSDAT setting (a path) and the rpsmain.ism filename.

Text repository

Enter the name and location of your repository text file (for example, rpstext.ism). If the RPSTFIL environment variable is set, this field is automatically populated with the RPSTFIL setting (a path and filename). If RPSTFIL is not set, but RPSDAT is, this field is automatically populated with the RPSDAT setting (a path) and the rpstext.ism filename.

Dictsource path

Enter the directory in which you would like to place the system catalog files. If you are regenerating a system catalog and you used the -c command-line option when starting DBA, this field displays the path specified in the dictsource line of the connect file.

Conversion setup

If you want to use a conversion setup file as input, enter the path and filename. If you’ve set the SODBC_CNVFIL environment variable, DBA automatically populates this field with the SODBC_CNVFIL setting. Clear the field (or leave it blank) if you do not want to use a conversion setup file. For client/server configurations, the conversion setup file must be on the server. (For information on creating a conversion setup file, see Generating and editing a conversion setup file.)

Field report view

Clear this option if you want DBA to ignore repository report view flag settings and include all fields — both viewable and non-viewable. Select this option if you want DBA to omit fields with report view flag settings of non-viewable. However, note that fields defined as keys or tags in Repository are always included in the system catalog.

Update option

Select one of the following update options to determine how the system catalog will be generated. (Select "Clear and re-create catalog" to create a new system catalog.)

To update an existing system catalog by generating only tables that do not already exist, select "Only add new tables." Existing system catalog files are not changed. (This is equivalent to running dbcreate with the -u option.) Conversion setup file settings are ignored if you use this option.
To update an existing system catalog based on changes to the repository, select "Add new tables and update existing tables." (This is equivalent to running dbcreate with the -x option.) This option overwrites existing tables in the system catalog, and it adds any new table definitions. If you use a conversion setup file, tables marked as OUT are not changed or removed.
To create the system catalog from scratch , select "Clear and re-create catalog". This option creates tables in the system catalog as they are defined in the repository. (This is equivalent to running dbcreate with the -c option.) If you use a conversion setup file, tables marked OUT are omitted from the system catalog.

Initialize users and groups

Select this option to create or restore the default set of level-based users and groups. If you select this option but do not select “Overwrite existing”, DBA creates the default users and groups only if the system catalog has no users and groups. If you select this option and select "Overwrite existing", DBA creates the default users and groups, overwriting any changes you've made to users and groups. For more information, see Initializing level-based users and groups.

When you create a new system catalog, you must use this option, which adds support for level-based users and groups. This adds the sodbc_users.* and sodbc_groups.* files to the system catalog. Without this support (without these files), you will not be able to access the database or the system catalog. Be sure to create these files and keep them with the other system catalog files.

Overwrite existing

Select this option to overwrite existing users and groups; all changes you’ve made to users and groups will be lost. This option is available only when “Initialize users and groups” is selected.

Click OK or press Enter to start the generation.

Unless an error occurs, an information window will open and display the message “System catalog generated.” If there is an error, a log file (ConvErrs.log) will be created in your TEMP directory.

Make sure the system catalog has a table for each structure you want included.

Notes on regenerating the system catalog

Typically, you will need to regenerate the system catalog if you have done one of the following:

Made changes to the repository
Made changes to the conversion setup file

You may want to regenerate the system catalog if you made changes to the location of Synergy data files (for an alternative, see Changing the location or names for table data files), and you may want to regenerate if you are distributing several system catalogs all based on the same repository definitions (see Handling a repository shared by multiple databases).

You do not need to regenerate for changes made to tables with DBA. These changes automatically update the relevant catalog files. Rather, regenerating the system catalog may reverse these changes if you’re not careful when you specify generation options and fail to use a conversion setup file when necessary.

To regenerate a system catalog, use dbcreate or DBA. Be sure to use the repository files originally used to create the system catalog, and note that generated files will overwrite any system catalog files in the target directory.

With dbcreate, use -c, -x, or -u, depending on how you want the system catalog regenerated. If you use -c, be sure to use -p or -a, depending on the type of user you want to use with the database (and be sure to use -n if you want to preserve the current set of users and groups or users and privileges). You must use dbcreate to regenerate a system catalog with privilege-based users. See dbcreate utility for more information on dbcreate options and syntax.
With DBA, open the system catalog (see Starting DBA (xfdba)), select Catalog > Generate from the DBA menu, change settings in the Generate System Catalog window as necessary, and click OK or press Enter. Unless an error occurs, an information window will open displaying the message "System catalog generated". If there is an error, a log file (ConvErrs.log) will be created in your TEMP directory.

Note the following:

If you regenerate the system catalog after making changes to the repository, we suggest you use the -c option (the "Clear and re-create catalog" option in DBA), which clears tables in the system catalog before regenerating it. Other options may not fully update the system catalog contents.
If you are regenerating the system catalog to incorporate changes in a conversion setup file, be sure to include the -i option in the dbcreate command, set SODBC_CNVFIL, or specify the file in the "Conversion setup" field in DBA.

Make sure the options you use with dbcreate or DBA don't overwrite information you want to preserve. For example, be careful not to inadvertently initialize users and groups or privileges, which will reverse changes you've made to these.

Privilege-based users are automatically initialized when you regenerate a system catalog with -c unless you use the -n option. (Privilege-based users are not affected by -x or -u.) So if you've added users or made changes to privileges, those changes will be lost when you regenerate with -c unless you also use -n.
Level-based users and groups are not automatically initialized when you regenerate a system catalog. To initialize them, use the -p option for dbcreate without using -n (which allows user and group files to be created if they don't exist, but does not allow users and groups to be initialized).

Preserving views

To preserve views in a system catalog when you regenerate, do one of the following:

When using dbcreate from the command line, use the -u or -x options (rather than -c).
When using the DBA program, use the “Only add new tables” option or the “Add new tables and update existing tables” option.
Write a program that saves view information, and run that program before you regenerate your catalog. Then, after the catalog has been regenerated, use the output from the program to re-create the views. The Connectivity Series distribution includes an example SQL Connection program, exam_saveviews.dbl (in the connect\synsqlx directory), that illustrates this.

Example dbcreate commands for regenerating

In the following examples, the repository files are named rpsmain and rpstext and are located in a directory defined by the environment variable DATA. The system catalog files will be created in a directory defined by the environment variable TARGET.

Adding a new table definition to an existing system catalog

To add a new table definition to an existing system catalog, create the new structure definition in Repository; then enter one of the following at the command line:

On Windows or UNIX:

dbcreate -u -r DATA:rpsmain DATA:rpstext -d TARGET:

On OpenVMS:

$ DBCREATE -U -R DATA:RPSMAIN DATA:RPSTEXT -D TARGET:

Changing an existing table definition in a system catalog

To change an existing table definition in a system catalog, change the existing structure definition in Repository; then enter one of the following at the command line:

On Windows or UNIX:

dbcreate -x -r DATA:rpsmain DATA:rpstext -d TARGET:

On OpenVMS:

$ DBCREATE -X -R DATA:RPSMAIN DATA:RPSTEXT -D TARGET:

Creating the default level-based user and group files in a specified directory

To create the default user and group files in a specified directory, enter one of the following at the command line:

On Windows or UNIX:

dbcreate -p -r DATA:rpsmain DATA:rpstext -d TARGET:

On OpenVMS:

$ DBCREATE -P -R DATA:RPSMAIN DATA:RPSTEXT -D TARGET:

Creating the default level-based user and group files if they don’t already exist

To create default user and group files in current directory—but only if they don’t already exist, enter one of the following at the command line:

On Windows or UNIX:

dbcreate -p -n -r DATA:rpsmain DATA:rpstext

On OpenVMS:

$ DBCREATE -P -N -R DATA:RPSMAIN DATA:RPSTEXT

Removing a single table from a system catalog

To remove a single table from a system catalog, remove the structure definition in Repository; then enter one of the following at the command line:

On Windows or UNIX:

dbcreate -c -r DATA:rpsmain DATA:rpstext -d TARGET:

On OpenVMS:

$ DBCREATE -C -R DATA:RPSMAIN DATA:RPSTEXT -D TARGET: