Generating the system catalog

This topic includes the following sections:

 

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:

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:

Note

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 run dbcreate from the command line on Windows, UNIX, or OpenVMS. The dbcreate program is in the connect subdirectory of the main Synergy/DE installation directory. The syntax for dbcreate is as follows:

dbcreate -c -p|-a [option] [...]
dbcreate -x|-u [option] [...]
dbcreate -?|-h

For examples, see Examples below. For information on regenerating the system catalog, see Notes on regenerating the system catalog below.

Dbcreate options

See Discussion below for more information on these options. Note that you should use either -c, -x, or -u in each dbcreate command. If you use -c, you should also use either -p or -a to indicate which type of user the system catalog will support. To preserve existing users when regenerating, include -n in the command.

-c

Generates the system catalog from scratch — i.e., removes any existing system catalog files and creates new system catalog files from the repository. See -c below.

-x

Updates the system catalog. New structures are added to the system catalog as new tables. Tables for changed structures are updated. See -x below.

-u

Adds new structures as new tables in the system catalog, but does not update tables that are already part of the system catalog. See -u below.

-a

When used with -c, this option adds support for privilege-based users or restores the initial set of privilege-based users. See -a below.

-p

Adds support for level-based users and creates or initializes a default set of users and groups. See -p below.

-n

Prevents changes to existing users and groups, but allows default users (and groups or privileges) to be created if they don’t exist. See -n below.

-r rps_main rps_text

Specifies the location and name of the repository main file and repository text file. See -r below.

-d target_dir

Specifies the directory where the system catalog will be created. See -d below.

-i [cnv_file]

When used with cnv_file, this specifies a conversion setup file (to change table access levels, exclude tables from the system catalog, or add tables back into the system catalog). See -i below.

-l log

Creates a log file to record dbcreate messages. See -l below.

-v

Turns on verbose logging, which includes informational messages in addition to cautions and errors. See -v below.

Discussion

Dbcreate options can be in any order, but each dbcreate command should include one of these options: -c, -u, or -x. If you use -c, you should also include either -p or -a to specify whether the database will have privilege-based users or level-based users and groups. For more information on users, see xfODBC security: managing access.

On OpenVMS, the dbcreate utility is set up as a verb. So if you pass more than eight parameters to dbcreate, you must enclose the parameters in quotes. Each option counts for one parameter, and each path specification counts for one parameter. The following command, for example, has nine parameters, so they must be enclosed in quotes:

$ DBCREATE "-C -P -R DATA:RPSMAIN DATA:RPSTEXT -D CAT: -l LG.TXT"

-c

The -c option causes the system catalog to be generated from scratch — i.e., it removes existing system catalog files in the target location and creates new system catalog files from the repository. If the system catalog is generated to a directory that already contains system catalog files, those files will be overwritten. The -c option should be used with either -p or -a:

To preserve existing users and privileges or existing users and groups, include the -n option in the command.

If you use a conversion setup file, changes specified in the conversion setup file will be made to the system catalog.

-x

The -x option updates a system catalog. If the repository has new structures, they are added to the system catalog as new tables. If the repository has structures that are different than the corresponding tables in the system catalog, those tables are updated. System catalog files for privilege-based users are not affected if you use this option. If you use -p, level-based default users and groups will be restored, eliminating any custom users and groups unless you also use -n.

If you use a conversion setup file, tables marked as OUT are not changed or removed. Settings in the conversion setup file are applied for tables marked as IN.

-u

The -u option adds new structures as new tables in the system catalog. Tables that are already part of the system catalog are not updated. System catalog files for privilege-based users are not affected if you use this option. If you use -p, level-based default users and groups will be restored, eliminating any custom users and groups unless you also use -n.

Conversion setup file settings are ignored with -u.

-a

The -a option is for use with -c. It adds support for privilege-based users (i.e., for users and privileges managed with GRANT and REVOKE statements) or restores the initial set of privilege-based users. If you are regenerating a system catalog that already has support for privilege-based users, using -a will eliminate any change you've made to users, unless you also use -n. (Privilege-based user files are not affected when dbcreate is used with -x or -u.)

Important

Do not use the -a option with -p or with a system catalog that supports level-based users and groups. This may corrupt the system catalog.

-p

The -p option is for use with -c. It adds support for level-based users and creates or initializes a default set of users and groups. If level-based users and groups already exist, this option restores default users and groups, eliminating any changes you've made to users and groups, unless -n is also used.

Important

Do not use the -p option with -a or with a system catalog that supports privilege-based users. This may corrupt the system catalog.

-n

The -n option prevents changes to existing users and groups, but enables dbcreate to create default users and groups if they don’t already exist. With level-based users, this is effective with -c, -x, and -u. With privilege-based users, this is effective only with -c. (Privilege-based user files are not affected by -x or -u.)

-r

The -r rps_main rps_text option specifies the location and name of the repository main file and repository text file. If specified, this option overrides RPSMFIL, RPSTFIL, and RPSDAT settings. If not specified, dbcreate uses RPSMFIL, RPSTFIL, and RPSDAT to locate repository files.

-d

The -d option specifies the directory where the system catalog will be created. If not specified, the system catalog will be created in the working directory.

-i

When -i is used with cnv_file, it specifies the conversion setup file to use. When regenerating a system catalog, a conversion setup file enables you to exclude tables from the system catalog (or add them back in) and change table access levels. To use a conversion setup file, you need to generate the conversion setup file and then either use the -i option in the dbcreate command or set the SODBC_CNVFIL environment variable before running dbcreate. See Generating and editing a conversion setup file for more information, and note the following:

The -i option is effective when used with -c and -x, but not with -u. See -c and -x above for more information.

-l

The -l option creates a log file for dbcreate messages. (If -l is not used, messages are printed to the screen.) By default, -l logs only cautions and errors. For verbose logging, use -v with -l.

Log specifies the path (optional) and filename for the log file. Include the filename extension. If you don’t specify a path, the file is saved to the current working directory.

-v

The -v option turns on verbose logging, which includes information messages in addition to cautions and errors. When used with -l, logging messages are written to a file; otherwise, they are written to the screen. (Note that if you use -v with -u or -x and a conversion setup file, the log incorrectly indicates that conversion setup file settings are applied. Instead, none are applied if used with -u. If used with -x, only settings for tables marked as IN are applied.)

Examples

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, and the system catalog will support level-based users.

dbcreate -c -p -r DATA:rpsmain DATA:rpstext -d TARGET:
$ DBCREATE -C -P -R DATA:RPSMAIN DATA:RPSTEXT -D TARGET:

The following Windows example, however, will create a system catalog that supports privilege-based users:

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

The next example adds tables for any new repository structures, but doesn’t change any existing tables:

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

This example adds tables for new structures and updates tables for structures that have changed in the repository:

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

For more examples, see Examples.

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

Note

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

DBA can be used only 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,

1. 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 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.


            
2. Select Catalog > Generate from the DBA menu. (For information on using the menus and windows in DBA, see DBA basics.)
3. 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.

The Generate System Catalog window

4. 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.)

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.

Important

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.

5. 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.

6. 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:

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.

Note the following:

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.

Preserving views

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

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:

dbcreate -u -r DATA:rpsmain DATA:rpstext -d TARGET:
$ 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:

dbcreate -x -r DATA:rpsmain DATA:rpstext -d TARGET:
$ 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:

dbcreate -p -r DATA:rpsmain DATA:rpstext -d TARGET:
$ 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:

dbcreate -p -n -r DATA:rpsmain DATA:rpstext 
$ 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:

dbcreate -c -r DATA:rpsmain DATA:rpstext -d TARGET:
$ DBCREATE -C -R DATA:RPSMAIN DATA:RPSTEXT -D TARGET: