While Sybase never ported their server to OS/2, Oracle did. However after more than 10 years of releasing a server on OS/2, Oracle has dropped OS/2 as a supported platform for both the client and the server. They aren't the only ones: Oracle, Ingres, Informix, Sybase and Microsoft have all dropped OS/2 as a supported platform for either their server and/or their client. (Gupta SQLBase and Micro Decisionware have disappeared from all platforms.) At first glance this seems somewhat depressing but the reality is, from an ODBC standpoint, it doesn't matter one bit. From the client viewpoint, it doesn't matter because:

• Almost all the functionality is at the server anyway; for the most part the client merely transports SQL commands to the database server and retrieves the results sent by the server.
• Communication protocols between the server and the client rarely change; both because there is no need to, and in order to remain compatible between releases.
• Client packages for almost all those vendors (I am still looking for Ingres and Informix - can anyone help me out?) are still available. And OS/2 is so stable from release to release, that the client packages still work.

From the viewpoint of database servers it doesn't matter because:

• Database server technology had evolved to pretty much its highest point about the time most vendors stopped supporting OS/2 for their servers. Or to be more specific: relational database server technology was pretty much all there by the late 1990's. What has happened since then has been object oriented rather than relational, and advances in connecting to databases other than through ODBC, i.e. Java. We'll talk more about that is a second.
• The trend now, especially if you believe IBM, is to serve data via a web server to users whose client is a web browser. Database interface by browser is extremely crude and primitive, both because of the browser and because of the HTTP protocol. It doesn't take much of a database server to work just fine in a browser->web server->database server role.

The point I'm trying to make is that if you are connecting to a database with ODBC, everything you need in the OS/2 world, both server and client, already existed by the late 1990's. But what about object oriented - the "universal database" trend in Oracle, DB2 and Informix that has happened lately; and what about the Java stuff?

Object oriented, or rather object/relational features put into databases in the last few years are truly a strange thing. These features are a response by the established relational database vendors to try and circumvent any market advantage that the new object oriented databases might have. Oracle, DB2 and Informix all added some types of "object oriented" features to their databases and then renamed the databases as "universal databases" - meaning I suppose that they can handle everything in the universe. The strange thing about objects in SQL databases is that they aren't relational. To quote from Database - Principles, Programming and Performance by O'Neil and O'Neil

"... the object-relational model allows a column value (on a single row) to contain a set (or some other type of collection, such as an array) of row-like values. In some systems a single column value can itself hold a table, a feature know as table nesting. In other cases, collections of such objects can be cast to a table, so that SQL retrieval concepts will work on the collection. What this all adds up to is the most extreme possible departure from the first normal form rule of the rational model,.... RULE 1: First Normal Form Rule. In defining tables, the relational model insists that columns that look like multi-valued fields (sometimes called repeating fields) or have any internal structure (like a record) are not permitted! A table that obeys this rule is said to be in first normal form. By comparison, the object-relational model breaks the first normal form rule by permitting column values to contain multi-valued fields, structured data values."

(By the way - this is an excellent book it you are interested in reading in depth about databases. The ISBN number is 1-55860-438-3.) What all this means is that ODBC can't work with the "object oriented" data precisely because it is not relational. And neither can JDBC, and neither can any commercial application package you can get that connects to more than one database. Informix, Oracle and DB2 (in version 6) all have added incompatible "object-relational" features to their databases that require you to pick one database and stick to it. The SQL99 (formally known as SQL3) standard is an attempt to try and make common some of the "objectness" of these new "universal database" features, but it will be years before that "standard" is implemented by the various database vendors, if it ever is. (By to way: PostgreSql also has many of those "object oriented" features.)

Java, or the part we are interested in, JDBC, is an attempt to address the fact that accessing databases from a browser stinks. HTTP, the protocol used by web browsers, is stateless; meaning that a browser (and the web server) has no idea (generally speaking) what page you came from and what you were doing before you showed up at the present page. In fact it is also really "connection-less", because a connection is made, and broken, between the browser and the web server each time you are served a new web page. (An enhancement to the HTTP protocol has added "connection caching" which keeps a connection "open" for a period of time, but that is to address the overhead of making and breaking connections rather than persevering "state" information between browser pages.) All this is the exact opposite of what is needed when viewing data in a database. Concurrency and transaction isolation in a database are achieved by isolation levels and locking. Locking implies a "state." If you are accessing the data in a "stateless" connection it is impossible to implement locking. Enter Java and JDBC. Since you can't build a decent user interface to a database using browser/HTTP facilities, you put a Java applet on the web page, and the Java applet connects back to the database via JDBC which doesn't use HTTP. All of that in order to use the browser as a primary user interface.

ODBC is by definition not JDBC - although the two are actually sometimes related: Java/JDBC has a "level" or "type" of connection where Java/JDBC interfaces to ODBC on the client, and ODBC makes the connection to the database and servers the data to Java/JDBC. (Think of the logic of that: you build a Java application that is downloaded from a web server to a client which makes a connection to a database via ODBC - which must already be resident on the client - all to avoid installing the Java application on the client.)

(Despite that fact that using a browser as a primary user interface to any application is a giant step backwards from the user and programmer viewpoint, we will cover serving data via ODBC and a web server to a browser in the next couple of articles.)

Sybase System 10/11/12

(The above paragraph is a work of fiction and any resemblance to real persons, companies. or events is purely coincidental.)

So, what was I saying about Sybase? Oh yeah; Sybase is a database server that runs on Windows NT and various Unix-like boxes, including Linux. Sybase the server is not available for OS/2 but Sybase the client, which allows you to connect to any of their servers, was released by Sybase for OS/2. You will notice I said "was released" - Sybase no longer supports OS/2 as a client platform, but you can find the old client out in the internet at various locations. And like all the other "big" database servers we will look at, the old clients work just fine with the new servers - sometimes even better than the newer releases.

Sybase at one time was marketed by Microsoft and called SQL Server, until Microsoft released their own database called ... Microsoft SQL Server. The MS SQL Server is (or was) very similar to the Sybase product - you could almost call them twins. In fact the Intersolv Sybase ODBC driver reports the database name for Sybase System 10 as SQL Server. When Sybase and Microsoft divorced SQL Server was at version 4.2. Sybase released their own version as version5; Microsoft released their own version as version 6 - after all who wants to buy version 5 when version 6 is available? Sybase, after they realized what Microsoft had done, figured they could play the version number game too, and released their next version as System 10, followed by System 11 and now System 12.

The System 10 client, which is what is available for OS/2, connects just fine to System 11, and probably System 12 although I have not tested this. In fact it is the System 10 client for OS/2 that was bundled by Sybase in their System 11 distribution CD.

Since Sybase is only available as a client on OS/2 we will not spend any time on the features of Sybase; if you are connecting to Sybase from OS/2 it is because you need data contained in a Sybase database.

Installing the OS/2 Client & Configuring ODBC

1. Download the client package and unzip into a temporary directory, and run X:/TEMPDIR/OS2/SETUP.EXE where X:/TEMPDIR is the temporary directory you created.
2. After an initial informational screen you will have the choice of a default or custom install.The instructions assume a custom install.

   

3. Pick the components to install. IBM TCP/IP is the primary choice because you can connect with this protocol to all Sybase servers regardless of the platform; we will assume TCP/IP as the protocol for the rest of the instructions. The option "Update Environmental Variable" should cause the CONFIG.SYS file to be modified, but I had to modify mine manually. Go ahead and see if you are lucky.

   

4. Next you are prompted for the host name of the machine hosting the Sybase Server. The name you enter should also appear in your hosts file or be known to your DNS server. In other words, the name of the host machine (it if it a TCP/IP connection) should be ping-able, meaning you should be able to type ping name from an OS/2 Command Prompt Window and get return packets.

   

5. When the install finishes you will have a Sybase System 10 folder on your desktop that looks like this.

   

