PA281185 0

DB2 Basics: Stored Procedures, Functions, Modules, Oh My!

DB2 has a number of objects where that use procedural SQL. IBM calls this SQL PL, not to be confused with PL/SQL or TSQL, both similar concepts. Much PL/SQL also works in DB2, if you have the Oracle compatibility vector enabled. The various types of objects have different places and purposes, and it can be useful to understand the use...

IanB 0

Monitoring Extent Movement Progress

Since DB2 9.7, you have been able to reduce the size of tablespaces using the statement: ALTER TABLESPACE USERSPACE1 REDUCE MAX Using this functionality, however, requires that the tablespace uses automatic storage and that it was created with the reclaimable storage attribute. Unfortunately, tablespaces created in DB2 9.5 or earlier will not have the reclaimable storage attribute, and it’s not...

DSC03515 0

Funny SQL I Have Seen Lately

I’ve run across a few strange SQL statements lately, and while the mistakes they make are not the most impactful, I thought I’d share how I think they could be better written. Unnecessary use of a Common Table Expression This query isn’t really wrong, it just doesn’t make much sense. Here it is as I discovered it: with sortsnap as...

IsolationLevels 0

DB2 Basics: Isolation Levels and Concurrency Phenomena

Isolation levels are such a critical concept to maximizing concurrency for transaction processing databases where ACID is so critical. What is ACID? ACID stands for Atomicity, Consistency, Isolation, Durability. This is a set of concepts that applies to any transaction processing system, whether it is a Relational Database Management System (like DB2) or not. ACID is one of the big...

3

Happy 6th Blogiversary to db2commerce.com!

It has been 6 years since I started to blog, and about four and a half years of weekly blogging! Over that time, I have published over 460,000 words in more than 410 posts! That’s 4-8 books worth of content! I’m a whole reference shelf! Nearly 50,000 of those words were written by 6 guest bloggers: Mike Krafick Ian Bjorhovde...

DSC06398 0

Calculating Row Size for a Table

There are several scenarios with DB2 where we need to know what the “worst-case” row length is. That is, if all varying fields are completely full and any LOBs are maximally inlined, how much space could a single row actually take up? SYSCAT.TABLES gives us the average row size, which is useful for some use cases, but not all of...

f8259cc09326948fe0d3a93567a9fcc2 3

How to Get a List of Local DB2 Databases

The database directory is something many of us use every day. It includes informaiton for connecting to databases, both local and remote, as well as additional information like alternate servers and filesystems where database directories reside. Getting just the local database names out of it may take a bit of practice. Please use the code here cautiously, after thorough testing,...

Screenshot_060816_111344_AM 1

The New IBM DB2 Knowledge Center

A new IBM DB2 Knowledge Center has been released, and search is actually better than before. Though there are still problems. There are some resources and things you can do to customize and make your IBM DB2 Knowledge Center experience better. And always remeber the local copy with really good search is an option, and a good one for those...

***UNDER STRICT EMBARGO UNTIL 00.01 ON 11 DECEMBER, 2013 GMT***DOCTOR WHO XMAS SPECIAL 2013 0

Deprecated, Discontinued, and Changed Defined for DB2

With each new release of DB2, there is a list of three different categories of old functionality that is somehow different in the new version. The use of these terms can be confusing if you don’t look at them every day. Discontinued Discontinued means that a feature or function is no longer available. If you try to use it, it...