Installation and migration
This chapter provides information to help you evaluate the changes in DB2 11 for z/OS and to plan for a successful installation of or migration to DB2 11 for z/OS. It includes the following topics:
12.1 Currency of versions and migration paths
Figure 12-1 is an overview over the general availability (GA) and end of service (EOS) dates for DB2 for z/OS.
Figure 12-1 Currency of versions
Before you begin the installation or migration process, look at the big picture. You need to be aware of the major requirements to get from your current version of DB2 to DB2 11 for z/OS. You need to know where you are currently and where you need to be before you embark on this process considering DB2, z/OS, and tools.
Figure 12-2 points out the versions, currency dates, and the minimum required z/OS levels.
Figure 12-2 DB2 versions and required z/OS level
The discussion in this book, and mostly in Chapter 2, “Synergy with System z” on page 7, has described the functions of DB2 that you can enable if your z/OS level is z/OS 2.1, rather than the minimum required level z/OS 1.13. If there are functions you need with z/OS 2.1, you need to take into account the z/OS migration first.
As shown in Figure 12-1, for DB2 10 provides the opportunity to get to it by using a skip-level migration from DB2 V8. For DB2 11 skipping level is not allowed. Thus, if you are on DB2 9, you first have to move to DB2 10 to migrate to DB2 11. As a result, the migration process has fewer modes than for the migration to DB2 10.
The modes that you can use while migrating to DB2 10 are DB2 11 conversion mode (CM), DB2 11 CM*, DB2 11 ENFM, DB2 11 ENFM*, DB2 11 NFM.
Figure 12-3 summarizes the possible migration modes and how you can get to each of them.
Figure 12-3 Migration modes and paths
DB2 can operate in the following various mode during the migration to DB2 11: =
CM Conversion mode is the mode DB2 is in when DB2 11 is started for the first time after migration from DB2 10. When DB2 11 is started for the first time, you see messages indicating that the code DB2 is running under is the DB2 11 code, but that the catalog is still in DB2 10. You must fix this mismatch after you started DB2 with V11 code. Job DSNTIJTC is the installation job that handles this catalog adjustment. After successful execution of DSNTIJTC DB2 still remains in DB2 11 CM. Data sharing systems can be migrated from DB2 10 NFM to DB2 11 CM one member at a time. DSNTIJTC needs to be executed only once, because it is in the nature of a DB2 data sharing group that the catalog is common and shared among the members. CM is the only mode that allows for release co-existence between DB2 10 and DB2 11 members.
Important: Fallback to DB2 10 NFM is possible only from CM. This point is illustrated by the vertical bar in Figure 12-3 and is labelled as the Point of no return.
ENFM The Enabling New Function Mode is entered after installation job DSNTIJEN is executed. This job invokes the CATENFM utility with the START option, which prepares the DB2 catalog for DB2 11. H describe this step in 12.4.3, “DB2 11 ENFM and NFM” on page 339.
DB2 remains in this mode until all the enabling functions are completed. Data sharing systems can only have DB2 11 members in this mode.
NFM After the catalog migration completes successfully, you can use job DSNTIJNF, which also invokes the CATENFM utility, but this time with the COMPLETE option to reach New Function Mode. This mode indicates that all catalog changes are complete and new functions can now be used.
ENFM* The ENFM* mode is the same as ENFM, but the * indicates that at one time DB2 was at DB2 11 NFM. Objects that were created when the system was at NFM can still be accessed but no new objects can be created. When the system is in ENFM* it cannot fallback to DB2 10 or coexist with a DB2 10 system.
CM* This mode is the same as CM, but the * indicates that at one time the subsystem was at a higher level. Objects that were created at the higher level can still be accessed. When DB2 is in CM* it cannot fallback to DB2 10 or coexist with a DB2 10 system.
12.2 Prerequisites for DB2 11
This section describes the prerequisite requirements for hardware and software to successfully install and work with DB2 11 for z/OS.
12.2.1 Processors
DB2 11 operates on IBM z10™ or later processors running z/OS 1.13 or later. The processors must have enough real storage to satisfy the combined requirements of:
•DB2 11 for z/OS
•z/OS
•The appropriate DFSMS storage management subsystem components, access methods, telecommunications, batch requirements and other applications required in your environment.
 
Tip: DB2 11 requires increased real storage as compared to DB2 10 for z/OS.
12.2.2 Auxiliary storage
The minimum disk space requirement, based on installing DB2 using the panel default values is approximately 1.3 GB. You need additional space for your data.
 
Note: The default values might not meet your installation’s needs. Over time more disk space might be required for your DB2 subsystems.
12.2.3 Operational requirements
Operational requirements are the products that are required and must be present on the system or the products that are not required but should be present on the system for this product to operate all or part it its functions.
Mandatory requirements
Mandatory operational requisites identify products that are required for DB2 to operate its basic functions. Table 12-1 lists these requirements tor DB2 11.
Table 12-1 Mandatory operational requirements
Program number
Product name and minimum service level
5615-DB2
DB2 11 for z/OS, DB2 base APAR PM93577
5615-DB2
DB2 11 for z/OS, internal resource lock manager (IRLM) 2.3, plus APARs PM84765 and PM85053
Any one of the following:
5694-A01
z/OS (DFSMS, IBM Language Environment® Base Services, Security Server/RACF) V1.13
5650-ZOS
z/OS (DFSMS, Language Environment Base Services, Security Server/RACF) V2.1
The following functions in DB2 11 require z/OS V2.1
•2 GB large pages
•1 MB fixed page frames for DB2 execution code
•Improved performance of batch updates in data sharing
•Improved usability and consistency for security administration
Conditional operational requirements
Conditional operational requisites identify products that are not required for DB2 11 to operate its basic functions but are required at run time to operate specific functions. Table 12-2 lists the requirements.
Table 12-2 Target system conditional operational requirements
Program number
Product name and minimum service level
Function
5655-N98
IBM SDK for z/OS, Java 2 Technology Edition
Applications or stored procedures written in Java, such as those using the JDBC or SQLJ interfaces to DB2;
Decimal Float data type usage in Java (in a 31-bit environment)
5655-N99
IBM SDK for z/OS, Java 2 Technology Edition
Applications or stored procedures written in Java, such as those using the JDBC or SQLJ interfaces to DB2;
Decimal Float data type usage in Java (in a 64-bit environment)
5697-A01
z/OS 1.13 Web Deliverable
1 MB pageable for new FRAMESIZE and PGFIX(NO) in DB2 11 for buffer pool
5697-A01
z/OS 1.13 APAR OA40967
2 GB page for new FRAMESIZE option in DB2 11 for buffer pool
5635-A02
Information Management System (IMS) V11.01.00
Transaction Management
5655-M15
Customer Information Control System (CICS) Transaction Server for z/OS V03.01.00 and V03.02.00
Transaction Management
For V03.01.00 and V03.02.00 you need APAR PM01800 to return the correct Version and Release number for DB2 11
12.2.4 Optional program requirements
This section describes which version of associated products are tolerated by DB2 11.
Connectivity
DB2 for z/OS supports DRDA as an open interface allowing access from any client.
DB2 Connect Version 10.1 Fixpack 2 or DB2 Connect Version 9.7 Fixpack 6 clients or higher are the minimum required levels for a seamless migration.
However, DB2 Connect Version 10.5 Fixpack 2 is required to support some DB2 11 for z/OS features, such as:
•Array support
•Autocommit performance improvements for procedures and cursors
•Data sharing support for global variables
•Longer client information fields
DB2 11 acting as a client supports the following relational database products:
•DB2 Enterprise Server (ESE) for Linux, UNIX and Windows, V9.5 (575-F41) or later
•DB2 Express Edition for Linux, UNIX and Windows, V9.5 (5724-E49) or later
•Database Enterprise Developer Edition V 9.5 (5724-N76) or later
•DB2 for IBM iSeries® V6.1. (5761-SS1) or later
•DB2 Server for VSE & VM V7.3 (5697-F42) or later
•Any other DRDA compliant client or relational DBMS server
Development tools
The following products improve the productivity of database designers, administrators and application developers that are working with DB2 11:
•InfoSphere Optim™ pureQuery® Runtime for z/OS V3.3 (5655-W92)
•InfoSphere Optim Configuration Manager for DB2 for z/OS V3.1 (5655-AA3)
•IBM Data Studio V4.1
Programming languages
The minimum levels for programming languages are:
•Enterprise COBOL for z/OS V3.4 (5655-G53) or later
•VS Fortran 2.6 (5668-806, 5688-087, 5668-805). New data type and function are not supported since DB2 9.
•Enterprise PL/I for z/OS V3.9 (5655-H31)
IBM DB2 Accessories Suite for z/OS
IBM DB2 Accessories Suite for z/OS, V3.1 (5697-Q04) is a no-charge offering consisting of three features, each bundling components designed to enhance your use of DB2 for z/OS, including the addition of and changes to the following components:
•The DB2 Accessories Suite V11 feature provides spatial functions supporting DB2 11 for z/OS.
•The JSON capability bundles necessary components that enable DB2 10 for z/OS to be used as a JSON document store.
•An update to Data Studio 4.1 delivers health monitoring, single query tuning, and application development tools for DB2 for z/OS.
12.3 DB2 11 installation changes and considerations
As DB2 evolves in its overall improved functionality, there are also several changes which apply to the installation of a DB2 11 subsystem. This section describes the following DB2 11 installation changes and considerations:
12.3.1 More support of naming standards in install and IVP jobs
When you enter the installation panel through CLIST DSNTINST, the first panel that you see is DSNTIDA1. This panel was changed in DB2 10, allowing you to use different prefixes for the SMP/E target library names and for everything else. This enhancement avoids the additional editing on subsequent panels such as panel DSNTIPT, where you can specify all the output library names that you would like to use.
Starting with DB2 11, a new panel DSNTIPG has been added to the install dialog. Figure 12-4 shows the new install panel DSNTIPG. The information that you can provide here gives great flexibility to change user IDs and library name prefixes for the installation and IVP job which are generated through the dialog.
DSNTIPG INSTALL DB2 - INSTALLATION PREFERENCES
===>
Enter authorization IDs for installing DB2-supplied routines:
1 ROUTINES CREATOR ===> SYSADM Authid to create and bind DB2 routines
2 SEC DEF CREATOR ===> SYSADM Authid for routines w/ SECURITY DEFINER
Enter authorization IDs for other installation and IVP jobs:
3 INSTALL SQL ID ===> SYSADM To process SQL in install jobs
4 INSTALL PKG OWNER ===> To own packages bound by install jobs
5 INSTALL GRANTEE(S)===> PUBLIC > To be granted access on objects created
by install jobs
Enter the prefix for data sets created by installation and IVP jobs:
6 INSTALL IC PREFIX ===> DSN1110 For COPY data sets
7 INSTALL DS PREFIX ===> DSN1110 For other data sets
F1=HELP F2=SPLIT F3=END F4=RETURN F5=RFIND F6=RCHANGE
F7=UP F8=DOWN F9=SWAP F10=LEFT F11=RIGHT F12=RETRIEVE
. . . . . . . . . . . . . . . . . . . . . . . . . . .
Figure 12-4 DSNTIPG install panel
The following options are available in the new panel:
•1 ROUTINES CREATOR
The ROUTINES CREATOR field specifies the CURRENT SQLID setting that is to be used when creating, configuring, and validating most DB2-supplied routines. This field also specifies the default OWNER that is to be used when binding packages for these routines.
Acceptable values are 1 to 8 characters, the first of which must be an alphabetic character.
The value that you enter in the ROUTINES CREATOR field is assigned by the installation CLIST as the setting of the AUTHID parameter for installation programs DSNTRIN in job DSNTIJRT and DSNTRVFY in DSNTIJRV job.
The AUTHID parameter is used by the DSNTRIN program as the CURRENT SQLID setting when creating and configuring DB2-supplied routines. The DSNTRIN program also uses the AUTHID parameter as the default OWNER when binding packages for the DB2-supplied routines.
The AUTHID parameter is used by the DSNTRVFY program as the CURRENT SQLID setting when validating DB2-supplied routines. The DSNTRVFY program also uses the AUTHID parameter as the default OWNER when binding packages for validation of these routines.
 
Note: Specify an authorization ID that has installation system administrator authority. Routines that are created or configured with installation system administrator authority are marked as system-defined.
System-defined routines can be executed by the system DBADM and SQLADM authorities, which fits well with popular SQL tuning tools. For example, IBM Optim Query Tuner requires many of the DB2-supplied stored procedures to be available and accessible by an authority that focuses on SQL tuning activities.
•2 SEC DEF CREATOR
The value of the SEC DEF CREATOR field specifies the CURRENT SQLID setting that is to be used when creating and configuring DB2-supplied routines that are defined with the SECURITY DEFINER option.
Acceptable values are 1 to 8 characters, the first of which must be an alphabetic character.
The value that you enter in the SEC DEF CREATOR field is assigned by the installation CLIST as the setting of the SECDEFID parameter for installation program DSNTRIN in job DSNTIJRT.
The SECDEFID parameter is used by the DSNTRIN program as the CURRENT SQLID setting when creating and configuring DB2-supplied routines that have the SECURITY DEFINER attribute. The default owner of the packages that are bound for these routines is the ID that is specified in the ROUTINES CREATOR field.
 
Note: Specify an ID that can be used as a logon ID because it is used by WLM to execute routines that have the SECURITY DEFINER attribute.
•3 INSTALL SQL ID
The INSTALL SQL ID field specifies the CURRENT SQLID setting that is to be used when SQL is processed by most DB2 installation and sample jobs.
This field does not apply to the DSNTIJRT and DSNTIJRV jobs. For these jobs, you can use the ROUTINES CREATOR and SEC DEF CREATOR fields, which are also on the DSNTIPG panel, to specify the CURRENT SQLID.
•4 INSTALL PACKAGE OWNER
The INSTALL PKG OWNER field specifies the authorization ID to assign as the owner of packages and plans that are bound by most installation and sample jobs.
This field does not apply to the DSNTIJRT and DSNTIJRV jobs. Use installation panels DSNTIPRA - DSNTIPRP to specify package owners for stored procedures that are provided by DB2.
•5 INSTALL GRANTEE(S)
The INSTALL GRANTEE(S) field specifies the authorization IDs that are to be granted access to objects that are created and bound by most installation and sample jobs.
This field does not apply to the DSNTIJRT and DSNTIJRV jobs. Use installation panels DSNTIPRA - DSNTIPRP to specify authorization IDs for routines that are provided by DB2.
Use commas to separate individual IDs. Do not use embedded blanks. You can enter up to 44 characters, including commas.
To be able to enter more than one ID, type EXPAND in the command line, place the cursor on the input field, and hit enter. You then get a screen that allows you to enter various IDs up to a length of 44 bytes in total.
Alternatively, you can also assign EXPAND to one of your function keys, place the cursor into the input field, and press Enter to open the ISPEXPND screen shown in Figure 12-5.
Figure 12-5 EXPAND screen for panel DSNTIPG
The authorization IDs that you enter in this field are granted the following privileges:
 – The USE privilege for STOGROUPs and table spaces that are created by IVP jobs
 – The USE privilege for buffer pool BP0, the SYSDEFLT storage group, and table space DSNDB04.SYSDEFLT
 – The DBADM privilege for databases that are created by IVP jobs
 – The CREATETAB and CREATETS privileges for the temporary database, DSNDB04
 – The DELETE, INSERT, SELECT, and UPDATE privileges for tables and created global temporary tables that are created by IVP and installation jobs other than DSNTIJRT
 – The EXECUTE privilege for packages and plans that are bound by IVP and installation jobs other than DSNTIJRT
 – The BIND privilege on most plans that are bound by IVP jobs
•6 INSTALL IC PREFIX field
The INSTALL IC PREFIX field specifies the prefix for image copy data sets that are created by DB2 installation and IVP jobs.
This is especially beneficial because the image copy prefix almost always had to be changed in the past in jobs, for example DSNTEJ0.
The value that you can specify here are 1 to 17 characters that form a valid z/OS data set name prefix.
•7 INSTALL DS PREFIX field
The INSTALL DS PREFIX field specifies the prefix for most data sets that are created by most DB2 installation and IVP jobs. This field does not apply to data sets that are created by job DSNTIJIN, which applies for example to TEMPLATE or LISTDEFs that are created as part of the IVP jobs.
Again, the value that you can specify here are 1 to 17 characters that form a valid z/OS data set name prefix.
12.3.2 No more EDM calculations
Traditionally the installation CLIST used linear calculations based on the estimate number of databases, plan, and so on to determine the settings of the various EDM pools. These calculations have changed with the changes in virtual storage use by DB2 and have now been replaced by stepped sizing according to your site’s size. These are the five defined stepped sizes:
Small site: About 100 plans, 50 application databases, and 1000 tables
Small-Medium site: About 200 plans, 200 application databases, and 4000 tables
Medium site: About 400 plans, 400 application databases, and 8000 tables
Medium-Large site: About 600 plans, 600 application databases and 12,000 tables
Large site: About 800 plans, 800 application databases, and 16,000 tables
These settings are starting points. You have to check your actual requirements.
Based on the numbers indicated on the installation CLIST panels, Table 12-3 lists pool sizes.
Table 12-3 EDM Pool stepped sizings
System size/ parameter name
EDMDBDC (KB)
EDMSTMTC (KB)
EDM_SKELETON_ POOL (KB)
Small
40960
122880
81920
Small-Medium
102400
307200
204800
Medium
204800
614400
409600
Medium-Large
409600
1228800
819200
Large
819200
2457600
1638400
When you get to the DSNTIPC panel, shown in Figure 12-6, you can override the calculated stepped sizes. This example decreases the storage sizes that installation CLIST has picked based on the values entered on previous installation panels.
If you compare the values in Table 12-3 with the values that were assigned to the 3 EDM pools, you can see that they match with the ones for a small site. This, in fact is what you would get if you accept the default values coming from input member DSNTIDXA.
DSNTIPC INSTALL DB2 - CLIST CALCULATIONS - PANEL 1
===>
You can update the DSMAX, EDMPOOL STATEMENT CACHE (if CACHE DYNAMIC is YES),
EDM DBD CACHE, SORT POOL, and RID POOL sizes if necessary.
Calculated Override
1 DSMAX - MAXIMUM OPEN DATA SETS = 20000 (1-200000)
2 DSNT485I EDM STATEMENT CACHE = 122880 K K
3 DSNT485I EDM DBD CACHE = 40960 K K
4 DSNT485I EDM SKELETON POOL SIZE = 81920 K K
5 DSNT485I EDM LIMIT BELOW THE BAR = 0 K K
6 DSNT485I BUFFER POOL SIZE = 109 M
7 DSNT485I SORT POOL SIZE = 10000 K K
8 DSNT485I MAX IN-MEMORY SORT SIZE = 1000 K K
9 DSNT485I RID POOL SIZE = 400000 K K
10 DSNT485I DATA SET STORAGE SIZE = 26000 K
11 DSNT485I CODE STORAGE SIZE = 38200 K
12 DSNT485I WORKING STORAGE SIZE = 45024 K
13 DSNT486I TOTAL MAIN STORAGE = 617 M M
14 DSNT487I TOTAL STORAGE BELOW 16M = 1036 K WITH SWA ABOVE 16M LINE
15 DSNT438I IRLM LOCK MAXIMUM SPACE = 2160 M, AVAILABLE = 2160 M
 
