JDBC FastLoad Error 1154/1248

Connectivity
Enthusiast

JDBC FastLoad Error 1154/1248

As a part of our Teradata evaluation effort, I have created a java ETL process which attempts to batch load data from an Oracle instance into a Teradata 13.10 Express VM instance.

When I try to use the FASTLOAD option for the JDBC driver, I inevitably get an Error 1154 which is caused (presumably) by an Error 1248.  When I execute the ETL class, I end up with this stack trace:


SQLException:

SQL State: HY000

SQL error code: 1154

java.sql.BatchUpdateException: [Teradata JDBC Driver] [TeraJDBC 13.00.00.02] [Error 1154] [SQLState HY000] A failure occurred while inserting the batch of rows destined for database table "lodstar"."ACCOUNT". Details of the failure can be found in the exception chain that is accessible with getNextException.

    at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeBatchUpdateException(ErrorFactory.java:138)

    at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeBatchUpdateException(ErrorFactory.java:128)

    at com.teradata.jdbc.jdbc.fastload.FastLoadManagerPreparedStatement.executeBatch(FastLoadManagerPreparedStatement.java:2075)

    at BulkTdataOra.main(BulkTdataOra.java:142)

Caused by: com.teradata.jdbc.jdbc_4.util.JDBCException: [Teradata JDBC Driver] [TeraJDBC 13.00.00.02] [Error 1248] [SQLState HY000] Found 2394 faulty row(s) while inserting the batch of rows destined for database table "lodstar"."ACCOUNT"!

    at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeDriverJDBCException(ErrorFactory.java:90)

    at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeDriverJDBCException(ErrorFactory.java:70)

    at com.teradata.jdbc.jdbc.fastload.FastLoadManagerPreparedStatement.executeBatch(FastLoadManagerPreparedStatement.java:2054)

    ... 1 more

getNextException:

com.teradata.jdbc.jdbc_4.util.JDBCException: [Teradata JDBC Driver] [TeraJDBC 13.00.00.02] [Error 1248] [SQLState HY000] Found 2394 faulty row(s) while inserting the batch of rows destined for database table "lodstar"."ACCOUNT"!

    at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeDriverJDBCException(ErrorFactory.java:90)

    at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeDriverJDBCException(ErrorFactory.java:70)

    at com.teradata.jdbc.jdbc.fastload.FastLoadManagerPreparedStatement.executeBatch(FastLoadManagerPreparedStatement.java:2054)

    at BulkTdataOra.main(BulkTdataOra.java:142)

However, when I remove the FASTLOAD option from the JDBC connect string, it works fine (albeit slowly).  The target tables are empty/fresh/newly created, as that appears to be a requirement for FASTLOAD.  But I am always getting the same error.  I am using the 13.10 version of the JDBC libs

Is there a problem in my usage of the FASTLOAD (see excerpt below) ?  Can it (or should it) not use typical JDBC batching?  Does the driver not handle the setObject method?  The error message is not particularly helpful and the code works fine if the driver is not using FASTLOAD.

...

...

Tags (1)
8 REPLIES
Enthusiast

Re: JDBC FastLoad Error 1154/1248

Looks like the code formatting did not work... here is the excerpt without code highlighting:

              while(driverRs.next()){

                  do {

                      curBatch+=1;

                      //for each row:

                          for (int i=rsmd.getColumnCount();i > 0 ; i--){

                              //for each column map the vals into the insert:

//System.err.println(i + " " + rsmd.getColumnName(i)+ " - " + rsmd.getColumnTypeName(i) + " " + driverRs.getObject(i));

                        obj=driverRs.getObject(i);

                        if (obj instanceof oracle.sql.Datum)

                            obj=((oracle.sql.Datum)obj).toJdbc();

                              if (obj != null)

                            tgtPs.setObject(i, obj);

                        else{

//System.err.println("null val for "+i + " "+ obj);

                            tgtPs.setNull(i,rsmd.getColumnType(i));

                        }

                          }

                     tgtPs.addBatch();

                  }while(driverRs.next() && curBatch<=batchSize);

                  totBatch+=curBatch-1;

System.out.println("   committing batch of "+(curBatch-1)+ "\t\ttable total: " + totBatch);  

                  //exec this batch

                  tgtPs.executeBatch();

                  curBatch = 0;

               }

Teradata Employee

Re: JDBC FastLoad Error 1154/1248

The error message indicates that there were problems with 2394 rows.

How many total rows were you attempting to insert?

Enthusiast

Re: JDBC FastLoad Error 1154/1248

in that case, 2394.  It fails for 100% of the rows regardless of the batch size.

Teradata Employee

Re: JDBC FastLoad Error 1154/1248

If every row is failing, then perhaps you can provide the DDL for the destination table, and provide one row of data that doesn't work.

That will help us troubleshoot the problem.

Enthusiast

Re: JDBC FastLoad Error 1154/1248

Sure thing:

Source Oracle DDL:

create table lodstar.ACCOUNT

(

  ov_id                 NUMBER(20) not null,

  pit_start             DATE not null,

  pit_start_yq          NUMBER(5) not null,

  pit_stop              DATE not null,

  ov_creation_id        NUMBER(20) not null,

  ov_creation_date      DATE not null,

  ov_updated_id         NUMBER(20) not null,

  ov_updated_date       DATE not null,

  ov_number             NUMBER(20) not null,

  ov_row_status         VARCHAR2(10) not null,

  uidaccount            NUMBER(19),

  uidcustomer           NUMBER(19),

  accountid             VARCHAR2(64),

  starttime             DATE,

  stoptime              DATE,

  opcocode              VARCHAR2(64),

  juriscode             VARCHAR2(64),

  name                  VARCHAR2(64),

  sic                   VARCHAR2(64),

  accountstatuscode     VARCHAR2(64),

  revenuecode           VARCHAR2(64),

  billingmodeflag       CHAR(1),

  printdetail           CHAR(1),

  fulldaybill           CHAR(1),

  prewindow             NUMBER(5),

  postwindow            NUMBER(5),

  edi                   CHAR(1),

  regioncode            VARCHAR2(64),

  addtime               DATE,

  tcrparticipant        CHAR(1),

  currencycode          VARCHAR2(64),

  owner                 VARCHAR2(64),

  accounttypecode       VARCHAR2(64),

  spclhandlecode        VARCHAR2(64),

  lsuser                VARCHAR2(64),

  lstime                DATE,

  uidmarketparticipant  NUMBER(19),

  uidorganization       NUMBER(19),

  indclassificationcode VARCHAR2(64),

  tok_rtstamp           TIMESTAMP(6) WITH TIME ZONE,

  tok_cscn              NUMBER

);

Teradata target DDL:

CREATE MULTISET TABLE

LODSTAR."ACCOUNT"(

"OV_ID" DECIMAL(20,0) NOT NULL

,"PIT_START" DATE NOT NULL

,"PIT_START_YQ" DECIMAL(5,0) NOT NULL

,"PIT_STOP" DATE NOT NULL

,"OV_CREATION_ID" DECIMAL(20,0) NOT NULL

,"OV_CREATION_DATE" DATE NOT NULL

,"OV_UPDATED_ID" DECIMAL(20,0) NOT NULL

,"OV_UPDATED_DATE" DATE NOT NULL

,"OV_NUMBER" DECIMAL(20,0) NOT NULL

,"OV_ROW_STATUS" VARCHAR(10)  NOT NULL

,"UIDACCOUNT" DECIMAL(19,0) NULL

,"UIDCUSTOMER" DECIMAL(19,0) NULL

,"ACCOUNTID" VARCHAR(64)  NULL

,"STARTTIME" DATE NULL

,"STOPTIME" DATE NULL

,"OPCOCODE" VARCHAR(64)  NULL

,"JURISCODE" VARCHAR(64)  NULL

,"NAME" VARCHAR(64)  NULL

,"SIC" VARCHAR(64)  NULL

,"ACCOUNTSTATUSCODE" VARCHAR(64)  NULL

,"REVENUECODE" VARCHAR(64)  NULL

,"BILLINGMODEFLAG" CHAR(1)  NULL

,"PRINTDETAIL" CHAR(1)  NULL

,"FULLDAYBILL" CHAR(1)  NULL

,"PREWINDOW" DECIMAL(5,0) NULL

,"POSTWINDOW" DECIMAL(5,0) NULL

,"EDI" CHAR(1)  NULL

,"REGIONCODE" VARCHAR(64)  NULL

,"ADDTIME" DATE NULL

,"TCRPARTICIPANT" CHAR(1)  NULL

,"CURRENCYCODE" VARCHAR(64)  NULL

,"OWNER" VARCHAR(64)  NULL

,"ACCOUNTTYPECODE" VARCHAR(64)  NULL

,"SPCLHANDLECODE" VARCHAR(64)  NULL

,"LSUSER" VARCHAR(64)  NULL

,"LSTIME" DATE NULL

,"UIDMARKETPARTICIPANT" DECIMAL(19,0) NULL

,"UIDORGANIZATION" DECIMAL(19,0) NULL

,"INDCLASSIFICATIONCODE" VARCHAR(64)  NULL

,"TOK_RTSTAMP" TIMESTAMP(6) WITH TIME ZONE NULL

,"TOK_CSCN" DECIMAL(38,0) NULL)

