Creating dynamic SQL object sets

Use this procedure to create an object set that contains dynamic SQL. You can use dynamic SQL object sets to create a SQL statement to fetch the objects that you want the services to operate upon. When you enter the SQL statement , DASD MANAGER PLUS executes an SQL PREPARE for validation purposes before saving the SQL statement to the repository.

DASD MANAGER PLUS reads the temporary data set, verifying it by performing SQL PREPARE. Then, followed by a cursor OPEN, FETCH of one row, and CLOSE, DASD MANAGER PLUS saves the SQL in the repository with the final specification changes.

To create a dynamic SQL object set

1. On the DASD MANAGER PLUS Main Menu, select Object Sets and press Enter.The Object Set menu panel appears.

   DEMO ----------------------------- Object Set Menu ----------------------------
   Command ===>                                                                   
                                                                                 
   An Object Set is an ordered group of DB2 Objects. Type a specific Object Set   
   or type a wildcard pattern for a selection list.                               
                                                                                 
   Object Set . . .  BMCDEC30.FIOBJSET01                                          
                                                                                 
   Select an option. Then press Enter.                                              
    2 1. List                                                                     
      2. Create a new Object Set                                                  
      3. Edit an Object Set Specification                                         
      4. Edit an Object Set Properties                                            
      5. Rename an Object Set                                                     
      6. Copy an Object Set                                                       
      7. Delete an Object Set                                                     
      8. View Object Set Result Set                                               
      9. List Action Impact

2. In the Object Set field, enter an object set name in the form of owner.name (up to 8 characters for owner and up to 18 characters for name) and press Enter.Valid characters for the object set are as follows:

   • A–Z (upper case only)
   • 0–9
   • @ # $ & - / and space

   Together, the owner name and object set name must form a unique name. The default owner name is your TSO logon ID. The product does not support delimited names. For more information about the owner name, see Controlling-access-to-Object-Sets.

   DASD MANAGER PLUS validates that the owner.name does not already exist on the subsystem.

3. Select Create a new object set and press Enter.The Create Object Set panel appears:

   DEMO ---------------------------- Create Object Set ---------------------------
   Command ===>                                                                   
                                                                                 
   Type new Object Set data. Then press Enter.                                    
                                                                                 
   Object Set . . . . BMCDE3O.FIOBJSET01
                                                                                 
   Description  . . .

   Important

   As an alternative method, perform the following steps to open the Object Set List from the main object set panel:

   1. Select List and specify a wildcard pattern for the object set name.
   2. Type C in any Act field and press Enter.

4. On the Create Object Set panel, optionally enter an object set description and press Enter.

   Important

   The object set name that you specified on the Object Set Main menu is carried forward. You can change the object set name if necessary.

   The Specify Object Set panel is displayed.

   DEMO  ---------------------- Specify Object Set ----------     Row 1 to 1 of 1
   Command ===>                                                  Scroll ===> CSR  
                                                                                 
   Object Set: BMCDE30.FIOBJSET01                                                 
                                                                                 
   I =Insert L =Like D =Delete E =Edit C =Copy M =Move A =After B =Before         
   S =SQL text (for type SQ)   X =Expand Wildcard                                 
                                                                                 
   Include/Exclude options:  Plus sign  = Include,  Minus sign  = Exclude.        
   Object types: TS, IX, TB, SG, PL, PG, IS, OS, SQ                               
                                                                    More:       >
       Incl/ Obj  Name or                       By   Begin End        Include     
   Act Excl  Type Name Pattern                  Part Part  Part  IX RI LOB XML HST
   *** ******************************** TOP **************************************
       +                                          N      0     0  N  N  B   B   B
   *** ******************************* BOTTOM ************************************

5. Enter the following information into the Specify Object Set panel:

   In this field	Enter this information
   Act	S
   INCL/EXCL	Plus (+) sign to include, or minus (-) sign to exclude.
   Obj Type	SQ
   By Part	Type Y if you want to specify partition numbers, or N if you don't want to specify partition numbers.
   Begin Part	Enter partition numbers. If you entered N in the By Part field, these values are set to zero.
   End Part
   INCLUDE OPTIONS
   IX	Enter Y to include these objects, or N to exclude them.
   RI
   LOB	Enter Y to include these objects, or N to exclude them. Type B to include base objects and exclude auxiliary objects. Type O to exclude base objects and include auxiliary objects that match the naming pattern.
   XML
   HST
   ACH
   Description	(optional) Enter a description.

   DASD MANAGER PLUS opens ISPF Edit on a temporary data set for you to enter SQL statements. An ISPF Edit on a temporary dataset is used for creating and changing SQL statements.

6. Enter a valid SQL SELECT statement in ISPF Edit. The SQL text must return the following variables:

   • TS, IX, or SG (two-character literal)
   • DBNAME for TS or CREATOR (up to 128 characters) for IX or SG
   • TS name (up to eight characters), IX name (up to 128 characters), or STOGROUP name (up to 128 characters)
   • Optional for IX, if non translated UNICODE to EBCDIC names returned, CREATOR and NAME

   • Optional partition number 0 through 4096

     If not specified, the partition number is set to 0.

   Following are some SQL text examples for object types TS, IX, and SG:

   SQL text examples for object types TS, IX, and SG

   Object type	SQL statement
   TS	SELECT 'TS', DBNAME, NAME FROM SYSIBM.SYSTABLESPACE WHERE DBNAME LIKE ‘QZU%’
   	SELECT 'TS', DBNAME, TSNAME, PARTITION FROM SYSIBM.SYSTABLEPART WHERE DBNAME='QZUDPT22' AND TSNAME='QZUS0122' AND PARTITION IN (4090, 4092, 4094, 4096)
   IX	SELECT 'IX', CREATOR, NAME FROM SYSIBM.SYSINDEXES WHERE TBCREATOR='QZU' SELECT 'IX', CREATOR, NAME, CREATOR, NAME FROM SYSIBM.SYSINDEXES WHERE TBCREATOR='QZU'
   	SELECT 'IX', IXCREATOR, IXNAME, PARTITION FROM SYSIBM.SYSINDEXPART WHERE IXCREATOR='QZU' AND PARTITION > 100 AND PARTITION <= 200 SELECT 'IX', IXCREATOR, IXNAME, IXCREATOR, IXNAME, PARTITION FROM SYSIBM.SYSINDEXPART WHERE IXCREATOR='QZU' AND PARTITION > 100 AND PARTITION <= 200
   SG	SELECT 'SG',’ANY', STORNAME FROM SYSIBM.SYSTABLEPART WHERE DBNAME='QZUD11' AND TSNAME='QZUS0111'
   	SELECT 'SG','ANY', STORNAME, PARTITION FROM SYSIBM.SYSTABLEPART WHERE DBNAME='QZUD11' AND TSNAME='QZUS0111' AND PARTITION BETWEEN 1 AND 4

   
   

7. Press END to the ISPF edit and return to the Object Set Specification panel.
8. Press END again to create the object set.