A fix is available
APAR status
Closed as program error.
Error description
GOC - Generate ALTER TABLE statements instaed of DROP/CREATE in COMPARE. 1. Customer is dropping multiple existing columns and appending multiple new columns. 2. Customer is dropping column from table and index. OC processes the table first and decides to drop and recreate the table.
Local fix
Problem summary
**************************************************************** * USERS AFFECTED: Users of DB2 Administration Tool for z/OS * * and DB2 Object Comparison Tool for z/OS who * * want to make changes to Table columns using * * a DB2 ALTER TABLE statement instead of * * drop/creating the Table. * **************************************************************** * PROBLEM DESCRIPTION: Several problems were addressed in this * * APAR, all relating to Object Compare * * generating a DB2 ALTER TABLE statement * * instead of generating a DB2 DROP TABLE * * and DB2 CREATE TABLE statement: * * * * 1. When the column definition is * * changed from NULL to NOT NULL, * * Admin Tool/Compare drops and recreates * * the table, as there is no DB2 ALTER * * TABLE statement for changing the NULLs * * attribute. * * * * 2. DB2 supported ALTER TABLE table DROP * * COLUMN col RESTRICT statement to drop * * a column with certain restrictions. One * * restriction is that the column cannot * * be dropped if referenced by an index. * * When attempting to drop a column that * * is referenced by an index, DB2 will * * issue an error message: * * * * DSNT408I SQLCODE = -478, ERROR: * * ALTER, DROP, OR REVOKE AFFECTING * * OBJECT TYPE COLUMN CANNOT BE * * PROCESSED BECAUSE OBJECT index OF * * TYPE INDEX IS DEPENDENT ON IT * * * * Currently Admin Tool/Compare does not * * generate ALTER TABLE DROP COLUMN. The * * table is dropped and recreated. * * * * 3. When Table columns are rearranged, * * Admin Tool/Compare drops and recreates * * the table instead of using DB2 ALTER * * TABLE DROP COLUMN and ADD COLUMN * * statements to achieve the same result. * **************************************************************** * RECOMMENDATION: * **************************************************************** A new option on panel ADB2PCO called "Unload altered tables" was implemented to give users the option to generate ALTER TABLE statements instead of DROP/CREATE TABLE statements. The default for this option is 'No'. When this option "Unload altered tables" is 'Yes', an UNLOAD record will be generated in addition to the following: 1. Object Compare will generate ALTER TABLE DROP COLUMN when the last few columns of the table are changed from NULL to NOT NULL or vice versa. For example: The table has the same columns in the same order on the source and target. The definition of the last column is changing from WITH DEFAULT NULL (NULL and default) to NOT NULL WITH DEFAULT (NOT NULL and default). Eg. ROLE_ORIGIN VARCHAR(12) FOR SBCS DATA WITH DEFAULT NULL, is changed to: ROLE_ORIGIN VARCHAR(12) NOT NULL FOR SBCS DATA DEFAULT 'RESAUTH' There is no DB2 ALTER TABLE statement which supports changing NULL/NOT NULL to NOT NULL/NULL. Instead of generating DROP/CREATE TABLE, the column is dropped and appended: ALTER TABLE >sqlid<.T003ACCESS_ROLE DROP COLUMN ROLE_ORIGIN RESTRICT; ALTER TABLE >sqlid<.T003ACCESS_ROLE ADD COLUMN ROLE_ORIGIN VARCHAR(12) NOT NULL FOR SBCS DATA DEFAULT 'RESAUTH' In this release, VARCHAR is the only supported data type. 2. Object compare will handle this case by generating the following DDL statements: DROP INDEX inx1; to remove the dependency between column and index. Then CREATE INDEX inx.. (without col1); ALTER TABLE tb1 DROP COLUMN col1 RESTRICT; Drop the column. Pending the uniqueness of a ROWID column. 3. Object Compare will generate ALTER TABLE DROP COLUMN and ALTER TABLE ADD COLUMN statements to rearrange, drop, or add columns when possible instead of using DROP/CREATE TABLE statements. DB2 Restrictions for using ALTER TABLE DROP/ADD COLUMN still apply. If these restrictions are encountered, Object Compare will continue to use the DROP / CREATE TABLE statements, as before.
Problem conclusion
Problem has been resolved.
Temporary fix
Comments
APAR Information
APAR number
PH00552
Reported component name
DB2 OBJECT COMP
Reported component ID
5697G6400
Reported release
B20
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2018-07-17
Closed date
2018-09-20
Last modified date
2018-10-02
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UI58619 UI58620
Modules/Macros
GOCCMP GOCGCMP
Fix information
Fixed component name
DB2 OBJECT COMP
Fixed component ID
5697G6400
Applicable component levels
Fix is available
Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSAUVH","label":"IBM Db2 Object Comparison Tool for z\/OS"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.2.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Document Information
Modified date:
02 October 2018