RC/Extract : Exporting data from a DB2 database to another platform such as a spreadsheet.

Products

• RC/Extract for DB2 for z/OS

Issue/Introduction

Exporting data from a DB2 database to another platform such as a spreadsheet can be useful for analysis purposes. Having extracted the required data from DB2 using RC/Extract, with a careful record selection process, into an extract object, it can be transferred to a spreadsheet or other database. It also means that additional sql access to the source database will not be required and so the data can be obtained without causing the system more sql traffic.

Environment

Release: R20
Component: RCX

Resolution

The procedure is as follows:

1. Create the Source Definition as is normally done to select the data that is required.
   
   
2. Perform the extract.
   
   
3. Use the line command "O" on the extract object.
   
   
4. This command is called "Export Object" command
   
   
5. Select option "Export output format . . C" so that the Export file is be produced in CSV (comma separated variables) format. This is suitable for import into other tools such as Microsoft Excel. Fill out the Export Data Set Allocation options.
   
   
6. Run the export in batch or online. If run online an ispf edit session is started so that the data can be edited further if required. You might want to remove some data that you don't want exported.
   
   
7. The dataset produced can now be downloaded to another platform and the data loaded into any tool that recognises CSV format.
   
   
8. Rename the downloaded dataset as a .txt file first, edit out the headers that RC/Extract has placed into it. If there is data from more than one table in the extract then split out the data between special data lines {{START and the {{END to different files with a .CSV extension so that they can be opened separately by your utility that recognises .csv files. Note that the data is exported with comma delimited headings so these can be used as column names in your spreadsheet or database.
   
   
9. The split out data in .csv files that can then be imported into a spreadsheet or other Database engines. Normally in a Windows environment .csv files are recognised by Microsoft Excel as a "Comma Separated Values File".

An example of an exported extract object is below. It contains data from two tables, TBROLE and TBEMP. Note the {{START and the {{END lines that delimit the different table data.

For import into a spreadsheet or database the data that is needed is between these special lines.

  CA RC/Extract Export Object AUTHID 03:06:012 2012.237  
  {{START T AUTHID.TBROLE 0001 11  
  ROLE_ID,ROLE_DESC,ROLE_SALARY,ROLE_SALARY_CODE  
  AUDITO,AUDIT OFFICER,.,A  
  CRANED,CRANE DRIVER,.,A  
  DISPAT,DISPATCHER,.,A  
  DISPMN,DISPATCH MANAGER,.,A  
  DRIVER,GENERAL DRIVER,.,A  
  MANAGE,MANAGER,.,A  
  MATREC,MATERIALS RECEIVER,.,A  
  PLANTO,PLANT OPERATOR,.,A  
  RECMAN,RECEIVALS MANAGER,.,A  
  STRMAN,STOREMAN,.,A  
  WAREHS,WAREHOUSE MANAGER,.,A  
  {{END T AUTHID.TBROLE  
  {{START T AUTHID.TBEMP_TASK_ROLE 0007 1  
  EMPNO,TASK_ID,ROLE_ID  
  134800,111111,DRIVER  
  {{END T AUTHID.TBEMP_TASK_ROLE 

Additional Information

For more information about this facility see : View and Export Data for Extract Objects