II05485: SQLCODE407 UPDATING DB2 TABLES DEFINED AS ' WITH DEFAULT '

APAR status

• Closed as canceled.

Error description

• MSGAS6299 DSQ REQUEST 213 ERROR = 016/0306 SQLCODE = -407
  UPDATING DB2 COLUMNS DEFINED AS TIMESTAMP OR DATE WHEN USING
  AS (PROGRAM NUMBER 5688-108 - COMPONENT ID 568810801) WITH DB2.
  .
  THE INFORMATION AS HAS RETURNED FROM DB2 ABOUT A COLUMN DEFINED
  AS 'NOT NULL WITH DEFAULT' IS THAT IT IS 'NOT NULL'.
  THEREFORE SQLCODE407 IS RETURNED WHEN AS ATTEMPTS TO CHANGE IT
  VIA INCLUDE OR UPDATE PROCESSING.
  .
  A BYPASS FOR THIS SITUATION IS TO USE AN AS MASK TABLE WITH
  INCLUDE PROCESSING, AND TO USE A DB2 VIEW WITH UPDATE PROCESSING
  .
  NOTE. IF THE DB2 TABLE IS COPIED TO ANOTHER DB2 TABLE, THE
  WITH DEFAULT ATTRIBUTE WILL BE LOST.
  

Local fix

Problem summary

• THIS PROBLEM IS CAUSED BY AS PROVIDING A BLANK VALUE FOR A
  DATE, TIME OR TIMESTAMP COLUMN, WHICH HAS BEEN DEFINED AS
  NOT NULL WITH DEFAULT.
  .
  THE INFORMATION REGARDING EACH COLUMN IN A RELATIONAL TABLE IS
  OBTAINED BY AS VIA PREPARE AND DESCRIBE STATEMENTS. USING
  THIS MECHANISM THE FACT THAT A COLUMN IS DEFINED AS NOT NULL IS
  INDICATED, BUT NO INDICATION IS GIVEN FOR DATE, TIME AND
  TIMESTAMP COLUMNS ABOUT 'NOT NULL WITH DEFAULT'. AS A RESULT
  AS CANNOT PROVIDE ANY SPECIAL PROCESSING TO SOLVE THE REPORTED
  PROBLEM.
  

Problem conclusion

• YOU CAN HOWEVER UPDATE SUCH TABLES FROM AS, AVOIDING THE
  REPORTED PROBLEM BY THE FOLLOWING METHODS:
  .
   1. CREATE A SIMPLE VIEW OF THE TABLE WHICH DOES NOT INCLUDE THE
      COLUMNS DEFINED AS 'NOT NULL WITH DEFAULT'.
      THIS VIEW CAN THEN BE USED AS AN IN OR INCLUDE TABLE. IT CAN
      BE UPDATED USING AS UPDATE OR VIA THE INCLUDE MECHANISM.
      HOWEVER THIS METHOD WILL NOT ALLOW YOU TO UPDATE THE
      TABLE USING THE CHANGE COMMAND.
  .
   2. CREATE AN AS MASK TABLE WHICH DOES NOT INCLUDE THE COLUMNS
      DEFINED AS 'NOT NULL WITH DEFAULT'.
      YOU CAN THEN ASSOCIATE THIS MASK TABLE WITH THE RELATIONAL
      TABLE, FOR EXAMPLE USING THE COMMANDS:
      .
           IN MASK=(DB2)TABLE(ORDER LIST)
        OR INCLUDE MASK=(DB2)TABLE(KEY LIST)
      .
      YOU CAN THEN USE THE CHANGE COMMAND OR THE INCLUDE MECHANISM
      TO UPDATE THE TABLE. HOWEVER, THIS METHOD WILL NOT ALLOW
      YOU TO UPDATE THE TABLE USING AS UPDATE.
  .
   3. FOR CHANGE YOU CAN ALSO BUILD YOUR OWN IMAGE PANEL. THIS
      PANEL CAN EITHER PROTECT SUCH COLUMNS TO PREVENT THEM FROM
      BEING UPDATED, OR NOT DISPLAY THEM AT ALL.
  .
  IN ALL OF THE ABOVE CASES AS WILL NOT SUPPLY A VALUE FOR
  THE TIME, DATE AND TIMESTAMP COLUMNS, THEREFORE THE DEFAULT
  VALUE WILL BE USED BY DB2.
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

Applicable component levels