SQL null byte map as a 2-dimensional array

Nulls are something we are all going to have to get use to handling as we move from DDS files to SQL tables, or start using data from DB2 of i or other non-IBM i databases. Recently I was working on a project with a SQL Table with more than 300 columns. If there was no data in a particular field it was null. If you think about it null is a better alternative than blank or zero as those values could be valid values for the contents of that column.

I have written about handling null in SQL in an earlier post, but in this scenario the method I described to define the null byte map was not practical. I am not going to list 300+ subfields in a data structure array for the null indicators for the matching data structure array I would be moving the SQL Table's data into.

But before I explain how I did that in my RPG let me start with the example SQL table.

01 CREATE OR REPLACE TABLE MYLIB.TESTFILE ( 02 FIELD1 CHAR(2) NOT NULL DEFAULT '', 03 FIELD2 CHAR(2), 04 FIELD3 DECIMAL(5,0), 05 FIELD4 DATE, 06 PRIMARY KEY(FIELD1) 07 ) 08 RCDFMT TESTFILER ;

I am sure you have noticed that this Table does not contain 300+ columns. The same method I am going to describe will work with any Table, whether it has 300+ columns or just four.

The Table contains four rows, each with a different number of null values.

FIELD1 FIELD2 FIELD3 FIELD4 01 11 1 12/31/2017 02 22 2 - 03 33 - - 04 - - -

The example RPG program is just going to bring in rows from the Table and move them into a data structure array using a multi row Fetch statement. What I choose to do with the data after that is irrelevant to this subject. It also makes it short enough that I can include it in one part, see below.

01 **free 02 ctl-opt option(*nodebugio:*srcstmt:*nounref) alwnull(*usrctl) ; 03 dcl-ds Data extname('TESTFILE') qualified dim(999) 04 end-ds ; 05 dcl-ds Nulls qualified dim(9999) ; 06 Ind int(5) dim(4) ; 07 end-ds ; 08 dcl-s NbrOfElements int(5) inz(%elem(Data)) ; 09 dcl-s Rows packed(5) ; 10 exec sql SET OPTION COMMIT = *NONE, CLOSQLCSR = *ENDMOD, DATFMT = *ISO ; 11 exec sql DECLARE C0 CURSOR FOR SELECT * FROM TESTFILE ORDER BY FIELD1 FOR READ ONLY ; 12 exec sql OPEN C0 ; 13 exec sql FETCH C0 FOR :NbrOfElements ROWS INTO :Data :Nulls ; 14 exec sql GET DIAGNOSTICS :Rows = ROW_COUNT ; 15 exec sql CLOSE C0 ; 16 *inlr = *on ;

Line 1: As this was written on IBM i 7.3 I should free my RPG.

Line 2: My standard control options.

Lines 3 and 4: This is the definition of the data structure array for the data from the example SQL table. Rather than list all the column by using the External Name keyword, EXTNAME the precompiler will use the table's definition for the data structure's subfields. I have coded that this array has 9,999 elements.

Lines 7 – 9: I need my null byte map array to have the same number of elements as the data's array. The data structure's subfield definition is interesting as it is a definition of an array, with the same number of elements as the number of subfields in the data's data structure. Each element has to be a 5 long integer, null is expressed at -1 and zero means that is not null. What this has created is a two dimensional array, an array within an array.

Line 8: This is the variable I will be using in the Fetch statement for the number of rows to retrieve from the table. I have initialized with the same number as the number of elements in the Data data structure array.

Line 9: I will be using this variable to contain the number of rows I retrieved using the Fetch.

Line 10: I like to put SQL options into my source member to ensure these values are used when the program is run. Otherwise I have to rely on the person compiling the program to change compile command parameters.

Line 11: Here is the definition of the cursor I will be using. I am selecting all the columns from TESTFILE and retrieving them in order by the value in FIELD1.

Line 12: The cursor is opened.

Line 13: Here I am fetching the number of rows held in the variable NbrOfElements, which is the same as the number of elements in the data structure array Data, into the two data structures Data and Nulls. As there is no comma ( , ) separating the two the null byte map (indicators) are moved to Nulls.

Line 14: Here I am getting the number of rows retrieved from the Fetch into the variable Rows.

Line 15: I am done with my cursor so it is time to close it.

I need to give the DATFMT in the SQL options so that the precompiler can calculate the length of the each element of the data structure array. If I do not, see below:

10 exec sql SET OPTION COMMIT = *NONE, CLOSQLCSR = *ENDMOD ;

Then the precompiler does not know what the length of FIELD4, the date field, is and I get a precompile error:

MSG ID SEV RECORD TEXT SQL5011 30 13 Position 48 Host structure array DATA not defined or not usable.

If I am using a two dimensional array I need to access the elements within it like:

Nulls(2).Ind(4)

The above is the null indicator for the equivalent of the second row retrieved from the Table and fourth column within it, FIELD4. With a Table of four columns it is not hard to determine which number to use for the desired array element in the Ind array. With 300+ it is too hard to sit there and count the columns in the file to determine which indicator matches the desired field. I use the compile listing. If I look where the Data data structure is shown I know that the number to the left of the subfield name is the number I need to use for the matching element in the Ind array.

*--------------------------------------------------------- * Data structure . . . . . . : DATA * External format . . . . . : TESTFILER : MYLIB/TESTFILE *--------------------------------------------------------- 000001=D FIELD1 2A 000002=D FIELD2 2A 000003=D FIELD3 5P 0 000004=D FIELD4 10D DATFMT (*ISO-)

What about the data I retrieved from the Table? First the elements from the Data data structure array:

> EVAL data DATA.FIELD1(1) = '01' DATA.FIELD2(1) = '11' DATA.FIELD3(1) = 00001. DATA.FIELD4(1) = '2017-12-31' DATA.FIELD1(2) = '02' DATA.FIELD2(2) = '22' DATA.FIELD3(2) = 00002. DATA.FIELD4(2) = '0001-01-01' DATA.FIELD1(3) = '03' DATA.FIELD2(3) = '33' DATA.FIELD3(3) = 00000. DATA.FIELD4(3) = '0001-01-01' DATA.FIELD1(4) = '04' DATA.FIELD2(4) = ' ' DATA.FIELD3(4) = 00000. DATA.FIELD4(4) = '0001-01-01' DATA.FIELD1(5) = ' ' DATA.FIELD2(5) = ' ' DATA.FIELD3(5) = 00000. DATA.FIELD4(5) = '0001-01-01'

Even though I know some of those columns are null the Data is just the field types' default value. I have to look in the Nulls for the null byte map indicators to see which columns are really null.

> EVAL nulls NULLS.IND(1,1) = 0 NULLS.IND(1,2) = 0 NULLS.IND(1,3) = 0 NULLS.IND(1,4) = 0 NULLS.IND(2,1) = 0 NULLS.IND(2,2) = 0 NULLS.IND(2,3) = 0 NULLS.IND(2,4) = -1 NULLS.IND(3,1) = 0 NULLS.IND(3,2) = 0 NULLS.IND(3,3) = -1 NULLS.IND(3,4) = -1 NULLS.IND(4,1) = 0 NULLS.IND(4,2) = -1 NULLS.IND(4,3) = -1 NULLS.IND(4,4) = -1 NULLS.IND(5,1) = 16448 NULLS.IND(5,2) = 16448 NULLS.IND(5,3) = 16448 NULLS.IND(5,4) = 16448

When I see -1 I know that indicates that the equivalent subfield in Data is null. In debug the numbers in parentheses ( ( ) ) show the value for the first array, Nulls followed by a comma and then the element in the second array, Ind.

I only retrieved four rows using the Fetch statement, see below:

> EVAL rows ROWS = 00004.

The elements for the fifth and beyond elements in Nulls are unused, which is the value 16448.

I did find this a way of defining the null byte map array a lot easier than entering 300+ subfields.

 

This article was written for IBM i 7.3, and should work for earlier releases and TRs that support RPG free format definitions.