IBM Support

SE64621 - OSP-DB-OTHER RMVPFCST AND ADDPFCST WITH PRIMARY KEY AND
MSGCPA32B1 WITH DEFAULT ANSWER K RESULT IN MISSING PRIMARY KEY

Subscribe to this APAR

By subscribing, you receive periodic emails alerting you to the status of the APAR, along with a link to the fix after it becomes available. You can track this item individually or track all items by product.

Notify me when this APAR changes.

Notify me when an APAR for this component changes.

 APAR (Authorized Program Analysis Report)

Abstract

OSP-DB-OTHER RMVPFCST AND ADDPFCST WITH PRIMARY KEY AND
MSGCPA32B1 WITH DEFAULT ANSWER K RESULT IN MISSING PRIMARY KEY

Error Description

CREATE OR REPLACE TABLE QIBMDATA/TABLE1 (COL1 INT)              
INSERT INTO QIBMDATA/TABLE1 VALUES(1)                          
INSERT INTO QIBMDATA/TABLE1 VALUES(NULL)                        
INSERT, UPDATE, or MERGE not allowed by CHECK constraint, which
is correct, NULL values are not allowed with a primary key      
defined                                                        
                                                               
We now remove the contstraint:                                  
RMVPFCST FILE(QIBMDATA/TABLE1) CST(*ALL) TYPE(*PRIKEY)          
CPA32B1 - Key specifications are going to be removed from file  
TABLE1. (K G)                                                  
K                                                              
Constraint was removed.                                        
Constraint was removed.                                        
1 constraint(s) removed from file TABLE1.                      
1 constraint(s) removed from file TABLE1.                      
                                                               
When we now add again the constraint:                          
ADDPFCST FILE(QIBMDATA/TABLE1) TYPE(*PRIKEY) KEY(COL1) <--      
problem not adding the primary key check constraint            
The the primary key check constraint is not added.              
And when running an insert we actual got NULL values are not    
allowed with a primary key defined.                            
insert into QIBMDATA/TABLE1 values(null)                        

Problem Summary

NULL values are allowed to be inserted into a table with a      
primary key.   This can be reproduced using the following steps:
                                                               
1) create or replace table t1 (col1 int)                        
2) insert into t1 values(1)                                    
3) insert into t1 values(null) - fails with "INSERT, UPDATE, or
MERGE not allowed by CHECK constraint."                        
4) RMVPFCST FILE(T1) CST(*all) TYPE(*PRIKEY)                    
5) Respond with a 'K' for the inquiry message "Key              
specifications are going to be removed from file T1.  (K G)"    
6) ADDPFCST FILE(T1) TYPE(*PRIKEY) KEY(COL1)                    
                                                               
7) insert into t1 values(null) - this should fail and is        
successful                                                      

Problem Conclusion

This problem can occur for some numeric data types.  Constraint
processing is corrected for keyed files to add a primary key    
check constraint to prevent NULL values for all data types.    

Temporary Fix

Comments

Circumvention


PTFs Available

R710 SI60509 PTF Cover Letter   6320
R720 SI60510 PTF Cover Letter   6306
R730 SI60511 PTF Cover Letter   6299

Affected Modules

         
         

Affected Publications

Summary Information

Status............................................ CLOSED PER
HIPER........................................... No
Component.................................. 5770SS1DB
Failing Module.......................... RCHMGR
Reported Release................... R710
Duplicate Of..............................




System i Support

IBM disclaims all warranties, whether express or implied, including, but not limited to, the implied warranties of merchantability and fitness for a particular purpose. By furnishing this document, IBM grants no licenses to any related patents or copyrights. Copyright © 1996,1997,1998, 1999, 2000, 2001, 2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017 IBM Corporation. Any trademarks and product or brand names referenced in this document are the property of their respective owners. Consult the Terms of use link for trademark information

[{"Type":"MASTER","Line of Business":{"code":"LOB57","label":"Power"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Platform":[{"code":"PF012","label":"IBM i"}],"Version":"7.2.0"},{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SG15Q","label":"APARs - OS\/400 General"},"Component":"","ARM Category":[],"Platform":[{"code":"PF012","label":"IBM i"}],"Version":"V7R1M0;V7R2M0;V7R3M0","Edition":"","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
02 December 2016