Share:|

One of my favorite BMC Catalog Manager for DB2 commands is APPLY. The idea behind this command is to Apply a SQL statement model or template to a list of objects. With this command, you can generate and execute ad-hoc SQL statements without the need of editing or generating statements one by one.

 

This command will substitute the host variables in the SQL template for the corresponding columns in the list entries and create one statement for each object in the list. The host variables must be the DB2 catalog column names.

 

Let's do a step by step example:

 

Let's suppose I want to add a RESTRICT ON DROP attribute to all my tables.

 

First, I generate a list with all my tables, from the LIST panel or just from any panel with TB ISPRXM.%. Now, I could generate an ALTER statement for each of them, change this attribute and execute, but that could be really time consuming, depending on the number of tables.

 

I will do it in just one execution, using APPLY. From any row in the list, I use 's' to see the full catalog row information. There, I can see that the columns I would need for the statement template are named CREATOR and NAME in the catalog (SYSTABLES).

 

Then, I need to create the SQL template. I could do it by hearth or by checking the right sintax in the manual, but it is much easier if BMC Catalog Manager for DB2 do it for you. From any row in the list, I use ALTER to generate an ALTER statement, in the "Create/Alter Table" panel, change the WITH RESTRICT ON DROP attribute and generate the SQL:

 

     ALTER TABLE                      
       ISPRXM.MYTABLE       
          ADD RESTRICT ON DROP        
     ;  

 

Then, I edit the SQL and change the column values for host variables with the column names:

 

     ALTER TABLE                      
       :CREATOR.:NAME       
          ADD RESTRICT ON DROP        
     ;                                                              

and save it in SQL Table as MYAPPLYSQL.

 

Then, I come back to my list and just use the command APPLY MYAPPLYSQL ALL. If I want to exclude any particular object in the list, I can use the X designator in the Cmd column. BMC Catalog Manager for DB2 will process the command against all objects except those excluded with the X designator.

 

In the "Confirm APPLY SQL MODEL" panel, I can review, edit and change the template, and select Apply to list objects 'Y'. In the "Confirm SQL" panel, I can see it has generated an ALTER statement for each table in the list, substituting the correct creator and name from the list. There, I can edit, save and execute the list of commands.

 

Of course, I could be done with SQL, using a combination of literals and column names from SYSIBM.SYSTABLES, but this method is much easier.

 

Another not well known BMC Catalog Manager for DB2 command that is related to this, is IMPORT (do not confuse with IMPORT function in BMC Change Manager for DB2). With this command, you can import SQL statements in a PDS dataset (for example, the PDS you use as input for SPUFI) to the BMC Catalog Manager for DB2 SQL table. From the SQL Table, it is very easy to browse, edit or execute SQL Statements or of course, use them as SQL templates for APPLY command.

 

Hope this entry can be useful.

Enjoy