Kbase P125728: What Oracle indexes does data server create during protoora migration?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
9/4/2007 |
|
Status: Unverified
GOAL:
What Oracle indexes does data server create during protoora migration?
GOAL:
Does schema holder contain all the indexes in Oracle?
GOAL:
What schema holder indexes are created during protoora migration process?
GOAL:
How to alter existing Oracle tables to support data server operations?
GOAL:
What is the role of Progress_recid in an Oracle data server operations
FACT(s) (Environment):
All Supported Operating Systems
Progress 9.x
OpenEdge 10.x
Oracle DataServer
FIX:
The protoora migration utility create following Oracle schema objects to support FIND PREV/LAST statements and cursor repositioning in an OpenEdge application for this database. If the Oracle table already exists, following procedure need to be executed to maintain proper data server operation
1. Create a sequence generator for the table named table-name_SEQ. Start with 1 and
increment by 1, as shown:
CREATE SEQUENCE table-name_SEQ START WITH 1 INCREMENT BY 1;
2. Add a column to the table named progress_recid. This column holds a number that can
be null. For example:
ALTER TABLE table-name ADD (progress_recid number null);
3. Update the table and set the progress_recid using table-name_SEQ.nextval, as shown:
UPDATE table-name SET progress_recid = table-name_SEQ.nextval;
4. Create a unique index name, table-name##progress_recid, that consists of just the
progress_recid column, as shown:
CREATE UNIQUE INDEX table-name##progress_recid ON table-name (progress_recid);
5. Drop every non-unique index from the table and recreate it using the same components.
Add progress_recid as the last component, as shown:DROP INDEX table-name##index-name;
CREATE INDEX table-name##index-name ON table-name(column-name, progress_recid);
6. To verify the sequence is created
SELECT table-name_SEQ FROM sys.dual;
Following is except from the .sql script generated by migration utility
DROP SEQUENCE customer_SEQ;
CREATE SEQUENCE customer_SEQ START WITH 1 INCREMENT BY 1;
DROP TABLE customer;
CREATE TABLE customer (
CREATE UNIQUE INDEX customer##progress_recid ON customer (progress_recid);
CREATE INDEX customer##countrypost ON customer (U##country, U##postalcode, progress_recid);
CREATE UNIQUE INDEX customer##custnum ON customer (custnum);
CREATE INDEX customer##name ON customer (U##name, progress_recid);
CREATE INDEX customer##salesrep ON customer (U##salesrep, progress_recid);
In schema holder, the progress_recid field is not shown nor is unique index table_name##progress.recid. Data server uses the field and the index internally in the C code layer to support FIND PREV/LAST statements and cursor repositioning in an OpenEdge application for this database,