Wednesday, June 24, 2020

Using SQL to validate packed fields

validate packed number fields with sql

When I heard of these three table functions being introduced as part of the latest round of Technology Refreshes, IBM i 7.4 TR2 and 7.3 TR8, I was really interested to know what they did as their names, "Validate data", really piqued my interested. These table functions will validate packed numeric fields at, what I like to call, three levels:

  1. VALIDATE_DATA:  validates at the member level
  2. VALIDATE_DATA_FILE:  validates at the file level
  3. VALIDATE_DATA_LIBRARY:  validates at the library level

These three exist in the SYSTOOLS library.

It is nigh impossible to put bad data into date, time, and timestamp fields in files. But it is not too hard to put bad data into a packed numeric field. While rare, I am seeing this more as data is taken from other types of databases and applications, and loaded into files in IBM i.

I have created a file with a packed field and I have put alphanumeric data into it to show how these table functions can are used. My file, TESTFILE contains two fields the first alphanumeric and the second packed numeric. I created the first record in this file to have alphanumeric data in the numeric field. I need to know the relative record number as these table functions return that as the "key" to identify which record contains the bad numeric field:

SELECT RRN(A) AS "RRN",A.* FROM MYLIB.TESTFILE A

RRN  F001     F002
---  -------  ----
  1  FIRST       -
  2  SECOND      2 
  3  THIRD       3

The hyphen ( - ) indicates the value null for the field F002 in the first record, as a numeric value cannot be returned into the results. Personally, I think Query does a better job of letting me know there is bad data in a field:

RUNQRY *NONE TESTFILE

F001            F002
------- -------------
FIRST   +++++++++++++
SECOND             2
THIRD              3

The first of these three table functions, VALIDATE_DATA, validates the file at the member level, and requires three parameters passed to it:

  1. LIBRARY_NAME:  Library that contains the file I want to test. Has to be the library name, Using the value *LIBL will return an error
  2. FILE_NAME:  File that contains the member to test
  3. MEMBER_NAME:  Name of the member. Could also be the values *FIRST or *LAST

My file contains only one member so I will be using *FIRST for the member name.

There are two ways I can enter the statement for this table function, the first way is with the parameter names:

SELECT *
  FROM TABLE(SYSTOOLS.VALIDATE_DATA(
              LIBRARY_NAME => 'MYLIB',
              FILE_NAME => 'TESTFILE',
              MEMBER_NAME => '*FIRST'))

Using the statement this way the parameters can be entered in any order:

SELECT *
  FROM TABLE(SYSTOOLS.VALIDATE_DATA(
              MEMBER_NAME => '*FIRST',
              LIBRARY_NAME => 'MYLIB',
              FILE_NAME => 'TESTFILE'))

The other way is to pass the parameters in the expected order, library, file, member, without the parameter names:

SELECT *
  FROM TABLE(SYSTOOLS.VALIDATE_DATA(
              'MYLIB','TESTFILE','*FIRST'))

The results are as follows:

                                                           RELATIVE
                            LIBRARY    FILE_     MEMBER    _RECORD
VALIDATE_TIME               _NAME      NAME      _NAME     _NUMBER
--------------------------  ---------  --------  --------  --------
2020-06-03 15:27:44.048101  MYLIB      TESTFILE  TESTFILE         1


SQL_     REASON  COLUMN
WARNING  _CODE   _NAME   WARNING_TEXT
-------  ------  ------  --------------------------------------
    802      6   F002  Data conversion or data mapping error.

What is SQL Warning 802 and reason code 6? Fortunately this is the same as IBM i message SQL0802 which is:

SQL0802                               
Data conversion or data mapping error.

Reason code:
6 -- Numeric data that is not valid.

Just as a heads up. When playing which these table functions occasionally the above message was accompanied by the one below:

                                                           RELATIVE
                            LIBRARY    FILE_     MEMBER    _RECORD
VALIDATE_TIME               _NAME      NAME      _NAME     _NUMBER
--------------------------  ---------  --------  --------  --------
2020-06-03 15:27:44.048817  MYLIB      TESTFILE  -                1


SQL_     REASON  COLUMN
WARNING  _CODE   _NAME   WARNING_TEXT
-------  ------  ------  ----------------------------------
    462      0   QSYS2   Procedure or user-defined function 
                           PARTITION_STATISTICS in QSYS2 
                           returned a warning SQLSTATE.

This message can be ignored.

If I had a multimember file I would just replace *FIRST with one of the member names.

Next is the file level, VALIDATE_DATA_FILE. This table function will check all members in the file passed as a parameter:

SELECT *
  FROM TABLE(SYSTOOLS.VALIDATE_DATA_FILE(
              LIBRARY_NAME => 'MYLIB',
              FILE_NAME => 'TESTFILE'))

SELECT *
  FROM TABLE(SYSTOOLS.VALIDATE_DATA_FILE(
              'MYLIB','TESTFILE'))

It is pretty much the same as the previous table function. It is so similar that its results are the same too, which is why I am not going to show them here.

Last level is the library level, VALIDATE_DATA_LIBRARY. No surprises that the only parameter is the library name:

SELECT *
  FROM TABLE(SYSTOOLS.VALIDATE_DATA_LIBRARY(
              LIBRARY_NAME => 'MYLIB'))

SELECT *
  FROM TABLE(SYSTOOLS.VALIDATE_DATA_LIBRARY(
              'MYLIB'))

As there could be many files in the library running this statement interactively could take some time for the results to be returned. I could create simple CL program to run the statement and output it to a file.

01  PGM

02  DLTF FILE(MYLIB/VALIDLIB)
03  MONMSG MSGID(CPF0000)

04  RUNSQL SQL('CREATE TABLE MYLIB.VALIDLIB AS +
05              (SELECT * +
06               FROM TABLE(SYSTOOLS.VALIDATE_DATA_LIBRARY(+
07               LIBRARY_NAME => ''MYLIB''))) +
08             WITH DATA') +
09           COMMIT(*NC)

10  ENDPGM

Lines 4 – 9: I do use the Run SQL Statement Command, RUNSQL, a lot as I find it easier to write a quick, little CL program than the equivalent in RPG.

Lines 4 – 8: The results from the VALIDATE_DATA_LIBRARY statement are used to create the table VALIDLIB in my library.

After submitting this program to batch, and it completing, I can then run the following SQL statement to see how many bad packed numeric fields I have in files in MYLIB:

SELECT * FROM MYLIB.VALIDLIB

The data says I have two files with bad packed numeric fields:

                                                             RELATIVE
                            LIBRARY    FILE_      MEMBER     _RECORD
VALIDATE_TIME               _NAME      NAME       _NAME      _NUMBER
--------------------------  ---------  --------=  --------   --------
2020-06-06 22:32:00.398852  MYLIB      TESTFILE   TESTFILE          1
2020-06-06 22:32:00.725011  MYLIB      TESTFILE2  TESTFILE2        11


SQL_     REASON  COLUMN
WARNING  _CODE   _NAME      WARNING_TEXT
-------  ------  ---------- --------------------------------------
    802      6   F002     Data conversion or data mapping error.
    802      6   ANOTHERFLD Data conversion or data mapping error.

 

You can learn more about the VALIDATE_DATA, VALIDATE_DATA_FILE, and VALIDATE_DATA_LIBRARY SQL table functions from the IBM website here.

 

This article was written for IBM i 7.4 TR2 and 7.3 TR8.

No comments:

Post a Comment

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.