WebDocs Home | Show Navigation Hide Navigation

Using the Sample Database As a Tutorial

This tutorial guides you through the steps needed to prepare a Synergy database for access by an ODBCenabled application. As you follow these steps, you will do the following:

Set catalog generation options

Generate a system catalog

Create a connect file

Customize the system catalog by adding users and groups, deleting a table, and changing a table’s access level

Regenerate the system catalog

You will do all of this with the sample database, so you don’t have to practice on your own data. The sample database is included in the Connectivity Series distribution. (It is not included in the xfODBC Client installation.) This database includes a repository and is stored in the connect\synodbc\dat and connect\synodbc\dict subdirectories of the main Synergy/DE installation directory.

Use this tutorial with a standalone xfODBC configuration or on the server of a client/server configuration.

 

On Windows, you may need to change the GENESIS_HOME environment variable setting and move the sample database and repository to a writable location outside of Program Files so that files can be created and updated. If you do this, adjust the procedures in this tutorial to use the new location.

1. Install xfODBC

The first step is to install xfODBC. Follow the installation instructions, and see “Configuring Connectivity Series”.

 

The remainder of this tutorial assumes that during installation you set the Synergy directory as /usr/synergyde in a UNIX environment or DKA600:[SYNERGYDE] on OpenVMS. If your Synergy directory has another name or location, you must substitute that name or location in the examples.

2. Set file locations and options

Once you’ve installed xfODBC, you need to set some options and file locations. These settings prepare your environment for the next step in the process, generating a system catalog. To prepare for system catalog generation, set the RPSMFIL and RPSTFIL environment variables to point to the location of the sample repository main and text files. For example,

RPSMFIL=%CONNECTDIR%synodbc\dict\rpsmain.ism
RPSMFIL=%CONNECTDIR%synodbc\dict\rpsmain.ism

For more information on these environment variables, see “Specifying repository file locations”. For general information on how to set environment variables in xfODBC, see “Setting environment variables for xfODBC”.

 

Make sure SODBC_CNVFIL is not set. This environment variable should not be set until the conversion setup file has been created. For more information, see “Specifying a conversion setup file (SODBC_CNVFIL)”.

You may want to set other environment variables that set system catalog generation options. For information, see “Setting catalog generation options”.

3. Generate the system catalog from DBA

To make Synergy data accessible to ODBCenabled applications, you must create a system catalog for the database. The system catalog is generated from repository definitions and provides the information the xfODBC driver needs to access the data files.

You can generate the system catalog from the command line using the dbcreate utility, or you can generate it from the xfODBC Database Administrator (DBA), a program that you can also use to modify the system catalog. For this tutorial, we’ll generate the system catalog both ways. In this step, we’ll use DBA to generate it; in the next step (“Generate the system catalog from the command line”), we’ll use the dbcreate utility to generate it from the command line.

1.Open DBA by doing one of the following:

In Windows Control Panel, select Synergy Control Panel, and then click “xfODBC DBA.”

Type the following at a Windows or UNIX command prompt:

dbr SODBC_DBA:xfdba.dbr

Type the following at an OpenVMS command prompt:

$ RUN SODBC_DBA:XFDBA.EXE

2.In DBA, select Catalog > Generate. (On UNIX and OpenVMS, press ctrl+p to activate the DBA menu. For more information, see “DBA basics”.)

The message “No system catalog connected.” is displayed.

3.Click OK or press enter.

4.In the Generate System Catalog window, complete the following fields:

Main repository

This field sets the path and filename of the repository main file. (By default, this field contains the value of the RPSMFIL environment variable.) Make sure this field contains one of the following:

On Windows:

CONNECTDIR:synodbc\dict\rpsmain.ism

On UNIX:

CONNECTDIR:synodbc/dict/rpsmain.ism

On OpenVMS:

DKA600:[CONNECT.SYNODBC.DICT]RPSMAIN.ISM

Text repository

This field sets the path and filename of the repository text file. (By default, this field contains the value of the RPSTFIL environment variable.) Make sure this field contains one of the following:

On Windows:

CONNECTDIR:synodbc\dict\rpstext.ism

On UNIX:

CONNECTDIR:synodbc/dict/rpstext.ism

On OpenVMS:

DKA600:[CONNECT.SYNODBC.DICT]RPSTEXT.ISM

Dictsource path

This field specifies where the system catalog will be saved. Type one of the following paths:

On Windows,

CONNECTDIR:synodbc\dict

On UNIX,

CONNECTDIR:synodbc/dict

On OpenVMS,

DKA600:[CONNECT.SYNODBC.DICT]

Conversion setup

Clear this field.

Field report view

Clear this option to generate the system catalog from all repository fields. If this option is selected, the system catalog won’t include fields for which the “No report view” option is set in the repository.

Update option

Leave the default option (Clear and recreate catalog) selected. This option ensures that the system catalog is generated from scratch. Before generating, DBA clears existing system catalog files from the directory you specify in the Dictsource path field.

Initialize users and groups

Select this option to ensure that the initial groups (SYSTEM and USER) and the initial users (DBADMIN, DBA, and PUBLIC) are created. These initial users and groups enable you to open the system catalog in DBA and customize the system catalog.

Overwrite existing

This option instructs DBA to overwrite existing users and groups. It is available only when the “Initialize users and groups” option is selected. It has no effect if you’re generating a system catalog for the first time, but if you’ve generated a system catalog and made changes to users and groups, these changes will be lost if you select this option.

5.Click OK or press enter.

6.When DBA is finished generating the system catalog, a message (“System catalog generated”) is displayed. Click OK or press enter.

For more information, see “Generating the system catalog”.

4. Generate the system catalog from the command line

There are two ways to generate a system catalog: from DBA and from the command line. “Generate the system catalog from DBA” uses the first method. In this section, you’ll use the second method; you’ll use the dbcreate utility to generate a system catalog from the command line.

Once you have completed the previous step (Generate the system catalog from DBA), you’ve generated the system catalog, so you can skip this step. If you want to try generating the system catalog from the command line, however, follow the procedure in this section.

To generate the system catalog from the command line, enter the following command:

dbcreate -c -p -r rpsmain rpstext

The c option clears and regenerates the system catalog, the p option creates a default set of users and groups, and the r option specifies the location and name of the repository main file and repository text file. (If you don’t include the r option, dbcreate uses the RPSMFIL and RPSTFIL environment variable settings.)

You should now have several ISAM files that begin with GENESIS_ or SODBC_ in the current working directory. To generate the system catalog in any other directory, enter a command with the following syntax:

dbcreate -c -p -d target_directory -r rpsmain rpstext

where target_directory is the location the system catalog will be saved to. For more information, see “Generating the system catalog”.

5. Create a connect file

The next step is to create a connect file. You’ll need a connect file to customize the system catalog and to create a data source name (DSN), which is required for ODBC access. Connect files contain information on where the Synergy data files and the system catalog are located. Connect files can also contain environment variable settings and data access settings.

The xfODBC distribution includes a sample connect file, sodbc_sa, located in the directory that the GENESIS_HOME environment variable is automatically set to (connect\synodbc). You can use this connect file to complete the tutorial, or you can create your own as an exercise.

Every connect file must have a dictsource line and a datasource line. The dictsource line specifies the directory where your system catalog will be located, and the datasource line specifies the directory where your Synergy data files reside. You can also set some environment variables in the connect file. If you use any environment variables in the Open filename field of Repository file definitions, this file is the best place to define those variables. It’s better than defining them system wide (where they’re not needed), and it keeps them all in one location.

For the sample database, the connect file must contain three lines: the dictsource line, the datasource line, and a line that sets the XFDBTUT environment variable.

For Windows:

dictsource "C:\Program Files\Synergex\SynergyDE\connect\synodbc\dict\"
datasource ";C:\\Program Files\\Synergex\\SynergyDE\\connect\\synodbc\\dat;"
XFDBTUT=C:\Program Files\Synergex\SynergyDE\connect\synodbc\dat

For UNIX:

