Start of change

Db2 function level settings in Db2 Admin Tool

Within Db2 Admin Tool, you can specify the Db2 function level, the APPLCOMPAT function level, the target function level, and the maximum Db2 function level accepted.

These terms are defined as follows:

Db2 function level
The function level at which the Db2 subsystem is currently running.

When Db2 Admin Tool starts, it gets this value from Db2. For batch jobs that do not connect to Db2, Db2 Admin Tool determines the current function level of the subsystem based on the value in the DSNHDECP module.

You can override this value by specifying a different function level on the Options for Change Functions (ADB2PCO) panel in the DB2 function level field:

Figure 1. Db2 function level on the Options for Change Functions (ADB2PCO) panel
Start of change
ADB2PCO n                   Options for Change Functions                  14:19
Command ===>                                                                   
                                                                               
                                                         DB2 System: DD1A      
                                                                    
                                                                               
Recreate accelerated tables  . . . . . YES  (Yes/No. Default is Yes)           
Restore replication of tables  . . . . YES  (Yes/No. Default is Yes)           
Reload accelerated tables  . . . . . . YES  (Yes/No. Default is Yes)           
Restore acceleration of tables . . . . YES  (Yes/No. Default is Yes)           
Remove deleted accelerated tables  . . YES  (Yes/No. Default is Yes)           
      
Load accelerated tables LOCKMODE . . . NONE        (Default is TABLESET)       
Load accelerated tables DETECTCHANGES  DATA        (Default is DATA)  
Unload altered tables  . . . . . . . . NO   (Yes/No/Des. Default is YES)     
Preserve all data  . . . . . . . . . . YES  (Yes/No. Default is YES)      
                                                                               
Enable WSL authorization switching . . NO   (Yes/No. Default is No)            
Object processing order  . . . . . . . H    (T - Object type, H - DB hierarchy.
                                             Default is H)                     
Statement validation exit name . . . .           (Name of EXEC used to validate
                                                  statements in WSL Validate)   
Allow PBR2 to PBR changes  . . . . . . NO   (Yes/No. Default is No) 
DB2 release number . . . . . . . . . . 1215 (Use VVRM format)                  
DB2 function level . . . . . . . . . . 504  (E.g. 100, 500, 501, 5nn)    
GRANT processing order . . . . . . . . C    (C - CREATE prefix for GRANT       
                                             P - POSTUTIL prefix for GRANT     
                                             Default is C )
End of change
Tip: To navigate to this panel, specify P.CH on the DB2 Administration Menu (ADB2) panel.
APPLCOMPAT function level
The function level value of the CURRENT APPLICATION COMPATIBILITY special register. This value is displayed on the main DB2 Administration Menu (ADB2) panel:
Figure 2. APPLCOMPAT value on the DB2 Administration Menu (ADB2) panel
ADB2 dmin -------------- DB2 Administration Menu 12.1.0 ------------------ 00:49
Option ===> 1                                                                  
                                                                               
   1 - DB2 system catalog                                DB2 System: DD1A      
   2 - Execute SQL statements                            DB2 SQL ID: ADM001     
   3 - DB2 performance queries                           Userid    : ADM001     
   4 - Change current SQL ID                             DB2 Schema: ADM001     
   5 - Utility generation using LISTDEFs and TEMPLATEs   DB2 Rel   : 1215       
   P - Change DB2 Admin parameters                       DB2 F.Lvl : V12R1M510                      
  DD - Distributed DB2 systems                           ApplCompat: V12R1M510                     
   E - Explain                                                                 
   Z - DB2 system administration                                               
  SM - Space management functions                                              
   W - Manage work statement lists                                             
   X - Exit DB2 Admin 																				              
  CC - DB2 catalog copy version maintenance												          
  CM - Change management                                                       
                                                                               
Interface to other DB2 products and offerings:                                 
   I  DB2I   DB2 Interactive                                                   
   C  DB2 Object Comparison Tool

