Data Access APIs–Part 1: Fun with UPI

First, I’d like to apologize to our good friend SQLLIB.  Those of you who have been working with the Oracle Database for some time will notice that, while it too is a common data access library, I’ve omitted it from this series of posts.

No, it’s not because of some personal vendetta against SQLLIB.  In fact, I like SQLLIB (primarily for internals-related reasons), but I’ve excluded it because it’s not a user-oriented data access library and as such, I don’t consider it a first class citizen.  And, for those of you who may find yourself asking, “what the hell is this SQLLIB thing Jonah’s going on about?”, it’s a library commonly used by the Oracle Precompiler suite, not something people develop directly against.  Though, if there’s enough interest, I may do a post on SQLLIB in the future.

Regardless, in this series of articles, I’m going to discuss and demonstrate the evolution of Oracle’s UPI and OCI data access APIs.  The articles are arranged as follows:

  • Article 1: The User Program Interface (UPI)
  • Article 2: The Oracle Call Interface Version 7 (OCI7)
  • Article 3: The Oracle Call Interface Version 8 (OCI8)

But, before we get too deep into UPI, we need to start at the beginning.

What is a Data Access Layer?
A data access layer is a programming interface which allows developers to write programs capable of interacting with a database.  So, whether you’re using ToadSQL DeveloperSQL*Plus, or a custom application, each relies on a data access layer to talk to the Oracle Database.  However, in this set of articles, we won’t be discussing standard data access APIs like ODBC or JDBC.  Instead, we’ll be taking a low-level look at proprietary Oracle APIs.

Oracle-supported APIs can be classified into two sets, public and internal-only.

Public Oracle Data Access APIs
These are APIs which Oracle has documented, supported, and wants customers to use.

Public APIs include:

  • Oracle Call Interface (OCI)
  • Oracle C++ Call Interface (OCCI)
  • Standard APIs (ODBC, JDBC, .NET, OLE DB, …)

Private, internal-only Oracle Data Access APIs
These are APIs which Oracle uses internally, does not provide external documentation for, and does not want customers using for various reasons.

Private APIs include:

  • User Program Interface (UPI)
  • SQL Library Runtime (SQLLIB)
  • Even older stuff than this…

In this article, we’ll focus on the User Program Interface, as it has been the core of Oracle’s data access for some time.

The User Program Interface
While UPI was once a popular internal-only API used within Oracle, it is in a constant state of being phased out in favor of the Oracle Call Interface (OCI).  Now, you may be asking, “why are you doing a blog on something that’s being phased out?”  Well, I’m doing it for three reasons: 1. Over the years, quite a few people have asked me about it, 2. UPI is still in use, and 3. I think it’s good for people to understand the evolutionary progress of how things got to where they are now.

So, how are we going to see some basic UPI in action?  Bring on the example!

A UPI-based Example Program
Because I strongly believe concepts are best demonstrated using an applied method, I’ve taken an old Oracle OCI demonstration program and turned it into a very basic interactive SQL utility that allows you to execute basic SQL queries.  The code kinda sucks because I spent very little time cleaning up the original, merged using a different style, don’t do great error checking, and could do a few things more correctly.  But, it’s still a basic working example.

However, before we get into the fun stuff, we have to deal with the legal issues.

The Inevitable Disclaimer
It must be noted that I haven’t (yet) worked for Oracle, had access to the source code, or can guarantee proper execution of this on your database version or platform.  The following is based on years of personal observation and research and while I’ve tested it on 8i, 9i, 10g, and 11g, I make no guarantees.  THE SOFTWARE AND EXAMPLES PROVIDED ON THIS PAGE ARE PROVIDED “AS IS” AND WITHOUT WARRANTY OF ANY KIND.  THE AUTHOR DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES.  THIS SOFTWARE IS PROVIDED FOR DEMONSTRATION PURPOSES ONLY AND IS NOT ENDORSED, VERIFIED, OR SUPPORTED BY ORACLE CORPORATION.  THIS SOFTWARE SHOULD NOT BE USED IN A PRODUCTION ENVIRONMENT UNDER ANY CIRCUMSTANCES.

OK, now we can get to the fun stuff :-)

Prerequisite Knowledge
This blog entry assumes that you have a basic understanding of the C programming language as well as a rough idea of data access concepts such as parsing, binding, defining, and execution.

OK, let’s start!

Including the Oracle C Data Types
Before we can use UPI, we must first include the Oracle platform type definitions, oratypes.h.  If you’ve done any OCI programming, you’ve no doubt run across this file.  This file, located in $ORACLE_HOME/rdbms/public, can easily be included using:

#include "oratypes.h"                      /* Oracle-declared Platform Types */

Next, we need the C complier to know something about UPI.

Including the UPI Definitions
In order for our program to make use of UPI, we must now include the UPI function prototypes and definitions, which are contained in a file called upidef.h.

While I’ve made several requests for a copy of Oracle’s official upidef.h, they have been true to their internal-only mission and have never graced me with a copy.  Consequently, it has taken me almost ten years to collect and research only a subset of the entire API.  However, looking (optimistically) toward the future, I’d like to avoid changing anything in my code should I eventually get a copy of the real upidef.h (hint, hint).  Accordingly, I’ve named my version of the definitions identically, and included the header file as follows:

