Locate DB2 files on the iSeries

Henry wrote in with this question recently: "I work with Window-based platforms and know nothing about the iSeries. I need to locate DB2 files on the iSeries and copy or export them to a Windows XP machine where the data will be used with another application. Also, somewhere in the process, I need to convert the data to text delimited file." Can you get Henry started? -- Deb Tart, associate editor

Answer Wiki

Thanks. We'll let you know when a new response is added.

Hi Debra,

Any *FILE object on the AS/400 is a DB2 table. To get a list of the *FILE, or DB2 table objects you can use the ‘DSPOBJD *ALL/*ALL *FILE’ command.

To copy a DB2 table to a Windows system he can use FTP, or instead of that he can use ODBC/JDBC to access the data on the AS/400 directly. This way the data is up to date.

To convert data from the AS/400 he can use the CPYTOSTMF and CPYTOIMPF commands. FTP will also convert but not binary and packed decimal columns. There are also conversion utilities available that will do the job. Using these commands he can write the converted table to the IFS and copy it from there to his Windows system through network serving (AS/400 function) or through Client Access.

Best regards


Discuss This Question: 3  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • JPLamontre
    an other approach can be using ODBC. Depending on skill available, * uses WIN programming based on ODBC with VB or Excel (ie msquery) or * uses iSeries programming based on CPYTOIMPF or CPYTOSTMF with output to the correct NT server declared in QNTC
    0 pointsBadges:
  • WaltZ400
    You can also direct query file QADBXREF in QSYSY library. This contains a header record for each data base file on the system and is automatically maintained by the operating system. It contains basic info about the file such as name, library it resides on, number of fields and keys, etc. Make sure you select only records where column DBXTYP equals 'D' so you don't look at source files that contain program source code etc. These files are tagged with a code 'S'. Also if you are only interested in finding physical data files and not logical views, also select column DBXTYP equal to 'PF'.
    655 pointsBadges:
    You can query access the table SYSTABLES in the Schema QSYS2 to retreive the list of DB2 tables. Here is a link to an article which should help you build the files you need. http://www.itjungle.com/fhg/fhg040605-story02.html
    0 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: