Data Access for ISAM User Manual - Configuration

Configuring Easysoft Data Access for ISAM

This section explains how to check that Easysoft Data Access for ISAM is connecting to a sample ISAM database and how to set up additional local data sources on both Windows and Unix.

Chapter Guide

Creating data sources

A data source called SAMPLE_ISAM is automatically set up to point at an Easysoft sample ISAM database on the server machine when Easysoft Data Access for ISAM is installed.

This is used as an example in Querying a data source on Windows and Querying a data source on Unix to illustrate how to validate that a connection has been made after the installation process has been completed.

Another data source called EASYSOFT_ISAM is also created, which contains empty data and schema directories which are ready to accept data definitions entered via the Easysoft Administrator.

The EASYSOFT_ISAM data source can be used to point directly at your own ISAM data or new data sources may be created (see Creating additional data sources on Windows and Creating additional data sources on Unix) to:

A correctly configured data source can then be connected to by any ODBC-compliant application (see "Demonstration".

Querying a data source on Windows

On Windows, a program called sql.exe is installed with Easysoft Data Access for ISAM into <InstallDir>\Easysoft\Easysoft Data Access for ISAM , which can be run using the required data source as a parameter.

e.g.

sql.exe "dsn=sample_isam"

which points at the SAMPLE_ISAM Easysoft demo database.

When Easysoft Data Access for ISAM is installed, a System Data Source called SAMPLE_ISAMis automatically created and the demo database is loaded into <InstallDir>\Easysoft\Easysoft Data Access for ISAM\demo\data (for the data) and <InstallDir>\Easysoft\Easysoft Data Access for ISAM\demo (for the schema).

Querying a data source on Unix

On Unix you can use one of the two scripts which are installed with Easysoft Data Access for ISAM to query ISAM data sources using SQL statements:

¯ OR ¯

Both of these scripts are stored in <InstallDir>/easysoft/isam/bin and run an Easysoft utility called sql, which in turn executes SQL statements.

These scripts enable the sql command to be executed without needing to set LD_LIBRARY_PATH, as the script determines the LD_LIBRARY_PATH values from a separate configuration file (es.ld.so.conf) and then runs the sql command, specifying the data source to query and the user name and password to use to access the data source.

By default, both scripts specify:

The demosqlscript specifies the data source name as [SAMPLE_ISAM] (dsn=sample_isam).

The easysqlscript specifies the data source name as [EASYSOFT_ISAM] (dsn=easysoft_isam).

User names and passwords can be set up by the database owner (see odbc.ini settings for Unix).

If you want to query a data source other than SAMPLE_ISAM or EASYSOFT_ISAM you need to create a copy of either demosqlor easysqlfor each new data source, rename it and specify the appropriate data source name in each new script.

You then have a script ready to run for querying each data source against which you can execute a standard SQL command as follows (where the new data source is called "new_dsn_name" and the new script is called "new_sql"):

./sql -d "dsn=new_dsn_name" -f new_sql -o7

To query a data source using the demosql script:

1.  Change into the <InstallDir>/easysoft/isam/bin directory.

2.  Type:

 ./demosql

 If a line beginning "conn =" is displayed, a connection has been made and the data source can be queried with SQL statements.

 For example, to display a list of tables in the data source, type:

 select * from info_schema.tables;


NB

SQL lines in demosql scripts must end in the ";" character, rather than a line feed.


 If an 'invalid user' or 'invalid password' error is displayed, check that the script specifies the correct user name and password.

3.  Press <Enter> to stop typing SQL and return to the system prompt.

Another utility called isql is included in the unixODBC open source driver manager, which is installed as part of Easysoft Data Access for ISAM and can be run by going to the <InstallDir>/easysoft/unixODBC/bin directory and typing:

./isql DSN [UID [PWD]] [options]

where

Type ./isql only for option details.


NB

SQL lines in isql scripts do NOT use the ";" character as a line terminator.


Creating additional data sources on Windows

To create a data source connecting to your local System Z data:

1.  Select Start > Settings > Control Panel, double-click Administrative Tools and then Data Sources (ODBC).


9x

Select Start > Settings > Control Panel and double-click ODBC Data Sources (32bit).



NT

Select Start > Settings > Control Panel and double-click Data Sources (ODBC).


 The ODBC Data Source Administrator dialog box is displayed:

Figure 17: The ODBC Data Source Administrator

2.  Select the User DSN tab to set up a data source that only you can access.

¯ OR ¯

 Select the System DSN tab to create a data source which is available to anyone who logs on to this Windows machine.

3.  Click Add to add a new DSN.

 The Create New Data Source dialog box displays a list of the drivers which are available:

Figure 18: The Create New Data Source dialog box

4.  Select Easysoft Data Access for ISAM and then click Finish.

 A dialog box displays the fields required to configure an Easysoft Data Access for ISAM data source:

Figure 19: The Easysoft Data Access for ISAM setup dialog box

The name of the User or System data source to be created, as used by the application when calling the SQLConnect or SQLDriverConnect functions.

This name cannot be changed once the data source has been created.

Descriptive text which may be retrieved by certain applications to describe the data source.

The valid ISAM user name as defined in the ISAM schema (if any) with which to connect to the data source.

The valid ISAM password as defined in the ISAM schema (if any) for the user name entered in the User field.

The default path of the data to be accessed with this data source.

The default path of the schema to be accessed with this data source.

The maximum number of areas of cache memory that may be opened.

This field is set to 30 by default.

The time (in seconds) after which an area of cache memory will be closed.

This field is set to 30 by default.

The number of times that a retry will be attempted if a lock is encountered.

This field is set to 10 by default.

The time (in seconds) between each lock retry.

This field is set to 1000 by default.

Select this attribute to wait for a lock from another application to be released before attempting an insert, delete or update.

If any of these actions is attempted when this option is not selected the user will receive an error message immediately.

This attribute is selected by default and optimises the access speed to ISAM data files consisting of fixed length records.

Note that this option cannot be used if any of the underlying data files is of variable length.

The filename prefix to which a log file will be generated.

This may consist of a directory path only, a directory path and a filename prefix, or be left blank.

The log filename takes this prefix and adds an underscore followed by a unique number (derived from the process ID) and ".log".

For example, if you set this field to "C:\isam" then the log filename will be of the format C:\isam_026503.log.

If no path is specified then the file will be written to the directory where the data source is located.

Select this attribute to log the entries to all functions and the attributes of all calls in the SQI layer.

Select this attribute to log the exits from all functions.

Select this attribute to log information regarding the file cache.

Select this attribute to log the selection of indexes used when starting a query.

Select this attribute to log any expression checking that the SQI layer performs.

Select this attribute to log any errors reported at the SQI layer.

Select this attribute to log information on the opening of files and how many reads are performed.

Select this attribute to log summary information about the SQI function calls.


Caution!

Enabling logging will seriously impair performance so remember to disable it once you have finished.


5.  Click OK to create the data source, or click Cancel to abandon it.

The Updates button on the Easysoft Data Access for ISAM DSN dialog box attempts to connect your PC to the Internet and get the latest version number for this software from the Easysoft version server.

A message will be displayed if a more recent version of the software is available.

If you have changed your data source configuration, you are advised to click OK to save the changes before accepting the new software.

Creating additional data sources on Unix

To configure a data source for your own ISAM data when the data is local (i.e. is on the same Windows machine as the ODBC compliant application that you want to use to connect to the data), you can either:

¯ OR ¯

This is exactly the same mechanism as is used on the ODBC Data Source Administrator on Windows platforms (see Creating additional data sources on Windows).

Creating a system data source

1.  Copy <InstallDir>/easysoft/isam/schema/blank.dsn to either your home directory or a temporary directory.

2.  Edit the following settings:

3.  Log on as the root user

4.  Type:

 ODBCSEARCH="ODBC_SYSTEM_DSN"

 export ODBCSEARCH

5.  Run:

 /usr/local/easysoft/unixODBC/bin/odbcinst -i -s -f filename

 where filename is the new name in step 2, including the path name.

 This creates a system-wide data source in /etc/odbc.ini, where it can be accessed by the unixODBC driver manager.

Creating a user data source

1.  Copy <InstallDir>/easysoft/isam/schema/blank.dsn to either your home directory or a temporary directory.

2.  Edit the following settings:

3.  Run:

 /usr/local/easysoft/unixODBC/bin/odbcinst -i -s -f filename

 where filename is the new name in step 2, including the path name.

 This creates a user-specific data source in a file called .odbc.ini (residing in the home directory of the user you are currently logged on as), where it can be accessed by the unixODBC driver manager.


NB

Data sources can also be created by entering their attributes directly into either the odbc.ini or .odbc.ini files (see odbc.ini settings for Unix for details of the attributes you need to specify).


odbc.ini settings for Unix

If you are running Easysoft Data Access for ISAM on a Unix system, you will probably set up data sources on the Unix machine by editing blank.dsn (see Creating additional data sources on Unix).

However, the following data source attributes can also be specified directly in /etc/odbc.ini (system data sources) or the relevant .odbc.ini file (user data sources):

Setting Description/example
[data source name] e.g. [ISAM].
driver = EASYSOFT_ISAM
sort_path = /tmp Directory where temporary sort data is saved if sorting requires more than sort_mem_size memory
sort_mem_size = 256 Amount of memory (in Kb) allocated for sorts before the results are saved to disk.
rs_mem_size = 256 Number of rows that will cache into memory before being stored on disk.
rs_path = /tmp Directory where result set rows are temporarily stored when rs_mem_size is exceeded.
blob_path = /tmp Directory where blob data types in the result set are temporarily stored.
sqicount = 1 Number of SQIs used by the data source.
target_string1 = Data Access for ISAM Connection string for SQI.
target_driver1 = /usr/local/easysoft/isam/lib/libesdisam_sqi.so Required SQI driver.
dtcount = 1 Number of data type libraries required for ISAM.
dtlibrary1 = /usr/local/easysoft/isam/lib/libesdisam_dt.so Data type library for data type conversions.
data_path = path of the ISAM application data e.g. /usr/local/easysoft/isam/demo/data/.
schema_path = path of the ISAM application schema Directory where the data source schema files (views, users, passwords and privileges) are stored (specify a different schema path for each data source to allow unique users, privileges etc. to be configured e.g. /usr/local/easysoft/isam/demo/schema/).
isamlibrary = path of the ISAM library e.g. /usr/local/easysoft/isam/lib/libesisam.so.
cache_expiry = 2 How long files are cached open for (default is 2).
schema_null = 1 Use NULL rather than an empty string ("") to denote a table that doesn't have a schema.
catalog_null = 1 Use NULL rather than an empty string ("") to denote a table that doesn't have a catalog.If you're using an ISAM data source with a linked server in Microsoft SQL Server, add the following lines to the section for the data source in odbc.ini:schema_null = 1catalog_null = 1Doing this will prevent SQL Server from generating the following error when working with tables that don't have a schema or catalog:Invalid schema or catalog specified for provider 'MSDASQL'. OLE DB error trace [Non-interface error: Invalid schema or catalog specified for the provider.].Returning an empty string for the schema or catalog name is permitted by the ODBC specification. However, SQL Server treats an empty string as an invalid schema or catalog name.
statistics=num The level of cardinality that the SQLStatistics function returns. The default value 31 means that SQLStatistics cardinality is handled by the ODBC client application.The values can be added together. For example, to retrieve the number of table rows and to use SQL_QUICK for index statistics use the value 25.If you're working with large amounts of ISAM data in SQL Server, using the statistics setting may improve performance. If you experience performance problems, try using SQL_QUICK rather than the default SQL_ENSURE to retrieve index statistics.Note that if you override SQL Server's default behaviour for SQLStatistics in this way, you'll need to check that the results are as expected. This is because SQL Server is unaware that the potentially less accurate SQL_QUICK is being used. The application can't, therefore, allow for the possibility that the statistics are incorrect.
statistics=1 Return the number of rows in the table.
statistics=2 Use the SQL_ENSURE argument to specify the level of accuracy when retrieving unique index statistics. SQL_ENSURE requests that the statistics are retrieved unconditionally.
statistics=4 Use the SQL_ENSURE argument to specify the level of accuracy when retrieving nonunique index statistics.
statistics=8 Use the SQL_QUICK argument to specify the level of accuracy when retrieving unique index statistics. SQL_QUICK requests that the statistics are retrieved only if they are readily available. The values may not be current.
statistics=16 Use the SQL_QUICK argument to specify the level of accuracy when retrieving nonunique index statistics.
logging = 3 See Logging options.
logfile = /tmp/isam See Logging options.
default_uid = Specify a default user name for the data source. This overrides any user name specified on remote client machines.
default_pwd = Specify a default password for the data source. This overrides any password specified on remote client machines.

Figure 20: odbc.ini settings

Logging options

Various levels of logging may be enabled by insertng the logfile= and the logging= lines into your odbc.ini file:

The log filename adds to this prefix an underscore and a unique number derived from the Unix PID (process Identification number).

For example, setting logfile=/tmp/isam will result in a log filename of the format isam_026503.log.

Valid logging values are:

LogNumber Log Description
1 Entries to all functions and attributes to all calls in the SQI layer.
2 Exits from all functions.
4 File caching.
8 The opening of files and how many reads are performed.
16 Expression checking performed by the SQI layer on schema calls.
32 Errors reported at the SQI layer.
64 Indexes used when starting a query.
128 Summary information about SQI function calls.
256 Qualifiers (criteria) passed into the SQI start query function.
512 Summary information regarding the number of ISAM calls made.

Figure 21: odbc.ini log level options

Multiple logging options can be enabled by adding values together.

For example, to enable entry, exit and expression logging, use:

1+2+16 = 19


Caution!

The enabling of logging will seriously impair performance and it should be disabled after use.