Wednesday, April 29, 2015

Coping with data decimal error when read file

data decimal error pssr fixnbr and monitor group

I am sure we have experienced running a program when it errors with "Decimal-data error occurred", RNQ0907. After analyzing the program I would find that the error is caused by bad data in a field in the input file. Yes, I will correct the bad data, but I might want to have the program continue using a default value and create a dump that can be used for analysis later. Examples of creating dumps can be found in the post: Producing a dump from programs.

How can I flag the record as having an error, give the numeric field a default value, and continue processing?

First let's start with our file, TESTFILE. It contains two fields, see below:

The program will only error if the field containing the bad data is used.

  A          R TESTFILER
  A            FLD1           3P 0
  A            FLD2           5A

I put blanks into FLD1, the numeric field, of the second record of the file:

FLD1 FLD2
1 ONE
  TWO
3 THREE

All I am going to do is to create simple RPG program to read TESTFILE and output to the display, using RPG's DSPLY operation code, the values of the two fields, see below. If you are not familiar with all free RPG line 1 denotes that TESTFILE is being for only input.

01  dcl-f TESTFILE ;

02  dow (1 = 1) ;
03    read TESTFILER ;
04    if (%eof) ;
05      leave ;
06    endif ;

07    dsply ('Record = ' + %char(FLD1) + ' ' + FLD2) ;
08  enddo ;

09  *inlr = *on ;

Which when I run this program the output will be:

  DSPLY  Record = 1 ONE
  Decimal-data error occurred (C G D F).

I can take the option "D" to create a dump, and the program ends.

If I did not need FLD1 I would just remove it from line 7, see below. Now when the program runs it does not error as FLD1 is not used.

07    dsply ('Record = ' + FLD2) ;

So what approaches are there to stop the program erroring and continue?

The first approach I tried was using the *PSSR subroutine. This subroutine is only executed when an error occurs, any error. It is just coded like any other subroutine, the only difference is the second parameter with the ENDSR operation code, line 14.

01  dcl-f TESTFILE ;

02  dow (1 = 1) ;
03    read TESTFILER ;
04    if (%eof) ;
05      leave ;
06    endif ;

07    dsply ('Record = ' + %char(FLD1) + ' ' + FLD2) ;
08  enddo ;

09  dsply ('End of pgm') ;
10  *inlr = *on ;

11  begsr *PSSR ;
12    dump(a) ;
13    dsply ('Oh no! I am in *PSSR') ;
14  endsr '*CANCL' ;

In this example the second parameter is not really optional. If it is blank control passes back to the point where the error happened, and an exception error occurs. The second parameters gives where in the RPG cycle I want to return. For example, If I had used *GETIN, instead of *CANCL, I go to the next input record routine of the RPG cycle. As I am not using the RPG cycle the program returns to the "top" and the first record is read again.

The only option I can use here is *CANCL, this ends the program once the subroutine has finished, see below. Which does not allow the program to continue.

  DSPLY  Record = 1 ONE
  DSPLY  Oh no! I am in *PSSR

In my opinion the worse way that I could do this is by using the FIXNBR option in the Control Options/H-Specs, see line 1 below. When a record is read with an invalid number it is changed to default, zero, automatically.

01  ctl-opt fixnbr(*inputpacked:*zoned) ;

02  dcl-f TESTFILE ;

03  dow (1 = 1) ;
04    read TESTFILER ;
05      if (%eof) ;
06        leave ;
07      endif ;

08    dsply ('Record = ' + %char(FLD1) + ' ' + FLD2) ;
09  enddo ;

10  *inlr = *on ;

The big problem is that I do not know when it happened, see below.

  DSPLY  Record = 1 ONE
  DSPLY  Record = 0 TWO
  DSPLY  Record = 3 THREE

This leaves me ignorant that there is bad data in the file.

In my opinion the best method for capturing these errors is to use the MONITOR operation code. If I place moving FLD1 to itself in a monitor group, see line 8 below, and there is an error it is monitored. The ON-ERROR on line 9, captures the data decimal error, and I can put any logic I want to flag the error and give it a default value so that the program will continue. In this case I want to perform a dump, line 10, before changing the value in FLD1, line 11.

01  dcl-f TESTFILE ;

02  dow (1 = 1) ;
03    read TESTFILER ;
04    if (%eof) ;
05      leave ;
06    endif ;

07    monitor ;
08      FLD1 = FLD1 ;
09    on-error 907 ;
10      dump(a) ;
11      FLD1 = 999 ;
12    endmon ;

13    dsply ('Record = ' + %char(FLD1) + ' ' + FLD2) ;
14  enddo ;

15  *inlr = *on ;

The output looks like:

DSPLY  Record = 1 ONE
DSPLY  Record = 999 TWO
DSPLY  Record = 3 THREE

You can read more about monitor groups in the post: MONITOR for errors in RPG.

Even if you were to use SQL and fetch rows/records from the file, see below, you would still get an error.

01  dcl-ds File_ds extname('TESTFILE') ;
02  end-ds ;

03  exec sql DECLARE C0 CURSOR FOR
                SELECT * FROM TESTFILE ;

04  exec sql OPEN C0 ;

05  dow (1 = 1) ;
06    exec sql FETCH C0 INTO :File_ds ;
07    if (SQLCOD <> 0) ;
08      dsply ('SQLCOD = ' + %char(SQLCOD)) ;
09      dsply ('SQLSTATE = ' + SQLSTATE) ;
10      leave ;
11    endif ;

12    dsply ('Record = ' + %char(FLD1) + ' ' + FLD2) ;
13  enddo ;

14  *inlr = *on ;
15  exec sql CLOSE C0 ;

The output from this program would give me this output:

  DSPLY  Record = 1 ONE  
  DSPLY  SQLCOD = -802   
  DSPLY  SQLSTATE = 22023

Which does not allow me to change the column/field in error and continue as easily as I could using a read operation in RPG.

 

You can learn more about this on the IBM website:

 

This article was written for IBM i 7.2, and should work for earlier releases too.

10 comments:

  1. Similarly, read into a data structure, to determine the field in error.
    http://www.itjungle.com/fhg/fhg031715-story01.html

    Chris Ringer

    ReplyDelete
    Replies
    1. Jon Paris, author of the article you mention, have been swapping emails about this article and the merits of his and my approach to this issue.

      Delete
  2. Ramesh SingampalliApril 29, 2015 at 2:42 PM

    I would suggest SQL tables instead of dds. This bad data because of packed fields. If we use SQL tables, this data will be Will validate at the time of insert. This feature not available in dds.

    ReplyDelete
    Replies
    1. @Ramesh, you can always use a default value in DDS (DFT) and during write to the file the default value is inserted if nothing else arrives.

      Y Vago

      Delete
  3. This is a handy tool to process imported data from outside sources.

    ReplyDelete
  4. Though i like the way you use Monitor method here, isnt it cumbersome to use Monitor method for individual fields? Here, you know that FLD1 has blanks thereby causing the issue. But, what if FLD2 has junk values and this error occurs? Certainly i wouldnt like to use Monitor method for each and individual fields.

    ReplyDelete
  5. How to enter a blank in packed decimal filed

    ReplyDelete
    Replies
    1. Most of the time I see this happen is when the file has been copied from not-IBM i environment.

      The program does a copy file and it can copy alphanumeric into a numeric field.

      Delete
    2. In the above example, you have enter a blank in FLD1 but when I enter a blank I get a 0 in that field.So please let me know how did you enter the blank in the FLD01 which is packed deicimal.

      Delete
    3. This post was written 3 years ago so I cannot remember exactly how I did it.

      If I had to do it again I would alter the hexadecimal value of the field using a well know 3rd party software product. Blank = x'4040'

      Delete

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.