View source for ODBC96

= Implementing ODBC access on UV 9.6 =

Note that these instructions refer to Ardent, but presumably are pretty much the same for IBM ... also, our TCL prompt is the : :-)

Ardent provide an account for managing ODBC access, called HS.ADMIN (HS refers to the old [[HyperStar]] product. Did Prime buy [[HyperStar]]?). Within this is the admin command, also HS.ADMIN.

* Preparing the account

<pre>
: LOGTO HS.ADMIN
: HS.ADMIN

[[UniVerse]] Server Administration

 1. List activated accounts
 2. Show [[UniVerse]] ODBC Config configuration for an account
 3. Activate access to files in an account
 4. Deactivate access to files in an account
 5. Run HS.SCRUB on a File[[/Table]]
 6. Update File Information Cache in an account
                                                                                
Which would you like? ( 1 - 6 )  ?
</pre>

Select option 3, give it the account name, and wait. The account is now configured for ODBC access. However, converting an existing account to ODBC is fraught with problems.


* Accessing the data

Log to the enabled account. Several new VOC entries will have been added, the main ones being the [[HS_FILE_ACCESS]] file, and the HS.SCRUB, HS.UPDATE.FILEINFO, and HS.SHOW.CONFIG verb.

HS.SHOW.CONFIG will show you the necessary information to put into the ODBC client to access the account. On your client pc, start up UVODBC config, and enter this information. It is allegedly always better to use the absolute path of the account, rather than the account name, as this is supposed to make a major difference to access times. Try using an application such as MS Access to make sure the account and files are visible. Note that you may be puzzled as to the fact that some files aren't or are there when you think it should be the other way round...

By default, the [[HS_FILE_ACCESS]] file is created with entries giving system files NONE access, and the DEFAULT access is [[READ_WRITE]].

HS.UPDATE.FILEINFO will have been run by the conversion program, and will have created a hidden OS file telling the ODBC program which files to make visible. Unfortunately, HS.UPDATE.FILEINFO has a habit of abending silently on problems, and can leave the hidden file either truncated or corrupted. In the former case, you're left wondering why some files aren't visible, in the latter ODBC just doesn't work. Later versions apparently have a verbose option available, with 9.6 I was given some help to make it run in verbose mode all the time (which is a right nuisance most of the time, but invaluable when things go wrong). The following program should be substituted for HS.UPDATE.FILEINFO
<pre>
0001:       CALL *HS.FILEINFO( RESULT, "REFRESH", 1)
0002:       PRINT RESULT
0003:    END
</pre>
If there are problems, this will at least give you the information as to which file is causing it to abend. Finding out why may still be an interesting challenge.

* Cleaning up the account

ODBC is very sensitive to badly configured dictionaries. In order to know which fields to export, it will first look for the PHrase @SELECT, and secondly for @. However, HS.UPDATE.FILEINFO scans the entire VOC, so it pays to have everything "just so", not just the stuff you plan to export. Also, multi-value fields are exported separately from single-value fields, each ASSOC or unassociated field being exported in its own table. UV provides the HS.SCRUB tool for cleaning up.

HS.SCRUB is good for recommending SQL data types for each exported data field. It will check and flag any data records which do not match. But what it does not do very well (if at all) is make sure that your dictionary is consistent and not likely to confuse ODBC with any discrepancies. To that end, I've written a couple of utilities.

[[ASSOC.INTEGRITY]] Checks the integrity of associated fields. It goes through the dictionary, deleting any association for a single-value field, and building fresh phrases for any associations.

[[SQLDICT]] Builds an @SELECT phrase for ODBC. It creates three sub-phrases, @D-TYPES and @I-TYPES, which contain a list of D and I fields with valid SQL type-declarations in field 8, and which are referenced by @SELECT, and also @NO-SQL which is a list of fields with no SQL type-declaration. If field 8 contains "NO SQL" then this routine ignores it completely.

After running these two latter routines, HS.SCRUB should be run and the output reviewed, not least because it provides a lot of useful diagnostic information. And don't forget to review any changes these three programs may have made to the dictionary!

* Gotchas

In our experience, there is a problem with CHAR[[/VARCHAR]]. MS Access seems to like one version and choke on the other, while MS SQL-Server does exactly the same the other way round :-(