Posts tagged "SQL"

SQL, NoSQL or NewSQL Databases

SQL Databases


English: SQL CLR internal architecture diagram...

English: SQL CLR internal architecture diagram ???(??)â?¬: SQL CLR ??? (Photo credit: Wikipedia)

SQL Databases have had great success in the past for online transactional systems and SQL has become a popular standard to access data.

These databases enforce also the ACID  (Atomicity, Consistency, Isolation, Durability) properties and therefore relieve the application programmer from any consideration about them. Jim Gray defined these to be the properties of a reliable transaction system in the late 1970s and developed technologies to achieve them automatically. In 1983, Andreas Reuter and Theo Härder coined the acronym ACID to describe them.

However databases with big data are becoming more common (e.g the database supporting Facebook) and SQL is considered too slow to handle the large volume of data and large number of transactions,

Scalability and performance issues with relational databases became commonplace and somebody  suggested to use NoSQL databases and to give up the ACID properties.


NoSQL Databases

NoSQL is a catch-all name for different kinds of database architectures — key-value stores, document databases, column family databases and graph databases.

Each of them has it’s own relative advantages and disadvantages. However, in order to get scalability and performance, NoSQL databases give up “queryability” (i.e. they cannot use SQL) and usually ACID transactions. Some NoSQL DBs still support ACID like RavenDB (


NewSQL Databases

English: Computer science researcher Michael S...

English: Computer science researcher Michael Stonebraker just after giving a talk at the University of California, Berkeley (306 Soda Hall, HP Auditorium) on “Task-specific Search”. (Photo credit: Wikipedia)

NewSQL Databases are an alternative to NoSQL Databases proposed by Michael Stonebraker, a legendary computer scientist at MIT who specializes in database systems and is considered to be the forefather of big data. Stonebraker developed INGRES, which helped pioneer the use of relational databases, and has formed nine companies related to database technologies.

Stonerebraker proposed a new type of database that offers high performance and scalability without giving up SQL and ACID transactions.

Some key points pointed out by Stonebraker are:

  • SQL is good.
  • Traditional databases are slow not because SQL is slow. It’s because of their architecture and the fact that they are running code that is 30 years old. He found that about 95% of the processing time is due to overheads such as locking the records, taking the data into the buffers etc.
  • NewSQL provides performance and scalability while preserving SQL and ACID transactions by using a new architecture that drastically reduces overhead.


A good example of NewSQL database is VoltDB which is an open source database developed by a company with the same name founded by Stonebraker.

Some of the performance figures of VoltDB are pretty amazing:

  • 3 million transactions per second on a “couple hundred cores”
  • 45x the performance of “a SQL vendor who’s name has more than three letters and less than nine”
  • 5-6 times faster than Cassandra and same speed as Memcached on key-value operations


A Video on OldSQL vs. NoSQL vs. NewSQL for New OLTP

The video was published on Jun 29, 2012 by Dr.Michael Stonebraker


NoSQL Databases Explained

10 things you should know about NoSQL databases

Big Data(bases): Making Sense of OldSQL vs NoSQL vs NewSQL



Enhanced by Zemanta

Be the first to comment - What do you think?
Posted by Mario1 - 27/12/2013 at 5:16 pm

Categories: Database   Tags: ,

DB2 For i Modernization

DB2 For  i Modernization


I noticed an interesting article on the IT Jungle website about DB2 For i Modernization and I have re-published it below for your convenience.


DB2 For i Modernization Gets Assist From RPG OA

Published: June 25, 2012

by Dan Burger

DB2 For i Modernization

English: Labeled (SQL) database icon with shortcut identification (Photo credit: Wikipedia)

If you don’t know something is broke, is that any reason not to fix it? That’s not a trick question. It’s another way of saying ignorance is bliss, which doesn’t carry a lot of weight in traffic court or database management. You might think you have a get out of jail free card because the IBM i operating system, with its built-in database management system, doesn’t need a database administrator, but that little marketing half-truth is exactly what “ignorance is bliss” is all about.

It’s true the IBM midrange computer doesn’t need a database administrator in the same way that Oracle, Microsoft SQL Server, or Sybase does. Those DBA positions are full-time jobs that add $100,000 annually to the cost of the databases, which are standalone products with standalone costs. But the thing that may have cost AS/400, iSeries, and IBM i users is that most have tended to ignore the database and as long as nothing was broke, there was nothing to fix.

For many companies, this scenario can be described as “so far so good.” But the future, with its insistence on open and distributed systems, may not be so kind.

This is not a story about the sky is falling. It’s a story about what you can do to prepare the wealth of information in your database for modern times, which means that data, in a lot of cases, needs to be accessible to more than just RPG and COBOL applications. It also means the DB2 for i database is capable of much more than what is currently being asked of it.

Just as modern RPG is modular, modern DB2 for i development is modular. In either case, modular is the opposite end of the spectrum from monolithic. Modular development makes code easier to reuse and, at the database level, it means code that has for years been written into each program can be written once and accessed by multiple programs. Obviously, less coding means apps can be written quicker.

The tallest hurdle companies run into if and when they consider database modernization is the amount of coding changes that must take place (just as it is with application modernization that goes beyond screen scraping).

But there are two things to remember about coding changes. The first is that technology has resulted in fewer hand-coded changes than there used to be. The second is that data-centric development removes data processing from the application code, which shortens the app dev process and makes business processes the focal point. When data processing is handled on the database side, it is written once. When it is handled on the application side, it requires coding in each app that is written.

If an RPG (or COBOL) program could be advanced in terms of openness and interoperability with only a few lines of code being changed, would anyone be interested?

Dan Cruikshank, one of the members of the DB2 for i dream team at IBM thinks we may be on the verge of finding out. Cruikshank has been modernizing databases for IBM midrange shops for nearly 20 years using SQL and data-centric programming. He says applying RPG Open Access to this task has made it much easier. There is code available that can help, and by reusing components Cruikshank transforms RPG I/O to an I/O call using the call-level interface, which has been on the platform for many years. The process eliminates rewriting code from scratch. Like anything new, it requires learning unfamiliar skills and new tools, but he says it does not require an army of programmers.

The benefits include reduced IT expenses, derived from enhanced developer productivity, improved application performance and scalability, which comes through reducing CPU consumption and disk I/O operations. Over and above all that is the benefit of keeping data on the IBM i platform.

Refactoring and re-engineering the DB2 for i database to support SQL brings into play a set processing capabilities (replacing single file processing) and functionality that includes automatic key generation with identity columns and sequences, improved online analytical processing capabilities, high-speed text searches, and query optimization. But at what cost? And what if you crash the database?

“In most heritage applications, only about 20 percent needs to be changed,” Cruikshank says. “Most companies are not aware of the 20 percent until someone like me points it out. All they see is the 100 percent and that appears to be overwhelming. Once they realize that only 20 percent or less of the application portfolio accounts for 80 percent or more of the resource consumption, then the mountain becomes a mole hill. Changing a handful of programs becomes a no-brainer decision. There are tools that help determine what needs to be changed within the 20 percent, and the truth is that only a few applications–the ones that are truly critical–that need to be altered.”

They are easily identified, he says. The first to look at are those that use open query files. Next, look for programs that are doing updates, inserts, and deletes, especially within a loop. These are the programs that are most likely to be shared with programs written in Java, NET, PHP, and all the other possibilities in a hybrid environment. Also consider altering programs with mass reads and/or writes in batch jobs.

“RPG OA comes in because all the RPG programs that are doing updates to the same file now go to a handler that calls the same service program that calls .NET or Java or PHP. With just a few lines of code change, everyone plays by the same rules–rules that are pushed down to the database.”

Before RPG OA, which was introduced in IBM i 7.1 and made compatible back to 6.1, the process of converting the familiar DDS-based programs to the DDL-based programs that are used in SQL typically involved a long program rewrite using either SQL stored procedures or external stored procedures using embedded SQL in the RPG program so it would recognize and use the new database access methods. Now Cruikshank writes an Open Access handler that transforms the traditional I/O to call a service program or a stored procedure.

Now you don’t need to worry if your newly coded database programs will lock horns with applications and crash the database. Cruikshank explains the benefit of a single relational database with two interfaces as allowing conversion to take place gradually rather than a flip-the-switch-and-pray process. DB2 objects created with DDS can be accessed with SQL and objects created with SQL can be accessed with the native record-level access interface. This allows SQL conversion and the added capabilities it brings without impacting existing applications.

There was a time in the mid-1990s when Cruikshank showed people how to make RPG or COBOL act like SQL, but since IBM decided SQL was the roadmap for the future he says companies are wasting programming dollars by not using SQL. (The SQL runtime engine is integrated and built into the IBM i operating system.) Companies have been slow to see the light. Most have yet to use SQL and some that did have translated RPG and COBOL to SQL on a one-for-one basis, meaning it still looks at one record at a time, which defeats the power of SQL for using sets of data and multiple tables. Cruikshank claims that companies that replace an RPG program that has multiple files with a single SQL view are realizing 10X to 20X improvements in performance. When a batch program is taking too long to run, he says, you can’t expect it to run fast when it reads only one record at a time.

To take a deeper dive into the RPG OA techniques, Cruikshank provides code that he uses to write handlers in two published articles. The first is titled Decoupling RPG database IO using Rational Open Access: RPG Edition, which you can find here. The second is titled Accessing SQL Functions Using Rational Open Access: RPG Edition, which can be found here.



Business Strategy Bumps Into Database Deficiency

DB2 for i: The Beating Heart of the IBM i Platform

Get Database Skills for Career ROI

DB2 on i: The Time, Money, and Risk of Modernization


The IBM system i DB2 is also known as DB2/400 and you can learn more about it with many Amazon books.


Click here for the DB2/400 books at Amazon UK

Click here for the DB2/400 books at Amazon US







Be the first to comment - What do you think?
Posted by Mario1 - 02/07/2012 at 4:03 pm

Categories: Computer Software   Tags: , , , ,

Some Computer Books Suggestions

Some Books Suggestions

Some good books that we have read recently are the following:

An Introduction to Web Application Development with IBM Websphere Studio It has been written by Gary Craig and Peter Jakab Mauny and provides all the information needed to learn Websphere Studio and pass the IBM Certified Associate Developer exam. The book includes also a CD-ROM with IBM Wesphere software. The book is published by IBM Press.

Java for S/390 and AS/400 COBOL Programmers by Phil Coulthard and George Farr. The Java syntax and JDK functionality is explained through comparaison with COBOL. The book introduces applets, Java Native Interface (JNI), Remote Method Invocation (RMI), Java Beans, Enterprise Java Beans (EJB), servlets, JSP, XML etc. It includes also an introduction to Visual Age for Java which is included in a CD-ROM. The book is published by IBM Press.

The Data Warehouse Toolkit It has been written by Ralph Kimball, who has designed data warehouses for many years (in addition to teaching and consulting). The book compares and contrasts the techniques used for developping on-line transaction processing (OLTP) with those required for data warehouses and demonstrates that they are often quite different. The book is published by John Wiley & Sons, Inc and includes a useful CD.

A complete guide to DB2 Universal Database One very good book written by Don Chamberlin (one of the co-inventors of the original SQL database language). The book supplies detailed and updated information without assuming prior knowledge of DB2 and SQL. You will find dozens of practical tips to save you hours of work in developing database applications. Morgan Kaufmann Publishers Inc.

e-RPG One very good book on the development of Web applications on the AS400 with RPG programming language is “e-RPG” by Bradley V.Stone – MC Press.

JAVA Programming – Advanced Topics A very good book by Joe Wigglesworth and Paula Lumby (both of the IBM Toronto Lab) –Thomson Learning. The book includes a CD full of sotware (e.g. IBM Visual Age for Java, Borland Jbuilder 3 and IBM DB2 UDP Personal Edition)

Enterprise JAVABEANS by Example This excellent book has been written by Henri Jubin, Jurgen Friedrichs and a team of specialists in IBM International Technical Support Organisation. It provides all the information insight and resources you need to begin building tomorrow’s Enterprise Javabeans applications – today. Prentice Hall.

XML and JAVA One very good book on the subject, that includes also good information on Servlets and three tier applications is “Professional Java XML Programming” by Alexander Nakhimovsky and Tom Myers Wrox Press

The Linux A-Z by Phil Cornes. It is a unique publication that guides the student up to the Linux learning ladder to gain a good understanding of the Operating System. It includes different parts on Basics, Administration, System Programming, Device Drivers and Internals. It is published by Prentice Hall.

Beginning Linux Programming by Neil Matthew and Richard Stones. One very good book that is an achievement in terms of its subjet coverage as well as its surprising depth. Published by Wrox Press

1 comment - What do you think?
Posted by Mario1 - 03/05/2012 at 3:09 pm

Categories: Computer Books   Tags: , , , ,