let me give you more oddity. I can go and copyto import file, clear the table, add the column, and copy from import file (but it tells me zero records copied (but it totally repopulates the table) and all is "well" 

disregard my last comment, file is really empty and don't know why (yet).

When you read the file using your program and get the empty record, are you reading the file with native I/O or embedded SQL?

For native I/O, Control spec ALWNULL() determines what happens.

• If you have ALLNULL(*NO), and you read a record with a NULL value in a field, you get a data mapping error, and you don't know what record it was.
• ALWNULL(*INPUTONLY) means the field that had NULL value is returned to your program as the column's default value. Your program doesn't know that the value was NULL.
• The only way you really know is if you use ALWNULL(*USRCTL). After the READ (in your case), you need to test %NULLIND(fieldname) for any NULL-capable fields, before you try to use the value of those fields. If %NULLIND(field) is *OFF, the value in the field is usable. If %NULLIND(field) is *ON, the field is NULL. But, even when %NULLIND(field) is true, the field may still have a value, but you shouldn't use it; it's important to test the %NULLIND first.

If you're reading with embedded SQL, it's a little different. You need to add in indicator variable for any NULL-capable field. For example, if field1 allows NULL, but field2 does not, you might have something like

/EXEC SQL SELECT field1, field2

INTO :F1 :F1Ind, :F2

FROM sometable

/END-EXEC

Your fields F1 and F2 get the values of field1 and field2. F1Ind is an indicator variable, which works kind of like %NULLINID(field1). Note that there is no comma between :F1 and :F1Ind - they're referring to the same column in the SELECT list. The indicator variable must be defined as a 2-byte binary, such as a 5I 0. If the column was NULL, the indicator variable will have a -1. If you have a NULL value in the table, but forgot the indicator variable on your statement, then you get a negative SQLCODE.  A -2 indicates a data mapping error (value in the table won't work with how the receiving field is defined in your program).

What you ADDED the NEW field, did you include the NOT NULL with DEFAULT property?

Yes I did include it as not null and default property but when it tried to add, it said there were null values (which there are not). All fields are defined as not null.

I can export the records, clear the table, add the field,  reimport in the same records (via inav or cpytoimpf) and it works and doesn't blow up, etc. But when I take the data (the record is like 457 long), and pull all positions off into a flat file, (and the data looks perfect except I will get a blank record on the front), and even if I remove the blank record, when I try to put (1-457) the data back into the file (do a clear), it will say record 0 and every numeric field it will say is bad which they aren't. (believe me I have had a few data mapping errors in moving the stuff out of s/36ee and I know what they are).

In reading dale's, the definition has no null allowed fields, (originally like I said, he defined them null, and that didn't work for what I wanted, so we deleted the file and rebuilt it from sql specs (all with not null and default value). And since that time, I have probably deleted the first 50,000 records out of the file.

And because this null crap is new to me, I then have to assume that the data (looking at like with a wrkf with say F10 or F10/F11) isn't going to show the "null crap" I mean I was expecting to see like binary zeros or something but it is all PROPER ebcdic expected values.

And getting it on record 0 during a load, it is like the moment I open up the file, bingo it does such.

Obviously something is somewhere on one of the numeric fields because inav/sql won't add to it saying there is null values in decimal fields.

I have read what Dale has said and he has given me some ideas. Thanks...

I read bob's post from Oct 2012 on this as it is the same aplication. What I as an idiot think is odd, is it tells me that for example orderid has a data mapping error.

According to the info at ibm, one should be able to say something like 

select * from mylib.myfile where orderid is null but that returns from this (in strsql), no records selected.

Bob commented 18 months ago that the "null" is an attribute not necessarily a value within. 

A few more tid bits about using NULL capable (and variable length) DB2 fields natively in RPG, if it helps. I did some research a year ago. Sorry if this does not format pretty. 

DDS keywords are:
A RGERRMSG 256A COLHDG('Sugar' 'Message')
A VARLEN <-- like VARYING in D-Spec or VARCHAR in SQL DDL 
A ALWNULL <-- like NULL in SQL DDL 
A DFT(*NULL) <-- this is the default anyway for ALWNULL
A ALIAS(ERROR_MSG)

