Solved

How can i get a dump of entire DB2 schema?

Posted on 2010-09-09
6
1,802 Views
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.

Regards
Ali.
0
Comment
Question by:bhagatali
[X]
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
6 Comments
 
LVL 35

Accepted Solution

by:
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 &ROOTPATH *CHAR 106  VALUE('/CSVFILES') 
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) +            
                          OUTFILE(QTEMP/QAFDBASI)                    

             /* Override to newly-created outfile */
             OVRDBF     FILE(QAFDBASI) TOFILE(QTEMP/QAFDBASI)        
                                                                     
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                                                 
 EOF: ENDPGM

Open in new window

0
 

Author Closing Comment

by:bhagatali
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.
0
 
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

0
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

by:bhagatali
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.

Regards
Ali.
0
 
LVL 27

Expert Comment

by:tliotta
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.

Tom
0
 

Author Comment

by:bhagatali
ID: 33651099
Thanks for the pointer Tom. Appreciate it.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Hello I read in a discussion about a person who configured a very simple mirror RAID with two hard drives; the system and data were on the same partition. He asked how to repair the system as it was not booting up anymore. In his case running …
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.

732 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