#include "upidef.h"                        /* Jonah's Spiffy UPI Definitions */

Now that we’ve included the UPI definitions and function prototypes, we’re ready to start building our program.  The first step is to define an HDA.

Defining UPI’s Host Data Area Structure
Similar to the concept of opaque handles, UPI uses an old technique of having the developer allocate a block of data which is passed back-and-forth to UPI.  Internally, UPI maps offsets within this block to members of a C structure.  As for definition, we’re going to define an array containing 512 elements of type ub1 (unsigned char/uint8_t).  Depending on the platform and alignment-related issues, the HDA structure may also be allocated using an array of ub1, ub2, or ub4 with array-length changes being made accordingly.

static ub1      hda[512] = { 0 };                  /* Host Data Area for UPI */

Defining a Cursor Using UPI
In UPI, cursors are represented simply by a number.  Accordingly, we define a cursor as an unsigned word.

static uword    cnum;                                       /* Cursor Number */

Logon to Oracle
Next, we need to make a connection to the Oracle database.  UPI provides a couple different logon functions, but we’ll use upilog.  upilog will connect to the orcl database and create a session using scott/tiger.  During this process, the HDA structure will be updated to contain some information about this connection.

    if (upilog(&hda[0],
               (text *) "scott/tiger@orcl",
               -1, 0,
               -1, 0,
               -1, UPILFDEF, UPILMDEF))

Opening a Cursor
Now that we’re connected to the server, we need to get ready to process a statement.  However, before we can process a statement, we must open a cursor.  UPI provides the upiopn call for this.

    if (upiopn(&hda[0], &cnum, -1))

Parsing a SQL Statement
Now that the user has passed-in a query for processing, we need to parse the query.  UPI provides several functions for parsing, including the upipse and upiosd functions.  In our example, we’ll use upiosd, which parses the SQL statement and associates it with our cursor.  In our usage, we’re telling UPI to defer the actual parse [using (ub4) 1] until we call a describe/execution function.  Similarly, rather than specific version 6/7 mode, we’re telling the system to parse the statement in standard (default) mode [via (sword) 1].

        if (upiosd(&hda[0], cnum, (text *) sql_statement, -1,
                   (ub4) 1, (sword) 1))

Describing the Select-List
As we’re allowing the user to enter dynamic SQL queries, we need to describe the select-list prior to defining the output variables and fetching the data.  UPI provides several functions for describing the select list, including the upidpr, upidqr, and upidsc functions.  In our example, we’ll rely on upidsc, which returns the maximum size, datatype, column name, length of the column name, maximum display size, precision of numeric items, scale of numerics, whether null values are allowed, and a couple other things.

            ret = upidsc(&hda[0], cnum, pos,
                         &pmxl[pos], &pvll[pos],
                         &perc[pos], &podt[pos], (text *)&coln[pos],
                         &coll[pos], &pmxdisl[pos],
                         &ppre[pos], &pscl[pos],
                         &pnul[pos]);

Defining Output Variables
The next step is to define the output variables for each select-list item in the query.  To define output variables, UPI provides the upidfc, upidfn, and upidfps functions.  In the example, we’ll use upidfn, which is a non-piecewise define for a select-list item by position.  In our call, we’re making sure to pass reference to our indicator value for this item as well.  Failure to do so will result in errors when attempting to fetch a column containing a NULL value.

            if (upidfn(&hda[0], cnum, (sword) i,
                       obuf[i], coll[i],
                       (sword) SQLT_STR, (b2 *) &oind[i],
                       (text *) 0, (size_t) 0,
                       (ub2 *) 0, (ub2 *) 0,
                       (sword) 0))

Executing the Query
Now that we’ve defined our output variables, we’re ready to execute the query.  The order of describe/define/execution can be different in different situations.  Regardless, UPI provides several calls for execution including, upiefn, upiexe, and upiexn.  In our example, we’ll use upiexe.

    if (upiexe(&hda[0], cnum))

Fetching Data
So, we’ve defined our output variables and executed the statement.  If all has gone well so-far, we can start fetching data.  UPI provides several different calls including upifch and upifcn.  We could also use upiefn (execute+fetch), but are instead going to use the simplest version, upifch.  The upifch function performs a row-at-a-time fetch on our cursor.

        while (upifch(&hda[0], cnum) == 0)

Conclusion
This has been a brief example of writing a simple application using Oracle’s UPI data access API.  In the next two articles, you’ll see how this same application can be improved and made more descriptive using OCI7 and the current version of OCI, OCI8.

Full Program Listing
The following is the entire source code to the extremely simple interactive SQL client built using the User Program Interface.  Given that Oracle still considers UPI proprietary, I will not be attaching nor sending my UPI definitions header file, upidef.h.  If you’d like to actually run the program below, you can fairly easily recreate the UPI function prototypes based on the data types and argument ordering provided.

