Midrange News for the IBM i Community


Posted by: Viking
Information Systems
CA
Performance: Write directly to IFS or to a PF and CPYTOIMPF?
has no ratings.
Published: 09 May 2013
Revised: 10 May 2013 - 3975 days ago
Last viewed on: 28 Mar 2024 (7687 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

Performance: Write directly to IFS or to a PF and CPYTOIMPF? Published by: Viking on 09 May 2013 view comments(4)

Hi all,

If my end goal is to create a .csv file on the IFS for users to open in Excel, which way performs better... and RPG program that writes directly to the IFS, or an RPG program that writes to a PF and then does CPYTOIMPF to get the .csv file to the IFS?

I've done it both ways but am just curious which way performs better? This time I'm anticipating writing just a few hundred records monthly, if that makes a difference.

I like writing to the IFS directly from RPG especially when the number of fields/columns will vary, but this time the number of fields/columns will always be the same, so a PF would work.

Thanks.

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: Paulster
Premium member *
Sweden and The Netherlands
Comment on: Performance: Write directly to IFS or to a PF and CPYTOIMPF?
Posted: 10 years 10 months 19 days 19 hours 44 minutes ago

Don't do any direct RPG writing to IFS files but created a routine to copy any PF to a .csv in the IFS instead. This is an easy way to let the end-user extract whatever PF they have to .csv without any help from the IT dept.

Anyway, with this method, performance is no issue as it takes only seconds to copy 100k records to the IFS.

Posted by: DaleB
Premium member *
Reading, PA
Comment on: Performance: Write directly to IFS or to a PF and CPYTOIMPF?
Posted: 10 years 10 months 19 days 14 hours 50 minutes ago

I've never found performance to be a problem either way. With a few hundred records, it's not going to be an issue anyway. But there's definitely a lot more programming required to roll your own.

That being said, there are things you can do when you write your own, that CPYTOIMPF won't do for you. Excel sometimes interprets import data incorrectly, and there are no options to change how Excel handles the import. For example, if you have x,"0000123",y, Excel will change the value to 123, treating as a number and zero suppressing. But if that value represents an account number, that would be undesirable. CPYTOIMPF doesn't give you any way to fix this, but if you write your own, you could write it as x,="0000123",y, and it will appear correctly in Excel (it sees it as a formula that evaluates to a string literal).

Posted by: dag0000
Premium member *
Comment on: Performance: Write directly to IFS or to a PF and CPYTOIMPF?
Posted: 10 years 10 months 19 days 14 hours 6 minutes ago

Unless there are circumstances that the OP hasn't mentioned, it makes no sense to write all the code necessary to create that file directly and to create it correctly, when there is a tool available that can already do all that work. 

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: Performance: Write directly to IFS or to a PF and CPYTOIMPF?
Posted: 10 years 10 months 19 days 13 hours 27 minutes ago

Since I challenged IBM regarding their CPYTOIMPF command taking "centuries" to finish and my COZTOOLS CPYTOCSV taking seconds, they completely rewrote their command. It performs just as well as CPYTOCSV, and in v7r1 TR6, they added an ORDER By parameter and finally after 10 years of CPYTOCSV doing it, IBM finally added an option to insert the field names as the first row of the CSV file.

Since I don't make any money off of CPYTOCSV I don't care which version people use, but here are the pro's and cons of each implementation:

 

FeatureCPYTOCSVCPYTOIMPF
Copy an DB2 to CSV X X
Customized Separators X X
Header Row Options X X (TR6 and later)
Optionally Insert Excel Column Functions X  
Format Numeric Output X  
Tight (trimmed) quoted values X  
Convert Dates to Excel Dates X  
APIs to create CSV via RPG X  
Starting and End Record Selection X X
ORDER BY SQL-style parameter   X (TR6 and later)