Query CCSID - IBM AS400

This is a discussion on Query CCSID - IBM AS400 ; 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 ...

+ Reply to Thread
Results 1 to 8 of 8

Thread: Query CCSID

  1. Query CCSID

    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

  2. Re: Query CCSID

    Thad Rizzi wrote:
    > 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

  3. Re: Query CCSID

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

    Thad Rizzi wrote:
    > 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
    kmill@us.eye-bee-m.com (spam trick) GO HAWKEYES!!
    >>> ibm.com/iseries/db2

    (opinions stated are not necessarily those of my employer)

  4. Re: Query CCSID

    On Oct 28, 10:52*am, Thad Rizzi wrote:
    > 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.

  5. Re: Query CCSID

    On Oct 28, 12:24*pm, Kent Milligan wrote:
    > Why are you trying to take data out of DB2 for i?
    >
    >
    >
    >
    >
    > Thad Rizzi wrote:
    > > 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
    > km...@us.eye-bee-m.com (spam trick) * GO HAWKEYES!!
    > *>>> 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.


  6. Re: Query CCSID

    On Oct 28, 11:17*am, Karl Hanson wrote:
    > Thad Rizzi wrote:
    > > 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


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

  7. Re: Query CCSID

    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.

    Thad Rizzi wrote:
    > On Oct 28, 11:17 am, Karl Hanson wrote:
    >> Thad Rizzi wrote:
    >>> 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

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


    --
    Kent Milligan
    ISV Enablement - System i
    kmill@us.eye-bee-m.com (spam trick) GO HAWKEYES!!
    >>> ibm.com/iseries/db2

    (opinions stated are not necessarily those of my employer)

  8. Re: Query CCSID

    Thad Rizzi wrote:

    > 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.

    --
    Tom Liotta
    http://zap.to/tl400

+ Reply to Thread