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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 35

Accepted Solution

Gary Patterson earned 500 total points
ID: 33643190
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

ID: 33645188
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
ID: 33645274
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 33645375
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

ID: 33649922
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

ID: 33651099
Thanks for the pointer Tom. Appreciate it.

Featured Post

Don't Cry: How Liquid Web is Ensuring Security

WannaCry is just the start. Read how Liquid Web is protecting itself and its customers against new threats.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Windows 7 does not have the best desktop search built in. This is something Windows 7 users have struggled with. You type something in, and your search results don’t always match what you are looking for, or it doesn’t actually work at all. There ar…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

726 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