Re: DB2 zoned data issue with SQL

Thanks rob. I had figured it was those pgms with program described files.

Though I do believe we have journaled files.

Can you offer an example perhaps of what to seek out in the journal
entries to detect these culprit pgms?

Jay

Sent from my iPhone

On Oct 18, 2019, at 4:00 PM, Rob Berendt <rob@xxxxxxxxx> wrote:

Ok, here it is. Are you ready for a history lesson?
Back in the day people tried to think in advance of possible columns

they might need. Someone may thought sometime in the future they may need
a column like Yugoslavian currency conversion for example. After awhile
(before/after the breakup of Yugoslavia for example) they may have decided
they no longer have use of that. So some clever person may have decided
that they could use RPG data structures to overlay that column with a
character column and use it to store Yes/No or any number of possible
needs. Actually more common in the S/36 shop where you had no external
file capability for RPG.

IBM, to allow this, does not check for validity upon writes to any table

defined with DDS. However, if you take that same table and define it with
DDL it will do validity checks upon write. Figuring that if you are using
DDL you are no longer doing overlay tricks.

Short answer, redefine the table with DDL and stop this from happening.

You could check your journal receivers to see where the bad data came

from. I would guess though that the percentage of shops whose tables are
defined with DDS, and do not have a replication HA solution, and who are
doing journaling is rather low.

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of

Jay Vaughn

Sent: Friday, October 18, 2019 3:17 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx

Subject: DB2 zoned data issue with SQL

CAUTION: This email originated from outside of the organization. Do not

click links or open attachments unless you recognize the sender and know
the content is safe.

So our database has data output to it daily by older cobol/rpg pgms.
The system has been around FOREVER.

Getting more into sql and have discovered an issue with some data columns
that have "invalid" data values in the zoned data fields.

This does NOT play well with sql. When sql issues an insert using data
from this source, it errors and from my pretty extensive research in the
past, there is no way to "handlle, massage, or prepare" the column values
prior within the same sql statement.

The fix to clean the data is simply a 3 line coded pgm such as...

h alwnull(*inputonly) fixnbr(*zoned)
f filename up e k disk
c update rcdfmt

Thats IT! all rows are processed and re-initialized correctly for the
zoned data columns.

We are at a point with SQL development that we really need to get to the
bottom of this and get it fixed.
Has anyone dealt with this before and what was the actual cause and
solution?

TIA

Jay
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related

questions.

Help support midrange.com by shopping at amazon.com with our affiliate

link: https://amazon.midrange.com

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related

questions.

Help support midrange.com by shopping at amazon.com with our affiliate

link: https://amazon.midrange.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com