Schema Holder incremental delta.df

Posted by LegacyUser on 27-Feb-2003 22:48

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

All Replies

Posted by LegacyUser on 28-Feb-2003 05:52

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

Posted by LegacyUser on 02-Mar-2003 17:18

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

>

Posted by LegacyUser on 03-Mar-2003 00:09

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

>

Posted by LegacyUser on 10-Mar-2003 07:29

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

Posted by LegacyUser on 10-Mar-2003 07:36

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

>>

>

This thread is closed