Results 1 to 4 of 4
  1. #1
    Join Date
    May 2003
    Location
    Phoenix, US
    Posts
    39

    Unanswered: Database Architectures

    Hi,
    I just put the different database architectures(UDB, Oracle & Sybase)
    in a single DOC and tried to map against each of these Databases.
    This would be useful for any DBA working on any of these databases
    to understand the other databases easily by mapping.

    Pls go through this DOC and let me know your comments on this .


    Thanks
    Sateesh
    Attached Files Attached Files
    OCP,IBM UDB,SYBASE DBA
    TCS, BANGALORE, INDIA

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Couple of comments:

    1. UDB is no longer the unique name of the DB2 UDB for Linux, UNIX, and Windows product. The DB2 UDB name also applies to other DB2 platforms including DB2 UDB for OS/390. If your analysis only applies to the DB2 UDB for Linux, UNIX, and Windows product, then it might be a good idea to state that.

    2. DB2 has the ability to have pre-compiled SQL that is embedded in supported application programming languages. This provides for an optimized access plan that is completely determined and compiled at bind time (which is usually done at the same time the program is compiled). This can speed up OLTP processing and allow for more extensive SQL optimization, since more time can be spent on the optimization process during the bind without affecting run-time performance.

    The importance of compiled SQL at compile/bind time has become somewhat less important with the increasing use of dynamic SQL in transaction processing with JDBC and other CLI interfaces in DB2, but can be an advantage with C++ and other DB2 supported programming languages that can allow embedded SQL.

    To alleviate the problem of determining the access path each time an SQL statement is submitted, other database products use cached access plans that may be re-used in certain circumstances. DB2 also has added the ability to reuse cached access plans to its product, while retaining support for embedded SQL in programming languages.

    With SQL query front-end products, the use of dynamic SQL is not a significant performance factor. However, DB2 is generally recognized to have a more sophisticated query optimization process than most other products.

    3. There are differences in the locking mechanism between DB2 and Oracle (I don't know about Sybase). DB2 stores its lock information in a separate part of memory dedicated to the locklist. Oracle stores lock information by writing the lock data to the data page of the row being locked. Whether there is a performance penalty may depend on whether the Oracle lock information gets written to disk or if it resides in memory with the rest of the data page, but there may be extra CPU time used to make room on the data page for the lock information.

    IBM claims that its method (separate memory address space for locklists) results in more efficient processing of locks than Oracle can achieve, especially if the number of locks gets large and Oracle runs out of space on the data page for storing lock information.

  3. #3
    Join Date
    May 2003
    Location
    Phoenix, US
    Posts
    39
    Thanks for your comments Marcus..
    I'll add this as part of the Document
    OCP,IBM UDB,SYBASE DBA
    TCS, BANGALORE, INDIA

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Thanks sateesh for your document ... I'm sure many will find it very useful ...
    Thanks Marcus for adding your expert comments ..

    Sathyaram

    Originally posted by msateeshkumar
    Thanks for your comments Marcus..
    I'll add this as part of the Document
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •