Performance impact of LVARCHAR

We use COBOL to access Informix. The ESQL/COBOL product has not seen an update in over a decade. The manual has a print date of April 1996, version 7.24. Obviously, we're talking old. That predates the Informix buyout of Illustra, so there is no concept of user-defined datatypes. Which brings me to my question. Does anyone know how big of a performance impact there is when inserting into a table that contains a column LVARCHAR(5000)? The manual says it is "implemented as a built-in opaque data type" (http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.sqlr.doc/id s_sqr_125.htm?resultof=%22%6c%76%61%72%63%68%61%72%22%20), but I have no idea how the COBOL pre-compiler can handle this since the data type did not exist that far back. The program runs, and the results seem correct, but it is extremely slow compared to similar activity against a table with no LVARCHAR columns. Anyone heard rumors of IBM updating the ESQL/COBOL product? Thanks.

What version of IDS are you running? COBOL is handling the column as a CHAR column and the engine is doing the type conversion. A 5000 character column will be causing the row to taking up at least 3 data pages on disk. And since it is being inserted using a CHARACTER type host variable, it is taking up the full 5000 bytes plus the two byte length word on disk. You really are not getting any advantage from using an LVARCHAR over using a simple CHAR(5000) column. The additional pages of storage tends to be very inefficient. However, it may simply be the size of the column rather than anything inherent in the LVARCHAR data type. The only real overheads of using LVARCHAR is the 2 byte length word prepended to the column's data string and the conversion function calls to convert it to a simple CHAR array padding any missing trailing spaces. When you say it is slow versus another table without LVARCHAR, does that table have very wide rows, similar to this table in the > 5000 byte range? The comparison may not be fair. Anyway, the main advantage of using VARCHAR and LVARCHAR columns instead of CHAR is that the table only stores the actual characters that are in the host variable when the row is added thereby saving some storage for columns variable length but stable contents once inserted. IB that like an ESQL/C FIXCHAR column, the COBOL CHARACTER field is padded on the right with spaces to its full length. That means that you may be storing all 5000 bytes of this column even when the application only placed a few significant characters in the field. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Sep 15, 2009 at 7:10 PM, MARK COLLINS <markc@myfastmail.com> wrote: > We use COBOL to access Informix. The ESQL/COBOL product has not seen an > update > in over a decade. The manual has a print date of April 1996, version 7.24. > Obviously, we're talking old. That predates the Informix buyout of > Illustra, > so there is no concept of user-defined datatypes. > > Which brings me to my question. Does anyone know how big of a performance > impact there is when inserting into a table that contains a column > LVARCHAR(5000)? > > The manual says it is "implemented as a built-in opaque data type" > ( > http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.sqlr.doc/ids _sqr_125.htm?resultof=%22%6c%76%61%72%63%68%61%72%22%20 > ), > but I have no idea how the COBOL pre-compiler can handle this since the > data > type did not exist that far back. > > The program runs, and the results seem correct, but it is extremely slow > compared to similar activity against a table with no LVARCHAR columns. > > Anyone heard rumors of IBM updating the ESQL/COBOL product? > > Thanks. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001517448ad2df32470473a8ab22

We're using 11.50.FC3. The table is in a dbspace with 8k page size, so I think we are only using one page per row, but that would leave a lot of empty space at the end of each page. I believe you are correct about the padding with spaces at the end of the variable. The precompiler allows us to use VARCHAR(xx) instead of PIC X(xx) in the working-storage section, which corrects this problem for VARCHAR, but obviously it won't work with LVARCHAR. Would it be possible to create insert/update triggers to strip the trailing spaces in the column with a trim()?

Yes: > create table trim_test (one lvarchar(1000)); Table created. > create procedure trim_varchar( ) referencing new as new for trim_test;> let new.one = rtrim(new.one); > end procedure; Routine created. > create trigger trim_test_ins insert on trim_test> for each row ( execute procedure trim_varchar() with trigger references ); Trigger created. > insert into trim_test values ('sklasjdfhlasdkjfhl aklsdfhaskl asdkljfh '); 1 row(s) inserted. > select ']'||one||'[' from trim_test; (expression) ]sklasjdfhlasdkjfhl aklsdfhaskl asdkljfh[ 1 row(s) retrieved. > > create table no_trim_test(one lvarchar(1000)); Table created. > insert into no_trim_test values ('sklasjdfhlasdkjfhl aklsdfhaskl asdkljfh'); 1 row(s) inserted. > select ']'||one||'[' from no_trim_test; (expression) ]sklasjdfhlasdkjfhl aklsdfhaskl asdkljfh [ 1 row(s) retrieved. > Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Sep 16, 2009 at 10:44 AM, MARK COLLINS <markc@myfastmail.com> wrote: > We're using 11.50.FC3. The table is in a dbspace with 8k page size, so I > think > we are only using one page per row, but that would leave a lot of empty > space > at the end of each page. > > I believe you are correct about the padding with spaces at the end of the > variable. The precompiler allows us to use VARCHAR(xx) instead of PIC X(xx) > in > the working-storage section, which corrects this problem for VARCHAR, but > obviously it won't work with LVARCHAR. > > Would it be possible to create insert/update triggers to strip the trailing > spaces in the column with a trim()? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0023545bef70e4788c0473b42d1f

Awesome. Thanks, Art.