6. You must insure that the environmental variable SYBASE is set in your CONFIG.SYS file. It should be set to the directory where you installed the Sybase Client. For instance, if you installed the client to F:\SQL10, then in your CONFIG.SYS file should be a line that says SET SYBASE=F:\SQL10 . We will assume that directory for the rest of the instructions. You also need to add the directory containing the Sybase client DLLs to the LIBPATH statement, so add F:\SQL10\DLL to the libpath.
7. Download the update for the OS/2 client and unzip the update file in the client directory, making sure to preserve the update file's directory structure. The updated files will overwrite the existing files in the directory structure. See See Downloading Updates from Sybase below for help on where and how to get updates.
8. Now you have to configure the client for the server(s) you want to connect to. On the client you will create a "server" for each machine you want to connect to. The "server" is equivalent to DB2's catalog or Oracle's Net Name. A "server" in Sybase amounts to a named connection choice where you have specified a machine hosting the database and the protocol used to get to that machine. Double-click on the icon for SQLEDIT. Click on Edit and then Add Server from the menu.

   

9. You will be prompted for the "name" of the server you want to connect to. This "name" is an arbitrary name you will use when connecting to the server - and when setting up an ODBC connection. If you need to connect to multiple servers the name can be something to help you distinguish the servers. In the figure below I am using the name mrlinux , which is also the host name of the Sybase server machine; but the name you enter does not have to correspond to a TCP/IP hostname.

   

10. You should now have a screen where you can create a "service", i.e. specify a network protocol for connecting to the "server" you created. Click on Edit and Add Service.

    

11. To create a "service" you have to specify the type of connection or service, the network transport and the server machine ID. For client connections the Service Type should be query , Network Driver should be IBM TCP/IP , and the Connection Information should be the IP address and port number of the server machine. The default port number for Sybase server is 4100; separate the IP address and port number with a comma. In the figure above the IP address of the machine I want to connect to is 192.168.32.102. Notice the port number is also specified.

    

12. When you click OK you should see defined a "service" that look like the figure below (except with your server's IP address.)

    

13. We now need to ping the server to make sure our connection information is correct. Start the SYBPING program in the Sybase folder. If you get a message like the one below you will have to adjust a locale setting file; the message is saying that the LANG environmental variable on your machine is set to a value that does not appear in the locale data file. However we can still ping with this error, but you will have other problems if you don't fix it. You may or may not have this problem, depending on what other software you have loaded on your machine.

    

14. The SYBPING screen looks like this. Select the "server" you created (in steps 8-10) and click on PING.

    

15. If you have configured the communications correctly and you can connect to your server you see a message box like the following.

    

16. Now let's go correct the locale problem we saw in step 13; if you didn't see the error message box from step 13 skip this step and the next one. Open a Command Prompt window and type SET LANG to determine the setting for your locale. Note the value that is returned - in this figure the value is EN-US.

    

17. Now we need to edit the LOCALES.DAT file located in the LOCALES subdirectory of the Sybase client directory. You can use EPM or the system editor. Add a line to the [OS2] section with the setting from your LANG environmental variable. The easiest way to do this is to copy the first line in the [OS2] section and change the value to what your LANG setting is. In my case the setting I needed was EN_US.

    

    We are almost done with the client part. If you are running Warp 4, WSeB, or eComStation you will have a conflict between a DLL in the Sybase client package and one used by the OS/2 system; I don't know whether this problem also exists in Warp version 3 or not. The DLL that causes the problem is named LIBCS.DLL, and it appears both in C:/OS2/DLL and in F:/SQL10/DLL (assuming that C: is your boot drive.)

    Aside: OS/2 experiences two problems with DLL's: DLL rot and name conflicts.

    • DLL "rot" is where one or multiple DLLs are used by different applications and one or more of the DLLs get out of sync version wise. Usually this problem is caused because a later (or earlier) version DLL packaged with one of the applications that uses the DLLs will overwrite an earlier (or later) version and cause the application needing the other version to break. Luckily this doesn't happen much with OS/2 because there aren't that many applications that share DLLs, and applications do not update system DLLs in OS/2. Plus IBM's license for its C++ products require developers to rename IBM DLLs they distribute to get around version conflicts.
    • DLL name conflicts are where completely unrelated DLL's from two different vendors just happen to have the same name. This is partly encouraged by OS/2's limitation of 8 character names for all DLL's. The problem happens like this: when a program needs the functions contained in a DLL the operating system searches the directories specified in the LIBPATH statement until it finds the first DLL that matches the name needed - unless the DLL is already loaded into memory or unless the application explicitly loads the DLL itself. If there happens to be another DLL with the same name in some directory specified in the LIBPATH before the directory that contains the DLL your application wants, it will get loaded instead of the one intended and you will get an SYS2070 error message: can't load the program segment. This is because the function or other programmy thingy needed by the application is not in the DLL that was loaded by the system - it was the wrong DLL! You can sometimes get around this problem by starting the application that has the problem with a batch file that specifies a BEGINLIBPATH statement specifying your application's DLL directories; this will cause your application DLL directories to be searched first when run from the batch file. This solution will not always work though, and the Sybase client is an example of when that technique will not work. It will not work with the Sybase client because we have multiple applications using the client and each one would have to be started with its own batch file with the BEGINLIBPATH. Plus some applications, e.g. the ODBC Command Processor, start separate sessions that do not inherit the environment from the starting program - so they use the LIBPATH specified in the CONFIG.SYS instead of any BEGINLIBPATH from a batch file.

    To solve this problem we need to rename the Sybase LIBCS.DLL to some other name, thereby avoiding the conflict with the OS/2's LIBCS.DLL. (Its easier to rename the LIBCS.DLL from Sybase rather than the one from OS2/DLL because we can figure out which files use the Sybase DLL; we might never figure out what files use the OS2 version of LIBCS.DLL). I have uploaded a package called SYBFIX.ZIP which contains a renamed LIBCS.DLL (renamed to U3X2C.DLL) and a modified versions of the various files which use LIBCS.DLL: LIBSYBDB.DLL (Sybase client file), L2SYB12.DLL and IVSYB12.DLL (ODBC drivers). You could also do this yourself using the DLLRNAME.EXE utility from IBM VisualAge C++, or with EPM if you are careful.

18. Rename the file LIBCS.DLL to LIBCS.SAV and rename the file LIBSYBDB.DLL to LIBSYBDB.SAV. Both files are in the F:\SQL10\DLL directory.
19. Download and unzip the contents of SYBFIX.ZIP into your F:\SQL10\DLL directory.

Now lets check and make sure the renamed DLL stuff works and that we can execute something that exercises the client library before we setup ODBC.

20. Start up ISQL from a Command Prompt window. This application is in your F:\SQL10\BIN directory. Start it with: isql -Usa -P -Smrlinux where mrlinux represents the name of the "server" you defined in step 9. You have to specify the type of connection or service, the network transport and the server machine ID. For client connections the Service Type should be query, Network Driver should be IBM TCP/IP, and the Connection Information should be the IP address and port number of the server machine. The default port number for Sybase server is 4100; separate the IP address and port number with a comma. In the figure above the IP address of the machine I want to connect to is 192.168.32.102. Notice the port number is also specified. The -U parameter specifies the user ID, the -P parameter specifies the password, the -S parameter specifies the server to connect to. In the example we give we are using the default system administrator user ID of sa, which has no password. If you are connecting to an established Sybase system the sa user ID may not exist, or may have a different password. You should use the user ID and password assigned to you by the system administrator.
21. Type in ISQL: select * from sysusers and press Enter. On the next line type go and press Enter. You should see data if you are connected correctly. Type QUIT to exit from ISQL.

    

    Now that we know the client works OK we can setup an ODBC driver.

22. If you do not have Lotus SmartSuite installed on your machine you will have to download the file ODBC3_01-BIN_OS2.ZIP from http://venuto.monrif.net/download.htm. I have found the Intersolv version 2.11 Sybase driver to have a few problems, so I recommend the version 3.01 Intersolv driver instead of the version 2.11 driver. Use the ODBCINST.EXE program to install the driver; see last month's article for info on where to get the driver and how to install it. Also note - since the driver in ODBC3_01-BIN_OS2.ZIP is an ODBC version 3 driver you will also need to have the ODBC version 3 Driver Manager installed; this is included in the same file if you need it.
23. After the driver has been installed, copy the file IVSYB12.DLL from the F:\SQL10\DLL directory to your ODBC driver directory. If you have Lotus SmartSuite and it's ODBC drivers installed you will have to copy the file L2SYB12.DLL . These files are modified copies of those DLLs which call the U3X2C.DLL instead of LIBCS.DLL (from the rename DLLs steps 18-19 above.) The modified versions of L2SYB12.DLL and IVSYB12.DLL are included in SYBFIX.ZIP and were unzipped in the F:\SQL10 directory in step 19.
24. Now we will create an ODBC Data Source for the Sybase server we want to connect to. Open the ODBC Administrator and click on the add button. Select the Sybase driver from the list.

    

25. Fill in the settings on the Setup page. The server name is the name you used when you created a named "server" in step 9 above. The database name is the database on that server you want to connect to. Pubs3 is a sample database included on the Sybase distribution CD. If you do not specify a name you will connect to the Master database on the server.

Success! We can now retrieve data via ODBC in an OS/2 application. The figure below shows data from the authors table from the pubs3 sample database in Lotus Approach.

Installing the WinOS2 Client & ODBC

The Windows 3.1 client that comes in Sybase Professional package for Sybase does not install in a Windows 3.1 machine - or at least a WinOS2 session. (I doubt seriously if it installs on a regular Window 3.1 machine either.) I could only get it to install by running the install program from a Windows NT machine and setting the destination for the installed files to my OS/2 machine; this method also works from a Windows 98 machine but it is not the most elegant of methods. I then discovered the file SYBWIN16.ZIP which is an image of the Sybase Windows 3.1 client package in a zip file, which turned out to be an easier install than trying to install from the CD. The SybWin16.zip package does not have the additional products that come on the Sybase Professional CD like Infomaker (a report writer), Sybase Central, etc.

1. Make a directory on your hard drive where you want the Windows client to live. We will use F:\SQLW10 in the rest of these instructions.
2. Unzip the SybWin16.zip file in that directory, making sure to preserve the directory structure in the zipped file.
3. Now the hard part: you must manually make a "program group" (or folder) and add icons for each program/utility that is part of the package in Windows Program Manager. At a minimum you want to make program items for SQLEdit, wSybPing, and wISQL; other programs/utilities you might be interested in are:
• wDefCopy (wdefncpy.exe) - copies database definitions from one database to another,
• BulkCopy (wbcp.exe) - copies data from one database to another or a file,
• DLLversions (wdllvers.exe)- displays information about loaded DLLs, including which version the DLL is, in addition to loading and unloading of DLLs.

To create a program group (folder) in Program Manager use File - New and select Program Group. To create program icons in the folder use File - New and select Program Item; fill in the name of the item, the path and name of the executable; the files are located in the F:/SQLW10/BIN . My folder looks like this.



4. Edit your AUTOEXEC.BAT file and add the F:/SQLW10/DLL directory to your path statement. You may have to make a separate AUTOEXEC.BAT file for your Sybase client if your path statement is too long, since Windows will only recognizes something like 60 or 128 characters in the path statement. I have an AUTOEXEC.SYB file on my machine and a Win-OS/2 Window program icon which uses that file as AUTOEXEC with the Sybase specific path and environment variable.
5. Add the environmental variable SYBASE to your AUTOEXEC.BAT file, as SET SYBASE=F:\SQLW10

   Now restart Windows, or the new Window icon if you made a separate AUTOEXEC.BAT file. We must configure the Sybase Windows Client just like we did the Sybase OS/2 client.

6. Start the SQLEDIT program in Windows. You will see a screen like this.

   

7. Type in a name for your Sybase server in the Input Server Name box; remember this is an arbitrary name to use for referring to your server. I suggest you use the same name you used setting up the OS/2 client (step 9 in the OS/2 Client section). Press the Add button.
8. On the right side of the screen select: Query as the Service Type, Win3 as the Platform, and WNLWNSCK as the Net-Library Driver. In the Connection Information/Network Address box enter the IP address of the Sybase server, with the port number separated by a comma. 4100 is the default port number for Sybase. Press the Add Server button.
9. Start the wISQL program. Click on Connect and fill in the parameters in the Connect dialog box. These parameters should be the same as you used when you connected the OS/2 client.

   

10. Enter a SQL select statement and press Ctrl-Enter. You can use the authors table in the Pubs3 database, or if you are connecting to the Master database you could try SELECT * FROM SYSUSERS. If you see results then you have successfully set up the client.

    Now install the Sybase Win16 ODBC driver. This is available from the www.sybase.com\download - see See Downloading Updates from Sybase below for instructions on how to get the file.

11. Unzip the ODBC driver file you downloaded into a temporary directory.
12. Start Windows and from the Program Manager menu click File - RUN and enter SETUP.EXE from the temporary directory where you unzipped the ODBC driver file.
13. Sybase is distributing an ODBC driver made by Intersolv. The Intersolv install program will install the Sybase ODBC driver and the Windows ODBC Driver Manager and Administrator. The Install Directory is where some accessory/utility programs are installed; the Driver Install Directory is where the Sybase ODBC driver will be installed. It is also where the ODBC Driver Manager and Administrator will be installed, along with a license file. You can make those the same directory if you want.

    
    [Click image to view full size.]

14. Once the ODBC install program is finished, you have to create an ODBC Data Source for accessing the Sybase driver. Start the ODBC Administrator and click on the ADD button. Select the Sybase driver and click on OK.

    

15. The setup screen is similar to the one for OS/2. Click on the Advanced button in order to specify a database.

    

[Click image to view full size.]

Downloading Updates from Sybase

1. Go to www.sybase.com/download to get the upgrades.
2. On the left side of the screen you will see EBFs/Upgrades - click on that.
3. Now you will see a screen with a drop-down towards the top labeled Timeframe - select ALL. Well actually you don't have to do that to get the products that we need for this article, but doing that will increase the number of products shown so you can see the other stuff Sybase has.

   

4. Now click on the product you want: Open Client for the OS/2 client update, ODBC for the Windows 3.1 ODBC driver. (This is also where you go for SQL Anywhere or Watcom SQL update.)
5. When the screen for the product you selected appears - go to the Timeframe drop down and select ALL again; that will get you the listing for all the updates.

You will have to create a user ID on Sybase's web site in order to be able to download the package. While annoying, it is relatively painless to do.

Sybase Conclusion

Oracle

Oracle has had a server ported to OS/2 since at least 1988. One of Oracle's "claims to fame" was that its database ran on almost every hardware platform that existed; but like other vendors Oracle has stopped supporting a number of platforms, including OS/2. The last version of Oracle Server released for OS/2 was version 7.3; version 9 of the server was recently released, so the OS/2 side is 2 major releases behind. Oracle no longer ports to OS/2 and encourages their customers to migrate to other platforms - however customers with a support agreement in place (meaning annual payments) can get OS/2 support. Various version of the OS/2 Oracle server can still be picked up occasionally from eBay. The client, on the other hand, is available from various locations on the internet. This client will connect to Oracle servers from version 6 through 8 - and probably through version 9, although I have only tested version 7.2 and 8.x.

Oracle 7.2 Database Features

Feature	Description
Database Type	Client/Server.
Security	Objects and user. User security can be either integrated with OS/2 or within database.
	yes - although most views with joins aren't update-able.
Referential Integrity	Yes - with cascade deletes, etc.
Object Comments	Yes
Transactions	Yes
Isolation Levels
Deadlock Resolution	Yes
Connect Speed	Average
On Line Backup	Yes
SQL Level	SQL89
Enhancements	Yes
Query Optimizer	Yes
Triggers	Yes - row and statement levels. Can use PL/SQL in trigger body.
Procedure	Yes
Large Table Support	No
Large Row Support	No
Replication	Yes
Data Types	Char, Number, Date, Float, Varchar, Long, Long Raw.
Distributed Transactions	Yes - 2 phase commit
Heterogeneous Vendor Access	No - well not in base product.

Oracle server 7.2 or 7.3 is a very robust, full featured product. It handles distributed processing (that is distributed transactions), replication of data both as read-only and update-able, and can take advantage of multiple processors (multiple CPUs) to handle parallel operations for table scans, sorts, joins, data loading, and indexing.

Oracle has a procedural language that is tightly integrated with its SQL called PL/SQL. This allows complex processing that is not possible with SQL by itself, to be accomplished rather easily. PL/SQL can be used write queries, triggers and stored procedures. It can also be sent via ODBC to the server which will interpret the statement and return the results.

Oracle has rather advanced trigger and stored procedure capability. Triggers can be set at the row level for update, insert and deletes, or at the statement level.

Oracle provides interfaces to C/C++ as either embedded SQL or Call Level Interface (CLI), and Rexx with an interface that is somewhat similar to DB2s.

Specifics on the data types are:

• Char is a fixed length file up to a maximum of 255 characters.
• Numbers are stored either in the Float type or Number type - the number type having a fixed scale. Integers, decimals, smallints are all handled by the number type.
• Varchar is a variable size type up to a maximum of 2000 characters.
• Long and Long Raw types can be up to 2 GB in size, although only one LONG type is allowed per table; Long handles character data, Long Raw everything else.
• Date types handle a date and time, roughly equivalent to the timestamp type in other databases, although the time component will only handle time down to the second. Date type in ODBC can cause some problems - some drivers map the Oracle Date type to the ODBC timestamp type, others map it to the ODBC date type.

Replication

Replication is handled between Oracle databases. Replication can take place on a table level or schema level, and as read-only or update-able. While replication is only supported between Oracle databases, the databases do not have to be running on the same operating system.

Distributed Processing

Distributed transactions can take place across multiple Oracle databases on the same or different operating systems. In addition objects in other databases, on the same or different machines, can be referred to in SQL statements. True distributed processing requires something called 2 phase commit. I had an e-mail from a reader requesting a description about 2 phase commits (although the question was in relation to DB2; so in response to Chris Graham...

Distributed processing is essentially a transaction that takes place on multiple machines. In order for the transaction to be "atomic" all the items in the transaction must either successfully complete or fail as a unit - meaning that it must either successfully commit or rollback on all machines. The problem is that using the normal 3 transaction "states" of active, committed and aborted it is impossible to guarantee an atomic transaction across two machines. For example: a bank transaction that withdraws money from a savings account located in a database on one machine and deposits it in a checking account in a different database located on a separate machine. The saving account machine makes the withdrawal and sends a message to the checking account machine to make a deposit. When the savings account machine receives a message from the checking account machine indicating that the deposit has taken place the saving account machine issues a commit and then sends a message to the checking account machine for it to issue a commit. If the network goes down, or the checking account machine fails before it can issue the commit the saving account machine is now in a fix: it has already committed the change. How can it roll back a change that has already been committed? Other transactions may have taken place after the commit that depended on the committed amount. For distributed processing to work correctly another "state" had to be invented to handle commits that are related and atomic but that happen on different machines.

Instead of a transaction simply going through 3 states a third state was created called prepared . A prepared state is one where the transaction is "hardened" or durable, meaning it can survive a machine crash because it is written to the database log, and can change to either a committed or aborted state.

Now the sequence is: saving account makes withdrawal, enters prepared state, send message to checking account, checking account makes deposit and enters prepared state, checking account sends an OK message to the saving account machine. The saving account machine now commits its transaction and sends a commit message to the checking account machine. If the checking account machine fails before it finalizes the commit or the network crashes, it still has the pending transaction in the prepared state. When the checking account machine is brought back on-line the log is read and the pending transaction completes and sends a message to the saving account machine, which releases the lock and completes the transaction.

The coordination between the saving account machine and the checking account machine is handled by an entity called a transaction monitor. Oracle has a transaction monitor built in, but other companies make transaction monitors, like Tuxedo, that work with various databases.

Installing the Client on OS/2

1. Run the ORAINST.EXE file in the X:\TEMPDIR\OS2\INSTALL directory - X:\TEMPDIR is the temporary directory you unzipped the files into. The install program is a text based VIO type application. After you start the program it will ask you to reboot. This is so that the directory you specified where the Oracle products will be installed is added to the libpath. After rebooting start the install program again.
2. Select Oracle7 Clients.

   

3. Select Application User Client.

   

4. The next screen has the parts of the client you can select. SQL*PLUS is an GUI based SQL editor for entering SQL statements; you can deselect this option if you are only going to use ODBC to access Oracle. SQL*VDM is needed for WinOS2 connectivity. The Oracle Rexx interface installs a package that provides Rexx functions for accessing Oracle data, much like the DB2 Rexx interface; this is not needed for ODBC. The SQL*NET Client and SQL*NET Protocol Adapters packages are required.

   

5. The next screen is where you pick each type of communications protocol you will be using to connect to any Oracle server.

   

6. If you pick TCP/IP this screen will appear where you choose which TCP/IP package you are using. I have only used IBM's TCP/IP - so I cannot speak to the other packages.
7. When the install program finishes you will end up with an Oracle folder on your desktop. You must configure the SQL*NET part to finish the client install. Click on the SQL*NET V2.2 folder.
8. Click on the SQL*NET Easy Configuration icon.

   

9. Select Add Database Alias and click on OK. A "Database Alias" in Oracle Land is the some thing as cataloging a database in DB2 or making a "server" in Sybase, that is: it is making an entry in a list specifying a server machine, database on that server, and protocol used to access that machine. The entry is called a database alias and is assigned any arbitrary name you like, up to 128 characters (I suggest sticking with something closer to 18 characters or so.) You specify this database alias for the Connect or Server Name parameter when you are connecting to Oracle or setting up ODBC.

   

10. Type in the name you want to know the database by that you are connecting to. In this figure I've chosen to call the database oraos2 . You can use any name you want, up to 128 characters - be reasonable.

    

11. Select the communication method to use - the protocol you select must be enabled on the server you want to connect to; in the figure below I am using TCP/IP - I have better luck with TCP/IP than NetBios or NamedPipes.

    

12. Enter the TCP/IP address of the machine hosting the database. Also, you must enter the name of the database on that machine. You may have to inquire of the database administrator what the name is. For V 7.x databases the default name is ORCL.

    

You can find the name of the database in the Oracle "listener" configuration file, at the bottom of the file. The file is called LISTERNER.ORA and is usually located in ORAOS2\NETWORK\ADMIN for OS2 and Oracle\Ora81\NETWORK\ADMIN for NT. The database name is the SID= parameter. The figure below shows a listener.ora file for NT. for Oracle Server v 8.1.5.

This figure shows the listener.ora file for OS/2. Both files show an installation with only one database on the machine. If there are multiple databases on the machine they will show up as multiple sections in the "SID_LIST=" section of the file.

Installing OS/2 ODBC

Once you have installed the driver you must create a ODBC Data Source in order to connect via ODBC.

1. Start the ODBC administrator and click the ADD button.
2. Select the Oracle ODBC driver and click on OK. We will use an Intersolv v 3.01 driver in the examples below.
3. Fill in the Data Source Name and Description. The Server field is where you put in your SQL*NET name you created in step 10 above. If you want to connect to a server located on the same machine (Oracle calls this a "local server"), then leave the Server field blank.

   

4. Go to the Advanced page and click on the Catalog Comments check box. This tells the ODBC driver to get the comments from the Oracle catalog (which is should do anyway.)

   

5. Click on OK. You're done.

Installing the Client on WinOS2

The Oracle WinOS2 client does not include an ODBC driver, unfortunately. There is the file ORACAE3.ZIP on Hobbes but I could not get the installer to work on my machine. Oracle Windows ODBC drivers however are available from a number of other sources, including Microsoft.

Oracle Summary