Set the CCSID at a field level

[GLS400]

Hi All:

Is there any to set the ccsid of the field being created and inserted into a file?
I know I can set it at the job level but can I set it at the field level.

The issue I'm dealing with is that I'm creating a file in sql (create table as blah blah )
....All of the fields except 2 are coming from other files, the last 2 are being created within the program.
All of the fields except those 2 have a ccsid of 37 .... the new fields are 65535.

When the new file gets imported into access/sql server etc the last 2 fields are JUNK

Thanks for any assistance
GLS

[Viking]

Re: Set the CCSID at a field level

Just curious... if you do DSPSYSVAL SYSVAL(QCCSID) does it show 65535?

[GLS400]

Re: Set the CCSID at a field level

Just curious... if you do DSPSYSVAL SYSVAL(QCCSID) does it show 65535?

yup

[Viking]

Re: Set the CCSID at a field level

This is not recommended, although as shipped the machine comes with CCSID 65535. Your business partner or system administrator probably should have set that to 37 in the beginning. I think it's safe to change it which will solve some CCSID related issues, possibly including this one, but research that a bit first.

I'm sorry I don't know the answer to your question about setting it on the INSERT at the field level, I'm looking.

[Viking]

Re: Set the CCSID at a field level

Possibly you can use CAST.

cast( MYFIELD as char (10) CCSID 37 )

[GLS400]

Re: Set the CCSID at a field level

Hi Viking:

I'm looking at that now...just can't get the syntax right

cast(DDAMT + DSVCG as num(9,2) ccsid(37)   as DED_AMOUNT,

el puko on the leading paren after ccsid
remove it "clause not valid for specified type"

At least I think I'm going down the right road

Thanks
GLS

[Scott M]

Re: Set the CCSID at a field level

No parens around the 37 - see Viking's example.

[GLS400]

Re: Set the CCSID at a field level

No parens around the 37 - see Viking's example.

It didn't work on numeric fields but it did work on alpha fields.
The numeric fields did make it to PC land as legible.
I'll look into the why later.

For now Thank You to Viking and Scott.....You have both been a great asset

GLS

[Scott M]

Re: Set the CCSID at a field level

It doesn't work on numeric because the second C in CCSID stands for Character. It only relates to how character fields are stored, do a DSPFFD on a file and note that CCSID is only defined on character fields. Numbers are numbers no matter what language

[Scott Klement]

Re: Set the CCSID at a field level

You asked about setting the CCSID when creating a new field in a file, right? You wouldn't use CAST for that, you'd just specify a CCSID on the CREATE TABLE or ALTER TABLE command. (or DDS source)

IMHO, it's smart to set the CCSID on all fields that will contain text.

Create Table MYTABLE (
   Field1 Char(10) CCSID 37,
   Field2 Char(50) CCSID 285
)

Alter Table MYTABLE add column NEWFIELD CHAR(25) CCSID 500

Or whatever.

I would also strongly recommend setting the QCCSID sysval. This is a trivial change, takes a second, and it's very rare that it causes any problems.... but it solves a LOT of problems.

[tomliotta]

Re: Set the CCSID at a field level

I'd also recommend setting the appropriate QCCSID value, but there are potential problems. Mostly, the problems arise only when different language users have been working on the system, but this site does get international exposure. Discussion so far assumes that the new QCCSID value will be 37; but use whatever CCSID value is appropriate. I'll use "37". Usually you can determine the 'appropriate' CCSID by using DSPJOB to see the 'Default coded character set identifier' of your interactive jobs.

First step would be to go through objects you've defined on your system with CCSID tags. Look for objects that are NOT 37 and also NOT 65535. (Note that this includes programs that have been compiled in jobs that ran with a different CCSID. Those might be 3rd-party programs.)

For those objects, review differences in the code pages between 37 and the object code page. E.g., the '@' sign is x'7C' under CCSID 37, but it's x'44' under CCSID 297 (a French CCSID). Be aware that such characters can seem to change in some circumstances after changing QCCSID.

Most sites probably won't run into any problems. But some site members will need to take extra care.

Tom