DB2 DBA's blog

Archive for the ‘DB2 database’ Category

What is the best relational database?

In DB2 database, DBA, MySQL, Oracle database, PostgreSQL, SQL Server, Sybase on May 11, 2013 at 11:45

“I have the simplest tastes. I am always satisfied with the best.” Oscar Wilde

If we look at market figures, then “Gartner 2012 Worldwide RDBMS market share” reports 48.3 percent revenue share for Oracle.

Three major factors make the Oracle database a clear leader:

1. Remains #1 in worldwide RDBMS software revenue share
2. Holds a larger revenue share than four closest competitors combined
3. Leads next closest competitor revenue share by 29%

But why is that so? Let us have a look at the latest DB-Engines Ranking where Oracle is not only #1 in the DB-Engines Ranking of Relational DBMS but also in the Complete Ranking.

The DB-Engines Ranking are measured the popularity of a system by using the following parameters:

- Number of mentions of the system on websites, measured as number of results in search engines queries.
- General interest in the system.
- Frequency of technical discussions about the system.
- Number of job offers, in which the system is mentioned.
- Number of profiles in professional networks, in which the system is mentioned.

What is the best relational database? The best answer given in answers.yahoo.com is the following: “Define “best”. Oracle is like a BMW. Expensive but has all the fixings. But not everyone needs a BMW. MySQL is like a VW Beetle (the old model). Its cheap, and gets you where you need to go. But you have to tweak it to suit your needs.” Nice explanation!

Next, let us look at Gartner’s Magic Quadrant for Data Warehouse Database Management Systems from January 31st, 2013:

The reason Oracle is top on the “Ability to Execute” scale is simple. It can be described by me with just one word: Exadata. Of all the vendors in this analysis, Oracle reports the highest incidence of nontraditional analytics customers: sectors such as hospitality, energy trading, life sciences and food distribution show up in its reference base. According to Gartner, many of the vertical markets where Oracle has the greatest success contain traditional implementers or late adopters of data warehousing. Oracle’s customers range in annual revenue from $100 million to over $10 billion.

The Business Technology Forum raised the same question: Which is the best enterprise RDBMS database? The article is very much to the point coming to the conclusion that Oracle have the upper hand.

Mirror, Mirror on the Wall, which is the best RDBMS of all? You do not expect an answer from the Mirror “It depends” because anyone trying to be diplomatic will answer that way. Well, at least they say that Oracle is most scalable, most feature rich, and just a wonderful RDBMS.

More to read:

Why would you use Oracle database?
Why Oracle is the Preferred Database Platform?
Is Microsoft’s SQL Server really cheaper than Oracle?

The last one favors SQL Server over Oracle but a word of caution on the credibility on the Oracle side: the author calls PL/SQL “P-SQL” and refers to Active Data Guard as “Advanced Data Guard”.

It will take DB2, SQL Server, Sybase, PostgreSQL and all other key RDBMS players at least several more releases before they can approach the current self-management and tuning capabilities of Oracle Database 11g. But when that day comes, Oracle will not be still at version 11.2.0.

12c is knocking on the door.

Rate this:

 

 

 

 

 

 

11 Votes

▶ View 2 Comments

The Choice of a New Generation

In DB2 database, DBA, Oracle database on March 31, 2012 at 10:44

Recently, Coca-Cola Bottling migrated the SAP databases from Oracle to IBM DB2. TheCoca Cola case study claims that they gained better performance for the SAP applications, reduced the storage needs and removed workload from the database administrators.

What else can one wish for? Granted that they had zero DB2 experience in-house but the lead DBA knew how to spell DB2! See for yourself:

And they have now higher productivity as the DBAs spend less time with DBA work and can concentrate more on SAP application tuning. If that’s not a perfect example of fast ROI and low TCO!

In another story entitled When will the database world wake up?, the author says that “IBM DB2 is more cost efficient than competing products especially Oracle Database… Better price-performance, better database compression, HADR: are these the only value proportion to buy or move to IBM DB2? No, IBM DB2 offers more values to the customers.”

I am again in a perplexing situation. I go on reading. “Strong partnership exists between IBM and SAP, joint SAP and IBM teams work on all levels of the product development. DB2 is certified by SAP within 4-8 weeks of its general availability (GA) date while Oracle does not have its new database releases certified by SAP until 1.5 to 2+ years after their GA date. All new DB2 features are designed to be 100% compatible and exploited by SAP directly.”

Now, I need a second opinion. Let us see what database guru Jonathan Lewis has to say:

That is, Jonathan says “SAP runs badly on everything, so you might as well choose the cheapest thing to run it badly on, rather than buying a sophisticated database system and then finding that you’re not allowed to use any of the expensive features that you’ve paid for.” Crystal clear!

If you would like to see what Oracle have to say, check this study: Oracle Database 11g vs. IBM DB2 UDB V9.7 Manageability Overview.

What’s next? Let us peek into some of Oracle’s most interesting features and try to find their equivalent in DB2? All comments and moreover answers to DB2 equivalents of the 6 Oracle’s features below are welcome.

1. Real Application Testing Database Reply
2. Automatic SQL Tuning
3. Edition-Based Redefinition
4. Enterprise Manager Cloud Control
5. SQL Performance Analyzer, AWR and ADDM
6. SQL Plan Management

What equivalents?

Another interesting comparison of Oracle and DB2, 51 pages in total, is called Database Manageability and Productivity Cost Comparison Study. The study concluded:

· Oracle Database 11g Release 2 Enterprise Edition resulted in an overall 43 percent time savings when compared to IBM DB2 Enterprise 9.7.
· Oracle Database 11g Release 2 Enterprise Edition resulted in an overall 45 percent step/complexity savings when compared to IBM DB2 Enterprise 9.7.
· Based on a productivity savings of 43 percent, the time savings amounted to USD $51,600.00 per year per DBA for an Oracle Database 11g Release 2 DBA versus an IBM DB2 Enterprise 9.7 DBA.

More interesting, in my opinion, is Conor O’Mahony’s analysis of that study entitledOracle’s Shameful Study: Oracle Database 11gR2 vs. IBM DB2 9.7. Scott Hayes replies:

I am ready to take part in the study suggested by Scott Hayes, if ever organized. “Each installs their respective database, completes a design implementation, loads data, and performs maintenance tasks.” I am even ready to do it without any links to the documentation and any internet access.

What do you conclude from the above? That setting memory_target is a hard thing to so? Details here.

The Oracle System Monitoring Plug-In for IBM DB2 Database gives DBAs the opportunity to manage also IBM DB2 UDB (LUW) database instances. In fact, OEM Cloud Control 12c supports besides IBM DB2 also TimesTen, Sybase, Microsoft SQL Server and MySQL. Check the heterogeneous (Non-Oracle) management.

P.S. Last Sunday (25th March 2012), Dennis Howlett wrote an article entitled Oracle DB under threat? In summary, he said that Oracle’s database is under the spotlight in SAP shops. In summary, I would say: “Remember what Jonathan Lewis says“.

A couple of days after my post, Julie Bort wrote an interesting note entitled IBM Is Picking A New Fight With Oracle. Let me quote her: “IBM also wants customers to know that it hasn’t turned SAP into an enemy like Oracle has. It promises its new database makes SAP business software work faster than Oracle. Coca-Cola switched to DB2 10 and SAP worked up to 60% faster, it says.” Working up to 60% faster means that it can also work 5% faster or than even it can be slower, right?

Rate this:

 

 

 

 

 

 

14 Votes

▶ View 1 Comment

Oracle Database Appliance and Automatic Bug Fixing in the Cloud

In Bugs, Database tuning, DB2 database, DBA, Grid Control, Oracle database on September 21, 2011 at 19:13

Mathematician Alfred North Whitehead said: “Civilization advances by extending the number of important operations which we can perform without thinking about them.”

Same holds for the Database!

Oracle have just announced the new “Oracle Database Appliance” with self managing automatic features preconfigured (simple, highly reliable, affordable database system for small/midsize enterprises and departments):

I have just gathered a list of the automated processes offered by Oracle within the Oracle database (in no order whatsoever):

- Automatic Statistics Collection
- Automatic Tuning Optimizer (ATO)
- Automatic Repair in a Data Guard Configuration
- Automatic Undo Management
- Automatic Undo Retention Tuning
- Automatic Shared Memory Management
- Automatic Space Segment Management (ASSM)
- Automatic PGA Memory Management
- Automatic Memory Management (AMM)
- Automatic Degree of Parallelism
- Automatic Storage Management (ASM)
- Automatic RAC Database Startup/Restart
- Automatic Maintenance Tasks
- Automatic Tablespace Point In Time Recovery
- Automatic Workload Repository (AWR)
- Automatic Service Registration
- Automatic SQL Tuning (my favourite!)
- Automatic Database Diagnostic Monitor
- Automatic Segment Advisor
- Automatic Diagnostic Repository (ADR)
- Automatic Checkpoint Tuning
- Automatic Maintenance Jobs
- Automatic Global Index Maintenance During DDL
- Automatic Client Failover
- Automatic OCR Backup Rotation
- Automatic Plan Capture with SPM
- Automatic Refresh of Materialized Views
- Automatic VIP failback
- Automatic Block Recover
- Automatic Disaster Recovery Fails with RMAN
- Automatic Channel Failover
- Automatic Synchronization of Oracle Text Index
- Automatic Registration of the Database with the Default Listener
- Automatic Offlining of the Datafile in Noarchivelog
- Automatic Datatype Conversion
- Automatic Tape Drive Cleaning and a Cleaning Tape in a Tape Library
- Automatic Controlfile Backup
- Automatic Eject Of Tape After Backup Using Oracle Secure Backup
- Automatic BackupSet Failover On Missing or Corrupt BackupPieces
- Automatic BMR (Block Media Recovery)
- Automatic System Tasks
- Automatic Database Performance Monitoring
- Automatic Archiving
- Automatic Propagation in Replication
- Automatic Job Scheduling
- Automatic Resume of DataPump

Quite a list I would say, right?

An excellent paper from Oracle called Oracle Database 11g vs. IBM DB2 UDB V9.7 points out the most important trend of database manageability: the self automation of the database product. Let me quote (part of) the conclusion of the paper:

“The Automatic Database Diagnostic Monitor (ADDM), SQL Advisors and Real Application Testing are just some of the unique Oracle Database 11g features that are yet unmatched by DB2 version 9.7. Oracle Database 11g is the only database product available today that automatically collects and manages historical performance data for self-management purposes, periodically and automatically analyses this data and makes tuning recommendations. Oracle Database 11g is also the only RDBMS with rich software quality management features for real workload testing. These distinct technologies are at the core of the next generation of Oracle databases that represent simplicity, ease of management and software quality management while still providing the most robust, reliable and secure of relational databases.”

How true indeed! But what we would like to see in the future is even more: how about automatic bug fixing in the Cloud? Just like this:

1. First, we set the credentials with Oracle Support: How to set the ‘My Oracle Support’ Preferred Credentials in the Grid Console? [ID 1168603.1]
2. The Oracle database creates an incident and it is transferred to Oracle Support via the Enterprise Manager Support Workbench.
3. Then it is internally verified if the problems is bug related.
4. If it is a bug then Oracle’s own BugDB checks for a patch or workaround which fixes the bug.
5. If there is a patch available, then the patch is automatically uploaded to the clients cloud environment and then applied (online of course)!
6. If there is a workaround with an init.ora parameter, then the “alter system” command is automatically applied, as what Oracle Support can remotely run in the client’s database is controlled by the client with a new init.ora parameter called mos_cloud_permission_level.

That is what I call automation!

Note that something similar is even now offered by Oracle via the SQL Repair Advisor. It is a semi-automatic patching of SQL statements throwing ORA-600 or ORA-7445. But here by patch Oracle mean more of an SQL transformation than a standard patch downloadable from MOS.

Rate this:

 

 

 

 

 

 

5 Votes

▶ View 1 Comment

I’m in a very perplexing situation: IBM DB2 9.7 or Oracle 11.2?

In DB2 database, DBA, Oracle database on September 1, 2011 at 07:28

Unlike Owen Wilson’s perplexing situation between the past and the present in Midnight in Paris, mine is in the present only: two current releases of two database brands: IBM DB2 9.7 and Oracle 11.2.

Which one is easier to administer? DB2 9.7 or Oracle 11.2? Which one saves more time and has higher total cost of ownership? Perplexing situation! But why?

In a 29 page article from October 2010, called Comparing DBA Productivity: An Oracle/DB2 Task Complexity Analysis, Triton Consulting (an IBM Premier Business Partner) “proved” that the advantages of DB2 9.7 over Oracle 11.2 are more than 50%:

In a 51 page article from June 2011, called Database Manageability and Productivity Cost Comparison Study: Oracle Database 11g Release 2 vs IBM DB2 Enterprise 9.7, ORC International “proved” that the advantage of Oracle 11.2 over DB2 9.7 are the following:

- Oracle Database 11g reduces required DBA time by 43 percent compared to IBM DB2
- Oracle is 45% less complex and easier to manage
- By using Oracle instead of DB2, businesses can save US$51,600 per DBA per year

What is going on? One tells us that IBM DB2 is easier to manage than Oracle, another one tells us exactly the opposite.

Obviously, comparing Oracle to DB2 is not as straightforward as comparing ArchiCAD to NotePad?

Let us consider the backup and recovery scenario from both studies. The pro-IBM one claims that the DB2 advantage is 83%. The pro-Oracle one says that the IBM process requires 65% more time and 51% more steps. Those who have spent years of really backing up and restoring databases know very well that there are dozens of factors that influence on the process. Giving figures for average time is simple going out of proportions.

Getting realistic and keeping low profile on the marketing aspect, most of us know that all is up to the people who do the work: the DBAs. There are DBAs that can install/patch the Oracle software and create/upgrade a database in less than 2 hours; there are DBAs who will struggle may be 2 days before asking for help.

What is complex for a certain DBA might be trivial for another. Bottom line is that education, work experience and knowledge are what matter when DBA work is done.

Look at the following screen captured from the video New Approaches for Database Cost Savings by Forrester’s Noel Yuhanna:

The efforts required according to the last column are low. And this might eventually bring cost savings due to increased DBA productivity. Questions is whose efforts are we talking about? It is all up to the people, their skills and knowledge.

In my opinion, when database features are compared, what matters is what is automated and what the DBA is not required to do instead of the time required for the work. And here features like the Automatic Database Diagnostic Monitor, Tuning Advisors, Real Application Testing make the difference! Namely, the self management capabilities of the RDBMS.

Comparing things on a general level (even with detailed figure estimations) might give us somewhat picture about what’s going on but crunching numbers in the database industry might not always bring us the same results. The two contradictory articles mentioned about seem to show us this perplexing situation.

Rate this:

 

 

 

 

 

 

4 Votes

▶ View 1 Comment

The Google DBA, the GUI DBA and the DBA who does all the work

In DB2 database, DBA, MySQL, Oracle database, SQL Server on June 26, 2011 at 12:36

I often neglect notes and comments on people who divide DBAs into groups and types but this DBA’s Journey into the Cloud is worth commenting on:

The exposé is so short that one does not get the impression if these 3 DBA types are mutually exclusive or not. If not, why would then someone profile DBAs into groups?

You obviously use Google and Metalink (called MOS nowadays). And you use GUIs: Grid Control, Oracle Developer, Toad? Doesn’t look impressive if you cannot make it into the last group, right?

And into which group will the guy hired for this Pre-Sales DBA job fit?

Let me change to another classification method and peek at the The DBA Corner of Craig Mullins. He says that the more common types of DBAs are:

- System DBA
- Database Architect
- Database Analyst
- Data Modeler
- Application DBA
- Task-Orientated DBA
- Performance Analyst
- Data Warehouse Administrator

Too many. I might agree that the system DBA and application DBA are different roles but all those analysts? “The performance analyst is usually the most skilled, senior member of the DBA staff, a role that he has grown into due to his experience and the respect he has gained in past tuning endeavors.” Interesting..

Next, I want to see what Don Burleson has to say. What Type of DBA Are You? Only 3 options here:

- The Scientist DBA
- The Can-do DBA
- The Empathic DBA

Looks like classification on emotional basis. Interesting reading though.

Let us look at what Wikipedia has to say. Déjà vu! 3 types again:

- System DBA
- Development DBA
- Application DBA

Development DBA? Used to be called a programmer years ago. I always thought that the Development DBA takes care of the development databases 

What do people have to say about the DBA types in the internet forums. Let us visit DBA support. Funny stuff at the end! Now, the types are 4, with short descriptions listed below:

- The smart and industrious DBA
- The stupid and industrious DBA
- The smart and lazy DBA
- The stupid and lazy DBA

Up to now, it looks to me that I am always a mixture of some type. A hybrid? Brian Knight figures it out in his article DBA Job Description: What type of DBA are you? 3 types again:

- Production DBA
- Development DBA
- Hybrid DBA

I really don’t know. 10 years ago, he wrote about the Death of the Production DBA and companies still recruit SQL Server Production DBAs.

If you surf the Internet, you will find all type of DBA classifications: the Oracle DBA and the MySQL DBA; the certified DBA and non-certified DBA; the on-call DBA and the 9-to-5 DBA.

There is even a Marketing DBA: “DBA skill needs to sale out software to vendors. To convince customer in technical point of view marketing team requires DBA help. Marketing DBA tries to satisfy each technical queries of customer and help marketing team. There is no database administration and maintenance directly involve in this DBA role.”

Look once more in the text above: A database administrator role such that there is no database administration. No comment.

But eventually, it looks to me that DBAs can be divided into two groups: those who divide DBAs into groups and those who don’t..

Rate this:

 

 

 

 

 

 

12 Votes

▶ Comment

The DBA: an architect or an administrator?

In DB2 database, DBA, MySQL, Oracle database, SQL Server on April 26, 2011 at 19:55

Most people in IT comprehend the complexity and importance of the DBA role.

Database architects and administrators are the building block of any IT organizations. The area of database management is so vast that some DBAs specialize nowadays in certain parts of the DBA profession: replication, performance tuning, clustering technologies, etc.

No surprise that CNN Money places both the software architect and the database administrator in the Top 100 for the Best Jobs in America. In fact, software architect is the best job in America and database administrator is #7.

Have a look at all IT jobs that can be found in Top 100 for 2010:

1. Software Architect
7. Database Administrator
17. Information Systems Security Engineer
18. Software Engineering / Development Director
20. Information Technology Manager
21. Telecommunications Network Engineer
24. Network Operations Project Manager
26. Information Technology Business Analyst
28. Information Technology Consultant
30. Test Software Development Engineer
31. Information Technology Network Engineer
33. Information Technology Program Manager
35. Computer and Information Scientist
37. Programmer Analyst
38. Applications Engineer
49. Systems Engineer
60. Information Technology Specialist
66. Systems Administrator
67. Web Developer
70. Technical Services Manager
77. Information Technology Systems Manager
79. Information Technology Training Specialist
88. Technical Writer
95. Information Technology Project Coordinator
96. Web Project Manager

For those of you who have spent years in database architecture and administration, this comes as no surprise perhaps.

For those who might be puzzled and for the ones who would like to read more on the subject, check this article from Datavail called Database and DBA Management.

Last year, Sloane Stricker presented the Top 10 reasons DBAs quit. I would like to add what triggers this and what managers should try to do in order to keep the DBAs in the company:

- DBAs need at least 20% white time: that is time on their own to read white papers, blogs, forums, metalink, etc. Time to test, research and debug new features and old issues. Time to attend online seminars, conferences, Oracle Open World, etc.
- Try to find what motivates each DBA. They are all individuals with own preferences and interests. Bonus intensives should be real personal. I value very much the team work but it is just one finger that presses “Enter” upon shutdown immediate.
- Keep their salaries enough competitive. Do not just tie them to the company’s job grade or job band. There is nothing wrong with you most senior DBA having higher annual income than your IT Director. Really!

For those of you who might be more deeply interested in the topic, check the Gartner/Burton paper: Realigning Database Administration to Jump-Start a Data Management Organization. Very useful for midsize and big organizations! Here is the conclusion of the article:

“The formation of a Data Management Organization (= DMO) is Gartner’s recommended solution for correcting the sorry state of data management in the majority of enterprises. Competitive pressures mean that time is of the essence, Gartner sees the reorganization of the existing database administration groups into an embryonic DMO as a fast-track, but effective, first step in this process. This guidance document provides a framework to direct this organizational transformation.”

As it might sound a bit out of context here, I recommend that you read the whole article.

And finally back to the question in the title: it is both. The DBA should nowadays be both an architect and an administrator. The time of DBA 2.0 is over, it is now time for DBA 3.0!

Rate this:

 

 

 

 

 

 

11 Votes

▶ View 2 Comments

On the Total Cost of Database Administration

In DB2 database, DBA, MySQL, Oracle database, SQL Server on March 25, 2011 at 10:42

Have you ever read those “white papers” distributed over the internet on total costs of database administration? One claiming that “Microsoft SQL Server required significantly less effort to install and maintain than Oracle”, another one saying that “DBAs can perform typical administrative functions in 41 percent less time when using Oracle Database compared to Microsoft SQL Server”.

You can just stare in disbelief in their “facts” and “arguments”. The former quote above is from ComputerWorld “The Total Cost of Administration Champ: Microsoft SQL Server 2005 or Oracle Database 10g?”, the latter is from TechRepublic “Oracle Database 11g vs.
Microsoft SQL Server 2008″.

If Oracle is the Lexus of relational databases, is then your Oracle DBA cost as high as the service cost of an expensive car? Should you consider switching to SQL Server? Nobody is saying that IBM’s DB2 is cheap but very often the comparison is between Oracle and SQL Server.

You might have seen Don Burleson’s article Oracle 10g v. SQL Server. I agree with Don that reducing the number of expensive DBAs is hardly an area of opportunity for the IT manager to save money. But when you often show managers figures like the ones below:

Managers will sooner or later start believing some of those “research figures”. Some of them at least. You see, somebody claims that one DBA can administer 9.9 Oracle databases/31.2 Microsoft databases , another one mentions 15.2 Oracle databases/65.4 Microsoft databases. Probably the figures are not far from reality although I have seen that with some automation the numbers can grow much higher (100-150 databases per DBA) although I can’t take seriously studies giving me numbers about users supported by DBA. Sorry, what users? Internet users, database users/schemas? Come on..

It is not up to the number of databases a DBA handles or the gigabytes per DBAs or even the brand. It is up to the skills and experience of person doing the database administrations, it is up to environment and the DBA team handling the work. It is much about the set-up of the databases and their complexity. Of course the automation involved in the DBA work is a significant factor as well. But generalizations like the ones above and below are just mildly insane. If someone tries to convince you that you will save a lot of money by putting your databases in “the Cloud”, just turn and run 

About 5 years ago, Forrester did the following forecasts, decide for yourself if predictions were TRUE or FALSE. At least I disagree with the performance tuning curve.

1. DBA to databases ratio:

2. Database administration cost is declining:

3. Database administration challenges:

Database administration has been and will remain the most complex part of IT. The new features added by vendors like Oracle, IBM and Microsoft to the relation software only make administration more difficult, complex and hard to learn and master from scratch.

I like Don Burleson's database articles

YesNo

VoteView ResultsPolldaddy.com

P.S. There is a book written in 1954 called “How to Lie with Statistics”. You can get it now for 5.99 from amazon.com

Rate this:

 

 

 

 

 

 

3 Votes

▶ View 1 Comment

Scorecards for ranking relational databases

In DB2 database, DBA, Oracle database, SQL on March 18, 2011 at 18:10

In June 1970, Edgar “Ted” F. Codd publicly publishes the following paper: A Relational Model of Data for Large Shared Data Banks. IBM refused to implement his model to preserve revenues of IMS/DB. Customers pressured IBM to build it (System-R project) and a relational language SEQUEL: Structured English Query Language, later SQL, evoked.

Codd’s produced 12 rules, which in fact is a set of thirteen rules (numbered zero to twelve), that define a relational database.

Depending on your brand preferences, one can play with the words in the definition of a relational database and for example get Oracle on top as most “relational”. Or get IBM or even 3-4 other brands above Oracle. Some give one point for each rule being fulfilled, thus 12 points is the maximum. Some give 0.5 points for partially fulfilled rules in which case Oracle would be rated as 11.5/12 as (R6) is not satisfied (and will never be).

Oracle have come closer than all other brands to solving the update view issue using MVs (materialized views). DB2 can also support the update view fully, if the integrity between a view and its base table can be maintained. Microsoft SQL Server has to solve the view problem as they are behind.

Some people claim that there is no RDBMS that supports totally the update views rule (R6), the management of null values rule (R3) and and logical data independence (R9). My claim is that Oracle satisfies (R3) & (R9). For (R6) , I give Oracle 0.5 points.

You can see rather obscure statements like on http://www.firstsql.com that “only Ingres and FirstSQL among the RDBMS vendors have a solid foundation in their engine for future improvements.” Additionally they say that “Oracle would rate about 60% compliance. Others are lower except Ingres and FirstSQL which rate about 65% compliance. Interestingly enough, Xbase products rate 0% in compliance with the relational model.” Yeah, right 

Overall, regardless of the question is Oracle, Microsoft or IBM most relational in their database products, we should remember than nowadays it doesn’t really matter, does it? What Codd (and Date) produced was just a model and they missed (or didn’t bother to wait for) the business train. Larry Ellison spotted the business idea and built a database empire. With all the limitations faced throughout the years.

Today, the technology does not drive the business. It is vice versa. We develop technology to make business move forward. That is why I don’t care at all if DB2, Oracle or SQL Server is 100% relational. It doesn’t matter any longer! What would you rather be: right or rich?

And finally, here is for your reference Chris Date’s version of the Codd’s Rules and note that in Version 8, Oracle was 8.5/12 RDBMS compliant 

0. (Yes, there is a Rule 0!)
For a system to qualify as a RELATIONAL, DATABASE, MANAGEMENT system, that system must use its RELATIONAL facilities (exclusively) to MANAGE the DATABASE.

1. The information rule:
The information rule simply requires all information in the database to be represented in one and only one way, Namely by values in column positions within rows of tables.

2. The guaranteed access rule:
This rule is essentially a restatement of the fundamental requirement for primary keys. It says that every individual scalar value in the database must be logically addressable by specifying the mane of the containing table, the name of the containing column and the primary key value of the containing row.

3. Systematic treatment of null values:
The DBMS is required to support a representation of “missing information and inapplicable information” that is systematic, distinct from all regular values (for example, “distinct from zero or any other number,” in the case of numeric values),
and independent of data type. It is also implied that such representations must be manipulated by the DBMS in a systematic way.

4. Active online catalog based on the relational model:
The system is required to support an online, inline, relational catalog that is accessible to authorized users by means of their regular query language.

5. The comprehensive data sublanguage rule:
The system must support a least one relational language that
(a) has a linear syntax,
(b) can be used both interactively and within application programs,
(c) supports data definition operations (including view definitions), data manipulation operations (update as well as retrieval),
security and integrity constraints, and transaction management operations (begin, commit, and rollback).

6. The view updating rule:
All views that are theoretically updatable must be updatable by the system.

7. High-level insert, update, and delete:
The system must support set-at-a-time INSERT, UPDATE, and DELETE operators.

8. Physical data independence:
Self-explanatory.

9. Logical data independence:
Self-explanatory

10. Integrity independence:
Integrity constraints must be specified separately from application programs and stored in the catalog. It must be possible to change such constraints as and when appropriate without unnecessarily affecting existing applications.

11. Distribution independence:
Existing applications should continue to operate successfully
(a) when a distributed version of the DBMS is first introduced;
(b) when existing distributed data is redistributed around the system.

12. The nonsubversion rule:
If the system provides a low-level (record-at-a-time) interface, then that interface cannot be used to subvert the system (e.g.) bypassing a relational security or integrity constraint.

P.S. I have not mentioned anything about MySQL for in my opinion MySQL is rather an SDK (Software Development Kit) than a database.

Rate this:

 

 

 

 

 

 

1 Vote

▶ Comment

Watson, IBM InfoSphere Streams and Oracle Streams

In Grid Control, Replication, Oracle database, DB2 database on February 24, 2011 at 19:30

Initially, my plan was to write only on Oracle Streams but after Watson’s victory on Jeopardy, I got an interesting question from an ex-colleague of mine: Is this supercomputer HA? What if the database crashes and there is no source for answers? Funny, isn’t it  After all Watson is an example of an analytics workload optimized system, not an online sales platform.

But first, have a look at this, how Ken Jennings and Brad Rutter, the show’s two biggest winners, took on their database rival on Feb. 14-16 2011. Note that according to the president of WABC – New York City’s ABC television affiliate Monday’s broadcast of the IBM Watson/Jeopardy Challenge was the most watched show on any channel in the NY Metro area.

Watson is a mixture of 10 racks of IBM POWER 750 servers, Linux OS, 15 TB of RAM, 2880 processor cores and operates at 80 teraflops. The data is stored in a DB2 database. Another one of the 4 major technologies Watson is based on is IBM InfoSphere Streams. While IBM InfoSphere Streams enables continuous and super fast analysis of massive volumes of information, Oracle Streams enables continuous and very fast replication of massive volumes of information. Well, at least when it works and there are no errors. Say one like this:

Looking at the details of the error, what do we understand?

As much as we understand from the statistics:

My personal opinion is that Streams is as good as Advanced Replication was. After all, Oracle will not develop it any longer. Oracle bought Golden Gate and will try to use it for all data integration/replication solutions.

P.S. If only Sherlock Holmes had this type of Watson 

Rate this:

 

 

 

 

 

 

1 Vote

▶ View 1 Comment

Isolation levels in database transactions in Oracle and DB2

In DB2 database, DBA, Oracle database on February 23, 2011 at 19:24

The SQL92 standard defines four levels of transaction isolation with differing degrees of impact on transaction processing. These isolation levels are defined in terms of the following three read types:

Dirty reads: A transaction reads data that has been written by another transaction that has not been committed yet.
Fuzzy reads: A transaction rereads data it has previously read and finds that another committed transaction has modified or deleted the data.
Phantom reads: A transaction re-runs a query returning a set of rows that satisfies a search condition and finds that another committed transaction has inserted additional rows that satisfy the condition.

SQL92 defines four levels of isolation based on the read permissions above (nonrepeatable read is same as a fuzzy read):

Now, Oracle offers only the read committed and serializable isolation levels plus a read-only mode that is not part of SQL92. Read committed is the default. Read-only transactions see only those changes that were committed at the time the transaction began and do not allow INSERT, UPDATE, and DELETE statements. Serializable transactions see only those changes that were committed at the time the transaction began, plus those changes made by the transaction itself through INSERT, UPDATE, and DELETE statements.

Here is how to set in Oracle the isolation level of a transaction, remember to issue the statement at the very beginning:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION READ ONLY;

DB2 supports all four isolation levels. The isolation levels are called:

RR: Repeatable read
RS: Read stability
CS: Cursor stability
UR: Uncommitted read

Have a look at this table to see the DB2 UDB equivalent to the ANSI SQL Isolation Level:

In DB2, the isolation level can be specified in several different ways. It depends on if we use static or dynamic SQL. At precompile or bind time we can also use the PREP or BIND APIs to specify the isolation level. On database servers that support REXX: REXX and the command line processor connect to a database using a default isolation level of cursor stability. At the statement level use the WITH clause. From CLI or ODBC at runtime use the CHANGE ISOLATION LEVEL command. For dynamic SQL within the current session use the SET CURRENT ISOLATION statement to set the isolation level for dynamic SQL issued within a session.

DB2/LUW introduces a new isolation level semantic called “CURRENTLY COMMITTED” that allows DB2 locking to generally work as Oracle does and this means that your applications can work with both databases without different logic. There is also a new interface called “CLP Plus” that is useful for running scripts written for another vendor’s interface. It mimics SQL*PLus in Oracle.DB2/LUW introduces a new isolation level semantic called “CURRENTLY COMMITTED” that allows DB2 locking to generally work as Oracle does and this means that your applications can work with both databases without different logic. There is also a new interface called “CLP Plus” that is useful for running scripts written for another vendor’s interface. It mimics SQL*PLus in Oracle.

Finally, here is comparison of the most important database brands:

P.S. To determine the isolation level of a package in DB2, execute the following query: SELECT ISOLATION FROM SYSCAT.PACKAGES WHERE PKGNAME = ‘X’ AND PKGSCHEMA = ‘Y’ .

P.P.S. OLE DB, Teradata, and nonrelational data sources do not have a concept similar to isolation levels.