Ispirer Ispirer
 


Ispirer Home Page Ispirer SQLWays Product Page - Migration to SQL Server Request SQLWays

How to Extract Logical and Physical Files From DB2 iSeries (AS400)

This article explains how to extract DDS (Data Definition Structure) for Logical files from DB2 iSeries (AS400) database. DDS information can be extracted by copying the MBR files from server or compiling the logical files. Below you can find a step by step guide on how it can be done.

Copy MBR files (recommended)

Data Definition Structure is stored in the MBR files on DB2 iSeries (AS400) server, like it is shown on the screenshot:

MBR files can be found in the library where they were created. An example of a path where MBR files can be stored is shown below:

/QSYS.LIB/<LIB_NAME>.LIB/<DDS_FILE_NAME>.FILE/<MBR_FILE_NAME>.MBR

<LIB_NAME> - library, in which files with DDS are stored

<DDS_FILE_NAME> - file with DDS (usually it’s QDDSSRC)

<MBR_FILE_NAME> - desired MBR file.

So you need to copy these MBR files using FTP client or DOS FTP Client.

Using FTP client

We will use FileZilla as an FTP client in our example.

To connect to an iSeries (AS400) FTP server, you need to specify:

- Host - the name of the server to connect

- Username

- Password

- Port (optional) - FTP server port

After successful connection to the server, it is required to set the ASCII transfer type. To do this, you need to go to the “Transfer” tab and set the “Transfer type” option to ASCII, like it is set below:

The screenshot below shows an example of the location of a file that contains the DDS of a physical or logical file.

First, you need to find a file that stores DDS. In this case, this is QDDSSRC.FILE in the ISPTESTER1 library. In this example, EXAMPLEDDS.MBR is selected, which stores DDS. In order to download this file from an FTP server, you need to right-click on it and select “Download”. Also, you can download multiple files at once by selecting more than one file. Now this file is located on your local disk. The file from the example “EXAMPLEDDS.MBR” stores the following content, which is the required DDS:

Please note: If the contents of the downloaded file is presented in binary form, then you need to double-check your transfer type. Please see above.

Using DOS FTP client (Command line)

To copy MBR files using the DOS FTP Client you need to connect to the FTP server in your command window (terminal):

ftp <your server> [:<port number>].

Then enter the requested username and password.

Using the cd command, go to the directory where required mbr files are located. Usually this is QDDSSRC.FILE.

Use the “get” command to download the required MBR file.

Also, you can download several files at once with the mget command. In the screenshot below, the mget command is specified with the “*” option, which will download all files from the current directory.

If you want to specify only some files, you can list them with a delimiter (space) as follows:

mget <file1> <file2> <file3>

Note: With the “ls” command, you can list the names of the files in this directory.

Please note: If the contents of the downloaded file is presented in binary form, then you need to change your transfer type. It can be done by specifying ftp command “ascii”.

Using Compile and WRKSPLF (Working with Spooled Files)

After compiling a physical or logical file, the result will be stored in the Spooled files. And in this files are stored DDS that we need.

Below is an example of the result of the EXAMPLEDDS compilation, which stores the DDS. DDS can be copied from the compilation result.

To get the same results you need to compile the logical file and then find the appropriate Spooled file.

To compile the logical file you need to go to the PDM (Programming Development Manager), by selecting point 5 of IBM i Main Menu and then point 2 - PDM. Then you need to select the required library and a file. To do this, you need to select point 1 - “Working with libraries”. Specify the library in which the file resides. In the screenshot below, ISPTESTER1 is required library.

In the appeared window it is necessary to specify the option 12 - “Working with” for the selected library (in this example ISPTESTER1). Then find the file that stores the DDS. Usually this is a QDDSSRC file and specify the option 12.

Then you need to compile the required physical or logical file by specifying option 14 - “Compile”. After compilation, you can see the result of compiling the EXAMPLEDDS file in Spooled Files.

Then you need to search in Spooled files by using the Spooled Files work menu. To enter this menu you need to specify “WRKSPLF” in the command input line.

In the menu that will be opened, you need to find the result of compiling of the required file. In this case it will be an EXAMPLEDDS file. To display file contents you need to specify option 5.

And you will see the results of compilation, where DDS will be stored.


Ispirer Home Page Ispirer SQLWays Product Page - Migration to SQL Server Request SQLWays

sqlways/faq/extract-logical-files-and-physical-files-from-as400.txt · Last modified: October 27, 2017, 05:38:23 AM by alexandr.kirpichny
 
© 1999-2017, Ispirer Systems Ltd.
All Rights Reserved.  Privacy Statement