dictsource  /usr/synergyde/connect/synodbc/dict
datasource ;/usr/synergyde/connect/synodbc/dat;
XFDBTUT=/usr/synergyde/connect/synodbc/dat

For OpenVMS:

dictsource  DKA600:[SYNERGYDE.CONNECT.SYNODBC.DICT]
datasource ;DKA600:[SYNERGYDE.CONNECT.SYNODBC.DAT];
XFDBTUT=DKA600:[SYNERGYDE.CONNECT.SYNODBC.DAT]

If you make your own connect file, create a text file with the above lines. Then, to make sure you created the connect file correctly, compare the file you create to the sample connect file, sodbc_sa. For more information, see “Setting Up a Connect File.”

6. Open the system catalog

Once you’ve generated a system catalog and created a connect file, you can view and customize the system catalog.

1.Open the DBA program if it isn’t already open. (See step 1 for instructions.)

2.Select Catalog > Open. The Open System Catalog window opens.

3.Enter the connect file name, user name, and password. The user name and password are case sensitive.

In the Connect file field, enter the name of the connect file. If you created your own connect file, enter its filename here. Otherwise, type

sodbc_sa

In the User name field, type

DBADMIN

In the Password field, type

MANAGER

Alternatively, you can type an entire connect string in the Connect file field—for example, DBADMIN/MANAGER/sodbc_sa. (The syntax for a connect string is username/password/connect_filename.) If you do this, leave the User name and Password fields blank.

You can also open the system catalog from the command line. To do this, close DBA; then do one of the following:

Type the following at a Windows or UNIX prompt:

dbr SODBC_DBA:xfdba.dbr -c DBADMIN/MANAGER/sodbc_sa

Type the following at an OpenVMS prompt:

$ XFDBA -C DBADMIN/MANAGER/SODBC_SA

For more information, see “Opening a system catalog in DBA”.

 

If you get an error message that says “Login failed: unable to open user file”, it may be that

the users and groups were not initialized when you generated the system catalog.

one of the entries may have been spelled incorrectly.

the case of the user name or password was incorrect.

Try opening the system catalog again, doublechecking the case and the spelling. If you still aren’t able to open it, regenerate the system catalog by following the instructions in “Generate the system catalog from DBA” or “Generate the system catalog from the command line”. Make sure you either select the Initialize users and groups option (in DBA) or set the p option (for the command line). Once you’ve regenerated the system catalog, open the system catalog in DBA.

7. Modify the users and groups

Once you’ve opened the system catalog in DBA, you can view and customize users and groups; you can view tables, columns, indexes, and segments; and you can delete tables and columns.

1.Select Maintenance > Groups. Then select Group Maintenance > New Group to display the Group window.

2.In the Group window, complete the following fields:

Group name

Enter a temporary group name, such as TEMPGRP.

Access level

Enter an access level of 102. (You may want to test different access levels to see how they affect read/write access. For more information on access levels, see “Setting security levels”.)

Description

Enter a description, such as “Temporary group”, and then click OK or press enter. Notice the Group ID in the Group List window; it should be 3.

3.Close the Group List window.

4.Select Maintenance > Users. Then select User Maintenance > New User. The User window is displayed.

5.In the User window, complete the following fields:

User name

Enter a temporary user name, such as TempUser.

Password

Enter a password for this user, such as DBAPSWD. Remember that the password and user name are case sensitive.

Group ID

Assign the user to the new temporary group by entering 3 in this field.

Full name

Enter the user’s full name, or enter a description of the user, such as “Temporary user”.

6.Click OK or press enter, and then close the User List window.

7.Select Maintenance > Groups. Notice that the TEMPGRP now has one user assigned to it.

8.Close the Group List window.

8. Generate a conversion setup file

Conversion setup files are text files that contain information on tables in the data files. This information includes table names, table access levels, and data file locations, among other things. Using a conversion setup file, you can change the access level of a table or add a table back into the system catalog. Additionally, if you use DBA to delete a table from the system catalog, you can use a conversion setup file to preserve that change when you regenerate. See “Generating and editing a conversion setup file”.

1.Select Catalog > Generate Conversion Setup File.

2.Click OK or press enter to generate the conversion setup file to the displayed path and name.

