If you want to do it with logical files just refine the field as alpha, but
make sure you specify an appropriate CCSID for the data.
Copied from ibm
Example 1:
The following example shows how to specify the CCSID keyword for physical
files.
|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+.
...8
00010A CCSID(285)
00020A R RECORD1
00030A FIELD1 75G CCSID(13488)
00040A FIELD2 150A
00050A FIELD3 20A
00060A FIELD4 10A CCSID(1208 *NORMALIZE)
00070A FIELD5 10G CCSID(1200)
AFIELD1 is assigned a UCS-2-ccsid value of 13488. FIELD2 and FIELD3 are
assigned a CCSID value of 285. FIELD4 is assigned a UTF-8 CCSID value of
1208 and its data will be normalized before being inserted or updated in the
file. FIELD5 is assigned a UTF-16 CCSID value of 1200 and its data will not
be normalized before being inserted or updated in the file.
Example 2:
The following example shows how to specify the CCSID keyword on a
corresponding logical file.
|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+.
...8
00000A
00010A R RECORD1
00020A FIELD1 75A CCSID(37)
00030A FIELD2 150G CCSID(13488 80)
00040A FIELD3 20A
00050A FIELD4 10G CCSID(1200 *NORMALIZE)
00060A FIELD5 10A
A
The logical file's FIELD1 is assigned a SBCS CCSID value of 37. Conversion
occurs between the physical file and the logical file for FIELD1 since the
physical file field contains UCS-2 data. The logical file's FIELD2 is
assigned a UCS-2-ccsid value of 13488. Conversion occurs between the
physical file and the logical file for FIELD2 since the logical file
contains UCS-2 data. A CCSID is not specified for FIELD3. FIELD4 is assigned
a UTF-16 CCSID value of 1200. Conversion occurs between the physical file
and the logical file for FIELD4 since the physical file field contains UTF-8
character data. The data will be normalized. FIELD5 is assigned the CCSID of
the job in which the file is created. Conversion occurs between the physical
file and the logical file for FIELD5 since the physical file field contains
UTF-16 data. The data will not be normalized.
----------------------------------------------------------------------------
----
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of iseries@xxxxxxxxxxxx
Sent: 20 October 2009 19:15
To: 'Midrange Systems Technical Discussion'
Subject: DBCS issues
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?
Dennis Lovelady
404-386-9745 (c)
AIM: delovelady MSN: fastcounter@xxxxxxxxxxxx
--
"I don't want to achieve immortality through my work, I want to achieve it
through not dying."
- Woody Allen
<
http://www.linkedin.com/in/dennislovelady> View Dennis Lovelady's profile
on LinkedIn
midrange.com
