AW: AW: SFL Load - SQL vs. LF

Hi Raul,

I suspect that checking for a valid date within RPG has anything to do with
data validation in a physical file!

May be I answered the wrong question?!

Birgitta

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces+birgitta.hauser=lp-gmbh.com@xxxxxxxxxxxx
[mailto:midrange-l-bounces+birgitta.hauser=lp-gmbh.com@xxxxxxxxxxxx] Im
Auftrag von Raul A. Jager W.
Gesendet: Freitag, 9. September 2005 14:32
An: Midrange Systems Technical Discussion
Betreff: 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
>  
>