This tutorial guides you through the steps needed to prepare a Synergy database for access by an ODBC-enabled application. For this tutorial, the resulting system catalog will support level-based users. See Managing access with users, groups, and table settings for information on users.
With this tutorial, you will do the following:
- Set catalog generation options
- Generate a system catalog
- Create a connect file
- Customize the system catalog by adding level-based 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 stand-alone 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 on Unix 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 RPSTFIL=%CONNECTDIR%synodbc\dict\rpstext.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 ODBC-enabled 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 re-create 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 level-based 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 level-based 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 |
If you want to use level-based users, there are two ways to generate a system catalog: from DBA and from the command line. You used the first method above. In this section, you’ll use the second method; you’ll use the dbcreate utility to generate a system catalog from the command line.
If you completed the previous section (Generate the system catalog from DBA), you can skip this step. But if you want to try generating the system catalog from the command line, complete this step.
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 level-based 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.) For more information on dbcreate syntax and options, see dbcreate utility.
You should now have several ISAM files that begin with GENESIS_ and 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 in Generate the system catalog from DBA above for instructions.) |
| 2. | Select Catalog > Open from the DBA menu. The Open System Catalog window opens. |
| 3. | Enter the connect file name, username, and password. The username 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 the system catalog in DBA.
|
|
If you get an error message that says “Login failed: unable to open user file”, it may be that
Try opening the system catalog again, double-checking 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 level-based 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 Managing access with users, groups, and table settings.)
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 username, such as TempUser.
Password
Enter a password for this user, such as DBAPSWD. Remember that the password and username 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 read-only access for users with an access level of 200 or greater. See Managing access with users, groups, and table settings. |
| 3. | Save your changes and close the file. |
For more information, see Editing a 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 ODBC-enabled 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 from the system catalog.
| 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 read-only 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 Managing access with users, groups, and table settings. |
| 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 dbcreate utility.) 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 Using DBA to generate a system catalog with level-based users.
| 13. | Create a DSN |
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 ODBC-enabled 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.
|
|
It is possible to connect via ADO or ADO.NET without a DSN. See DSN-less connections and Connection strings. |
| 14. | Access your data with an ODBC-enabled application |
You should now be ready to access Synergy data from an ODBC-enabled application. For examples that take you through the steps of accessing data, see ODBC access examples.
