Just a short entry today to discuss a feature that will be useful for organizations having tables containing long text strings which are stored as VARCHARs. It involves altering the data type of a column from LONG VARCHAR to a compatible data type.
In previous versions of DB2 before to V9 it is not possible to change the data type of a LONG VARCHAR or LONG VARGRAPHIC column using ALTER. Instead, you would have had to unload the data, drop the table, create the table using the new column definitions and reload the data back into the new table. Of course, when you drop the table you’d lose any authorizations, indexes, etc. defined on the table so you’d have to save and re-create those as well. Basically, it wasn’t easy unless you have a change management tool that automates the process.
But before going any further, what is a LONG VARCHAR? Well, a VARCHAR is a varying-length column for character string data where n specifies the maximum length of the string. If n was greater than 254, the column was setup as a long string column – that is, a LONG VARCHAR; same story for VARGRAPHIC columns.
As of DB2 V9, you will not create any more LONG VARCHAR columns – they are all treated as VARCHARs. But the old LONG VARCHAR columns, migrated from earlier releases, still exist. The COLTYPE in the DB2 Catalog (SYSIBM.SYSCOLUMNS) will be LONGVAR for LONG VARCHAR columns and LOGVARG for LONG VARGRAPHIC columns.
The good news is that V9 allows you to ALTER the data type of LONG VARCHAR to VARCHAR and LONG VARGRAPHIC to VARGRAPHIC using ALTER TABLE with the ALTER COLUMN parameter. First, you must lookup the LENGTH of the column in the SYSCOLUMNS and use that for the length of the new data type specification. For example, if LONGCOL in table EXAMPLE is a LONG VARCHAR you can ALTER it in DB2 V9 to VARCHAR. Assuming the length of the column was 500, you can issue the following ALTER to change the column to VARCHAR from LONG VARCHAR:
ALTER TABLE EXAMPLE
ALTER COLUMN LONGCOL
SET DATA TYPE VARCHAR(500);
You must specify the exact value stored in the DB2 Catalog for LENGTH in this ALTER TABLE statement. Failure to do so will result in an error message.
So, you can convert all of those LONG VARCHAR columns from past versions of DB2 to VARCHAR using the ALTER statement in V9.
News, views, and items of interest on IBM's Db2 database management system and mainframes.
if i have a longvar column of size ( say 3000) in a table defined in a 4k Table space, Is it possible to store text in the column which has length more than 3000 ?
ReplyDeleteI have db2 v9.x If i use LONGVARCHAR will i be facing any problems.
ReplyDeleteRight now iam facing one problem for longvarchar that is
i unable to retrieve it using = in select query
ex:select * from table_name where LONGVARCHAR_COL='ABCD';
above query is giving compatibility error
foll is working
ex:select * from table_name where LONGVARCHAR_COL like 'ABCD';
will it correct or it gives any problems in future because iam using it on production system application.
Hello everyone, i am using ibmdb2 version db2 v 9.7
ReplyDeleteon a particular table i have a column COL_ABC of type VARCHAR(1000),
i want to alter the column
to LONG VARCHAR.
I have written the following SQL statement but am getting errors:
ALTER TABLE TBL_ABC ALTER COLUMN COL_ABC set DATA TYPE LONG VARCHAR;
Any idea how to resolve this issue?
thanks in advance.