0

When migrating from Solaris servers to Linux servers, isql seemed to return more trailing spaces (in data files / columns) on linux servers than in Solaris servers.

old-isql version : Sybase CTISQL Utility/15.0/P-EBF16309 ESD #16/DRV.15.0.7/i386/Solaris 10/BUILD1500-112/OPT/Sun Oct 12 20:37:16 2008

new-isql version : SAP CTISQL Utility/16.0 PL04/P-EBF23393/DRV.16.0.00.04/Linux Intel/Linux 2.6.18-128.el5 i686/BUILD1600-004/OPT/Fri Aug 22 02:47:30 2014

old-isql-data :

|aaaa  |bbbb  |

new-isql-data :

|aaaa      |bbbb      |

this makes data-loaders based on positions to fail.

is there a parameter somewhere to control these trailing spaces in data columns.

data file is extracted using isql command of sybase. data-loader is importing data into oracle database using the command sqlldr.

Example of position-based loader :

LOAD DATA
INTO TABLE BPSA_TRANSACTION
TRAILING NULLCOLS
(
INSTR_ID POSITION (1:11) "decode(trim(:INSTR_ID),'NULL',NULL,trim(:INSTR_ID))" ,
BK_ID POSITION (12:17) "decode(trim(:BK_ID),'NULL',NULL,trim(:BK_ID))" ,
ACCT_ID POSITION (18:26) "decode(trim(:ACCT_ID),'NULL',NULL,trim(:ACCT_ID))" ,

...etc

Thanks

Noureddine Ettalhi
  • 53
  • 1
  • 7
  • 3
    it may help if you update the question to show some sample data exhibiting the issue; also of interest may be a description of what *`data-loaders`* means in your environment and what it has to do with `isql`; assuming the data in the database is actually the same, another item to check may be the locale setting on the two hosts – markp-fuso Jan 04 '23 at 22:39
  • I have updated the description of topic. – Noureddine Ettalhi Jan 05 '23 at 14:45
  • normal data extraction is typically done with the `bcp` tool; while data extraction with `isql` is doable (I've done it myself plenty of times), it also requires a bit more SQL coding to get the formatting correct; *assuming* no issues with the SQL coding, the next step would be to compare the default character sets (`sp_helpsort`); going from something like `iso_1` to `utf8` can lead to some SQL functions generating 'extra space'; without necessary details (sample output, query to reproduce the output, character set info, `isql` command line flags, etc) I can only guess at the issue – markp-fuso Jan 05 '23 at 16:09

1 Answers1

0

Finally I will opte for this solution : Instead of changing all positions in all data-loader control files to match the new field positions due to extra-spaces, I will rather use separator into control file to parse fields.

The separator was ignored (replaced by space) in current sources presumably for performance reason (as the loader does not have to compute these positions for each record ).

Noureddine Ettalhi
  • 53
  • 1
  • 7