Using advanced SQL with the Wildcard field to add table spaces at job build time

Edit online

If you have an application that frequently adds or removes table spaces, you can use the Wildcard field in conjunction with advanced SQL to allow Db2 Automation Expert to resolve the object list at job build time. You can use this feature to avoid manually modifying the object profile whenever your application adds an object.

About this task

Advanced SQL works with the selection criteria that you specify in the Database Like, Tablespace Like, and Creator Like fields on the Enter Tablespaces Like to Display window. The results of selection criteria are intersected with the results of the advanced SQL statement to select the table spaces. When you set Wildcard to Y, the advanced SQL and the selection criteria are evaluated at build time to select the table spaces.

In addition, when the Wildcard field is set to Y, the advanced SQL is saved with the object profile and used at build time to generate the object list.

Procedure

  1. On the Enter Tablespace Like to Display window, enter a database, table space, or creator name or mask in the appropriate fields and enter Y in the Wildcard field.
    Note: The Creator Like field allows up to 128 bytes. To scroll this field, place the cursor in the field and use the PF11 key to scroll right and the PF10 key to scroll left.
  2. Enter Y in the Advanced SQL field and Y in the Update SQL field, as showing in the following figure:
    Figure 1. Adding table spaces at job build time 
                           Enter Tablespaces Like to Display                        
                                                                                
  Database Like. . *          Wildcard   Y (Yes/No)                             
  Tablespace Like. *          Exclude    I (E - Exclude, I - Include)           
  Creator Like . . PD*         >                                                
                                                                                
  Process Dependent Indexes . . . . . . . . . . N (Yes/No)                      
  Process Referentially Dependent Tablespaces . N (Y - Yes, N - No,             
                                                   B - Build time Expansion,    
                                                   R - Run time Expansion)      
  Process Cloned Tables . . . . . . . . . . . . N (Yes/No)                      
  Process AUX Tablespaces . . . . . . . . . . . N (L - LOB, X - XML,            
                                                   A - All, N - No)             
  Process History and Archive objects . . . . . N (H - History,                 
                                                   A - Archive,                 
                                                   B - Both, N - None)          
  Advanced SQL . . . . Y (Yes/No)    Update SQL Y (Yes/No)
  3. Press Enter.
    The Object Selection Advanced SQL panel is displayed, as shown in following figure:
    Figure 2. Object Selection Advanced SQL panel
     AUTOXPRT V1R1   ----  Object Selection Advanced SQL   --- 2023/10/19  16:28:24 
 Option  ===>                                                  Scroll ===> CSR  
                                                            Db2 Subsystem: SS01 
 -------------------------------------------------------------------------------
 Commands: Execute - Test Sql  Import - Import from dataset                     
 Line Commands: C - Copy  D - Delete  I - Insert  M - Move  R - Repeat          
                T - Table/Column Lookup                                         
 -------------------------------------------------------------------------------
 Cmd  SQL                                                                       
                                                                                
 ***************************** Bottom of Data **********************************
  4. Enter your SQL statement in the SQL input area.
    You can enter any SQL statement that meets these requirements:
    • It must be a SELECT statement that specifies the following columns:
      • The first column is required and must represent a database name.
      • The second column is required and must represent a table space name.
      • The third column is optional, but if present must represent a partition number.
    • The FROM clause must be specified after the SELECT statement.
    • Use any WHERE clause criteria that identifies the objects to be included in the object profile.
    • You can enter comments by inserting two consecutive hyphens, followed by the comment. Any characters that follow two consecutive hyphens and are before the end of a line are ignored.

    You can optionally enter the T line command to look up table names and their columns on the subsystem. This command invokes the Table Selection panel, which you can use to generate a list of tables on this subsystem and find column names associated with the tables.

    The following panel shows a sample valid advanced SQL statement:
    Figure 3. Sample advanced SQL statement
     AUTOXPRT V1R1   ----  Object Selection Advanced SQL   --- 2023/10/19  16:28:24 
 Option  ===>                                                  Scroll ===> CSR  
                                                            Db2 Subsystem: SS01 
 -------------------------------------------------------------------------------
 Commands: Execute - Test Sql  Import - Import from dataset                     
 Line Commands: C - Copy  D - Delete  I - Insert  M - Move  R - Repeat          
                T - Table/Column Lookup                                         
 -------------------------------------------------------------------------------
 Cmd  SQL                                                                       
      SELECT D.NAME,S.NAME                                                      
      FROM SYSIBM.SYSDATABASE D, SYSIBM.SYSTABLESPACE S                         
      WHERE D.IMPLICIT = 'Y' AND S.IMPLICIT = 'Y' AND  D.NAME = S.DBNAME        
 ***************************** Bottom of Data **********************************
  5. To verify that the results of the SQL select statement are as expected, enter EXECUTE in the Option field and press Enter.
    The SQL statement is run and the results of the SELECT are listed on the Advanced SQL Test Facility panel, as shown in the following figure:
    Figure 4. Advanced SQL Test Facility
     AUTOXPRT V1R1   ------- Advanced SQL Test Facility ------ 2023/10/19  16:29:30 
 Option  ===>                                                  Scroll ===> CSR  
                                                                                
                                                            Db2 Subsystem: SS01 
                                                       Row 1 of 529          +> 
 -------------------------------------------------------------------------------
 Dbname   Tsname   Part Creator    Dbid  Obid  Psid Bpool    Lockrule  Pagesize 
 DSN04828 A2345678    1 TSINB       584     1     2 BP0         R             4 
 DSN04829 A2345678    1 TSINB       585     1     2 BP0         R             4 
 DSN04830 A2345678    1 TSINB       586     1     2 BP0         R             4 
 DSN04831 A2345678    1 TSINB       587     1     2 BP0         R             4 
 DSN04722 AAAR0002    1 PDDAB       766     1     2 BP0         R             4 
 DSN03865 ABESS       1 PDSHEL     4973     1     2 BP0         R             4 
 DSN04379 ACDRCD      1 PSDPDB      575     1     2 BP0         R             4 
 DSN04168 APP1        1 TWSHAWN    5345     1     2 BP0         R             4 
 DSN03346 APPLICAN    1 TWSHAWN    4414     1     2 BP0         R             4 
 DSN03774 APPLICAN    1 TWSHAWN    4877     1     2 BP0         R             4 
 DSN03425 ARYRINLI    1 ARYTEST    4494     1     2 BP0         R             4 
 DSN03425 ARYRINLI    2 ARYTEST    4494     1     2 BP0         R             4 
 DSN03425 ARYRINLI    3 ARYTEST    4494     1     2 BP0         R             4 
 DSN02375 ARYTESTR    1 ARYTEST    3348     1     2 BP0         R             4 
 DSN02375 ARYTESTR    2 ARYTEST    3348     1     2 BP0         R             4 
 DSN02375 ARYTESTR    3 ARYTEST    3348     1     2 BP0         R             4 
 DSN02541 ARYTESTR    1 ARYTEST    3524     1     2 BP0         R             4 
 DSN02541 ARYTESTR    2 ARYTEST    3524     1     2 BP0         R             4 
 DSN02541 ARYTESTR    3 ARYTEST    3524     1     2 BP0         R             4 
 DSN03543 ARYTESTR    1 ARYTEST    4631     1     2 BP0         R             4 
 DSN03543 ARYTESTR    2 ARYTEST    4631     1     2 BP0         R             4 
 DSN03543 ARYTESTR    3 ARYTEST    4631     1     2 BP0         R             4 
 DSN02100 BADCHARS    1 PDERMA     3007     1     2 BP0         R             4
    This panel shows the results of the SQL SELECT statement execution. The data on the panel is read only.
    Note: The EXECUTE command runs the SQL statement without consideration for the Database Like, Tablespace Like, and Creator Like criteria that you specified on the Enter Tablespaces Like to Display window.
  6. When you are satisfied with the results of the SELECT statement, on the Advanced SQL Test Facility panel, press PF3 twice.
    The window that is shown in the following figure is displayed.
    Figure 5. Specifying partition processing for spaces
                      Choose Partition Method                  
                                                           
  Utilities can run against each partition or it can       
  run against all partitions.  When DAJ explodes wild      
  card table and index spaces, which method would you      
  like partitioned spaces exploded?                        
                                                           
           Explode  A  (A - All, P - Partitioned)
    Specify how table space partitions are processed. To handle all partitions (for example, equivalent to a REORG TABLESPACE), enter A in the Explode field. To individually process each partition (for example, equivalent to REORG TABLESPACE PART n), enter P in the Explode field.
  7. Press Enter.
    The specified spaces are added to the Update Object Profile Display with Y entered in the corresponding Wildcard column.

What to do next

You can enter the EXPLODE primary command or the E line command to see the expanded list of all table spaces and index spaces currently included in the profile.