PRESS: ENTER to continue RETURN to exit HELP for more information
Figure 12-6 Install Panel DSNTIPC
If you are installing DB2 for the first time and not using SAP, use the supplied defaults input member, DSNTIDXA. If you are using SAP, you should specify DSNTIDXB, the SAP-specific input member. If you process the panels several times within a single run of the CLIST, all the previous values that are entered, except edited output data sets, remain the same.
If you use the SAP input member, the calculated storage sizes are quite different as shown in Figure 12-7.
DSNTIPC INSTALL DB2 - CLIST CALCULATIONS - PANEL 1
===>
You can update the DSMAX, EDM STATEMENT CACHE (if CACHE DYNAMIC is YES),
EDM DBD CACHE, EDM SKELETON POOL, SORT POOL, and RID POOL sizes if necessary.
Calculated Override
1 DSMAX - MAXIMUM OPEN DATA SETS = 20000 20000 (1-200000)
2 DSNT485I EDM STATEMENT CACHE = 300000 K K
3 DSNT485I EDM DBD CACHE = 150000 K K
4 DSNT485I EDM SKELETON POOL SIZE = 81920 K K
5 DSNT485I EDM LIMIT BELOW THE BAR = 0 K K
6 DSNT485I BUFFER POOL SIZE = 2085 M
7 DSNT485I SORT POOL SIZE = 10000 K 64000 K
8 DSNT485I MAX IN-MEMORY SORT SIZE = 1000 K K
9 DSNT485I RID POOL SIZE = 400000 K 100000 K
10 DSNT485I DATA SET STORAGE SIZE = 26000 K
11 DSNT485I CODE STORAGE SIZE = 38200 K
12 DSNT485I WORKING STORAGE SIZE = 17404 K
13 DSNT486I TOTAL MAIN STORAGE = 2566 M 2326 M
14 DSNT487I TOTAL STORAGE BELOW 16M = 1269 K WITH SWA ABOVE 16M LINE
15 DSNT438I IRLM LOCK MAXIMUM SPACE = 1292763 M, AVAILABLE = 4096 M
F1=HELP F2=SPLIT F3=END F4=RETURN F5=RFIND F6=RCHANGE
F7=UP F8=DOWN F9=SWAP F10=LEFT F11=RIGHT F12=RETRIEVE
. . . . . . . . . . . . . . . . . . . . . . . . . . .
Figure 12-7 DSNTIPC results when using DSNTIDXB member
12.3.3 Modified installation jobs
Several installation jobs have been changed fromDB2 10 to reflect the product layout in DB2 11. This section lists the changed jobs and provides a few details about the changes:
DSNTIJIN
During installation, the DSNTIJIN job defines VSAM and non-VSAM data sets for DB2. The following groups of changes are applied to this job:
•The VSAM DEFINE statement for the BSDS data sets have been modified. The changes are necessary to support for longer RBA/LRSN.
•The job steps that were used to defined the following table space have been removed:
 – DSNDB06.SYSCOPY
The SYSIBM.SYSCOPY table now resides in the DSNDB06.SYSTSCPY table space.
 – DSNDB06.SYSSTR
This table space used to contain the following tables:
 • SYSIBM.SYSSTRINGS
 • SYSIBM.SYSCHECKS
 • SYSIBM.SYSCHECKDEP
 • SYSIBM.SYSCHECKS2
DB2 11 defines one table space for each table. The following new table spaces are listed in the order corresponding to the tables:
 • DSNDB06.SYSTSSRG
 • DSNDB06.SYSTSCHKS
 • DSNDB06.SYSTSCHKD
 • DSNDB06.SYSTSCHX
 – DSNDB06.SYSRTSTS
This table space used to contain the following tables:
 • SYSIBM.SYSTABLESPACESTATS
 • SYSIBM.SYSINDEXSPACESTATS
DB2 11 defines one table space per each of these tables. The following new table spaces are listed in the order corresponding to the tables:
 • DSNDB06.SYSTSTSS
 • DSNDB06.SYSTSISS
•New job steps have been added for new table spaces and index spaces.
DB2 11 has 16 new table spaces and 17 new index spaces, including the table spaces and associated index spaces that listed previously.
The additional four table spaces are:
 – DSNDB06.SYSTSQRE for the SYSIBM.SYSQUERYPREDICATE table (plus two indexes)
 – DSNDB06.SYSTSQRS for the SYSIBM.SYSQUERYSEL table (plus two indexes)
 – DSNDB06.SYSTSIXS for the SYSIBM.SYSINDEXES table (plus two indexes)
 – DSNDB06.SYSTSSFB for the SYSIBM.SYSSTATFEEDBACK table (plus three indexes)
DSNTIJUZ
The DSNTIJUZ job defines the DB2 data-only DSNZPxxx subsystem parameter module, the application defaults load module, and the data-only DSNHMCID load module.
As with every release, there are multiple changes to this job. Many system parameters were added, some updated and some removed. Refer to the 12.5.1, “New system parameters” on page 346, 12.5.2, “Changed defaults for existing system parameters” on page 354, and 12.5.3, “Removed system parameters” on page 355.
DSNTIJID, DSNTIJIE, and DSNTIJIF
After you define your system data sets and DB2 initialization parameters, you must initialize your system data sets executing these jobs in the shown sequence.
The DSNTIJID job records the active log data set names to the BSDS, formats the active log data sets, and initializes the DB2 directory table spaces and indexes. The BSDS is initialized in the basic, pre-DB2 11 format. After you complete your installation, you can optionally use the DSNTIJCB job to convert the BSDS to the new format that supports 10-byte RBA and LRSN fields.
The DSNTIJIE job initializes the DB2 catalog table spaces and indexes.
The DSNTIJIF job initializes the remaining DB2 catalog table spaces and indexes.
Those jobs are adjusted to the removed and new catalog and directory table spaces and indexes. See “DSNTIJIN” on page 327 for additional information about which objects these are.
DSNTIJSG
During installation, the DSNTIJSG job binds DB2-supplied packages, plans and creates a few objects such as the RLF database and related objects. This job added the creation of the query optimization database in step DSNTIJQ (EXPLAIN tables in schema SYSIBM.)
In DB2 11 is, this job also creates, the program authorization database, table space, table, and index. The SYSIBM.DSNPROGAUTH program authorization table is used to verify that a program is authorized to use a plan. A sample INSERT statement is provided, which you can uncomment, customize, and execute to populate the table. See 10.2, “Enhancements to program authorization” on page 250.
DSNTIJRT
DSNTIJRT creates all DB2-provided routines. It is good practice to run this job in CM and again after DB2 enters NFM in case any packages for DB2-supplied routines need to be refreshed. Some new routines might also be added to DB2 11 and not created until NFM.
DSNTIJIC
This job takes image copies of the DB2 11 catalog and directory.
In addition to adjusting the job to the correct table space names, the SHRLEVEL option has also been changed to SHRLEVEL (CHANGE) for all catalog and directory table spaces. It was SHRLEVEL(REFERENCE) in DB2 10.
12.3.4 New installation job DSNTIJCB
The DSNTIJCB job is an optional job to convert existing BSDSs to the extended format.
 
Important: Do not run this job before you are in NFM. The DB2 subsystem must be stopped in order to run this job.
12.3.5 Miscellaneous
In addition to the changes in the installation process that discussed earlier in this chapter, there are some minor but useful things listed in this section:
How to create a DB2 11 INSTALL member from your DB2 10 member
If you already have one or more DB2 for z/OS subsystems installed on your system, and you need to create one more, it is sometimes convenient to install the new one using the configuration (system parameter) settings of an existing DB2 system. One reason might be that system tests before you actually start migrating an existing subsystem to DB2 11.
To generate customized installation jobs, you have to go through the installation CLIST. The installation CLIST needs an input member containing pre-set values for most of the system parameters. DB2 11 provides an input member with default settings to get started. However you might not want to start with the IBM provided default values, but with values as they are set for one of your subsystems in DB2 10.
One way to get the current values is to manually compare the defaults that DB2 11 provides in member prefix.sdsnsamp(DSNTIDXA) and override those with what you currently have available for DB2 10. This process might not be the best method for the following reasons:
•You might overlook something that can cause issues later.
•The DSNTIDxx member that you used during your DB2 10 installation or migration might be stale, because after the installation or migration you might have changed several system parameters not going through the official update process but through manual changes in the DSNTIJUZ job instead.
You can follow the procedure documented here to convert your stale DB2 10 DSNTIDxx member to a DB2 11 DSNTIDxx member and start the installation from there.
First, remember the DSNTIJXZ job. This job calls the DSNTXAZP program to update a stale DSNTIDxx member with the current DSNZPARM settings. Refer to Figure 12-8. DSNTIJXZ connects to an active DB2 and reads the active system parameter settings. The result is a new DSNTIDyy member, which is up-to-date. The job output on the right gives you a listing with information about how the input DSNTIDxx differs from the actual settings of this subsystem.
Figure 12-8 DSNTIJXC/DSNTXAZP
The following steps look at the process that involves using this DSNTIJXC job. Figure 12-9 assumes that the whole process starts with a stale DSNTIDxx and an intact, existing DB2 subsystem. Start on the top left.
1. Run DSNTIJXZ on DB2 10 to refresh you DB2 10 DSNTIDxx member. Figure 12-9 assumes the output member would be DSNTIDyy.
2. Run the DB2 11 install CLIST in MIGRATE mode and specify:
 – DSNTIDyy as migration input member
 – DB2 11 DSNTIDXA as input member
 – A member, for example DSNTIDzz to receive the changes
3. Discard the customized migration jobs. They are not needed for the task here.
4. Run the DB2 11 install CLIST in INSTALL mode and specify DSNTIDzz as the input member.
You are now ready to use the generated, customized install jobs for the installation of a new DB2 subsystem, which uses system parameter settings like the subsystems that you identified as a good one to get started with.
Figure 12-9 CREATE new DB2 11 DSNTIDxx input from old DB2 10 DSNTIDxx
BIND PLAN with RETAIN option
Many installation and IVP jobs bind plans that are sometimes used during the installation or verification process and can also be used for various tasks by different users later.
Up to DB2 10, the BIND PLAN statements did not include the RETAIN option on the ACTION keyword. The RETAIN option preserves EXECUTE privileges when you replace a plan. If ownership of the plan changes, the new owner has to grant the privileges BIND and EXECUTE to the previous owner. RETAIN is not the default. If you do not specify RETAIN, everyone but the plan owner loses the EXECUTE privilege (but not the BIND privilege).
In DB2 11 the following Installation and verification jobs now specify RETAIN on their BIND PLAN statements:
•IVP jobs
 – DSNTEJ1L
 – DSNTEJ1P
 – DSNTEJ1S
 – DSNTEJ2A
 – DSNTEJ2C
 – DSNTEJ2D
 – DSNTEJ2E
 – DSNTEJ2F
 – DSNTEJ2H
 – DSNTEJ2P
 – DSNTEJ3C
 – DSNTEJ3P
 – DSNTEJ4C
 – DSNTEJ4P
 – DSNTEJ5C
 – DSNTEJ5P
 – DSNTEJ6U
 – DSNTEJ71
 – DSNTEJ73
 – DSNTEJ75
 – DSNTEJ76
 – DSNTEJ77
 – DSNTEJ78
•Installation jobs
 – DSNTIJSG
 – DSNTIJTM
Use of SYNONYMS replaced by ALIASES
In DB2 10, SYNONYMs are deprecated and will not be enhanced starting from DB2 10. Synonyms behave differently with DB2 for z/OS than with the other DB2 family products. Synonyms are not recommended for use when writing new SQL statements or when creating portable applications. Use aliases instead.
Synonyms are no longer used in DB2 11 IVP jobs. The following jobs have changed to use ALIASES instead of SYNONYMs:
•DSNTEJ1
The DSNTEJ1 job creates all objects that are to be used by the sample verification jobs.
•DSNTEJ1U
The DSNTEJ1U job creates a database, table space, and table with Unicode CCSID.
DSNTEJ1U loads data into the table from a data set that contains a full range of characters in an EBCDIC Latin-1 code page, which results in a mix of single and double-byte characters in the Unicode table. It then runs DSNTEP2 to select and display the data in hex format.
•DSNTEJ7
The DSNTEJ7 job demonstrates how to create a LOB table with all the accompanying LOB table spaces, auxiliary tables, and indexes. DSNTEJ7 also demonstrates how to use the DB2 LOAD utility to load a LOB table.
12.4 Considerations for migrating to DB2 11
Migrating a DB2 subsystem means to move from one software version to another. As stated earlier in this chapter, a migration to DB2 11 is possible only if your subsystem is on DB2 10 NFM when you start the migration process. The sequence of jobs shown in Figure 12-10 gives an overview of the migration process, listing changes in the speech bubbles next to the boxes representing individual migration steps.
Figure 12-10 DB2 11 migration process at a glance
The next sections discuss what has changed in terms of the following functions:
12.4.1 Premigration considerations
Before you actually start your DB2 subsystem in conversion mode using the DB2 11 code, plan for the new version. This planning includes completing the activities listed in this section and carefully reviewing the release incompatibilities discussed in 12.6, “Release incompatibilities” on page 357.
Items deprecated in earlier versions are now eliminated
Each DB2 release deprecates items. To deprecate something does not mean that the function does not exist anymore. Instead, no new development effort is spent on these items. Thus, you need to prepare for the removal of the function in a subsequent release. The following items are eliminated in DB2 11:
•Password protection for active log and archive log data sets
•DSNH CLIST NEWFUN values of V8 and V9
•Some DB2 supplied routines:
 – SYSPROC.DSNAEXP
 – AMI-based DB2 MQ functions, see APAR PK37290 for guidance
 – DB2MQ1C.*
 – DB2MQ2C.*
 – DB2MQ1N.*
 – DB2MQ2N.*
•CHARSET application programming default value is removed in DB2 11.
CHARSET was a DSNHDECP parameter used to specify whether the character set associated with the default EBCDIC CCSID was either ALPHANUM or KATAKANA. Prior to DB2 8, this function was needed by DB2 parser for parsing in EBCDIC. Beginning in DB2 V8, parser parses statements in Unicode and no longer needs to know whether the character set is alphanumeric or Katakana.
•BIND PACKAGE options ENABLE and DISABLE (REMOTE) REMOTE (location-name,…,<luname>,…)
In DB2 11, you cannot use the BIND PACKAGE options ENABLE and DISABLE (REMOTE) REMOTE (location-name,…,<luname>,…) to enable or disable specific remote connections. You can use the ENABLE(REMOTE) or DISABLE(REMOTE) options to enable or disable all remote connections.
•Sysplex query parallelism
In DB2 11, sysplex query parallelism is no longer supported. Packages that used sysplex query parallelism in releases before DB2 11 use CPU parallelism in DB2 11.
If you system was allowed to use sysplex query parallelism at all was determined by the setting of system parameter COORDNTR. Because sysplex query parallelism is eliminated from DB2 11, the system parameter is also removed from the DSNZPARM module.
•DSN1CHKR
In DB2 11, the DSN1CHKR utility is no longer needed and therefore not longer supported. You can use DSN1CHKR in versions prior to DB2 10 NFM to scan the specified table space for broken links, broken hash chains, and records that are not part of any link or chain.
Because DB2 10 New Function Mode (NFM), catalog and directory table spaces do not contain hashes or links. Thus, DSN1CHKR is unnecessary.
The DSN1810I and DSN1816I messages are issued when the DSN1CHKR utility is invoked.
Fallback PTF
In the rare case of a severe error while operating under DB2 11 conversion mode, you might need to return to operation on the previous version. This process is called fallback. After fallback, the catalog remains a DB2 11 CM catalog.
DB2 10 by its nature does not support all the changes that occurred to the catalog during the CATMAINT utility execution that made the catalog a DB2 11 CM catalog. This means that a certain maintenance level is required on your DB2 10 code to tolerate these changes.
A fallback PTF plus prerequisite PTFs prepare the DB2 10 code to handle a DB2 11 catalog. DB2 10 must have started at least once with this fallback PTF applied. When you try to start DB2 with the DB2 11 code base for the first time, and your DB2 10 system has never been started with this PTF applied, you receive the error message shown in Example 12-1.
Example 12-1 Missing fallback PTF error message
DSNR045I -DB0B DSNRRPRC DB2 SUBSYSTEM IS STARTING 883
AND
IT WAS NOT STARTED IN A
PREVIOUS RELEASE WITH THE FALLBACK SPE APPLIED.
FALLBACK SPE APAR: PM31841
NEW RELEASE LEVEL: 0000D780
KNOWN LEVEL(S): 0000D6700000D6720000D6750000D680000
 
Important: As you can see from message DSNR45i, the V10/V11 Fallback SPE APAR is PM31841, PTF UK96357 for the fallback SPE. You need to install the PTF to prepare your subsystems for the migration to DB2 11.
Premigration checkout job DSNTIJPM
On DB2 11 target data set prefix.SDSNSAMP, that is, not on the customized NEW.SDSNSAMP, you can find job DSNTIJPM. Run this job on DB2 10 prior to your migration to DB2 11. It queries the DB2 catalog to identify conditions that you need to take into account before you attempt to of after you migrated to DB2 11.
To allow customers maximum time to prepare for migration to a new release, the DSNTIJPM job is also shipped under a different name in the previous release. For example:
•V8 DSNTIJPM is shipped as DSNTIJP8 in V7
•V9 DSNTIJPM is shipped as DSNTIJP9 in V8
•V10 DSNTIJPM is shipped as DSNTIJPA in V8 and V9
•V11 DSNTIJPM is shipped as DSNTIJPB in V10
This arrangement permits customers to begin preparing for migration in advance of buying and SMP/E-installing the new version.
The DSNTIJPB job is added to DB2 10 through maintenance (APAR PM94057) some time before DB2 11 is generally available (GA), which allows you to run these reports as early as possible so that you have enough time to action on the items that might be found in your catalog.
DSNTIJPB uses DB2 REXX Language Support, which is bound by running installation job DSNTIJTM, job step DSNTIRX. If you did not bind the packages and plan for DB2 REXX when you migrated to DB2 10 for z/OS, use the DSNTIJTM job, the DSNTIRX job step, to do so before running DSNTIJPB.
At the time this book was written, this job generates 24 reports, that include the following information:
1. Existence of the previous-release sample database. The objects in this database are needed for the IVP jobs that you are supposed to run in CM.
2. User-defined indexes on the DB2 catalog that reside in user-managed page sets. Because these indexes reside on user-managed storage, you need to modify the ENFM catalog conversion job, DSNTIJEN, to define shadow data sets for them for use by DB2 online REORG. Use the job step descriptions in the prolog of DSNTIJEN to determine the appropriate placement of the AMS DELETE and DEFINE statements for each shadow data set you need to add.
3. User-defined indexes on the DB2 catalog that reside in DB2-controlled page sets. DSNTIJEN is going to handle those indexes automatically, but before running DSNTIJEN, you should review the current space allocations for the data sets for these indexes and increase the space for any that are approaching capacity to accommodate expansion during catalog conversion.
4. Plans last bound prior to DB2 9. These plans are auto-rebound when they are called for the first time in DB2 11 if your setting for system parameter ABIND is set to YES or COEXIST. If COEXIST. If ABIND is set to NO, DB2 V11 returns SQLCODE -908 (SQLSTATE 23510) for all attempts to use any such plan until it is explicitly rebound.
5. Plans last bound prior to DB2 9. Same as 4.
6. EXPLAIN tables, which are not in the expected DB2 10 format. Consider running the DSNTIJXA job for those tables.
 
Attention: The DSNTIJXA job converts all EXPLAIN tables in the system to DB2 10. DB2 10 format explain tables are only allowed encoded in UNICODE. Thus, if the DSNTIJXA job expands the format of any EXPLAIN tables from say DB2 9 format to DB2 10 format, but those are still encoded in EBCDIC, they are unusable after the expansion. You must then use the DSNTIJXB and DSNTIJXC jobs to convert it from EBCDIC to UNICODE!
7. Reserved report.
8. A list of MQTs that are affected by migration job DSNTIJEN. You need to drop these MQTs before starting migration to DB2 V11 ENFM, otherwise CATENFM processing might fail. You can recreate them after you have completed running job DSNTIJEN.
9. AMI IBM MQSeries® functions that were deprecated in V8 and 9 and became obsolete in DB2 10. These are now dropped when you run DSNTIJRT, the job that creates DB2 provided routines and related objects.
10. XML MQSeries functions that were deprecated in V8 and 9 and became obsolete in DB2 10. These are now dropped when you run DSNTIJRT, the job that creates DB2 provided routines and related objects.
11. A list of simple table spaces. Simple table spaces were deprecated with DB2 9. You can still keep them in DB2 11, but you should migrate them to any other table space type, because if you accidently drop a simple table space, you are unable to recreate it as such in DB2 11. The same is true for DB2 10.
12. Trigger packages that have an invalid SECTNOI. These invalid SECTNOI were caused by a bug, which were corrected by PTF UK42129. Drop and re-create those triggers, because they might cause unpredictable results.
13. Views that contain a reference to a temporal table, such as for example:
CREATE VIEW VW1 AS (SELECT * FROM POLICY_INFO
FOR BUSINESS_TIME AS OF '2008-06-15'
WHERE POLICY_ID = 'A123') ;
The use of such views was allowed at DB2 10 GA, but the support was discontinued with APAR PM45015 later. These views should be dropped to avoid migration errors.
14. MQTs with a period specification. See 13 for an explanation
15. SQL functions with a period specification. See 13 for an explanation.
16. Catalog table spaces with version errors. It might occur that the oldest version is larger than the current version. You must correct this problem by running MODIFY RECOVERY, followed by an REORG before you start your catalog migration.
17. A list of packages that reference catalog tables that are stored in one of the following catalog table spaces:
 – SYSCOPY
 – SYSSTR
 – SYSRTSTS
As discussed earlier in this chapter, these table spaces are dropped and replaced by new table spaces in DB2 11. The first time you touch these tables in DB2 11, DB2 executes an automatic rebuild if ABIND is set to YES or COEXIST. If ABIND = NO, then SQLCODE -908 occurs.
18. The SYSIBM.SYSCOPY and SYSIBM.SYSOBDS catalog tables can contain orphaned rows due to a bug that is now fixed. These rows do not cause trouble while you are on DB2 10 but might interfere with the catalog migration. If report 18 returns a few rows, you must run the REPAIR utility as follows for each of the RIDs listed in this report:
REPAIR OBJECT
LOCATE TABLESPACE DSNDB06.SYSALTER RID X'<rid>' DELETE
19. Report orphaned rows in SYSTABSTATS.
20. Report orphaned rows in SYSCOLAUTH.
21. Report inconsistent version numbers in the DB2 catalog.
22. Report plan dependencies on table spaces processed by ENFM.
23. Report package dependencies on table spaces processed by DSNTIJTC.
24. Report plan dependencies on table spaces processed by DSNTIJTC.
12.4.2 DB2 11 CM
Conversion mode is the first mode you enter when you migrate to DB2 11. It is an important step in the migration process, because after you are in DB2 11 CM, all operation is done using the new code base. Refer to DB2 11 for z/OS Installation and Migration, SC19-4056 for a detailed description about all jobs and the sequence to use to execute those to migrate your DB2 subsystems to CM.
This section focuses on the changes in this process from DB2 10 for the following jobs:
Consider using the DB2 10 DSNTIJXZ job to refresh your DB2 10 DSNTIDxx member before you use it as migration input to the DB2 11 installation CLIST. Refer to 12.3.5, “Miscellaneous” on page 329 for information about how to create a member for installing a new DB2 11 based on an existing DB2 10.
DSNTIJMV
The DSNTIJMV migration job is not new in DB2 11. DSNTIJMV first renames existing members containing started task JCL, such as xxxxMSTR, xxxxDBM1, and so on.
In a second step, it updates JCL, which can, for example, contain release dependent data set names for data sets, such as SDSNLOAD and so on. The JCL is generated into the procedure library that you indicated on the data set names panel of the installation CLIST.
Since DB2 10, the installation CLIST also generates the procedure JCL for the core WLM environments which are associated with the many DB2 supplied stored procedures. The library names used in these JCL members might also require changes. For this purpose, the DB2 11 CLIST now also renames the members containing the WLM procedure JCL. This task adds 11 additional RENAME statements to this job.
DSNTIJUZ
DSNTIJUZ creates the DSNZPARM and DSNHDECP load modules. If you compare the DB2 10 version of this job with DB2 11, you find that there are many new and removed system parameters. System parameters are discussed in 12.5.1, “New system parameters” on page 346, 12.5.2, “Changed defaults for existing system parameters” on page 354, and 12.5.3, “Removed system parameters” on page 355.
DSNTIJRT
Execute this job twice when you are migrating from DB2 10 to DB2 11. The first time is when you are in conversion mode and the second time when you are in NFM.
DSNTIJRT creates stored procedure ADMIN_COMMAND_MVS in DB2 11 CM (if it was not already created before migrating from DB2 10). The DB2-supplied stored procedure is described at 9.7, “ADMIN_COMMAND_MVS stored procedure” on page 217.
Conversion mode
Changes that occur when you run this job in CM are:
1. Drop all existing obsolete AMI-based DB2 MQ functions and all DB2 XML MQ routines. You can use the following query if you would like to identify the affected routines:
SELECT SCHEMA
, NAME
, SPECIFICNAME
, ROUTINETYPE
FROM SYSIBM.SYSROUTINES
WHERE SCHEMA IN ( 'DB2MQ1C' , 'DB2MQ2C'
, 'DB2MQ1N' , 'DB2MQ2N'
, 'DMQXML1C', 'DMQXML2C'
)
ORDER BY SCHEMA, SPECIFICNAME;
These routines are also identified in one report after running job DSNTIJPM/B as described in “Premigration checkout job DSNTIJPM” on page 335.
2. Bind all packages from DB2 11 DBRMs.
NFM
You must run this job a second time in NFM. This time, DB2 creates:
•Associated created global temporary table SYSIBM.MVS_CMD_OUTPUT
 
Note: DSNTRIN is a program called by job DSNTIJRT to install and configure DB2-supplied routines. This includes validation and adjustment of various SQL objects that are used by the routines and that have been modified in the service stream or on a product version/release boundary. Current APARs for this program are PM45652 and PM93782.
DSNTIJIC
DSNTIJTC is a sample job that copies DB2 subsystem’s catalog and directory. Starting with DB2 11 CM, this job has seven additional table spaces included in the list of objects to copy.
These following table spaces in ENFM replace three DB2 10 table spaces as follows:
•DSNDB06.SYSTSCPY replaces DSNDB06.SYSCOPY
•DSNDB06.SYSTSCKS, DSNDB06.SYSTSCHX, DSNDB06.SYSTSCKD, and DSNDB06.SYSTSSRG replace DSNDB06.SYSSTR
•DSNDB06.SYSTSISS and DSNDB06.SYSTSTSS replace DSNDB06.SYSRTSTS
These new table spaces are only created in ENFM, but DSNTIJIC is changed starting in CM, so the following message indicates that there is special handling in place for those objects by DB2:
DSNU1530I CSECT-NAME - OBSOLETE OR NFM CATALOG OR DIRECTORY OBJECT object-type DSNDB0n.object-name WILL NOT BE PROCESSED
The same message is issued if you run DSNTIJIC in NFM and the three replaced table spaces have been removed from the catalog.
 
Tip: DSNTIJIC is not changed in NFM. You can remove the job steps to remove those three COPY steps from the job manually.
In case you do not use DSNTIJIC to copy your catalog and directory, make sure to adjust your own copy job accordingly. You might want to refer to DSNTIJIC if you are not sure about the order by which copy the catalog and directory objects.
A second change in DSNTIJIC is that the copies are produced using SHRLEVEL CHANGE instead of SHRLEVEL REFERENCE. You might want to consider changing your own image copy jobs accordingly.
Application compatibility
A new DB2 version typically comes with several changes or enhancements to SQL. These changes are listed in 12.6.1, “Application and SQL release incompatibilities” on page 357.
In the past releases all applications affected by those incompatibilities had to be adjusted to the new behavior of DB2 prior to the migration.
DB2 11 introduces support of SQL application compatibility using a system parameter, special register and bind option. This function is described in 12.7.1, “Example of DB2 10 application compatibility” on page 373.
12.4.3 DB2 11 ENFM and NFM
After you have successfully tested DB2 11 in CM for a reasonable time, you continue the migration to NFM. If you complete the few panels of the installation CLIST in ENFM mode, the CLIST generates the necessary installation and IVP on the library that you specified. Example 12-2 shows the list of generated jobs.
Example 12-2 Install and IVP jobs generated as result of ENFM installation CLIST completion
DSNT478I BEGINNING EDITED DATA SET OUTPUT
DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSNTIJEN)', ENFM PROCESSING
 DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSNTIJNH)', HALT ENFM PROCESSING
DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSNTIJNF)', TURN NEW FUNCTION  MODE ON
DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSNTIJNX)', CREATE NFM-DEPENDENT  OBJECTS
DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSNTIJES)', DISABLE USE OF NEW
 FUNCTION (ENFM*)
DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSNTIJCS)', RETURN FROM ENFM OR
ENFM* TO CM*
DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSNTIJCI)', CHECK INDEXES AFTER
ENFM
DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSNTIJCV)', CONVERT CATALOG AND
DIRECTORY FORMAT
DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSNTESC)', SAMPLE DATA
DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSNTESH)', SAMPLE DATA
DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSNTESD)', SAMPLE DATA
DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSNTESA)', SAMPLE DATA
DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSNTESE)', SAMPLE DATA
DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSNTEJ0)', SAMPLE JCL
DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSNTEJ1)', SAMPLE JCL
 DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSNTEJ1L)', SAMPLE JCL
DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSNTEJ1P)', SAMPLE JCL
DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSNTEJ1S)', SAMPLE JCL
DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSNTEJ1T)', SAMPLE JCL
DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSNTEJ1U)', SAMPLE JCL
DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSNTEJ2A)', SAMPLE JCL
DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSNTEJ2C)', SAMPLE JCL
DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSNTEJ2D)', SAMPLE JCL
DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSNTEJ2E)', SAMPLE JCL
DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSNTEJ2F)', SAMPLE JCL
DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSNTEJ2H)', SAMPLE JCL
DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSNTEJ2P)', SAMPLE JCL
DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSNTEJ2U)', SAMPLE JCL
DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSNTEJ3C)', SAMPLE JCL
DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSNTEJ3P)', SAMPLE JCL
DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSNTEJ3M)', SAMPLE JCL
DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSNTEJ4C)', SAMPLE JCL
DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSNTEJ4P)', SAMPLE JCL
DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSNTEJ5A)', SAMPLE JCL
DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSNTEJ5C)', SAMPLE JCL
DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSNTEJ5P)', SAMPLE JCL
DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSNTEJ6O)', SAMPLE JCL
DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSNTEJ6R)', SAMPLE JCL
 DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSNTEJ6U)', SAMPLE JCL
DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSNTEJ6V)', SAMPLE JCL
DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSNTEJ6W)', SAMPLE JCL
DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSNTEJ6Z)', SAMPLE JCL
DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSN8ES1)', SAMPLE SQL PROCEDURE
DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSN8ES2)', SAMPLE SQL PROCEDURE
DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSNTEJ63)', SAMPLE JCL
DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSNTEJ64)', SAMPLE JCL
DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSNTEJ65)', SAMPLE JCL
DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSNTEJ66)', SAMPLE JCL
DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSNTEJ67)', SAMPLE JCL
DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSNTEJ7)', SAMPLE JCL
DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSNTEJ71)', SAMPLE JCL
DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSNTEJ73)', SAMPLE JCL
DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSNTEJ75)', SAMPLE JCL
DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSNTEJ76)', SAMPLE JCL
DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSNTEJ77)', SAMPLE JCL
DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSNTEJ78)', SAMPLE JCL
DSNT489I CLIST EDITING 'DB0BM.NEW.ENFM.SDSNSAMP(DSNTIJNG)', UPDATE DSNHDECP FOR
ENFM
The number of installation jobs, that is the ones that start with DSNTI, are just a handful. You most likely only run few of them because the majority deals with halting processes or going backwards rather than forward.
Refer to Figure 12-10 on page 333 for an overview of the job steps described here:
DSNTIJEN
DSNTIJEN prepares the DB2 catalog and directory for NFM. This job has been around since DB2 8. However, each version requires different changes to the DB2 catalog and therefore contains different steps and its execution time varies accordingly. After you have first started DSNTIJEN, the status of your DB2 11 subsystem changes from CM to enabling-NFM (ENFM).
After DSNTIJEN has completed, all DB2 11 catalog changes are completed as well. Table 12-4 show the progression of the number of objects of the DB2 catalog and directory across the DB2 versions.
Table 12-4 Number of catalog and directory objects
Version
Table Spaces
Tables
Indexes
Columns
LOB columns
Inline LOB columns
V1
11
25
27
269
0
0
V3
11
43
44
584
0
0
V5
12
54
62
731
0
0
V7
20
84
118
1212
2
0
V8
22
85
132
1265
2
0
V9
28
104
165
1652
6
0
V10
95
134
233
2036
36
4
V11
108
143
250
2202
42
9
The following SELECT statement lists the names of the catalog columns defined as inline LOBs and their table names, as shown in Table 12-5:
SELECT NAME,TBNAME,TBCREATOR,LENGTH FROM SYSIBM.SYSCOLUMNS WHERE TBCREATOR LIKE ‘SYS%’ AND COLTYPE IN (‘BLOB’,‘CLOB’) AND LENGTH > 4;
Table 12-5 Tables having inline LOB columns
COLUMN NAME
TBNAME
TBCREATOR
INLINE LENGTH
SPT_DATA
SPTR
SYSIBM
32146
DESCRIPTOR
SYSCONTROLS
SYSIBM
12004
RULETEXT
SYSCONTROLS
SYSIBM
16004
STATEMENT
SYSPACKSTMT
SYSIBM
15364
STMTBLOB
SYSPACKSTMT
SYSIBM
7172
STMTTEXT
SYSQUERY
SYSIBM
2052
DEFAULTTEXT
SYSVARIABLE
SYSIBM
2004
DESCRIPTOR
SYSVARIABLES
SYSIBM
2004
PARSETREE
SYSVIEWS
SYSIBM
27674
 
Tip: If you are not sure in which mode your DB2 subsystem currently runs, you can use DB2 command -DIS GROUP or -DIS GROUP DETAIL. The command works in data sharing and non-data sharing.
The output of -DIS GROUP to check the mode is shown in Example 12-3.
Example 12-3 -DIS GROUP result from non-data-sharing subsystem
DSN7100I -DB0B DSN7GCMD
*** BEGIN DISPLAY OF GROUP(........) CATALOG LEVEL(111) MODE(CM )
PROTOCOL LEVEL(2) GROUP ATTACH NAME(....)
--------------------------------------------------------------------
DB2 DB2 SYSTEM IRLM
MEMBER ID SUBSYS CMDPREF STATUS LVL NAME SUBSYS IRLMPROC
-------- --- ---- -------- -------- --- -------- ---- --------
........ 0 DB0B -DB0B ACTIVE 111 SC63 ID0B DB0BIRLM
--------------------------------------------------------------------
SPT01 INLINE LENGTH: 32138
*** END DISPLAY OF GROUP(........)
DSN9022I -DB0B DSN7GCMD 'DISPLAY GROUP ' NORMAL COMPLETION
***
In general, job DSNTIJEN performs the following functions:
•Saves the current RBA or LRSN in the BSDS.
•Converts SYSCOPY to a new table space, SYSTSCPY.
•Converts SYSRTSTS to two new table spaces, SYSTSISS and SYSTSTSS.
•Converts SYSSTR to four new table spaces, SYSTSCKS, SYSTSCHX, SYSTSCKD, and SYSTSSRG.
•During REORG, converts all table spaces and indexes that are processed by DSNTIJEN to use the RBA and LRSN format that is specified in the DSN6SPRM.UTILITY_OBJECT_CONVERSION setting.
•Resets and re-initializes the SYSUTILX table space in step ENFM0010. Therefore, utilities should not be run during this step.
•Changes types and lengths of existing catalog columns.
DSNTIJEN consists of the following job steps:
ENFM0000 Terminates pending DSNENFM.* utilities.
ENFM000A Gets a list of table spaces that are in ICOPY and COPY status.
ENFM000B Image copies table spaces that are identified in job step ENFM000A.
ENFM0001 Updates the catalog for the new release.
ENFM0010 Enabling-NFM for SYSUTILX.
ENFM002x Enabling-NFM steps for SYSLGRNX.
ENFM003x Enabling-NFM steps for SYSCOPY.
ENFM004x Enabling-NFM steps for SYSRTSTS.
ENFM005x Enabling-NFM steps for SYSTSIXS.
ENFM006x Enabling-NFM steps for SYSTSTAB.
ENFM007x Enabling-NFM steps for SYSSTR.
ENFM9900 Terminates pending DSNENFM.* utilities
When the DSNTIJEN job ran to migrate the DB2 subsystem to DB2 11 ENFM, the error message shown in Example 12-4 was encountered.
Example 12-4 DSNU2902I error message
DSNU2902I -DB0B 251 04:29:53.96 DSNURMAP - MAPPING DATABASE MAPDB IS INVALID
This is a new error message. The autonomic creation of mapping tables for REORG SHRLEVEL CHANGE is described in 11.1.4, “Automated REORG mapping table management” on page 278.
The message text clearly describes the problem as shown in Example 12-5. This example uses the MAPDB in REORG_MAPPING_DATABASE system parameter but have not created this database in the subsystem. To correct the problem, which caused DSNTIJEN to end with RC 8, the database MAPDB needs to be created.
Example 12-5 DSNU2902I message text
DSNU2902I
csect-name MAPPING DATABASE database-name IS INVALID
 
