Right, the problem is that SAPDB 6.2 xload doesn't seem to support FORMATTED BINARY on
DATAEXTRACT.
COMPRESSED or FORMATTED seem to be the only choices. I believe SAPDB6.2 is still
pretty much ADABAS D 6.2 after all.
I had trouble with using COMPRESSED because SAPDB 6.2 would only enclose CHAR/VARCHAR
columns in the given DELIMITER in the data file, other types like FIXED would not get
the delimiters around the value. SAPDB7.4 would however expect all given values to be
enclosed in a delimiter. Fixing all data files would be a painfully slow job.
Nevertheless, by alot of trial and error I found a way to export the data correctly
and in a format that can be imported into 7.4, including LONG columns+data.
This DATAEXTRACT command (example) works on 6.2 and creates data files that can be
imported to 7.4 almost unmodified and without problems concerning binary data in
CHAR/VARCHAR columns or the above troubles with COMPRESSED mode. The drawback of the
increased data file size because of the HEX mode is not as bad compared to the
increased size of FORMATTED mode.
/
DATAEXTRACT * FROM "SUPR9"."INDX" ;
"MANDT" 1 CHAR HEX
"RELID" 2 CHAR HEX
"SRTFD" 3 CHAR HEX
"SRTF2" 4 CHAR HEX
"LOEKZ" 5 CHAR HEX
"SPERR" 6 CHAR HEX
"AEDAT" 7 CHAR HEX
"USERA" 8 CHAR HEX
"PGMID" 9 CHAR HEX
"BEGDT" 10 CHAR HEX
"ENDDT" 11 CHAR HEX
"CLUSTR" 12 CHAR HEX
"CLUSTD" 13 CHAR HEX
OUTFILE 'tab3166ddl.txt'
OUTFILE 'tab3166data.txt'
SEPARATOR ',' DELIMITER ''
LONGFILE "CLUSTD" 'tab3166long1.bin'
/
The only modification needed to the data files would be necessary if the table
contains LONG columns.
SAPDB6.2 exports the LONG columns data pointers in the form of "->322-577" where
SAPDB7.4 complains about the "->" and wants "322-577".
Other than that, some more parsing and corrections on the xload/sapdbloader are
commands necessary, but it works.
I basically did this with a java program:
1.) create a catalog extract on 6.2
2.) parse the extract:
- create a catalog import command file for 7.4 (with all objects, tables, indices,
sequences, views)
- create a data extract command file for xload, containing DATAEXTRACT statements
for each table like above
- create a table reference file linking tablenames and data files (needed in step
4)
3.) extract the data
4.) use the tableref file and parse all result command files (if present) of the
DATAEXTRACT commands
- create a data import command file for use with DATALOAD in 7.4
5.) load the catalog into 7.4 with the catalog import command file from step 2
6.) load the data into 7.4 with the data import command file from step 4 (6.2 data and
longfiles needed)
I tested the whole procedure with single tables and everything seemed to work fine.
Now I'm currently at step 3 extracting the whole 6.2 database which is 30GB in
size...takes a while.
If everything runs successfully I plan to write up a howto and provide the java code
used to parse/create/modify the command and data files.
Regards,
Marco
----- Original Message -----
From: "Schildberg, Steffen" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, August 18, 2003 10:46 AM
Subject: RE: LF in VARCHAR column confuses loadercli
> Hi Marco,
>
> Watz [mailto:[EMAIL PROTECTED] wrote:
> > Subject: Re: LF in VARCHAR column confuses loadercli
> >
> > Ok looks like I found a solution that might work, though it
> > requires me to do alot more parsing.
> > Adding the HEX keyword behind each CHAR field in the field
> > list of DATAEXTRACT/DATALOAD should do the job, at least
> > according to the docs.
> >
> As you wrote you're using FORMATTED data files. So a slight
> and moderate modification could be to use FORMATTED BINARY
> instead of FORMATTED for loading. In this case the Loader
> reads row length bytes for every record and not line by line.
> 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
