-1

I want to export a database file that is created through a query, from the AS400 machine to my pc in the form of a csv file.

Is there a way to create that connection of the AS400 and my pc through a cl program?

An idea of what I want to do can be derived from the following code:

CLRPFM     DTABASENAME                         
RUNQRY     QRY(QRYTEST1)             
                   
                                        
CHGVAR     VAR(&PATH)  VALUE('C:\TESTS')     
CHGVAR     VAR(&PATH1) VALUE('C:\TESTS')    
CHGVAR     VAR(&CMD)   VALUE(%TRIM(&PATH) *CAT '/DTABASENAME.CSV' !> &PATH !> &PATH1)                     
                                                               
STRPCO     PCTA(*YES)                                        
STRPCCMD   PCCMD(&CMD) PAUSE(*YES)

where I somehow get my database file, give the path that I want it to be saved in, in my pc , and lastly run the pc command accordingly

Alkis Ko
  • 99
  • 1
  • 12

2 Answers2

1

Take a look at
Copy From Query File (CPYFRMQRYF)
Which will allow you to create a database physical file from the query.

You may also want to look at
Copy To Import File (CPYTOIMPF)
Which will copy data from a database physical file to an Integrated File System (IFS) stream file (such as .CSV); which are the type of files you'd find on a PC.
ex:

CPYTOIMPF FROMFILE(MYLIB/MYPF) TOSTMF('/home/myuser/DTABASENAME.CSV') RCDDLM(*CRLF) DTAFMT(*DLM) STRDLM(*DBLQUOTE) STRESCCHR(*STRDLM) RMVBLANK(*TRAILING) 
FLDDLM(',')

However, there's no single command to transfer data to your PC. Well technically, I suppose that's not true. If you configure a (SMB or NFS) file share on your PC and configure the IBM SMB or NFS client; you could in fact CPYTOIMPF directly to that file share or use the Copy Object (CPY) command to copy from the IFS to the network share.

If your PC has an FTP server available, you could send the data via the IBM i's FTP client. Similarly, if you have a SSH server on your PC, OpenSSL is available via PASE and SFTP or SCP could be used. You could also email the file from the i.

Instead of trying to send the file to your PC from the i. An easier solution would be to kick off a process on the PC that runs the download. My preference would be a Access Client Solution (ACS) data transfer.

You configure and save (as a .dtfx file) the transfer
ACS Data Transfer

Then you can kick it off with a STRPCCMD cmd('java -jar C:\ACS\acsbundle.jar /plugin=download C:\testacs.dtfx')

More detailed information can be found in the Automating ACS Data Transfer document

The ACS download compoent is SQL based, so you could probably remove the need to use Query/400 at all

enter image description here

Charles
  • 21,637
  • 1
  • 20
  • 44
  • Thank you for your answer! So a bit more specifically, if i already have a databasee file and a pc with an ftp server, what i would need is to use the cpytoimpf command and save my database file into a stream file in my pc ? Would i also need to runn something through strpccmd? – Alkis Ko Apr 05 '21 at 08:01
  • 1
    You can script the IBM i built in FTP client, but I'd highly recommend [FTP API](https://www.scottklement.com/ftpapi/) – Charles Apr 05 '21 at 13:45
0

Assuming that you have your IFS QNTC mapped to your network domain. You could use the command CPYTOIMPF to copy the data directly from an IBMI DB2 file to a network directory.

This sample would result in a CSV file.

CPYTOIMPF FROMFILE(file) TOSTMF('//QNTC/servername or ip/path/filename.csv') STMFCCSID(*PCASCII) RCDDLM(*CRLF) STRDLM(*NONE)

Use the FLDDLM(';') option in addition to make semicolon separated values, omit it to use comma as value separator.

double-beep
  • 5,031
  • 17
  • 33
  • 41
Michael Guenther
  • 1
  • Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jan 29 '23 at 08:27