Explanation
The REORG utility statement has detected that the database that is specified for the REORG TABLESPACE utility MAPPINGDATABASE keyword or the REORG_MAPPING_DATABASE subsystem parameter does not exist or cannot be used to implicitly create a mapping table.
 
csect-name
The name of the control section that issued the message.
database-name
The name of the database.
System action
Utility processing terminates.
 
Administrator response
Specify a valid database name for the REORG TABLESPACE utility MAPPINGDATABASE keyword or specify a valid value for the REORG_MAPPING_DATABASE subsystem parameter.
Severity
8 (error)
Because DSNTIJEN failed, not all table spaces changed during ENFM catalog processing have been adjusted correctly. Example 12-6 shows the results of -DIS GROUP DETAIL DB2 command. Only SYSUTILX has been completed at this point. All other table spaces listed here still need to be processed.
Note that the MODE this is displayed in the second line of the command output is already set to EN, because the example is now at a point of no return.
 
Restriction: When you entered mode EN and left CM, you cannot fallback to DB2 10 NFM anymore!
Example 12-6 -DISPLAY GROUP DETAIL output
DSN7100I -DB0B DSN7GCMD
*** BEGIN DISPLAY OF GROUP(........) CATALOG LEVEL(111) MODE(EN )
PROTOCOL LEVEL(2) GROUP ATTACH NAME(....)
--------------------------------------------------------------------
DB2 DB2 SYSTEM IRLM
MEMBER ID SUBSYS CMDPREF STATUS LVL NAME SUBSYS IRLMPROC
-------- --- ---- -------- -------- --- -------- ---- --------
........ 0 DB0B -DB0B ACTIVE 111 SC63 ID0B DB0BIRLM
--------------------------------------------------------------------
TABLE ENABLED
SPACE NEW FUNCTION
-------- ------------
SYSUTILX YES
SYSLGRNX NO
SYSCOPY NO
SYSRTSTS NO
SYSTSIXS NO
SYSTSTAB NO
SYSSTR NO
--------------------------------------------------------------------
SPT01 INLINE LENGTH: 32138
*** END DISPLAY OF GROUP(........)
DSN9022I -DB0B DSN7GCMD 'DISPLAY GROUP ' NORMAL COMPLETION
***
After creation of database MAPDB, the DSNTIJEN job is run. After completion of DSNTIJEN, the -DISPLAY GROUP DETAIL command output in Example 12-7shows that all page sets, which are touched during CATENFM execution are now ready for NFM.
Example 12-7 -DIS GROUP DETAIL output in ENFM
DSN7100I -DB0B DSN7GCMD
*** BEGIN DISPLAY OF GROUP(........) CATALOG LEVEL(111) MODE(EN )
PROTOCOL LEVEL(2) GROUP ATTACH NAME(....)
--------------------------------------------------------------------
DB2 DB2 SYSTEM IRLM
MEMBER ID SUBSYS CMDPREF STATUS LVL NAME SUBSYS IRLMPROC
-------- --- ---- -------- -------- --- -------- ---- --------
........ 0 DB0B -DB0B ACTIVE 111 SC63 ID0B DB0BIRLM
--------------------------------------------------------------------
TABLE ENABLED
SPACE NEW FUNCTION
-------- ------------
SYSUTILX YES
SYSLGRNX YES
SYSCOPY YES
SYSRTSTS YES
SYSTSIXS YES
SYSTSTAB YES
SYSSTR YES
--------------------------------------------------------------------
SPT01 INLINE LENGTH: 32138
*** END DISPLAY OF GROUP(........)
DSN9022I -DB0B DSN7GCMD 'DISPLAY GROUP ' NORMAL COMPLETION
***
 
Tip: If DSNTIJEN fails, fix the reason for the failure and rerun the job without modifying it at all. Just re-submit it. Steps that were already completed previously are figured out and the job continues with the correct step.
The subsystem in this example is a small sandbox system. The DSNTIJEN job completed in 0.78 minutes elapsed time. Experiences from the early support program show that the elapsed time for the ENFM-NFM migration of other subsystems was also short, that is approximately in the 2 minute interval.
However, the majority of the time is spent reorganizing the following table spaces:
•DSNDB01.SYSLGRNX
•DSNDB06.SYSCOPY
•DSNDB06.SYSRTSTS
•DSNDB06.SYSTSIXS
•DSNDB06.SYSTSTAB
•DSNDB06.SYSSTR
So if you want to determine what the expected elapsed time for your subsystems might be, you can reorg those table spaces and add up the elapsed time.
 
Tip: In order to reduce the elapsed time for CATENFM, make sure that you use MODIFY RECOVERY to clean up obsolete SYSLGRNX and SYSCOPY entries.
DSNTIJNX
The DSNTIJNX job creates installation objects that are dependent on NFM. DSNTIJNX creates a new DSNRLMTxx table (used by the Resource Limit Facility) in the new format plus statements for altering an existing DSNRLMTxx table to the new format after DB2 enters NFM.
DSNTIJNX also rebinds both SPUFI packages and REXX Language Support packages with the bind options supported in DB2 11 NFM:
•ARCHIVESENSITIVE
Determines whether references to archive enabled tables in static SQL statements and dynamic SQL statements are affected by the value of a new SYSIBMADM.GET_ARCHIVE global variable. This global variable indicates whether a reference to an archive enabled table in a table-reference should include rows in the associated archive table.
Refer to 6.2, “Global variables” on page 102 for an explanation about global variables.
•SYSTIMESENSITIVE
Indicates whether references to system-period temporal tables in static and dynamic SQL statements are affected by the value of the CURRENT TEMPORAL SYSTEM_TIME special register. When a system-periods temporal table is referenced and the value in effect for the CURRENT TEMPORAL SYSTEM_TIME special register is not the null value, the following period specification in implicit: FOR SYSTEM_TI;E AS OF CURRENT TEMPORAL SYSTEM_TIME.
•BUSTIMESENSITIVE
Indicates whether references to system-period temporal tables in static and dynamic SQL statements are affected by the value of the CURRENT TEMPORAL BUSINESS_TIME special register. When a system-period temporal table is referenced and the value in effect for the CURRENT TEMPORAL BUSINESS_TIME special register is not the null value, the following period specification is implicit: FOR SYSTEM_TIME AS OF CURRENT TEMPORAL BUSINESS_TIME.
DSNTIJRT
If you are migrating from DB2 10, you should run this job two times. Refer to “DSNTIJRT” on page 338, which describes the steps necessary to get into DB2 11 conversion mode.
When you execute the job, it creates the new stored procedure SYSPROC.ADMIN_COMMAND_MVS (if not already created in DB2 10) and its created global temporary table SYSIBM.MVS_CMD_OUTPUT.
12.5 Subsystem parameters
New DB2 releases typically have new and removed system parameters (DSNZPARMs) as well as changes in their default values. This section describes the following information:
12.5.1 New system parameters
This section describes new system parameters introduced with DB2 11 and gives a brief explanation about their functionality:
APPLCOMPAT
The APPL COMPAT LEVEL field on panel DSNTIP41 specifies the default release level value of the APPLCOMPAT BIND and REBIND option.
Acceptable values are V10R1 and V11R1. The default behavior is V10R1 after migration and V11R1 for a new installation. See “Example of DB2 10 application compatibility” on page 373.
AUTHEXIT_CACHEREFRESH
The AUTHEXIT_CACHEREFRESH system parameter specifies whether entries in the cache package authorization cache, the routine authorization cache, the DDF user authentication cache, and the dynamic statement cache are refreshed or whether the dependent packages are invalidated when access control authorization exit is active and the user profile is changed in RACF. Acceptable values are ALL and NONE, with a default of NONE.
ALL Specifies that DB2 refreshes the cache entries of the package authorization, the routine authorization, and the dynamic statement and invalidates dependent packages when the user profile or resource access is changed in RACF. The cache entries are refreshed only when the access control authorization exit is active.
NONE Specifies that DB2 does not refresh the cache entries of the package authorization, the routine authorization, and the dynamic statement or invalidate dependent packages when the user profile or resource access is changed in RACF. This is the default value for the field.
When the AUTHEXIT_CACHEREFRESH system parameter is set to ALL and the access control authorization exit is active, DB2 listens to type 62, type 71, and type 79 ENF signals from RACF for user profile or resource access changes and refreshes the DB2 cache entries accordingly. If you define RACF classes for DB2 objects and administrative authorities without using IBM-supplied RACF resource classes, you need to enable the SIGNAL=YES option for these classes in the RACF Class Descriptor Table.
AUTHEXIT_CHECK
The AUTHEXIT_CHECK subsystem parameter specifies whether the owner or the primary authorization ID is used for authorization checks when the access control authorization exit (DSNX@XAC) is active.
Acceptable values are PRIMARY and DB2, the default is PRIMARY.
PRIMARY Specifies that DB2 provides the ACEE of the primary authorization ID to perform all authorization checks. The primary authorization ID must be permitted access to the resources in RACF. This is the default value for the field.
DB2 Specifies that DB2 provides the ACEE of the package owner to perform authorization checking when processing the AUTOBIND, BIND, and REBIND commands. DB2 provides the ACEE of the authorization ID as determined by the DYNAMICRULES option to perform dynamic SQL authorization checking. The access control authorization exit uses the ACEE for XAPLUCHK for authorization checking.
The XAPLUCHK authorization ID can be a user or a group in RACF. To ensure successful authorization checks with the owner ACEE, the owner authorization ID in XAPLUCHK must be permitted access to the resources in RACF. If the owner is a group in RACF, you need to permit the group access to the resource associated with the connection in the RACF DSNR class. You can issue the PERMIT command to grant a group access to subsystem.BATCH in the DSNR class, as follows:
PERMIT DSN.BATCH CLASS(DSNR) ID(DB2GRP) ACCESS(READ)
INDEX_CLEANUP_THREADS
INDEX_CLEANUP_THREADS specifies the maximum number of threads that can be created to process the cleanup of pseudo-deleted index entries on this subsystem or data sharing member. Pseudo-deleted entries in an index are entries that are logically deleted but still physically present in the index.
Acceptable values are in the range between 0 and 128, with a default of 10.
This parameter works in conjunction with the SYSIBM.SYSINDEXCLEANUP catalog table, which controls cleanup processing of pseudo-deleted index entries.
The default setting is appropriate for most situations. However, a larger setting might be appropriate in cases where large indexed tables or large numbers of indexed tables are constantly modified. A smaller value might be appropriate in cases where thread count is severely constrained.
When INDEX_CLEANUP_THREADS is set to 0, no cleanup is performed by the subsystem or data sharing member regardless of the entries in the SYSIBM.SYSINDEXCLEANUP catalog table.
Refer to 4.10, “Idle thread break-in” on page 82 for a discussion of index cleanup.
LIKE_BLANK_INSIGNIFICANT
The LIKE BLANK INSIGNIFICANT field specifies whether blanks are significant when applying the LIKE predicate to a string. If set, the blank insignificant behavior applies.
Acceptable values are NO and YES. NO is the default setting, which actually represents the DB2 10 behavior.
NO LIKE treats trailing blanks within fixed-length character strings as significant.
YES When the LIKE predicate is applied against fixed-length character column data, DB2 strips trailing blanks from the data before performing the comparison. If the data is all blank, DB2 reduces it to a single blank before performing the comparison.
Refer to Example 12-8 for a few simple examples of the result DB2 returns based on the setting for this system parameter.
Example 12-8 LIKE_BLANK_INSIGNIFICANT
Given:
CREATE TABLE LIKETEST (C1 CHAR(10));
INSERT INTO LIKETEST VALUES(' AA '),
INSERT INTO LIKETEST VALUES('A AA A'),
 
When LIKE_BLANK_INSIGNIFICANT=NO
 
for:
SELECT * FROM LIKETEST
WHERE C1 LIKE 'AA%'
=> No rows are returned
 
for:
SELECT * FROM LIKETEST
WHERE C1 LIKE '%AA'
=> No rows are returned
 
for:
SELECT * FROM LIKETEST
WHERE C1 LIKE '%AA%'
=> ' AA ' and 'A AA A' are returned
 
 
When LIKE_BLANK_INSIGNIFICANT=YES
 
for:
SELECT * FROM LIKETEST
WHERE C1 LIKE 'AA%'
=> No rows are returned
 
for:
SELECT * FROM LIKETEST
WHERE C1 LIKE '%AA'
=> ' AA ' is returned
 
for:
SELECT * FROM LIKETEST
WHERE C1 LIKE '%AA%'
=> ' AA ' and 'A AA A' are returned
MAXSORT_IN_MEMORY
The MAXSORT_IN_MEMORY subsystem parameter specifies the maximum allocation of storage in kilobytes for a query that contains an ORDER BY clause, a GROUP BY clause, or both. The storage is allocated only during the processing of the query. Increasing the value in this field can improve performance of such queries but might require large amounts of real storage when several such queries run simultaneously.
Acceptable values: 1000 to the value specified in SORT POOL SIZE, whichever is larger
The default value: 1000.
This value is used for the final in-memory work file storage for the final sort. So it can use up to this value. Because the default for the SORT POOL size is 10,000 and if this value is set to 1000, then sort will still only use up to 1 MB for the final in-memory sort work file.
You would need to adjust this value to a larger number if you want more in-memory storage for the final work file. Say if you sets it to 128000 and you still have 10,000 for the SORT POOL SIZE, then sort will only use up to the maximum. Sort only allocates the storage needed for this which can be up to the value defined. So if sort only needs 10 KB, then that is all it will allocate.
OBJECT_CREATE_FORMAT
The OBJECT_CREATE_FORMAT subsystem parameter specifies whether DB2 is to create new table spaces and indexes to use a basic or extended log record format. See 3.1, “Extended RBA and LRSN” on page 24.
PARAMDEG_DPSI
The PARAMDEG_DPSI system parameter specifies the maximum degree of parallelism that you can specify for a parallel group in which a data partitioned secondary index (DPSI) is used to drive parallelism.
A DPSI is a non partitioning index that is physically partitioned according to the partitioning scheme of the table. When you specify a value of greater than 0 for this parameter, you limit the degree of parallelism for DPSIs so that DB2 does not create too many parallel tasks that use virtual storage.
Acceptable values are between 0-254, and DISABLE. The default is 0.
0 Specifies that DB2 uses the value that is specified for the PARAMDEG subsystem parameter, instead of PARAMDEG_DPSI, to control the degree of parallelism when DPSI is used to drive parallelism. This is the default value for the field.
1 Specifies that DB2 creates multiple child tasks but works on one task at a time when DPSI is used to drive parallelism.
2-254 Specifies that DB2 creates multiple child tasks and works concurrently on the tasks that are specified. The number of specified tasks can be larger or smaller than the number of tasks as specified in PARAMDEG. When PARAMDEG is set to 1, the rest of the query does not have any parallelism.
DISABLE Specifies that DB2 does not use DPSI to drive parallelism. Parallelism might still occur for the query if PARAMDEG is greater than 1.
PARAMDEG_UTIL
The PARAMDEG_UTIL subsystem parameter specifies the maximum number of parallel subtasks for some utilities.
PARAMDEG_UTIL affects the following utilities:
•REORG TABLESPACE
•REBUILD INDEX
•CHECK INDEX
•UNLOAD
•LOAD
Acceptable values are positive integers between 0 to 32767, with a default of 0.
0 No additional constraint is placed on the maximum degree of parallelism in a utility.
1 to 32767 Specifies the maximum number of parallel subtasks for all affected utilities.
PCTFREE_UPD
The PCTFREE_UPD subsystem parameter specifies the default value to use for the PCTFREE FOR UPDATE clause of CREATE TABLESPACE or ALTER TABLESPACE statements. It specifies the default amount of free space to reserve on each page for use by subsequent UPDATE operations when data is added to the table by INSERT operations or utilities. This parameter has no effect on table spaces that have fixed length rows.
Acceptable values are AUTO and 0 to 99, with a default of 0. This value is used as the default FOR UPDATE value when no PCTFREE FOR UPDATE clause is specified for a CREATE TABLESPACE or ALTER TABLESPACE statement.
AUTO DB2 uses real-time statistics values to automatically calculate the percentage of free space that is to be used by update operations. This value is equivalent to specifying PCTFREE FOR UPDATE -1 in the CREATE TABLESPACE or ALTER TABLESPACE statement.
0 to 99 DB2 reserves the specified percentage of space for use by update operations.
Without having experience with this function, you would assume that setting a value at page set level is more efficient in most cases than setting a positive integer for the system parameter.
For the use of PCTFREE_UPD, see 13.3, “Reduced need for REORG” on page 390.
PKGREL_COMMIT
The PKGREL_COMMIT subsystem parameter specifies whether, at COMMIT or ROLLBACK, a persistent DB2 thread will release a package that is active on that thread if certain DB2 operations are waiting for exclusive access to that package.
The value in this field is meaningful only for packages that are bound with the RELEASE(DEALLOCATE) bind option.
Acceptable values are YES and NO, with a default setting of YES
•YES
For packages that are bound with the RELEASE(DEALLOCATE) option, the following operations are permitted at COMMIT or ROLLBACK while the package is active and allocated by DB2 for a persistent DB2 thread:
 – BIND REPLACE PACKAGE and REBIND PACKAGE requests, including auto rebind online schema changes for tables and indexes that are statically referenced by the package
 – Online REORG operations that materialize pending definition changes for objects that are statically referenced by the package
