SQL Tips for DB2 LUW

Featured Blog Entries

All posts

Size matters: A handy routine to compute the defined row size of a table

Comments (10) Visits (26666)

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]

Tags: table row width length size sql0670n 670 sqlcode pagesize getrowsize

DB2 Tech Talk: Inside Row and Column Access Control for DB2 10 for LUW

Visits (7105)

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]

Tags: db2 fine access rcac talk column tech row private virtual control database grained

Fractions of time: A PostgreSQL compatible DATE_PART function

Visits (9979)

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: ... [More]

Tags: extract doy epoch date_part millenium decade century postgresql dow

DB2 Tech Talk: Deep Dive into the new PureData expert integrated systems!

Visits (6280)

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]

Tags: expert talk tech integrated systems appliance puredata db2

Migrating from TSQL: The DATEADD function

Comments (5) Visits (24083)

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]

Tags: sql timestamp microsoft dateadd sybase server tsql arithmetic datetime

DB2 Tech Talk: What's Hot from Information on Demand Conference

Visits (5020)

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!

Tags: las iod 2012 vegas

Who's the most popular kid on the block?

Comments (8) Visits (9247)

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]

Tags: by frequent group olap values stats_mode

DB2 Tech Talk: Compression Comparison DB2 vs. Oracle Database and The Rest

Visits (6811)

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

Tags: index xml temp lob ratio adaptive row oracle compression page

The trouble of combining DISTINCT with ORDER BY

Comments (4) Visits (58731)

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]

Tags: olap by order distinct sybase row_number over

DB2 Tech Talk: Best Practices in Database Application Performance Management; August 30 2012

Comments (2) Visits (6895)

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! Register HERE.

Tags: optim talk query tech manager perfromance tuner

Blogs