Hi Everyone,

I really have this doubt which surprised me

I created a db2 table with a two varchar(1) columns,

I stored number values in the table, inserted some of them with regular sequel and some of them in within ''. I also updated some of them with the sequel below

"UPDATE test_test
SET test = (select cast(9 as varchar(1)) from sysibm.sysdummy1)
WHERE test=5";

I was under the impression that db2 stores digits as a character value. But when I perform mathematical functions on the columns, it does calculates the result for me. So does this mean it stores the numbers in a varchar field as numbers? Does it do this with varchar of 1 only and stores the numbers in varchar(n) as a string value. Can somebody help me understand what I think is correct or not. It will help me resolve some code issues

Thanks
dbsam

dbsam, If it is a Character (or Varchar) datatype column, then it is stored as a Character.

However, in DB2 LUW V9.7, Weak Typing was introduced. See the topic What's new in DB2 V9.7: Implicit casting simplifies application enablement in the Information Center for more details.

Thanks so much for clearing my confusion and pointing me to the document. I got it now. Much appreciate it

No problem, dbsam. Now for the real question. Why would you ever define a column as Varchar(1)? That uses 3 bytes of space as opposed to CHAR(1) which only uses 1 byte.

Hi Stealth_dba, this is a big question for our team too. This db was designed 6-7 years back before I joined here. The db is not limited to just this one flaw in the design. However we are in a process of moving to whole new system which is still in the planning stages and have long time to go. Until we get there completely, we cannot discard our present system. The application code is using the current db and it is hard to redesign the existing system now. So we will have to bear with such shortcomings until we get the new system implemented and fully functional.

Thanks

dbsam

Using VARCHAR over CHAR has one nice advantage: a string literal is VARCHAR (in DB2) and will not be propagated implicitly to CHAR. For for ad-hoc type of queries, VARCHAR would safe you the explicit cast. From a storage and processing point of view, you'd rather go with CHAR, of course.

p.s: The SQL standard actually says that string literals should be of type CHAR.

Understood...so now I have safely asked the development team to remove the cast part in the update query so that they can rid of the extra overhead of casting


So instead of the following query
UPDATE db2inst1.test_test
SET test1 = (select cast(? as varchar(1)) from sysibm.sysdummy1)
WHERE test2 = ? and test3 = ?

now they are using
UPDATE db2inst1.test_test
SET test1 = ?
WHERE test2 = ? and test3 = ?

Thanks guys for clarifying..you have been very helpful

dbsam

Is it possible to decrease column length??

I have altered a column and increased its size from CHAR(10) to CHAR(20).
but later when i was trying to reduce it back to CHAR(10) it was unsuccessful.
My table is empty.

I don't know what this question has to do with the original post, but the manual (ALTER TABLE - IBM DB2 9.7 for Linux, UNIX, and Windows) under "SET DATA TYPE altered-data-type":
So the answer is: it depends. You can reduce the size, but it depends on the data in the table and what kind of truncation would occur. And, of course, it depends on the DB2 version you are using and your platform whether the above quoted paragraph actually applies to you.