•NO
DB2 will not implicitly release an active package at COMMIT or ROLLBACK for a persistent DB2 thread. See 4.9, “Allow BIND, REBIND, and DDL to break-in persistent threads” on page 81.
PREVENT_ALTERTB_LIMITKEY
PREVENT_ALTERTB_LIMITKEY determines whether DB2 disallows altering the limit key by using an ALTER TABLE statement for index-controlled partitioned table spaces. This alter operation places the table space in REORG-pending (REORP) restrictive status, and the data is unavailable until the affected partitions are reorganized. Use PREVENT_ALTERTB_LIMITKEY to avoid this data unavailability.
The values are acceptable:
NO Specifies that you can alter a limit key by using an ALTER TABLE statement for index-controlled partitioned table spaces. This is the default.
YES Specifies that altering a limit key by using an ALTER TABLE statement for index-controlled partitioned table spaces is not permitted. An ALTER TABLE statement must not attempt to alter the limit key for an index-controlled partitioned table.
 
Note: The focus here is on index-controlled partitioned table spaces. Altering an index key of a table-controlled partitioned table space is not this much of an issue, because this is considered a pending change, that is the table space is set to advisory state AREOR instead of restrictive state REORP.
 
Tip: Instead of wanting to make use of this system parameter, you might want to migrate your index-controlled partitioned table spaces to table-controlled partitioning. The easy way to accomplish this is to ALTER the clustering Index, which at the same time is the clustering index, to NOT CLUSTER and ALTER it back to CLUSTER right after this.
REC_FASTREPLICATION
The REC_FASTREPLICATION parameter specifies whether the RECOVER utility uses FlashCopy to recover from a FlashCopy image copy. The following values are acceptable:
NONE The RECOVER utility uses standard I/O to restore a FlashCopy image copy.
PREFERRED The RECOVER utility uses FlashCopy to recover from a FlashCopy image copy if FlashCopy support is available. This is the default.
REQUIRED The RECOVER utility forces the use of FlashCopy to recover from a FlashCopy image copy to ensure that recovery occurs as quickly as possible. This option causes RECOVERY to fail if FlashCopy cannot be used.
 
Note: The last sentence in the description for option REQUIRED does not mean that DB2 fails if you only have sequential image copies that are sufficient for the recovery of your page set. The whole DSNZPARM applies only to FlashCopy image copies.
If you use BACKUP SYSTEM to create system-level backups, note that a recovery from a FlashCopy image copy that uses FlashCopy for the restore can cause BACKUP SYSTEM to fail because bidirectional FlashCopy is not supported.
REORG_DROP_PBG_PARTS
The REORG_DROP_PBG_PARTS subsystem parameter specifies whether the REORG utility removes trailing empty partitions when operating on an entire partition-by-growth table space. An empty trailing partition occurs when the REORG utility moves all data records from a partition into lower numbered partitions.
Acceptable values are DISABLE and ENABLE, with a default setting of DISABLE.
This parameter is meaningful only when the REORG utility is run against an entire PBG table space. It is ignored for a REORG of a non-partition-by-growth table space, for a partition-level REORG of partition-by-growth table spaces, and for a REORG of a hash partition-by-growth table space.
ENABLE Specifies that any trailing empty partitions that are present at the successful completion of the REORG are always removed. LOB table spaces and auxiliary indexes that are associated with these empty partition-by-growth partitions are also removed.
DISABLE Specifies that the number of partition-by-growth partitions at the successful completion of the REORG are always equal or greater than the number of partitions before the REORG utility was run. Even if the REORG is able to relocate all data records into the lowest numbered partitions, trailing empty partition-by-growth partitions are retained.
REORG_MAPPING_DATABASE
The REORG_MAPPING_DATABASE subsystem parameter specifies the default database that REORG TABLESPACE SHRLEVEL CHANGE uses to implicitly create the mapping table.
An acceptable value is a character string of a maximum of 8 bytes length. The default value are 8 blanks, implying an implicitly defined database will be used
When processing a REORG TABLESPACE SHRLEVEL CHANGE request, the REORG utility has the option to create its own mapping table and mapping index, instead of relying on user's input. Specifying this subsystem parameter with a valid database name directs REORG to allocate the mapping table in the database that is specified. By default, REORG uses an implicitly defined database for the mapping table allocation. For details, see 11.1.4, “Automated REORG mapping table management” on page 278.
STATFDBK_SCOPE
The STATFDBK_SCOPE subsystem parameter specifies the scope of the SQL statements that DB2 collects statistics recommendations for in the SYSIBM.SYSSTATFEEDBACK catalog table.
The following are acceptable values:
NONE No statistics recommendations are collected in the catalog table.
DYNAMIC Statistics recommendations are collected in the catalog table for dynamic SQL statements only.
STATIC Statistics recommendations are collected in the catalog table for static SQL statements only.
ALL Statistics recommendations are collected in the catalog table for all SQL Statements. This is the default.
 
Note: Even though all DB2 users can query the SYSIBM.SYSSTATFEEDBACK table, the implemented functionality is primarily meant to be used by certain SQL optimization tools to help you improve access path selection for SQL statements.
TEMPLATE TIME
The TEMPLATE_TIME subsystem parameter specifies the default setting for the TIME option of the TEMPLATE statement.
Acceptable values are UTC and LOCAL, with a default of UTC.
UTC Coordinated Universal Time.
LOCAL Local time at the DB2 database manager.
 
Tip: Set all DB2 data sharing members to the same value.
UTILITY_OBJECT_CONVERSION
The value of the UTILITY_OBJECT_CONVERSION parameter specifies whether DB2 utilities that accept the RBALRSN_CONVERSION option will convert existing table spaces and indexes to 6-byte page format, to a 10-byte page format or prevent conversion of a 10-byte format to a 6-byte page format.
The default behavior normally applies when the utility control statement does not specify the RBALRSN_CONVERSION option. Alternatively, the UTILITY_OBJECT_CONVERSION parameter can also be used to prevent use of RBALRSN_CONVERSION options to convert existing table spaces and indexes to 6-byte page format
For a more detailed description about this system parameter and extended RBAs and LRSNs in general, refer to 3.1, “Extended RBA and LRSN” on page 24.
WFSTGUSE_AGENT_THRESHOLD
The WFSTGUSE_AGENT_THRESHOLD subsystem parameter determines the percentage of available space in the work file database on a DB2 subsystem or data sharing member that can be consumed by a single agent before a warning message is issued.
Refer to 4.4, “Work file database enhancements” on page 65 for the usage and implications of the settings for this system parameter.
WFSTGUSE_SYSTEM_THRESHOLD
The WFSTGUSE_SYSTEM_THRESHOLD subsystem parameter determines the percentage of available space in the work file database on a DB2 subsystem or data sharing member that can be consumed by all agents before a warning message is issued.
Refer to 4.4, “Work file database enhancements” on page 65 if you want to learn more about the usage and implications based on the settings for this system parameter
12.5.2 Changed defaults for existing system parameters
Besides the many new system parameters introduced with DB2 11 for z/OS, there are also a number of changed system parameters. Table 12-6 shows a list of affected system parameters.
Table 12-6 System parameters with changed limits
System parameter
Change
Description
DSMAX
Upper limit increased from 100,000 to 200,000
The maximum number of data sets that can be open at one time.
EDMDBDC
Upper limit increased from 2097152 to 4194304
The minimum size (in KB) of the DBD cache that is to be used by EDM.
EDMSTMTC
Upper limit increased from 1048576 to 4194304
The size (in KB) of the statement cache that is to be used by the EDM.
EDM_SKELETON_POOL
Upper limit increased from 2097152 to 4194304
The minimum size of the EDM skeleton pool in KB.
MAXKEEPD
Upper limit increased from 65535 to 204800
The total number of prepared, dynamic SQL statements that can be saved past a commit point by all threads in the system using KEEPDYNAMIC(YES) bind option.
REORG_LIST_PROCESSING
Default was changed from SERIAL to PARALLEL
The default setting for the PARALLEL option of the DB2 REORG TABLESPACE utility. The PARALLEL option indicates whether REORG TABLESPACE processes all partitions specified in the input LISTDEF statement in a single utility execution (PARALLEL YES) or process each in a separate utility execution (PARALLEL NO).
REORG_PART_SORT_NPSI
Default was changed from NO to AUTO
Specifies the default method of building a non-partitioned secondary index (NPSI) during REORG TABLESPACE PART. This setting will be used when the SORTNPSI keyword is not provided in the utility control statement. The SORTNPSI keyword specifies whether REORG TABLESPACE PART decides to sort all keys of a NPSI and how to make that decision. The setting is ignored for a REORG which is not part-level or a REORG with no NPSIs.
SUBQ_MIDX
Default was changed from DISABLE to ENABLE
Whether to enable or disable multiple index access for queries having subquery predicates.
PREVENT_NEW_IXCTRL_
PART
Default was changed from NO to YES.
Determines whether DB2 disallows the creation of new index-controlled partitioned tables. This subsystem parameter ensures that new partitioned tables use table-controlled partitioning, which is the preferred partitioning method for non-universal table spaces.
12.5.3 Removed system parameters
The category of system parameters described in this section are those that have been removed. The fact that system parameters have been removed does not necessarily mean that the functions that they influenced is taken out of DB2. Rather there is no way any longer to influence the behavior of DB2 with regards to the function of these system parameters.
Table 12-7 lists the removed system parameters.
Table 12-7 Removed system parameters
System parameter
DB2 11 behavior
Description
MVSGP and MVSGP2
 
Names, respectively, a group of MSS volumes to be used for archive 1 log data sets and group of MSS values to be used for archive 2 log data sets. DB2 11 does not recognize these devices.
There were opaque system parameter, residing in DSN6ARVP. They are still there, but ignored if set.
CCORDNTR
NO
Specifies whether this DB2 member can coordinate parallel processing on other members of the group.
ASSIST
NO
Specifies whether this DB2 member can assist a parallelism coordinator with parallel processing. Because there cannot be coordinators anymore, there cannot be any assistants either.
OPTIXIO
ON
OPTIXIO=ON means that DB2 provides stability to I/O costing for queries, with less sensitivity to buffer pool sizes. Use of OPTIXIO=OFF can cause access path selection to be heavily influenced by object size and buffer pool size.
OPTIOWGT
ENABLE
Controls how DB2 balances the I/O cost and CPU estimates when selecting access paths.
OJPERFEH
YES
Specifies whether to enable outer join enhancements.
PTCDIO
OFF
Enables an optimizer fix for inefficient index path for a single-table query.
RETVLCFK
NO
Specifies whether the VARCHAR column is to be retrieved from a padded index.
DISABSL
NO
Specifies whether SQLWARN1 and SQLWARN5 are set for non-scrollable cursors on OPEN and ALLOCATE CURSOR.
SMSDCFL
blank
Specifies the DFSMS data class for indexes
STATCLUS
ENHANCED
Specifies the type of clustering statistics to be collected by the RUNSTATS utility. ENHANCED means that DB2 uses an improved algorithm for collecting statistics in effect with the drawback that it can change many access paths.
SEQCACH
SEQ
Specifies whether to use the sequential mode to read cached data from a 3990 controller. Prefetch reads will always use sequential access.
SEQPRES
YES
Specifies whether DB2 utilities that do a scan of a non partitioning index followed by an update of a subset of the pages in the index allow data to remain in cache longer when reading data.
DISABSCL
NO
Specifies whether SQLWARN1 and SQLWARN5 are set for non-scrollable cursors on OPEN and ALLOCATE CURSOR.
OPTIOPIN
YES
Specifies whether the DB2 optimizer should use an improved costing formula to estimate the cost of index and data access to the inner table of a join.
PGRNGSCR
YES
Specifies whether to enable a DB2 optimizer change that can improve performance of queries that contain one or more of the following predicates:
Timestamp < <host-var or string constant>
Timestamp <= <host-var or string constant>
Timestamp >= <host-var or string constant>
Timestamp > <host-var or string constant>
Timestamp BETWEEN <host-var or string constant> AND <host-var or string constant>
 
Note: All the system parameters that are listed in Table 12-7 were already defined as deprecated in DB2 10.
12.5.4 Deprecated system parameters
The only subsystem parameter deprecated in DB2 11 is PRIVATE_PROTOCOL.
12.6 Release incompatibilities
This section describes the following types of incompatibilities:
12.6.1 Application and SQL release incompatibilities
There are IBM and industry standards for SQL that DB2 for z/OS must be compliant with. DB2 for z/OS might be out of compliance because of a defect or an incomplete implementation. When detected and prioritized accordingly, try to fix those compliance issues with one of the upcoming new product releases. The fix might lead to an incompatible change. If the applications are not adjusted to the changed behavior before going to the new release, this might break existing applications after the release migration. These changes, which might lead to incompatibilities are saved up and introduced only on DB2 release boundary and not with maintenance stream within one major release.
Change to determination of ASUTIME for dynamic statements
In DB2 11 NFM with application compatibility set to V11R1, the dynamic SQL ASUTIME limit for each routine is used by the resource limit facility. The ASUTIME limit that is specified for the routine determines the limit. If the dynamic SQL statements in a routine use more ASUTIME than the limit, then SQLCODE -905 is returned. This SQLCODE occurs even if the value is lower than the ASUTIME limit of a top-level calling package. The ASUTIME limit that is specified for the top-level calling package is not considered. In previous versions of DB2, SQLCODE -905 is issued only when the limit of the top-level calling package is encountered.
The possible impact to your DB2 environment might be that because the limit is enforced for each monitored routine, your applications might return more SQLCODE -905 errors.
While in conversion mode with application compatibility for your package set to value V10R1, run your applications with IFCID 0366 or IFCID 0376 enabled. Then, review the trace output for incompatible changes with the identifier 1103. Review and, if necessary, adjust the ASUTIME limits on routines and packages that use dynamic SQL.
Automatic rebind of plans and packages created before DB2 Version 9
Plans and packages that were last bound before Version 9 are not supported in DB2 11 CM and later.
•Possible impact to your DB2 environment:
If you specify YES or COEXIST for the ABIND subsystem parameter, DB2 11 automatically rebinds plans and packages that were bound before Version 9. As a result, an execution delay might occur the first time that such a plan or package is loaded. Also, the automatic rebind might change the access path to a potentially more efficient access path.
If you specify NO for the ABIND subsystem parameter, negative SQLCODEs are returned for each attempt to run a package or plan that was bound before Version 9. SQLCODE -908, SQLSTATE 23510 is returned for packages, and SQLCODE -923, SQLSTATE 57015 is returned for plans until they are rebound in DB2 11.
•Actions to take:
To identify plans and packages that were bound before Version 9, run the DB2 11 premigration job DSNTIJPM on your DB2 10 catalog. If the job output reports some packages, you might want to rebind while still in DB2 10 to prevent those automatic rebinds to occur.
Invalidated plans and packages
During the enabling-NFM processing, plans and packages that reference the DB2 catalog and directory table spaces which are changed by CATENFM, become invalidated.
Refer to “DSNTIJEN” on page 341 for the list of table spaces which are modified during CATENFM:
The packages that are dependent on the following catalog tables are also invalidated:
•SYSIBM.SYSCOPY
•SYSIBM.SYSCHECKS
•SYSIBM.SYSCHECKS2
•SYSIBM.SYSCHECKDEP
•SYSIBM.SYSSTRINGS
•SYSIBM.SYSINDEXSPACESTATS
•SYSIBM.SYSTABLESPACESTATS
Actions to take:
•For SYSLGRNX, existing CHAR(6) columns were changed to CHAR(10). You might need to modify your application before it can run successfully.
•For SYSUTILX, the RBA fields were moved to new fields.
•The SYSCOPY table space was replaced by a new table space, SYSTSCPY.
•The SYSRTSTS table space was replaced by two new table spaces, SYSTSTSS and SYSTSISS. SYSTSTSS contains the SYSIBM.SYSTABLESPACESTATS catalog table and SYSTSISS contains the SYSIBM.SYSINDEXSPACESTATS table.
•The SYSSTR table space was replaced by the following table spaces:
 – SYSTSCKS, which contains SYSIBM.SYSCHECKS
 – SYSTSCHX, which contains SYSIBM.SYSCHECKS2
 – SYSTSCKD, which contains SYSIBM.SYSCHECKDEP
 – SYSTSSRG, which contains the SYSIBM.SYSSTRINGS catalog table
 
Important: For all the new table spaces mentioned previously, you must make sure that you adjust your procedures, such as COPY of the DB2 catalog and directory, as well RECOVER of catalog and directory. When you adjust your procedures, you must make sure that you remember that there is a specific order, which is required for copying and recovering your catalog and directory.
Refer to the description of the RECOVER utility in DB2 11 for z/OS Utility Guide and Reference, SC19-4067 for more information.
Also, make sure that you carefully thing about any other processes or products, which can rely on the old page set names and adjust those accordingly.
Default for ODBC limited block fetch
The default for the LIMITEDBLOCKFETCH initialization keyword changed.
Limited block fetch guarantees the transfer of a minimum amount of data in response to each request from the requesting system.
With limited block fetch, a single conversation is used to transfer messages and data between the requester and server for multiple cursors. Processing at the requester and server is synchronous. The requester sends a request to the server, which causes the server to send a response back to the requester. The server must then wait for another request to tell it what should be done next.
In DB2 10, ODBC limited block fetch was disabled by default. In DB2 11 NFM, ODBC limited block fetch is enabled by default.
The possible impact to your DB2 environment is that your applications might use limited block fetch, when they did not do so previously.
 
Tip: If the default is not appropriate for your ODBC applications, you can change it by modifying the value of the LIMITEDBLOCKFETCH initialization keyword.
Views, MQTs, and SQL table functions with period specifications
In DB2 11, views, materialized query tables, and SQL table functions that were created with period specifications in DB2 10 are not supported. Period specifications refer to either system-time or application-time (formerly known as system-time) temporal tables. If such views, materialized query tables, or SQL functions are used in DB2 11, incorrect results might occur.
 
