Midrange News for the IBM i Community


Posted by: Bob Cozzi
Rogue Programmer
Cozzi Productions, Inc.
Chicagoland
Null Indicators in Databae Are you Using them?
has no ratings.
Published: 26 Nov 2011
Revised: 23 Jan 2013 - 1582 days ago
Last viewed on: 22 May 2017 (4395 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.

Null Indicators in Databae Are you Using them? Published by: Bob Cozzi on 26 Nov 2011 view comments(9)

Is anyone actually using the Null Indicator support in the database?

In DDS, you need to use ALWNUL keyword.

In SQL it is the default for all fields UNLESS you specify "NOT NULL".

When using RPG native I/O the ALWNULL(*USRCTL) is necessary to acknowledge NULLs in database records, and the %NULLIND(myField) built-in function is used to test and set the null "indicator" for the field.

When using SQL I/O in RPG IV, ALWNULL(*USRCTL) is also necessary, however the null indicator is tested/set using a 5i0 (2-byte signed integer) variable that's returned on a FETCH or set on an UPDATE/INSERT operation.

%NULLIND has nothing to do with anything when SQL is used. Frown So conversion between the two could be necessary.

With %NULLIND the classic *ON or *OFF status can be tested:

  • When *ON, the field is null and the contents should be ignored/not used.
  • When *OFF, the field is NOT null and its contents can be used.

With SQL Null Indicators:

  • When the null indicator is -1 the field is null and the contents should be ignored.
  • When the null indicator is 0, the fields is NOT null and its contents can be used.

See these two related articles for additional information:

SQL Setting Null Indicators

Debugging Null Indicators in RPG IV

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

COMMENTS

(Sign in to Post a Comment)
Posted by: neilrh
Premium member *
Jackson, MI
Comment on: Null Indicators in Databae Are you Using them?
Posted: 5 years 5 months 26 days 21 hours 31 minutes ago
Edited: Mon, 28 Nov, 2011 at 07:27:18 (2004 days ago)

I used to.  Back with my previous employer, all new development I worked on used DDL defined tables and allowed null where appropriate.  My other colleagues also began moving in the same direction - especially for some PC based applications, with data resident on our AS400, they also got into constraint definitions and other things that RPG programmers tend to view as being from the dark side.  But then the entire corporation became IS NULL, and now I work for somewhere-else.

In somewhere-else-land, their favourite RPG trainer at common, is of the opinion that DDL defined tables offer no performance inprovement over DDS defined files and there's no point in learning any new stuff when DDS is perfectly adequate.  Also integers are nice, but packed has been around for decades, and who needs null when you have *zero, *blank, *loval & *hival??  Even /free is quaint.

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: Null Indicators in Databae Are you Using them?
Posted: 5 years 5 months 26 days 18 hours 51 minutes ago

I've moved to DDL (i.e., SQL) only definitions for files (tables <g>).

I am maintaining existing stuff and only convert it if it clearly converts to SQL without anything in the way--on thing that's in the way occasionally is CL-based CRTLF statements on the fly. Done so that the logical isn't around on a day-to-day basis; but rather built when needed.  Which ironically is the perfect scenario for embedded SQL. But I'm just there to fix things not to change the world. So I leave those and move on.

But the NULL thing was something I only thought was important. Years ago I looked at it and thought it was goofy--why bother? Then as time went on and I moved to SQL-only definitions and more recently to SQL-only database I/O in my programs, I thought, why not?

Having used it I realize my initial thinking on it was correct. It has its place in the world, just like nuclear weapons--just you don't really need to use them as there are so many other ways of getting the job done--but everyone once in a while...

Posted by: Tmitchell
Premium member *
Monett MO
Comment on: Null Indicators in Databae Are you Using them?
Posted: 5 years 5 months 26 days 18 hours 6 minutes ago

We use them when accessing files created by our SQL server side. They always create the fields as NULL capable, so we must look at the indicator.

 

Posted by: neilrh
Premium member *
Jackson, MI
Comment on: Null Indicators in Databae Are you Using them?
Posted: 5 years 5 months 26 days 17 hours 35 minutes ago

Ok, probably a dumb question, but why create/delete LF on the fly when you can make them permanent with MAINT(*REBLD), if you don't need them using up space or slowing file writes/updates.

Posted by: DaleB
Premium member *
Reading, PA
Comment on: Null Indicators in Databae Are you Using them?
Posted: 5 years 5 months 26 days 16 hours 42 minutes ago

Yes, started using them when we started defining tables w/ SQL.

If you don't allow NULL, and try to use some special value to "get the job done," it's been my experience that sooner or later you wind up getting burned.

As far as the values of the null indicator variable, your list is not complete. For SELECT, besides 0 and -1, you can also get -2 on data mapping error.

For INSERT and UPDATE there are other values that let you use the default value, or ignore the column. (Ignoring the column is handy because you can change the behavior of a static statement, and you don't need multiple versions of the statement to do it.)

See Embedded SQL Programming, Indicator variables in applications that use SQL, and also Indicator variables used to assign special values.

For the opposing side, I find there are some rather painful shortcomings with SQL CREATE TABLE. There should be an (ANSI SQL) equivalent to REFFLD; you can create a table like another table, but nothing at column level. Would also be nice to have (i specific) extensions equivalent to EDTCDE/EDTWRD, and maybe one or two others.

Posted by: neilrh
Premium member *
Jackson, MI
Comment on: Null Indicators in Databae Are you Using them?
Posted: 5 years 5 months 26 days 15 hours 58 minutes ago

While I agree with your thoughts on REFFLD missing from DDL.  One of the biggest problems I have is with EDTCDE/EDTWRD on DDS files - spent a couple of hours trying to debug an issue, and it eventually boiled down to EDTCDE(3) on the file, and negative values in a field that "just never would be negative" - the EDTCDE prevented you from seeing the sign of the value on the field in question.

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: Null Indicators in Databae Are you Using them?
Posted: 5 years 5 months 26 days 15 hours 34 minutes ago

I think lack of knowledge and legacy implementation is why they were using dynamically created logicals instead of something else. They even have some SORT (FMTDATA) calls embedded in their CL.

Posted by: rgilsdor
Premium member *
Wayne, Michigan
Comment on: Null Indicators in Databae Are you Using them?
Posted: 5 years 5 months 25 days 15 hours 45 minutes ago

Yes I use them on files created using CREATE TABLE.

I also use them a bunch whenever I use SQL SELECT from within my programs. I have grown fond of creating SQL scalar functions to help modularize some of my code and get a value that may need to come from several sources to figure out. In these functions I regularly use a NULL return if there is no valid value.

For example, what co-driver was in a power unit at a particular time when querying from a file that contains the unit and a time stamp? Many times there is only a single driver so a proper null return is good. 

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: Null Indicators in Databae Are you Using them?
Posted: 5 years 5 months 25 days 14 hours 56 minutes ago

In addition to the 0 (not null) the -1 (null) I am now getting a -2 returned into the Indicator variable. This is happening on records with no valid date in the date field.

Yet another value to monitor for... I've since changed my code from:

if (iShipDate = 0);  // Ship Date is NOT NULL
...
elseif (iShipDate = -1);  // Ship date is NULL
...
endif;

to this:

if (iShipDate = 0);  // Ship Date is NOT NULL
...
elseif (iShipDate < 0);  // Ship date is NULL or screwed up
...
endif;