Did I get my money worth?

At the end of November I submitted a Purchase Order for my 2009 Annual Oracle Support. I passed the six figure mark several years ago and am well on my way to topping the next hundred next year. Here is my report from Metalink of the number of incidences and close times for the year:

2. Close Rates (closed calls only)

Time	Incidents	%	Cumulative Total
4 h	1	1.41	1.41
12 h	1	1.41	2.82
24 h	0	0	2.82
48 h	3	4.23	7.05
72 h	1	1.41	8.46
1 Week	2	2.82	11.28
2 Weeks	3	4.23	15.51
4 Weeks	40	56.34	71.85
>4 Weeks	20	28.17	100.02
Total Incidents	71

That doesn't count the 5 TARs I still have open; one from May. 84% of my TARs are closed in four weeks or more. Let me say that again; FOUR WEEKS OR MORE. Each TAR cost me approximately $2500.

So, Oracle Community, did I get adequate return on my investment?

(Oh, and BTW, I love how my cumulative total adds up to 100.02%)

The Bug, part II

AH HA! you guys took the bait!

Granted, this is a bug in the particular JDBC Driver I am using. The 10.2.0.2 driver straight out of the box has bug 4711863 that can possibly insert bad data when you use Java 1.5. In fact, I proved it with a scenario similar to:

drop table tablea
/
drop table tableb
/
create table tablea (
record_id number(38) primary key,
price number)
/
create table tableb (
record_id number(38),
price number)
/
-- fill it
begin
for i in 1..100 loop
 insert into tablea values (i, 0+(i*.0000001));
 commit;
end loop;
end;
/

quit

Then, my java program sucks data from tableA and inserts it into tableB. Inside my java program, I convert a Double to BigDecimal using code like:

String insertString = "insert into tableB values (?, ?)";
PreparedStatement stmt2 = this.dbConnection.prepareCall(insertString);
stmt2.setInt(1,rs.getInt("RECORD_ID"));
stmt2.setBigDecimal(2, new BigDecimal(rs.getDouble("PRICE")));

I query the data from tableB and boy am I surprised:

SQL> select * from tableb where price <> price*1;

      RECORD_ID                PRICE
-------------------- --------------------
              2          .00000019RS
              3          .00000029RS
              4          .00000039RS
              5          .00000049RS
              6          .00000059RS
              7          .00000069RS
              8          .00000079RS
              9          .00000089RS
             10          .00000099RS

So maybe it's a sqlplus setting or something. I try:

SQL> set numwidth 20
SQL> /

      RECORD_ID                PRICE
-------------------- --------------------
              2          .00000019RS
              3          .00000029RS
              4          .00000039RS
              5          .00000049RS
              6          .00000059RS
              7          .00000069RS
              8          .00000079RS
              9          .00000089RS
             10          .00000099RS

Same deal.

I go back and verify my source data from tableA, and sure enough it's correct:

SQL> select * from tablea where record_id = 2;

 RECORD_ID           PRICE
--------------- ---------------
         2        .0000002

SQL> l
  1* select record_id, price, dump(price) dmp from tableb where price <> price * 1
SQL> /

RECORD_ID                PRICE DMP
--------- -------------------- ----------------------------------------
        2          .00000019RS Typ=2 Len=21: 189,20,246,100,100,100,100
                               ,100,100,100,10,50,63,24,66,18,73,52,74,
                               72,23

        3          .00000029RS Typ=2 Len=21: 189,30,246,100,100,100,100
                               ,100,100,99,65,25,44,36,48,77,59,78,61,5
                               7,85

        4          .00000039RS Typ=2 Len=21: 189,40,246,100,100,100,100
                               ,100,100,99,19,100,25,48,31,36,46,4,48,4
                               3,46

        5          .00000049RS Typ=2 Len=21: 189,50,246,100,100,100,100
                               ,100,100,98,74,75,6,60,13,95,32,30,35,29
                               ,7

        6          .00000059RS Typ=2 Len=21: 189,60,246,100,100,100,100
                               ,100,100,98,29,49,87,71,96,54,18,56,22,1
                               4,69

        7          .00000069RS Typ=2 Len=21: 189,70,246,100,100,100,100
                               ,100,100,97,84,24,68,83,79,13,4,82,8,100
                               ,30

        8          .00000079RS Typ=2 Len=21: 189,80,246,100,100,100,100
                               ,100,100,97,38,99,49,95,61,71,91,7,95,85
                               ,92

        9          .00000089RS Typ=2 Len=21: 189,90,246,100,100,100,100
                               ,100,100,96,93,74,31,7,44,30,77,33,82,71
                               ,53

       10          .00000099RS Typ=2 Len=21: 189,100,246,100,100,100,10
                               0,100,100,96,48,49,12,19,26,89,63,59,69,
                               57,14

