Again: Table VARCHAR <-- VIEW CHAR

Since we didn't really get any solid answers with this before, I'll try again. Our system has a lot of tables with a lot of CHAR columns. I'd like to change these to VARCHAR, but we have a legacy app that wouldn't like that. So, can I create a view that has a CHAR column, while the underlying table has a VARCHAR column? Here are some possible solutions: 1. Use IUS and make the view like so-- CREATE VIEW charview ASSELECT varcharcol::CHAR(30), ... FROM mytable 2. Create a VARCHAR to CHAR function that just does a type conversion and do the same thing, changing line two to-- SELECT tochar(varcharcol), ... 3. Create the view while the table really contains a CHAR column and then alter the table to be VARCHAR and do a trim on the columns. As I understand it, the view definition doesn't change even after you alter the table. Of course, if you had to restore the table, you'd have to do this all over again. Now the big questions. Would any/all of these even work? If so, what kind of performance hit would I have to take? Is there a better way? Am I wasting my time even thinking about this? We have tables that are twice as big as they need to be due to having CHAR columns. And our lovely legacy app likes to read the tables a lot. I figure that if I can give it twice as many rows per page it would be happier. -- --Marty Allred Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.

Marty Allred wrote: > > Since we didn't really get any solid answers with this before, I'll try > again. Actually I saw several solid answers the first time. The answer was and is NO. You cannot do what you want to do through any feature of the engine, trick of the trade, or scratching your left ear with your right elbow logic. I just cannot be done. HOWEVER DON'T GIVE UP YET! Read on. > Our system has a lot of tables with a lot of CHAR columns. I'd like to > change these to VARCHAR, but we have a legacy app that wouldn't like > that. So, can I create a view that has a CHAR column, while the > underlying table has a VARCHAR column? No. The truth is I cannot see that the legacy applications will even notice the change as long as the max length of the varchar matches the actual length of the original char column. The apps are probably fetching the data into a "C" char array which is not a problem. If you fetch a varchar into a char in C or 4GL it is automatically converted by the engine on retrieval and converted to char and padded with spaces to the size of the char array (with a warning in any indicator variable if the column was truncated to fit). You will only have trouble with 4GL and ISQL programs that are recompiled and only if they use "LIKE " to declare variables. Anything that is already compiled will just keep on running. > Here are some possible solutions: > 1. Use IUS and make the view like so-- > CREATE VIEW charview AS> SELECT varcharcol::CHAR(30), ... > FROM mytable Do you have IUS? I don't even know if IUS supports such type casts in view definitions, though I suppose you can write a datablade... > 2. Create a VARCHAR to CHAR function that just does a type conversion > and do the same thing, changing line two to-- > SELECT tochar(varcharcol), ... This one is dog slow but might actually work. > 3. Create the view while the table really contains a CHAR column and > then alter the table to be VARCHAR and do a trim on the columns. As I > understand it, the view definition doesn't change even after you alter > the table. Of course, if you had to restore the table, you'd have to do > this all over again. Since views are evaluated at runtime and are just an encapsulation of a select statement this one will not work at all. You'll just get VARCHAR back not CHAR. > Now the big questions. Would any/all of these even work? If so, what > kind of performance hit would I have to take? Is there a better way? > Am I wasting my time even thinking about this? Yes I think you are sweating for no reason. Try it on a test system. > We have tables that are twice as big as they need to be due to having > CHAR columns. And our lovely legacy app likes to read the tables a > lot. I figure that if I can give it twice as many rows per page it > would be happier. Maybe. Varchars are a mixed bag when it comes to performance. Sometimes their prodigious and careful use CAN speed things up, usually there is no real effect or a performance loss. The only thing you can say is that MOST of the time varchar saves storage, but not always. Art S. Kagel

In article <378D153A.F7F0F2C9@bloomberg.net>, kagel@bloomberg.net wrote: > Marty Allred wrote: > > Our system has a lot of tables with a lot of CHAR columns. I'd like to > > change these to VARCHAR, but we have a legacy app that wouldn't like > > that. So, can I create a view that has a CHAR column, while the > > underlying table has a VARCHAR column? > > No. > > The truth is I cannot see that the legacy applications will even notice > the change as long as the max length of the varchar matches the actual > length of the original char column. The apps are probably fetching the > data into a "C" char array which is not a problem. If you fetch a > varchar into a char in C or 4GL it is automatically converted by the > engine on retrieval and converted to char and padded with spaces to the > size of the char array (with a warning in any indicator variable if the > column was truncated to fit). If it matters, the legacy app is in COBOL. Having never used the language, I'm trusting the people here that maintain the old app when they tell me that the reason why we have only CHAR columns is because it can't handle using VARCHARs. I had assumed that it would pad as needed. > You will only have trouble with 4GL and ISQL programs that are > recompiled and only if they use "LIKE " to declare variables. Anything > that is already compiled will just keep on running. Well, we do have a lot of 4GL in use, all of it using LIKE. > > Here are some possible solutions: > > > 1. Use IUS and make the view like so-- > > CREATE VIEW charview AS> > SELECT varcharcol::CHAR(30), ... > > FROM mytable > > Do you have IUS? I don't even know if IUS supports such type casts in > view definitions, though I suppose you can write a datablade... No, we don't have IUS here right now. My interpretation of the IUS manual tells me it can be done this way. Upgrading to IUS is actually an option. > > 2. Create a VARCHAR to CHAR function that just does a type conversion > > and do the same thing, changing line two to-- > > SELECT tochar(varcharcol), ... > > This one is dog slow but might actually work. I kinda figured performance with this option would be rather poor. If we were that desperate for drive space, I suppose it could be an option though. > > 3. Create the view while the table really contains a CHAR column and > > then alter the table to be VARCHAR and do a trim on the columns. As I > > understand it, the view definition doesn't change even after you alter > > the table. Of course, if you had to restore the table, you'd have to do > > this all over again. > > Since views are evaluated at runtime and are just an encapsulation of > a select statement this one will not work at all. You'll just get > VARCHAR back not CHAR. I know what I was thinking. The admonitions in the back of my mind about views not changing when the underlying table changed had to do with using SELECT * and expecting it to still work as SELECT * when columns had been added or removed. Now that I put a tiny bit of thought into it, I realize that the SELECT statement is stored at the time of creation and the engine must expand the SELECT * to the real column names when it gets stored. Since the SELECT will still look at the columns to determine data type, this won't work. > > Now the big questions. Would any/all of these even work? If so, what > > kind of performance hit would I have to take? Is there a better way? > > Am I wasting my time even thinking about this? > > Yes I think you are sweating for no reason. Try it on a test system. > > > We have tables that are twice as big as they need to be due to having > > CHAR columns. And our lovely legacy app likes to read the tables a > > lot. I figure that if I can give it twice as many rows per page it > > would be happier. > > Maybe. Varchars are a mixed bag when it comes to performance. > Sometimes their prodigious and careful use CAN speed things up, usually > there is no real effect or a performance loss. The only thing you can > say is that MOST of the time varchar saves storage, but not always. We have a real issue with disk i/o and I had hoped that the extra effort of looking at row length would be more than offset by getting more rows per page. I've taken the time to look at average column lengths and factored in the extra 2 characters (and I know that the index is still full length) and we would really end up with about a 40-50% reduction in storage. On systems that are often i/o-bound, this sounded like it might make a noticeable difference. Thanks for your input. -- --Marty Allred Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.