TPT load Varchar(MAX) LOB from SQL Server to Teradata

Tools
Teradata Employee

TPT load Varchar(MAX) LOB from SQL Server to Teradata

Hi everyone.  I'm trying to load SQL server tables to Teradata14 using OleDB via TD's OleLoad tool.  I'm having trouble with attributes defined as VARCHAR(MAX) in SQL server - it seems that this is a LOB data type.  Here is the script that OleLoad is generating:

USING CHAR SET UTF8
DEFINE JOB MyJob
(
DEFINE SCHEMA MySchema
(
ProjectID VARCHAR(36),
WBSElement VARCHAR(72),
Network INTEGER,
Activity SMALLINT,
Item SMALLINT,
BOM VARCHAR(12),
ItemText1 VARCHAR(120),
LongText VARCHAR(63999),
ReserveNo INTEGER,
Material INTEGER,
MaterialText VARCHAR(120),
"Category" VARCHAR(75),
ItmCat VARCHAR(3),
Status VARCHAR(24),
ResPurcReq VARCHAR(3),
PurchReq INTEGER,
PO VARCHAR(3),
ProfCenter VARCHAR(21),
UnloadingPoint VARCHAR(75),
OUn VARCHAR(9),
Mvt VARCHAR(3),
Mvt2 VARCHAR(9),
PGp VARCHAR(9),
A VARCHAR(3),
Vendor VARCHAR(30),
StorageLoc VARCHAR(12),
Rd VARCHAR(3),
TimeUnit VARCHAR(9),
GLAccount VARCHAR(18),
MaterialGroup VARCHAR(18),
QtyUnE FLOAT,
UnE VARCHAR(9),
RequirementsQty FLOAT,
BUn VARCHAR(9),
RequirementsDate VARCHAR(30),
QtyReceived FLOAT,
QtyWithdrawn FLOAT,
ShortfallQty FLOAT,
DelTime VARCHAR(9),
QtyAvailable FLOAT,
PriceLCurrency DECIMAL(19,4),
Per VARCHAR(3),
LtstReDate VARCHAR(30),
OperLTO VARCHAR(9),
GRT VARCHAR(3),
AutoID INTEGER
);
DEFINE OPERATOR DDLOperator()
TYPE DDL
ATTRIBUTES
(
VARCHAR PrivateLogName = 'ddl_log',
VARCHAR TdpId = @MyTdpId,
VARCHAR UserName = @MyUserName,
VARCHAR UserPassword = @MyPassword,
VARCHAR WorkingDatabase = @MyDatabase
);
DEFINE OPERATOR DataConnOper()
TYPE DATACONNECTOR PRODUCER
SCHEMA MySchema
ATTRIBUTES
(
VARCHAR AccessModuleName = 'Oledb_Axsmod',
VARCHAR AccessModuleInitStr = 'noprompt jobid=1',
VARCHAR FileName = 'Untitled',
VARCHAR Format = 'Formatted',
VARCHAR EnableScan = 'No',
VARCHAR IndicatorMode = 'Yes',
VARCHAR PrivateLogName = 'producer_log'
);
DEFINE OPERATOR MyConsumer()
TYPE LOAD
SCHEMA MySchema
ATTRIBUTES
(
VARCHAR DateForm = 'IntegerDate',
VARCHAR ErrorTable1 = 'ProjectComponent_errors1',
VARCHAR ErrorTable2 = 'ProjectComponent_errors2',
VARCHAR LogTable = '"CONOCO_SAP"."ProjectComponent_Log"',
VARCHAR PrivateLogName = 'consumer_log',
VARCHAR TargetTable = '"ProjectComponent"',
VARCHAR TdpId = @MyTdpId,
VARCHAR UserName = @MyUserName,
VARCHAR UserPassword = @MyPassword,
VARCHAR WorkingDatabase = @MyDatabase
);
STEP create_the_table
(
APPLY
('CREATE MULTISET TABLE "ProjectComponent" ( ProjectID VARCHAR(12) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
WBSElement VARCHAR(24) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
Network INTEGER NOT NULL ,
Activity SMALLINT NOT NULL ,
Item SMALLINT NOT NULL ,
BOM VARCHAR(4) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
ItemText1 VARCHAR(40) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
LongText VARCHAR(32000) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
ReserveNo INTEGER ,
Material INTEGER ,
MaterialText VARCHAR(40) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
"Category" VARCHAR(25) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
ItmCat CHAR(1) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
Status VARCHAR(8) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
ResPurcReq CHAR(1) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
PurchReq INTEGER ,
PO VARCHAR(1) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
ProfCenter VARCHAR(7) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
UnloadingPoint VARCHAR(25) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
OUn VARCHAR(3) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
Mvt VARCHAR(1) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
Mvt2 CHAR(3) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
PGp VARCHAR(3) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
A VARCHAR(1) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
Vendor VARCHAR(10) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
StorageLoc VARCHAR(4) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
Rd VARCHAR(1) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
TimeUnit VARCHAR(3) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
GLAccount VARCHAR(6) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
MaterialGroup VARCHAR(6) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
QtyUnE FLOAT ,
UnE VARCHAR(3) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
RequirementsQty FLOAT ,
BUn VARCHAR(3) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
RequirementsDate VARCHAR(10) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
QtyReceived FLOAT ,
QtyWithdrawn FLOAT ,
ShortfallQty FLOAT ,
DelTime VARCHAR(3) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
QtyAvailable FLOAT ,
PriceLCurrency DECIMAL(19,4) NOT NULL ,
Per CHAR(1) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
LtstReDate VARCHAR(10) CHARACTER SET UNICODE CASESPECIFIC ,
OperLTO VARCHAR(3) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
GRT CHAR(1) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
AutoID INTEGER NOT NULL );')
TO OPERATOR (DDLOperator);
);
STEP load_the_data
(
APPLY
('INSERT INTO "ProjectComponent" ( :ProjectID, :WBSElement, :Network,
:Activity, :Item, :BOM, :ItemText1,
:LongText, :ReserveNo, :Material,
:MaterialText, :"Category",
:ItmCat, :Status, :ResPurcReq,
:PurchReq, :PO, :ProfCenter,
:UnloadingPoint, :OUn, :Mvt, :Mvt2,
:PGp, :A, :Vendor, :StorageLoc,
:Rd, :TimeUnit, :GLAccount,
:MaterialGroup, :QtyUnE, :UnE,
:RequirementsQty, :BUn,
:RequirementsDate, :QtyReceived,
:QtyWithdrawn, :ShortfallQty,
:DelTime, :QtyAvailable,
:PriceLCurrency, :Per, :LtstReDate,
:OperLTO, :GRT, :AutoID );')
TO OPERATOR (MyConsumer)
SELECT * FROM OPERATOR (DataConnOper);
);
);

I have tried the following:

1) Setting LongText to VARCHAR(31000) in the schema definition and table definition.  This loaded the data, and I could potentially be okay with this BUT - If I set it to anything larger than VARCHAR(31000) i get error 3933 - max possible row length in table is too large.  I have another table with 2 attributes of type VARCHAR(MAX), so this won't work.

2) Setting the data type of the attribute in the target table to CLOB.  This builds the table and reaches the acquisition phase but results in error 3798 - column or character expression is larger than the max size.

Any help is appreciated.

4 REPLIES
Teradata Employee

Re: TPT load Varchar(MAX) LOB from SQL Server to Teradata

Just found this (right after i posted....)

http://developer.teradata.com/tools/articles/large-object-loading-with-teradata-parallel-transporter

will try defferred loading and will report back

Teradata Employee

Re: TPT load Varchar(MAX) LOB from SQL Server to Teradata

Tried deferred loading using this script:

USING CHAR SET UTF8
DEFINE JOB MyJob
(
DEFINE SCHEMA MySchema
(
Network INTEGER,
Activity SMALLINT,
Element VARCHAR(12),
ProjectID VARCHAR(36),
WBSElement VARCHAR(72),
ActivityDesc VARCHAR(150),
ControlKeyText VARCHAR(120),
WorkCntr VARCHAR(24),
Ctrl VARCHAR(12),
Plant VARCHAR(12),
Status VARCHAR(105),
ActyT VARCHAR(60),
Calc VARCHAR(3),
Ca VARCHAR(6),
Cnf VARCHAR(6),
CapInvPr VARCHAR(12),
"ID" INTEGER,
ID2 INTEGER,
confirm VARCHAR(30),
CostElem VARCHAR(18),
Counter INTEGER,
ItemID VARCHAR(60),
KeywordID VARCHAR(21),
ProjLanguage VARCHAR(6),
MaterialGroup VARCHAR(18),
MilestoneDesc VARCHAR(60),
R VARCHAR(3),
ObjCL VARCHAR(30),
Pri VARCHAR(3),
PercentDu FLOAT,
PercentWk FLOAT,
ProfCenter VARCHAR(21),
Psm VARCHAR(3),
Cat VARCHAR(3),
PO VARCHAR(3),
PurchReqNo INTEGER,
PRItm SMALLINT,
PGR VARCHAR(9),
POrg VARCHAR(12),
ReqCC VARCHAR(18),
RequestedBy VARCHAR(30),
PlanNo VARCHAR(30),
Vendor VARCHAR(30),
WorkCenterText VARCHAR(90),
ActivityQuantity FLOAT,
Unit VARCHAR(9),
OrUnit VARCHAR(9),
ActStart VARCHAR(30),
ActFinish VARCHAR(30),
ActDurn FLOAT,
WorkA FLOAT,
Amount DECIMAL(19,4),
Currency VARCHAR(9),
Delay FLOAT,
Earlier FLOAT,
TimeUnit VARCHAR(9),
ErlStrtB CHAR(23),
ErlFinB CHAR(23),
WorkF FLOAT,
FF VARCHAR(9),
Later FLOAT,
LtStartB CHAR(23),
LtFnB CHAR(23),
Past FLOAT,
PlanDelTim VARCHAR(6),
Price DECIMAL(19,4),
PriceUnit VARCHAR(9),
QuantityReceived FLOAT,
RemainWrk FLOAT,
TFl VARCHAR(12),
TimeUnit2 VARCHAR(9),
TimeUnit3 VARCHAR(9),
TimeUnit4 VARCHAR(9),
TimeUnit5 VARCHAR(9),
ProjWork FLOAT,
LongText CLOB AS DEFERRED BY NAME,
Budget DECIMAL(19,4),
ActualCost DECIMAL(19,4),
CommittedCost DECIMAL(19,4)
);
DEFINE OPERATOR DDLOperator()
TYPE DDL
ATTRIBUTES
(
VARCHAR PrivateLogName = 'ddl_log',
VARCHAR TdpId = @MyTdpId,
VARCHAR UserName = @MyUserName,
VARCHAR UserPassword = @MyPassword,
VARCHAR WorkingDatabase = @MyDatabase
);
DEFINE OPERATOR DataConnOper()
TYPE DATACONNECTOR PRODUCER
SCHEMA MySchema
ATTRIBUTES
(
VARCHAR AccessModuleName = 'Oledb_Axsmod',
VARCHAR AccessModuleInitStr = 'noprompt jobid=1',
VARCHAR FileName = 'Untitled',
VARCHAR Format = 'Formatted',
VARCHAR EnableScan = 'No',
VARCHAR IndicatorMode = 'Yes',
VARCHAR PrivateLogName = 'producer_log'
);
DEFINE OPERATOR MyConsumer()
TYPE INSERTER
SCHEMA MySchema
ATTRIBUTES
(
VARCHAR DateForm = 'IntegerDate',
VARCHAR PrivateLogName = 'consumer_log',
VARCHAR TargetTable = '"ProjectActivity"',
VARCHAR TdpId = @MyTdpId,
VARCHAR UserName = @MyUserName,
VARCHAR UserPassword = @MyPassword,
VARCHAR WorkingDatabase = @MyDatabase
);
STEP create_the_table
(
APPLY
('CREATE MULTISET TABLE "ProjectActivity" ( Network INTEGER NOT NULL ,
Activity SMALLINT NOT NULL ,
Element VARCHAR(4) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
ProjectID VARCHAR(12) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
WBSElement VARCHAR(24) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
ActivityDesc VARCHAR(50) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
ControlKeyText VARCHAR(40) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
WorkCntr VARCHAR(8) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
Ctrl CHAR(4) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
Plant CHAR(4) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
Status VARCHAR(35) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
ActyT VARCHAR(20) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
Calc VARCHAR(1) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
Ca VARCHAR(2) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
Cnf VARCHAR(2) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
CapInvPr VARCHAR(4) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
"ID" INTEGER ,
ID2 INTEGER ,
confirm VARCHAR(10) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
CostElem VARCHAR(6) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
Counter INTEGER ,
ItemID VARCHAR(20) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
KeywordID VARCHAR(7) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
ProjLanguage VARCHAR(2) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
MaterialGroup VARCHAR(6) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
MilestoneDesc VARCHAR(20) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
R VARCHAR(1) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
ObjCL VARCHAR(10) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
Pri VARCHAR(1) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
PercentDu FLOAT ,
PercentWk FLOAT ,
ProfCenter VARCHAR(7) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
Psm VARCHAR(1) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
Cat VARCHAR(1) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
PO VARCHAR(1) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
PurchReqNo INTEGER ,
PRItm SMALLINT ,
PGR VARCHAR(3) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
POrg VARCHAR(4) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
ReqCC VARCHAR(6) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
RequestedBy VARCHAR(10) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
PlanNo VARCHAR(10) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
Vendor VARCHAR(10) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
WorkCenterText VARCHAR(30) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
ActivityQuantity FLOAT NOT NULL ,
Unit VARCHAR(3) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
OrUnit VARCHAR(3) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
ActStart VARCHAR(10) CHARACTER SET UNICODE CASESPECIFIC ,
ActFinish VARCHAR(10) CHARACTER SET UNICODE CASESPECIFIC ,
ActDurn FLOAT ,
WorkA FLOAT ,
Amount DECIMAL(19,4) NOT NULL ,
Currency VARCHAR(3) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
Delay FLOAT ,
Earlier FLOAT ,
TimeUnit VARCHAR(3) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
ErlStrtB TIMESTAMP(3) NOT NULL ,
ErlFinB TIMESTAMP(3) NOT NULL ,
WorkF FLOAT ,
FF VARCHAR(3) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
Later FLOAT ,
LtStartB TIMESTAMP(3) NOT NULL ,
LtFnB TIMESTAMP(3) NOT NULL ,
Past FLOAT ,
PlanDelTim VARCHAR(2) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
Price DECIMAL(19,4) NOT NULL ,
PriceUnit VARCHAR(3) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
QuantityReceived FLOAT ,
RemainWrk FLOAT ,
TFl VARCHAR(4) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
TimeUnit2 VARCHAR(3) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
TimeUnit3 VARCHAR(3) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
TimeUnit4 VARCHAR(3) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
TimeUnit5 VARCHAR(3) CHARACTER SET UNICODE NOT NULL CASESPECIFIC ,
ProjWork FLOAT ,
LongText CLOB CHARACTER SET UNICODE NOT NULL ,
Budget DECIMAL(19,4) ,
ActualCost DECIMAL(19,4) ,
CommittedCost DECIMAL(19,4) );')
TO OPERATOR (DDLOperator);
);
STEP load_the_data
(
APPLY
('INSERT INTO "ProjectActivity" ( :Network, :Activity, :Element,
:ProjectID, :WBSElement,
:ActivityDesc, :ControlKeyText,
:WorkCntr, :Ctrl, :Plant, :Status,
:ActyT, :Calc, :Ca, :Cnf, :CapInvPr,
:"ID", :ID2, :confirm, :CostElem,
:Counter, :ItemID, :KeywordID,
:ProjLanguage, :MaterialGroup,
:MilestoneDesc, :R, :ObjCL, :Pri,
:PercentDu, :PercentWk, :ProfCenter,
:Psm, :Cat, :PO, :PurchReqNo,
:PRItm, :PGR, :POrg, :ReqCC,
:RequestedBy, :PlanNo, :Vendor,
:WorkCenterText, :ActivityQuantity,
:Unit, :OrUnit, :ActStart,
:ActFinish, :ActDurn, :WorkA,
:Amount, :Currency, :Delay,
:Earlier, :TimeUnit, :ErlStrtB,
:ErlFinB, :WorkF, :FF, :Later,
:LtStartB, :LtFnB, :Past,
:PlanDelTim, :Price, :PriceUnit,
:QuantityReceived, :RemainWrk, :TFl,
:TimeUnit2, :TimeUnit3, :TimeUnit4,
:TimeUnit5, :ProjWork, :LongText,
:Budget, :ActualCost,
:CommittedCost );')
TO OPERATOR (MyConsumer)
SELECT * FROM OPERATOR (DataConnOper);
);
);

Am now getting this error: I/O Error on file 'Untitled'

Variable <VAR> Field length error.  Expected maximum length 1024, received length 8069 in record 68.

Teradata Employee

Re: TPT load Varchar(MAX) LOB from SQL Server to Teradata

Anyone who finds this, I've found out that TPT currently only supports loading LOB's from flat files.

Teradata Employee

Re: TPT load Varchar(MAX) LOB from SQL Server to Teradata

Hi tstrick4,

On to your first issue, check in the settings section of OleLoad, the default session character set is set to "UTF8" this makes a column size lesser. If you will use session charset "ASCII", you will probably be able to select two columns with VARCHAR(31000).