How can i get a dump of entire DB2 schema?

Posted on 2010-09-09
Last Modified: 2013-12-06
Hi All,

We are using a DB2/400 database. For a migration requirement, we are required to dump all data in all tables of a schema into a text file or into a excel file (or CSV file). Is there an easy way to do it preferably using the iSeries Navigator GUI?

The team that will be working on this is not too familiar with AS400 commands so I will have to do with using only the GUI.

Question by:bhagatali
LVL 34

Accepted Solution

Gary Patterson earned 500 total points
Comment Utility
There is no "push button" way to do this for all tables in a schema at once.  You can use Client Access File Transfer function to download tables one at a time in CSV, Excel, or any of a number of additional formats.

Personally, I'd just write a short script (I prefer CL for these tasks) to dump a list of tables for each schema to a file, and then read through the file, executing the CPYTOIMPF commmand for each table to generate a CSV for each table.

The program below is an example - it has not been tested, and may not work as expected.  Use it at your own risk.

Cut and paste source code into SEU, save, and compile using the CRTCLPGM command (or use PDM).
Run program: CALL yourlib/yourprogramname PARM('SCHEMA_NAME_TO_DUMP')

Program will compile a list of all table in the named schema, and the convert them one by one into CSV files, storing them in the AS/400 IFS under the /CSVFILES folder.  IF your system already has a folder with that name, modify the program to use a different folder.

- Gary Patterson

PGM PARM(&SCHEMA)                                                    
DCLF QAFDBASI            /* System outfile for DSPFD OPTION(*BASATR) */                                                      
DCL &SCHEMA *CHAR 10     /* SCHEMA (LIBRARY) TO DUMP  */             
DCL &PATH *CHAR 117      /* AS/400 IFS PATH FOR CSV FILES */         
DCL &FILE *CHAR 128      /* STREAM FILE PATH AND NAME */             
             MD &ROOTPATH             /* Create the base folder undr the root of the IFS */
                 MONMSG CPF0000

             CHGVAR     VAR(&PATH) VALUE(&ROOTPATH *TCAT '/' *CAT &SCHEMA)                                   
             MD         DIR(&PATH)      /* Create subdirectory under CSVFILES for this schema */                             
               MONMSG CPF0000                                        

             /* Dump list of all physical files (tables) in this lib (schema) */
             DSPFD      FILE(&SCHEMA/*ALL) TYPE(*BASATR) +           
                          OUTPUT(*OUTFILE) FILEATR(*PF) +            

             /* Override to newly-created outfile */
READ:        RCVF                                                    
                   MONMSG   CPF0864  EXEC(GOTO EOF) /* Monitor for EOF */                    
             /* Build CSV path and filename */
              CHGVAR &FILE (&PATH *TCAT '/' *TCAT &ATFILE *TCAT '.CSV') 

             /* Export to CSV */
              CPYTOIMPF  FROMFILE(&ATLIB/&ATFILE *ALL) +                
                           TOSTMF(&FILE) MBROPT(*REPLACE) +             
                           STMFCODPAG(*PCASCII) RCDDLM(*CRLF)           
              GOTO READ                                                 

Open in new window


Author Closing Comment

Comment Utility
Thanks Gary.I have used this approach before so I can write up a CL for our requirement using your instructions as a guide. Just wanted to check with the experts if there was an easier "push" way to get this done.
LVL 16

Expert Comment

by:theo kouwenhoven
Comment Utility
Hi bhagatali,

Yes it can be done:

- Open the iSeries navigator.
  - Open Databases
    - Open your-DB
      - Open Schemas
        - Open the required library
          - right-click the requierd objecs (e.g. tables or indexes)
          - Click 'Generate SQL'
          - Select the objects you need and generate the SQL.

Good Luck

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline


Author Comment

Comment Utility
Hi murphey2,

The steps that you mentioned would only give the structure of the tables that I need. It would give me the SQL statement that was used to create that table.I am looking for a way to dump the data from the tables.

LVL 27

Expert Comment

Comment Utility
And don't forget that the .CSV files are going to take up space -- potentially more than the actual 'schema' itself, though most likely not.

Migrations sometimes have space issues as a contributing factor. It's just something to keep in mind.


Author Comment

Comment Utility
Thanks for the pointer Tom. Appreciate it.

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Just about everyone has an old PC laying around.  Ask anyone in the IT industry, whether they are a professional or play in it as a hobby.  From outdated Desktops to cheap "throwaway" laptops, they are all around and not as hard to "fix up" as you m…
As the title indicates, I have done this before. It chills me everytime I update the OS on my phone, ( because one time I did this and I essentially had a bricked …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now