SQL v. NoSQL: We've been here before

Recently Craig Mullins was reflecting on the 'war" between object databases (ODBM) and relational database technology (RDMS) 15-20 years ago and how it parallels the current "conflict" between SQL and NoSQL big data database systems.

 

One interesting thing about NoSQL is that there are different categories of NoSQL databases based on processing needs. A list of these categories with descriptions can be found at nosql-database.org.

According to this article published in June, MongoDB is the clear leader in document-oriented databases. The leaders in big table style databases are Cassandra and Apache Hadoop/Hbase. Neo4J is the undisputed leader with graph databases, and Riak has emerged as the leader in key-value stores.

Getting back to Craig's post, he's right when he says we've seen this before. New technologies come along, and a lot of them have value, but IT departments cannot and will never wholly discard a proven solution for every buzz-generating thing that emerges. As Craig puts it, it's "a good idea to remain skeptical of a wholesale replacement of relational" databases.

Obviously big data is important and necessary, but that doesn't make operational data any less vital. Businesses clearly see the need to bring the two together. As a result, the lines are blurring as traditional relational database vendors integrate NoSQL into the RDMS. DB2 11, for instance, supports simple integration of Hadoop with operational data. And with IBM introducing DB2 for z/OS table functions to access Hadoop data with DB2 data, I believe Apache Hadoop will be important going forward.

As you can imagine though, there are many different ways to integrate operational data with big data. If your strategy is to move data between DB2, Netezza and Hadoop, you may want to look at the Cloudera distribution. This piece from IBM developerWorks tells you how to get started.

 

Naturally, it won't be just SQL or NoSQL, but a combination based on individual business needs that will ultimately result in big data and analytic data being accessed together with operational data.

Has your company started a big data project? What are some challenges you are facing with enriching your operational data with big data?

Using Spatial Data

Following up on my post about on spatial data support in DB2 for z/OS, this week we'll cover how to create a table and index containing spatial data. While you read along, you might want to view this visual, a sample application delivered with ArcGIS by ESRI.

Once IBM Spatial Support for DB2 for z/OS is installed, these objects are defined in DB2:

• Stored procedures: When you request a spatial operation (e.g., importing spatial data), Spatial Support invokes the appropriate stored procedure to perform the operation.
• Spatial data types: A spatial data type must be assigned each table or view column that is to contain spatial data.
• Spatial catalog tables and catalog views.
• A spatial grid index, so that you can define grid indexes on spatial columns.
• Spatial functions: These work with spatial data in numerous ways (e.g., determining relationships between geometries, generating additional spatial data).
• Definitions of coordinate systems.
• Default spatial reference systems.
• Two schemas: DB2GSE and SYSPROC.

As a DBA, I can imagine I'd want to include spatial data in table columns or create indexes to ensure that queries are running efficiently. To learn about these and other uses of spatial data, see the IBM Spatial Support for DB2 Version 9.1 for z/OS User’s Guide and Reference.

(Notes: The DB2 LOAD utility will fail if a spatial grid index is created on the target table. Before running this utility, you must drop the spatial grid index by using the ST_drop_index stored procedure. The spatial grid index can be recreated after running the LOAD utility. Some utility support -- Reorg Tablespace , Reorg Index, Rebuild Index and Check Index -- was introduced with DB2 10. Details are here. Utility support for DB2 9 is enabled through APAR PM35200.)

A sample install verification program (IVP) job can be found in the install sample library SDSNSAMP(DSN5SCMD). This job contains statements to create tables and call sample programs that use the spatial stored procedures to create/drop indexes as well as enable/disable spatial support.

Once Spatial Support is installed, these data types can be used:

 

ST_Point

ST_LineString

ST_Polygon

ST_MultiPoint

ST_MultiLineString

ST_MultiPolygon

ST_Geometry

 

In this example, a ADDRESS table is created that includes a LOCATION column of type DB2GSE.ST_POINT:

           CREATE TABLE ADDRESS ( ..., LOCATION DB2GSE.ST_POINT, ...) ;

 

To help improve performance, a “spatial grid index” can be created on this column using the SYSPROC.ST_create_index stored procedure. As I noted in the previous post, standard DDL CANNOT be used create an index on spatial data.

This sample calls the ST_create_index stored procedure to create the LOC_IX index on the LOCATION column in the ADDRESS_TABLE table:

CALL SYSPROC.ST_CREATE_INDEX( NULL, ADDRESS_TABLE, LOCATION , NULL, LOC_IX,NULL, 10.0, 20.0, 35.0, ?, ?)

The DB2 optimizer considers a spatial index for use when a query contains one of these functions in its WHERE clause:

• ST_Contains
• ST_Crosses
• ST_Distance
• EnvelopesIntersect
• ST_Equals
• ST_Intersects
• ST_Overlaps
• ST_Touches
• ST_Within

 

Sample SQL -- List the address found in the Chicago Loop:

SELECT address_line1, City, State, Zip

FROM address_table AS a

WHERE ST_Within(a.location,:Chicago_Loop_Area) = 1

As I mentioned in the previous post, there's little available information on managing spatial data in DB2. However, this IBM developerWorks piece is very informative. It also includes links to additional resources.

 

Becoming a DB2 'Expert'

As I noted in my previous post, Susan Visser blogs about DB2 and related topics on IBM developerWorks. She recently wrote about becoming a DB2 evangelist:

 

                "I’m sure many people have mixed emotions about the prospect of speaking at a conference because public speaking is still one of the number one fears in the world. Even people who you think are natural at speaking in front of thousands of people have fears. ...

                "This fear can be overcome, and thanks to the DB2Night Show’s 'DB2’s Got Talent' competition, you can slowly build up the skills you need to create a presentation that conveys content you want to share and to speak in front of others. You’ll get feedback, you’ll be recorded, and you can learn from your mistakes and do it again. I can’t think of a better way to build presentation skills."

DB2 evangelists, or experts, give presentations (and blog), but that's not how they got to be experts. They got to be experts by using the product -- just like you. DB2 is a great database. It's also an incredibly broad discussion topic. None of us knows it all. If you work on DB2, you most likely possess knowledge that, when shared, can benefit other DB2 users. If you've thought about sharing your experience with other DB2 professionals but weren't sure how to get started, here's a great way to jump in. Go to Susan's blog and read more about the contest.