Query CCSID

[Thad Rizzi]

I've created a query that builds a file that I use a CPYTOIMPF to get
onto the IFS as a text file. The problem is that when I look at the
file on the IFS some of the fields come across as hex. I've noticed
that all of these fields are result fields I built inside the query.
When I do a DSPFFD on the file created by the query I noticed that my
result fields have a specified CCSID of 65535 instead of 37 which all
of the other fields have. That explains the problem but isn't there
some way to change this inside the query? I'd like all fields to have
CCSID of 37 so that they are viewable after the CPYFRMIMPF.

Looks like my only other otion is to copy the query file to a file
that has all fields with CCSID of 37 but it seems like there should be
an easier work around.

Thanks,

Thad Rizzi

[Karl Hanson]

Post by Thad Rizzi
I've created a query that builds a file that I use a CPYTOIMPF to get
onto the IFS as a text file. The problem is that when I look at the
file on the IFS some of the fields come across as hex. I've noticed
that all of these fields are result fields I built inside the query.
When I do a DSPFFD on the file created by the query I noticed that my
result fields have a specified CCSID of 65535 instead of 37 which all
of the other fields have. That explains the problem but isn't there
some way to change this inside the query? I'd like all fields to have
CCSID of 37 so that they are viewable after the CPYFRMIMPF.
Looks like my only other otion is to copy the query file to a file
that has all fields with CCSID of 37 but it seems like there should be
an easier work around.

Could you use a CAST with the "hex" fields, something like this? :

create table qtemp.xyz as
(select cast(X'F1F2' as char ccsid 37) as c1
from sysibm.sysdummy1) with data

--
Karl Hanson

[Thad Rizzi]

Post by Karl Hanson

Post by Thad Rizzi
I've created a query that builds a file that I use a CPYTOIMPF to get
onto the IFS as a text file.  The problem is that when I look at the
file on the IFS some of the fields come across as hex.  I've noticed
that all of these fields are result fields I built inside the query.
When I do a DSPFFD on the file created by the query I noticed that my
result fields have a specified CCSID of 65535 instead of 37 which all
of the other fields have.  That explains the problem but isn't there
some way to change this inside the query?  I'd like all fields to have
CCSID of 37 so that they are viewable after the CPYFRMIMPF.
Looks like my only other otion is to copy the query file to a file
that has all fields with CCSID of 37 but it seems like there should be
an easier work around.

create table qtemp.xyz as
   (select cast(X'F1F2' as char ccsid 37) as c1
      from sysibm.sysdummy1) with data
--
Karl Hanson

Yeah that would have worked too. I was just trying to elimanate any
other steps.

[Kent Milligan]

Have you looked at using DB2 Web Query? It has the ability to capture the
result of a query into a spreadsheet type of output.

Post by Thad Rizzi

Post by Karl Hanson

Post by Thad Rizzi
I've created a query that builds a file that I use a CPYTOIMPF to get
onto the IFS as a text file. The problem is that when I look at the
file on the IFS some of the fields come across as hex. I've noticed
that all of these fields are result fields I built inside the query.
When I do a DSPFFD on the file created by the query I noticed that my
result fields have a specified CCSID of 65535 instead of 37 which all
of the other fields have. That explains the problem but isn't there
some way to change this inside the query? I'd like all fields to have
CCSID of 37 so that they are viewable after the CPYFRMIMPF.
Looks like my only other otion is to copy the query file to a file
that has all fields with CCSID of 37 but it seems like there should be
an easier work around.

create table qtemp.xyz as
(select cast(X'F1F2' as char ccsid 37) as c1
from sysibm.sysdummy1) with data
--
Karl Hanson

Yeah that would have worked too. I was just trying to elimanate any
other steps.

[Kent Milligan]

Why are you trying to take data out of DB2 for i?

Post by Thad Rizzi
I've created a query that builds a file that I use a CPYTOIMPF to get
onto the IFS as a text file. The problem is that when I look at the
file on the IFS some of the fields come across as hex. I've noticed
that all of these fields are result fields I built inside the query.
When I do a DSPFFD on the file created by the query I noticed that my
result fields have a specified CCSID of 65535 instead of 37 which all
of the other fields have. That explains the problem but isn't there
some way to change this inside the query? I'd like all fields to have
CCSID of 37 so that they are viewable after the CPYFRMIMPF.
Looks like my only other otion is to copy the query file to a file
that has all fields with CCSID of 37 but it seems like there should be
an easier work around.
Thanks,
Thad Rizzi

[Thad Rizzi]

Post by Kent Milligan
Why are you trying to take data out of DB2 for i?

Post by Thad Rizzi
I've created a query that builds a file that I use a CPYTOIMPF to get
onto the IFS as a text file.  The problem is that when I look at the
file on the IFS some of the fields come across as hex.  I've noticed
that all of these fields are result fields I built inside the query.
When I do a DSPFFD on the file created by the query I noticed that my
result fields have a specified CCSID of 65535 instead of 37 which all
of the other fields have.  That explains the problem but isn't there
some way to change this inside the query?  I'd like all fields to have
CCSID of 37 so that they are viewable after the CPYFRMIMPF.
Looks like my only other otion is to copy the query file to a file
that has all fields with CCSID of 37 but it seems like there should be
an easier work around.
Thanks,
Thad Rizzi

--
Kent Milligan
ISV Enablement - System i
 >>> ibm.com/iseries/db2
(opinions stated are not necessarily those of my employer)- Hide quoted text -
- Show quoted text -

SOX. I run a query over a file generated by DSPUSRPRF *ALL and print
reports each day. Now I want this data on the IFS for another process
to use.

[Thad Rizzi]

Post by Thad Rizzi
I've created a query that builds a file that I use a CPYTOIMPF to get
onto the IFS as a text file.  The problem is that when I look at the
file on the IFS some of the fields come across as hex.  I've noticed
that all of these fields are result fields I built inside the query.
When I do a DSPFFD on the file created by the query I noticed that my
result fields have a specified CCSID of 65535 instead of 37 which all
of the other fields have.  That explains the problem but isn't there
some way to change this inside the query?  I'd like all fields to have
CCSID of 37 so that they are viewable after the CPYFRMIMPF.
Looks like my only other otion is to copy the query file to a file
that has all fields with CCSID of 37 but it seems like there should be
an easier work around.
Thanks,
Thad Rizzi

Looks like I found it. My user profile was set to use CCSID 65535. I
changed it to 37 and when I recreated the query it worked just fine.
Also, I made a mistake in the initial post, I was using a CPYTOIMPF
not a CPYFRMIMPF.

BTW, what is the recommended setting for the system value QCCSID?

Thanks.

[Thomas]

Post by Thad Rizzi
BTW, what is the recommended setting for the system value QCCSID?

Thad:

IMO, the recommended setting is whatever is appropriate for the language
of its users. For example, systems with normal U.S. English users would
be well served by QCCSID 37.