A Follow-Up on Drop Column Support

When I recently wrote about the new DB2 11 drop column support, I admitted that I couldn't understand why this was such a highly requested feature. A commenter provided some insight.

The commenter -- Brian -- explained that the issue isn't (as I'd surmised) the random need to drop a column from a production table. More commonly, a developer may be asked to add a column, only to later learn it wasn't really needed. So now the developer must remove it.

In addition, Robert Catterall offers some good supporting information on adding and dropping columns. His post also examines some of the changes in DB2 11.

Have you had a chance to use drop column? If so please post some comments on the situation and how this has helped you.

DB2 10-11 Migration: Application and SQL release incompatibilities

I previously noted that DB2 11 allows applications with incompatible SQL or XML to run in compatibility mode.

Here I'll examine a related question: What will cause an application to be incompatible?

IBM provides this information in the DB2 for z/OS Installation and Migration Guide, in the section titled, "Application and SQL release incompatibilities."

A list of potential incompatibilities, along with brief descriptions, follows. See the installation and migration guide to understand how these incompatibilities can impact your applications and how to deal with them.

* Change to determination of ASUTIME for dynamic statements -- In Version 11, new-function mode with application compatibility is set to V11R1, and the dynamic SQL ASUTIME limit for each routine is used by the resource limit facility.

* 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 Version 11 conversion mode and later.

* Change in default for ODBC-limited block fetch initialization keyword -- The default for the LIMITEDBLOCKFETCH initialization keyword is changed in Version 11.

* DB2 objects created with period specifications in Version 10 -- In Version 11, views, materialized query tables and SQL table functions that were created with period specifications in Version 10 are not supported.

* Dropping columns -- In Version 11 new-function mode, columns named CLONE, ORGANIZATION or VERSIONING should be specified as a delimited identifier to be dropped from a table.

* XPath processing -- In DB2 Version 11 new-function mode with application compatibility set to V11R1, XPath is allowed to continue processing even when errors occur on filtered results when using predicate expressions with explicit cast or operations with an invalid value.

* XML document node implicitly added on insert and update -- In DB2 Version 11 new-function mode 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.

* Client information special registers length -- In DB2 Version 11 new-function mode with application compatibility set to V11R1, special registers for client information fields might return different length values. The values in the CURRENT CLIENT_USERID, CURRENT CLIENT_WRKSTNAME, CURRENT CLIENT_APPLNAME, and CURRENT CLIENT_ACCTNG special registers are determined by the application compatibility level.

* Client information results from ADMIN_COMMAND_DB2 -- In DB2 Version 11 conversion mode, the ADMIN_COMMAND_DB2 result set row returns changes in the created global temporary table SYSIBM.DB2_THREAD_STATUS when processing-type = "THD". The column data type and maximum lengths for WORKSTATION, USERID, APPLICATION and ACCOUNTING change.

* Altering limit keys blocks immediate definition changes -- In DB2 Version 11 new-function mode, if a limit key is altered for certain table space types, immediate definition changes cannot be made until the limit key changes are materialized.

* Removing the SYSPUBLIC schema from the SQL PATH routine option -- Starting in DB2 Version 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.

* SYSIBMADM schema added to the SQL path -- In DB2 Version 11 new-function mode with application compatibility set to V11R1, SYSIBMADM is added to the SQL path as an implicit schema.

* Change in result for CAST(string AS TIMESTAMP) -- In DB2 Version 11 new-function mode with application compatibility set to V11R1, the result of CAST(string AS TIMESTAMP) is changed in some cases.

* New maximum lengths for values that are returned for some built-in functions -- In DB2 Version 11 new-function mode with application compatibility set to V11R1, the maximum lengths for values that are returned for some built-in functions is decreased.

* Timestamp string representations -- DB2 Version 11 new-function mode with application compatibility set to V11R1 strictly enforces valid string representations of timestamp values.

* SQL reserved words -- Version 11 has several new SQL reserved words. These are listed in reserved words.

 

* Fully qualified user-defined function names -- When using a user-defined function with the same name as a built-in function that has been added to Version 11, the function name must be fully qualified. If the function name is unqualified and SYSIBM precedes the schema used for this function in the SQL path, DB2 invokes one of the built-in functions.

* SQLCODE changes -- Some SQLCODE numbers and message text may change in DB2 Version 11. Also, the conditions under which some SQLCODEs are issued may change.

With earlier DB2 releases, addressing incompatibilities would require substantial changes and testing before the migration could take place. That's the major benefit of DB2 10 compatibility mode. It enables customers to migrate quickly to DB2 11 while maintaining current applications until they can be modified and tested on the new release.

Incompatibilities that Could Impact Migrations to DB2 11

As part of the process of migrating from DB2 Version 10 to DB2 11, you should be aware of these incompatibilities:

·         Application and SQL release incompatibilities: Next week's post will cover application and SQL-specific incompatibilities in detail.

 

·         V10R1 application compatibility: When the application compatibility value is set to V10R1, applications that attempt to use functions and features introduced in DB2 11 or later versions may behave differently or receive errors.

·         Utility release incompatibilities.

·         Command release incompatibilities.

·         Storage release incompatibilities.

 

·         Other release incompatibilities.

 

·         Deprecated functions: During migration it's important to know of all functions that are deprecated in DB2 11. While deprecated functions are still supported, that support could be removed in the future. So avoid creating new dependencies that rely deprecated functions and plan to remove existing dependencies.

 

·         Functions that are no longer supported.

ALTER Operations Restricted in DB2 11

DB2 11 introduces new restrictions that limit the types of ALTER operations that can be conducted on an object.

The restrictions pertain to the IBM solution known as Online Schema Evolution. They generally involve having a mixture of alters that are immediate, as well as alters that place an object in REORG-pending status. A sample scenario is presented in PMR58791, "Workaround for ALTER operations that can no longer be done together in Version 11."

My takeaway from this is that, whenever possible, DBAs should perform all ALTER operations that take effect immediately prior to performing the ALTER operations that put the object in REORG-pending status.

For related information, see my previous posts about Online Schema Evolution. This one, from back in September 2007, covers enhancements in DB2 9. In July 2010 I wrote about the updates in DB2 10.