/* ========================================================================= */
/* INTERACTIVE SQL EXAMPLE                                                   */
/* Oracle User Program Interface (UPI)                                       */
/*                                                                           */
/*                                  Jonah H. Harris <jonah.harris@gmail.com> */
/*                                          http://www.oracle-internals.com/ */
/* ========================================================================= */

/* Standard Headers */
#include <stdio.h>                                /* Standard C Input/Output */
#include <stdlib.h>                                    /* Standard C Library */

/* Oracle-specific Headers */
#include <oratypes.h>                      /* Oracle-declared Platform Types */
#include "upidef.h"                                    /* My UPI Definitions */

/* ========================================================================= */
/* << DEFINITIONS >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> */
/* ========================================================================= */

#define MAXATTR             1024  /* Max number of attributes in select-list */

/* ========================================================================= */
/* << STATIC VARIABLES >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> */
/* ========================================================================= */

static ub1      hda[512] = { 0 };                  /* Host Data Area for UPI */
static uword    cnum;                                       /* Cursor Number */

/* ========================================================================= */
/* << FORWARD DECLARATIONS >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> */
/* ========================================================================= */

static void upi_error (void);

/* ========================================================================= */
/* << PUBLIC FUNCTIONS >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> */
/* ========================================================================= */

int
main (int argc, char **argv)
{
    char    sql_statement[256];
    sword   i, pos;
    sword   ret;
    ub1     podt[MAXATTR], ppre[MAXATTR], pnul[MAXATTR];
    ub2     pmxl[MAXATTR], coll[MAXATTR],
            pmxdisl[MAXATTR], pvll[MAXATTR];
    sb1     pscl[MAXATTR];
    sb2     perc[MAXATTR];
    text    coln[MAXATTR][31];
    text   *obuf[MAXATTR];
    sb2     oind[MAXATTR];

    /* Logon */
    if (upilog(&hda[0],
               (text *) "scott/tiger@orcl",
               -1, 0,
               -1, 0,
               -1, UPILFDEF, UPILMDEF))
    {
        printf("Cannot connect as scott. Exiting...\n");
        exit(1);
    }

    /* Open a cursor */
    if (upiopn(&hda[0], &cnum, -1))
    {
        printf("couldn't open cursor!\n");
        upi_error();
        exit(1);
    }

    /* Main loop */
    for (;;)
    {
        ub4 rnum = 0;   /* A generic record number counter */

        printf("\nEnter a query or \"exit\"> ");
        fgets(sql_statement, sizeof(sql_statement), stdin);

        /* If user typed, "exit", bail! */
        if (strncmp(sql_statement, "exit", 4) == 0)
            break;

        /* Parse the statement */
        if (upiosd(&hda[0], cnum, (text *) sql_statement, -1,
                   (ub4) 1, (sword) 1))
        {
            upi_error();
            continue;
        }

        /* Iterate over each attribute in the select-list */
        for (pos = 1; pos < MAXATTR; pos++)
        {
            coll[pos] = sizeof(coln[pos]);

            /* Describe the select-list item */
            ret = upidsc(&hda[0], cnum, pos,
                         &pmxl[pos], &pvll[pos],
                         &perc[pos], &podt[pos], (text *)&coln[pos],
                         &coll[pos], &pmxdisl[pos],
                         &ppre[pos], &pscl[pos],
                         &pnul[pos]);

            if (coll[pos] > sizeof(coln[pos]))
                coll[pos] = sizeof(coln[pos]);

            /* Make sure this string is null-terminated */
            coln[pos][coll[pos]] = '\0';

            if (ret)
            {
                if (ret == 1007)
                    break;
                upi_error();
                continue;
            }
        }

        /*
         * Print out the total count and the names of the select-list
         * items, column sizes, and datatype codes.
         */
        pos--;
        printf("\nThere were %d select-list items.\n", pos);
        printf("Item name                     Length   Datatype\n");
        printf("\n");
        for (i = 1; i <= pos; i++)
        {
            printf("%*.*s", coll[i], coll[i], coln[i]);
            printf("%*c", 31 - coll[i], ' ');
            printf("%6d   %8d\n", coll[i], podt[i]);

            /* Allocate the output */
            obuf[i] = (text *) malloc(coll[i]+1);
            if (obuf[i] == NULL)
            {
                fprintf(stderr, "Failed to allocate %u bytes!\n",
                    coll[i]+1);
                exit(EXIT_FAILURE);
            }

            /* Define the output parameter */
            if (upidfn(&hda[0], cnum, (sword) i,
                       obuf[i], coll[i],
                       (sword) SQLT_STR, (b2 *) &oind[i],
                       (text *) 0, (size_t) 0,
                       (ub2 *) 0, (ub2 *) 0,
                       (sword) 0))
            {
                upi_error();
                break;
            }
        }

        /* Execute */
        if (upiexe(&hda[0], cnum))
        {
            upi_error();
            continue;
        }

        /* Fetch & Display */
        while (upifch(&hda[0], cnum) == 0)
        {
            printf("\n-- RECORD %04u -------------------------\n", ++rnum);
            for (i = 1; i <= pos; i++)
            {
                printf("%s", coln[i]);
                printf("%*c ", 31 - coll[i], ':');
                printf("%s\n", (oind[i]) ? "NULL" : obuf[i]);
            }
        }

        /* Display the summary */
        printf("\n%u record%s returned.\n", rnum, (rnum == 1) ? "" : "s");

        /* Cleanup */
        for (i = 1; i <= pos; i++)
            if (obuf[i] != NULL)
                free(obuf[i]);

    } /* for (;;) */

    /* Close the cursor */
    upicls(&hda[0], cnum);

    /* Logoff */
    upilof(&hda[0]);

    /* Bail! */
    exit(EXIT_SUCCESS);

} /* main() */

