Hi all !

i want to know wats the difference between VAr Char and Long Var formats in db2 .



Cheers,
Gk.

LONG VARCHAR is treated like a LOB in DB2 and should no longer be used (use CLOB or BLOB instead). At some point it may actually be removed.

Generally, a LOB or LONG VARCHAR should be avoided if the data will fit into VARCHAR or VARCHAR FOR BIT DATA because LOB's and LONG VARCHAR do not use bufferpools and require synchronous I/O for reads and writes. But if the VARCHAR is large enough, you need to make sure the tablespace is the proper size (up to 32K) so that the row will fit in page size.

so what happens if we have LONG VARCHARs in tables in 4k-page TS? I am working on improving performance of our prod. databases and found a table containing data of 32000 and 14133 B (per column, not record). This table is in 4k-pagesize TS USERSPACE1 same as all other tables.

How much do you think it affects performance?

The performance hit is the same as it would be if defined as a LOB (does not use bufferpools and requires synchronous I/O for read and write).

At some point Long Varchar support will be removed from the product, but not sure when.

actually my concern was not the using LONG VARCHARs intead of LOBs, but the fact that records/columns lengths are > than 4k=TS page size. How much does it affect performance? Is moving those tables to large TSs a must or a recommendation?

when you say "does not use bufferpools and requires synchronous I/O for read and write" do you mean the access to this particular table and its indexes will not use BPs?

For LOBs and Long Varchar, the data is located in a separate area in the tablespace (even if you use SMS or use DMS and don't specify different tablespace for LONG data in the create table statement). So the rest of the data and indexes do use the bufferpools, but not the LOB or Long Varchar data. Using Long Varchar could have noticeable negative impact as it definitely takes longer to do synchronous I/O with LOBs and Long Varchar than if the data was in a bufferpool with a high bufferpool hit ratio.

When you say the column is greater than 4K, does that mean the actual data, or just the column definition of the Long Varchar. All Long Varchar data is treated as a BLOB and cannot use bufferpools, no matter how short or how long they are. But things have changed a little bit with 9.7 and inline LOBs (I don't know if this applies to Long Varchar).

Long Varchar has been deprecated by IBM. This means that support for it is slated to be removed from the product at some future date (which may not be determined yet) and you are being put on notice.

I ment the whole row length (defined when table was created) and even data in some cols longer 4K (some actual data is 32000 B long - those are for storing XML char strings). Pls don't tell me I should create XML-type columns for them - it's a different story

Why to place LOBs in large or different TSs if the rest of the data in the same row goes to BPs anyway? Those large TSs would not be using BPs nonetheless.

Should it be a concern that some long records (actual data in them) will occupy the whole page (so we would have 1 row per page) or even few pages sometimes? Does it affect performance? Should I move those table to TS with bigger page size (8K,16K,etc)? What is recommended number of records per page?

Thanks in advance

The optimum number of rows per page depends on your application and database design. Since DB2 does all I/O and memory storage for bufferpools at the page level, there would some benefit to having multiple rows per page if your application needs multiple rows at one time and they happen to be on the same page. This brings various other topics into discussion such as clustered indexes (which determines what page a data rows is stored on during an insert) and reorgs.

I do not want to get into a long (no pun intended) discussion about Long Varchar because it is deprecated.

But for LOB columns it is best to place them in a separate tablespace (LONG IN when defining a table in a DMS tablespace) so that you can use File System Caching (by the operating system) when you define the Long tablespace. No File System Caching should be used for all other tablespaces since they are cached in DB2 bufferpools.

got it. thank you, Mr.Feldman