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