3.If the file already exists, you are prompted to overwrite it. Click OK or press enter to overwrite the existing file.

4.Set the SODBC_CNVFIL environment variable to the path and filename of the conversion setup file you just generated. (For more information, see “Specifying a conversion setup file (SODBC_CNVFIL)”.) For example:

SODBC_CNVFIL=GENESIS_HOME:sodbccnv.ini

For more information, see “Generating and editing a conversion setup file”.

9. Edit the conversion setup file

Once you’ve generated the conversion setup file, you can use a text editor to do the following:

Mark a table as IN or OUT. This determines whether the table will be considered when the system catalog is regenerated. For information, see IN | OUT.

Change a table’s access level.

Change a table’s data file location.

To edit the conversion setup file,

1.Open the conversion setup file sodbccnv.ini with a text editor. Notice the following:

Each of the four data tables is set to IN.

Each data table has an access level of 100.

Each data table specifies the XFDBTUT environment variable for opening the data file.

2.Change the access level of the ORDERS table to 101 and the PLANTS table to 200. An access level of 101 allows read/write access for users with an access level of 101 or greater; an access level of 200 allows readonly access for users with an access level of 200 or greater. See “Setting security levels”.

3.Save your changes and close the file.

For more information, see “Editing the conversion setup file”.

10. Remove a table from the system catalog

You can use DBA to delete a table from the system catalog, but the table will be added back if you regenerate the system catalog. To remove a table and keep it out—even if you regenerate—use a conversion setup file.

1.Make sure the SODBC_CNVFIL environment variable is set to the location of your conversion setup file.

2.In DBA, open the system catalog for the sample database.

3.Select Maintenance > Tables.

4.Highlight the VENDORS table and select Table Maintenance > Delete Table.

5.At the prompt, click OK or press enter to delete the table.

6.Open the conversion setup file in a text editor. Note that the VENDOR table is set to OUT, so it is not available to an ODBCenabled application. Before you deleted the VENDORS table, this was set to IN, but because the SODBC_CNVFIL environment variable is set, tables deleted from the DBA are automatically set to OUT in the conversion setup file. (If SODBC_CNVFIL is not set when you delete a table in DBA, the conversion setup file will not be automatically updated.) You can also change a table’s IN |  OUT setting by editing the conversion setup file.

For more information, see “Deleting a table”.

11. Change a table’s access level

To change a table’s access level, you must edit the conversion setup file and then regenerate the system catalog, using the conversion setup file as input.

1.Open the conversion setup file in a text editor.

2.On the CUSTOMERS line, change ACC=100 to ACC=101. This changes the CUSTOMERS table from a readonly table to a table that can be viewed and changed by users that belong to groups with access levels of 101 or greater. For more information, see “Setting security levels”.

3.Save the conversion setup file.

To complete this change, you must regenerate the system catalog, using the conversion setup file as input. As the system catalog is regenerated, DBA reads the conversion setup file and makes any changes specified in this file to the system catalog.

12. Regenerate the system catalog

After making changes to the conversion setup file, regenerate the system catalog. You can regenerate the system catalog from the command line or from DBA. If you generate from the command line, use the i option and specify the conversion setup file’s path and filename after the option. (See “Using a conversion setup file”.) If you use DBA to regenerate, make sure the Conversion setup field of the Generate System Catalog window contains the conversion setup file name. For information, see “Regenerating the system catalog”.

13. Access your data with an ODBCenabled application

Now that you’ve set file locations and options, generated a system catalog, and created a connect file, you’re almost ready to access the sample database from odbcenabled applications. There is, however, one more requirement: in most cases, you must have a DSN (data source name).

DSNs contain the information needed to access a database. For example, a DSN may contain the name of the connect file as well as user and password information. You can create a DSN for the sample database or you can use the system DSN (named xfODBC) included with the Connectivity Series installation. For information on DSNs, see “Setting up access with DSNs”.

See “ODBC access examples” for examples that take you through the steps of accessing data.

 

It is possible to connect via ADO or ADO.NET without a DSN. See “DSNless connections” and “Connection strings”.