vsbabu.org

This one is for bookmarks

It started 3 days ago and discussion is still going on. Someone posted to comp.databases.oracle (and 3 others), a small note with title Company thought DB2 better than Oracle. He concluded that it may take 4-5 years for DB2 to reach Oracle standard.

If you’ve abundant free time, here is the link to Google archives.

I have zero experience with DB2; from Andy Todd’s blog, it has some annoyances:

• It is not possible to make a NOT NULL nullable.
• Foreign key constraints cannot be disabled, they should just be dropped.
• You can’t change the name or data type of columns within DB2 either. With the exception of increasing the length of VARCHAR columns that is.
• You can’t drop a column from a table

Not good! Looking back after working with Oracle since 1995, I have had only one major issue with it; where I had dreamed of an easy way out!

It happened in 1997, while using Oracle 7.3. The problem was to rename a column (there was a typo in the DDL to alter the table) in a production database table, that was at the center of ERD! I had to create some SQLs that generated other SQLs to disable relevant constraints across the database, then create a temporary table with proper column names, copy the data over, drop the original table, re-create it from temp table, drop temp table and then enable the constraints. It took about 12 hours - luckily, this was an OLTP system with virtually zero activity on weekends. DevX has an article (May 2002) on renaming a table column without any data manipulation.

Off the top of my head, here are the features I would love to have in Oracle.

• SQL*Plus behaving in every environment with command history like shell. The best I’ve seen is HP-UX’s ied command. Can HP open source and port it? Please?
• Unbuffered DBMS_OUTPUT. Several years ago, I solved this by writing a PL/SQL package using DBMS_PIPES to log messages to a pipe. A C program listened to the pipe to collect the messages, processing it as needed. Now, with Java stored procedures a reality, there is Log4PL/SQL; seems like a good option. Note to self: Check out PLNet.org - PL/SQL code repository.
• Stored procedures in Python. I don’t know the technical details of the implementation of PL/SQL, but it might make very good business sense for Oracle to publish an API that can be used to add plugins that allow stored procedure development in different languages.
• Light weight forms deployment server. Now, it is possible to service forms using 9iAS (PDF).

1. I'm not quite sure what you're wanting on the SQL Plus point, but if its decent command-line editing, then you're looking for gqlplus. It seems to be a readline-wrapper around sqlplus that adds completion, history and all that other stuff sane people expect from a command interpreter. Hope that helps.

   Posted by: Adam Keys on September 14, 2003 08:24 PM

2. The other thing I don't like about DB2 is lock promotion. I didn't realise what a favour Oracle were doing to us when they did away with it in Oracle 7. In DB2 its very hard to determine when a number of row locks are going to be promoted to a full table lock - but you certainly know about it when it happens.

   As for your wish list items, TOAD seems to be the SQL*Plus replacement of choice, although history in SQL*Plus would be good.

   Stored procedures in Python are unlikely to be coming our way any time soon, although this is the point to put in the obligatory mention of Jython.

   Posted by: Andy Todd on September 15, 2003 05:19 AM

3. GQLPlus is exactly what I am looking for. Still, the only thing I miss in Linux after moving from HP-UX is HP's ied utility. Just call any command line program that reads stdin, like 'ied program' and you get full VI editing on history on what you type into the program.

   I use TOAD once in a while, but prefer SQL*Plus overall. Atleast, it helps me remember the usual V$ and user_* objects in the data dictionary.

   Posted by: Babu on September 15, 2003 06:08 PM