• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Guru: Sorting Options For CPYTOIMPF

    March 27, 2017 Ted Holt

    I hope that whoever came up with the idea for the Copy to Import File (CPYTOIMPF) command was well compensated. When I think of the time and effort that that command has saved me and countless others, I feel deep gratitude. The addition of the ORDERBY parameter increased the usefulness of CPYTOIMPF, and I’d like to share that with you.

    CPYTOIMPF copies a single-format database file (table, physical file, view, or logical file) to a stream file or physical file in a format that is acceptable to another system or application. Probably the most common use of this command is to build a file of comma-separated values (CSV) for import into Microsoft Excel. Understanding a bit of the internals of this command can help you make even better use of it.

    When you run CPYTOIMPF, the underlying program creates a dynamic SQL statement. That is, it builds a SELECT statement in a character string variable, uses the PREPARE statement to convert the string into an executable statement, and executes the statement. Since the statement is prepared in a string variable, you can add clauses that normally follow ORDER BY in SQL queries to the end of the ORDERBY parameter.

    These are the clauses that SQL syntax permits to follow an ORDER BY expression.

    FETCH FIRST n ROWS 
    OPTIMIZE FOR n ROWS
    FOR UPDATE
    FOR READ ONLY
    WITH <isolation-level>
    SKIP LOCKED DATA
    USE CURRENTLY COMMITTED 
    WAIT FOR OUTCOME

    Most of these options are not applicable in the context of CPYTOIMPF. For example, FOR UPDATE is useless because CPYTOIMPF doesn’t change the retrieved data. The only clauses that I know of that will benefit you are FETCH FIRST n ROWS and SKIP LOCKED DATA. You can use FETCH FIRST to limit the number of rows to be copied. SKIP LOCKED DATA ignores data that is locked by other jobs.

    Here’s a CPYTOIMPF command to serve as an example. As you should have physical file QIWS/QCUSTCDT on your system, you can experiment as you desire. This command copies the data in the physical file to stream file custcdt-1.csv in the current directory.

    CPYTOIMPF FROMFILE(QIWS/QCUSTCDT)
              TOSTMF('custcdt-1.csv')
              MBROPT(*REPLACE)       
              STMFCCSID(*PCASCII)    
              RCDDLM(*CRLF)          
              RMVBLANK(*TRAILING)    
              ORDERBY('state, city') 
              ADDCOLNAM(*SYS)

    Notice the ORDERBY parameter. When the system builds the SQL SELECT, it places the value state, city immediately after ORDER BY. Here are more examples.

    ORDERBY(*arrival)

    Copy the data in arrival sequence (i.e. by relative record number).

    ORDERBY('baldue desc')

    Copy in descending order by balance due.

    ORDERBY('baldue desc fetch first 5 rows only')

    Copy in descending order by balance due, retrieving only the first five rows (records).

              ORDERBY(6,5)

    Sort the data by sixth column, and within that by the fifth column. This is equivalent to the first example, as the fifth and sixth columns of QCUSTCDT are city and state.

    What a wonderful utility!

    RELATED STORIES

    What’s New With CPYFRMIMPF And CPYTOIMPF?

    ADD ORDERBY parameter to CPYTOIMPF command

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags: Tags: Copy To Import File, CPYTOIMPF, Four Hundred Guru, Guru, IBM i

    Sponsored by
    FOCAL POINT SOLUTIONS GROUP

    IntellaFLASH™

    FPSG is the only hosting provider that offers IntellaFLASH™.  IntellaFLASH was created by FPSG and is an exclusive FPSG solution that provides the following:

    • No User downtime for production backups
    • Supports BRMS and Tivoli Storage Manager
    • Provides near Continuous Data Protection (CDP)
    • Create point-in-time copies of your entire environment within minutes
    • Easy and quickly repeatable
    • Processes are tied into Job Schedulers
    • No user downtime for planned outages
    • No disruption to the send and receive process production, and DR stays in sync during the Switch test
    • Supports heterogeneous environments
    • Create test/development environments on the fly
      ⇒ Simplify operating system/application upgrade testing efforts
      ⇒ Improve quality assurance testing

     

    Watch our IntellaFLASH™ Video to learn more

    Let’s Discuss Your Custom Solution Needs

    ContactUs@FocalPointSg.com

    Follow us on LinkedIn

    focalpointsg.com | 813.513.7402

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    As I See It: The Politics of Self-Disclosure IBM i Shops Seeking More Services

    3 thoughts on “Guru: Sorting Options For CPYTOIMPF”

    • Kelly Cookson says:
      March 27, 2017 at 10:56 am

      Nice tip. I appreciate the info.

      Reply
    • jonboy49 says:
      March 27, 2017 at 10:23 pm

      Had to chuckle when I read this Ted – your “wonderful utility” is my bête noire! I hate it with a passion. It is the reason why the very first Open Access handler I ever wrote was to produce CSVs directly – so I could avoid it!

      Reply
    • Allister Jenks says:
      February 19, 2019 at 8:30 pm

      As cool as the ORDERBY parameter is, this change has a nasty catch. It might seem obvious by the provision of an *ARRIVAL option for the new parameter that *NONE is not necessarily going to give you arrival sequence. But because *NONE is the default, any old code you have using CPYTOIMPF may be affected by this. We had a tool that worked fine for over 9 years suddenly start doing weird stuff after we upgraded to 7.2 recently. It seems SOMETIMES when you specify *NONE (or are just using old code that gets that as a default) it will essentially preserve arrival sequence, then split the file in half and swap the halves!!

      Reply

    Leave a Reply Cancel reply

TFH Volume: 27 Issue: 20

This Issue Sponsored By

  • Fresche
  • Quadrant Software
  • WorksRight Software
  • Computer Keyes
  • Northeast User Groups Conference

Table of Contents

  • The Bang For The Buck Of Entry IBM i Servers
  • IBM i Shops Seeking More Services
  • Guru: Sorting Options For CPYTOIMPF
  • As I See It: The Politics of Self-Disclosure
  • IBM Jacks Up Hardware Maintenance Fees

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • COMMON Set for First Annual Conference in Three Years
  • API Operations Management for Safe, Powerful, and High Performance APIs
  • What’s New in IBM i Services and Networking
  • Four Hundred Monitor, May 18
  • IBM i PTF Guide, Volume 24, Number 20
  • IBM i 7.3 TR12: The Non-TR Tech Refresh
  • IBM i Integration Elevates Operational Query and Analytics
  • Simplified IBM i Stack Bundling Ahead Of Subscription Pricing
  • More Price Hikes From IBM, Now For High End Storage
  • Big Blue Readies Power10 And IBM i 7.5 Training for Partners

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2022 IT Jungle

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.