Ideal situtation for a schemaHolder (schema-image) for ORACLE

[Chris Kelleher]

Since I am not at a level where I can speak authoritatively on this
matter. I will speak in ASSumptions, we all know how dangerous that
can be.


I look at our ORACLE DB that was loaded with the definitions in a
PROGRESS DB. I see all these U## fields for case-insensitve indexes.

First Question again as I asked before. Are the U## fields for the
sake of the PROGRESS dataserver or ORACLE? Internally, only to ORACLE
itself I cannot understand why they are there. It's like redundant
information. Strictly for ORACLE'S use it is information it does NOT
need. However, in conjunction with a Dataserver I understand their
function. PROGRESS NEEDS those fields and indexes, NOT ORACLE.

The PROGRESS DataServer For ORACLE Guide Chap2 on Case-insensitve
indexes under Modifying Tables to Support Case-insensitive Indexes, I
think, should read: "To use case-insensitive indexes [THAT THE
PROGRESS DATASERVER REQUIRES] with an ORACLE database, make the
following changes..."

It seems like a whole lot of overhead that ORACLE does not need to
handle: triggers to set up to add, update, or delete the U## fields
when ever the original field is used; have two fields i.e. U##emp-id
and emp-id for each field if it is used as a component of an index;
extra indexes, one for PROGRESS and one for a java GUI client let's
say; etc.


My big ASSumption is this then: suppose I have a nice clean ORACLE
database that has all the tables, fields, and indexes I need; that is,
nothing that even hints that PROGRESS exists in this known universe.
Suddenly PROGRESS and 4GL are born unto this universe. I see it, I
like it, I want to use it... against my ORACLE DB. Based on that, can
I not create a schema-image of that ORACLE DB that I can then place in
the schema Holder that would then be used by the Dataserver?

The schema holder contains information about the data definitions for
the ORACLE database, now the schema-image has all the information that
PROGRESS cares about. The ORACLE DB does not have all that extra
stuff to load it down.

A java-client accessing the ORACLE db, let us say, no longer has to be
bogged down even more or "wonder" what all these U## fields mean, just
the 4GL via the dataserver schema holder(schema-image) needs to be
concerned with them.

I hope I have made sense and have not been excessively didactic.

Larry
TIA

 

[Chris Kelleher]

Larry,
This is why we have to have it in our oracle schema: (cut this excerpt from ora
tech doc i wrote for our developers - too lazy to write it from scratch)
---
Since we use the case-insensitive index feature of Progress, our ORACLE database
will have an extra field for each indexed field - the U## field. For example,
if vchnum is an indexed field, the ORACLE table will have one field - VCHNUM -
which contains the normal data, and a 2nd field U##VCHNUM for the uppercase
version of the index component. (This only applies to Character fields.)
---

other info you might find helpful (about the U## stuff) :
----


Populating ORACLE from a source other than Progress:

When populating an ORACLE database record from a source other than by Progress
4GL, care must be taken to correctly populate the Progress_RECID and the U##
fields, or the records added will not be accessible by our application.
----

hope this helps,
kt

Larry.Spencer@ccmail.irs.gov (Larry Spencer) on 10/13/99 11:32:53 AM

To: servers@peg.com
cc: (bcc: Katie Eveler/GELCO)

Subject: Ideal situtation for a schemaHolder (schema-image) for ORACLE

Since I am not at a level where I can speak authoritatively on this
matter. I will speak in ASSumptions, we all know how dangerous that
can be.


I look at our ORACLE DB that was loaded with the definitions in a
PROGRESS DB. I see all these U## fields for case-insensitve indexes.

First Question again as I asked before. Are the U## fields for the
sake of the PROGRESS dataserver or ORACLE? Internally, only to ORACLE
itself I cannot understand why they are there. It's like redundant
information. Strictly for ORACLE'S use it is information it does NOT
need. However, in conjunction with a Dataserver I understand their
function. PROGRESS NEEDS those fields and indexes, NOT ORACLE.

The PROGRESS DataServer For ORACLE Guide Chap2 on Case-insensitve
indexes under Modifying Tables to Support Case-insensitive Indexes, I
think, should read: "To use case-insensitive indexes [THAT THE
PROGRESS DATASERVER REQUIRES] with an ORACLE database, make the
following changes..."

It seems like a whole lot of overhead that ORACLE does not need to
handle: triggers to set up to add, update, or delete the U## fields
when ever the original field is used; have two fields i.e. U##emp-id
and emp-id for each field if it is used as a component of an index;
extra indexes, one for PROGRESS and one for a java GUI client let's
say; etc.


My big ASSumption is this then: suppose I have a nice clean ORACLE
database that has all the tables, fields, and indexes I need; that is,
nothing that even hints that PROGRESS exists in this known universe.
Suddenly PROGRESS and 4GL are born unto this universe. I see it, I
like it, I want to use it... against my ORACLE DB. Based on that, can
I not create a schema-image of that ORACLE DB that I can then place in
the schema Holder that would then be used by the Dataserver?

The schema holder contains information about the data definitions for
the ORACLE database, now the schema-image has all the information that
PROGRESS cares about. The ORACLE DB does not have all that extra
stuff to load it down.

A java-client accessing the ORACLE db, let us say, no longer has to be
bogged down even more or "wonder" what all these U## fields mean, just
the 4GL via the dataserver schema holder(schema-image) needs to be
concerned with them.

I hope I have made sense and have not been excessively didactic.

Larry
TIA

 

[Chris Kelleher]

The U## fields are an option, referred to as something like an
"enhanced compatibility" or somesuch.

Meaning, if you want case-insensitive sorting, you need to use
this. Why would you want case-insensitive sorting? From a
progress world, usually because you already wrote an app that
already has case insensitive sorting, because it runs against
a progress db, and you want to keep that feature, but run it
against an oracle db.

You do not have to set it up that way, as long as you are willing to
live with case-sensitive sorting, or have made some sort of case-
insensitive sorting solution from a purely oracle standpoint.

That said, it would be impossible to have the schemaholder itself
manage the case-insensitive index without the Oracle fields - the
schemaholder does not (and should not) contain data, so it cannot
index it. It cannot and should not contain a real index, either - that
would be a train-wreck of a configuration.

Just my $0.02. My opinion only, not my company's, etc. etc.

- John Coleman

>-----Original Message-----
>From: Larry Spencer <Larry.Spencer@ccmail.irs.gov>
>To: servers@peg.com <servers@peg.com>
>Date: Wednesday, October 13, 1999 11:40 AM
>Subject: Ideal situtation for a schemaHolder (schema-image) for ORACLE
>
>
>> Since I am not at a level where I can speak authoritatively on this
>> matter. I will speak in ASSumptions, we all know how dangerous that
>> can be.
>>
>>
>> I look at our ORACLE DB that was loaded with the definitions in a
>> PROGRESS DB. I see all these U## fields for case-insensitve indexes.
>>
>> First Question again as I asked before. Are the U## fields for the
>> sake of the PROGRESS dataserver or ORACLE? Internally, only to ORACLE
>> itself I cannot understand why they are there. It's like redundant
>> information. Strictly for ORACLE'S use it is information it does NOT
>> need. However, in conjunction with a Dataserver I understand their
>> function. PROGRESS NEEDS those fields and indexes, NOT ORACLE.
>>
>> The PROGRESS DataServer For ORACLE Guide Chap2 on Case-insensitve
>> indexes under Modifying Tables to Support Case-insensitive Indexes, I
>> think, should read: "To use case-insensitive indexes [THAT THE
>> PROGRESS DATASERVER REQUIRES] with an ORACLE database, make the
>> following changes..."
>>
>> It seems like a whole lot of overhead that ORACLE does not need to
>> handle: triggers to set up to add, update, or delete the U## fields
>> when ever the original field is used; have two fields i.e. U##emp-id
>> and emp-id for each field if it is used as a component of an index;
>> extra indexes, one for PROGRESS and one for a java GUI client let's
>> say; etc.
>>
>>
>> My big ASSumption is this then: suppose I have a nice clean ORACLE
>> database that has all the tables, fields, and indexes I need; that is,
>> nothing that even hints that PROGRESS exists in this known universe.
>> Suddenly PROGRESS and 4GL are born unto this universe. I see it, I
>> like it, I want to use it... against my ORACLE DB. Based on that, can
>> I not create a schema-image of that ORACLE DB that I can then place in
>> the schema Holder that would then be used by the Dataserver?
>>
>> The schema holder contains information about the data definitions for
>> the ORACLE database, now the schema-image has all the information that
>> PROGRESS cares about. The ORACLE DB does not have all that extra
>> stuff to load it down.
>>
>> A java-client accessing the ORACLE db, let us say, no longer has to be
>> bogged down even more or "wonder" what all these U## fields mean, just
>> the 4GL via the dataserver schema holder(schema-image) needs to be
>> concerned with them.
>>
>> I hope I have made sense and have not been excessively didactic.
>>
>> Larry
>> TIA
>>
>>
>
>