Hi all,
For Progress/Oracle schema holders, is there documentation/procedures on how
to:
1) create a delta.df
2) apply this to the schema holder and oracle db
Most responses from the PEG indicate the procedures (if any) are vague and
tools are still buggy.
Thanks very much, Darren Ko
Darren,
It's really easy - you generate an incremental .DF between 2 Progress DBs ("proper"
DBs, not schema holders) and apply it to the schema holder DB.
Then you run the Dataserver utility "generate delta SQL Progress to Oracle".
It takes that .DF and generates some SQL (DDL) that you run against your Oracle
DB.
HTH
Nick
Hi Nick,
Is there a proper way to convert a Schema Holder back to a plain Progress
database (without dumping the .df and hacking away the FOREIGN clauses)?
Can you please confirm these steps?
1. Generate delta.df b/w 2 Progress db
2. Connect to schema holder
3. Run "Generate delta SQL Progress to Oracle" - Input : delta.df
- Output: delta_sh.df, delta.sql
4. Apply delta_sh.df to schema holder
5. Apply delta.sql to Oracle database
Is it correct to assume you don't apply the plain Progress delta.df directly
against the schema holder, but use the schema holder version delta_sh.df?
Thanks for your help,
Darren Ko
"Nick Williamson" wrote:
>
>Darren,
>
>It's really easy - you generate an incremental .DF between 2 Progress
>DBs ("proper"
>DBs, not schema holders) and apply it to the schema holder DB.
>
>Then you run the Dataserver utility "generate delta SQL Progress to Oracle".
>It takes that .DF and generates some SQL (DDL) that you run against your
>Oracle
>DB.
>
>HTH
>Nick
>
Hi Nick,
Platform: Progress 9.1D05 on HP/UX 11, Oracle 8.1.7 64-bit
Did more testing on this ... the main problem is after I load the .sql (DDL),
my test program doesn't work anymore, and returns:
ORACLE error -911 see "ORACLE Error Messages and Codes Manual". (1252)
invalid character
The scenario is this, I have a simple test db which I used to create a schema
holder. My test program successfully creates new records through the schema
holder/dataserver.
I then cloned the plain Progress database and made changes, then produced a
delta.df b/w them. I ran this thru "Generate delta SQL Progress to Oracle"
utility to produce:
- delta_sh.df
- delta_sh.sql
After loading these into the Schema Holder (via Progress dictionary) and
Oracle (via sqlplus), my test program cannot create reocrds anymore (no-lock
reads are ok), crashing out with the following error:
ORACLE error -911 see "ORACLE Error Messages and Codes Manual". (1252)
invalid character
This is similar to the Progress bug discovered earlier (since fixed in 9.1D05)
when you CTRL-C during a Progress Dataserver session, and later on tries to
insert a record, the dataserv.lg with qt_debug, CURSOR crashes out at this
point with rc = 911:
14:20:56 OCI call oparse sqlcrc = 3072
14:20:56 SELECT BOO.USP_V## _seq.nextval FROM sys.dual < ERROR!!!!!!!
14:20:56 OCI call oexfet rc = 911
When everything was still working, the dataserv.lg had this instead:
14:25:12 OCI call oparse
Have a suspicious feeling it's something to do with the "USP_V##" phrases in
both the delta_sh.df and delta_sh.sql. This view is not in the original
schema holder/dataserver when it was first created, not sure why this is gets
created everytime during delta's.
Thanks again, Darren
Contents of miscellaneous delta files ...
RENAME FIELD "usp_char2" OF "usp" TO "usp_char2b"
RENAME FIELD "usp_int2" OF "usp" TO "usp_int2b"
RENAME FIELD "usp_dec2" OF "usp" TO "usp_dec2b"
RENAME FIELD "usp_date2" OF "usp" TO "usp_date2b"
RENAME FIELD "usp_log2" OF "usp" TO "usp_log2b"
DROP FIELD "usp_char3" OF "usp"
DROP FIELD "usp_int3" OF "usp"
DROP FIELD "usp_dec3" OF "usp"
DROP FIELD "usp_date3" OF "usp"
DROP FIELD "usp_log3" OF "usp"
UPDATE DATABASE "charlene"
RENAME FIELD "usp_char2" OF "usp" TO "usp_char2b"
RENAME FIELD "usp_int2" OF "usp" TO "usp_int2b"
RENAME FIELD "usp_dec2" OF "usp" TO "usp_dec2b"
RENAME FIELD "usp_date2" OF "usp" TO "usp_date2b"
RENAME FIELD "usp_log2" OF "usp" TO "usp_log2b"
DROP FIELD "usp_char3" OF "usp"
DROP FIELD "usp_int3" OF "usp"
DROP FIELD "usp_dec3" OF "usp"
DROP FIELD "usp_date3" OF "usp"
DROP FIELD "usp_log3" OF "usp"
UPDATE FIELD "usp_char1" OF "usp"
FOREIGN-POS 1
UPDATE FIELD "usp_char1" OF "usp"
SHADOW-COL "2"
FIELD-MISC15 2
UPDATE FIELD "usp_char2b" OF "usp"
FOREIGN-POS 3
UPDATE FIELD "usp_date1" OF "usp"
FOREIGN-POS 4
UPDATE FIELD "usp_date2b" OF "usp"
FOREIGN-POS 5
UPDATE FIELD "usp_dec1" OF "usp"
FOREIGN-POS 6
UPDATE FIELD "usp_dec2b" OF "usp"
FOREIGN-POS 7
UPDATE FIELD "usp_int1" OF "usp"
FOREIGN-POS 8
UPDATE FIELD "usp_int2b" OF "usp"
FOREIGN-POS 9
UPDATE FIELD "usp_log1" OF "usp"
FOREIGN-POS 10
UPDATE FIELD "usp_log2b" OF "usp"
FOREIGN-POS 11
UPDATE TABLE "usp"
FOREIGN-NAME "USP_V## "
FILE-MISC27 "USP"
FOREIGN-TYPE "VIEW"
PROGRESS-RECID 12
DROP VIEW USP_V## ;
CREATE VIEW USP_V## AS SELECT
USP_CHAR1,
U##USP_CHAR1,
USP_CHAR2,
USP_DATE1,
USP_DATE2,
USP_DEC1,
USP_DEC2,
USP_INT1,
USP_INT2,
USP_LOG1,
USP_LOG2,
PROGRESS_RECID
FROM USP;
"Nick Williamson" wrote:
>
>Darren,
>
>It's really easy - you generate an incremental .DF between 2 Progress
>DBs ("proper"
>DBs, not schema holders) and apply it to the schema holder DB.
>
>Then you run the Dataserver utility "generate delta SQL Progress to Oracle".
>It takes that .DF and generates some SQL (DDL) that you run against your
>Oracle
>DB.
>
>HTH
>Nick
>
Hi Darren,
Your statements about generating/applying delta DF/SQL are absolutely correct.
There's no way I know of to reverse-engineer a "proper" Progress DB from a
schema holder - I think that, as you say, hacking out the "foreign" tokens
(as well as others like FIELD-MISC13 and SHADOW-COL) would be the way to do
it.
HTH
Nick
"Darren Ko" wrote:
>
>Hi Nick,
>
>Is there a proper way to convert a Schema Holder back to a plain Progress
>database (without dumping the .df and hacking away the FOREIGN clauses)?
>
>Can you please confirm these steps?
>
>1. Generate delta.df b/w 2 Progress db
>2. Connect to schema holder
>3. Run "Generate delta SQL Progress to Oracle" - Input : delta.df
- Output: delta_sh.df,
>delta.sql
>4. Apply delta_sh.df to schema holder
>5. Apply delta.sql to Oracle database
>
>Is it correct to assume you don't apply the plain Progress delta.df directly
>against the schema holder, but use the schema holder version delta_sh.df?
>
>Thanks for your help,
>Darren Ko
Hi Darren,
Yikes! You're experiencing errors I haven't seen before, so I don't think I
can be of much assistance. You might like to try typing the SQL query straight
into SQL*PLUS and seeing if it parses/runs successfully.
Other than that, I can only suggest that you speak to tech support.
HTH
Nick
"Darren Ko" wrote:
>
>Hi Nick,
>
>Platform: Progress 9.1D05 on HP/UX 11, Oracle 8.1.7 64-bit
>
>Did more testing on this ... the main problem is after I load the .sql
>(DDL),
>my test program doesn't work anymore, and returns:
>
>ORACLE error -911 see "ORACLE Error Messages and Codes Manual". (1252)
invalid character
>
>The scenario is this, I have a simple test db which I used to create a
>schema
>
>holder. My test program successfully creates new records through the schema
>
>holder/dataserver.
>
>I then cloned the plain Progress database and made changes, then produced
>a
>
>delta.df b/w them. I ran this thru "Generate delta SQL Progress to Oracle"
>
>utility to produce:
>
>- delta_sh.df
>- delta_sh.sql
>
>After loading these into the Schema Holder (via Progress dictionary) and
>
>Oracle (via sqlplus), my test program cannot create reocrds anymore (no-lock
>reads are ok), crashing out with the following error:
>
>ORACLE error -911 see "ORACLE Error Messages and Codes Manual". (1252)
invalid character
>
>This is similar to the Progress bug discovered earlier (since fixed in
>9.1D05)
>
>when you CTRL-C during a Progress Dataserver session, and later on tries
>to
>
>insert a record, the dataserv.lg with qt_debug, CURSOR crashes out at
>this
>
>point with rc = 911:
>
>14:20:56 OCI call oparse 14:20:56 SELECT BOO.USP_V## _seq.nextval FROM sys.dual < ERROR!!!!!!!
>14:20:56 OCI call oexfet rc = 911
>
>When everything was still working, the dataserv.lg had this instead:
>
>14:25:12 OCI call oparse
>
>RENAME FIELD "usp_char2" OF "usp" TO "usp_char2b"
>RENAME FIELD "usp_int2" OF "usp" TO "usp_int2b"
>RENAME FIELD "usp_dec2" OF "usp" TO "usp_dec2b"
>RENAME FIELD "usp_date2" OF "usp" TO "usp_date2b"
>RENAME FIELD "usp_log2" OF "usp" TO "usp_log2b"
>
>DROP FIELD "usp_char3" OF "usp"
>DROP FIELD "usp_int3" OF "usp"
>DROP FIELD "usp_dec3" OF "usp"
>DROP FIELD "usp_date3" OF "usp"
>DROP FIELD "usp_log3" OF "usp"
>
>
>
>UPDATE DATABASE "charlene"
>RENAME FIELD "usp_char2" OF "usp" TO "usp_char2b"
>RENAME FIELD "usp_int2" OF "usp" TO "usp_int2b"
>RENAME FIELD "usp_dec2" OF "usp" TO "usp_dec2b"
>RENAME FIELD "usp_date2" OF "usp" TO "usp_date2b"
>RENAME FIELD "usp_log2" OF "usp" TO "usp_log2b"
>DROP FIELD "usp_char3" OF "usp"
>DROP FIELD "usp_int3" OF "usp"
>DROP FIELD "usp_dec3" OF "usp"
>DROP FIELD "usp_date3" OF "usp"
>DROP FIELD "usp_log3" OF "usp"
>UPDATE FIELD "usp_char1" OF "usp"
FOREIGN-POS 1
>UPDATE FIELD "usp_char1" OF "usp"
SHADOW-COL "2"
FIELD-MISC15 2
>UPDATE FIELD "usp_char2b" OF "usp"
FOREIGN-POS 3
>UPDATE FIELD "usp_date1" OF "usp"
FOREIGN-POS 4
>UPDATE FIELD "usp_date2b" OF "usp"
FOREIGN-POS 5
>UPDATE FIELD "usp_dec1" OF "usp"
FOREIGN-POS 6
>UPDATE FIELD "usp_dec2b" OF "usp"
FOREIGN-POS 7
>UPDATE FIELD "usp_int1" OF "usp"
FOREIGN-POS 8
>UPDATE FIELD "usp_int2b" OF "usp"
FOREIGN-POS 9
>UPDATE FIELD "usp_log1" OF "usp"
FOREIGN-POS 10
>UPDATE FIELD "usp_log2b" OF "usp"
FOREIGN-POS 11
>UPDATE TABLE "usp"
FOREIGN-NAME "USP_V## "
FILE-MISC27 "USP"
FOREIGN-TYPE "VIEW"
PROGRESS-RECID 12
>
>
>
>DROP VIEW USP_V## ;
>CREATE VIEW USP_V## AS SELECT
>USP_CHAR1,
>U##USP_CHAR1,
>USP_CHAR2,
>USP_DATE1,
>USP_DATE2,
>USP_DEC1,
>USP_DEC2,
>USP_INT1,
>USP_INT2,
>USP_LOG1,
>USP_LOG2,
>PROGRESS_RECID
>FROM USP;
>
>
>
>
"Nick Williamson" wrote:
>>
>>Darren,
>>
>>It's really easy - you generate an incremental .DF between 2 Progress
>>DBs ("proper"
>>DBs, not schema holders) and apply it to the schema holder DB.
>>
>>Then you run the Dataserver utility "generate delta SQL Progress to Oracle".
>>It takes that .DF and generates some SQL (DDL) that you run against your
>>Oracle
>>DB.
>>
>>HTH
>>Nick
>>
>