Midrange News for the IBM i Community


Posted by: clbirk
problem trying to add field
has no ratings.
Published: 25 Feb 2014
Revised: 26 Feb 2014 - 1124 days ago
Last viewed on: 26 Mar 2017 (2079 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

problem trying to add field Published by: clbirk on 25 Feb 2014 view comments(10)

a year ago, our php programmer set up a table with null values allowed. That caused me a problem, so I generated the sql, changed it to not null, deleted the table off, and rebuilt it. All was fine (or so I thought). We use it every day, all day long, without problems.

Today I try to add a new field and I get a sql system state error that says that a not null field contains null values. I remember a year ago when I put in a topic asking some questions, bob told me that the null stuff is stored somewhere.

I made a copy of the table and then used an rpg2 program to read all the records and I get a "empty record" which if you look with wrkf in the db table it isn't there. I tried on a copy of the table to clear it, delete the blank record and reload the data into it, and I get the data mapping errors but the odd thing is there is NOTHING wrong with the data, it tells me record 0...

I have this same table on another i, and of course when I generated the specs for it and loaded it over there, it was all not null and it has the same sort of data in it and all is fine in adding columns, etc. (no data mapping errors).

Any thoughts on this? Doing sql selects, there are no records with values less than 1 in the numeric fields, etc. And what really stumped me when I had a flat file to load into a cleared table, I couldn't and got same mapping error.

It is like there is some attribute somewhere in one of those other sql related crap that...

chris

 

 

 

Toda

 

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: clbirk
Premium member *
Comment on: problem trying to add field
Posted: 3 years 29 days 21 hours 4 minutes ago

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" 

 

Posted by: clbirk
Premium member *
Comment on: problem trying to add field
Posted: 3 years 29 days 20 hours 57 minutes ago

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

Posted by: DaleB
Premium member *
Reading, PA
Comment on: problem trying to add field
Posted: 3 years 29 days 17 hours 54 minutes ago
Edited: Tue, 25 Feb, 2014 at 14:11:44 (1125 days ago)

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).

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: problem trying to add field
Posted: 3 years 29 days 17 hours 39 minutes ago

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

Posted by: clbirk
Premium member *
Comment on: problem trying to add field
Posted: 3 years 29 days 16 hours 55 minutes ago

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...

 

 

 

Posted by: clbirk
Premium member *
Comment on: problem trying to add field
Posted: 3 years 29 days 16 hours 40 minutes ago

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. 

 

 

Posted by: Ringer
Premium member *
Comment on: problem trying to add field
Posted: 3 years 29 days 16 hours 36 minutes ago

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

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: problem trying to add field
Posted: 3 years 29 days 6 hours 53 minutes ago
Edited: Wed, 26 Feb, 2014 at 01:14:08 (1124 days ago)

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.

Posted by: DaleB
Premium member *
Reading, PA
Comment on: problem trying to add field
Posted: 3 years 29 days ago

"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, ...).

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: problem trying to add field
Posted: 3 years 28 days 23 hours 43 minutes ago

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.