Wow thanks alot for the extensive description. That would mean I could simply parse the DDL file created with CATALOGEXTRACT USER for "CREATE TABLE" statements and build a list of tablenames for the DATALOAD RepMan commands I have to create. Sounds very easy at least for a complete user extract. I already have the extracted catalog from my SAPDB6.2 database (its a 14MB file!), so I will try that later today with a small java app. The app should be able to take the SAPDB6.2 DDL file and produce
1.) a xload batch file containing all the DATAEXTRACT FOR DATALOAD statements in the right order 2.) a RepMan batch file containing all the DATALOAD statements in the right order 3.) a new RepMan CATALOGLOAD DDL file containing only sequence and view definitions to be loaded later (the tables+indices will be done by DATALOAD first) As far as I understood (correct me) the table with catalog information created with SAVE CATALOG can help in building a list of consistent DATALOAD statements for one or more tables with their dependencies, but I would have to evaluate the table myself just like xload/RepMan do it internally. However correctly evaluating this table sounds like a "cleaner" way than parsing the DDL file, just more work. We should probably put up the table description up on the SAPDB wiki, too. I will try to document each step so we can put up some little migration instructions to the SAPDB wiki (if it works ok). There will certainly be more people wanting to migrate to SAPDB7.x in the future. I'll let you know about my progress or further problems. Thanks alot, Marco ----- Original Message ----- From: "Schildberg, Steffen" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, January 06, 2003 12:19 PM Subject: RE: using xload to migrate from SAPDB6.2 to SAPDB7.x > Hi, > > [Watz wrote] > > > Subject: using xload to migrate from SAPDB6.2 to SAPDB7.x > > > > > > I'm thinking of doing the following to migrate a large (20GB) > > SAPDB6.2 database to 7.x: > > > > on SAPDB6.2: > > - use xload to do a "CATALOGEXTRACT USER" (basically to > > recreate the sequence/view definitions) > > - use xload to do a "DATAEXTRACT FOR DATALOAD ..." for all > > tables of this user (all data belongs to one user) > > > > then: > > - modify the catalog result file to only contain the view and > > sequence definitions (remove all CREATE TABLE/INDEX > > statements since those will be done with DATALOAD for these tables) > > > > on SAPDB7.x: > > - create the target database and user > > - use xload/repm to do a DATALOAD for all the command+data > > files of all tables from the above DATAEXTRACT > > - use CATALOGLOAD to load all the view and seqeunce definitions > > > > Could this actually work? > > > Yes it will. But be aware of some modification due to changes in the > syntax of the xload --> repmcli commands. > We could offer assistance in getting it running. As a result a wiki > could be produced (and docs of course) to make it easier for all those > "walking" the same way. > > > The problem I have here...does the DATALOAD actually perform > > referential integrity checks? > > > Yes it does implicitely check the integrity because it simply performs > mass inserts. So the kernel is the one that does this work. > > > If so, is there a tool or anything that could help in > > bringing all DATALOAD statements for all tables into the > > right order so the referential integrity is not violated? > > > There is a tool and you've already mentioned it: Xload and RepMan do the > job. If you run a 'CATALOGEXTRACT USER' Xload as well as RepMan extract the > catalog in the right order paying attention to the referential integrity. They're > able doing that by issuing the database command: > 'SAVE CATALOG USER INTO <some table name>' and then queriying that table. > So you could either manually parse the created ddl-file and pick all tablenames > in the right order to build DATAEXTRACT commands or you could use the above stmt > to build the DATAEXTRACT commands. The table created looks as follows: > > COLUMNNAME ? MOD ? DATATYPE ? CODE ? LEN ? DEC ? COLUMNPRIVILEGES ? DEFAULT > ?????????????????????????????????????????????????????????????????????????????? > LEVEL ? KEY ? FIXED ? ? 6 ? 0 ? SEL+UPD+ ? ? > CMD_NO ? KEY ? FIXED ? ? 6 ? 0 ? SEL+UPD+ ? ? > SEQ_NO ? KEY ? FIXED ? ? 6 ? 0 ? SEL+UPD+ ? ? > DT_FORMAT ? OPT ? CHAR ? BYTE ? 1 ? ? SEL+UPD+ ? ? > OWNER ? OPT ? CHAR ? ASCII ? 64 ? ? SEL+UPD+ ? ? > TABLENAME ? OPT ? CHAR ? ASCII ? 64 ? ? SEL+UPD+ ? ? > ERROR_CODE ? OPT ? FIXED ? ? 4 ? 0 ? SEL+UPD+ ? ? > CMD ? OPT ? CHAR ? ASCII ? 254 ? ? SEL+UPD+ ? ? > > The column CMD contains either information if the object is a table and what is > defined on that table or it contains the whole definition of the object if it is > a view, synonym or trigger. > The order in which the objects are stored in the table is the order of creation. > A sample is here: > > LEVEL ? CMD_NO ? SEQ_NO ? DT_FORMAT ? OWNER ? TABLENAME ? ERROR_CODE > ????????????????????????????????????????????????????????????????????????????? > 0 ? 1 ? 0 ? ? ? STEFFEN ? DL_TEST ? 0 > 0 ? 2 ? 0 ? ? ? STEFFEN ? LEERTEST ? 0 > 0 ? 3 ? 0 ? ? ? STEFFEN ? LONG_TEST ? 0 > 0 ? 4 ? 0 ? ? ? STEFFEN ? MIGRATION ? 0 > 0 ? 5 ? 0 ? ? ? STEFFEN ? TE_TEST2 ? 0 > 0 ? 6 ? 0 ? ? ? STEFFEN ? TE_REFTEST ? 0 > 0 ? 7 ? 0 ? ? ? STEFFEN ? TE_TEST ? 0 > 1 ? 8 ? 0 ? 010000000 ? STEFFEN ? V_DL_TEST ? 0 > > The CMD column did not fit so I append it here: > > ? CMD ? > ????????????????????????????????????????????????????? > ? TABLE + INDEX ? > ? TABLE ? > ? TABLE ? > ? TABLE + USERKEY ? > ? TABLE ? > ? TABLE + INDEX + PRIMARY KEY ? > ? TABLE + FOREIGN KEY + CONSTRAINT ? > ? CREATE VIEW V_DL_TEST AS SELECT COL2 FROM DL_TEST ? > > Beside the mentioned command there are 2 more commands: one for a single table and > one for all objects: > SAVE CATALOG OF <table name> INTO <some table name> and > SAVE CATALOG ALL INTO <some table name> > > > > I already heard that its not possible to use single files > > created with TABLEEXTRACT USER on SAPDB6.2(or ADABAS D) to > > load them into SAPDB7.x because something in the internal > > format has changed (maybe page size?). I think it would have > > been the easiest thing to to a TABLEEXTRACT USER to get all > > tables into the new DB without having to worry about > > referential integrity. > > > Yes it would be easiest but it isn't possible. The page size changed and the > format of the created data files by Xload and RepMan aren't compatible. > > Regards, > Steffen > -- > Steffen Schildberg > SAP DB Team > SAP Labs Berlin > _______________________________________________ > sapdb.general mailing list > [EMAIL PROTECTED] > http://listserv.sap.com/mailman/listinfo/sapdb.general > _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