/* ========================================================================= */
/* << PRIVATE FUNCTIONS >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> */
/* ========================================================================= */

static void
upi_error (void)
{
    text msg[512] = { 0 };

    fprintf(stderr, "\nOracle ERROR\n");
    upigem(&hda[0], (text *) &msg);
    fprintf(stderr, "%s", msg);
    fflush(stderr);

} /* upi_error() */

UPI is fun! :)

NEXTGRES Gateway: MySQL Emulator for Oracle

So, a few people have asked me what NEXTGRES Gateway is.  My short answer, the ultimate database compatibility server.

Sorry if this blog entry sounds very marketing-oriented, but I’ve been working on this personal project non-stop for the last 8 months and am really excited about it.

NEXTGRES Gateway in a nutshell:

  • Designed to assist in database/application migration
  • Written in C
  • Supports TCP and UDP sockets
  • Runs in multi-threaded or multi-process mode (depending on configuration)
  • Runs on Windows, UNIX, Linux
  • Supports MySQL, PostgreSQL, Oracle, and SQL Server/Sybase (TDS) server-side wire-level protocols
  • Supports MySQL, PostgreSQL, Oracle, and SQL Server/Sybase (TDS) client-side wire-level protocols, as well as ODBC
  • Supports pass-through SQL or syntax translation between MySQL, PostgreSQL, Oracle, and SQL Server
  • Supports native procedural language execution via translation of PL/SQL and T-SQL to C (no C complier is needed on your system though, thanks to TCC)
  • Supports data type conversion to/from MySQL, PostgreSQL, Oracle, and SQL Server/Sybase
  • Supports local statement/result-set caching

In this entry, I’m going to focus on MySQL server emulation.

MySQL Server Emulation
Now that I’ve fully completed the MySQL server emulation component, something I discussed with the Oracle Data Access guys at OpenWorld, here’s a couple examples for you.

Say you have an application that runs on MySQL and you’d like to migrate it to Postgres, but don’t want to do any code changes.  Well, if you’re using fairly standard ODBC/JDBC, you don’t have much to worry about.  But what if it’s a PHP application using the mysql_* calls, or an application using the MySQL client libraries, or a third-party application you don’t have the code for?  The answer is to use NEXTGRES Gateway.

NEXTGRES Gateway allows you migrate your data to another database transparently to the application.  The general process for using NEXTGRES Gateway is as follows:

  • Migrate Data (MySQL to Postgres/Oracle/SQL Server)
  • Shutdown MySQL
  • Start NEXTGRES Gateway in MySQL Emulation mode and point it to the Postgres/Oracle/SQL Server data
  • Test
  • You’re done!

Unlike other databases which claim to be compatible, NEXTGRES Gateway allows you to migrate an application to another database server with no application changes.

In the following example, I’m using the native MySQL client to connect to a PostgreSQL 8.3 database.  It’s important to note that no changes have been made to the MySQL client, it’s just connecting to NEXTGRES Gateway which is emulating the MySQL server by performing SQL syntax and protocol translation to Postgres.

jharris@jharris-desktop$ mysql -A -u root -h 127.0.0.1 pgdb
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.39 (NEXTGRES Gateway 4.2.0.1)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select count(*) from pg_tables;
+-------+
| count |
+-------+
| 51    |
+-------+
1 row in set (0.01 sec)

mysql> select tablename from pg_tables limit 5;
+-------------------------+
| tablename               |
+-------------------------+
| sql_features            |
| sql_implementation_info |
| pg_statistic            |
| sql_languages           |
| sql_packages            |
+-------------------------+
5 rows in set (0.01 sec)

That’s cool and all, but say we want to move our MySQL application to Oracle:

jharris@jharris-desktop$ mysql -A -u root -h 127.0.0.1 oradb
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.39 (NEXTGRES Gateway 4.2.0.1)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select count(*) from all_tables;
+----------+
| COUNT(*) |
+----------+
| 108      |
+----------+
1 row in set (0.03 sec)

mysql> select count(*) from user_tables;
+----------+
| COUNT(*) |
+----------+
| 9        |
+----------+
1 row in set (0.01 sec)

mysql> select user from dual;
+-----------+
| USER      |
+-----------+
| AUTOGRAPH |
+-----------+
1 row in set (0.01 sec)

mysql> select table_name from all_tables limit 5;
+-----------------------+
| TABLE_NAME            |
+-----------------------+
| DUAL                  |
| SYSTEM_PRIVILEGE_MAP  |
| TABLE_PRIVILEGE_MAP   |
| STMT_AUDIT_OPTION_MAP |
| AUDIT_ACTIONS         |
+-----------------------+
5 rows in set (0.02 sec)

For those that didn’t catch it, NEXTGRES Gateway performed a simple SQL translation from MySQL to Oracle syntax on:

SELECT table_name FROM all_tables LIMIT 5;

to

SELECT table_name FROM all_tables WHERE ROWNUM < 6;

If you want to see more, I’ll be happy to demonstrate MySQL, Postgres, and Oracle emulation at theSoutheastern Oracle Users Conference, February 24 & 25 in Charlotte, North Carolina.  I’ll also be presenting my session, “Listening In: Passive Capture and Analysis of Oracle Network Traffic”.  This session is designed to help you diagnose issues with and optimize applications for, the Oracle network protocol.

Added on 2010-01-30 per Baron’s question:

jharris@jharris-desktop$ mysql -A -u testuser1 -h 127.0.0.1 postgres
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.39 (NEXTGRES Gateway 4.2.0.1)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show tables;
+------------------------------------------------------+
| Tables_in_postgres                                   |
+------------------------------------------------------+
| active_locks                                         |
| active_queries                                       |
| information_schema.administrable_role_authorizations |
| information_schema.applicable_roles                  |
| information_schema.attributes                        |
| information_schema.check_constraint_routine_usage    |
| information_schema.check_constraints                 |
| information_schema.column_domain_usage               |
| information_schema.column_privileges                 |
| information_schema.column_udt_usage                  |
| information_schema.columns                           |
| information_schema.constraint_column_usage           |
| information_schema.constraint_table_usage            |
| information_schema.data_type_privileges              |
| information_schema.domain_constraints                |
| information_schema.domain_udt_usage                  |
| information_schema.domains                           |
| information_schema.element_types                     |
| information_schema.enabled_roles                     |
| information_schema.information_schema_catalog_name   |
| information_schema.key_column_usage                  |
| information_schema.parameters                        |
| information_schema.referential_constraints           |
| information_schema.role_column_grants                |
| information_schema.role_routine_grants               |
| information_schema.role_table_grants                 |
| information_schema.role_usage_grants                 |
| information_schema.routine_privileges                |
| information_schema.routines                          |
| information_schema.schemata                          |
| information_schema.sequences                         |
| information_schema.sql_features                      |
| information_schema.sql_implementation_info           |
| information_schema.sql_languages                     |
| information_schema.sql_packages                      |
| information_schema.sql_parts                         |
| information_schema.sql_sizing                        |
| information_schema.sql_sizing_profiles               |
| information_schema.table_constraints                 |
| information_schema.table_privileges                  |
| information_schema.tables                            |
| information_schema.triggered_update_columns          |
| information_schema.triggers                          |
| information_schema.usage_privileges                  |
| information_schema.view_column_usage                 |
| information_schema.view_routine_usage                |
| information_schema.view_table_usage                  |
| information_schema.views                             |
| jhhdemotbl                                           |
| jhhtest                                              |
| pg_aggregate                                         |
| pg_am                                                |
| pg_amop                                              |
| pg_amproc                                            |
| pg_attrdef                                           |
| pg_attribute                                         |
| pg_auth_members                                      |
| pg_authid                                            |
| pg_autovacuum                                        |
| pg_cast                                              |
| pg_class                                             |
| pg_constraint                                        |
| pg_conversion                                        |
| pg_cursors                                           |
| pg_database                                          |
| pg_depend                                            |
| pg_description                                       |
| pg_enum                                              |
| pg_freespacemap_pages                                |
| pg_freespacemap_relations                            |
| pg_group                                             |
| pg_index                                             |
| pg_indexes                                           |
| pg_inherits                                          |
| pg_language                                          |
| pg_largeobject                                       |
| pg_listener                                          |
| pg_locks                                             |
| pg_namespace                                         |
| pg_opclass                                           |
| pg_operator                                          |
| pg_opfamily                                          |
| pg_pltemplate                                        |
| pg_prepared_statements                               |
| pg_prepared_xacts                                    |
| pg_proc                                              |
| pg_rewrite                                           |
| pg_roles                                             |
| pg_rules                                             |
| pg_settings                                          |
| pg_shadow                                            |
| pg_shdepend                                          |
| pg_shdescription                                     |
| pg_stat_activity                                     |
| pg_stat_all_indexes                                  |
| pg_stat_all_tables                                   |
| pg_stat_bgwriter                                     |
| pg_stat_database                                     |
| pg_stat_sys_indexes                                  |
| pg_stat_sys_tables                                   |
| pg_stat_user_indexes                                 |
| pg_stat_user_tables                                  |
| pg_statio_all_indexes                                |
| pg_statio_all_sequences                              |
| pg_statio_all_tables                                 |
| pg_statio_sys_indexes                                |
| pg_statio_sys_sequences                              |
| pg_statio_sys_tables                                 |
| pg_statio_user_indexes                               |
| pg_statio_user_sequences                             |
| pg_statio_user_tables                                |
| pg_statistic                                         |
| pg_stats                                             |
| pg_tables                                            |
| pg_tablespace                                        |
| pg_timezone_abbrevs                                  |
| pg_timezone_names                                    |
| pg_trigger                                           |
| pg_ts_config                                         |
| pg_ts_config_map                                     |
| pg_ts_dict                                           |
| pg_ts_parser                                         |
| pg_ts_template                                       |
| pg_type                                              |
| pg_user                                              |
| pg_views                                             |
| plproxy.cluster_partitions                           |
| plproxy.clusters                                     |
| testtbl                                              |
| utility.index_byte_sizes                             |
| utility.table_byte_sizes                             |
| utility.user_table_sizes                             |
+------------------------------------------------------+
132 rows in set (0.03 sec)

mysql> show databases;
+-----------+
| Database  |
+-----------+
| template1 |
| template0 |
| postgres  |
+-----------+
3 rows in set (0.01 sec)

More info to come!

NEXTGRES Gateway SQL*Plus Teaser…

Do you love SQL*Plus but hate that you can’t use it with other, non-Oracle databases?  Do you wish you could more easily migrate some of your MySQL or Postgres applications to Oracle?  If so, stay tuned, because my next few blog entries are going to show you how to do just that.  But first, I want to throw a little teaser out there for the SQL*Plus crowd…

Let’s face it, SQL*Plus is an awesome tool.  And, it’s a tool everyone who’s had to manage databases from multiple RDBMS vendors wishes they could use against all of them.  Well, if you’re using Postgres, MySQL, SQL Server, or DB2 and are tired of using cheap, JDBC-driven SQL*Plus knock-offs, my next blog entry will show you how to interface the real SQL*Plus with each of those systems using nothing but Instant Client, a couple libraries, and a little ingenuity.

OpenWorld 2009 Recap

Having now had several days to recover from, and reflect on, my trip to beautiful San Francisco for Oracle OpenWorld 2009, I wanted to follow up with a blog post describing my experience.

First, before anything else, I went to In-N-Out Burger where I had my usual Double-Double with fries animal style; oh, how I missed thee.

Next, with the exception of Larry’s keynote on Wednesday, I skipped the rest of keynotes.  While I wanted to attend Michael Dell’s, I was hungry and decided on breakfast at Mel’s Drive-in instead.  Out of coincidence, I ended up sitting next to Mark Prichard, Senior Principal Product Manager for WebLogic at Oracle; we ended up discussing Tuxedo and some of the features Oracle has been adding to it since the BEA acquisition.  A bit later, I visited the Tuxedo booth to learn more and was quite happy to see Oracle putting more resources into developing such a great product as Tuxedo.

Day-By-Day
On Monday, I attended a couple sessions, including one on TimesTen caching for Oracle, as well as the OTN Night event.  At the event, I had a great discussion with several cool Oracle users and, with the exception of a few incorrect answers to questions for “Oracle Jeopardy”, it was a great night.

Tuesday was a busy day.  Surprisingly, during my session I saw former EnterpriseDB colleague, fellowPostgres developer, and new Google employee Greg Stark.  We discussed several different database-related topics during lunch and then attended the Hybrid Columnar Compression session given by Bill Hodak and Amit Ganesh.  Based on the simplified diagram displayed in the session, my guess is thatHybrid Columnar Compression is implemented using some of the in-block table/row directory functionality with the compression unit header containing the number of blocks in the unit as well as pointers to which block(s) within the compression unit contain which fields.  More to come on that topic in a future blog post :-)

Tuesday night, I attended the Pythian Bloggers Meetup event and met fellow bloggers Alex Gorbachev,Markus EiseleRobyn SandsArjen VisserFuad ArshadRaimonds SimanovskisVít Špinka, Steve Lemme, and many others.  Unfortunately, I was unable to meet up with Justin KestelynDoug Burns, and Tim Bray.  After the meetup, a few of us went over to Chevy’s and talked shop for awhile.

Wednesday was similarly busy.  Fortunately, before I left, I was able to meet long-time online friend and colleage Jonathan Gennick.  Jonathan invited me to meet him at one of Mogens Nørgaaard’s Oracle Closed World sessions where I was also able to meet Graham Wood and several other well-known Oracle/OakTable guys.  It was at OCW that I was able to watch one of the best, non-marketing-oriented Cloud Computing presentations given by Jeremiah Wilton.  After the OCW session, I enqueued myself to see Larry’s keynote which I enjoyed.

All in all, I spent a great deal of time talking to some of the developers and technical guys at the Oracle database DEMOgrounds including Stephan HaisleyMark Williams, Advanced Security developers, Advanced Compression developers, Optimizer guys, some of the BerkeleyDB guys, and several members of the Data Access group.  While I primarily visited the Exadata exhibit hoping to meet up with Kevin Closson and Greg Rahn, I was able to have a few great discussions regarding the architecture of Exadata as well as watch that machine perform an amazing amount of data processing in a very short amount of time.

