The next DB2 Tech Talk is one that should be of interest to those that follow this blog. On April 10, 2014, Nick Ivanov, an IBM Senior Consultant, will present a talk called: SQL Tips and Techniques, Leverage the Power of SQL Those who follow this blog will already realize that SQL is a powerful language. The objective of this talk is to help you learn to use more of that SQL power. Join us to explore some of the less well-known features of SQL and see how they can help in some practical situations. We will also look at some common mistakes and... [More]
Motivation Have you ever received an error message that left you scratching your head? Of course you have. Matter of fact I for one cannot recall any software product that ever outright impressed me with the quality of their error messages. DB2's messages, overall tend not to be too bad compared to what I have seen with competitors when it comes down to regular SQL. But things get really interesting when you add SQL PL (or PL/SQL) to the mix. "My" product OpenPages is chock full of tens of thousands of lines of PL/SQL and recently... [More]
But first, what's up with Serge? OK, I haven't been posting for a long time, but I do have a good excuse. Some six months ago I switched jobs from being " SQL Architect for DB2 for LUW " to being " Chief Architect for OpenPages " . OpenPages is a product IBM acquired some two years ago that is leading in the eGRC (enterprise Governance, Risk, and Compliance) space. Coming from deep in the bowels of DBMS development covering a three tier architecture, Java and a host of Web UI interfaces is challenging, but just what... [More]
There is another DB2 Tech Talk coming up this week on Thursday June 20, 2013 at 12:30 PM EDT. Transaction system availability is always a key issue, and the all-new DB2 10.5 for Linux, UNIX and Windows includes a series of enhancements that make it faster and easier for you to ensure the level of availability that stakeholders in your organization expect. Join IBM architect Aamer Sachedina for an in-depth discussion of the enhancements in DB2 10.5 that help ensure 'always available' transactions. DB2 pureScale provides two major... [More]
Background Back in the early 1990s when I first started working on database language architecture, DB2 for Linux, Unix, and Windows was a new DB2 database manager (I believe we called it DB2 for common servers back then) and making a big jump in the SQL that it supported along with implementing the Starburst compiler with the research team from the Almaden Research Center. During that time, we started to make SQL more user-extensible with user-defined objects for procedures, functions and data types. The first kind of user-defined data type was... [More]
It's almost time for another DB2 Tech Talk. I think this will be particularly interesting for DBAs that have heard about BLU Acceleration and are craving more technical information about this exciting new technology. BLU Acceleration in DB2 10.5 for Linux, UNIX and Windows delivers results from data-intensive analytic workloads with speed and precision that is termed "speed of thought" analytics. Join IBM Distinguished Engineer and DB2 expert Sam Lightstone for an in-depth discussion of the all-new BLU Acceleration features... [More]
Are you writing SQL applications that need to be portable across DB2 platforms? Are you looking for information to help ensure your DB2 applications are portable? Back in June 2012, Serge posted an entry called " Family matters: The SQL Reference for Cross-Platform Development " where he wrote about the DB2 family and the availability of Version 4 of the SQL Reference for Cross-Platform Development . I want you to be aware of some related cross-platform summary information that is also available. A developerWorks article with... [More]
As your DB2 Tech Talk host, I want invite you to join the next DB2 Tech Talk to learn the technical details behind the recent announcement of DB2 10.5 for Linux, UNIX and Windows with BLU Acceleration software Distinguished Engineer and DB2 expert Berni Schiefer will lead us on a technical tour of the all new DB2 10.5 with BLU Acceleration software. You will learn about new features of this release such as: • BLU Acceleration, for “Speed of Thought” analytics Designed to handle data-intensive analytics... [More]
Introduction A table function, as you will likely know, is a function that returns a number of rows instead of a single scalar value. Table functions can therefore be invoked in the FROM clause of an SQL statement, while scalar functions get invoked in expressions. Table functions are nothing new to DB2. They have been around with C and Java implementations since DB2 V5.1. And in DB2 7.1 we made them available in inline SQL PL by using the RETURN statement as the body of the table function. The C and Java table function implementation however,... [More]
Background This week a co-worker converting an Oracle application to DB2 contacted me and requested a REVERSE function. REVERS() is a function which reverses strings by reordering all the characters in reverse order. For example: 'Hello World!' becomes '!dlroW olleH'. Not a terribly useful function within an application, generally speaking. However reversing strings can reduce contention on index pages when inserting rows into a table in ascending order. At any rate, I have learned over the years that conversions are much more successful when... [More]
Background Have you ever seen: "SQL0670N The row length of the table exceeded a limit of "<length>" bytes. (Table space "<tablespace-name>".)" When creating or altering a table DB2 imposes limits on the total worst case size of a row. This size depends on the page size of the tablespace in which the table resides: 4005 bytes in a table space with a 4K page size 8101 bytes in a table space with an 8K page size 16293 bytes in a table space with an 16K page size 32677 bytes in a table space with an 32K... [More]
Many organizations are struggling to comply with data security and compliance mandates, while also reducing costs. With the new row-permission and column-mask features, IBM DB2 10 takes security and ease of use to the next level. Join IM Information Management Chief Security Architect Walid Rjaibi for a deep dive technical discussion of the new Row and Column Access Control (RCAC) features in DB2 10 for Linux UNIX and Windows and InfoSphere Warehouse 10. Walid will also teach you proven methods to implement data security in the most... [More]
Intro Anyone who has ever attended a talk of mine on SQL compatibility has heard my claim that "to support Oracle applications in DB2 we had to add a completely new date-arithmetic library of functions since no two functions operating on date appeared to be the same in DB2."
Well, it appears PostgreSQL has its own set of functions yet again.
So, sooner or later a request for the DATE_PART() function in DB2 was bound to pop up.
Not having PostgreSQL available to test for an exact match, here is what I came up with:
The new PureData expert integrated systems are optimized for delivering data applications with simplicity and speed. Join the architect of the PureData transactional and operational warehousing PureData products for a look at these new offerings, which feature DB2 for Linux as a core technology. We will explain how which type of workload goes on which system, how to leverage patterns, factory-optimized scalability and much more to speed deployment, ease administration and reduce development efforts. Date: Nov 15th, 2012 Time: 12:30 ET More... [More]
Background When our team added compatibility for DB2 with Oracle applications we learned that no two functions were compatible between Oracle and DB2 as far as date-time arithmetic was concerned.
We ended up adding an entirely new library to achieve compatibility.
It appears the difference between DB2 and MS SQL Server is equally profound in this area given that I was recently asked to provide a match for the DATEADD() function. DATEADD() This TSQL is a bit of an odd one.
It takes three arguments: A "measure" The measure can be... [More]
In case you have been hiding under a rock: IOD 2012 is nearly upon us! So we figured it would be nice to present the next DB2 Tech Talk straight from IOD and take a pulse of what's hot and what's cool. Circle your calendars for October 24, 2012 12:30 PM ET - that's 9:30 AM in Las Vegas. As always you can register at www.idug-db2.com. See you there!
Background Roberto from Italy sent me the following question: Is there something like the STATS_MODE() function in DB2? He says STATS_MODE() is supposed to return the most frequently occurring value from a multi-set of values. A bit of rifling through the internet, steering clear of zones forbidden to me, reveals that STATS_MODE is an aggregate function similar to SUM() or AVG(). The function returns the most frequent value within a group. If there are two equally frequent values one of them will be picked with no specific rule. DB2 does not... [More]
Join Chris Eaton and myself for a discussion of DB2 Compression capabilities. DB2 has been supporting compression for several releases and we have
continuously improved the story and stayed ahead of the competition. He will compare DB2 10 for LUW to Oracle Database and other industry databases, explaining the features and advantages that help to maximize valuable storage resources. September 27th, 12:30 PM ET. Register: HERE
Motivation Yesterday one of my coworkers from India asked for my help in migrating the following SQL statement from Sybase to DB2. SELECT DISTINCT c1, c2 FROM t ORDER BY c3 When this statement is run in DB2 (assuming the appropriate definition of "T") the following error is being returned: SQL0214N An expression in the ORDER BY clause in the following position, or starting with "C3" in the "ORDER BY" clause is not valid. Reason code = "2". Why does DB2 raise this error? The reason is simple: It's not... [More]
In this episode of the DB2 Tech Talk Series Cliff Leung and Holly Hayes will discuss how you can use Optim Query Workload Tuner and Optim Performance Manager to easily get to the bottom of common database problems. For those who have not looked at OPM and OQWT in a while, prepare to be amazed. We hope to see you at this very informative Tech Talk on Thursday August 30th at 12:30PM - 2PM ET!