DSPFFD:
Field Type Length Length
RGERRMSG CHAR 256 258 <-- 2 leading bytes (integer) to hold the data length
Variable length field
Allows the null value  <-- Right there, says it can hold NULL value

In RPG:
H AlwNull( *UsrCtl ) <-- Allows Read/Update of NULLable *DB2* fields.

FCDREGSGR uf a e k Disk <-- no change to F-Spec.

// Is now NULL, sets data length to zero, current field value is retained but 100% unreachable now.
%NullInd(RgErrMsg) = *ON ;

// Can see old field value in debug hex only: Eval RgErrMsg:X

If ( %NullInd(RgErrMsg) ) ; <-- See if DB2 field value is null.
If ( Not %NullInd(RgErrMsg) ) ; <-- See if DB2 field value is not null.
If ( RgErrMsg = *NULL ) ; <-- NO not like this! Wrong!
If ( RgErrMsg <> ' ' ) ; <-- No error generated in RPG but probably not the IF statement you want.
If ( %Subst(RgErrMsg:1:1) <> ' ' ) ; <-- DIES in RPG with substring length error if NULL because field Len = 0 for a NULL field.

%NullInd(RgErrMsg) = *OFF ; <-- Not NULL anymore, and leaves data length = zero.
%Len(RgErrmsg) = 5 ; <-- %LEN sets length from zero to 5 and blanks out the first characters 
RgErrmsg = ' ' ; <-- or just assign a new value.

1. CLEAR RECFMT does not change the current NULL field indicators so
yes the CLEAR honors the current %NULLIND DB2 field indicators.
And CLEAR RECFMT does set VARLEN field lengths to zero (empty).

2. DBU and SQL will NOT show the hidden value in a field if NULL.

3. DBU shows NULL field values as Reverse Image YELLOW.

4. SQL shows NULL fields as a dash - only.

5. If NULL ON, the DB2 unreachable field value 100% cannot be used even
if NULL is set back to OFF (LEN becomes 0 and any new value would need to be assigned).

6. SELECT * from CDREGSGR WHERE RGERRMSG IS NOT NULL (the correct syntax to check for NULLs)

7. Not sure how to see in debug if a DB2 field is currently NULL.
Len=0000 is a clue. Eval RgErrmsg:X (the old value is a timestamp here too 20130225145539861234)
0000F2F0 F1F3F0F2 F2F5F1F4 F5F5F3F9 - ..20130225145539
F8F6F1F2 F3F40000 00000000 ........ - 861234..........

8. If not overrridden, the default value for a NULLable field is NULL.
Example: FIELD3 can have a NULL value. Omitting it in the INSERT statement sets its value to NULL.
INSERT INTO MYFILE (FIELD1, FIELD2) VALUES('TEST', 'DATA')
Same thing for CPYF when a new "FIELD3" has been added to a record layout to
copy records from the old file to the new file.

9. When NULL is ON for a field and you set it OFF, it gets set to it's default value.
So normally blanks, zeros, LEN=0 (for VARLEN) etc.

Compiled Listing (input/output specs):
I (ALWNULL) *VAR A 311 568 TRN_RGERRMSG Sugar Error Message
O (ALWNULL) TRN_RGERRMSG 568A VCHR 256 (RGERRMSG) Sugar Error Message

Chris Ringer

As I mentioned in the other post, if a field is null capable and you're reading it with SQL you need to include an indicator variable or you will get a mapping error. If you are reading it with good old RPG then you need to include the ALWNULL(*USRCTL) keyword on the Header spec.

also be aware when you use alter table to insert a new column all existing records n are set to NULL for that column.

"also be aware when you use alter table to insert a new column all existing records n are set to NULL for that column."

Only if the default value for the column is NULL. That happens when the new column is null-capable (i.e., you did not specify NOT NULL), and you did not specify some other DEFAULT. If it's null-capable and you do specify a DEFAULT value, the new column in existing rows will get that default. If the column is NOT NULL, you either get the explicit DEFAULT value, or the fall back is the default based on data type (blanks, zero, ...).

How did you add the record? Show us the syntax. Show us the RPG code that is blowing up. Show us the SQL code you used to and the data field. If all of this is clean then your data is probably corrupted and that's what's causing the data mapping error.