Unload data from multiple tables

shaankumar · #1 (**permalink**) 03-02-10, 16:37

Hi,

My requirement is to unload the data from all the tables in a database to different flat files. The simplest way is to write separate unload utility jobs, specify the specific table that you want to unload and run the job to create the flat file. But the problem with this - You will have to manually submit all the jobs which is time consuming and also, if the database schema changes then the utility job will also have to be changed.

To avoid that can we? -
1. As a first step, get the list of all the tables in the database
2. Pass the first table name as a parameter to the unload utility control card
3. The job would download the data from the first table.
4. And then pass the second table name as parameter to the unload utility control card
5. The job would download the data from the second table.
6. This process would repeat for all the tables.

Please let me know if the above approach is valid. If yes, how to accomplish that else please suggest an alternative approach.

sathyaram_s · #2 (**permalink**) 03-02-10, 19:46

Yes ... sounds perfect ..
what platform and version are you on ?

shaankumar · #3 (**permalink**) 03-02-10, 20:12

This is DB2 on Z/OS. The DB2 version is 8.

dav1mo · #4 (**permalink**) 03-03-10, 12:19

One of the things you could do and I have done several times is to use QMF. With QMF, you can write a query against the sysibm tables that will output your JCL and file names. Then use the form to put the rows of data into the correct format.

As an example here is a query to run in QMF (it will ask you to supply your collid, table creator id and a table name). It just builds a rebind statement for all packages in a collection that use a particular table:

Code:

SELECT DISTINCT 'REBIND PACKAGE (' ||                                        
       RTRIM(A.COLLID) || '.' || RTRIM(A.NAME) || '.(' || A.VERSION || ')) -'
,'EXPLAIN(YES) OWNER(A9139)'                                                 
                                                                             
    FROM SYSIBM.SYSPACKAGE A                                                 
        ,SYSIBM.SYSPACKDEP B                                                 
WHERE A.LOCATION = ''                                                        
 AND  A.COLLID  = &UR_COLLID                                                   
 AND  A.VERSION =  (SELECT MAX(B.VERSION)                                    
                      FROM SYSIBM.SYSPACKAGE B                               
                   WHERE A.LOCATION = B.LOCATION                             
                     AND A.COLLID = B.COLLID                                 
                     AND A.NAME = B.NAME)                                    
  AND A.LOCATION = B.DLOCATION                                               
  AND A.COLLID   = B.DCOLLID                                                 
  AND A.NAME     = B.DNAME                                                   
  AND B.BNAME    = &UR_TBNAME                                                   
  AND B.BQUALIFIER = &UR_CREATOR_ID                                                
  AND B.BTYPE = 'T'

Run the above query then import the below form from a dataset, with the command import form from 'your dsn' and then hit PF12
The the form:

Code:

H QMF 13 F 04 E V W E R 01 03 10/03/03 11:36              
T 1110 002 011 1112 007 1113 040 1114 007 1115 006 1116 005 1117 005 1118 003 1119 008 1120 008 1122 006 1121 050
R CHAR    COL1                                     BREAK1X 2      72    C     1   DEFAULT  DEFAULT  NO     
R CHAR    COL2                                             2      72    C     2   DEFAULT  DEFAULT  NO     
V 1201 001 0
V 1202 001 2
T 1210 001 003 1212 004 1213 006 1214 055
R 1    CENTER 
V 1301 001 2
V 1302 001 0
T 1310 001 003 1312 004 1313 006 1314 055
R 1    CENTER 
V 1401 002 NO
V 1402 001 1
V 1403 001 0
T 1410 001 003 1412 004 1413 006 1414 055
R 1    RIGHT  
V 1501 001 1
V 1502 003 YES
V 1503 003 YES
V 1504 003 YES
V 1505 003 YES
V 1506 003 YES
V 1507 003 YES
V 1508 003 YES
V 1509 003 YES
V 1510 003 YES
V 1511 004 NONE
V 1512 002 NO
V 1513 007 DEFAULT
V 1514 002 NO
V 1515 004 NONE
V 2790 001 1
V 2791 003 YES
V 2805 003 YES
T 2810 001 003 2812 004 2813 006 2814 055
R 1    LEFT   
V 2901 002 NO
V 2902 001 1
V 2904 001 0
V 2906 002 NO
V 2907 002 NO
T 2910 001 003 2912 004 2913 006 2914 055
R 1    LEFT   
V 3080 001 1
V 3101 002 NO
V 3102 002 NO
V 3103 001 0
V 3104 001 0
T 3110 001 003 3112 004 3113 006 3114 055
R 1    LEFT   &1
V 3201 002 NO
V 3202 004 NONE
V 3203 001 0
V 3204 001 0
T 3210 001 003 3212 004 3213 006 3214 055
R 1    LEFT   
V 3080 001 2
V 3101 002 NO
V 3102 002 NO
V 3103 001 0
V 3104 001 0
T 3110 001 003 3112 004 3113 006 3114 055
R 1    LEFT   
V 3201 002 NO
V 3202 001 1
V 3203 001 0
V 3204 001 1
T 3210 001 003 3212 004 3213 006 3214 055
R 1    RIGHT  
E

Here is how the data comes out looking, and in this case you just copy it right into your jcl for rebinding a package:

Code:

REBIND PACKAGE (DB2P85.AMD005.(2008-05-09-18.03.27.046136)) -
  EXPLAIN(YES) OWNER(A9139)                                  
REBIND PACKAGE (DB2P85.ASM012.(2007-03-13-09.21.51.172742)) -
  EXPLAIN(YES) OWNER(A9139)                                  
REBIND PACKAGE (DB2P85.ASM025.(2010-01-06-16.20.12.249540)) -
  EXPLAIN(YES) OWNER(A9139)

Dave

Internet Services

Web Development

Digital Marketing

Consumer Tech