TomLiotta Jan 14, 2010   4:40 AM GMT

It's not clear what you mean by "workaround". What that means to me is that you need to keep your system and files configured incorrectly while still achieving desired results. In the past, CPYTOIMPF could give the desired results even from incorrect input. But now that it works properly, the results are coming out according to the way things are configured, which isn't what you want. Therefore, you need a "workaround". Why not just correct your configuration and get it over with? Tom

125,585 pointsBadges:

LDB Jan 14, 2010   5:24 AM GMT

I don't know what you mean by correct the configuration. The system is a customer's site, and has a mixed environment. The older files are "flat" files and the new files are externally defined. These files have a CCSID assigned automatically to them when they are created (newer ones are 37). It is not my opinion that the CPYTOIMPF has problems, it is documented on an IBM page, stating that after a fix to a previous problem, now CPYTOIMPF does not convert to the tofile CCSID. Obviously, much more detail then that. Also, as I said in the original post; CPYTOPCD doesn't have any problems with converting the CCSID code. Although it doesn't code it as 819, it codes it as 437, which has never given me a problem when I had to FTP it to a PC/server. Here is what I am trying to do; instead of coding an rpg pgm to output a flat file with commas and double quotes, that I then will copy to the IFS with CPYTOPCD, and then from there FTP to a server... I want to avoid coding the rpg pgm. Copy/convert the DDS data file to a CSV file that can be FTPed to the sever. I have found a couple of comments indicating the use of a few steps involving doing a CPYTOSTMF and CPTYTOIMPF (some steps are even on the site I gave above). However, these "work arounds" are not for creating a CSV file. P.S. I can copy my DDS file using CPYTOPCD (it creates it with CCSID 437 (not CSV)), and I can FTP with no problem. Additionally, I've created a file using CPYTOIMPF with a CCSID 437, however when trying to FTP the file it ends with error 451 input/out problems. So, I don't think the code is actually 437. Thanks

45 pointsBadges:

TomLiotta Jan 14, 2010   7:48 AM GMT

Fair enough, but what is the problem? That is, what CCSIDs are involved in the job and how is the resulting streamfile created? Needed info would be the QCCSID system value, the job CCSID (and possibly job default CCSID) of the job that puts lines into the streamfile, the CCSID of the input file, and an actual CPYTOIMPF command that is executed. And, the previous discussion seems that the streamfile doesn't exist when CPYTOIMPF is executed -- is that true? The FTP 451 error would seem to indicate that CCSID conversion could be a prime suspect. If bit patterns are inserted into the streamfile that don't belong due to a conversion mismatch, then FTP could indeed have a problem with encoding. Tom

125,585 pointsBadges:

LDB Jan 14, 2010   5:28 PM GMT

