NEED HELP WITH FASTLOAD

[joemrk3]

I am new to Teradata. I am trying to load a pipe "|" delimited file into Tera. Below is the script I trying to run but am getting errors. Error message posted below code.

SESSIONS 1;

ERRLIMIT 25;

.logon ${ADP_LOGIN}

SET RECORD VARTEXT ‘|’;
DEFINE
 rank_no (integer),
 yes_code  (char(2)),
 eng_label (char(100)),
 esp_label (char(100)),
 ataglance_eng (char(6)),
 summary_eng (char(6)),
 transdetail_eng (char(6)),
 ataglance_esp (char(6)),
 summary_esp char(6)),
 transdetail_esp (char(6))
FILENAME=./cat_logo.txt;

SHOW;

BEGIN LOADING PROTO_PSD1.mi_yes16_cat_logo
ERRORFILES PROTO_PSD1.mi_yes16_cat_logo_ERR
CHECKPOINT 1000;
INSERT INTO PROTO_PSD1.mi_yes16_cat_logo (rank_no,yes_code,eng_label,esp_label,ataglance_eng,summary_eng,transdetail_eng,ataglance_esp,summary_esp,transdetail_esp)
VALUES (:rank_no,:yes_code,:eng_label,:esp_label,:ataglance_eng,:summary_eng,:transdetail_eng,:ataglance_esp,:summary_esp,:transdetail_esp);

END LOADING;

LOGOFF;

0004 SET RECORD VARTEXT ‘|’;

**** 10:31:06 Invalid SET RECORD VARTEXT command: ‘|’ is invalid
              after VARTEXT

     The SET RECORD VARTEXT command syntax:

     SET RECORD VARTEXT [[DELIMITER] '|'|'c']
        [DISPLAY_ERRORS ['error_file'] [NOSTOP]
        [TRIM NONE] | [TRIM LEADING|TRAILING|BOTH ['p']]
        [QUOTE NO] | [QUOTE OPTIONAL|YES ['"']|['q' ['r']];
**** 10:31:06 Command not processed
     ===================================================================
     =                                                                 =
     =          Logoff/Disconnect                                      =
     =                                                                 =
     ===================================================================

**** 10:31:06 Logging off all sessions
**** 10:31:06 Total processor time used = '0.1 Seconds'
     .        Start : Wed May 17 10:31:05 2017
     .        End   : Wed May 17 10:31:06 2017
     .        Highest return code encountered = '8'.
**** 10:31:06 FDL4818 FastLoad Terminated

[dnoeth]

Try single quotes '|' instead of ‘|’ (maybe your editor changed the quotes).

Additionally for VARTEXT all input fields must be defined as VARCHARs.

[joemrk3]

Thanks for the reply, I made those changes and now I am getting this error:

SESSIONS 1;

ERRLIMIT 25;

.logon {ADP_LOGON}

DROP TABLE PROTO_PSD1.mi_yes16_cat_logo_err;

.SET RECORD VARTEXT "|";
DEFINE
 rank_no (varchar(10)),
 yes_code  (varchar(2)),
 eng_label (varchar(100)),
 esp_label (varchar(100)),
 ataglance_eng (varchar(6)),
 summary_eng (varchar(6)),
 transdetail_eng (varchar(6)),
 ataglance_esp (varchar(6)),
 summary_esp (varchar(6)),
 transdetail_esp (varchar(6))
FILE=./cat_logo.txt;

SHOW;

BEGIN LOADING PROTO_PSD1.mi_yes16_cat_logo ERRORFILES PROTO_PSD1.mi_yes16_cat_logo_err

INSERT INTO PROTO_PSD1.mi_yes16_cat_logo (rank_no,yes_code,eng_label,esp_label,ataglance_eng,summary_eng,transdetail_eng,ataglance_esp,summary_esp,transdetail_esp)
VALUES (:rank_no,:yes_code,:eng_label,:esp_label,:ataglance_eng,:summary_eng,:transdetail_eng,:ataglance_esp,:summary_esp,:transdetail_esp);

END LOADING;

LOGOFF;

.QUIT;

0004 DROP TABLE PROTO_PSD1.mi_yes16_cat_logo_err;

**** 11:36:55 RDBMS error 3807: Object
              'PROTO_PSD1.mi_yes16_cat_logo_err' does not exist.

0005 .SET RECORD VARTEXT "|";

**** 11:36:55 Now set to read 'Variable-Length Text' records
**** 11:36:55 Delimiter character(s) is set to '|'
**** 11:36:55 Command completed successfully

0006 DEFINE
      rank_no (varchar(10)),
      yes_code  (varchar(2)),
      eng_label (varchar(100)),
      esp_label (varchar(100)),
      ataglance_eng (varchar(6)),
      summary_eng (varchar(6)),
      transdetail_eng (varchar(6)),
      ataglance_esp (varchar(6)),
      summary_esp (varchar(6)),
      transdetail_esp (varchar(6))
     FILE=/users/work223/staging/222_yes/yes_req_2016_tera/matrix/data/cat_logo
     .txt;

**** 11:36:55 FDL4803 DEFINE statement processed

0007 SHOW;

     FILE = /users/work223/staging/222_yes/yes_req_2016_tera/matrix/data/cat_logo.txt
     RANK_NO                          OFFSET =      0 LEN =    10 VARCHAR
     YES_CODE                         OFFSET =     12 LEN =     2 VARCHAR
     ENG_LABEL                        OFFSET =     16 LEN =   100 VARCHAR
     ESP_LABEL                        OFFSET =    118 LEN =   100 VARCHAR
     ATAGLANCE_ENG                    OFFSET =    220 LEN =     6 VARCHAR
     SUMMARY_ENG                      OFFSET =    228 LEN =     6 VARCHAR
     TRANSDETAIL_ENG                  OFFSET =    236 LEN =     6 VARCHAR
     ATAGLANCE_ESP                    OFFSET =    244 LEN =     6 VARCHAR
     SUMMARY_ESP                      OFFSET =    252 LEN =     6 VARCHAR
     TRANSDETAIL_ESP                  OFFSET =    260 LEN =     6 VARCHAR
     TOTAL RECORD LENGTH = 268

0008 BEGIN LOADING PROTO_PSD1.mi_yes16_cat_logo ERRORFILES PROTO_PSD1.mi_yes16_c
     at_logo_err

     INSERT INTO PROTO_PSD1.mi_yes16_cat_logo (rank_no,yes_code,eng_label,esp_l
     abel,ataglance_eng,summary_eng,transdetail_eng,ataglance_esp,summary_esp,t
     ransdetail_esp)
     VALUES (:rank_no,:yes_code,:eng_label,:esp_label,:ataglance_eng,:summary_e
     ng,:transdetail_eng,:ataglance_esp,:summary_esp,:transdetail_esp);

**** 11:36:55 Invalid BEGIN LOADING statement
**** 11:36:55 Expected CHECKPOINT or INDICATORS, found: INTO
     ===================================================================
     =                                                                 =
     =          Logoff/Disconnect                                      =
     =                                                                 =
     ===================================================================

**** 11:36:55 Logging off all sessions
**** 11:36:55 Total processor time used = '0.11 Seconds'
     .        Start : Wed May 17 11:36:54 2017
     .        End   : Wed May 17 11:36:55 2017
     .        Highest return code encountered = '8'.
**** 11:36:55 FDL4818 FastLoad Terminated

[feinholz]

I think the BEGIN LOADING is missing the semi-colon at the end. It also only lists one error table name; you should put 2 names (one for the ET table and one for the UV table).

[joemrk3]

Steve - thanks, that worked. Still getting an error, not sure if this is a coding or data issue?

Here is the recs from file:

1,SU,Utilities,Servicios pzblicos,ICON51,ICON61,ICTL19,ICON51,ICON61,ICTLSS
2,SH,Health Care,Atencisn de la Salud,ICON52,ICON62,ICTL17,ICON52,ICON62,ICTLSQ
3,DR,Pharmacy,Farmacia,ICON52,ICON62,ICTL06,ICON52,ICON62,ICTLSF

ERROR:

0009 BEGIN LOADING PROTO_PSD1.mi_yes16_cat_logo ERRORFILES PROTO_PSD1.mi_yes16_c
     at_logo_err1,PROTO_PSD1.mi_yes16_cat_logo_err2;

**** 12:11:59 Session count 25 returned by the DBS overrides
              user-requested session count
**** 12:12:01 Number of FastLoad sessions requested = 25
**** 12:12:01 Number of FastLoad sessions connected = 25
**** 12:12:01 FDL4808 LOGON successful
**** 12:12:03 RDBMS error 2635: Error tables are invalid OR
              mi_yes16_cat_logo is in an invalid state for Load -
     ===================================================================
     =                                                                 =
     =          Logoff/Disconnect                                      =
     =                                                                 =
     ===================================================================

**** 12:12:03 Logging off all sessions
**** 12:12:03 Total processor time used = '1.43 Seconds'
     .        Start : Wed May 17 12:11:56 2017
     .        End   : Wed May 17 12:12:03 2017
     .        Highest return code encountered = '12'.
**** 12:12:03 FDL4818 FastLoad Terminated

[feinholz · Accepted solution]

Since you want to start over, make sure you drop all of the tables (target table and error tables), recreate the target table, and start over again.

BTW, if this is something new, you should be switching over to using TPT (Teradata Parallel Transporter).

[Donna_OD]

Also you may want to try just putting

SET RECORD VARTEXT;

You don't have to put the "|" in there because it defaults to "|".