[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now


How can i get a dump of entire DB2 schema?

Posted on 2010-09-09
Medium Priority
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 2000 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!


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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Introduction Often we come across situations wherein our batch files would be needing to reboot Windows for a variety of reasons. A few of them would be like: (1) Setup files have been updated whose changes can take effect only after a reboot …
Many people tend to confuse the function of a virus with the one of adware, this misunderstanding of the basic of what each software is and how it operates causes users and organizations to take the wrong security measures that would protect them ag…
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.

650 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