Midrange News for the IBM i Community


Posted by: Bob Cozzi
Rogue Programmer
Cozzi Productions, Inc.
Chicagoland
Anyone using LOBs (CLOB, BLOB) in RPGIV?
has no ratings.
Published: 25 Aug 2011
Revised: 31 Jan 2013 - 4075 days ago
Last viewed on: 28 Mar 2024 (9932 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

Anyone using LOBs (CLOB, BLOB) in RPGIV? Published by: Bob Cozzi on 25 Aug 2011 view comments(3)

I started using a BLOB about a year ago to store a signature captured from a Topaz signature capture device. It works fine. Then recently I started using a CLOB instead of a large VARYING field.

Anyone else using anything like this and have any tips they'd like to share? I'm wondering if there's a storage difference if I use a VarChar(32766) vs CLOB(32766) if I include the ALLOCATE(xx) keyword on both types of fields? It would be just like IBM i to store the DB2 values identically in the database and yet make it more difficult to use the CLOB than a VARYING (VARCHAR) field. Wink

I started using a BLOB about a year ago to store a signature captured from a Topaz signature capture device. It works fine. Then recently I started using a CLOB instead of a large VARYING field.

Anyone else using anything like this and have any tips they'd like to share? I'm wondering if there's a storage difference if I use a VarChar(32766) vs CLOB(32766) if I include the ALLOCATE(xx) keyword on both types of fields? It would be just like IBM i to store the DB2 values identically in the database and yet make it more difficult to use the CLOB than a VARYING (VARCHAR) field. Wink

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: DaleB
Premium member *
Reading, PA
Comment on: Anyone using LOBs (CLOB, BLOB) in RPGIV?
Posted: 12 years 7 months 3 days 18 hours 54 minutes ago

Not sure about the actual storage method, but they're not really intended to be used for the same thing.

Maximum size of a VARCHAR is just over 32 KB. Maximim size of a CLOB is 1 byte less than 2 GB. You can use CLOB's for 32 KB or less, but for more than a little beyond 32 KB, VARCHAR isn't an option.

There are other differences, such as the ability to use locators for CLOB's, so that you don't have to retrieve the entire thing at once. Or to use file reference variables to load the CLOB, instead of handling the data directly.

Granted, declaring an alpha field with VARYING for a VARCHAR is simple. If you'll only ever need 32K, and you don't have to load that from a stream file, that's the way to go. In your example, the CLOB has overhead that you don't need (SQLTYPE and all that). But for other applications...

Posted by: DaleB
Premium member *
Reading, PA
Comment on: Anyone using LOBs (CLOB, BLOB) in RPGIV?
Posted: 12 years 7 months 3 days 13 hours 3 minutes ago

Did some playing around. Created tables with VARCHAR(25), and with CLOB(25).

Tables  with VARCHAR show field length 25, buffer length 27. Tables with CLOB show field length 25, buffer length 35.

VARCHAR is exactly as you'd expect, a 2-byte integer representing the length, and 25 characters of the string. (Which is the same as a 25A VARYING in RPG.)

When you look at a CLOB, it's very different. All of my rows have 19 bytes of x'00', then the word '*POINTER' followed by eight spaces, which adds up to 35 total bytes. Presumably there is an actual pointer, that we can't see, that points to the length, the content, and possibly other attributes. When you query it, you get the same thing, just the word '*POINTER'; needed to do CHAR(clobfield) to see the text.

35 bytes appears to be the magic number. Tried a CLOB(10), CLOB(40), and CLOB(400000), and they all had the same 35 bytes in the row. The field length shows the CLOB's length, but the buffer length is always 35.

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: Anyone using LOBs (CLOB, BLOB) in RPGIV?
Posted: 12 years 7 months 3 days 12 hours 50 minutes ago

Right LOB's store their data someplace other than "in" the database record therefore there has to be a pointer to that data. That pointer is stored in the file along with other things, perhaps length, perhaps offsets to the allocated data (which is sized by the ALLOCATE keyword) and another offset to the balance of the data. I was educated on this once, but never used it and forget specifically what's going on.