Playing in the Sandbox continued

1
Playing in the Sandbox - continued

• Pam Odden

2
Objectives

• Learn how to create copybooks for our personal
  tables
• Learn how to grant authority on our objects to
  other developers

3
Creating a Copybook

• Go to TSO option 9.D.2, making sure you are in
  SSID TSN
• DCLGEN SSID TSN
  
• 
  
• 
  
• Enter table name for which declarations are
  required
• 1 SOURCE TABLE NAME demo1
  (Unqualified)
• 2 TABLE OWNER ..... PJO
  (Optional)
• 3 AT LOCATION .....
  (Optional)
• 
  
• Enter destination data set (Can be
  sequential or partitioned)
• 4 DATA SET NAME ... 'PDFLIB.PJO.COPY(DCLDEM
  O1)'
• 5 DATA SET PASSWORD (If password
  protected)
• 
  
• Enter options as desired
  
• 6 ACTION .......... ADD (ADD new or
  REPLACE old declaration)
• 7 COLUMN LABEL .... NO (Enter YES
  for column label)
• 8 STRUCTURE NAME ..
  (Optional)
• 9 FIELD NAME PREFIX
  (Optional)

4
Resulting Copybook

• 
  
• DCLGEN TABLE(PJO.DEMO1)
  
• LIBRARY(PDFLIB.PJO.COPY(DCLDEMO1))
  
• ACTION(REPLACE)
  
• LANGUAGE(COBOL)
  
• APOST
  
• DBCSDELIM(NO)
  
• ... IS THE DCLGEN COMMAND THAT MADE THE
  FOLLOWING STATEMENTS
• 
  
• EXEC SQL DECLARE PJO.DEMO1 TABLE
  
• ( PAMS_COL_1 CHAR(20)
  NOT NULL,
• PAMS_COL_2 DATE,
  
• PAMS_COL_3 INTEGER
  NOT NULL,
• PAMS_COL_4 DECIMAL(9,
  2) NOT NULL
• ) END-EXEC.
  
• 
  
• COBOL DECLARATION FOR TABLE PJO.DEMO1
  
• 
  
• 01 DCLDEMO1.
  

5
Creating a Copybook with a Field Name Prefix

• DCLGEN
  SSID TSN
• 
  
• DSNE294I SYSTEM RETCODE000 USER OR DSN
  RETCODE0
• Enter table name for which declarations are
  required
• 1 SOURCE TABLE NAME demo1
  (Unqualified)
• 2 TABLE OWNER ..... PJO
  (Optional)
• 3 AT LOCATION .....
  (Optional)
• 
  
• Enter destination data set (Can be
  sequential or partitioned)
• 4 DATA SET NAME ... 'PDFLIB.PJO.COPY(DCLDE
  MO1)'
• 5 DATA SET PASSWORD (If
  password protected)
• 
  
• Enter options as desired
  
• 6 ACTION .......... REPLACE (ADD new or
  REPLACE old declaration)
• 7 COLUMN LABEL .... NO (Enter YES
  for column label)
• 8 STRUCTURE NAME ..
  (Optional)
• 9 FIELD NAME PREFIX D1-
  (Optional)
• 10 DELIMIT DBCS .... NO (Enter YES
  to delimit DBCS identifiers)

6
Resulting Copybook

• File-AID for DB2 Table Create
  Row 1 to 5 of 5
• 
  
• DCLGEN TABLE(PJO.DEMO1)
  
• LIBRARY(PDFLIB.PJO.COPY(DCLDEMO1))
  
• ACTION(REPLACE)
  
• LANGUAGE(COBOL)
  
• NAMES(D1-)
  
• APOST
  
• DBCSDELIM(NO)
  
• COLSUFFIX(YES)
  
• ... IS THE DCLGEN COMMAND THAT MADE THE
  FOLLOWING STATEMENTS
• 
  
• EXEC SQL DECLARE PJO.DEMO1 TABLE
  
• ( PAMS_COL_1 CHAR(20)
  NOT NULL,
• PAMS_COL_2 DATE,
  
• PAMS_COL_3 INTEGER
  NOT NULL,
• PAMS_COL_4 DECIMAL(9,
  2) NOT NULL
• ) END-EXEC.
  
• 
  

7
Granting Authority on the New Table

• Go to File-AID, TSO option O.FD, option 3.6
• Display, Grant, Revoke or Modify DB2 Privileges
• File-AID for DB2 ------------- Grant/Revoke
  Privileges ----------------
• OPTION
  SSID
• 
  
• Blank - Display Privileges
  
• G - Grant Privileges
  
• R - Revoke Privileges
  
• PM - Modify Privileges
  
• 
  
• Enter
  
• 
  
• Creator PJO (
  for selection list)
• Table Name demo1 (
  for selection list)
• 
  
• -- OR --
  
• 
  
• Database
  ( for selection list)

8
Granting Authority on the New Table cont.

• File-AID for DB2 -------- PRIVILEGES for table
  PJO.DEMO1 ----- Row 1 to 1 of 1
• COMMAND
  SCROLL PAGE
• 
  SSID TSN
• 
  
• Auth ID ALL SELECT UPDATE INSERT
  DELETE ALTER INDEX RI GRANTOR
• -------- --- ------ ------ ------
  ------ ----- ----- -- -------
• 00000 PJO G G G G
  G G G PJO
• Enter a G (Grant)on the prior screen
• Add user Ids and authorities you want to grant.
• Enter GRANT on the command line
• File-AID for DB2 -------- GRANT for table
  PJO.DEMO1 -------- Row 1 to 14 of 16
• COMMAND grant
  SCROLL PAGE
• 
  SSID TSN
• Use SQL to view/save the SQL source
  
• 
  
• Auth ID ALL SELECT UPDATE INSERT
  DELETE ALTER INDEX RI

9
Granting Authority on the New Table cont.

• File-AID for DB2 -------- PRIVILEGES for table
  PJO.DEMO1 ----- Row 1 to 2 of 2
• COMMAND
  SCROLL PAGE
• 
  SSID TSN
• 
  
• Auth ID ALL SELECT UPDATE INSERT
  DELETE ALTER INDEX RI GRANTOR
• -------- --- ------ ------ ------
  ------ ----- ----- -- -------
• 00000 GUH Y Y Y Y
  PJO
• 00000 PJO G G G G
  G G G PJO
• You can see the authority has been added
• A Y indicates the user has the authority
• A G indicates the user has the authority AND is
  able to grant it to others.

10
Granting Authority using SPUFI

• GRANT SELECT, INSERT ON TABLE PJO.DEMO1 TO
  STC058
• ---------------------------------------------
  --------
• DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL,
  SQLCODE IS 0
• select from sysibm.systabauth where ttname
  'DEMO1'
• ---------------------------------------------
  -----------
• GRANTOR GRANTEE INSERTAUTH SELECTAUTH
  UPDATEAUTH
• ---------------------------------------------
  -----------
• PJO GUH Y Y Y
• PJO PJO G G G
• PJO STC058 Y Y