RE: Table Definitions DDS or SQL?

Rob,

I must thank you for the information you provided on this thread, it was very 
timely for me.

Today, it was reported that we had a problem with our data in a Live system. On 
investigation, an overnight batch job failed with a decimal data error and the 
operator cancelled the job and didn't inform anyone.

Following the information that you provided below, I discovered that if I 
used...

CREATE TABLE QTEMP/XXX AS (SELECT * FROM XXX) WITH DATA

...the data was validated as it was written to the QTEMP file. SQL returns 
SQL0406 error message with error type 6 (Error type 6 is numeric data that is 
not valid.), indicating which field/column is in error.

Then, using...

SELECT * FROM XXX WHERE field <> DECIMAL(field,30,9)

...returned QRY2283 error message indicating which relative record number was 
in error.

Sweet.


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On 
Behalf Of rob@xxxxxxxxx
Sent: 06 October 2006 14:07
To: RPG programming on the AS400 / iSeries
Subject: RE: Table Definitions DDS or SQL?

You defined JOEP2 wrong.  JOEP2 has no DDS.
JOEP2 was defined with:
CRTPF FILE(ROB/JOEP2) RCDLEN(10)
It only has one value.  Define it that way and you will not get that message.

Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





"McGovern, Sean" <Sean.McGovern@xxxxxxxxxxxxxxxxxxxxxxx> 
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
10/06/2006 08:50 AM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


To
"RPG programming on the AS400 / iSeries" <rpg400-l@xxxxxxxxxxxx>
cc

Subject
RE: Table Definitions DDS or SQL?






Rob,

I obtain the error message 'Statement contains wrong number of values.' 
within SQL when performing INSERT INTO ROB/JOEP2 VALUES('NOWISTHETI') ?

How did you manage to use SQL to insert invalid data ?

Thanks,
Sean

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] 
On Behalf Of rob@xxxxxxxxx
Sent: 05 October 2006 21:43
To: RPG programming on the AS400 / iSeries
Subject: Re: Table Definitions DDS or SQL?

Like Denis, most of our files are defined using DDS.  But that's packaged 
software for you.
I define my new files using SQL.  Then again, I am probably the only one.

Here's something to chew over:

Problem with DDS created files.  DDS created files are built to allow crap 


in most fields.  Some legacy thing when people got off grunting, thumping 
their chest and writing batch programs that checked fields down to the 
byte level for data that is right.  Like the first COBOL program I wrote 
in a class - make sure a field that is supposed to be numeric, is.  DDS 
files are fast to write to, but slow to read from because they don't care 
what kind of data you write to them.  But they'll check the data quality 
on a read.  When you think about it, most of your data access is reading, 
right?
SQL created files check data on a write.  Not on a read.
Recently saw a classic example of this.
Compile this:
                R JOEPR
                  MYCHAR         5A
                  MYPACK         9P 2 
with CRTPF FILE(ROB/JOEP) SRCFILE(ROB/QDDSSRC) Run this command:  CRTPF 
FILE(ROB/JOEP2) RCDLEN(10) STRSQL INSERT INTO ROB/JOEP2 
VALUES('NOWISTHETI')

CREATE TABLE ROB/JOEP3
(MYCHAR CHAR (5 ) NOT NULL WITH DEFAULT,  MYPACK DEC (9 , 2) NOT NULL WITH 
DEFAULT) RCDFMT JOEP3R (exit sql)

CPYF FROMFILE(JOEP2) TOFILE(JOEP) MBROPT(*ADD) FMTOPT(*NOCHK)
Notice:  Allowed crap into JOEP.  You have a packed decimal field with 
alpha characters in it.

But CPYF FROMFILE(JOEP2) TOFILE(JOEP3) MBROPT(*ADD) FMTOPT(*NOCHK) Tells 
me to bugger off with CPF5035-Data mapping error on member JOEP3.

Now if I attempt to read the corrupted DDS created file with a simple 
RPGLE cycle program
     FJOEP      IP   E             DISK
     FQSYSPRT   O    F   80        PRINTER
     OQSYSPRT   D
     O                       MYCHAR
     O                                              ' '
     O                       MYPACK 
I will get RNQ0907-Decimal-data error occurred.

Verifying that DDS files check data integrity on read.  SQL created files 
check data integrity on write.

Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





"Denis Robitaille" <denis_robitaille@xxxxxxxxxxxx> 
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
10/05/2006 04:08 PM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


To
"RPG programming on the AS400 / iSeries" <rpg400-l@xxxxxxxxxxxx>
cc

Subject
Rép. : Table Definitions DDS or SQL?






Most of our files are defined using DDS. But all new files are defined
as SQL tables.

I have grey hair so I like DDS. But some kind of fields can not be
defined in DDS (like blob I think). If only for that, I agree with our
choice of SQL.

Mike <koldark@xxxxxxxxx> 2006-10-05 15:38 >>>

/me opens can of worms

Hey, I just thought of something. Do most people still use DDS for
file
descriptions or have you moved to SQL tables? I can see pros and cons
for
both. Thoughts?