Re: DBCS issues

[CRPence <CRPbottle@xxxxxxxxx>]

  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?