Topic
4 replies Latest Post - ‏2014-08-12T15:43:30Z by dlStickler
dlStickler
dlStickler
15 Posts
ACCEPTED ANSWER

Pinned topic Alter table CPF5029 DB2 for i

‏2014-07-29T18:04:26Z |

I have converted a DDS file to DDL table as a proof of concept.  As part of that same project, now I would like to alter the table.

I have tried several different types of data type columns, even a simple string.  I get this error:   CPF5035 reason code:  20 -- A field that is not null capable could not be set to null.

As long as nulls are allowed in the new column, the table can be altered, but if a default value is set, the above error is received.

 

  • B.Hauser
    B.Hauser
    241 Posts
    ACCEPTED ANSWER

    Re: Alter table CPF5029 DB2 for i

    ‏2014-07-30T05:41:05Z  in response to dlStickler

    When altering the columns, you need to add  'NOT NULL' or even better NOT NULL DEFAULT Whatever:

    ALTER TABLE YourTable

          ALTER COLUMN YourColumn SET DATA TYPE VARCHAR (50) ALLOCATE (50) DEFAULT '' NOT NULL

    Birgitta

    • dlStickler
      dlStickler
      15 Posts
      ACCEPTED ANSWER

      Re: Alter table CPF5029 DB2 for i

      ‏2014-07-30T13:26:57Z  in response to B.Hauser

      Thank you.

      here's my script:

      ALTER TABLE myTable
            add COLUMN test_column for   testcol CHARACTER (1) CCSID 37 NOT NULL DEFAULT 'N' NOT HIDDEN;

       

      Here's my error navigator script error, then the job log error.  It's vague so i'm a bit lost.  (I should also say that I am working on 7.1)  These are DDL tables that i have reverse engineered from DDS, following the reengineering path, (reverse engineer the DDS to DDL, change the PF to LF)

      I have tried several different data types.  As mentioned, there are no issues if nulls are allowed.  The column adds with no issues, but, in this situation, nulls are definitely not desired.

      SQL State: 58004
      Vendor Code: -901
      Message: [SQL0901] SQL system error. Cause . . . . . :   An SQL system error has occurred.  The current SQL statement cannot be completed successfully.  The error will not prevent other SQL statements from being processed. Previous messages may indicate that there is a problem with the SQL statement and SQL did not correctly diagnose the error. The previous message identifier was CPF5104. Internal error type 4406 has occurred. If precompiling, processing will not continue beyond this statement. Recovery  . . . :   See the previous messages to determine if there is a problem with the SQL statement. To view the messages, use the DSPJOBLOG command if running interactively, or the WRKJOB command to view the output of a precompile.  An application program receiving this return code may attempt further SQL statements.  Correct any errors and try the request again.

      I took a look at the job log:

       

      Data mapping error on member MYTABLESQL.                
      Data mapping error on member MYTABLESQL.                
      C                                                       
      Cancel reply received for message CPF5029.              
      Error writing to member MYTABLESQL in file MYTABLESQL.  

       

       

      • krmilligan
        krmilligan
        433 Posts
        ACCEPTED ANSWER

        Re: Alter table CPF5029 DB2 for i

        ‏2014-07-30T17:39:59Z  in response to dlStickler

        SQL0901 usually indicates an operating system defect, is the Database Group PTF recent?  If the problem still persists with the latest DB Group PTF, then you'll have to open a PMR with IBM Support.

        • dlStickler
          dlStickler
          15 Posts
          ACCEPTED ANSWER

          Re: Alter table CPF5029 DB2 for i

          ‏2014-08-12T15:43:30Z  in response to krmilligan
          Latest PTF image applied. The alter table works fine. Thank you!