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]
Motivation A few years ago I sat in a customer briefing and the customer representative summed up their database philosophy as "pack rats". What she meant was that the data warehouse is ever growing at a high rate and nothing ever gets deleted. Over the years DB2 has introduced numerous features which allow for ever growing databases such as: Large table spaces These alone allow tables to reach TB sizes Large Row iDs Large row ids allow more rows in a table Range partitioning This feature allows you to compose composite tables where... [More]
Claims When IBM announced DB2 10 for LUW on April 3 claims were made that DB2 10 is compatible with 98% of the source code of applications written for Oracle . Claims are easy to make, but hard to back up. IBM has a tendency to be conservative when making claims and I personally am even more so when I have to stand up in front of a customer and defend them. As the saying goes: Don't trust statistics you haven't developed yourself. So when I started updating my presentations on Oracle Application Compatibility to DB2 10 I decided to do my own... [More]
Prelude I have have been engaged in discussions on how to use DB2 for LUW for as long as I have been employed with IBM. Until not too long ago, before I started blogging, the primary platform for such discussion was usenet. And one of the very oldest debates I recall was with Bernard D. For at least a decade he has been tirelessly trying to convince us to extend DB2 for LUW to support comparisons of "row value constructors" (rvc). In fact the oldest record I can find on Google on this debate dates back to October 21, 2000. Well,... [More]
Would you like to get a four day, expert guided, expedition into DB2 10 for LUW? If so, then strap on those boots and sign up for one of the many events happening soon around the world! A the end of the class you can also get your DB2 10 certifications. For more information and to register go here . If you don't see a boot camp in your neighborhood, don't despair. Instead either come back to check once in a while. You can also contact firstname.lastname@example.org with subject "Bootcamp question"
Motivation Have you ever had the task to write a script for a database? Often users write scripts to monitor the database in a host language such as PERL or bash. But these host languages have some downsides: The language environment must be installed on the server. This creates a dependency on a specific environment and one more moving part The environment may not be portable. For example if you move the database from Windows to Linux a shell script is unlikely to work unchanged If a lot of data needs to be processed in the script performance... [More]
Motivation As part of my job I work in a group called the SQL Language council (SLC). The purpose of that group is, roughly three fold: Ensure any SQL added to DB2 (and to some extend IDS, Netezza and Derby) remains compatible between the products. Bring SQL extensions forward for standardization by ANSI and ISO Ensure that SQL Extensions are added with a vision that extends beyond the immediate product or business requirement. Needless to say meetings of the SLC can be quite exciting as different code-bases clash. I like those. On the flip... [More]
Register today for April 26, 12:30 EST to 2PM EST Join us for a technical tour of the
details behind these IBM DB2 and IBM InfoSphere Warehouse new product
releases as announced on April 3 rd . This technical tour
will cover powerful new features including: Storage Optimization advances ,
such as Adaptive Compression and Multi-Temperature Data Storage,
which are designed to optimize both the storage environment and
system performance. SQL Compatibility enhancements that make it even faster and easier to migrate from Oracle... [More]
Background In January 2011 I posted Inline SQL PL vs compiled SQL PL . One of the points I made in this entry was that inline SQL PL is preferable for simple SQL PL logic because it is faster. This implies of course that compiled SQL PL has some headroom when it comes to performance improvements. There is always room for improvement just about anywhere, so it can be interesting to talk about what motivated investments in this area. In this case an IBM partner was enabling an Oracle application to DB2. Most times we find that applications... [More]
The DB2 for LUW team has been busily driving down the cost of enabling Oracle applications since that effort started with DB2 9.7. Every DB2 9.7 fixpack up to DB2 9.7.5 has contained features to incrementally increase the level of compatibility allowing users to break free of Oracle. Now, finally it's time to unleash DB2 10 with yet another set of goodies. Here are some: Local Types This feature allows PL/SQL and SQL PL blocks to locally declare types for consumption in variables within the BEGIN .. END block. Previously when such type... [More]
Motivation Some years ago I visited a business partner to help them overcome some performance problems on DB2. They had a rather simple workload for quality assurance test which ran on a simple Windows PC. On another DBMS that workload executed without a problem and with acceptable throughput. On DB2 however that same workload completely overwhelmed the machine and throughput was, frankly, abysmal. Needless to say the partner was not too amused. We quickly discovered that the CPU was pegged at 100%. We further discovered that DB2 saturated the... [More]