Description fields in Informix databases

As Informix does not support varchar-fields over 255, how would you recommend saving descriptions, that usually are under 255 but sometimes can be over 1 K long. Using a fixed 2K field is a possibility, but it does waist a lot of space. I would be most grateful for any suggestions. Lauri Pietarinen -- ====================================================================== Lauri Pietarinen tel +358-9-2534 4632 AtBusiness Communications Oy fax +358-9-2534 4601 Itälahdenkatu 19 mob +358-50-594 2011 00210 HELSINKI mailto:lauri.pietarinen@atbusiness.com Finland http://www.atbusiness.com ======================================================================

One way is to have a field in your table that is a foriegn key to another table. That table could have three fields, the primary key, a sequence number, and the VARCHAR(255) field. Then split up the description field into 255 byte pieces and put them into this table using the sequence number to keep them in order. Not very pretty, but it will do the job if you're that worried about space. Another option which is probably more cost effective is to go ahead and make them 2K fields and buy another disk! :) -- # unrm / ksh: unrm: not found # man cpio Sent via Deja.com http://www.deja.com/ Before you buy.

Lauri Pietarinen wrote: > > As Informix does not support varchar-fields over 255, how > would you recommend saving descriptions, that usually > are under 255 but sometimes can be over 1 K long. > > Using a fixed 2K field is a possibility, but it does > waist a lot of space. And a poor choice since the IDS pagesize on most platforms is 2K so that your record will span 2 pages reducing I/O throughput by 50%! I recommend a comment sub-table with the main table's primary key, a smallint sequence number, and a comment field which can be a reasonable fixed length char (say 75 characters so each row will represent one line on displays with fixed fonts) or a varchar (255,0). The fixed size records will be slightly more efficient. This scheme has many advantages that you may or may not be able to make use of. First, if one day the commentary should grow to 4K or more you are all set. Second, you could add a datestamp or timestamp to the commentary table and keep and display a history of commentary. Third, you can add a userid and present comments by commentator. And on and on, you see where I am going. This is far more extensible than any commentary embedded in the master table can be without requiring frequent code maintenance. Art S. Kagel

IDS.2000 (9.2) supports LVARCHAR of up to 2K bytes. Lauri Pietarinen wrote: > As Informix does not support varchar-fields over 255, how > would you recommend saving descriptions, that usually > are under 255 but sometimes can be over 1 K long. > > Using a fixed 2K field is a possibility, but it does > waist a lot of space. > > I would be most grateful for any suggestions. > > Lauri Pietarinen > > -- > ====================================================================== > Lauri Pietarinen tel +358-9-2534 4632 > AtBusiness Communications Oy fax +358-9-2534 4601 > Itälahdenkatu 19 mob +358-50-594 2011 > 00210 HELSINKI mailto:lauri.pietarinen@atbusiness.com > Finland http://www.atbusiness.com > ====================================================================== -- Madison Pruet =========================================== Enterprise Replication Product Developement Dallas, Texas Informix Software ===========================================

Thank you for your help! So the choise would be between LVARCHAR and a separate table. I am a bit afraid of TEXT and BYTE, as we will be using ODBC. How much space do they take? ====================================================================== Lauri Pietarinen tel +358-9-2534 4632 AtBusiness Communications Oy fax +358-9-2534 4601 Itälahdenkatu 19 mob +358-50-594 2011 00210 HELSINKI mailto:lauri.pietarinen@atbusiness.com Finland http://www.atbusiness.com ======================================================================

In article <386AFB81.770C4867@atbusiness.com>, Lauri Pietarinen <lauri.pietarinen@atbusiness.com> wrote: > Thank you for your help! > > So the choise would be between LVARCHAR and a separate table. > I am a bit afraid of TEXT and BYTE, as we will be using ODBC. > How much space do they take? How much space do what take? TEXT and BYTE columns, I believe, are both stored in blobs, which are allocated in blocks, so it depends on your block size. Probably 2K per record. LVARCHAR would take the lenght of the string that's stored plus 2 bytes (educated guess, there. VARCHAR takes strlen + 1 byte to hold the length of the string. The farthest you can cound with 1 byte is 255, hence the limit. To increase the limit, I would guess you'd have to increase the var to hold the length.) The separate table will hold the length of each line + 1 + 4 + 4 bytes, and you will have an extra 4 bytes in the parent table. Plus the size of the index, which would be, ummm... err... negligible. This, of course, assumes that every record will have a description. -- # unrm / ksh: unrm: not found # man cpio Sent via Deja.com http://www.deja.com/ Before you buy.

mars1972@my-deja.com wrote: > > In article <386AFB81.770C4867@atbusiness.com>, > Lauri Pietarinen <lauri.pietarinen@atbusiness.com> wrote: > > Thank you for your help! > > > > So the choise would be between LVARCHAR and a separate table. > > I am a bit afraid of TEXT and BYTE, as we will be using ODBC. > > How much space do they take? > > How much space do what take? TEXT and BYTE columns, I believe, are both > stored in blobs, which are allocated in blocks, so it depends on your BLOBs take up 56 bytes in the 'HOME' row plus the space taken by the BLOB itself. If the BLOB is stored in tablespace the takes up exactly the space it needs (add some overhead since the page holding the tails of BLOBs that don't fit on a page will only fill to 2/3 full initially to allow the tails to grow). If the BLOB lives in a BLOBspace the then space depends on the BLOBpage size you set when you created the BLOBspace (at least PAGESIZE for your system, 2k except AIX and WinNT - 4K). BLOBspace BLOBs take up only complete pages so a 3K BLOB in a 2K BLOBpage BLOBspace takes up 4K. UDO/IDS.2000 SBLOBS (Smart BLOBs) work differently see the IDS.2000 Administrator's Guide and the Administrator's Reference Manuals. > block size. Probably 2K per record. LVARCHAR would take the lenght of > the string that's stored plus 2 bytes (educated guess, there. VARCHAR > takes strlen + 1 byte to hold the length of the string. The farthest > you can cound with 1 byte is 255, hence the limit. To increase the > limit, I would guess you'd have to increase the var to hold the length.) And from thence comes LVARCHAR. > The separate table will hold the length of each line + 1 + 4 + 4 bytes, > and you will have an extra 4 bytes in the parent table. Plus the size > of the index, which would be, ummm... err... negligible. This, of > course, assumes that every record will have a description. Or length of fixed CHAR field + 4 + 2 (SMALLINT sequence column). Also Why are you adding 4 bytes to the parent table? To store the number of comment lines? Not needed and it adds overhead to any operation the edits the commentary to update the parent row's count. If needed the count can be gotten directly from a SELECT COUNT(*) FROM commentary WHERE ....; query and I submit that a well written application may never need to know how many comment rows there are depending on the app's design. Art S. Kagel

In article <386B7D7D.DA14D5A8@bloomberg.net>, kagel@bloomberg.net wrote: > mars1972@my-deja.com wrote: > > > > In article <386AFB81.770C4867@atbusiness.com>, > > Lauri Pietarinen <lauri.pietarinen@atbusiness.com> wrote: > > > Thank you for your help! > > > > > > So the choise would be between LVARCHAR and a separate table. > > > I am a bit afraid of TEXT and BYTE, as we will be using ODBC. > > > How much space do they take? > > > > How much space do what take? TEXT and BYTE columns, I believe, are both > > stored in blobs, which are allocated in blocks, so it depends on your > > BLOBs take up 56 bytes in the 'HOME' row plus the space taken by the BLOB > itself. If the BLOB is stored in tablespace the takes up exactly the space > it needs (add some overhead since the page holding the tails of BLOBs that > don't fit on a page will only fill to 2/3 full initially to allow the tails > to grow). If the BLOB lives in a BLOBspace the then space depends on the > BLOBpage size you set when you created the BLOBspace (at least PAGESIZE for > your system, 2k except AIX and WinNT - 4K). BLOBspace BLOBs take up only > complete pages so a 3K BLOB in a 2K BLOBpage BLOBspace takes up 4K. > > UDO/IDS.2000 SBLOBS (Smart BLOBs) work differently see the IDS.2000 > Administrator's Guide and the Administrator's Reference Manuals. > > > block size. Probably 2K per record. LVARCHAR would take the lenght of > > the string that's stored plus 2 bytes (educated guess, there. VARCHAR > > takes strlen + 1 byte to hold the length of the string. The farthest > > you can cound with 1 byte is 255, hence the limit. To increase the > > limit, I would guess you'd have to increase the var to hold the length.) > > And from thence comes LVARCHAR. > > > The separate table will hold the length of each line + 1 + 4 + 4 bytes, > > and you will have an extra 4 bytes in the parent table. Plus the size > > of the index, which would be, ummm... err... negligible. This, of > > course, assumes that every record will have a description. > > Or length of fixed CHAR field + 4 + 2 (SMALLINT sequence column). Also > Why are you adding 4 bytes to the parent table? To store the number of > comment lines? Not needed and it adds overhead to any operation the edits > the commentary to update the parent row's count. If needed the count can > be gotten directly from a SELECT COUNT(*) FROM commentary WHERE ....; query > and I submit that a well written application may never need to know how many > comment rows there are depending on the app's design. > > Art S. Kagel > I added 4 bytes to the parent table to hold the reference to the sequence number in the child table, obviously. How else would you know which set of records in the child table relate to the current record in the parent table, unless there is already some unique field on the parent table that you could use? -- # unrm / ksh: unrm: not found # man cpio Sent via Deja.com http://www.deja.com/ Before you buy.

mars1972@my-deja.com wrote: > > In article <386B7D7D.DA14D5A8@bloomberg.net>, > kagel@bloomberg.net wrote: > > mars1972@my-deja.com wrote: > > > > > > In article <386AFB81.770C4867@atbusiness.com>, > > > Lauri Pietarinen <lauri.pietarinen@atbusiness.com> wrote: > > > > Thank you for your help! > > > > > > > > So the choise would be between LVARCHAR and a separate table. > > > > I am a bit afraid of TEXT and BYTE, as we will be using ODBC. > > > > How much space do they take? > > > > > > How much space do what take? TEXT and BYTE columns, I believe, are > both > > > stored in blobs, which are allocated in blocks, so it depends on > your > > > > BLOBs take up 56 bytes in the 'HOME' row plus the space taken by the > BLOB > > itself. If the BLOB is stored in tablespace the takes up exactly the > space > > it needs (add some overhead since the page holding the tails of BLOBs > that > > don't fit on a page will only fill to 2/3 full initially to allow the > tails > > to grow). If the BLOB lives in a BLOBspace the then space depends on > the > > BLOBpage size you set when you created the BLOBspace (at least > PAGESIZE for > > your system, 2k except AIX and WinNT - 4K). BLOBspace BLOBs take up > only > > complete pages so a 3K BLOB in a 2K BLOBpage BLOBspace takes up 4K. > > > > UDO/IDS.2000 SBLOBS (Smart BLOBs) work differently see the IDS.2000 > > Administrator's Guide and the Administrator's Reference Manuals. > > > > > block size. Probably 2K per record. LVARCHAR would take the lenght > of > > > the string that's stored plus 2 bytes (educated guess, there. > VARCHAR > > > takes strlen + 1 byte to hold the length of the string. The > farthest > > > you can cound with 1 byte is 255, hence the limit. To increase the > > > limit, I would guess you'd have to increase the var to hold the > length.) > > > > And from thence comes LVARCHAR. > > > > > The separate table will hold the length of each line + 1 + 4 + 4 > bytes, > > > and you will have an extra 4 bytes in the parent table. Plus the > size > > > of the index, which would be, ummm... err... negligible. This, of > > > course, assumes that every record will have a description. > > > > Or length of fixed CHAR field + 4 + 2 (SMALLINT sequence column). > Also > > Why are you adding 4 bytes to the parent table? To store the number > of > > comment lines? Not needed and it adds overhead to any operation the > edits > > the commentary to update the parent row's count. If needed the count > can > > be gotten directly from a SELECT COUNT(*) FROM commentary WHERE ....; > query > > and I submit that a well written application may never need to know > how many > > comment rows there are depending on the app's design. > > > > Art S. Kagel > > > > I added 4 bytes to the parent table to hold the reference to the > sequence number in the child table, obviously. How else would you know > which set of records in the child table relate to the current record in > the parent table, unless there is already some unique field on the > parent table that you could use? Ahh, and hence my confusion. The sequence number is a sequence of the comment line for the particular parent, ie first comment row for this parent, second comment row for this parent, etc. In this way one need not know the sequence in the parent. The key to the commentary rows (child) is the 4byte parent key plus the 2byte serial number BUT you can get the commentary for a particular parent row as follows with no additional column in the parent: SELECT c.* FROM comentary c, parent p WHERE c.parent_key = p.parent_key ORDER BY c.parent_key, c.sequence; This returns: Parent_Key Sequence Comments ---------- -------- ------------------------------------------- 12345 1 This is the first line of comments for the 12345 2 parent identified by key value 12345. 12345 3 This is the 3rd line of comments for 12345. 12356 1 This is the 1st line of comments for 12356. ... I think that you get it now?. Now you COULD store the highest sequence in the parent so that if more lines are added you do not have to retrieve the existing comments or SELECT MAX(sequence) but for most applications you need to FETCH the existing comment rows anyway to display so you can well live without the extra 2 bytes in the parent most of the time. Art S. Kagel

"Art S. Kagel" wrote: > mars1972@my-deja.com wrote: > > > > In article <386B7D7D.DA14D5A8@bloomberg.net>, > > kagel@bloomberg.net wrote: > > > mars1972@my-deja.com wrote: > > > > > > > > In article <386AFB81.770C4867@atbusiness.com>, > > > > Lauri Pietarinen <lauri.pietarinen@atbusiness.com> wrote: > > > > > Thank you for your help! > > > > > > > > > > So the choise would be between LVARCHAR and a separate table. > > > > > I am a bit afraid of TEXT and BYTE, as we will be using ODBC. > > > > > How much space do they take? > > > > > > > > How much space do what take? TEXT and BYTE columns, I believe, are > > both > > > > stored in blobs, which are allocated in blocks, so it depends on > > your > > > > > > BLOBs take up 56 bytes in the 'HOME' row plus the space taken by the > > BLOB > > > itself. If the BLOB is stored in tablespace the takes up exactly the > > space > > > it needs (add some overhead since the page holding the tails of BLOBs > > that > > > don't fit on a page will only fill to 2/3 full initially to allow the > > tails > > > to grow). If the BLOB lives in a BLOBspace the then space depends on > > the > > > BLOBpage size you set when you created the BLOBspace (at least > > PAGESIZE for > > > your system, 2k except AIX and WinNT - 4K). BLOBspace BLOBs take up > > only > > > complete pages so a 3K BLOB in a 2K BLOBpage BLOBspace takes up 4K. > > > > > > UDO/IDS.2000 SBLOBS (Smart BLOBs) work differently see the IDS.2000 > > > Administrator's Guide and the Administrator's Reference Manuals. > > > > > > > block size. Probably 2K per record. LVARCHAR would take the lenght > > of > > > > the string that's stored plus 2 bytes (educated guess, there. > > VARCHAR > > > > takes strlen + 1 byte to hold the length of the string. The > > farthest > > > > you can cound with 1 byte is 255, hence the limit. To increase the > > > > limit, I would guess you'd have to increase the var to hold the > > length.) > > > > > > And from thence comes LVARCHAR. > > > > > > > The separate table will hold the length of each line + 1 + 4 + 4 > > bytes, > > > > and you will have an extra 4 bytes in the parent table. Plus the > > size > > > > of the index, which would be, ummm... err... negligible. This, of > > > > course, assumes that every record will have a description. > > > > > > Or length of fixed CHAR field + 4 + 2 (SMALLINT sequence column). > > Also > > > Why are you adding 4 bytes to the parent table? To store the number > > of > > > comment lines? Not needed and it adds overhead to any operation the > > edits > > > the commentary to update the parent row's count. If needed the count > > can > > > be gotten directly from a SELECT COUNT(*) FROM commentary WHERE ....; > > query > > > and I submit that a well written application may never need to know > > how many > > > comment rows there are depending on the app's design. > > > > > > Art S. Kagel > > > > > > > I added 4 bytes to the parent table to hold the reference to the > > sequence number in the child table, obviously. How else would you know > > which set of records in the child table relate to the current record in > > the parent table, unless there is already some unique field on the > > parent table that you could use? > > Ahh, and hence my confusion. The sequence number is a sequence of the > comment line for the particular parent, ie first comment row for this > parent, second comment row for this parent, etc. No, no. What I meant by sequence number was serial number. The parent should have a unique id, normally for something like this, a serial column. That's what I meant by the extra 4 bytes in the parent table. > In this way one need not > know the sequence in the parent. The key to the commentary rows (child) > is the 4byte parent key plus the 2byte serial number BUT you can get the > commentary for a particular parent row as follows with no additional column > in the parent: > > SELECT c.* > FROM comentary c, parent p > WHERE c.parent_key = p.parent_key > ORDER BY c.parent_key, c.sequence; > > This returns: > > Parent_Key Sequence Comments > ---------- -------- ------------------------------------------- > 12345 1 This is the first line of comments for the > 12345 2 parent identified by key value 12345. > 12345 3 This is the 3rd line of comments for 12345. > 12356 1 This is the 1st line of comments for 12356. > ... > > I think that you get it now?. Now you COULD store the highest sequence in > the parent so that if more lines are added you do not have to retrieve the > existing comments or SELECT MAX(sequence) but for most applications you need > to FETCH the existing comment rows anyway to display so you can well live > without the extra 2 bytes in the parent most of the time. > > Art S. Kagel

Daniel Marshall wrote: > > "Art S. Kagel" wrote: > > > mars1972@my-deja.com wrote: > > > > > > In article <386B7D7D.DA14D5A8@bloomberg.net>, > > > kagel@bloomberg.net wrote: > > > > mars1972@my-deja.com wrote: > > > > > > > > > > In article <386AFB81.770C4867@atbusiness.com>, > > > > > Lauri Pietarinen <lauri.pietarinen@atbusiness.com> wrote: > > > > > > Thank you for your help! > > > > > > > > > > > > So the choise would be between LVARCHAR and a separate table. > > > > > > I am a bit afraid of TEXT and BYTE, as we will be using ODBC. > > > > > > How much space do they take? > > > > > > > > > > How much space do what take? TEXT and BYTE columns, I believe, are > > > both > > > > > stored in blobs, which are allocated in blocks, so it depends on > > > your > > > > > > > > BLOBs take up 56 bytes in the 'HOME' row plus the space taken by the > > > BLOB > > > > itself. If the BLOB is stored in tablespace the takes up exactly the > > > space > > > > it needs (add some overhead since the page holding the tails of BLOBs > > > that > > > > don't fit on a page will only fill to 2/3 full initially to allow the > > > tails > > > > to grow). If the BLOB lives in a BLOBspace the then space depends on > > > the > > > > BLOBpage size you set when you created the BLOBspace (at least > > > PAGESIZE for > > > > your system, 2k except AIX and WinNT - 4K). BLOBspace BLOBs take up > > > only > > > > complete pages so a 3K BLOB in a 2K BLOBpage BLOBspace takes up 4K. > > > > > > > > UDO/IDS.2000 SBLOBS (Smart BLOBs) work differently see the IDS.2000 > > > > Administrator's Guide and the Administrator's Reference Manuals. > > > > > > > > > block size. Probably 2K per record. LVARCHAR would take the lenght > > > of > > > > > the string that's stored plus 2 bytes (educated guess, there. > > > VARCHAR > > > > > takes strlen + 1 byte to hold the length of the string. The > > > farthest > > > > > you can cound with 1 byte is 255, hence the limit. To increase the > > > > > limit, I would guess you'd have to increase the var to hold the > > > length.) > > > > > > > > And from thence comes LVARCHAR. > > > > > > > > > The separate table will hold the length of each line + 1 + 4 + 4 > > > bytes, > > > > > and you will have an extra 4 bytes in the parent table. Plus the > > > size > > > > > of the index, which would be, ummm... err... negligible. This, of > > > > > course, assumes that every record will have a description. > > > > > > > > Or length of fixed CHAR field + 4 + 2 (SMALLINT sequence column). > > > Also > > > > Why are you adding 4 bytes to the parent table? To store the number > > > of > > > > comment lines? Not needed and it adds overhead to any operation the > > > edits > > > > the commentary to update the parent row's count. If needed the count > > > can > > > > be gotten directly from a SELECT COUNT(*) FROM commentary WHERE ....; > > > query > > > > and I submit that a well written application may never need to know > > > how many > > > > comment rows there are depending on the app's design. > > > > > > > > Art S. Kagel > > > > > > > > > > I added 4 bytes to the parent table to hold the reference to the > > > sequence number in the child table, obviously. How else would you know > > > which set of records in the child table relate to the current record in > > > the parent table, unless there is already some unique field on the > > > parent table that you could use? > > > > Ahh, and hence my confusion. The sequence number is a sequence of the > > comment line for the particular parent, ie first comment row for this > > parent, second comment row for this parent, etc. > > No, no. What I meant by sequence number was serial number. The parent should > have a unique id, normally for something like this, a serial column. That's > what I meant by the extra 4 bytes in the parent table. OK, I was assuming that the table already has such a SERIAL key. OK, if not then yes you have to allow for that also. FWEW took a bit to get to an understanding. Art S. Kagel

Is LVARCHAR only supported in 9.2 or is it also available in the "base" IDS product (7.3x-->) ?? Madison Pruet wrote: > IDS.2000 (9.2) supports LVARCHAR of up to 2K bytes. > > -- > Madison Pruet > > =========================================== > Enterprise Replication Product Developement > Dallas, Texas > Informix Software > =========================================== -- ====================================================================== Lauri Pietarinen tel +358-9-2534 4632 AtBusiness Communications Oy fax +358-9-2534 4601 Itälahdenkatu 19 mob +358-50-594 2011 00210 HELSINKI mailto:lauri.pietarinen@atbusiness.com Finland http://www.atbusiness.com ======================================================================

It is not available in 7.x. Lauri Pietarinen wrote: > Is LVARCHAR only supported in 9.2 or is it also available in the "base" IDS > product (7.3x-->) ?? > > Madison Pruet wrote: > > > IDS.2000 (9.2) supports LVARCHAR of up to 2K bytes. > > > > -- > > Madison Pruet > > > > =========================================== > > Enterprise Replication Product Developement > > Dallas, Texas > > Informix Software > > =========================================== > > -- > ====================================================================== > Lauri Pietarinen tel +358-9-2534 4632 > AtBusiness Communications Oy fax +358-9-2534 4601 > Itälahdenkatu 19 mob +358-50-594 2011 > 00210 HELSINKI mailto:lauri.pietarinen@atbusiness.com > Finland http://www.atbusiness.com > ====================================================================== -- Madison Pruet =========================================== Enterprise Replication Product Developement Dallas, Texas Informix Software ===========================================

LVARCHAR is only available in 9.x servers. It is not available in the 7.x or 8.x server families. Lauri Pietarinen wrote: > Is LVARCHAR only supported in 9.2 or is it also available in the "base" IDS > product (7.3x-->) ?? > > Madison Pruet wrote: > > > IDS.2000 (9.2) supports LVARCHAR of up to 2K bytes. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.62 -- see http://www.perl.com/CPAN #include <disclaimer.h>