home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
OS/2 Shareware BBS: InfoMgt
/
InfoMgt.zip
/
hwid2o.zip
/
HWID2D.DOC
next >
Wrap
Text File
|
1998-02-24
|
30KB
|
771 lines
DBF2DB2
=======================
Welcome to DBF2DB2 by Harald Wilhelm.
This file contains information you need to install and run DBF2DB2.
Disclaimer
==========
This product is supplied with no warranty, either expressed or implied. The
author Harald Wilhelm will not accept responsibility for any adverse
consequences that may arise from the use or misuse of DBF2DB2 or its
associated program and data files. Such adverse consequences include, but are
not limited to, loss of profit, and loss of data.
The author specifically disclaims all warranties, expressed or implied,
including but not limited to, any implied warranty of merchantability or
fitness for a particular purpose.
Copyright
=========
DBF2DB2 is Copyright 1998 Harald Wilhelm.
DBF2DB2 exists in evaluation and licensed versions.
Evaluation copies of DBF2DB2 are supplied for evaluation only. You may
distribute them freely, but they are not to be used commercially. After
21 days of evaluation, you must cease using DBF2DB2 unless you register it.
Licensed copies of DBF2DB2 may be used commerically, but not freely
distributed. Only one instance of each licensed copy may be used at any
particular time. The copyright of DBF2DB2 is currently held by the author
Harald Wilhelm.
Licensing DBF2DB2
=================
The author Harald Wilhelm invites you to license DBF2DB2 under negotiable
terms. To contact the author, see "Support" at the end of this document.
Alternatively, see "How to register" below if you simply wish to continue
using DBF2DB2.
How to register
===============
DBF2DB2 is shareware. This means that you can use the program for a limited
time (21 days) before you decide whether you want to keep it. If you want to
continue using DBF2DB2 after that time you are expected to register it. The
registered version (which you have after applying the registration codes),
removes the limitations of the non-registered release, but is otherwise
identical to the evaluation version.
Once you have registered, technical support is available from the author via
e-mail (prefered), fax or mail. Voice support is not provided.
Please see the file ORDER.TXT for order information.
After receiving the registration keys from the author, please edit the file
called HWID2D.INI. In the LICENSE section change the NAME and the CODE
entries. Replace the "Evaluation NAME" and the CODE with the name and code
you received from the author.
What is DBF2DB2?
================
With IBM DB2 UDB V5 a new feature was introduced. This feature is called
Table Function UDF. An UDF, User Defined Function, is external written by
third parties or customers to add new functions to the already installed
functions that come with DB2 UDB.
There are three typed of User Defined Functions. The third, new, feature is
called Table Function UDF. A Table Function UDF is an external written piece
of code that brings data from external sources into DB2 UDB. DB2 UDB operates
on this data like data from it's own tables.
DBF2DB2 utilizes this Table Function UDF to bring Dbase III databases into DB2.
With this feature two worlds come together. After registering this Table
Function UDF for every DB2 UDB database where you need access to Dbase III data
as well, you can work with Dbase III databases like DB2 does with it's own
tables. With standard SQL statements you can read, collect, search and even
join DB2 data and Dbase III data.
Before You Install
==================
Software Requirements
---------------------
OS/2
----
IBM OS/2 Warp V4 or higher
IBM DB2 UDB V5 or higher
Windows NT
----------
Microsoft Windows NT V4 or higher
IBM DB2 UDB V5 or higher
Installing DBF2DB2
==================
DBF2DB2 comes as a ZIP files either for IBM OS/2 Warp V4 or
Microsoft Windows NT V5. The files are called:
HWID2O.ZIP for IBM OS/2 Warp V4
HWID2N.ZIP for Microsoft Windows NT V4
Within a Single User Environment please use the ZIP file that matches your
operating system installed in your Single User Environment. In a Single User
Environment DBF2DB2 must be installed on every machine with IBM DB2 UDB V5.
Within a LAN or Workgroup Environment please use the ZIP file that matches
the operating system installed on your IBM DB2 UDB V5 server. In a DB2 UDB
Client Server Environment DBF2DB2 needs to be installed on the DB2 UDB server
only.
After you checked the correct ZIP file for your needs, please UNZIP the
complete HWID2x package into the SQLLIB\FUNCTION directory on your
IBM DB2 UDB machine. In a Single User Environment you need to install
DBF2DB2 on every machine. In a Workgroup Environment please install DBF2DB2
on the DB2 UDB server only.
Fast start
==========
For an immediate test, please use the following instructions. This describes
a typical scenario that you must repeat for every Dbase III database that
needs to be cataloged with a DB2 UDB system. There must be at least one DB2
UDB database created in your environment:
1. Please go to the directory where your DB2 UDB package was installed. In
a Single User Environment go to the SQLLIB\FUNCTION directory on your
local machine. In a Workgroup Environment please go to the
SQLLIB\FUNCTION directory on the DB2 UDB server. For example:
C:
CD \SQLLIB\FUNCTION
If your SQLLIB package is installed on a different drive or in a
different directory, please use the drives and/or directories that will
match.
2. Please copy the correct ZIP file that matches the operating system of
your SQLLIB package. For example:
COPY A:\HWID2O.ZIP
If the ZIP file is not on a disk please change the statements shown
above. Shown above is the ZIP file for IBM OS/2 Warp V4. If you need
to install the DBF2DB2 on a Microsoft Windows NT V4 system please
use the following command:
COPY A:\HWID2N.ZIP
3. We need to collect information about the Dbase III database, it's
contents and it's location. This information is stored into the
HWID2D.INI file and in a separate file for each Dbase III database.
We need to run the HWID2D.EXE service application to collect this
information. For example:
On OS/2: HWID2DO.EXE -dC:\SQLLIB\FUNCTION\TEST.DBF
On NT: HWID2DN.EXE -dC:\SQLLIB\FUNCTION\TEST.DBF
Please note that this command must be activated in the \SQLLIB\FUNCTION
directory only. If a Dbase III database is found at a different
location please change the location in the statement shown above.
After this application start we will find a modified HWID2D.INI and a
new file called TEST.UDF. The HWID2D.INI tells the Table Function UDF
where the database called TEST.DBF can be found. The TEST.UDF file
contains information about how to issue the correct SQL syntax.
This service application needs to be called once for every Dbase III
database that should be serviced by the Table Function UDF and that is
found in your environment.
4. Please open the "DB2 Command Center" or "DB2 Befehlszentrale" of the
DB2 UDB application. This application can be found in the DB2 UDB
folder after installation of DB2 UDB.
5. Please open the TEST.UDF file with a editor of your choice. Mark the
lines that can be found around and including the CREATE FUNCTION
statement. There are exact three statements from "CONNECT TO SAMPLE;",
"CREATE FUNCTION ...;" to "CONNECT RESET;". Please copy these three
statements as a whole to the clipboard.
6. Go to the "DB2 Command Center" and paste the contents in the textfield.
If you didn't install the DB2 SAMPLE database please change the name in
the "CONNECT TO" statement to match a DB2 UDB database, that can be
found in your environment.
7. Hit the big button in the upper left corner of the main window. This
activates the "DB2 Command Center". The notebook page will change and
should report a connection and two successful issued statements.
After this DB2 UDB "knows" that there is a Table Function UDF with a
specific name. This Table Function UDF is accessible from the DB2
database, where the CONNECT TO statement was issued against, only. If
you need access to this Table Function UDF from another database
please issue these three statements with a modified CONNECT TO
statement.
8. Go back to the editor and mark and copy the next three statements from
the TEST.UDF file. This time we copy the first "SELECT * ..." statement
and it's surrounding "CONNECT TO SAMPLE;" and "CONNECT RESET;"
statements.
9. Go back to the "DB2 Command Center" and change to the first notebook
page. Copy the clipboard contents to the textfiled and change the
database name if the SAMPLE database can not be found in this DB2 UDB
environment.
10. Again, hit the big button in the upper left corner of the main window.
The notebook page will change again and should report a connection,
three statements from the Dbase III database called TEST.DBF and one
successful issued statement.
If there are only three returned rows of data even if there is more
data in the Dbase III database, you are using the evaluation copy
of DBF2DB2. This evaluation copy limits the count of returned
rows to exact three rows. This limit goes away after registering
DBF2DB2.
11. That's all!
Inside the Table Function UDF
=============================
Overview
--------
The Table Function UDF enables DB2 UDB to read Dbase III databases with
standard SQL statements. This Table Function UDF was written that way,
to service an unlimited count of different Dbase III databases in mind.
If you have a look at the CREATE FUNCTION statement that you copied in the
above "Fast start" section, you'll notice that the CREATE FUNCTION
statement needs to know the names of all columns that should be returned
from the Table Function UDF to the DB2 UDB system. The problem is, that
different Dbase III database have different count of columns and even
different names for the various columns. DBF2DB2 addresses this problem and
resolves it.
The service application (see the Service application section), must be
called from within the SQLLIB/FUNCTION directory for every Dbase III
database that should be serviced by the DBF2DB2 Table Function UDF. The
result is the HWID2D.INI file with an entry for every Dbase III database.
This information is used from the Table Function UDF to find the location
of every Dbase III database.
I addition the service application produces a file with the extension UDF
for every Dbase III database it was called for. This file contains the
correct CREATE FUNCTION statement for this Dbase III database as well as
the correct syntax for some SQL SELECT statements.
This is neccessary because there is a strong relationship between the
CREATE FUNCTION call and every SQL SELECT statement that uses this Table
Function UDF.
- The name of the Table Function UDF is taken from the filename of the
Dbase III database. This name is used by the Table Function UDF to find
the correct section in the HWID2D.INI file. As already stated. This
information is used to find the location of the Dbase III database file.
This is first relationship:
CREATE FUNCTION name --> HWID2D.INI --> Dbase III database file
- The INTEGER input parameter is always the same. In SQL SELECT statements
using this Table Function UDF, this parameter must contain the correct
count of returned columns. The service application always puts the correct
count of columns in the SQL SELECT statements that are shown in the
UDF file. This information is checked by the Table Function UDF during
it's work. If there is a column count mismatch between the Dbase III
database, that was found with the help of the HWID2D.INI file, and the
INTEGER parameter, an error is returned. This is the second important
relationship:
INTEGER --> count of columns in RETURNS clause --> Dbase III database
Notify DB2 about Dbase III databases (CREATE FUNCTION)
------------------------------------------------------
Before a Table Function can be used it must be registered with every
database where the Table Function UDF is required. This is done with the
CREATE FUNCTION call. This CREATE FUNCTION call is different for every
Dbase III database, because the name of the function must be different
and the columns in different Dbase III database are different too, usually.
The name of the Table Function UDF is taken from the Dbase III database
filename. For example the database TEST.DBF will result in a Table Function
name TEST. This name is analyzed by the Table Function UDF and with this
name a corresponding section entry is searched in the HWID2D.INI file.
This leads to the location of the Dbase III database. The service
application will address this and create a correct CREATE FUNCTION call in
the UDF file for every Dbase III database it was issued against.
A second difference is in different Table Function UDFs. The RETURNS clause
must contain the correct count of columns that are returned from the Table
Function UDF the the DB2 UDB system. In addition the names of the columns
that are to be returned must be there too. This is addressed by the service
application too.
To avoid typing mismatch please use the CREATE FUNCTION call that was created
from the service application in the UDF file, always. This will avoid any
problems that needn't be there.
Use the Table Function UDF
--------------------------
After registering the Table Function UDF with the CREATE FUNCTION call, the
Table Function UDF can be used with standard SQL statements. The service
application creates a set of example SQL SELECT statements for every Dbase
III database it was called for, and it should be called for every Dbase III
database the Table Function UDF should service.
To avoid typing mismatch please use the SQL SELECT statements that were
created from the service application in the UDF file, always. This will
avoid any problems that needn't be there.
Service application
===================
With the Table Function UDF you receive a service application called
HWID2DO.EXE (for OS/2) or HWID2DN.EXE (for NT). This application does several
things.
The HWID2D.INI file
-------------------
First of all this application has to maintain a central INI file called
HWID2D.INI. This text-based INI file contains information, that is used
by the Table Function UDF to find the DBase III databases. For every
Dbase III database that should be serviced by the Table Function UDF,
the service application creates an entry in this INI file. The filename
of the Dbase III database file, without the extension, will be used as
a section name. Within this section the service application will create
two entries with the name DIRECTORY and DATA. For example:
[TEST]
DIRECTORY=C:\SQLLIB\FUNCTION
DATA=TEST.DBF
The Table Function UDF needs these entries to find the way to the
Dbase III database files. Every Dbase III database, that you want to be
serviced from the Table Function UDF needs a similar entry, with
different values of course. The Table Function UDF takes the function
name during SQL statement invocation, to find the correct section.
The *.UDF file
--------------
In addition the service application creates an UDF file for every Dbase III
database it was called for, and it should be called for every Dbase III
database the Table Function UDF should handle.
The service application takes the name of the Dbase III database filename
and creates a new file with the same name as the Dbase III database filename
without the extension DBF but with the extension UDF. For example: If the
service application is called with the Dbase III database filename TEST.DBF
and new file called. TEST.UDF is created.
This file contains the correct CREATE FUNCTION call and a set of example
standard SQL SELECT statements for the Dbase III database.
Calling the service application
-------------------------------
The service application should always be called from the SQLLIB/FUNCTION
directory of you DB2 UDB system. That way it is guaranteed that the service
application can enter the correct location of the Dbase III database file in
the HWID2D.INI file.
This is the calling sequenz for the service application:
On OS/2: HWID2DO.EXE -dDB [-fUDF] [-sSPECIFIC]
On NT: HWID2DN.EXE -dDB [-fUDF] [-sSPECIFIC]
The paramater -d is required and should contain the full qualified
drive/path/filename combination of a Dbase III database. For example:
On OS/2: HWID2DO.EXE -dC:\SQLLIB\FUNCTION\TEST.DBF
On NT: HWID2DN.EXE -dC:\SQLLIB\FUNCTION\TEST.DBF
As an optional paramater it is allowed to overwrite the UDF filename.
This is not required usually. But if there are two Dbase III databases with
an identical filename, it is required to use a different and unique UDF
filename for each of this UDF files for both Dbase III databases. If there
is need to overwrite the UDF filename, you need to overwrite the -s
parameter too. For example:
On OS/2: HWID2DO.EXE -dC:\SQLLIB\FUNCTION\TEST.DBF -fMYTEST.UDF -sMYTEST
On NT: HWID2DN.EXE -dC:\SQLLIB\FUNCTION\TEST.DBF -fMYTEST.UDF -sMYTEST
As a second optional paramater it is allowed to overwrite the specific
filename. This is not required usually. But if there are two Dbase III
databases with an identical filename, it is required to use a different
and unique UDF filename for each of this UDF files. If there is need to
overwrite the specific name, you need to overwrite the -f parameter too.
For example:
On OS/2: HWID2DO.EXE -dC:\SQLLIB\FUNCTION\TEST.DBF -fMYTEST.UDF -sMYTEST
On NT: HWID2DN.EXE -dC:\SQLLIB\FUNCTION\TEST.DBF -fMYTEST.UDF -sMYTEST
Some SQL examples
=================
This section contains some example SQL statements, that show the usage
of the Table Function UDF. It uses the test database that comes with this
package. If you don't have the SAMPLE database of the DB2 UDB package,
please change the name of the database in the CONNECT TO statement to
an existing database.
These are the SQL statements that are created from the service application
for the test database. You will find these examples in a file with the name
of the Dbase III database file with the extension *.UDF always. The service
application guarantees that you will always use the correct syntax.
Query all rows
--------------
CONNECT TO sample;
SELECT *
FROM TABLE (TEST(6)) AS T;
CONNECT RESET;
Query one column of all rows
----------------------------
CONNECT TO sample;
SELECT NAME
FROM TABLE (TEST(6)) AS T;
CONNECT RESET;
Query a selected row
--------------------
CONNECT TO sample;
SELECT *
FROM TABLE (TEST(6)) AS T
WHERE NAME='Wilhelm';
CONNECT RESET;
Query the number of rows
------------------------
CONNECT TO sample;
SELECT COUNT(*)
FROM TABLE (TEST(6)) AS T;
CONNECT RESET;
Create a DB2 table and copy the complete Dbase III database content
-------------------------------------------------------------------
CONNECT TO sample;
CREATE TABLE TEST (NAME VARCHAR(64),
PRENAME VARCHAR(64),
ZIP INT,
BIRTH DATE,
MARRIED CHAR(1),
NOTE VARCHAR(512));
INSERT
INTO TEST SELECT NAME,
PRENAME,
ZIP,
BIRTH,
MARRIED,
NOTE
FROM TABLE (TEST(6)) AS T;
CONNECT RESET;
Return codes
============
The Table Function UDF returns well documented return codes for the various
exceptions that may happen.
00000 SQLUDF_STATE_OK
---------------------
Everything's ok. You won't see this code because it's translated by DB2 UDB
in normal no-error condition.
02000 SQLUDF_STATE_EOF
----------------------
There are no more rows in the Dbase III database. This error is recognized
by DB2 UDB and transformed in a no-error condition. This is the usual end of
the Dbase III database scan.
38600 SQLUDF_STATE_DBFCLOSE
---------------------------
The Dbase III engine could not close the Dbase database. This error will
not happen very often.
38601 SQLUDF_STATE_DBFFLDCOUNT
------------------------------
The Dbase III engine reports that it can't read the count of columns in the
Dbase III database. This is a serious error. It might be possible that the
Dbase III database is corrupt or is no valid Dbase III database.
38602 SQLUDF_STATE_DBFFLDNDECIMAL
---------------------------------
The Dbase III engine reports that it can't read a decimal value in the
current row of the Dbase III database. This is a serious error. It might be
possible that the Dbase III database is corrupt or is no valid Dbase III
database.
38603 SQLUDF_STATE_DBFFLDNGETCHARACTER
--------------------------------------
The Dbase III engine reports that it can't read a character value in the
current row of the Dbase III database. This is a serious error. It might be
possible that the Dbase III database is corrupt or is no valid Dbase III
database.
38604 SQLUDF_STATE_DBFFLDNGETDATE
---------------------------------
The Dbase III engine reports that it can't read a date value in the
current row of the Dbase III database. This is a serious error. It might be
possible that the Dbase III database is corrupt or is no valid Dbase III
database.
38605 SQLUDF_STATE_DBFFLDNGETLOGICAL
------------------------------------
The Dbase III engine reports that it can't read a logical value in the
current row of the Dbase III database. This is a serious error. It might be
possible that the Dbase III database is corrupt or is no valid Dbase III
database.
38606 SQLUDF_STATE_DBFFLDNGETMEMO
---------------------------------
The Dbase III engine reports that it can't read a memo value in the
current row of the Dbase III database. This is a serious error. It might be
possible that the Dbase III database is corrupt or is no valid Dbase III
database.
38607 SQLUDF_STATE_DBFFLDNGETNUMERIC
------------------------------------
The Dbase III engine reports that it can't read an integer value in the
current row of the Dbase III database. This is a serious error. It might be
possible that the Dbase III database is corrupt or is no valid Dbase III
database.
38608 SQLUDF_STATE_DBFFLDNLENGTH
--------------------------------
The Dbase III engine reports that it can't read the length of the current
column of the current row of the Dbase III database. This is a serious
error. It might be possible that the Dbase III database is corrupt or is
no valid Dbase III database.
38609 SQLUDF_STATE_DBFFLDNTYPE
------------------------------
The Dbase III engine reports that it can't read the type of the current
column of the current row of the Dbase III database. This is a serious
error. It might be possible that the Dbase III database is corrupt or is
no valid Dbase III database.
38610 SQLUDF_STATE_DBFOPEN
--------------------------
The Dbase III engine reports that it can't open the Dbase III database.
This is a serious error. It might be possible that the Dbase III database
is corrupt or is no valid Dbase III database. In addition it might be
possible that the Dbase III database couldn't be found. Please check the
HWID2D.INI entry for this database and execute the service application
again.
38611 SQLUDF_STATE_DBFRECGO
---------------------------
The Dbase III engine reports that it can't move the row pointer in the
Dbase III database. This is a serious error. It might be possible that the
Dbase III database is corrupt or is no valid Dbase III database.
38612 SQLUDF_STATE_DBFRECSKIP
-----------------------------
The Dbase III engine reports that it can't move the row pointer in the
Dbase III database. This is a serious error. It might be possible that the
Dbase III database is corrupt or is no valid Dbase III database.
38613 SQLUDF_STATE_DBFRECTOP
----------------------------
The Dbase III engine reports that it can't move to the top of the Dbase III
database. This is a serious error. It might be possible that the Dbase III
database is corrupt or is no valid Dbase III database.
38614 SQLUDF_STATE_PRFCLOSE
---------------------------
The Profile Manager reports that it can't close the HWID2D.INI file.
38615 SQLUDF_STATE_PRFLOCATE
----------------------------
The Profile Manager reports that it can't read a value in the HWID2D.INI
file. Please check the contents of the HWID2D.INI file. Perhaps it's
corrupt. Please issue the service application again.
38616 SQLUDF_STATE_PRFOPEN
--------------------------
The Profile Manager reports that it can't open the HWID2D.INI file. Please
check the location and the contents of the HWID2D.INI file. Perhaps it's
corrupt. Please issue the service application again.
38617 SQLUDF_STATE_WRONGFIELDCOUNT
----------------------------------
There was found a mismatch between the INTEGER parameter in a SQL SELECT
statement and the RETURNS clause in the CREATE FUNCTION call. Please
execute the service application for this database again and always use
the templates from the UDF file that was created by the service application
for every Dbase III database.
Glossar
=======
In this section you'll find useful information releated to DBF2DB2 and it's
environment.
UDF
---
User Defined Function. They enable customers to write enhancements to the
DB2 UDB engine. One of the three types of UDF is the Table Function UDF.
Contents of the distribution ZIP file
=====================================
In the ZIP file you'll find the following files:
HWID2DO.DLL
-----------
The Table Function UDF Library itself for OS/2.
HWID2DN.DLL
-----------
The Table Function UDF Library itself for NT.
HWID2DO.EXE
-----------
A service application that analyzes Dbase III databases and creates the
correct syntax for the CREATE FUNCTION and SELECT statements. This
application will maintain a HWID2D.INI file where the Table Function UDF
can find the locations of all Dbase III databases that should be serviced.
This is the OS/2 version of this executable.
HWID2DN.EXE
-----------
A service application that analyzes Dbase III databases and creates the
correct syntax for the CREATE FUNCTION and SELECT statements. This
application will maintain a HWID2D.INI file where the Table Function UDF
can find the locations of all Dbase III databases that should be serviced.
This is the NT version of this executable.
HWID2D.INI
----------
A text-based INI file where the Table Function UDF finds the Dbase III
databases it should service.
HWID2D.DOC
----------
The documentation (this file).
README.1ST
----------
Some news around my shareware applications.
TEST.DBF
--------
A test Dbase III database for immediate testing.
TEST.DBT
--------
The memo file of the test Dbase III database.
TEST.UDF
--------
This file was created by HWID2D, the service application, and contains the
syntax of the CREATE FUNCTION and SELECT statements.
ORDER.TXT
---------
A file that contains registration information.
Known problems
==============
This section contains problems that do exist in the current release of
DBF2DB2.
The LONG VARCHAR problem
------------------------
I would like to use LONG VARCHAR fields for MEMO fields because in fact
DBase III MEMO fields can spread over more than one 512 Byte entry. The
IBM DB2 UDB documentation say's that it's possible but the CREATE FUNCTION
statement does not allow LONG VARCHAR fields in the RESULTS clause.
If this changes, either documentation or engine, I will change this too.
This problem is reported to IBM.
Support
=======
To contact me, use any of these:
Internet
--------
Harald_Wilhelm@hawi.de
Fax
---
Germany (0) 221 - 5909 - 199
Mail
----
Harald Wilhelm
Am Scheidweg 60
D-50765 Koeln (Cologne)
Germany