Solved

How can i get a dump of entire DB2 schema?

Posted on 2010-09-09
6
1,758 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
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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Is Cross Site Request Forgery (CSRF) & XSS applicable to RPG coding? 10 186
How to Create Bootable Mac OS X USB Drive 8 141
DB2 - LOG FILES. 4 54
z/OS IO Benchmark 3 51
Introduction How to create multiboot configuration with XP\Vista and Windows 7 on it? And most important question - how to do this correctly so not to have any kind of nightmares we get when system gets screwed? First of all one should realize t…
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.

808 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