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!
Motivation Recently one of our major business partners submitted the following feature request: " Please make all the SQL in my SQL Routines dynamic! " Why, I asked, would you want to do that? If you make the SQL dynamic at lot of bad things would happen to your SQL: No dependencies are recorded anymore. So you do not know anymore with which objects the routine interacts SQL executes under "invoker's rights" That is the invoker of the routine or trigger would need the authorization to execute the SQL within. There will be... [More]
This two-part Tech Talk series is a great way to start your preparation for the all new DB2 10 for Linux, UNIX and Windows 610 Fundamentals certification exam. Part one is on July 26th, with registration at bit.ly/tt2012july. Part two is August 2nd, with registration at bit.ly/ttpart2. Certification is an excellent way to update and validate your DB2 skills, expand professional opportunities and meet employer education objectives. We hope to see you at this very informative Tech Talk! Register HERE.
Background The DB2 family is comprised of three products: DB2 for Linux, Unix and Windows (LUW) and Infosphere Warehouse That's the product this blog mostly deal with and within which I have grown up as a developer. Some folks still like to call it "UDB" which is wrong on so many levels, but we know what you mean anyway. DB2 for z/OS That's the product that runs on the mainframe's native OS. (Not to be confused with DB2 for LUW running on z/Linux). Some folks like to call it the "big" DB2. Again wrong, but I won't hold it... [More]
Please join me on June 21, 2012 12:30 - 2:00 PM ET for our newest installment of DB2 Tech Talk. This Tech Talk continues the "deep dive" on the new DB2 10 and InfoSphere Warehouse 10 products. Matthias Nicola of IBM labs covers the bi-temporal data management features which let you manage data at past or future points in time, consistently and cost-effectively. Replay at: www.idug-db2.com/
The IBM Canada Lab in Toronto is the biggest Software Lab in Canada.
As part of our engagement with Universities and also to vet potential new hires we have an extensive program for interns. Once in a while we ask them to sum up their experiences in videos. A few years ago the following gem (http://www.youtube.com/watch?v=uc2O9p3btqI) was produced which I rediscovered a couple of days ago during a sleepless night . Enjoy the video .
Motivation There are multiple ways in which DB2 supports recursion in SQL. One of which is recursion using the ANSI SQL recursive UNION ALL approach. The other approach is using the CONNECT BY clause. Actually I like to add explanation of recursion into my "SQL on Fire" talks because the topic never seems to get old. In this blog however I will not talk about recursion in SQL itself. Instead I want to answer a frequent email question about how to achieve recursion in SQL PL (or PL/SQL). Fibonacci number A commonly used example when... [More]
Introduction Recently I discussed the motivation behind using anonymous blocks. The reasoning was centered around the need to execute complex scripts on the server without being able or willing to define a routine in the database's catalog. Inside of an anonymous block you can then define all the logic flow you need. You can also declare local variables to hold temporary data. DB2 10 expands beyond that by allowing you to declare local types and local procedures. Local types When ARRAY types were introduced in DB2 9.5 we discussed whether there... [More]
Register today for May 31, 12:30 EST to 2PM EST Join me in this DB2 Tech Talk where I will provide an update on DB2's Oracle Compatibility features. I will: Give an overview over the architecture and feature set Provide an update about what has been added since DB2 9.7 Do a quantitative assessment on the success of the strategy Look at a case study. This technical tour is the third DB2
Tech Talk on the DB2 10 and InfoSphere Warehouse 10 product releases.
Additional webcasts will be offered throughout 2012 to provide an
Motivation Remember the days when DB2 re-entered the TPC-C fight after a long hiatus? The time was DB2 8.1 FP4. I seriously do not remember years.. Time passes in releases. While my colleagues were tuning code path, bufferpools disks my team was looking at something more fundamental. What can we do to make SQL as efficient as possible for TPC-C. And ideally how can we make SQL more efficient for any class of OLTP workload. In other words how can we pour the most bang into the least SQL for a typical OLTP transaction? The result was what we... [More]
Register today for May 11, 12:30 EST to 2PM EST Learn about the new storage optimization features in the newly available DB2 10 product. We will cover three areas including: Adaptive Compression which allows you to reach higher compression ratios with DB2 10 than ever before. Multi-Temperature Data Management which configures your database so that your most frequently accessed data resides on fastest storage Workload Management , which
provides the ability to treat work differently based on the data touched. This technical tour is the second... [More]