In short, OpenWorld was great!  If you didn’t attend, I would suggest that you buy access to Oracle OpenWorld On Demand, there’s a ton of great content in there and it’s definitely worth it.

Lastly, I’d like to thank Oracle for inviting me to speak and I hope to be back next year!

Distributed Oracle Buffer Cache

If you’ve read EnterpriseDB’s latest press release, you’ll notice a new feature called Infinite Cache.  While it may sound revolutionary, it is not a new technology.  In fact, it was done for MySQL quite awhile ago under the Waffle Grid project.

While I consider this type of caching relatively simple, I don’t want Oracle to be left out.  As such, I’m going to join the fray by throwing my own project, Mercury Distributed Cache for Oracle, into the mix.

Similar to Waffle Grid for MySQL and Infinite Cache for EnterpriseDB, Mercury supplements the Oracle buffer cache with optimized, network-based data block storage to reduce the amount of read-related disk I/O which must be performed when working with large-scale databases.

For the time being, please forward all Mercury-related questions directly to me via email.

Advanced Query Optimization Question

Having now had a couple days to rest and reflect, I can definitively say that SEOUC was great.  I’m really glad I attended, was able to meet a lot of new people, and have some good discussions about problems people faced both technically and given the current economy.  I was also able to get some good insight into other types of Oracle environments and felt the conference was an overall success.

The conference ended with a closing session on the topic of Oracle Exadata Storage Server presented by Oracle Director of Product Management, Charles Garry.  While the slide deck was quite marketing-heavy, Charles did a good job entertaining everyone and answering questions.

After the session, I had a few discussions with people regarding the Exadata architecture and the method by which Oracle Database is able to distribute nodes of a query execution plan directly to the storage server for local processing.  For people who haven’t worked on query optimizers and executors, it seems that this is an area of technical confusion.  Regardless, Charles and I both rode the shuttle back to the airport, which gave me a chance to talk with him about Oracle from both a company and technology perspective.

Also, now that I’m home and have completed my second SEOUC presentation, The Life of a Query, I wanted to follow-up on my experience and ask you a question.

First, the description of The Life of a Query is as follows:

Have you ever wondered what happens when you execute a query?  In this session, we’ll take a walk through the Life of an Oracle Query from beginning to end.  Not only does this help you better understand the various steps in the execution of your query, but it will also give you a good appreciation for the architecture of the Oracle Database server and, in particular, of the query optimizer.  You will gain valuable information that will allow you to solve performance issues and write more efficient queries going forward.  Additionally, I will share some of my insights into the Oracle optimizer as we move through this discussion.  This discussion is also a great refresher for those of you familiar with Oracle concepts and architecture.

In short, this session takes a query and walks through all major components of the Oracle database starting from the client and going through connection, the entire Oracle Kernel stack, and back to the client.  After the session, I received quite a few positive comments from attendees.  Moreover, I found that people are extremely interested in how query optimization works.

While I covered the basics of parsing, query rewrite, and optimization, it seemed that including view/subquery merging, basic algebraic optimization, join permutation, join elimination, and partition elimination wasn’t enough.  Surprisingly, someone even asked to go into more depth on the mathematics behind query optimization; specifically the application of graph theory to plans and the way costing is performed using a graph.  This brings me to my question:

Would anyone be interested in a fairly advanced presentation/article on query optimization?

For comparison purposes, my meaning of fairly advanced is between the level of Jonathan Lewis’ Cost-Based Oracle Fundamentals and not-quite pure math.  If I decide to do it, I’m thinking of presenting it similar to Craig Shallahamer’s based-on-math-yet-practically-applied Forecasting Oracle Performance, describing the math and theory but visually demonstrating how it applies to actual queries.  Thoughts?

Currently At SEOUC

While I had almost missed my flight yesterday, I am now in Charlotte North Carolina attending and presenting at the SouthEastern Oracle Users Conference. My first session, Benchmarking: Tools, Methodologies, and Techniques, was earlier today and I think it went OK. I’ve also attended a couple sessions as well.

Specifically, I was impressed with the keynote from Rich Niemiec (the President of TUSC) this morning. While it was basically a history of Oracle, containing some of the details shared by Larry Ellison himself during the 2007 OOW keynote, Rich was able to add a bit more insight into things due to his past interviews and discussions with old, key Oracle employees such as Bruce Scott. Also of note was a presentation from Karen Morton (Method R Corporation–Think Hotsos), which covered the basics of how statistics affect the Oracle CBO and how to manage them. I found her demonstration, using playing cards, an excellent way to visualize basic query optimization calculations for things such as selectivity; I believe others did as well.

Also attending the conference are the guys from Bizwhazee, with their awesome PL/SQL+Web+Ajax product, TURBOEnterprise. If you want to try TURBOEnterprise, you can download it free for development from their web site. Or, if you just want to check out some of the cool Web 2.0 stuff you can develop in pure PL/SQL, you can play with it online.

Tanel Poder Training in New York