The value that is displayed on this panel is the function level that is used by Db2 Admin Tool for all functions, except for generating SQL. For generating SQL, Db2 Admin Tool uses the target function level.

The APPLCOMPAT function level is initially set to the APPLCOMPAT value with which the ADBMAIN package was bound. If you did not specify an APPLCOMPAT bind value, this value is set to the Db2 function level.

You can update the APPLCOMPAT function level by using a SET statement or the APPLCOMPAT primary command.
Restriction: You cannot set the APPLCOMPAT function level to a value that is greater than the function level value with which you bound ADBMAIN.
Target function level
The function level for generated DDL statements. Db2 Admin Tool generates requested DDL statements based on the syntax requirements for the target function level.

The default value is the current Db2 function level. However, you can specify any function level that is equal to or lower than the current Db2 function level. You can specify this value in one of the following places:

  • The Change Management (CM) batch parameter TGT_DB2FL
  • The ADB2RE stored procedure option TGTFL for generating statements
  • On the Generate SQL from DB2 catalog (ADB2GENB) panel, in the Target Function Level field:
    Figure 3. Target function level on the Generate SQL from DB2 catalog (ADB2GENB) panel
    ADB2GENB  -------------- DD1A Generate SQL from DB2 catalog ------------- 16:46
Option ===>                                                                    
                                                                               
 Generate SQL statements for database DSN8DVFZ           DB2 System: DD1A      
                                                         DB2 SQL ID: ADM001    
                                                                   More:     + 
 SQL statement types to be generated from the DB2 catalog:                     
   CREATE DATABASE  . . . . Y (Y,N)   GRANT access ON DATABASE . . Y (Y,N,A,R) 
   CREATE TABLESPACE  . . . Y (Y,N)   GRANT access ON TABLESPACE . Y (Y,N,A,R) 
   CREATE TABLE . . . . . . Y (Y,N)   GRANT access ON TABLE  . . . Y (Y,N,A,R) 
   CREATE VIEW  . . . . . . Y (Y,N)   GRANT access ON VIEW . . . . Y (Y,N,A,R) 
   CREATE INDEX . . . . . . Y (Y,N)   ALTER TABLE ADD FOREIGN KEY. Y (Y,N,D)   
   CREATE SYNONYM . . . . . Y (Y,N)   LABEL ON . . . . . . . . . . Y (Y,N)     
   CREATE ALIAS . . . . . . Y (Y,N)   COMMENT ON . . . . . . . . . Y (Y,N)     
   CREATE MASK  . . . . . . Y (Y,N)   ALTER TABLE ACTIVATE CONTROL Y (Y,N)     
   CREATE PERMISSION  . . . Y (Y,N)                                            
   CREATE TRIGGER . . . . . Y (Y,N,D,A,R)                                      
   CREATE STORAGE GROUP . . Y (Y,N)   GRANT use OF STORAGE GROUP . Y (Y,N,A,R) 
   REBIND PACKAGE . . . . . Y (Y,N,D)                                          
                                                                               
 New names/values for generated SQL: (leave blank to use current values)       
   Object schema  . . . . .           > Run SQLID  . . . . . . . . TS6462 
 New names/values for generated SQL: (leave blank to use current values)       
   Object schema  . . . . .           > Run SQLID  . . . . . . . . TS6462      
   Object grantor . . . . .           >                                        
   Alloc TS size as . . . . DEFINED     (DEFINED, USED, or ALLOC)              
   Database name  . . . . .                                                    
   Storage group for TS . .           > Storage group for IX . . .           > 
   Target DB2 version . . .             (Current DB2 version: 1215)            
   Target Function Level. .             (Current DB2 FL: 508)                  
   Use Masking  . . . . . . NO          (Yes/No)                               
   Use Exclude Spec . . . . NO          (Yes/No)                               
   Target cat qualifier . .           > (Default is SYSIBM)                    
   Generate catalog stats . NO          (Yes,No,Only)                          
     Statistics tables  . . ALL         (All or Select. Default is All)        
                            NO          (Yes,No,Alter,Only)                    
   PBG NUMPARTS value . . .             (Defined, Existing)                    
   PBG LOB objects  . . . .             (Computed, Implicit)                         
   Generate index cleanup .             (Yes,No,Only)                          
                                                                                
 SQL output data set and execution mode:                                       
   Add to a WSL . . . . . . NO          (Yes/No)                               
   Data set name  . . . . .                                                    
     Data set disposition . OLD         (OLD, SHR, or MOD)                     
   Execution mode . . . . . TSO         (BATCH or TSO)                         
   Commit statements per  .             (Db, tS, Tb, All, None. Default is All)
   DB2 defaults handling  .             (Keep, or Remove. Default is Keep)     
   Prompt to run SQL  . . . NO          (Yes/No. For TSO mode and no WSL)      
   Include SQL comments . . NO          (Yes/No)                               
                                                                                
  DB2 Command output data set:                                                  
    Data set name  . . . . .                                                    
      Data set disposition . OLD         (OLD, SHR, or MOD)      
                                        
  BP - Change batch job parameters      
   G - Change additional parameters
    Tip: For instructions on how to navigate to this panel, see Generating SQL to re-create a Db2 object.
Maximum Db2 function level accepted
The maximum function level on which you want to allow Db2 Admin Tool to run, even if that function level is not tolerated or supported.

By default, no value is defined. You can specify this value in one of the following places:

  • On the Db2 Function Level Confirmation (ADB2CONF) panel when you start Db2 Admin Tool. See Db2 function level confirmation when starting Db2 Admin Tool.
  • The Change Management (CM) batch parameter ACCEPT_FL
  • The ADB2RE stored procedure option ACCEPT FL for generating statements
  • On the Admin Defaults (ADB2P2) panel in the Max Db2 function level accepted field:
    Figure 4. Maximum function level accepted on the Admin Defaults (ADB2P2) panel
     ADB2P2 in                        DB2 Admin Defaults                       16:14
 Option ===>                                                                    
                                                                                
                                                          DB2 System: DD1A      
                                                                    More:     + 
                                                                                
 Max No of Rows to Fetch  . . . . . . 1000   (0-327670, 0=unlimited, def. 1000) 
                                                                                
 Max Chars in an SQL Stmt . . . . . . 32765  (4000-32765, default is 32765)     
                                                                                
 Pgm Action when SQL error:                                                     
   First do a . . . . . . . . . . . . R      (C-Commit, R-Rollback)             
   Display error panel  . . . . . . . YES    (Yes/No)                           
   Continue executing SQL . . . . . . NO     (Yes/No)                           
                                                                                
 Auto Refresh After Update  . . . . . YES    (Yes/No, default is YES)           
                                                                                
 Display SQL cost estimate  . . . . . NO     (Yes/No, default is YES)           
                                                                                
 Browse DB2 Command Output  . . . . . YES    (Yes/No)                           
                                                                                
 Max Chars in an ISPF Stmt  . . . . . 2000   (500-32765, default is 2000)       
 ...
                                                                          
Format type for SQL stmts  . . . . . S      (E-Enhanced or S-Simple)      
                                                                          
Run Accelerator functions in batch . NO     (Yes/No, default is NO)       
                                                                          
Max Db2 function level accepted  . . 506    (Blank or nnn>500)
    Tip: To navigate to this panel, specify P.2 on the DB2 Administration Menu (ADB2) panel.

Scenarios for setting function level values

You want to recreate an object without attributes that are new in Db2 12
Suppose that your Db2 function level is 504. You want to process a change that drops and recreates a table space that was originally defined with PAGENUM in the catalog. However, you do not want to use PAGENUM when recreating the table space. (PAGENUM is a new attribute that is available in function level 500.) In this case you want to set the target function level to 100 by performing the following actions:
  • For CM batch jobs: specify TGT_DB2FL = '100' as shown in the following example:
    //*                                                  
//ANALYZE  EXEC GOCCM,SSID=DC1A,PLAN=TS5764P          
//PARMS DD *                                         
 SYMBOL_NAME = '&SCENARIO.' SYMBOL_VALUE = 'A1638B01'                                      
 ACTION_ANALYZE_CHANGE = 'Y'                         
 CHANGE_NAME = '&SCENARIO..&JOBNAME.'                 
 CHANGE_COMMENT = 'CHANGE1'                          
 EXISTING_DATA_SET_ACTION='REPLACE'                  
 ACCEPT_FL = '504'   
 TGT_DB2FL = '100'
  • For GEN:
    1. On the Table Spaces (ADB21S) panel, specify the GEN line command for the table space.
    2. On the Generate SQL from DB2 catalog (ADB2GENB) panel, specify the following values and press Enter:
      • Target Function Level = 100
      • Execution mode = BATCH
    The generated batch JCL has ACCEPT_FL='504' and TGTFL='100':
    //IN       DD *                                   
  DB2SYS   = 'DC1A',                               
  DB2ALOC  = '',                                  
  DB2SERV  = 'RS22DC1A',                          
  DB2AUTH  = 'TS5764',                            
  DB2REL   = '1215',                              
  GENSG    = 'N',                                 
  GENDB    = '',                                  
  GENTS    = 'Y',                                 
  GENTABLE = 'Y',                                 
  GENVIEW  = 'N',                                  
  GENINDEX = 'Y',                                 
  GENSYN   = 'N',                                 
  GENALIAS = 'N',                                 
  GENLABEL = 'N',                                 
  GENCOMM  = 'N',                                  
  GENRELS  = 'N',                                 
  GENTRIG  = 'Y',                                 
  ACTVCNTL = 'N',                                 
  PENDCHGS = 'Y',                                 
  GENMASK  = 'N',                                  
  GENPERM  = 'N',                                 
  GRANTDB  = '',                                  
  GRANTTS  = 'Y',                                 
  GRANTTAB = 'N',                                 
  GRANTVW  = 'N',                                  
  GRANTSG  = 'N',                                 
  IMPLDB   = '',                     
  IMPLTS   = '',                     
  NEWDB    = '',                     
  NEWTSSG  = '',                     
  NEWIXSG  = '',                      
  NEWSQLID = '',                     
  NEWGRANTOR = '',                   
  SPCALLOC = 'DEFINED',              
  ROLEWARN = 'Y',                    
  GETDB2ZP = 'Y',                    
  ACCEPT_FL = '504',                 
  TGTFL    = '100',                  
  TGTDB2   = '';                     
  DB='DBXXEE01', TS='TUXXEE01';      
/*
    The resulting DDL for CREATE TABLESPACE does not include the PAGENUM option.
Your subsystem is at FL 500 but a higher Db2 function level is specified in the ISPF profile.
In this case, the profile might have been set up with a different subsystem. To set the Db2 function level value to FL 500:
  1. From the main DB2 Administration Menu (ADB2) panel, specify P.CH.
  2. On the Options for Change Functions (ADB2PCO) panel, specify 500 in the DB2 function level field.
Your subsystem is at FL 505 but you have not yet applied maintenance to Db2 Admin Tool to support FL 505
If you are not using any FL 505 enhancements, Db2 Admin Tool will run as usual. However, you must specify 505 as the maximum Db2 function level accepted. When you start Db2 Admin Tool, specify option 3 on the Db2 Function Level Confirmation (ADB2CONF) panel. If you are running the ADB2RE stored procedure to generate SQL or running CM batch, add the following parameter to your jobs: ACCEPT_FL = '505'.
End of change