Playing in the Sandbox

1
Playing in the Sandbox

• Pam Odden

2
Objectives

• Get some idea of how sandboxes can be useful in
  development
  
• Learn how to create tablespaces, tables, and
  indexes in the sandbox using File-AID for DB2
  
• Learn how to create objects using SPUFI
• A few comments on the new procedures

3
Whats a Sandbox?

• What is a sandbox? An area of space in the DB2
  region allocated for the use of one developer,
  like the area in PDFLIB.your-user-id, or like the
  C drive on your PC. It is your very own
  database.
• What will I use it for? During development it is
  often convenient to be able to try out more than
  one design, but formally requesting new tables
  from the DBA discourages experimentation. In the
  sandbox you can create a few versions of tables,
  indexes and views and see which works best for
  your application.
• During testing it is often convenient to use
  just a subset of the production data, and to
  create specific test cases. Sandbox tables can
  be populated quickly with data from existing
  tables, and modified as needed.
• Do I have to? No. The sandboxes are for the
  convenience of developers. Dont feel pressured
  if you are a DB2 beginner and this is
  overwhelming. Some super-users and
  super-user-wannabees will enjoy this new
  opportunity, others may use it little or not at
  all.
• An extra benefit is the understanding and
  experience gained by manipulating structures and
  data in DB2.
  

4
My Sandbox

• My sandbox is a database named DEVPJODB
• select from sysibm.sysdatabase where name
  'DEVPJODB'
  
• NAME CREATOR STGROUP BPOOL DBID
  IBMREQD CREATEDBY ROSHARE
  
• ---------------------------------------------
  ------------------------
  
• DEVPJODB SYSADM SYSDEFLT BP0 277 N
  SYSADM
  
• TIMESTAMP TYPE GROUP_MEMBER
  CREATEDTS
  
• ---------------------------------------------
  -----------------------
  
• 0001-01-01-00.00.00.000000
  2002-06-03-12.43.30.935619
  
• ALTEREDTS ENCODING_SCHEME
  SBCS_CCSID DBCS_CCSID MIXED_CCSID
  
• ---------------------------------------------
  ------------------------------
  
• 2002-06-03-12.43.30.935619 E
  500 0 0
  

5
Creating a New Tablespace with File-AID

• File-AID for DB2 Tablespace Create
  SSID TSN
  
• COMMAND create
  
  
• 
  
  
• Database Name DEVPJODB
  
  
• Tablespace Name DEMO1TS
  
  
• 
  
  
• STOGROUP SYSDEFLT FREEPAGE 0
  BUFFERPOOL BP0
  
• PRIQTY(K) 720 PCTFREE 10
  LOCKSIZE ANY
  
• SECQTY(K) 72 NUMPARTS
  CLOSE YES
  
• ERASE NO GBPCACHE ALL
  DSETPASS
  
• LOCKMAX
  SYSTEM SEGSIZE 4
  
• COMPRESS NO
  LOCKPART
  
• LARGE
  MAXROWS
  
• -- OR --
  
  
• 
  
  
• VCAT
  
  
• In TSO, go to option O.FD for File-AID for DB2
• Select option 3.2 Create, Drop, Alter DB2
  Objects. Select Tablespace. (See screen prints)
  

6
Creating a new table with File-AID

• File-AID for DB2 Table Create
  Row 1 to 5 of 8
  
• COMMAND
  SCROLL PAGE
  
• 
  SSID TSN
  
• Creator PJO Database
  DEVPJODB Editproc
  
• Table Name DEMO1 Tablespace
  DEMO1TS Validproc
  
• Restrict Drop NO (Yes or No) CCSID
  (EBCDIC or ASCII)
  
• Data Capture NO (Yes or No)
  
  
• Temporary NO (Yes or No)
  
  
• 
  
  
• MODEL TABLE (Optional, or pattern for list)
  
  
• Creator PJO
  
  
• Table Name Model Check
  Constraints NO (Yes or No)
  
• 
  
  
• 
  Length Unit Allow
  
• Column Name Type
  ,scale (for Nulls Fieldproc
  
• 
  (LL,SS) LOB) (Y/N/D)
  
• ------------------ ------------------
  ------- ----- ------- ----------
  
• ''''''
  
  
• ''''''
  
  

7
Adding Columns Manually

• File-AID for DB2 Table Create
  Row 1 to 5 of 5
  
• COMMAND DEFAULT
  SCROLL PAGE
  
• 
  SSID TSN
  
• Creator PJO Database
  DEVPJODB Editproc
  
• Table Name DEMO1 Tablespace
  DEMO1TS Validproc
  
• Restrict Drop NO (Yes or No) CCSID
  (EBCDIC or ASCII)
  
• Data Capture NO (Yes or No)
  
  
• Temporary NO (Yes or No)
  
  
• 
  
  
• MODEL TABLE (Optional, or pattern for list)
  
  
• Creator PJO
  
  
• Table Name Model Check
  Constraints NO (Yes or No)
  
• 
  
  
• 
  Length Unit Allow
  
• Column Name Type
  ,scale (for Nulls Fieldproc
  
• 
  (LL,SS) LOB) (Y/N/D)
  
• ------------------ ------------------
  ------- ----- ------- ----------
  
• 000001 PAMS_COL_1 CHAR 20
  D
  
• 000002 PAMS_COL_2 DATE
  Y
  

8
Setting Defaults

• Esssssssssssssssssssssssssss Default Values
  ssssssssssssssssssssssssssssN
  
• e
  Row 1 to 2 of 2 e
  
• e COMMAND
  SCROLL PAGE e
  
• e
  e
  
• e Specify Column Default Values for PJO.DEMO1
  e
  
• e
  e
  
• e COLUMN NAME TYPE(LEN) VALUE
  More e
  
• e ------------------ ----------------
  -------10-------20-------30-- e
  
• e PAMS_COL_1 CHAR(20) UNKNOWN
  e
  
• e PAMS_COL_4 DECIMAL(9,2) 1.0
  e
  
• e Bottom of data
  e
  
• e
  e
  
• e
  e
  
• e
  e
  
• e
  e
  
• PF3 back to previous screen, then enter CREATE in
  command line to create the table.
  
• Be sure to define the defaults BEFORE doing the
  CREATE

9
My New Table and Tablespace

• select from sysibm.systablespace where dbname
  'DEVPJODB'
  
• ---------------------------------------------
  -----------------------
  
• NAME CREATOR DBNAME DBID OBID
  PSID BPOOL PARTITIONS
  
• ---------------------------------------------
  -----------------------
  
• DEMO1TS PJO DEVPJODB 277 1
  2 BP0 0
  
• select from sysibm.systablepart where tsname
  'DEMO1TS'
  
• ---------------------------------------------
  ---------------------------
  
• PARTITION TSNAME DBNAME IXNAME
  IXCREATOR PQTY SQTY
  
• ---------------------------------------------
  ---------------------------
  
• 0 DEMO1TS DEVPJODB
  180 18
  
• select from sysibm.systables where dbname
  'DEVPJODB'
  
• ---------------------------------------------
  ---------------------------
  
• NAME CREATOR TYPE DBNAME
  TSNAME DBID OBID COLCOUNT
  
• ---------------------------------------------
  ---------------------------
  
• DEMO1 PJO T DEVPJODB
  DEMO1TS 277 4 4
  
• There are many more columns than shown here I
  didnt copy them all.
  

10
Data in my New Table

• INSERT INTO PJO.DEMO1
  
  
• (PAMS_COL_2
  
  
• , PAMS_COL_3)
  
  
• VALUES
  
  
• (CURRENT DATE
  
  
• , 456)
  
  
• ---------------------------------------------
  ---------
  
• DSNE615I NUMBER OF ROWS AFFECTED IS 1
  
  
• DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL,
  SQLCODE IS 0
  
• ---------------------------------------------
  ---------
  
