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... [More]
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... [More]
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.... [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 email@example.com with subject "Bootcamp question"
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... [More]
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
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:
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]
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... [More]
The discussion about the pros and cons of NULL in SQL has been raging for as long as SQL has been around.
In this post I won't dare wading into this controversy.
For those interested in such matters I point to the paper "Much ado about nothing" by C.J. Date.
Here, instead I want to discuss the implications of NULL, given that it is part of DB2's SQL.
What is NULL?
NULL represents the absence of a value.
Technically it is not a value... [More]
Fact: "John's birthday is Oct 12, 1965" If you know John and you know his birthday to be Oct 12, 1965, then never mind. If you know John and you thought his birthday was a different one, then there is a conflict. If you know John and you didn't know his birthday, then you just learned something new about John. If you didn't know John then you now know of his existence and his birthday.
How does this relate to databases, DB2 and SQL?
When ingesting new data into a table it is very common that you encounter... [More]
Some of you may have been using DB2 as far back or even longer than DB2 7.1, or DB2 UDB V7.1 to be correct. In that release we introduced SQL Procedures, and SQL Functions. When we shipped the features there was a bit of a confusion because everyone from sales to marketing to customers seemed have a dire need for a name of logical constructs we supported. It's just SQL we said. ANSI SQL comprises more than just SELECT. In includes IF, BEGIN.. END and so forth. All we do is implement the SQL standard! I learned and important lesson... [More]
When we started down the path of making DB2 compatible with Oracle Applications we wondered how to demonstrate the capability. It seemed unlikely that any application vendor would allow us to expose their Oracle Application to a public audience. Getting a custom application seemed not much more likely. But creating an artificial benchmark application would immediately cause suspicion that the workload is either too trivial or biased towards success. After a quite a bit of searching we found OpenBravo . This company sells an ERP product that is... [More]
It is very common nowadays for SQL statements to be generated by the application itself for by some productivity middle ware which abstracts the database.
On the server however, within routines, triggers or anonymous blocks most SQL is fixed and will execute just the way the definer of the object has specified it.
There are, however exceptions to this rule: Executing DDL statements such as CREATE TABLE or DROP INDEX While DB2 supports select DDL statements, generally DDL is not part of the SQL PL syntax Execution of... [More]
In my last post I described how to improve maintainability of routines using named parameter invocation .
Named parameter invocation made it easier to keep the many parameters of routines organized with out messing up the order.
Another way to make a routine invocation easier to read is by reducing the number of arguments I have to specify in the first place.
Let's go back to to that same example.
To underline that routines comprise procedures as well as functions I use an inline function this time.
CREATE OR REPLACE... [More]
DB2 for LUW supports functions with up to 90 parameters and for procedures the limit it 32000. While I hope few if any users actually reach these limits, routines with a dozen or more parameters are not uncommon. Let's take a look at a procedure invocation:
CALL total_compensation(salary, percent_bonus, stocks, total_comp);
Chances are the procedure's definition looked something like:
CREATE OR REPLACE PROCEDURE total_compensation(IN salary DECIMAL(9, 2), IN ... [More]
Data Archiving: best practices to improve database performance and reduce costs
Date: March 29, 2012 12:30 - 2:00 PM EST Presenter: Eric Naiburg, Program Director, Information Governance Solutions " When it comes to data, more isn’t always better. Users demand that
applications perform at their best, yet system complexity and data
volumes continue to grow. This causes application and database
performance to slow" I hope you will join us for the next DB2 Tech Talk, Eric will be talking about data archiving best... [More]
In this blog I last discussed how to generate unique timestamps and earlier I introduced a Java UDF to generate globally unique identifiers (uuid) . The former naturally produces timestamps, while the later produces a binary string. But what if what you need is a number? Of course both timestamps and binary strings can be represented as numbers. But these will be very long.For example a naive representation of a timestamp could be the number 20120303070659765345. This is too long even for a bigint. Certainly you would not want to... [More]
The Problem I've just finished debugging some quality assurance (QA) scenarios which failed just on Windows. The failure ended up being in the test case implementation rather than DB2. The kind of failure, however was interesting although I have seen the pattern repeatedly since my early days in DB2 development both internally as well as in customer code. Therefore I figure it's a good topic to blog about. Imagine a table recording events - perhaps for auditing, logging. Anything of the sorts: CREATE TABLE event(stamp TIMESTAMP NOT NULL... [More]
When I started leading the project to add Oracle's PL/SQL to DB2 it became quickly clear that perhaps amongst the top five features needed to claim a reasonable level of compatibility were autonomous transactions . An autonomous transaction ("#PRAGMA AUTONOMOUS" in PL/SQL speak) is a transaction which executes in total isolation from its invocation context. While this feature has seen a lot of uptake with the Oracle to DB2 realm I have rarely seen it used in a traditional DB2 environment. The reasons for that may be: Poor... [More]
Imagine you are running a website like online banking. Imagine further you want to get as close to zero downtime as possible. So you will invest into DB2 pureScale, HADR, or perhaps you choose a replication solution. All these capabilities will get you close, protecting you against unplanned outages as well as outages related to hardware and OS upgrades. What none of them do is helping you deploy application upgrades! Traditionally when you upgrade an application you will take a planned outage. Then you tear down the... [More]
I have taken over the hosting of DB2 Tech Talk which is very exciting. DB2 Tech Talk takes place, rain or shine, on the last Thursday of every month. 12:30 PM - 2:00 PM (Eastern Time) Here is this months topic Solving Complex Data Warehousing Problems with Advanced Tooling of InfoSphere Warehouse Advanced Editions Designed for technical practitioners like you, join us to find out how IBM InfoSphere® Warehouse Advanced Enterprise Edition can assist in simplifying the daily operation of managing complex warehouses. This Advanced edition adds... [More]
A major DB2 partner acquired a subsidiary a while back which sells an application that needs to me enabled from Oracle to DB2. The team is making good progress, but recently ran into a snag. There was a difference between Oracle and DB2 which caused DB2 to raise a run time error. With deeply nested routines which are being driven by an application homing in on the root cause was not trivial.
This is a prime example for tracing. Given a tracing facility the team could have attached to the application, traced the... [More]
One of the most popular blog entries on SQLTips4DB2 is the one on Aggregating Strings (October, 29 2010) . In this post I describe a means to sum strings into a a single comma separated text using XML. When I composed this post I was already working on a built-in function called LISTAGG . But somehow I forgot to blog about it assuming that first blog entry already contained its description. I must be getting old. So, without further delay here is a quick overview of the LISTAGG function introduced in DB2 9.7.4. We use the same sample data as... [More]
I didn't post a new blog entry past Monday because I had to prepare for a DB2 class I was teaching that Thursday. As part of this class I also wanted to provide some live demos of various SQL features. Traditionally my standard means of testing features on DB2 is the Command Line Processor (CLP) which you start with "db2". With DB2 9.7 however a new contender has arrived. The name of this shell is CLPPlus . So, for this class that I taught I choose CLPlus just to show it of to true blue customers. I am thrilled ! The first and... [More]
DB2 has had the FETCH FIRST n ROWS ( FFnR ) clause for many years.
FFnR is one of those tricky features that does actually exactly what it claims to do - surprise! A simple test: CREATE TABLE emp(name VARCHAR(10), salary INTEGER); INSERT INTO emp VALUES ('Joe' , 20000), ('Martin', 25000), ('Murphy', 30000), ('Sophie', 18000), ('Brett' , 22000), ('Joel' , 31000); Selecting the first three rows can be done like this: SELECT * FROM emp FETCH FIRST 3 ROWS ONLY; NAME SALARY ---------- ----------- Joe 20000... [More]
In this post I want to introduce a handy procedure which can be used to suspend a session for a specified amount of time.
A naive approach to wait is to simply execute a tight loop in a stored procedure such as this:
--#SET TERMINATOR @ CREATE OR REPLACE PROCEDURE SLEEP(seconds INTEGER) BEGIN DECLARE end TIMESTAMP; SET end = CURRENT TIMESTAMP + seconds SECONDS; wait: LOOP IF CURRENT TIMESTAMP >= end THEN LEAVE wait; END IF; END LOOP wait; END @ --#SET TERMINATOR ; VALUES CURRENT TIMESTAMP; 1 --------------------------... [More]
In my job I deal daily with colleagues, customers and partners around the world and even by family is spread out across three continents. So dealing with timezones is familiar. But things got even more complicated when a few years back Europe and the US diverged on dated when to switch to and from DST. I'm certainly not special and any global application needs to be able to translate time stamps from one timezone to another while obeying the latest rules (such as jumping forward an entire day in Samoa at the end of 2011!) For SQL and DB2... [More]