OK, so JDBC isn't 100% correct when you cast between BigDecimal and Double. Big whoop. And in fact, the fix for the aforementioned bug is fixed if you apply the appropriate patch.

My problem is in the fact that Oracle (the database software) let a bad piece of data in. Oracle says the client should type the data when binding. I agree. But I also think that the server shouldn't let non-numeric data into numeric columns, regardless. I contend that this is a bug on the server side as well as a bug on the client side. This is only one specific case of a Java program inserting bad data. I have some tables that have non-numeric characters that were never touched by Java.

Where is the bug?

So, if I have a table that has a column defined as NUMBER(10) and try to insert the value of 'ABCDEFG' in it, I get an "ORA-01858: a non-numeric character was found where a numeric was expected" error. Fair enough, I expect that the database does type checking and won't allow an invalid number in the field.

In addition, if I try to insert an 11 digit number, I get an "ORA-01438: value larger than specified precision allowed for this column" error.

So far so good.

If an invalid number, such as "-0" or "17~:49" got into the database via JDBC, would it be a client problem, a server problem, or both?

11g

I was asked the other day when I was going to start writing about 11g.

Quite frankly, I literally just got everything off 9i last weekend. The final thorn in my side of Oracle Applications was started at the close of business on Friday and lingered on to the wee hours of Saturday morning. It took about 18 months and my standard install of 10g has 23 patches applied, but finally I have a consistent environment on 10g.

And lets face it, 10g is a pretty darn good product once it's patched. The combination of 10g and the Linux 2.6 kernel is really a rock solid platform with a laundry list of features that you'll probably never need.

I'm keeping abreast of what's new in 11g and I haven't really seen anything compelling that piques my curiosity enough to even install it. Maybe my opinion will change when the calendar flips over and we have some of that mythical "free" time, but for now, I'll be sticking to 10g topics.

Phone Interview tips for DBAs

You only get one chance to make a first impression. I would think the following tips would be common sense, yet I am surprised every day.

1. When on a phone interview, don't put the interviewer on hold.
2. I don't mind calling you on your cell, but if the call drops, you're done.
3. I know you might be nervous, but don't cut me off in mid-sentence.
4. When you take my call, don't be driving in a car.
5. I don't expect you to pick up the phone on the first ring, but please pick it up. If I get your Voicemail, I'll try you again in five minutes. After that, you're on your own.

Database Administrator

Job Description
Interactive Brokers Group, industry leader in direct access brokerage, is seeking a motivated Database Administrator to provide day-to-day production support for our highly automated mission critical systems. This position requires interacting with very technical users, big-picture analysis, and acute problem solving skills.


Required Skills

• 3+ years experience as Oracle DBA in a production environment.
• Linux and/or Solaris
• Day-to-day Database Administration.
• Backup and Recovery using Recovery Manager.
• Oracle 10g (at least 2 years)
• Ability to quickly diagnose and resolve performance using standard tools. (explain plan, OWI, Profiler, Statspack)
• Some experience with partitioning
• Self-starter with initiative and desire to learn
• Ability to work quickly in a fast-paced environment.
• Rotating on-duty coverage is expected.
• Ability to communicate clearly, constructively and concisely to peers and end-users.
• Ability to work independently with minimal supervision and drive issues to closure.

Desired Skills

• Experience with Oracle Applications R12
• Automation of DBA tasks (Perl, CGI, Ksh)
• Some experience in 11g
• DR Strategies including Standby Database
• Financial Industry Background
• Oracle Connection Manager

Job Code: JH-DA-CT

Interactive Brokers Group LLC

Apply Now