Copy To Import File (CPYTOIMPF) in IBM i

Introduction and use of CPYTOIMPF command in IBM i
Copy To Import File CPYTOIMPF in IBM i, CPYTOIMPF, externally-described file, import file, non-numeric field and stream file,  database file member, save file, Input/output (I/O) operations, source member, stream file, Current Working Directory, Stream file,ibmi,as400,iseries,systemi, working with ifs,as400 and sql tricks, as400 tutorial, ibmi tutorial, working with ifs, working with integrated file system, CPYTOIMPF in as400, copy to import file in ibmi, copy to import file in as400, copy pf to source member using cpytoimpf in ibmi as400, copy PF to flat file using cpytoimpf in ibmi as400, copy pf to stream file in ifs using cpytoimpf in ibmi as400
Copy To Import File CPYTOIMPF in IBM i

Copy To Import File (CPYTOIMPF)

The Copy To Import File (CPYTOIMPF) command copies an externally-described file to an import file. The import file is the one which is created to copy data between different databases such as copying external PF to source physical file, and to externally-described physical file with one non-numeric field and to stream file.

Copy To Import File (CPYTOIMPF) to copy an externally-described file to a source physical file

  • Let's create the physical file PF20 in library EASYCLASS1 by executing the following DDL script.
  • CREATE or replace                                                 
      TABLE EASYCLASS1/PF20 (IDN DECIMAL (2 , 0) NOT NULL WITH          
     DEFAULT, NAME CHAR (20 ) NOT NULL WITH DEFAULT, ADDR1 CHAR (20 )   
     NOT NULL WITH DEFAULT, ADDR2 CHAR (50 ) NOT NULL WITH DEFAULT) 
    

    PF20 has been created.

    Table PF20 in EASYCLASS1 created but was not journaled.
  • Let's insert a record into PF20 using SQL insert.
  • INSERT INTO EASYCLASS1/PF20 VALUES(1, 'testname', 'testaddr1',     
     'testaddr2')                                                                                    
    
    1 rows inserted in PF20 in EASYCLASS1
  • Now using the CPYTOIMPF command to copy an externally-described file to source file member.
  •                          Copy To Import File (CPYTOIMPF)                       
                                                                                   
     Type choices, press Enter.                                                    
                                                                                   
     From file:                       FROMFILE                                     
       File . . . . . . . . . . . . .              > PF20                          
         Library  . . . . . . . . . .              >   EASYCLASS1                  
       Member . . . . . . . . . . . .                *FIRST                        
     To data base file:               TOFILE                                       
       File . . . . . . . . . . . . .              > DDSTODDL2                     
         Library  . . . . . . . . . .              >   EASYCLASS1                  
       Member . . . . . . . . . . . .              > MBR4NEW                      
     To stream file . . . . . . . . . TOSTMF                                       
                                                                                   
     Replace or add records . . . . . MBROPT         *ADD                          
     From CCSID . . . . . . . . . . . FROMCCSID      *FILE                         
     To CCSID . . . . . . . . . . . . TOCCSID        *FILE                         
     Record delimiter . . . . . . . . RCDDLM         *EOR                          
     Record format of import file . . DTAFMT         *DLM                          
     String delimiter . . . . . . . . STRDLM         *DBLQUOTE                     
    

    This takes the data in physical file named 'PF20' and insert it into source file DDSTODDL2 in library EASYCLASS1 member MBR4NEW. Please note if source member does not exist then it will be created after executing this command.

                               Work with Members Using PDM       
                                                                 
     File  . . . . . .   DDSTODDL2                               
       Library . . . .     EASYCLASS1           Position to  . . 
                                                                 
     Type options, press Enter.                                  
      2=Edit         3=Copy  4=Delete 5=Display       6=Print    
      8=Display description  9=Save  13=Change text  14=Compile  
                                                                 
     Opt  Member      Type        Text                           
          MBR3NEW                                                
          MBR4NEW                                                
                                                                 
    

    Here, member MBR4NEW gets created.

    Display member MBR4NEW

     Columns . . . :    1 100                                      Browse                                          EASYCLASS1/DDSTODDL2
     SEU==>                                                                                                                     MBR4NEW
     FMT **  ...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8 ...+... 9 ...+... 0                       
            *************** Beginning of data ***************************************************************************************  
    0001.00 4   ,"NAME4               ","ADDRESS4            ","PART2                                                000000            
    0002.00 4   ,"NAME4               ","ADDRESS4            ","PART2                                                000000            
    0003.00 4   ,"NAME4               ","ADDRESS4            ","PART2                                                000000            
            ****************** End of data ******************************************************************************************  
    

    Copy To Import File (CPYTOIMPF) to copy an externally-described file to an externally-described physical file with one non-numeric field

  • First create a physical file with one non numeric field having record length of 110 with no DDS using the below CRTPF command.
  •  CRTPF FILE(EASYCLASS1/PF21) RCDLEN(110)
  • Data in PF20 that has to be copied PF21.
  • RUNQRY *N PF20
                                                             Display Report                                                           
                                                                                                     Report width . . . . . :     100 
    Position to line  . . . . .                                                                  Shift to column  . . . . . .         
    Line   ....+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8....+....9....+...10                       
           IDN   NAME                  ADDR1                 ADDR2                                                                    
    000001   4   NAME4                 ADDRESS4              PART2                                                                    
    000002   4   NAME4                 ADDRESS4              PART2                                                                    
    000003   1   testname              testaddr1             testaddr2                                                                
    ****** ********  End of report  ********   
    
  • Execute the following CPYTOIMPF command to copy an externally-described file to an externally-described physical file with one non-numeric field'
  •                          Copy To Import File (CPYTOIMPF)                      
                                                                                  
     Type choices, press Enter.                                                   
                                                                                  
     From file:                       FROMFILE                                    
       File . . . . . . . . . . . . .              > PF20                         
         Library  . . . . . . . . . .              >   EASYCLASS1                 
       Member . . . . . . . . . . . .                *FIRST                       
     To data base file:               TOFILE                                      
       File . . . . . . . . . . . . .              > PF21                         
         Library  . . . . . . . . . .              >   EASYCLASS1                 
       Member . . . . . . . . . . . .              > *FIRST                        
     To stream file . . . . . . . . . TOSTMF                                      
                                                                                  
     Replace or add records . . . . . MBROPT         *ADD                         
     From CCSID . . . . . . . . . . . FROMCCSID      *FILE                        
     To CCSID . . . . . . . . . . . . TOCCSID        *FILE                        
     Record delimiter . . . . . . . . RCDDLM         *EOR                         
     Record format of import file . . DTAFMT         *DLM                         
     String delimiter . . . . . . . . STRDLM         *DBLQUOTE                    
    

    This takes the data in file 'PF20' and insert it into physical file 'PF21'

     Parameters or command                                         
     ===>                                                          
     F3=Exit   F4=Prompt   F5=Refresh   F9=Retrieve   F12=Cancel   
     F22=Display entire field           F23=More options           
     All records copied from file PF20 in EASYCLASS1.              
    
    RUNQRY *N PF21
                                                             Display Report                                                           
                                                                                                     Report width . . . . . :     110 
    Position to line  . . . . .                                                                  Shift to column  . . . . . .         
    Line   ....+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8....+....9....+...10....+...11             
           PF21                                                                                                                       
    000001 4   ,"NAME4               ","ADDRESS4            ","PART2                                             "                    
    000002 4   ,"NAME4               ","ADDRESS4            ","PART2                                             "                    
    000003 1   ,"testname            ","testaddr1           ","testaddr2                                         "                    
    ****** ********  End of report  ******** 

    Copy To Import File (CPYTOIMPF) to copy an externally-described file to a stream file

  • Data in PF20 that has to be copied PF20 in IFS home directory /home/EASYCLASSPF20.
  • RUNQRY *N PF20
                                                             Display Report                                                           
                                                                                                     Report width . . . . . :     100 
    Position to line  . . . . .                                                                  Shift to column  . . . . . .         
    Line   ....+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8....+....9....+...10                       
           IDN   NAME                  ADDR1                 ADDR2                                                                    
    000001   4   NAME4                 ADDRESS4              PART2                                                                    
    000002   4   NAME4                 ADDRESS4              PART2                                                                    
    000003   1   testname              testaddr1             testaddr2                                                                
    ****** ********  End of report  ********   
    
  • Execute following CPYTOIMPF command to copy from physical file PF20 in library EASYCLASS1 to stream file 'PF20' in IFS home directory /home/EASYCLASS.
  •                          Copy To Import File (CPYTOIMPF)                       
                                                                                   
     Type choices, press Enter.                                                    
                                                                                   
     From file:                       FROMFILE                                     
       File . . . . . . . . . . . . .              > PF20                          
         Library  . . . . . . . . . .              >   EASYCLASS1                  
       Member . . . . . . . . . . . .                *FIRST                        
     To data base file:               TOFILE                                       
       File . . . . . . . . . . . . .                                              
         Library  . . . . . . . . . .                  *LIBL                       
       Member . . . . . . . . . . . .                *FIRST                        
     To stream file . . . . . . . . . TOSTMF       > '/HOME/EASYCLASS/PF20'        
                                                                                   
     Replace or add records . . . . . MBROPT         *ADD                          
     From CCSID . . . . . . . . . . . FROMCCSID      *FILE                         
     Stream file CCSID  . . . . . . . STMFCCSID      *STMF                         
     Stream file authority  . . . . . STMFAUT        *DFT                          
     Record delimiter . . . . . . . . RCDDLM       > *CRLF                         
     Record format of import file . . DTAFMT         *DLM                          
    

    This takes the data in physical file PF20 in library EASYCLASS1 copy to stream file 'PF20' in IFS home directory /home/EASYCLASS.

     Parameters or command                                         
     ===>                                                          
     F3=Exit   F4=Prompt   F5=Refresh   F9=Retrieve   F12=Cancel   
     F22=Display entire field           F23=More options           
     All records copied from file PF20 in EASYCLASS1.              
    

    Work with Links to browse IFS stream file PF20.

    WRKLNK
                                 Work with Object Links                            
                                                                                   
     Directory  . . . . :   /home/EASYCLASS                                        
                                                                                   
     Type options, press Enter.                                                    
       2=Edit   3=Copy   4=Remove   5=Display   7=Rename   8=Display attributes    
       11=Change current directory ...                                             
                                                                                   
     Opt   Object link            Type             Attribute    Text               
           CPYTOSTMF1             STMF                                             
           PF20                   STMF                                             
           SAVF1                  STMF                                             
           UCSfile                STMF                                             
           UCSfile2               STMF                                             
           UCSfile4               STMF

    Display data in IFS stream file.

     Browse : /home/EASYCLASS/PF20                                                                                                     
     Record :       1   of       3 by  18                      Column :    1    103 by 131                                             
     Control :                                                                                                                         
                                                                                                                                       
    ....+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8....+....9....+....0....+....1....+....2....+....3.
     ************Beginning of data**************                                                                                       
    4   ,"NAME4               ","ADDRESS4            ","PART2                                             "                            
    4   ,"NAME4               ","ADDRESS4            ","PART2                                             "                            
    1   ,"testname            ","testaddr1           ","testaddr2                                         "                            
     ************End of Data********************

    Post a Comment

    © AS400 and SQL Tricks. All rights reserved. Developed by Jago Desain