Note: In DB2 11, you can still create a view to reference a System Period Temporal Table (STT), but a temporal predicate (For System_Time....) is not allowed in the view definition. The following option is allowed:
CREATE VIEW V_STT1 AS SELECT * from STT1
The following option not allowed:
CREATE VIEW V_STT1_NO AS SELECT * FROM STT1 FOR SYSTEM_TIME AS OF 2013-09-26 15:25:00.0
The temporal predicate is disallowed in a view definition to avoid nesting of temporal predicates, such as:
SELECT * FROM V_STT1_NO FOR SYSTEM TIME AS OF 2012-09-27 09:00:00.0.
In this case, there is no clearly defined semantic whether to use the temporal predicate from the outmost SELECT or the inner most SELECT.
To prepare for this change, drop all views, materialized query tables, and SQL table functions that contain a SYSTEM_TIME or BUSINESS_TIME period specification.
To identify such existing views, materialized query tables, and SQL table functions, run the DB2 11 premigration job DSNTIJPM on your DB2 10 catalog. You can also manually issue the following queries.
•To identify views and materialized query tables that were created with a period specification, issue the following query:
SELECT * FROM SYSIBM.SYSVIEWDEP WHERE BTYPE IN ('W', 'Z') AND DTYPE IN ('V', 'M'),
•To identify SQL table functions that were created with a period specification, issue the following query:
SELECT * FROM SYSIBM.SYSDEPENDENCIES WHERE BTYPE = 'Z';
•To identify SQL scalar functions that were created with a period specification or period clause, issue the following query:
SELECT * FROM SYSIBM.SYSPACKDEP WHERE BTYPE IN ('W', 'Z') AND DTYPE = 'N';
Dropping columns named CLONE, ORGANIZATION, or VERSIONING
In DB2 11 NFM, a column that is named CLONE, ORGANIZATION, or VERSIONING should be specified as a delimited identifier to be dropped from a table.
Prior to DB2 11, CLONE, ORGANIZATION, and VERSIONING are reserved keywords that can appear after the DROP keyword in an ALTER TABLE statement. When CLONE, ORGANIZATION, or VERSIONING is specified as a simple token (that is, not as a delimited identifier), these keywords can only match the DROP CLONE, DROP ORGANIZATION, or DROP VERSIONING clauses on an ALTER TABLE statement.
If you intend to drop a column named CLONE, ORGANIZATION, or VERSIONING in DB2 11, and the name is specified as a simple token on the ALTER TABLE statement, the DB2 subsystem might interpret the ALTER TABLE statement as specifying the DROP CLONE, DROP ORGANIZATION, or DROP VERSIONING clauses instead of the DROP COLUMN clause.
To drop a column named CLONE, ORGANIZATION, or VERSIONING in DB2 11, the name must be specified as a delimited identifier (for example, DROP "ORGANIZATION" or DROP "CLONE", assuming is the delimiter for a delimited identifier.
Alternatively, you can specify the optional COLUMN keyword in the DROP COLUMN clause, for example DROP COLUMN ORGANIZATION or DROP COLUMN CLONE.
See Table 12-8 for a summary of DROP COLUMN for CLONE.
Table 12-8 DROP example for CLONE
Prerequisite status
SQL statement
Result
CLONE relationship exists
ALTER TABLE ... DROP CLONE
RC 0, Clone table dropped
CLONE relationship exists
ALTER TABLE ... DROP “CLONE”
SQLCODE -148, reason-code 11, which means: The ALTER statement attempted to change a table that has a defined clone, or a table that is a clone.
CLONE relationship exists
ALTER TABLE ... DROP COLUMN CLONE
SQLCODE +610, because this is a schema change which leads to AREOR, that is it is a pending change.
NO CLONE relationship
ALTER TABLE ... DROP CLONE
SQLCODE -650, reason-code 20, which means: ALTER TABLE DROP CLONE cannot be used to drop a clone when the table does not have a defined clone.
NO CLONE relationship
ALTER TABLE ... DROP “CLONE”
SQLCODE -104, because DROP COLUMN needs keyword RESTRICT
NO CLONE relationship
ALTER TABLE .. DROP “CLONE” RESTRICT
SQLCODE +610, because this is a schema change which leads to AREOR, that is it is a pending change.
NO CLONE relationship
ALTER TABLE ... DROP COLUMN CLONE
SQLCODE +610, because this is a schema change which leads to AREOR, that is it is a pending change.
Allow XPath processing to continue with error on filtered results
In DB2 11 NFM with application compatibility set to V11R1, XPath processing might return fewer errors on predicate expressions with an explicit cast or an operation with an invalid value.
In previous versions of DB2, even though the invalid result is filtered from the result set, XPath processing returns an error SQLCODE. In DB2 11, examples of XPath expressions that have fewer errors include situations when:
•Data is filtered from the result by the predicate before an invalid operation such as division of a number by zero
•Data is explicitly cast to an incompatible data type
 
Tip: While in conversion mode with application compatibility for your package set to value V10R1, run your applications with IFCID 0366 or IFCID 0376 enabled. Then, review the trace output for incompatible changes with the identifier 1102.
Here is an example, with the definition of two books. See Example 12-9.
Example 12-9 Books definition
<books>
<book><title>XQuery 3.0</title>
<publishDate>soon</publishDate>
<price>40.00</price>
<edition>kindle</edition>
</book>
<book><title>XQuery 3.0</title>
<publishDate>2013-09-30</publishDate>
<price>50.00</price>
<edition>paper</edition>
</book>
</books>
Here is the query:
XMLQUERY('/books/book[title = "XQuery 3.0"][xs:date(publishDate) > "2013-10-01"][edition="paper"]' passing xmlcol)
You can see that xs:date(publishDate) would be an error for the first book because “soon” cannot be cast to date. However, it depends on the order the predicates are evaluated. If [edition="paper"] is evaluated first, the first book would be filtered out before the cast on date. If xs:date(publishDate) > "2013-10-01" is evaluated first, then the error shows up.
DB2 11 defers the error reporting until the last predicate is evaluated. Thus, the error is not reported. Due to the flexibility of XML, we try to provide more usability and fewer errors.
XML document node implicitly added on insert and update
In DB2 11 NFM with application compatibility set to V11R1, if an XML document does not have a document node, then one is added during insert and update operations.
In previous versions of DB2, document nodes are not implicitly added and an SQL insert or update of an XML document returned SQLCODE -20345. To avoid the error, an application needs to invoke the XMLDOCUMENT function before the insert or update.
In DB2 11, an XML document node is added, if one does not exist in the XML document.
The result is, that your applications might return fewer errors on insert and update operations.
 
Tip: While in conversion mode with application compatibility for your package set to value V10R1, run your applications with IFCID 0366 or IFCID 0376 enabled. Then, review the trace output for incompatible changes with the identifier 1101. In addition, you can review your applications for use of the XMLDOCUMENT function.
Here is an example:
select xmlelement(element "test", 1) from sysibm.sysdummy1;
returns
<?xml version="1.0"><test>1</test>
insert into T1(xmlcol) values ('<?xml version="1.0"><test>1</test>'),
works fine.
However, if you use XMLELEMENT directly in the insert as shown in the following example, you get -20345 on DB2 10.
insert into T1(xmlcol) values (xmlelement(element "test", 1));
The reason is that in DB2 that XMLELEMENT generates an XML element node but it does not generate a document node which is an invisible root of an XML tree. Insert requires an document node. Thus, you get -20345.
The reason that insert with XML string works is because the XMLPARSE function implicitly generates an XML document node. With DB2 10, you have to inject an XMLDOCUMENT function as shown in the following example to make the insert work.
insert into T1(xmlcol) values (XMLDOCUMENT(xmlelement(element "test", 1)));
On DB2 11, you do not have to do that. The original insert would work.
Client information results from ADMIN_COMMAND_DB2
Starting in DB2 11 CM, the ADMIN_COMMAND_DB2 result set row in the created global temporary table SYSIBM.DB2_THREAD_STATUS when processing-type = "THD" has changed. The column data type and maximum lengths for WORKSTATION, USERID, APPLICATION, and ACCOUNTING have changed.
In DB2 11 the following column data types and lengths change:
•WORKSTATION increases from CHAR(18) to VARCHAR(255).
•USERID increases from CHAR(16) to VARCHAR(128).
•APPLICATION increases from CHAR(32) to VARCHAR(255).
•ACCOUNTING increases from CHAR(247) to VARCHAR(255).
Your applications now receive a VARCHAR data type and possibly a different length client information value. The length is no longer padded to the supported maximum length.
In DB2 11, the stored procedure SYSPROC.ADMIN_COMMAND_DB2 also allows users to specify PROCESSING_TYPE (formerly PARSE_TYPE) LOB tables spaces (LS), XML table spaces (XS) and unknown spaces (UN) to retrieve information about table spaces when issuing the command -DISPLAY DATABASE. Based on the three output messages by type from ADMIN_COMMAND_DB2, you can generate COPY utility jobs to create image copies for the table spaces.
You should review your applications for use of the ADMIN_COMMAND_DB2 stored procedure.
Altering limit keys blocks immediate definition changes
In DB2 11 NFM, if you alter a limit key for certain table space types, you cannot make any immediate definition changes until the limit key changes are materialized.
In previous versions of DB2, altering a limit key was an immediate definition change. In DB2 11, altering a limit key for one of the following types of partitioned table spaces is now a pending definition change:
•Range-partitioned universal table spaces
•Table spaces that are partitioned (non-universal) with table-controlled partitioning
As in DB2 10, you cannot make immediate definition changes before pending definition changes are materialized.
 
Restriction: Some immediate alter operations that worked in previous versions of DB2 might fail in DB2 11 with SQLCODE -20385 reason code 1 or 2.
The new way for altering limit keys is described in detail in 4.3, “Improved availability when altering limit keys” on page 61.
Removing the SYSPUBLIC schema from the SQL PATH routine option
Starting in DB2 11 conversion mode, SYSPUBLIC is the schema that is used for public aliases. As such, the SQL PATH routine option must not specify the SYSPUBLIC schema.
In previous versions of DB2, you could not define functions, procedures, distinct types, and sequences in the SYSPUBLIC schema, but you were not restricted from specifying SYSPUBLIC as part of the SQL PATH. If you had specified SYSPUBLIC as part of the SQL PATH, it had no effect on their applications. With DB2 11 you will no longer be able to specify SYSPUBLIC as part of the SQL PATH.
Creation or resolution of some objects that worked in previous versions of DB2 might fail in DB2 11 with SQLCODE -713 if SYSPUBLIC is specified as part of the SQL PATH.
Query the catalog to see if any object schemas use SYSPUBLIC as the schema qualifier. This is highly unlikely for any object, but most likely with objects that use the SQL PATH (functions, procedures, distinct types, and sequences).
Change any existing SET PATH statements to not specify SYSPUBLIC as a schema.
SYSIBMADM schema added to the SQL path
In DB2 11 NFM with application compatibility set to V11R1, SYSIBMADM is added to the SQL path as an implicit schema.
If SYSIBMADM is not specified as an explicit schema in the SQL path, it is included as an implicit schema at the beginning of the path after SYSIBM, SYSFUN, and SYSPROC.
Applications that reference the content of the CURRENT PATH special register now have the SYSIBMADM schema returned when implicit schemas are included in the path. For example, the statement SELECT CURRENT PATH FROM SYSIBM.SYSDUMMY1 now returns “SYSIBM”, ”SYSFUN”, ”SYSPROC”, ”SYSIBMADM”, ”authid”, where authid is the authorization ID of the statement, instead of “SYSIBM”, ”SYSFUN”, ”SYSPROC”, ”authid.”
Change in result for CAST(string AS TIMESTAMP)
In DB2 11 NFM with application compatibility set to V11R1, the result of CAST(string AS TIMESTAMP) is changed in some cases.
Previously, when DB2 executed CAST(string AS TIMESTAMP), DB2 interpreted an 8-byte string as a Store Clock value and a 13-byte string as a GENERATE_UNIQUE value. This interpretation might result in an incorrect result from the CAST specification. Starting with DB2 11, with the application compatibility set to V11R1, when an 8-byte string or a 13-byte string is input to CAST(string AS TIMESTAMP), DB2 interprets the input strings as string representations of TIMESTAMP values.
An invalid representation of an 8-byte or 13-byte string in CAST(string AS TIMESTAMP) results in SQLCODE -180.
Suppose that you execute the SQL statements in DB2 11 NFM listed in Example 12-10 and Example 12-11 which show the DB2 10 and the DB2 11 behavior.
The examples should help you understand the issue.
Example 12-10 sets APPLCOMPAT special register to V10R1 to simulate the DB2 10 behavior. The casting character string 01/01/2013 to TIMESTAMP, which is supposed to represent January 1st, 2013, results in a completely different timestamp, dated 2034.
In the second part of Example 12-10, you see that if you provide the first 8 bytes of the store clock value, that is X'CAB5060708090100', which represents January 1st, 2013, casting returns the expected date.
Example 12-10 CAST as TIMESTAMP with APPLCOMPAT set to V10R1
SELECT CAST('1/1/2013' AS TIMESTAMP) FROM SYSIBM.SYSDUMMY1;
---------+---------+---------+---------+---------+---------+
---------+---------+---------+---------+---------+---------+
2034-07-25-16.43.41.599503
 
SELECT CAST(X'CAB5060708090100' AS TIMESTAMP) FROM SYSIBM.SYSDUMMY1;
---------+---------+---------+---------+---------+---------+--------
---------+---------+---------+---------+---------+---------+--------
2013-01-01-20.37.04.246928
Example 12-11 uses the exact same SQL statements but sets the APPLCOMPAT special register to V11R1. The first SELECT statement now returns what you in fact might have expected, that is the date of January 1st, 2013.
The second SELECT statement fails now, because in DB2 11, DB2 interprets the input strings as string representations of TIMESTAMP values, which X'CAB5060708090100' clearly not is.
Example 12-11 CAST as TIMESTAMP with APPLCOMPAT set to V11R1
SELECT CAST('1/1/2013' AS TIMESTAMP) FROM SYSIBM.SYSDUMMY1;
---------+---------+---------+---------+---------+---------
---------+---------+---------+---------+---------+---------
2013-01-01-00.00.00.000000
 
 
SELECT CAST(X'CAB5060708090100' AS TIMESTAMP) FROM SYSIBM.SYSDUMMY1; 00
---------+---------+---------+---------+---------+---------+---------+----
---------+---------+---------+---------+---------+---------+---------+----
DSNE610I NUMBER OF ROWS DISPLAYED IS 0
DSNT408I SQLCODE = -180, ERROR: THE DATE, TIME, OR TIMESTAMP VALUE *N IS
INVALID
 
Tip: While in DB2 11 conversion mode, or in DB2 11 NFM with application compatibility set to V10R1, identify applications with this incompatibility by starting a trace for IFCID 0366 or IFCID 0376, and then running the applications. Review the trace output for incompatible changes with the identifier 1109. If you need to convert Store Clock values to the TIMESTAMP data type, use the TIMESTAMP built-in function instead of CAST(string AS TIMESTAMP).
Example 12-12 lists an example.
Example 12-12 Invoke scalar function TIMESTAMP with store clock value
SELECT TIMESTAMP(X'CAB5060708090100') FROM SYSIBM.SYSDUMMY1;
---------+---------+---------+---------+---------+---------+-
---------+---------+---------+---------+---------+---------+-
2013-01-01-20.37.04.246928
New maximum lengths for values that are returned for some built-in functions
In DB2 11 NFM with application compatibility set to V11R1, the maximum lengths for values that are returned for the SPACE and VARCHAR built-in functions are decreased from 32767 to 32764.
If the length of the output string for any of these functions is greater than 32764 bytes, SQLCODE -171 is returned.
Review your applications for use of these functions, and, if necessary, modify the function input so that the result does not exceed 32764 bytes. While in conversion mode with application compatibility for your package set to value V10R1, run your applications with IFCID 0366 or IFCID 0376 enabled. Then, review the trace output for incompatible changes with the identifier 1110 or 1111.
Timestamp string representations
DB2 11 NFM with application compatibility set to V11R1 strictly enforces valid string representations of timestamp values.
DB2 11 behavior with application compatibility set to V11R1 is equivalent to DB2 10 with subsystem parameter BIF_COMPATIBILITY = CURRENT. With application compatibility set to V10R1, the enforcement of valid string representations depends on the BIF_COMPATIBILITY value.
Review your setting of the BIF_COMPATIBILITY subsystem parameter. If the value is not CURRENT, while in conversion mode with application compatibility for your package set to value V10R1, run your applications with IFCID 0366 or IFCID 0376. Then, review the trace output with the function identifier 3 to identify SQL with unsupported time stamp values. Make appropriate changes to your SQL.
12.6.2 Utility release incompatibilities
As for some other areas, utilities also have some incompatible changes, which are described in this section.
Parallelism change to the REBUILD INDEX utility
In DB2 11 conversion mode, the degree of parallelism can increase for the REBUILD INDEX utility.
The REBUILD INDEX utility previously limited the degree of parallelism to 18 subtasks. Now, because of the PARALLEL option value or the PARAMDEG_UTIL subsystem parameter value, the amount of parallelism might increase.
 
Attention: Increasing the degree of parallelism could constrain your system resources.
Refer to “PARAMDEG_UTIL” on page 350 if you want to read more about PARAMDEG_UTIL system parameter.
Changes to REORG default values
In DB2 11 conversion mode, the following changes are made to the default values for the REORG utilities:
The following changes are made to the default values for the REORG TABLESPACE utility:
•The default DRAIN value is changed from WRITERS to ALL.
•The NOPAD keyword is now the default value in the UNLOAD EXTERNAL clause and the DISCARD clause.
For the REORG INDEX utility the default for the DRAIN value is also changed from WRITERS to ALL.
Change to DSNU126I return code when running REORG on an LOB table space
DB2 10 deprecated the use of REORG TABLESPACE SHRLEVEL NONE for LOB table spaces. When you nevertheless used it, a REORG SHRLEVEL REFERENCE was performed and the DSNU126I message, which indicates that SHRLEVEL NONE is no longer supported, was associated with RC 0. If you try to use REORG LOG SHRLEVEL NONE, the job fails with DSNU126I and return code 8.
In preparation for this change, review your REORG job outputs for instances of DSNU126I while you are still running in DB2 10.
Changes to RECOVER utility
The TOLOGPOINT, TORBA, and RESTOREBEFORE keywords can accept basic 6-byte format or extended 10-byte format based on the length of the RBA or LRSN value that is specified. Previously, any length was accepted and then extended or truncated as required.
Operands of 6 bytes or less are interpreted as being in basic 6-byte format. Operands greater than 6 bytes are interpreted as being in ended 10-byte format. In both cases, padding on the left with X'00' occurs to form complete 6-byte or 10-byte operands. Conversion between basic and extended format is performed as required for the recovery operation.
Changes to DSNACCOX stored procedure default values
In DB2 11 NFM, changes are made to the defaults of the DSNACCOX stored procedure.
RRTDataSpaceRat input parameter default value is -1. Previously, it was 2.0.
RRTDataSpaceRat is the ratio of the space allocated to the actual space used. Specifies a criterion for recommending that the REORG utility is to be run on table space for space reclamation. If the following condition is true, DSNACCOX recommends running REORG:
•The object is not using hash organization.
•The SPACE allocated is greater than RRTDataSpaceRat multiplied by the actual space used. (SPACE > RRTDataSpaceRat × (DATASIZE/1024))
 
Tip: Review your calls to the DSNACCOX stored procedure. Look for NULL as the value of RRTDataSpaceRat. The new default turns off this criterion. Any positive values continue to be processed as in DB2 10.
Changes to DSNACCOX stored procedure result set
In DB2 11 NFM, ’XS’ for XML table spaces and ’LS’ for LOB table spaces are now possible values of the OBJECTTYPE column results.
Review your processing of results from the DSNACCOX stored procedure. Unexpected values might be handled as invalid by applications processing these result sets.
Changes to DSNACCOX processing for REORG and COPY recommendations
In DB2 11 NFM, more information is evaluated when REORG or COPY is recommended.
When the input parameter QueryType specifies REORG or COPY recommendations, DSNACCOX also checks the database exception table (DBET) entry for an exception state.
Review your processing of results from the DSNACCOX stored procedure. Unexpected values might be handled as invalid. Database exception table (DBET) states are added to the OBJECTSTATUS column of the result set.
Changes to DSNACCOX stored procedure processing for ChkLvl 8
In DB2 11 NFM, a new row is not inserted if the result set already has a recommendation for a utility operation.
When the input parameter ChkLvl specifies level 8 processing, DSNACCOX adds the utility operation recommendation to an existing row if one exists for the object. If an existing row does not exist, DSNACCOX continues to add a row.
ChkLvl 8 means: Check for objects that have restricted states. The value of the QueryType option must be ALL or contain RESTRICTED when this value is specified. The OBJECTSTATUS column of the result set indicates the restricted state of the object. A row is added to the result set for each object that has a restricted state.
Review your processing of results from the DSNACCOX stored procedure. Unexpected values might be handled as invalid. New rows for objects are only added to the result set if the object is not already present.
Differences in materializing limit key changes
In DB2 11 NFM, you can no longer materialize limit key changes for certain types of table spaces by using REORG TABLESPACE SHRLEVEL NONE or LOAD REPLACE.
Instead, this alter is a pending definition change, and the data remains accessible before the limit key changes are materialized. However, you cannot use the REORG TABLESPACE utility with the SHRLEVEL NONE option or the LOAD utility with the REPLACE option to materialize these changes. (SHRLEVEL NONE is the default value for REORG TABLESPACE. If you do not specify the SHRLEVEL option for REORG TABLESPACE, SHRLEVEL NONE is in effect.)
To learn more about the new behavior for limit key changes, you should refer to 4.3, “Improved availability when altering limit keys” on page 61.
12.6.3 Command release incompatibilities
In terms of commands, there are just a few incompatible changes that you should be aware of. They are listed within this section.
Change to DISPLAY UTILITY output
The output for the DISPLAY UTILITY command now includes the date and the time when the job was submitted.
 
Tip: Determine if any of your applications parse output of the DISPLAY UTILITY command and update the applications if needed.
Removing the SYSPUBLIC schema from the PATH bind option
Starting in DB2 11 conversion mode, SYSPUBLIC is the schema that is used for public aliases. As such, the PATH bind option must not specify the SYSPUBLIC schema.
In previous versions of DB2, you were not restricted from specifying SYSPUBLIC as part of the PATH bind option. With DB2 11 you will no longer be able to specify SYSPUBLIC as part of the PATH bind option.
Creation or resolution of some objects that worked in previous versions of DB2 might fail in DB2 11 with SQLCODE -713 if SYSPUBLIC is specified as part of the PATH bind option.
Query the catalog to see if any object schemas use SYSPUBLIC as the schema qualifier. This is highly unlikely for any object, but most likely with objects that use the PATH (functions, procedures, and sequences).
Change any existing PATH bind option to not specify SYSPUBLIC as a schema.
 
Note: PUBLIC ALIASES can only be defined for SEQUENCEs. This functionality does not apply to tables.
12.6.4 Storage release incompatibilities
When you migrate to DB2 11, be aware of the storage release incompatibilities.
There is a new minimum that your z/OS application programmers have to set for HVSHARE.
In DB2 11, the required amount of contiguous 64-bit shared private storage for each DB2 subsystem is 1 TB. In previous releases, the minimum requirement was 128 GB.
 
Restriction: If you do not have an adequate amount of contiguous 64-bit shared private storage, DB2 11 will not start.
12.6.5 Functions that are deprecated
During migration, be aware of the functions that are deprecated in DB2 11. Although they are supported in DB2 11, support for these functions might be removed in the future. Avoid creating new dependencies that rely on these functions, and if you have existing dependencies on them, develop plans to remove these dependencies.
The following functions are deprecated in DB2 11.
NEWFUN SQL processing options and DECP values
The SQL processing options NEWFUN(YES) and NEWFUN(NO) are deprecated, and the NEWFUN(V11) option is added in DB2 11. Use NEWFUN(V11) instead of NEWFUN(YES). Use NEWFUN(V10) instead of NEWFUN(NO). The NEWFUN(V8) and NEWFUN(V9) values are supported in DB2 11, but they cause the precompilation process to support only a Version 8 or Version 9 level of function.
The DSNHDECP parameter values NEWFUN=YES and NEWFUN=NO are also deprecated. Although these values are supported in DB2 11, you should use NEWFUN=V11 instead of NEWFUN=YES and use NEWFUN=V10 instead of NEWFUN=NO.
 
Note: You can only use NEWFUN(V8) or NEWFUN(V9) as a precompiler option. It is not allowed as DSNHDECP parameter option.
Some utility options
The following DB2 utility options are deprecated. Although they are supported in DB2 11, they will be removed in a later release of DB2.
•REORG TABLESPACE UNLOAD ONLY
Use the UNLOAD utility instead.
•REORG TABLESPACE UNLOAD PAUSE
Use the UNLOAD FORMAT INTERNAL utility instead.
•REORG TABLESPACE UNLOAD EXTERNAL
Use the UNLOAD utility instead.
•REORG TABLESPACE INDREFLIMIT
Use the DSNACCOX stored procedure to determine if the object needs to be reorganized.
•REORG TABLESPACE OFFPOSLIMIT
Use the SYSPROC.DSNACCOX stored procedure to determine if the object needs to be reorganized.
•REORG TABLESPACE INDREFLIMIT REPORTONLY and REORG TABLESPACE OFFPOSLIMIT REPORTONLY
REPORTONLY is valid only when the INDREFLIMIT or OFFPOSLIMIT option is specified, and these options are deprecated.
•REORG INDEX UNLOAD ONLY
Use the DIAGNOSE utility stop the process instead.
•REORG INDEX UNLOAD PAUSE
Use the DIAGNOSE utility stop the process instead.
•REORG INDEX LEAFDISTLIMIT
Use the DSNACCOX stored procedure to determine if the object needs to be reorganized.
•REORG INDEX LEAFDISTLIMIT REPORTONLY
REPORTONLY is valid only when the LEAFDISTLIMIT option is specified, and this option is deprecated.
•LOAD FORMAT UNLOAD
This is what you used when you generated the SYSREC using REORG TABLESPACE UNLOAD ONLY. A few steps back indicated that this deprecated in DB2 11 and that you should use UNLOAD FORMAT INTERNAL if you want to generate the same type of date.
Use the LOAD FORMAT INTERNAL option to load data that was unloaded with UNLOAD FORMAT INTERNAL.
•COPY CHANGELIMIT
Use the DSNACCOX stored procedure to determine if the object needs to be copied.
•REPAIR VERSIONS
Use the REPAIR CATALOG utility instead.
12.6.6 Functions that are no longer supported
If you are migrating to DB2 11 from DB2 10, be aware of the functions that are no longer supported.
Password protection for active log and archive log data sets
As of DB2 11, password protection for active log and archive log data sets is no longer supported.
Previous NEWFUN values
As of DB2 11, the DSNH CLIST no longer supports values of NEWFUN=V8 or NEWFUN=V9.
Some DB2-supplied routines
The following DB2-supplied routines are removed in DB2 11 and are unavailable to callers after migration to conversion mode. A report is added to the DSNTIJPM premigration job to detect occurrences of these routines on an existing subsystem or data sharing group, and to specify that these routines are not available in DB2 11.
•SYSPROC.DSNAEXP
•AMI-based DB2 MQ functions1
 – DB2MQ1C.GETCOL
 – DB2MQ1C.MQPUBLISH
 – DB2MQ1C.MQREAD
 – DB2MQ1C.MQREADALL
 – DB2MQ1C.MQREADALLCLOB
 – DB2MQ1C.MQREADCLOB
 – DB2MQ1C.MQRECEIVE
 – DB2MQ1C.MQRECEIVEALL
 – DB2MQ1C.MQRECEIVEALLCLOB
 – DB2MQ1C.MQRECEIVECLOB
 – DB2MQ1C.MQSEND
 – DB2MQ1C.MQSUBSCRIBE
 – DB2MQ1C.MQUNSUBSCRIBE
 – DB2MQ2C.GETCOL
 – DB2MQ2C.MQPUBLISH
 – DB2MQ2C.MQREAD
 – DB2MQ2C.MQREADALL
 – DB2MQ2C.MQREADALLCLOB
 – DB2MQ2C.MQREADCLOB
 – DB2MQ2C.MQRECEIVE
 – DB2MQ2C.MQRECEIVEALL
 – DB2MQ2C.MQRECEIVEALLCLOB
 – DB2MQ2C.MQRECEIVECLOB
 – DB2MQ2C.MQSEND
 – DB2MQ2C.MQSUBSCRIBE
 – DB2MQ2C.MQUNSUBSCRIBE
 – DB2MQ1N.GETCOL
 – DB2MQ1N.MQPUBLISH
 – DB2MQ1N.MQREAD
 – DB2MQ1N.MQREADALL
 – DB2MQ1N.MQREADALLCLOB
 – DB2MQ1N.MQREADCLOB
 – DB2MQ1N.MQRECEIVE
 – DB2MQ1N.MQRECEIVEALL
 – DB2MQ1N.MQRECEIVEALLCLOB
 – DB2MQ1N.MQRECEIVECLOB
 – DB2MQ1N.MQSEND
 – DB2MQ1N.MQSUBSCRIBE
 – DB2MQ1N.MQUNSUBSCRIBE
 – DB2MQ2N.GETCOL
 – DB2MQ2N.MQPUBLISH
 – DB2MQ2N.MQREAD
 – DB2MQ2N.MQREADALL
 – DB2MQ2N.MQREADALLCLOB
 – DB2MQ2N.MQREADCLOB
 – DB2MQ2N.MQRECEIVE
 – DB2MQ2N.MQRECEIVEALL
 – DB2MQ2N.MQRECEIVEALLCLOB
 – DB2MQ2N.MQRECEIVECLOB
 – DB2MQ2N.MQSEND
 – DB2MQ2N.MQSUBSCRIBE
 – DB2MQ2N.MQUNSUBSCRIBE
An application programming default value
The following application programming default value is removed in DB2 11:
CHARSET
ENABLE and DISABLE (REMOTE) REMOTE (location-name,…,<luname>,…)
In DB2 11, you cannot use the BIND PACKAGE options ENABLE and DISABLE (REMOTE) REMOTE (location-name,…,<luname>,…) to enable or disable specific remote connections. You can use the ENABLE(REMOTE) or DISABLE(REMOTE) options to enable or disable all remote connections.
Sysplex query parallelism
In DB2 11, Sysplex query parallelism is no longer supported. Packages that used Sysplex query parallelism in releases before DB2 11 use CPU parallelism in DB2 11.
DSN1CHKR utility
In DB2 11, the DSN1CHKR utility is no longer supported. The DSN1810I and DSN1816I messages are issued when the DSN1CHKR utility is invoked.
12.7 Controlling application compatibility
Requirements coming from SQL standard compliance and completion of support for new functions produce changes that might impact the compatibility of existing applications.
We look at a pervasive example of incompatibility in DB2 10 and provide an overview of the application compatibility support in DB2 11.
12.7.1 Example of DB2 10 application compatibility
One example for an incompatible change in DB2 10 was the changed results of a CHAR built-in scalar function. V9 result for CHAR was not consistent with the result for VARCHAR and CAST of decimal data types.
The problem that was raised for those functions was that leading zeroes were no longer returned when there is a decimal point. Though the functions were now working as designed to conform to SQL standards, this is an incompatible change if the applications rely on the leading zeros.
Example 12-13 shows the result of the implicit casting of a decimal value using the CHAR built-in scalar function in DB2 9. Notice that the leading zeros are included in the result in column DEC2CHAR but not in DECVARCHAR.
Example 12-13 V9 result of implicit cast of decimal using CHAR function
SELECT CHAR ( DECIMAL(00123.45,7,2) ) AS DEC2CHAR
, VARCHAR ( DECIMAL(00123.45,7,2) ) AS DEC2VARCHAR
FROM SYSIBM.SYSDUMMY1 ;
---------+---------+---------+---------+---------+---------+---
DEC2CHAR DEC2VARCHAR
---------+---------+---------+---------+---------+---------+---
00123.45 123.45
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
The incompatible change in DB2 10 is shown in Example 12-14. The same SELECT in DB2 10 shows that the result of CHAR is consistent with what VARCHAR returns.
Example 12-14 V10 result of implicit cast of decimal using CHAR function
SELECT CHAR ( DECIMAL(00123.45,7,2) ) AS DEC2CHAR
, VARCHAR ( DECIMAL(00123.45,7,2) ) AS DEC2VARCHAR
FROM SYSIBM.SYSDUMMY1 ;
---------+---------+---------+---------+---------+---------+-----
DEC2CHAR DEC2VARCHAR
---------+---------+---------+---------+---------+---------+-----
123.45 123.45
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
The explicit CAST of a 00123.45 decimal value to CHAR or VARCHAR always returned the 123.45 character string.
The change to be consistent in DB2 10 caused some applications to be incompatible. DB2 10 introduced a BIF_COMPATIBILITY system parameter. If the BIF_COMPATIBILITY subsystem parameter is set to V9_DECIMAL_VARCHAR, it reverts the result to how it looked before migrating to DB2 10. Another way to bring back the DB2 9 behavior was at the SYSCOMPAT_V9 package-level setting to beginning of PATH BIND option or in CURRENT PATH.
Because at some point you should have adjusted your applications to the SQL Standard, IFCID 366 was also introduced. You can use IFCID 366 to report applications that use the build-in scalar function CHAR with a decimal value. The trace record is written out once per thread for a particular SQL statement. Thus, this trace record can help identify which applications need to be changed to support the new behavior.
 
Note: If an index expression is created with the CHAR BIF in the index key, the trace is written during the execution of the INSERT SQL statement that inserts into the index. Likewise, for a materialized query table, the trace record is written on REFRESH TABLE.
Start the trace by using the following DB2 command:
-START TRACE(P) CLASS(32) IFCID(366)
A detailed description about which information you can get from turning on the tracing of IFCID 366 is provided later in this section, after a description of the enhancements that are introduced for similar incompatibility situation in DB2 11.
12.7.2 Overview of application compatibility in DB2 11
Sometimes incompatible changes cannot be avoided when SQL functionality is changed in a new DB2 release. DB2 11 helps you decide when your applications are ready for new SQL functionality.
You can influence the availability of new SQL functions after you are in NFM in the following ways:
•A new system parameter providing the default BIND option
New system parameter APPLCOMPAT is introduced to support the concept of V10 Application Compatibility. Acceptable values are V10R1 and V11R1. When you migrate a DB2 subsystem, the setting defaults to V10R1. When you install a new DB2 subsystem, it defaults to V11R1.
V10R1 The default BIND option is V10R1 compatibility behavior.
V11R1 The default BIND option is Vd11R1 compatibility behavior. This value is allowed in only NFM.
 
Attention: Even if you are still in DB2 11 compatibility mode, the assembly of your DSNZPARM works fine if you set the value to V11R1, which you are supposed to use only after you are in NFM.
Also, activating the changed DSNZPARM by using the -SET SYSPARM command or by restarting your DB2 subsystem works fine. You will not see any indication that the system parameter is set to a “wrong” setting in the sense that you cannot use the V11R1 while in CM.
If you try to BIND a package without specifying anything for APPLCOMPAT on the BIND statement, DB2 uses whatever is set in APPLCOMPAT system parameter and this would be V11R1 in this scenario. As a consequence, the BIND fails with the following error message:
DSNT225I -DB0B BIND ERROR FOR PACKAGE DB0B.DSNESPCS.DSNESM68 APPLCOMPAT(V11R1) OPTION IS NOT SUPPORTED
DSNT233I -DB0B UNSUCCESSFUL BIND FOR PACKAGE = DB0B.DSNESPCS.DSNESM68.(UK92200)
•BIND/REBIND options for packages
The APPLCOMPAT BIND option specifies the package compatibility level behavior for static SQL. The acceptable values and meanings are the same as described for the system parameter.
Use Table 12-9 to determine defaults that apply if you do not specify the APPLCOMPAT keyword on the BIND or REBIND statements.
Table 12-9 APPLCOMPAT defaults for BIND
Process
Default value
BIND PLAN
N/A
BIND PACKAGE
The value of subsystem parameter APPLCOMPAT
REBIND PLAN
N/A
REBIND PACKAGE
Existing value. If there is no existing value, the APPLCOMPAT subsystem parameter is used.
REBIND TRIGGER PACKAGE
Existing value. If there is no existing value, the APPLCOMPAT subsystem parameter is used.
•Special Register for Dynamic SQL (CURRENT APPLICATION COMPATIBILITY)
The CURRENT APPLICATION COMPATIBILITY specifies the compatibility level support for dynamic SQL.
The data type is VARCHAR(10).
The initial value of CURRENT APPLICATION COMPATIBILITY is determined by the value of the APPLCOMPAT bind parameter for the package. The initial value of CURRENT APPLICATION COMPATIBILITY in a user-defined function or stored procedure is inherited from the value of bind option APPLCOMPAT for the user-defined function or stored procedure package
Set the value with the SET APPLICATION COMPATIBILITY statement.
When your DB2 environment is migrated to NFM you can run applications with the features and behavior of either previous versions or the current version. For static SQL, the behavior is determined by application compatibility value of a package. For dynamic SQL, the behavior is determined by the APPLICATION COMPATIBILITY special register. If no application compatibility value is set, then the default value is determined by the APPLCOMPAT subsystem parameter. The default APPLCOMPAT value for a new installation is set to the current DB2 version. The default APPLCOMPAT value for a migrated environment is set to the previous DB2 version.
 
Attention: If you get an error testing DB2 11 DML in NFM, double check the setting of CURRENT APPLICATION COMPATIBILITY for dynamic SQL, and APPLCOMPAT bind option for static SQL.
APPLCOMPAT = V10R1
When you set the application compatibility value to V10R1, applications that attempt to use functions and features that are introduced in DB2 11 or later might behave differently or receive an error.
When your DB2 11 environment is migrated to NFM, you can run individual applications with some of the features and behavior of DB2 10. If application compatibility is set to V10R1 and you attempt to use the new functions of a later version, SQL might behave differently or result in a negative SQLCODE, such as SQLCODE -4743.
A migrated DB2 11 environment in conversion mode can have only applications that are bound with V10R1 application compatibility. This behavior ensures that fallback to a previous version of DB2 is successful.
Table 12-10 shows many of the features and functions that are controlled by application compatibility, and the results if you specify V10R1.
You might want to ignore the IFCID information in the third column for now. The contents of the IFCID records are discussed later.
Table 12-10 Behavior of V10R1 application compatibility
Feature or function
Result with V10R1 application compatibility
IFCID 0366 or IFCID 0376 trace function code
Specification of bind option DBPROTOCOL(DRDACBF)
DSNT298I
 
A period specification that follows the name of a view in the FROM clause of a query
SQLCODE -4743
 
A period clause that follows the name of a target view in an UPDATE or DELETE statement
SQLCODE -4743
 
A SET CURRENT TEMPORAL SYSTEM_TIME statement
SQLCODE -4743
 
A SET CURRENT TEMPORAL BUSINESS_TIME statement
SQLCODE -4743
 
A SET SYSIBMADM.MOVE_TO_ARCHIVE or SET SYSIBMADM.GET_ARCHIVE global variable assignment statement
SQLCODE -4743
 
Use of array operations and built-in functions such as:
•Use of the UNNEST collection-derived-table
•Use of the ARRAY_FIRST, ARRAY_LAST, ARRAY_NEXT, ARRAY_PRIOR, ARRAY_AGG, TRIM_ARRAY, CARDINALITY, MAX_CARDINALITY built-in functions
A SET assignment-statement of an array element as a target table
A CAST specification with a parameter marker as the source and an array as the data type
SQLCODE -4743
 
 
An aggregate function that contains the keyword DISTINCT and references a column that is defined with a column mask
SQLCODE -20478
 
A reference to an alias for a sequence object or a public alias for a sequence object
SQLCODE -4743
 
Invocation of the SPACE or VARCHAR built-in function when the result is defined as VARCHAR(32765), VARCHAR(32766), or VARCHAR(32767)
No error
1110, 1111
A SELECT with a table function reference that includes a typed correlation clause
SQLCODE -4743
 
An implicit insert or update of an XML document node
SQLCODE -20345
 
1101
A predicate expression with an explicit cast or an operation with an invalid value that does not affect the results of XPath processing
SQLCODE -20345
1102
A CALL statement that specifies an autonomous procedure
SQLCODE -4743
 
The lengths of values that are returned from CURRENT CLIENT_USERID, CURRENT CLIENT_WRKSTNNAME, CURRENT CLIENT_APPLNAME, or CURRENT CLIENT_ACCTNG special register are longer than the DB2 10 limits.
The special register values are truncated to the DB2 10 maximum lengths and padded with blanks
1104, 1105, 1106, 1107
How the resource limit facility uses ASUTIME value for nested routines
SQLCODE -905 is issued only when the ASUTIME limit of the top-level calling package is encountered.
1103
A CAST(string as TIMESTAMP) specification with an input string of length of 8 or an input string of length 13
An explicit cast specification from string as TIMESTAMP interprets an 8-byte character string as a Store Clock value and a 13-byte string as a GENERATE_UNIQUE value. CAST result might be incorrect.
1109
 
Attention: APPLCOMPAT(V10R1) is assumed for all static SQL packages bound prior to and in DB2 10.
 
Important: Static SQL packages, which were last bound prior to V9 are invalidated in conversion mode and go through automatic rebind the first time they are called. If you would like to prevent those automatic rebinds, you can:
•SET system parameter ABIND to NO. If you do this, you must remember that the program would not be able to execute successfully, because it remains invalidated.
•Rebind affected packages while you are still in DB2 10 NFM.
The list of affected packages is one of the reports generated by pre-migration job DSNTIJPM/B as explained in “Premigration checkout job DSNTIJPM” on page 335.
Valid time frame for APPLCOMPAT (V10R1)
As described, APPLCOMPAT(V10R1) is valid in all modes of DB2 11. Which setting is valid in which mode is also summarized in
Figure 12-11 V11 modes and APPLCOMPAT(V10R1)
Even though you are just now starting with DB2 11, looking ahead into DB2 11+1, you might ask yourself if V10R1 is still a valid option there. Figure 12-12, shows what you can expect! In DB2 V11 + 1, you are allowed to still stick with APPLCOMPAT(V10R1) in all modes. In addition to that all modes also support APPLCOMPAT (V11R1). Just the new VnnR1 setting is only allowed once you are in DB2 11+1 NFM.
Prior to migrating to DB2 11+1, if you run the pre-migration job DSNTIJPx, you can expect to see warnings for all packages, which are at that time bound with APPLCOMPAT(V10R1) and APPLCOMPAT(VnnR1).
Figure 12-12 V11+1 modes and APPLCOMPAT(V10R1)
Looking ahead to DB2 11 + 2, DSNTIJPx pre-migration job acts as listed here:
•Warnings for packages bound with APPLCOMPAT for DB2 11 + 1
•Warnings for packages bound with APPLCOMPAT for DB2 11
•Errors for packages bound with APPLCOMPAT(V10R1)
 – Packages set as Inoperative
 – No AUTOBIND allowed
 – SQL must be changed to be valid for DB2 11 or DB2 11 +1 or +2
How to find applications that use incompatible SQL statements?
After this extensive description of the Application Compatibility feature in DB2 11, and after you learned that at the second release past DB2 11 you are no longer allowed to use V10R1, you might ask yourself what IBM does for you to help identify the applications, for which you need to take action changing the used SQL to make them compatible with any subsequent release.
The answer is instrumentation!
IFCID 366 has already been introduced with DB2 10, which at that time primarily was intended to help identify applications which use implicit casting of decimal data using the CHAR function. IFCID 366 reports on packages affected in both modes and dynamic SQL
DB2 11 introduces a second IFCID, IFCID 376. IFCID 376 is a roll up of 366. DB2 writes One record for each unique static or dynamic statement
If you want to collect this type of information, you must turn on the tracing those IFCIDs.
Example 12-15 shows the description of the information that you can gain from tracing IFCID 366. Also refer to the description of field QW0366FN. Different finding are categorized in different values. The numbers listed in there also corresponding to the trace function codes listed in Table 12-10 on page 376.
Example 12-15 IFCID 366 record description
0366 QW0366 ________________IFCID 0366______________________________
0366 QW0366 IFCID 0366 RECORDS INFORMATION THAT CAN BE USED TO
0366 QW0366 IDENTIFY APPLICATIONS THAT ARE AFFECTED BY INCOMPATIBLE
0366 QW0366 CHANGE
0366 QW0366
0366 QW0366 THIS TRACE RECORD MIGHT CONTAIN INFORMATION ABOUT MULTIPLE
0366 QW0366 INSTANCES OF AN SQL STATEMENT. FOR EXAMPLE, WHEN THE SAME
0366 QW0366 DYNAMIC STATEMENT IS EXECUTED BY SEVERAL THREADS, OR
0366 QW0366 MULTIPLE TIMES BY THE SAME THREAD, MULTIPLE RECORDS ARE
0366 QW0366 WRITTEN.
0366 QW0366
0366 QW0366 THIS RECORD IS FOR SERVICEABILITY ONLY.
0366 QW0366 ---------------------------------------------------------
0366 QW0366FN THIS FIELD CAN HAVE THE FOLLOWING VALUES:
0366 QW0366FN 1: THE DB2 9 FOR Z/OS VERSION OF
0366 QW0366FN SYSIBM.CHAR(DECIMAL-EXPR) WAS EXECUTED.
0366 QW0366FN 2: THE DB2 9 FOR Z/OS VERSION OF
0366 QW0366FN SYSIBM.VARCHAR(DECIMAL-EXPR),
0366 QW0366FN CAST (DECIMAL AS VARCHAR), OR
0366 QW0366FN CAST (DECIMAL AS CHAR) WAS EXECUTED.
0366 QW0366FN 3: AN UNSUPPORTED CHARACTER STRING REPRESENTATION
0366 QW0366FN OF A TIMESTAMP WAS USED.
0366 QW0366FN 4: THE DB2 10 FOR Z/OS DEFAULT SQL PATH WAS USED,
0366 QW0366FN INSTEAD OF THE V11 PATH, WHICH HAS MORE IMPLICIT
0366 QW0366FN SCHEMAS.
0366 QW0366FN 1101: AN INSERT STATEMENT THAT INSERTS INTO AN XML COLUMN
0366 QW0366FN WITHOUT THE XMLDOCUMENT FUNCTION WAS EXECUTED,
0366 QW0366FN WHICH GENERATES SQLCODE -20345 ON A DB2 RELEASE
0366 QW0366FN PRIOR TO V11, BUT DOES NOT GENERATE AN ERROR
0366 QW0366FN STARTING IN V11.
0366 QW0366FN 1102: V10 XPATH EVALUATION BEHAVIOR WAS IN EFFECT, WHICH
0366 QW0366FN RESULTED IN AN ERROR. FOR EXAMPLE, A DATA TYPE
0366 QW0366FN CONVERSION ERROR OCCURRED FOR A PREDICATE THAT
0366 QW0366FN WOULD OTHERWISE BE EVALUATED TO FALSE. STARTING IN
0366 QW0366FN V11, SUCH ERRORS MIGHT ARE SUPPRESSED.
0366 QW0366FN 1103: A DYNAMIC SQL STATEMENT USES THE ASUTIME LIMIT THAT
0366 QW0366FN WAS SET FOR THE ENTIRE THREAD FOR RLF REACTIVE
0366 QW0366FN GOVERNING. FOR EXAMPLE, WHEN A DYNAMIC SQL STATEMENT
0366 QW0366FN IS PROCESSED FROM PACKAGE A, IF THE ASUTIME LIMIT
0366 QW0366FN WAS ALREADY SET DURING OTHER DYNAMIC SQL PROCESSING
0366 QW0366FN FROM PACKAGE B IN THE SAME THREAD, THE SQL FROM
0366 QW0366FN PACKAGE A USES THE ASUTIME LIMIT THAT WAS SET DURING
0366 QW0366FN THE SQL PROCESSING FROM PACKAGE B. STARTING WITH V11,
0366 QW0366FN DYNAMIC SQL FROM MULTIPLE PACKAGES USES THE ASUTIME
0366 QW0366FN LIMIT THAT IS SET IN THEIR OWN PACKAGE INFORMATION.
0366 QW0366FN 1104: THE CLIENT_USERID SPECIAL REGISTER WAS SET TO A
0366 QW0366FN VALUE THAT IS LONGER THAN THE SUPPORTED LENGTH
0366 QW0366FN PRIOR TO V11. THE VALUE WAS TRUNCATED.
0366 QW0366FN 1105: THE CLIENT_WRKSTNNAME SPECIAL REGISTER WAS SET TO
0366 QW0366FN A VALUE THAT IS LONGER THAN THE SUPPORTED LENGTH
0366 QW0366FN PRIOR TO V11. THE VALUE WAS TRUNCATED.
0366 QW0366FN 1106: THE CLIENT_APPLNAME SPECIAL REGISTER WAS SET TO
0366 QW0366FN A VALUE THAT IS LONGER THAN THE SUPPORTED LENGTH
0366 QW0366FN PRIOR TO V11. THE VALUE WAS TRUNCATED.
0366 QW0366FN 1107: THE CLIENT_ACCTNG SPECIAL REGISTER WAS SET TO
0366 QW0366FN A VALUE THAT IS LONGER THAN THE SUPPORTED LENGTH
0366 QW0366FN PRIOR TO V11. THE VALUE WAS TRUNCATED.
0366 QW0366FN 1108: THE CLIENT_USERID, CLIENT_WRKSTNNAME,
0366 QW0366FN CLIENT_APPLNAME, OR CLIENT_ACCTG SPECIAL REGISTER
0366 QW0366FN WAS SET TO A VALUE THAT IS LONGER THAN THE
0366 QW0366FN SUPPORTED LENGTH PRIOR TO V11. THE TRUNCATED VALUE
0366 QW0366FN WAS USED FOR A RESOURCE LIMIT FACILITY SEARCH.
0366 QW0366FN 1109: CAST(STRING AS TIMESTAMP) WAS EXECUTED WITH ONE
0366 QW0366FN OF THE FOLLOWING TYPES OF INPUT STRINGS:
0366 QW0366FN - A STRING OF LENGTH 8, WHICH DB2 TREATED AS A
0366 QW0366FN STORE CLOCK VALUE.
0366 QW0366FN - A STRING OF LENGTH 13, WHICH DB2 TREATED AS A
0366 QW0366FN GENERATE_UNIQUE VALUE.
0366 QW0366FN PRIOR TO V11, THIS BEHAVIOR IS INVALID FOR A CAST.
0366 QW0366FN IT IS VALID FOR THE TIMESTAMP BUILT-IN FUNCTION
0366 QW0366FN ONLY. STARTING IN V11, INPUT TO CAST IS NOT
0366 QW0366FN TREATED AS A STORE CLOCK VALUE OR A
0366 QW0366FN GENERATE_UNIQUE VALUE.
0366 QW0366FN 1110: THE VALUE OF THE ARGUMENT OF THE SPACE BUILT-IN
0366 QW0366FN FUNCTION WAS GREATER THAN 32764.
0366 QW0366FN 1111: THE VALUE OF THE OPTIONAL INTEGER ARGUMENT OF THE
0366 QW0366FN VARCHAR BUILT-IN FUNCTION WAS GREATER THAN 32764.
0366 QW0366SN STATEMENT NUMBER FOR THE QUERY.
0366 QW0366PL PLAN NAME FOR THE QUERY.
0366 QW0366TS TIMESTAMP FOR THE QUERY.
0366 QW0366SI STATEMENT IDENTIFIER.
0366 QW0366TY STATEMENT INFORMATION:
0366 QW0366DY X'8000': STATEMENT IS DYNAMIC.
0366 QW0366SC X'4000': STATEMENT IS STATIC.
0366 QW0366PC_OFF OFFSET FROM QW0366 TO QW0366PC_LEN.
0366 QW0366PN_OFF OFFSET FROM QW0366 TO QW0366PN_LEN.
0366 QW0366VL DS VERSION LENGTH.
0366 QW0366VN DS VERSION.
0366 QW0366PC_LEN LENGTH OF THE FOLLOWING FIELD.
0366 QW0366PC_VAR %U PACKAGE COLLECTION ID.
0366 QW0366PN_LEN LENGTH OF THE FOLLOWING FIELD.
Example 12-16 lists the description of IFCID record 376.
Example 12-16 IFCID 376 record description
0376 QW0376 ________________IFCID 0376______________________________
0376 QW0376 IFCID 0376 RECORDS INFORMATION ABOUT SQL STATEMENTS
0376 QW0376 THAT HAVE POTENTIAL INCOMPATIBLE CHANGES WHEN YOU SWITCH
0376 QW0376 TO NEW APPLICATION BEHAVIOR.
0376 QW0376
0376 QW0376 THIS TRACE RECORD IS SIMILAR TO THE IFCID 0366 RECORD,
0376 QW0376 EXCEPT THAT THIS TRACE RECORD CONTAINS INFORMATION FOR
0376 QW0376 UNIQUE INSTANCES OF SQL STATEMENTS. THIS TRACE
0376 QW0376 RECORD IS WRITTEN ONCE FOR EACH UNIQUE INSTANCE
0376 QW0376 OF THE FOLLOWING TYPES OF SQL STATEMENTS:
0376 QW0376 - DYNAMIC STATEMENTS IN THE DYNAMIC STATEMENT CACHE
0376 QW0376 - STATIC STATEMENTS THAT WERE BOUND IN VERSION 10
0376 QW0376 NEW-FUNCTION MODE OR LATER
0376 QW0376 FOR STATIC SQL STATEMENTS THAT WERE BOUND BEFORE VERSION
0376 QW0376 10 NEW-FUNCTION MODE, THIS RECORD IS WRITTEN ONCE FOR
0376 QW0376 UNIQUE COMBINATION OF PLAN, PACKAGE ID, AND STATEMENT
0376 QW0376 NUMBER. ON RARE OCCASIONS, MORE THAN ONE TRACE RECORD
0376 QW0376 MIGHT BE WRITTEN.
0376 QW0376
0376 QW0376 THIS RECORD IS FOR SERVICEABILITY ONLY.
0376 QW0376 ---------------------------------------------------------
0376 QW0376FN THIS FIELD HAS THE SAME VALUES AS QW0366.
0376 QW0376SN STATEMENT NUMBER FOR THE QUERY.
0376 QW0376PL PLAN NAME FOR THE QUERY.
0376 QW0376TS TIMESTAMP FOR THE QUERY.
0376 QW0376SI STATEMENT IDENTIFIER.
0376 QW0376TY STATEMENT INFORMATION:
0376 QW0376TY X'8000': STATEMENT IS DYNAMIC.
0376 QW0376TY X'4000': STATEMENT IS STATIC.
0376 QW0376SE SECTION NUMBER.
0376 QW0376PC_OFF OFFSET FROM QW0376 TO QW0376PC_LEN.
0376 QW0376PN_OFF OFFSET FROM QW0376 TO QW0376PN_LEN.
0376 QW0376VL DS VERSION LENGTH.
0376 QW0376VN DS VERSION.
0376 QW0376PC_LEN LENGTH OF THE FOLLOWING FIELD.
0376 QW0376PC_VAR %U PACKAGE COLLECTION ID.
0376 QW0376PN_LEN LENGTH OF THE FOLLOWING FIELD.
0376 QW0376PN_VAR %U PROGRAM NAME.
DB2 catalog support for APPLCOMPAT
A new column APPLCOMPAT has been added to DB2 catalog tables SYSIBM.SYSPACKAGE and SYSIBM.SYSPACKCOPY. Possible values are:
V10R1 SQL statements in the package have V10R1 compatibility behavior.
V11R1 SQL statements in the package have V11R1 compatibility behavior.
1 They were deprecated in DB2 9. You can convert those applications that use the AMI-based functions to use the MQI-based functions
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset