Re: AW: SFL Load - SQL vs. LF

I usualy check dates like this:

monitor;
   datefiled = %date(alphadate);
on-error,
   cry... :-(
endmon;

If you suspect there may be to many wong dates you can do a faster, but
not complete check:

if %xlate('123456789': '000000000': alphadate) = '0000-00-00'
 probably OK, at laest it has the proper structure..
else
  bad
endif;
___________________________________________________________________________________
Hauser, Birgitta wrote:

Hi,

Is there anyone who can explain this?
Data Validation code!
Or point to the original article?

Data Validation: Just try the following:
1. CREATE a physical file with fixed format, without DDS
  CRTPF FILE(MYLIB/BASEPF) RCDLEN(50)

2. Fill it with some information as follows:
  123456789XXXX
  555666777ABCXXXXX
  998877665BCF
  L­JRWE1234567

3. Create a DDS described file where the first field is packed numeric.
  Example:
  A          R BASEDDSF
   *
   * FELDER
  A            FLNUM1         3P 0       COLHDG('NUMER.FELD 1')
  A            FLNUM2         6P 3       COLHDG('NUMER.FELD 2')
  A            FLALFA        15A         COLHDG('ALPHA FELD 1')

4. Copy File 1 (BASEPF) into File 2 (BASEDDS) as follows:
  CPYF FROMFILE(MYLIB/BASEPF)
       TOFILE(MYLIB/BASEDDS)
       MBROPT(*REPLACE)
       FMTOPT(*NOCHK)

5. If you look into your file BASEDDS with WRKF,
  you'll see all 4 records copied.
  The joblog also confirms that all records are copied.
  If you try to read this file, i.e. with RPG you'll get an error,
  With record 4 because it contains invalid data.
  The date validation does not happen if a record is written (CPYF)
  But if the record is read.

6. Create a table with SQL that has the same description as BASEDDS
  CREATE TABLE MYLIB/BASEDDL (
               FLNUM1 NUMERIC(3, 0) NOT NULL DEFAULT 0 ,
               FLNUM2 NUMERIC(6, 3) NOT NULL DEFAULT 0 ,
               FLALFA CHAR(15)      NOT NULL DEFAULT '' )

7. Try to copy file 1 (BASEPF) into file 3 (BASEDDL)
  like you did it with BASEDDS file.
  CPYF FROMFILE(MYLIB/BASEPF)
       TOFILE(MYLIB/BASEDDL)
       MBROPT(*REPLACE)
       FMTOPT(*NOCHK)

8. If you check the joblob, you'll see that only 3 records are copied
  While the last one is not inserted, because it contains invalid data.
  That means data validation happens when a record (row) is written to
  A SQL described table.
  On the other hand, you can read all records in BASEDDS with SQL, because
  No data validation takes place.

If you compare the number or read operations with the number of update/write
operations, you'll agree, that you can gain performance by simply defining
physical files (Tables) with SQL instead of DDS.

Birgitta