• SELECT FROM PJO.DEMO1
  
  
• ---------------------------------------------
  ---------
  
• PAMS_COL_1 PAMS_COL_2 PAMS_COL_3
  PAMS_COL_4
  
• ---------------------------------------------
  ---------
  
• UNKNOWN 06/20/2002 456
  1.00
  
• Note the defaults showed up in the columns I did
  not enter a value for.

11
Creating a Table from a Model

• File-AID for DB2 Table Create
  Row 1 to 5 of 22
  
• COMMAND
  SCROLL PAGE
  
• 
  SSID TSN
  
• Creator PJO Database
  DEVPJODB Editproc
  
• Table Name DEMO2 Tablespace
  DEMO2TS Validproc
  
• Restrict Drop NO (Yes or No) CCSID
  (EBCDIC or ASCII)
  
• Data Capture NO (Yes or No)
  
  
• Temporary NO (Yes or No)
  
  
• 
  
  
• MODEL TABLE (Optional, or pattern for list)
  
  
• Creator SSASIDB1
  
  
• Table Name ASTU_STUDENT Model Check
  Constraints NO (Yes or No)
  
• 
  
  
• 
  Length Unit Allow
  
• Column Name Type
  ,scale (for Nulls Fieldproc
  
• 
  (LL,SS) LOB) (Y/N/D)
  
• ------------------ ------------------
  ------- ----- ------- ----------
  
• 000001 CCSDSTATUS CHAR 1
  D
  
• 000002 SCHOOLNUM CHAR 3
  D
  

12
Creating an Index

• File-AID for DB2 Index Create
  Row 1 to 9 of 22
  
• COMMAND
  SCROLL PAGE
  
• Table created, Index required on primary key.
  
  
• Creator PJO Creator
  PJO
  
• Index Name DEMO2X Table Name
  DEMO2
  
• 
  
  
• Index Type 2 (1/2) Partitioned
  N (Y/N)
  
• Cluster Y (Y/N) Unique
  1 (1. Yes, 2. No,
  
• Piecesize (K)
  3. Unique when not null)
  
• 
  
  
• SELECT A/D COLUMN NAME DATA
  TYPE(LENGTH)
  
• (1,2,..,n)
  
  
• 
  
  
• __ _ CCSDSTATUS CHAR(1)
  
  
• 1_ _ SCHOOLNUM CHAR(3)
  
  
• __ _ MY_COL
  TIMESTMP(26)
  
• __ _ STULINK INTEGER(4)
  
  
• __ _ ENTERDATE DATE(10)
  
  
• __ _ LEAVEDATE DATE(10)
  
  

13
Populating a Table using File-AIDs Copy Utility

• In TSO, go to option O.FD for File-AID for DB2
• Select option 3.3 Copy Rows Between Tables
• This provides a facility to
• - specify which rows and columns are to be copied
  
  
• - limit the number of rows to be copied
  
  
• - delete existing rows prior to copy
  
  
• - map columns from the source table/view to the
  target table/view
  
• - specify the total number of rows to be
  discarded due to errors
  
• - automatically create target table (Note it did
  create my table on the fly, but only when I put
  it in an existing tablespace.)
  

14
Copy Utility with a Selection Template

• File-AID for DB2 -------------- Table Copy
  Selection -------------------------
  
• COMMAND
  SSID TSN
  
• 
  
  
• Specify Table Copy Option
  
  
• Specify Option T Blank -
  Full Table Copy
  
• T - Use a
  Selection Template
  
• S - Use
  SQL for Selection (SPUFI)
  
• 
  
  
• From Table
  To Table
  
• ---------------
  ---------
  
• Creator SSASIDB1 Creator
  pjo
  
• Table Name astu_student Table Name
  demo2
  
• 
  
  
• 
  
  
• Maximum Rows To Select (
  for all rows)
  
• Column Selection NO
  (Yes or No)
  
• Delete Existing Rows NO
  (Yes or No)
  
• Column Name Matching YES
  (Yes or No)
  
• Discard Limit 20

15
Specifying Rows for Selection

• File-AID for DB2 -------- Copy Row Selection
  -------------- Row 1 to 10 of 164
  
• COMMAND
  SCROLL PAGE
  
• 
  SSID TSN
  
• Use SQL to view/save the SQL source
  
  
• 
  
  
• From Table
  
  
• ------------------
  
  
• Creator SSASIDB1
  
  
• Table Name ASTU_STUDENT
  
  
• 
  
  
• COLUMN NAME TYPE(LEN) WHERE
  
  
• ------------------ ------------------
  -------10-------20-------30-------4
  
• CCSDSTATUS CHAR(1)
  
  
• SCHOOLNUM CHAR(3) '351'
  
  
• STULINK INTEGER
  
  
• ENTERDATE DATE
  
  
• LEAVEDATE DATE
  
  
• ENTERCODE CHAR(3)
  
  
• LEAVECODE CHAR(3)
  
  

16
Creating a New Table using SPUFI

• CREATE TABLESPACE DEMO3TS
  
  
• IN DEVPJODB
  
  
• USING STOGROUP SYSDEFLT
  
  
• PRIQTY 8
  
  
• SECQTY 144
  
  
• ERASE NO
  
  
• SEGSIZE 4
  
  
• BUFFERPOOL BP0
  
  
• CLOSE YES
  
  
• LOCKSIZE ANY
  
  
• PCTFREE 10
  
  
• FREEPAGE 0
  
  
• ---------------------------------------------
  ---------
  
• DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL,
  SQLCODE IS 0
  
• CREATE TABLE PJO.DEMO3
  
  
• (SCHOOLNUM CHAR(03) NOT
  NULL WITH DEFAULT,
  
• MY_NAME_COL CHAR(30) NOT
  NULL WITH DEFAULT,
  
• RUN_DATE DATE NOT
  NULL WITH DEFAULT,
  

17
Populating a Table using SPUFI

• INSERT INTO PJO.DEMO3
• (SCHOOLNUM
• , MY_NAME_COL
• , RUN_DATE
• , LOAD_IND
• , STAT_RUN_DATE)
• SELECT
• R.SCHOOLNUM
• , SUBSTR(A.SCHOOLNAME,1,30)
• , R.RUN_DATE
• , R.LOAD_IND
• , R.STAT_RUN_DATE
• FROM SSTATDB1.RUN_CTL R
• INNER JOIN SSASIDB1.ASCH_SCHOOL A
• ON R.SCHOOLNUM A.SCHOOLNUM
• WHERE R.SCHOOLNUM '900'
• DSNE615I NUMBER OF ROWS AFFECTED IS 38
  
  
• DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL,
  SQLCODE IS 0
  

18
Using the New Procedures

• Database reports
• Run in PRF in the system you want the report for
• Easiest way to find them is to enter L (for list)
  next to FORM under Opt, then change CREATOR to
  SYSAD2, then find the report in the list
  
• You can also enter R (for Run) next to FORM under
  Opt, and enter SYSAD2.DEV_TABLE_COLS or whatever
  report you want under NAME
  
• Compile Requests
• 2nd line May I delete the ALT or TEST load
  modules? I will only delete these if you answer
  YES or check the box. You can still run your
  test, as your library concatenation will find the
  load module in INTERIM or PROD, so please
  indicate YES unless you need the module kept.
• Close CICS Trans I will not do this unless
  requested. Be aware that users may get 805 when
  I have compiled but not yet done the new copy.
  If I disable the transaction, anyone trying to
  use it will get Resource Unavailable until it is
  back up.

19
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.
  
• If your table is similar to one that already
  exists, use the existing table as a model, then
  delete the columns you dont want and add any new
  columns
• If you use the same name as an existing table,
  be careful to always use the correct creator when
  referring to your own table.
  
• Objects no longer needed can be dropped (removed)
  using the DROP TABLE and/or DROP TABLESPACE
  commands, either in File-AID or SPUFI. (See
  screen prints.)