Topic
  • 4 replies
  • Latest Post - ‏2014-08-12T15:43:30Z by dlStickler
dlStickler
dlStickler
21 Posts

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
    253 Posts

    Re: Alter table CPF5029 DB2 for i

    ‏2014-07-30T05:41:05Z  

    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
    21 Posts

    Re: Alter table CPF5029 DB2 for i

    ‏2014-07-30T13:26:57Z  
    • B.Hauser
    • ‏2014-07-30T05:41:05Z

    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

    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
    450 Posts

    Re: Alter table CPF5029 DB2 for i

    ‏2014-07-30T17:39:59Z  

    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.  

     

     

    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
    21 Posts

    Re: Alter table CPF5029 DB2 for i

    ‏2014-08-12T15:43:30Z  

    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.

    Latest PTF image applied. The alter table works fine. Thank you!