Re: LF in VARCHAR column confuses loadercli

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
>