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

Document ID:  TEC265249
Last Modified Date:  06/12/2017
{{active ? 'Hide' : 'Show'}} Technical Document Details

Products

  • CA Earl

Releases

  • CA Earl:Release:6.1

Components

  • CA EARL:EARL
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.

Question:

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

Environment:
z/OS
Answer:

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

Figure 1

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

Figure 2

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'

Figure 3

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

Figure 4

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'

Figure 5


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

Figure 6

 

 

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

 

 

Please help us improve!

Will this information enable you to resolve your issue?

Please tell us what we can do better.

{{feedbackText.length ? feedbackText.length : '0'}}/255

{{status}}

Not what you were looking for?

Search Again >

Product Information

Support by Product >

Communities

Join a Community >