Comments (9)
  • Add a Comment
  • Edit
  • More Actions v
  • Quarantine this Entry

1 dantress commented Permalink

Why isn't this procedure shipped by default with DB2 ?

2 SergeRielau commented Permalink


Wouldn't be the first function that starts in this blog and ends in the product.
Consider it an alpha release.

3 0UNX_현_지수 commented Permalink


Thank you for sharing useful infomation.
I konw LOB types are not include in the page limit.
Why function include LOB types?

4 SergeRielau commented Permalink

현 지수,

While it is correct that LOBs are not part of the row, DB2 must maintain "pointers" to these LOB values.
You can see in the "loblength" variable assignment how the pointer gets longer as the maximum size increases.
A 2GB LOB can still consume over 300 bytes within the row..

5 Feng WanLi commented Permalink

I READ THE IBM DB2 INFORMATION CENTER, THAT SAY ,THE DECIMAL IS " p/2+1 bytes, where p is precision " .

6 SergeRielau commented Permalink

Feng Piao,

You are correct. Also the code in question is not safe in compatibility mode since division of integers returns a DECFLOAT in NUMBER mode.
I have updated the code in the article to
TRUNC(length / 2) + 1
Thanks for finding the bug.

7 Kpfitzgerald commented Permalink

Hey Serge and Rick,

So I added the function to my 10.1 DB (hybrid OLTB) on windows 7 box, (internal dev system), and finally figured out how to do this on Data Studio 4.1. All good!
I run the query to get a listing of the row sizes, and walla! all 0's. Not sure if there is a casting issue or an encoding issue (UTF-8). Im pretty new to functions (only done 3 in my lifetime), and DS4.1 is really not being very user friendly on finding the "opportunity".
any Words of advice, or insight you can pass would be greatly appreciated. If you need further details or need to move this to a forum or email let me know.

8 Kpfitzgerald commented Permalink

Well i got it working... not sure how or why... My best guess is running statistics after deploying the UDF. I had stats prior, not sure if its a MUST to run after seems to be the only real fix i can think of, i did mess with the deployment parameters also, but that was just removing the debug from the deploy and using db2admin instead of anther user account with admin rights.

9 SergeRielau commented Permalink


This is odd.I don't think any of the potential reasons you guess at (UTF-8, DataStudio or stats) can have any impact on the function.
Reason being that it is written in SQL PL and thus datastudio won't be abel to do any harm to it.
All it does is to pass the text along to DB2.
The values the function looks at are also the defined lengths and not anything gleaned from statistics.
Perhaps it was function overloading that did you in? A previous attempt at the function with similar signature?
Glad you got it to work though.