Archives of the TeradataForum

Message Posted: Wed, 15 Nov 2006 @ 13:14:02 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi all,

We're taking all the points made by such distinguished contributors as Michael Larkins, Geoffrey Rommel and Joseph V D'silva but we're having a difficult time trying to get a fixed-length file, containing UTF-8 characters, to work in Fastload.

It will load the records which do not contain UTF-8 characters but, as soon as it encounters a field with UTF-8 characters, the record length as seen by Teradata does not match our Define statement.

We've tried things like making them BYTE rather than CHAR, plus one or two variations (such as changing the Set Record setting), but without success.

The reason we're defining all incoming columns as CHAR is that Oracle (so I'm told by our Oracle expert here) doesn't not have a method for exporting numeric fields in packed format. So, effectively, we see them as a string of numbers.

Below is an excerpt of our Fastload output. In the data example - at which point it failed - Fastload thinks the record length is 4 bytes bigger than the overall length as supplied by the Define statement. This is because, for this entry, there are two UTF-8 characters in CUS_COMPANY_NM.

Are we doing it all wrong ? Or, is it a slight tweak to what we've coded ? I'm really hoping one of you guys have done this sort of thing and knows the solution.

     /***********************************************************/
     0003 set session charset "UTF8";

     **** 04:44:31 Command completed successfully
     **** 04:44:31 Current CLI or RDBMS allows maximum row size: 64K
     **** 04:44:31 Character set for this job: UTF8
     **** 04:44:32 FDL4808 LOGON successful

     0007 SET RECORD TEXT;

     **** 04:44:47 Now set to read 'TEXT' records
     **** 04:44:47 Command completed successfully

     0008 DEFINE
          CUS_ID                (CHAR(12))
          CUS_DELETE_CD         (CHAR(1))
          CUS_UPDT_TD           (CHAR(19),NULLIF='                   ')
          CUS_UPDT_USER_ID      (CHAR(8))
          COU_ISO_ID            (CHAR(2))
          CUS_TITLE_CD          (CHAR(10))
          CUS_FORE_NM           (CHAR(30))
          CUS_INIT_NM           (CHAR(4))
          CUS_SUR_NM            (CHAR(50))
          CUS_COMPANY_NM        (CHAR(80))
     ...


     0009 Show;

          FILE = CNRCUT01.fixed.txt
          CUS_ID                           OFFSET =      0 LEN =    12 CHAR
          CUS_DELETE_CD                    OFFSET =     12 LEN =     1 CHAR
          CUS_UPDT_TD                      OFFSET =     13 LEN =    19 CHAR
          CUS_UPDT_USER_ID                 OFFSET =     32 LEN =     8 CHAR
          COU_ISO_ID                       OFFSET =     40 LEN =     2 CHAR
          CUS_TITLE_CD                     OFFSET =     42 LEN =    10 CHAR
          CUS_FORE_NM                      OFFSET =     52 LEN =    30 CHAR
          CUS_INIT_NM                      OFFSET =     82 LEN =     4 CHAR
          CUS_SUR_NM                       OFFSET =     86 LEN =    50 CHAR
          CUS_COMPANY_NM                   OFFSET =    136 LEN =    80 CHAR
     ...

     **** 04:44:47 Number of recs/msg: 77
     **** 04:44:47 Starting to send to RDBMS with record 1
     **** 04:44:48 RECORD is too long by 4 byte(s)
                   Field     1: xxxxxxxxxx
                   Field     2: N
                   Field     3: 2006-03-29 16:43:00
                   Field     4: userid
                   Field     5: XX
                   Field     6:
                   Field     7:
                   Field     8:
                   Field     9:
                   Field    10: AAAAA-FFFMA??O E CCCCA??O
                   Field    11:
     /***********************************************************/

Many thanks.

Dave Clough
Database Designer
Express ICS

www.tnt.com

	<Prev	Next>		<<First	<Prev	Next>	Last>>