Create Objectset—Sql - Documentation for Bmc Ami Recovery Manager for Db2 12.1

Sophia Al-Mansoor
Sophia Al-Mansoor
• • 4 min read

Use This Option to Specify Objects by Using a User-Defined Sql Select Statement in the Armsql Dd Statement. for More Information, See Specifying the Armbgrp...

Create Objectset—Sql - Documentation for Bmc Ami Recovery Manager for Db2 12.1

Use this option to specify objects by using a user-defined SQL SELECT statement in the ARMSQL DD statement.

For more information, see Specifying the ARMBGRP data set DD statements.

Must Read

You can also enter dynamic SQL inline in the INCLUDE SQL syntax using #BEGINSQL and #ENDSQL. This option allows multiple INCLUDE SQL statements with multiple SQL syntax in the same CREATE OBJECTSET syntax as shown in the following example:

//ARMIN     DD *             
  CREATE OBJECTSET RDAJTR.BY_SQL01
   REPLACE YES               
   DESCRIPTION 'SQL BY BATCH'
   INCLUDE SQL 
     #BEGINSQL
     SELECT 'TS', DBNAME, NAME
     FROM SYSIBM.SYSTABLESPACE
     WHERE DBNAME = 'ARMDBJTR'
     #ENDSQL                   
   INCLUDERI NO              
   INCLUDEIX NO              
   INCLUDELOB NO
   INCLUDE SQL 
     #BEGINSQL
     SELECT 'TS', DBNAME, NAME
     FROM SYSIBM.SYSTABLESPACE
     WHERE DBNAME = 'ARMDBLOB'
     #ENDSQL                                              
   INCLUDERI NO              
   INCLUDEIX YES              
   INCLUDELOB YES
   BYPART YES
   ...
   ...
   ...                                                
/*

The following examples use EXCLUDE SQL:

//ARMIN     DD *             
  CREATE OBJECTSET RDAJTR.BY_SQL01
   REPLACE YES               
   DESCRIPTION 'SQL BY BATCH'
   EXCLUDE SQL 
EXCLUDE SQL #BEGINSQL - SQL statements - #ENDSQL
      #BEGINSQL                                 
      SELECT 'TS', DBNAME, NAME                 
      FROM SYSIBM.SYSTABLESPACE                 
      WHERE DBNAME = 'XXXXXXX'                  
      #ENDSQL                            

EXCLUDE SQL #BEGINSQL - SQL statements - #ENDSQL
    #BEGINSQL                                   
    SELECT 'IX', CREATOR, NAME, CREATOR, NAME   
    FROM SYSIBM.SYSINDEXES                      
    WHERE DBNAME = 'XXXXXXX'

EXCLUDE SQL #BEGINSQL - SQL statements - #ENDSQL
    #BEGINSQL                                   
    SELECT 'SG', CREATOR, NAME, CREATOR, NAME   
    FROM SYSIBM.SYSSTOGROUP                     
    WHERE NAME = 'XXXXXXX'                      
    #ENDSQL
OptionDescription
LIKE

When used with INCLUDE SQL or EXCLUDE SQL, the LIKE option builds an object set using the name pattern that you specified and copies the attributes of an existing object set. The backup and recovery options of the existing object set and optionally the authorizations and objects of the existing object set are copied to the new object set that you are creating. Enter the name of the existing object set in the format creator2.name2. For more information, see Copying object sets.

Be aware of the following information:

  • Only one LIKE parameter is allowed in each CREATE command.

  • The LIKE parameter can be coded either before or after the INCLUDE SQL option.

  • To use LIKE, your primary or secondary AUTHID must have authority to open creator2.name2 (or else you must have SYSADM or system DBADM authority). The name of the new object set that you are creating cannot be the same as creator2.name2.

RETAIN AUTH

Not required; authorization performed by the Solution Common Code (SCC) 

This option is only valid in conjunction with the LIKE statement. RETAIN AUTH YES causes the new object set to retain all of the authorizations granted in the existing object set. RETAIN AUTH NO causes the new object set to be created with no object set authorizations.

RETAIN OBJECTS

This option is valid in conjunction with the LIKE statement. RETAIN OBJECTS YES (the default) causes the new object set to retain all objects and object definitions currently within the existing object set. RETAIN OBJECTS NO causes the new object set to be created without including the objects from the existing object set.

#BEGINSQL ... #ENDSQL

Use #BEGINSQL and #ENDSQL to enclose SQL statements within the INCLUDE SQL or EXCLUDE SQL syntax. SQL must begin with syntax #BEGINSQL and must end with #ENDSQL.

INCLUDERI

Use this option to include all table spaces associated by referential integrity in the object set.

INCLUDEIX

Use this option to include all associated indexes in the object set.

INCLUDELOB

Use this option to add all table spaces that are associated by LOB columns with the objects in the object set. Doing so ensures that both the base table space and the LOB table space are included in the object set.

INCLUDEXML

Use this option to add all table spaces that are associated by XML columns with the objects in the object set. Doing so ensures that all XML-related objects are included in the object set and will be processed together.

INCLUDEHISTORY

Use this option for Db2 Version 10 and later to add all of the objects that are associated by a history (versioning) relationship to those specified in the object set. The objects are also referred to as temporal objects and history objects.

BYPART

Use this option to add tables spaces to the object set by partition. BYPART YES has no effect on non-partitioned table spaces.
Use this option to add indexes to the object set by partition. BYPART YES has no effect on non-partitioned index spaces.

Sophia Al-Mansoor

Sophia Al-Mansoor

Global Business & E-Commerce Reporter
Linkedin Twitter

Sophia analyzes international trade, startup ecosystems, retail transformation, and supply chain logistics for modern digital publications.

Share this article
Twitter Facebook Pinterest