Re: DBCS issues

The open data type is probably not so good, at least as compared to having chosen Unicode :-( Either way however, there is going to be the problem that the data in the physical file eventually may not be representable in an alternate logical view of the data; i.e. mapping errors [perhaps in selection, terminating the query request] or substitution characters for the data may result. If there would be no such eventual potential problem, then there would have been little value\reason in having changed the data type. Anyhow, knowing those limits, here is an example of making the character data available in a view from an alternate library:

create table original.filename
( a50 char(50) )
;
insert into original.filename
values('This is 037 text')
;
alter table original.filename
alter column a50 set data type char ccsid 5035
;
insert into original.filename
values(x'0E0F')
;
create view alternate.filename
( a50 ) as
( select cast(a50 as char(50) ccsid 37)
from original.filename )
;

Regards, Chuck

iseries@xxxxxxxxxxxx wrote:

My client is using a software product that, in its latest
inception, decided to switch most of its descriptive/name
fields from (A) Alphanumeric to (O) DBCS-Open data types.

There are hundreds or thousands of queries that have been
developed over the years using SEQUEL (not to be confused with
SQL), which apparently has problems supporting this data. It
turns out that if we specify (for example) SST(problem_field, 1,
30) then SEQUEL does what we want, but careers can be made in
locating each of these fields and then making the appropriate
change to each and performing the necessary testing. And with the caveat that these sorts of problems are found only at run
time, this has the potential of being a longstanding nightmare.

To bridge this gap, I would like to create SQL views and/or DDS
logical files over the problem physicals, whereby the "view"
would have (A)-type data, thereby giving us an intermediate file
against which to query until the SEQUEL problem is resolved.
(There is no current ETA for such resolution.) I cannot seem to
come up with a means of converting the data with any - shall we
say "interesting" - DDS file descriptions. I'm sure I could
fairly easily write an SQL function that could be used in an SQL
view (e.g. DBCS2ALPHA), but I'd rather avoid that if only for
performance reasons. In my initial design, the views would have
the same name as the physical, but would reside in a different
library, which would ideally be first in the query's library
list, hopefully limiting the complexity of this whole scenario.

Is this a problem that has been solved before by anyone on this
list who might be willing to share? Do you have a trick for
handling a DBCS-Open field as alpha, for example? Do you have
some other solution?