How do I handle NULL data values in CA Earl for DB2, SQL/DS, and CA Datacom/SQL?

Products

• Earl

Issue/Introduction

Special handling is required when dealing with null data values in your CA Earl applications that access data from DB2, SQL/DS and CA Datacom/SQL. This article will show you how to identify and handle null data values in your Advantage CA-Earl application.

How do I handle NULL data values in CA Earl for DB2, SQL/DS, and CA Datacom/SQL?

Environment

z/OS

Resolution

First, let's look at a simple DB2 table named TESTNULL, which can be created using the following SQL statement:

CREATE TABLE creator.TESTNULL
(CHAR              CHAR(4) NOT NULL,
NULLINT           INTEGER,
NULLCHAR          CHAR(4) ) ;

Notice that columns NULLINT and NULLCHAR allow for null data.

Let's say that table TESTNULL has been loaded with the following data that contains some NULL values:

INSERT INTO creator.TESTNULL VALUES ('NOT0',0000,'0000');
INSERT INTO creator.TESTNULL VALUES ('BOTH',NULL, NULL );
INSERT INTO creator.TESTNULL VALUES ('NOT3',0003,'0003');
INSERT INTO creator.TESTNULL VALUES ('NULI',NULL,'0004');
INSERT INTO creator.TESTNULL VALUES ('NULC',0005, NULL );
INSERT INTO creator.TESTNULL VALUES ('NOT6',0006,'0006');

Now, let's use the following CA Earl request to print the three columns:

TESTNULL: DB SQL 'SELECT CHAR, NULLINT, NULLCHAR '
'FROM creator.TESTNULL '

DEFINE CHAR        4 X
DEFINE NULLINT     4 B
DEFINE NULLCHAR    4 X

REPORT EXCLUDE
TITLE ' CHAR NULLCHAR   NULLINT'
TITLE ' ---- --------   -------'
PRINT 1 CHAR 1 NULLCHAR 1 NULLINT
END

<Please see attached file for image>

src="/servlet/servlet.FileDownload?file=0150c000004AKrDAAW" alt="Figure 1" width="550" height="116" border="0">

Notice the bolded values in the report are not valid. Because the value for the column was null, the values from the previously processed record were carried forward.

Special handling is required when dealing with null columns.

As documented in the CA-Earl 6.1 Reference Guide, Section 2.10.4 "DB2, SQL/DS, or CA-DATACOM Table", item 3:

"To avoid data interrupts on null fields (which is allowed by DB2 or SQL/DS but incompatible with CA-Earl), test '?fieldname' to see if a field contains null values.When ?fieldname = 'Y', the field is a null field. When ?fieldname = 'N', the fieldis not a null field."

So, let's add ?fieldname handling to the previous request to identify the null values. We've added the ?fieldname tests for the two columns in order to assign a value to defined fields for printing. Of course, you would tailor your logic "IF ?fieldname =" to suit your application needs.

TESTNULL: DB SQL 'SELECT CHAR, NULLINT, NULLCHAR '
'FROM creator.TESTNULL '

DEFINE CHAR        4 X
DEFINE NULLINT     4 B
DEFINE NULLCHAR    4 X

DEFINE PRINTINT    (X 8) = ' '
DEFINE PRINCHAR    (X 8) = ' '

IF ?NULLCHAR = 'Y' THEN
SET PRINTCHAR = 'NULLCHAR'
ELSE
SET PRINTCHAR = ' '
ENDIF

IF ?NULLINT  = 'Y' THEN
SET PRINTINT = 'NULLINT'
ELSE
SET PRINTINT = ' '
ENDIF

REPORT EXCLUDE
TITLE ' CHAR NULLCHAR   PRINTCHAR   NULLINT    PRINTINT'
TITLE ' ---- --------   ---------   --------   --------'
PRINT 1 CHAR 2 NULLCHAR 6 PRINTCHAR 2 NULLINT 2 PRINTINT
END

<Please see attached file for image>

src="/servlet/servlet.FileDownload?file=0150c000004AKrEAAW" alt="Figure 2" width="550" height="116" border="0">

Notice the defined fields PRINTCHAR and PRINTINT now identify the null data.

WHERE Predicate

When adding a WHERE predicate to the SQL SELECT to select records based on null (or not null) columns, you still need to provide ?fieldname handling in the request to identify the null values.

Using the previous CA Earl request with the following series of modified SELECT statements with added WHERE predicates, notice the data which was retrieved from SQL and the resulting values:

TESTNULL: DB SQL 'SELECT CHAR, NULLINT, NULLCHAR '
'FROM creator.TESTNULL '
'WHERE NULLCHAR IS NOT NULL'

<Please see attached file for image>

src="/servlet/servlet.FileDownload?file=0150c000004AKrFAAW" alt="Figure 3" width="550" height="116" border="0">

TESTNULL: DB SQL 'SELECT CHAR, NULLINT, NULLCHAR '
'FROM creator.TESTNULL '
'WHERE NULLCHAR IS NULL'

<Please see attached file for image>

src="/servlet/servlet.FileDownload?file=0150c000004AKrGAAW" alt="Figure 4" width="550" height="88" border="0">

Notice above the NULLINT value 32770. As there was no prior record value to carry forward, this value is unpredictable (garbage).

TESTNULL: DB SQL 'SELECT CHAR, NULLINT, NULLCHAR '
'FROM creator.TESTNULL '
'WHERE NULLINT IS NOT NULL'

<Please see attached file for image>

src="/servlet/servlet.FileDownload?file=0150c000004AKrHAAW" alt="Figure 5" width="550" height="116" border="0">

TESTNULL: DB SQL 'SELECT CHAR, NULLINT, NULLCHAR '
'FROM creator.TESTNULL '
'WHERE NULLINT IS NULL'

<Please see attached file for image>

src="/servlet/servlet.FileDownload?file=0150c000004AKrIAAW" alt="Figure 6" width="550" height="87" border="0">

 

 

Additional Information

For more information about writing CA Earl requests, refer to the publications, Advantage CA-Earl User Guide and Advantagte CA-Earl Reference Guide at

https://support.ca.com/cadocs/7/b002371e.pdf

https://support.ca.com/cadocs/7/b002361e.pdf