Re: Questions regarding port of Sybase to Oracle

A copy of this was sent to chris_at_infinitum.madhouse (Chris J/#6) (if that email address didn't require changing) On 29 Jan 2000 14:41:02 -0000, you wrote:

>Hiya,
>
>Forgive what may seem an ignorant question, but I have no knowledge
>of what Oracle can offer to port what is a relatively complex system
>from Sybase. Currently, we're doing an evaluation of the amouont of
>effort involved in doing a port.
>
>The system makes heavy use of Sybase DB-Library and Open Server applications
>to interface front ends (such as Unify Vision) to OS level functions
>(eg: email). The system is essentially a fault collection and distribution
>system for a SCADA system.
>
>What I would like to know is:
> - Is there anything like Sybase Open Server for Oracle? This is
> a central part of the entire system; if it is absent,
> we may have to redesign large chunks of the applications.

If you use the open server to create stored procedures that do other things, then yes. Oracle supports the concept of an external procedure. external procedures are functions written in C (you put them into a .so/.sl file in Unix, a .dll on NT) that are callable from PL/SQL (our stored procedure language). I have, for example, written a set of tcp/ip external procedures that allow plsql to open sockets and to network stuff. This can be done from triggers, stored procedures, etc and are callable by the client as if they were just a normal stored procedure.

> - I'm assuming there is something similar to DB-Library. Is it
> simple to move from DB-Library to the Oracle version. We
> make use of registered procedures, and other advanced
> parts of DB-Lib.

Oracle has a call level interface call OCI. it is different from dblib (as you would expect). You can read about OCI on http://technet.oracle.com/ under documentation. you need an account but they are free and you can sign up right away.

the concept of a registered procedure is supported as well. the external procedures or java stored procedures have a plsql binding statement that maps the SQL types to C or Java types -- thats what we use to register a procedure. once registered, they behave just like a stored procedure would

> - In Dejanews, I've read that Oracle can't do full online database
> dumps, but it can do "tablespace" dumps. As we make once
> daily DB-Dumps and hourly transaction dumps to keep a
> remote standy system up to date, would tablespace dumps
> suffice? What is a tablespace, and how does it relate
> to the database?

different concepts here. A sysbase database is similar to (but not identical to) an Oracle tablespace. In Oracle, you have 1 database many tablespaces. You will use schemas to mimick the namespace you get with Sybase databases.

Yes, you can do full online database dumps (we call this a hot backup). In Oracle we typically do this a tablespace at a time. A database is made up of one or more tablespaces. A tablespace contains full objects such as a TABLE or an INDEX or a PARTITION of a table. A tablespace is made up of one or more FILES. You would "alter tablespace begin backup" to put a tablespace into backup mode. you can still fully read and write this tablespace and all objects in it during this process. When the tablespace is in backup mode, you can copy all of the files elsewhere. You can put all tablespaces into backup mode (you normally would not, you would do a tablespace at a time) to do a 'database dump'. Once you are done copying the files, you would issue an "alter tablespace end backup".

In Oracle, you do not "dump transaction logs". We operate in a more automated mode where as we use log files like a stack -- not like a heap in Sybase. We have 2 or more log files allocated to each database. As a log file fills (we only write to log files, never read them except during recovery), we move to the next log file and another process ARCH will pick up and copy the full log file -- allowing it to be used again later. ARCH can even automatically forward this logfile to another database where it is applied to another database to keep a remote standby up to date. This is pretty much all automated and part of the database. You would control how far out of sync these 2 databases are (the primary and the standby) by controlling the size of the oneline redo log files. If you have 5, 5 meg redo log files -- your standby will be typically "5 meg" behind or there abouts (depends on how fast you fill up redo and your network and so)

> - I'm assuming most of Sybase's Transact-SQL can go over without
> too many problems. Are there any caveats to watch out
> for?
>

goto technet and there are some conversion utilities. There are differences (big ones) between the languages. the conversion utilities go over them and help move them.

>Some further information to help understand the concepts (for those that
>may not have come across Open Server before now). Open Server is essentially
>a C API that allows you to write your open database server, than can handle
>very specialist stuff, act as a gateway, and allow you to do all sorts of
>other advanced things.
>
>We're using at a basic level: as faults come into the system, they populate
>one table, that logs the change of state of each point. Every time a point
>comes in, it fires a trigger on the table, that eventually does:
> exec SERVER_NAME...t_COS_ins @mp, @state
>
>Now, the server given by SERVER_NAME is an Open Server application, written
>purely in C, that contains "registered procedures". A registered procedure
>looks, at the SQL level, like a normal stored procedure. But it isn't,
>being coded external of the database, within its own server application.
>This allows us to pass incoming faults into a C program without the C
>code having to poll the table for changes. On this table, polling
>would be ineffecient and slow: we'd want to see the change immediately
>it comes in. Once the registered procedure has been called, then that
>passes it to other parts of the system, does various checks to see what
>the fault actually means, and notifies workstations that the fault has
>come in (using DB library functions to call registered procedures in a
>couple of WinNT apps).
>

This is 100% doable in EXTERNAL procedures or even just writing a java stored procedure.

>I hope the above description makes some sort of sense. The Oracle web page
>didn't seem to offer any help, and phoning Oracle to get further information
>hasn't resulted in anything useful.
>
>If anyone could provide any information or pointers, it would be most
>welcome.
>
>Thanks in advance,
>
>Chris...

-- 
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
 
Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation