Re: problem adding new field with system i navigator

On 24-Apr-2014 13:40 -0500, Dale Janus wrote:

I am slowly converting all our files from DDS to DDL.

Why? Be sure the change is for specific\explicit reasons and the [potentially negative] effects are fully understood; i.e. beware of making change simply for the sake of making change.

I have a file I want to add a new field to. I originally created the
file with DDL in system i navigator (V7R1M0), so I navigate to the
file, chose definition and created a new numeric field, remove
nullable from the default and click ok. Field is added. But when I am
at the file level and click ok, I get this error:
SQL State: 58004 Vendor Code: -901
Message: [SQL0901] SQL system error. <<SNIP>> previous message
identifier was CPF5104. Internal error type 4406 has occurred.
<<SNIP>>

job log shows CPF2972 error writing to member and CFP5029 data
mapping error. (I can't copy the job log)

The reason code [or return code] for a msg CPF5035 [as described by the msg CPF5029] should appear including other details that further diagnose the condition; that should be RC19 for RRN=1 for a NULL value encountered. In other words, additional details are all moot, because the problem will always be the same for every file with that same scenario, whenever the file has at least one row; i.e. no data, no problem, but with data there is always a problem.

Quite probably the msg SQL0901 [an effective SQL "function check"] is the wrong result, and -407 msg SQL0407 should have been the result for the failing request.

If we add the field as "null with default 0", it adds ok. Then we go
back and modify and remove the null. This is a long way around. (I am
working on a test file that has data in it.)

Should work fine if added originally as "not null with default 0"; i.e. no reason to remove null-ability, never add the capability.

If we use straight SQL, it works ok. My partner is good with SQL, I
am not, I need navigator's help.

Probably the "straight SQL" was not an equivalent scenario, due to a lack of any data having been added to the file, to properly mimic the original failing scenario; e.g.:

create table qtemp/prueba (old_col char not null)
;
insert into qtemp/prueba values(1)
; -- omit INSERT, and there is no error in this scenario
alter table qtemp/prueba add column new_col char
; -- need not be a numeric data type to see failure
alter table qtemp/prueba
alter column new_col set data type char not null
; -- expected to fail per NULL value(s)

So what is happening with system i navigator and SQL and NULL?

Seems the request fails as expected, though possibly unexpectedly with a generic -901 condition instead of [the more likely as expected] sqlcode=-407 sqlstate=23502

drop table qtemp/prueba
; -- do not fall-over prior test-case
create table qtemp/prueba (old_col char not null)
;
insert into qtemp/prueba values(1)
; -- even with INSERT this scenario works fine
alter table qtemp/prueba
add new_col decimal not null with default 0
; -- new column gets zero as default, not the NULL value