Playing in the Sandbox continued

1
Playing in the Sandbox - continued

• Pam Odden

2
Objectives

• Learn how to export data from production tables
  and load into sandbox tables

3
Extracting Data using File-AID for DB2

• Go to TSO option O.FD, making sure you are in
  SSID TSN
• Select 3.7 - Extract and Load Tables, then 1
  Extract from a table to a dataset
• Enter the database and name of the table you want
  to extract from, or enter to select from a list
• If you have done the same extraction before and
  saved the criteria, you can enter the pdf member
  name. The extract criteria consists of the
  select statement for the data to be extracted.
• Enter extract options. For help, see the screen
  prints at the end of the slide packet. If you do
  have null columns, and you plan to use the file
  to load into another table, you do need the null
  indicators.
• Pressing Enter takes you to the Extract Selection
  Template

4
Specify Extract Options

• File-AID for DB2 ------------- Extract Options
  -------------------------------
• COMMAND
  SSID TSN
• 
  
• Specify DB2 Table ( or pattern for list)
  Optional
• Creator SSTATDB1
  Database
• Table Name RUN_CTL
  Tablespace
• 
  
• Specify Extract Template Information
  
• Use Existing Criteria NO (Yes or
  No)
• Edit Criteria Dataset
  
• Member (Blank or
  Pattern for list)
• 
  
• Specify Extract Options
  
• Processing Mode 1
  (1 Online, 2 Batch)
• Formatted Print Report NO
  (Yes or No)
• Maximum Rows to Select 2000
  ( for all rows)
• Extract Null Indicators YES
  (Yes or No)

5
Extract Selection Template

• Enter a pdf member name for extract criteria if
  you wish to save them
• Select columns, and indicate sort order
• Enter WHERE clause
• When ready, press F6 to EXECUTE

6
Select Columns for Extract

• File-AID for DB2 -------- EXTRACT Selection
  Template --------- Row 1 to 4 of 4
• COMMAND
  SCROLL PAGE
• 
  SSID TSN
• Use SQL to view/edit the SQL source for
  SSTATDB1.RUN_CTL
• 
  
• Save Edit Criteria Information
  
• Save Template Criteria On Execute YES
  (Yes or No)
• Criteria Dataset Name 'PDFLIB.PJO.COPY'
  
• Member NEWDDL
  
• 
  
• Max Rows to Select 2000
  
• 
  
• SEL COLUMN NAME TYPE(LEN) ORDER
  (A/D) WHERE More
• --- ------------------ ------------------ --
  - -------10-------20----
• S__ SCHOOLNUM CHAR(3) 1
  A BETWEEN '300' AND '400'
• S__ RUN_DATE DATE
  
• S__ LOAD_IND CHAR(1)
  
• S__ STAT_RUN_DATE DATE

7
Specify Dataset Names and Format

• File-AID for DB2 ------------ Extract table
  SSTATDB1.RUN_CTL ------ SSID TSN
• COMMAND
  
• 
  
• Specify Extract Dataset Format
  
• EXTRACT Dataset format 1
  (1 Standard, 2 Delimited)
• Change Delimited Options NO
  (Yes or No)
• 
  
• Specify Output Datasets
  
• EXTRACT Dataset 'PJO..UNLOAD'
  
• Member (
  for member list)
• 
  
• Control Card Dataset 'PJO..CTRL'
  
• Member (
  for member list)
• 
  
• Create Layout File NO
  (Yes or No)
• Layout Dataset 'PJO..COBOL'
  
• Member (
  for member list)
• Language 1 (1
  COBOL, 2 PL/I)
• 
  

8
Loading Data using File-AID for DB2

• Go to TSO option O.FD, making sure you are in
  SSID TSN
• Select 3.7 - Extract and Load Tables, then 2
  Load from a dataset to a table
• Enter the database and name of the table you want
  to load to, or enter to select from a list
• Select load option, SQL insert or batch load
  utility

9
Enter Filenames for Data and Control Cards

• File-AID for DB2 ---------- Load Dataset
  Specifications ----------- SSID TSN
• COMMAND
  
• 
  
• Specify Input Datasets
  
• Load Dataset 'PJO.RUNCTL.UNLOAD'
  
• Member ( for
  member list)
• 
  
• Control Card Dataset 'PJO.RUNCTL.CTRL'
  
• Member ( for
  member list)
• 
  
• 
  
• An asterisk () in the above Dataset names will
  be replaced with
• 
  
• Table Name DEMO4
  
• 
  
• 
  

10
Specify Load Options

• File-AID for DB2 ----------- LOAD table -
  PJO.DEMO4 --------------- SSID TSN
• COMMAND
  
• 
  
• Specify Processing Option
  
• Processing Mode 1 (1
  Online, 2 Batch)
• 
  
• Specify Load Processing Option (select one)
  
• _ Add (Discard Duplicate Keys)
  
• _ Add (Replace Duplicate Keys)
  
• s Delete ALL Existing Data and Add New Data
  
• _ Add New Data to Empty Table
  
• 
  
• Specify Optional Discard Processing
  
• Save Discarded Rows yes (Yes
  or No)
• Maximum Rows to Discard 20 (
  for no maximum)
• Dataset for Discarded Rows
  'pdflib.pjo.cobol'
• Member disc (
  for member list)
• 
  
• 
  

11
Summary

• Use the sandbox to create small tables for
  testing and experimenting.
• You must create a tablespace first as a separate
  step before creating a table.
• Objects can be created using screens in File-AID
  for DB2, or using DDL (data definition language)
  in SPUFI.
• Tables can be populated using File-AID for DB2 to
  copy data from an existing table, or by using
  SPUFI to perform inserts of manually entered
  values, or inserts selected from another table.
• Tables can be populated from production by
  exporting data to a load file and loading into
  your test database.
• Have fun with your sandbox!