RE: CCSID and SQL

["Ducrest,Michel-Claude,VEVEY,CT-IP/FS" <Michel-Claude.Ducrest@xxxxxxxxxx>]

Thank you for your information. I will suggest them to my team.

Some more precisions:

1. I cannot change to 65535 because file contain data which were entered
with a specific CCSID and need a conversion to ensure the graphic
representation if the CCSID of another job differs.

2. Referential integrity need to have the same field(s): it means same type,
same length and same CCSID.

Michel-Claude Ducrest
tel. +41 21 924 16 14
fax +41 21 924 2882


-----Original Message-----
From: Vern Hamberg [mailto:vhamberg@xxxxxxxxxxxxxxxxxxxxxxxxx]
Sent: Wednesday,6. August 2003 16:03
To: Midrange Systems Technical Discussion
Subject: RE: CCSID and SQL


A couple ideas:

1. What if you put CCSID 66535 on both fields in the DDS. That's a 
"leave-it-alone" setting, IIRC.

2. Could you set up referential integrity with foreign keys and *DELETE 
*RESTRICT? Then you'd get a message if records exist in the dependent 
files. I don't know the CCSID implications.

3. Or use EXCEPTION JOIN on the key fields - then the only records returned 
are those with nothing in dependent files. Add all the key fields to the 
example, of course. It might be faster to use the key fields only in the 
field list - might allow index-only processing, esp. in the second version 
below.

     select * from master exception join detail1 on master.fld1 = 
detail1.fld1 exception join detail2 on master.fld1 = detail2.fld1

   This is the same, I think, as correlated subqueries in NOT EXISTS
predicates

     select * from master where not exists (select * from detail1 where 
fld1 = master.fld1) and not exists (select * from detail2 where fld1 = 
master.fld1)

4. There's always READ master with SETLL EQ on both detail files. Would 
CCSID matter there?

One thing I recommend to simulate multiple-format logicals with a UNION is 
to use the common key fields, then some identifier for each type of record 
(record indicator in RPGII terms), then a concatenation of all remaining 
fields that I need into a character field. When fetched, I'd use a SELECT 
CASE on the identifier to move that character field to a DS (RPG) or struct 
(C/C++).

I thought you could have VIEWs in the subselects that comprise a UNION. Is 
this not true? I know that only in V5R2 can a VIEW be constructed from a
UNION.

HTH

Vern

At 08:09 AM 8/6/2003 +0200, you wrote:
>Vern,
>
>Our problem is: verify, before deleting a row in a master file, that the
>item is not present in two other files.
>We also try first to use SQL UNION.  But in our case, the two physical
files
>have the same key, but different format and we would use "UNION of VIEW",
>which is not possible. It is the reason we must use logical multi-format.
>CCSID keyword exist at field level, but our application must work anywhere
>in the world, and we cannot hardcode the CCSID value.
>Is there any other solution?
>
>Michel-Claude Ducrest
>tel. +41 21 924 16 14
>fax +41 21 924 2882
>
>
>-----Original Message-----
>From: Vern Hamberg [mailto:vhamberg@xxxxxxxxxxxxxxxxxxxxxxxxx]
>Sent: Tuesday,5. August 2003 16:38
>To: Midrange Systems Technical Discussion
>Subject: Re: CCSID and SQL
>
>
>Michel-Claude
>
>Great question.
>
>My guess is that SQL uses intermediate results, translating the fields in
>question as needed. DDS files need to match - is there a keyword for CCSID
>at field level - I forget.
>
>Of course, a multi-format logical is not the same as JOIN in SQL - the
>logical is more like an SQL UNION. My assumption fits the UNION scenario
>better.
>
>Regards
>
>Vern
>
>At 10:56 AM 8/5/2003 +0200, you wrote:
> >We encountered the following problem:
> >
> >We try to create a logical file with multi-format (I know, it is not the
> >best way). The first physical file referenced by the logical one, has a
> >CCSID of 500, and the second one has a CCSID of 297. So the creation
abort
> >with a CPF or CPD error. That's normal. To be able to create the
> >multi-format, we have to accord both physical file CCSID first. That's
what
> >we do now.
> >
> >My question is the following:
> >SQL join request is able to work on two different CCSID physical file, if
> >the join fields are identical. Why is it not possible with DDS multi
>format?
> >
> >Michel-Claude Ducrest
> >tel. +41 21 924 16 14
> >fax +41 21 924 2882
>
>
>_______________________________________________
>This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
>To post a message email: MIDRANGE-L@xxxxxxxxxxxx
>To subscribe, unsubscribe, or change list options,
>visit: http://lists.midrange.com/mailman/listinfo/midrange-l
>or email: MIDRANGE-L-request@xxxxxxxxxxxx
>Before posting, please take a moment to review the archives
>at http://archive.midrange.com/midrange-l.
>
>_______________________________________________
>This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
>To post a message email: MIDRANGE-L@xxxxxxxxxxxx
>To subscribe, unsubscribe, or change list options,
>visit: http://lists.midrange.com/mailman/listinfo/midrange-l
>or email: MIDRANGE-L-request@xxxxxxxxxxxx
>Before posting, please take a moment to review the archives
>at http://archive.midrange.com/midrange-l.


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