I’m pleased to announce that good friend and fellow Oracle expert, Tanel Poder, will be giving a 2-day special training for NYOUG members on February 3rd and 4th, 2009.  His topic, Advanced Oracle Troubleshooting for DBAs and Performance Engineers, provides Oracle DBAs with an excellent, end-to-end approach for troubleshooting and solving Oracle problems.

Tanel’s extensive experience researching and administering Oracle gives him a unique perspective from which he is able to provide DBAs with a thorough understanding of Oracle as it interacts with the operating system.  Tanel’s approach and examples illustrate how one can easily identify, diagnose, and decipher database issues using utilities and information not available from within Oracle itself.

I wholeheartedly endorse this session and believe that anyone who wants to take their Oracle knowledge to the next level will greatly benefit from it.

Upcoming Conference Schedule for 2009

I have recently been informed that several of my submissions have been accepted for presentation at theSoutheastern Oracle Users Conference and for Collaborate 2009 (IOUG).  As such, my 2009 conference schedule will include attendance and presentation of the following:

  • The Life of a Query (DBA/Developer Track)
    Southeastern Oracle Users Conference–February 25-26, 2009
    IOUG Collaborate 2009 (Session #399)–May 7, 2009

    Have you ever wondered what happens when you execute a query?  In this session, we’ll take a walk through the Life of an Oracle Query from beginning to end.  Not only does this help you better understand the various steps in the execution of your query, but it will also give you a good appreciation for the architecture of the Oracle Database server and, in particular, of the query optimizer.  You will gain valuable information that will allow you to solve performance issues and write more efficient queries going forward.  Additionally, I will share some of my insights into the Oracle optimizer as we move through this discussion.  This discussion is also a great refresher for those of you familiar with Oracle concepts and architecture.
  • Database Benchmarking: Methodologies, Tools, and Techniques (DBA/Developer Track)
    Southeastern Oracle Users Conference–February 25-26, 2009
    IOUG Collaborate 2009 
    (Session #107)–May 4, 2009
    While we’re all familiar with the term benchmarketing, how do you tell fact from fiction? Planning and performing a proper, accurate, and scientific database benchmark isn’t always easy. And, while database benchmarks are performed for many different reasons, this session would cover the proper techniques, methodologies, and tools for performing the most common database benchmarking scenarios: hardware configuration comparison, database release comparison, and internal application benchmarking. Tools covered will include the Oracle Linux Test Suite (OLT), Real Application Testing, and Quest Software’s Benchmark Factory.
  • Introducing the Oracle Call Interface (Developer Track)
    IOUG Collaborate 2009 (Session #537)–May 6, 2009
    Often overlooked, the Oracle Call Interface (OCI) has long-since been a high performance, low-level data access API for Oracle. Unfortunately, many people shy away from using OCI simply because of its perceived complexity. However, this session will illustrate that anyone with a little knowledge of C can harness the power of OCI in their own applications. It will also demonstrate two of the most popular OCI wrapper libraries for those who just want to get their feet wet.

I’m also submitting several presentations to New York Oracle Users Group’s Spring General Meeting, and will hopefully be seeing some of you there again.

SibylNet, Oracle/Postgres Benchmark, Password Cracker for Oracle, Storage Engines…

It’s been quite awhile since I last wrote a blog entry. For that, I apologize. I’ve been extremely busy at work and haven’t been able to spend much time on Oracle stuff lately. Regardless, this is what I’ve been up to and what you can expect to see soon.

SibylNet & Unofficial Specification of the Oracle Network Protocol

Oracle has given me permission to proceed with releasing my open source client software for Oracle, SibylNet. As such, I’ve been combining all of my past research into a single protocol specification and client library which I hope to release in Q1 2009.

Oracle vs. Postgres Benchmark

To end a long-running dispute I’ve had with the Postgres community regarding over-reliance on the operating system to achieve good database performance, I’ve performed a comparison benchmark between Oracle8i Standard Edition (circa 1999) and Postgres 8.3/8.4-dev (the latest version) on the exact same hardware. The results may or may not surprise you.

A Fast Password Cracker for Oracle

Years ago I had written a password cracker for Oracle8i/9i, but had given up on it. Though, after playing with Laszlo Toth’s woraauthbf awhile back, I decided to update mine to take full advantage of newer multi-core systems. After a bit of redesign, it now makes use of multi-threaded parallel processing, lock-free/nearly-wait-free cache-optimized hash tables, and atomic locking. I now believe I have the world’s fastest password cracker for Oracle… but I’ll leave that to others to test. I’m currently getting this re-ported to Windows and after letting a few select people test it, will release it as open source.

A three-part series of articles pertaining to database frontends for custom storage engines

In today’s business environment, most companies prefer to buy software rather than build it. However, over the past year and a half, I’ve been approached by three different companies looking for a good database frontend with which they could integrate their own internally-developed storage engines. As each of these companies ran into several of the same issues, I thought that this topic would make for a good series of articles not only for those generally interested in databases, but specifically those who may find themselves in the same situation. The three articles (and products mentioned) are as follows:

  • Just What the Doctor Ordered (Dr. DeeBee Driver Kit)
  • Swimming with the Dolphin (MySQL)
  • An Elephant in the Room (Postgres)