PRIMARY INDEX("UIDACCOUNT");

 Oracle select String:

select "OV_ID","PIT_START","PIT_START_YQ","PIT_STOP","OV_CREATION_ID","OV_CREATION_DATE","OV_UPDATED_ID","OV_UPDATED_DATE","OV_NUMBER","OV_ROW_STATUS","UIDACCOUNT","UIDCUSTOMER","ACCOUNTID","STARTTIME","STOPTIME","OPCOCODE","JURISCODE","NAME","SIC","ACCOUNTSTATUSCODE","REVENUECODE","BILLINGMODEFLAG","PRINTDETAIL","FULLDAYBILL","PREWINDOW","POSTWINDOW","EDI","REGIONCODE","ADDTIME","TCRPARTICIPANT","CURRENCYCODE","OWNER","ACCOUNTTYPECODE","SPCLHANDLECODE","LSUSER","LSTIME","UIDMARKETPARTICIPANT","UIDORGANIZATION","INDCLASSIFICATIONCODE",cast("TOK_RTSTAMP" as timestamp) "TOK_RTSTAMP","TOK_CSCN" from LODSTAR.ACCOUNT where rownum < 100000

 Teradata insert String:

insert into LODSTAR."ACCOUNT" ("OV_ID","PIT_START","PIT_START_YQ","PIT_STOP","OV_CREATION_ID","OV_CREATION_DATE","OV_UPDATED_ID","OV_UPDATED_DATE","OV_NUMBER","OV_ROW_STATUS","UIDACCOUNT","UIDCUSTOMER","ACCOUNTID","STARTTIME","STOPTIME","OPCOCODE","JURISCODE","NAME","SIC","ACCOUNTSTATUSCODE","REVENUECODE","BILLINGMODEFLAG","PRINTDETAIL","FULLDAYBILL","PREWINDOW","POSTWINDOW","EDI","REGIONCODE","ADDTIME","TCRPARTICIPANT","CURRENCYCODE","OWNER","ACCOUNTTYPECODE","SPCLHANDLECODE","LSUSER","LSTIME","UIDMARKETPARTICIPANT","UIDORGANIZATION","INDCLASSIFICATIONCODE","TOK_RTSTAMP","TOK_CSCN") values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

Sample values:

"2394","81982383883","7/14/2011 9:20:16 AM","20113","12/30/4712","-9","7/16/2011 8:55:08 PM","-9","7/16/2011 8:55:08 PM","1","I","6393","6313","071420112","7/14/2011","1/1/2050 11:59:59 PM","ERCOT","TEXAS","TEST CRRAH ACCT DEFECT17773 ACQ","","ACTIVE","INVOICE","","","","","","","","7/14/2011 9:20:16 AM","Y","","","","","EIF_APP","7/14/2011 9:20:16 AM","","","","14-JUL-11 09.20.16.000000 AM -05:00","1405444292485"

Thanks

Enthusiast

Re: JDBC FastLoad Error 1154/1248

I believe I found the source of the problem.  When loading with the FASTLOAD connection, the java.sql.Timestamp object will not translate/load into a TIMESTAMP (6) WITH TIME ZONE column. 

The reason I am casting the oracle TS with TZ to TS is because there is no java sql type for TS with TZ (so far as I know)

Is there a teradata extension to JDBC (similar to the oracle.sql.Datum subclasses) that will enable us to use fastload for columns of type TIMESTAMP (x) WITH TIME ZONE  ?

TIA

Teradata Employee

Re: JDBC FastLoad Error 1154/1248

The Teradata JDBC Driver supports the standard JDBC API methods for inserting TIME and TIMESTAMP values into the database WITH TIME ZONE: the PreparedStatement setTime and setTimestamp methods with Calendar argument. These methods with Calendar argument are supported for regular SQL connections, and they are also supported for TYPE=FASTLOAD connections.

Here is a link to the Teradata JDBC Driver documentation on this topic:

http://developer.teradata.com/doc/connectivity/jdbc/reference/current/jdbcug_chapter_2.html#CHDECGBI

Your challenge will be in retrieving the time zone information from the Oracle column. In your Oracle SELECT statement, you may need to cast the Oracle TIMESTAMP WITH TIME ZONE column to VARCHAR in order to obtain both the timestamp value and the time zone value.

Enthusiast

Re: JDBC FastLoad Error 1154/1248

Thanks, I will give that a whirl.