Thanks Tom. The system is set with defaults. Sysval is 65535, my user profile defaults to sysval. The commands I am running during this testing phase are run interactively. The job session has defaults of "Coded character set identifier" 65535, and a "Default coded character set identifier" of 37. When CRTPF is used to create a data base file, the file's CCSID is 65535. I have done a lot of reading online (and some IBM) and it appears to me that these defaults are just that the typical defaults. I have tried many things so far and have managed to create an export file with code 437, but FTP still rejects it. This means that either my FTP is wrong or the file is end coded wrong. I tested my FTP by doing the tried and true method of taking a DB file and convert it using CPYTOPCD, which creates a file with a code of 437, then FTP without a problem. The work arounds that I have come across mostly all talk about a 2 step approach of using CPYTOIMPF and CPYTOSTMF. The latest attempt was from IBM Info Ctr http://publib.boulder.ibm.com/html/as400/v4r5/ic2924/index.htm?info/db2/rzaaxmstcpusingcl.htm Based on the information on that IBM page; which suggested these two steps (similar to other suggestions I've read). Data conversion is provided on these commands if the target stream file or database member already exists. If the file does not exist, you can use the following two-step method to get the data converted: Use the CPYTOIMPF and CPYFRMIMPF commands to copy the data between the externally-described file and a source physical file. Use the CPYTOSTMF and CPYFRMSTMF commands (which provide full data conversion regardless of whether the target file exists) to copy between the source physical file and the stream file. Here is an example: CPYTOIMPF FROMFILE(DB2FILE) TOFILE(EXPFILE) DTAFMT(*DLM) FLDDLM(';') RCDDLM(X'07') STRDLM('"') DATFMT(*USA) TIMFMT(*USA) My actual steps this time was to create a source physical file using CRTPF. After created I checked the code and it was 37. I did a CPYTIMPF as described above where I copied my externally described file to the source pf I just created. I told it *DLM, the dilimeter info, and even the code that I was unsure of RCDDLM(X'07'). It seemed to work fine. It was CSV format and coded 37. Which I expected. I then did as indicated above CPYTOSTMF. Copied the source file into a new file (one that did not already exist), a stmf called /fltrnsfr/wbssinvx.csv This also seemed to work fine. The data is in CSV format and the file is coded as 437. Then did FTP I tried it with BIN, and without BIN (ASCII), both attempts failed. I just now looked into the stmf w/F10 and see some of the coding. This stuff is pretty much greek to me, but the last code I notice is not the same as files that I've converted with CPYTOPCD, but then maybe it shouldn't be. It seems to be 201C0D0A At this point I think I may have to throw in the towel. I think I will need to code things the "old way." I hope all this gives you some sense of what is going on and what I am trying to do. Thanks

45 pointsBadges:

LDB Jan 14, 2010   9:17 PM GMT

I have finally copied and converted the file with a CCSID code of 819 and FTPed it over to the PC/server. The person who is able to view it on the server said that it looks good to him. Thank you for your help. I wrote up notes on the steps I took to get the file the way I needed it, maybe it will be of help to someone else. I'vepaste them below, but if it is too much information, just ignore it. Thanks again. Linda Steps to get an externally defined DB file converted to CSV stream file, and then FTPed to PC Server. 1. Create a source-pf Because CPYTOIMPF doesn’t convert externally defined files. CRTPF FILE(lib/new file name) RCDLEN(rec length) FILETYPE(*SRC) 2. Copy the externally defined data file into the source file. I am typing up even some of the defaults for this command; which I’ve underlined. My externally defined file had a CCSID code of 37 Also, note that RCDDLM was suggested to me to be X’07, however I used *EOR, and didn’t seem to have a problem. I used DTAFMT(*DLM) in order to convert the file to a delimited file CSV. I used the standard string delimiter of a double quote, and a field delimiter of a comma. Anything else that I didn’t specify was the default. CPYTOIMPF FROMFILE(lib/ext defined file name or logical view) TOFILE(lib/source file name) MBROPT(*REPLACE) FROMCCSID(*FILE) TOCCSID(*FILE) RCDDLM(*EOR) DTAFMT(*DLM) STRDLM(*DBLQUOTE) FLDDLM(‘,’) 3. Copy the source file which now had the data and delimiters into a stream file, and convert the CCSID. Note that the “from” file member is part of the QSYS.LIB file system and has certain syntax that must be used when expressing the file. Below is how the syntax should be expressed. The “to” stream file is expressed using the root file system syntax. Syntex for QSYS.LIB file system /QSYS.LIB/library name.LIB/file name.FILE/member name.MBR CPYTOSTMF FROMMBR(‘/QSYS.LIB/library name.LIB/file name.FILE/member name.MBR’) TOSTMF(‘/directory name/file name’) STMFOPT(*REPLACE) STMFCODEPAGE(00819) ENDLINFMT(*CRLF) 4. Lastly, FTP the stream file over to the PC/server. FTP RMTSYS(ip address) user id password NAMEFMT 1 LCD /local directory name CD /target systems directory name PUT stream file name QUIT

45 pointsBadges:

LDB Jan 14, 2010   11:13 PM GMT

Thanks Teandy. Read my last post in the discussion; I was able to accomplish the task and I documented the steps. The problem stems from the fact that I had an externally defined file CCSID 37 that I needed to make into a CSV streamfile, and code ASCII and send FTP. Anyway, it is done. Thanks

45 pointsBadges: