Copy DDS defined file to .txt file
I am trying to copy a large, 1.4 mil records, 535 byte records, from an old DDS defined file on the iSeries to a .txt file. I have used FTP and CPYTOIMPF using every combination of setting I can think of. Every time it translates the packed numeric fields correctly but creates garbage for the alpha fields. I would like to put this file in the IFS but sending straight to a pC would be OK also. Does anyone have any suggestions that I could try to resolve this dilema?
Software/Hardware used:
Software/Hardware used:
ASKED:
March 27, 2008 8:55 PM
UPDATED:
April 9, 2010 3:05 AM
Answer Wiki:
Hi,
Sounds like a problem in the CCSID. Take a look at your joblog when you run the CPYTOIMPF and see if you get any warning message about CCSID.
IF all else fails, you could try using query to create a file without the packed fields and FTPing it to the PC.
Regards,
Martin Gilbert.
=========================================================
It is almost certain to be a CCSID issue. First thing to check is if the QCCSID system value has ever been set to a useful value or if it's still at the default value of 65535. If it's 65535, then determine the appropriate value and begin to get your system setting ready to change. (Most sites can change it in the middle of a work day, and no one will notice that it was done.)
Most often, the appropriate value has been guessed by your system. You can see the "guess" by using DSPJOB to see what your interactive job is using. Look at your job's Job Definition Attributes and scroll down almost to the bottom to see "Default coded character set identifier". The default chosen by your system is the likely CCSID for the system.
Before running your CPYTOIMPF command:<pre>
CHGJOB CCSID(37)</pre>
Set your job to your 'default' CCSID. (For me, it would be 37.) Then make sure the streamfile that you'll be copying in to has bee3n deleted. (You'll want to create a new one when you run the copy.) Then try:<pre>
CPYTOIMPF FROMFILE( x )
TOSTMF( y )
STMFCODPAG(*PCASCII)
RCDDLM(*CRLF)</pre>
Let the command run under your new job CCSID and create the output streamfile. When it finishes, view the streamfile.
If it looks okay, you know that you need to manage the CCSID settings when you run the command. It it has garbage, then look at the file description of the database file and file field descriptions of that file. In particular, look for fields that may also be tagged with CCSID 65535.
There are various paths to take, but this should get you either past the problems or pointed to the precise sources of your problems.
Tom
To see all answers submitted to the Answer Wiki: View Answer History.
Hi there,
You could user several options:
1) Try using iSeries Access to download the file to text.
2) Try using the Client Access Data Transfer Add In to MS Excel
3) You could convert the data to text before sending it to the IFS:
Create a file with a unique field with the size of the record you are trying to convert and then concatenate character fields with casted-to-text-non-character fields into this new file and then try your original solution or one of the suggestions we give you here.
Good luck!
Gerardo
You can use Data Transfer from the iSeries (part of IBM client access). before you start the transfer click on file, properties and under the conversion tab make sure you check the “Convert CCSID 65535″ box.
Regards,
Hi:
I’ve found you need to use different commands depending on the ccsid and/or file type (internal or extrenally defined). Try the cpytostmf (copy to stream file). This solved my most recent issue.
Thank you for the response. There is nothing in the job log of any kind about the CPYTOIMP, error or otherwise. I tried using QRY400 and got the same results. I tried importing into Excel even though I knew it wouldn’t all fit and I tried importing into Access 2003. All attempts returned the same results. I think I will give them the DDS version of the file and let them worry about it.
It might be also that you have non displayable charaters in those text fields, try displaying their contents in hexadecimal format to see if in the places where a blank is displayed there is other code than X:40, maybe the problem is in the source.