TPT Dataconnector Operator Acquisition of Rows - Translation from UTF-8

Tools & Utilities

TPT Dataconnector Operator Acquisition of Rows - Translation from UTF-8

Hello, everyone.

I have a data source file encoded in UTF-8 that I have to load into a Teradata table.

I noticed that when I try and run my TPT script without setting the line "USING CHAR SET UTF8" when defining the job the data is loaded quickly, but special characters are incorrect.

On the other hand, when I set the CHAR SET as UTF8 when defining the job, the data is loaded correctly, including the special characters, but way slower.

I suspect the Acquisition Phase is taking long to end due the translations.

Is there anything I can do to speed-up this load job and still load the data properly?

The definition of the table is the following:

CREATE MULTISET TABLE P_ODS_STG.TB_COMPOSICAO_CADUNICO ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
CD_FAMILIAR INTEGER NOT NULL,
NU_NIS_INTEGRANTE BIGINT NOT NULL,
CD_CHV_NATURAL_PESSOA INTEGER NOT NULL,
CD_IBGE_FAMILIA INTEGER NOT NULL,
NU_ORDEM_PESSOA BYTEINT NOT NULL,
NU_NIS_RF BIGINT,
NU_ORDEM_RF BYTEINT,
CD_PARENTESCO BYTEINT,
DT_ULTIMA_ALT_DOMIC TIMESTAMP(6) NOT NULL,
DT_INCLUSAO_DOMIC TIMESTAMP(6) NOT NULL,
DT_ULTIMA_ALT_PESSOA TIMESTAMP(6) NOT NULL,
DT_CADASTRAMENTO_MEMB TIMESTAMP(6) NOT NULL,
DT_EXTRACAO_CADASTRO TIMESTAMP(6) NOT NULL,
RF_CARGA INTEGER NOT NULL,
IN_ORIGEM_DADO CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
KEY_PARTITION BYTEINT NOT NULL)
NO PRIMARY INDEX ;

My TPT script is the following:


USING CHAR SET UTF8
DEFINE JOB CARGA_TB_COMPOSICAO_CADUNICO
DESCRIPTION 'Este processo carrega a tabela TB_COMPOSICAO_CADUNICO'
(

DEFINE SCHEMA TB
(
CD_FAMILIAR VARCHAR(500),
NU_NIS_INTEGRANTE VARCHAR(500),
CD_CHV_NATURAL_PESSOA VARCHAR(500),
CD_IBGE_FAMILIA VARCHAR(500),
NU_ORDEM_PESSOA VARCHAR(500),
NU_NIS_RF VARCHAR(500),
NU_ORDEM_RF VARCHAR(500),
CD_PARENTESCO VARCHAR(500),
DT_ULTIMA_ALT_DOMIC VARCHAR(500) ,
DT_INCLUSAO_DOMIC VARCHAR(500) ,
DT_ULTIMA_ALT_PESSOA VARCHAR(500) ,
DT_CADASTRAMENTO_MEMB VARCHAR(500) ,
DT_EXTRACAO_CADASTRO VARCHAR(500) ,
RF_CARGA VARCHAR(500) ,
IN_ORIGEM_DADO VARCHAR(500),
KEY_PARTITION VARCHAR(500)
);

DEFINE OPERATOR SRC_FILE
TYPE DATACONNECTOR PRODUCER
SCHEMA TB
ATTRIBUTES
(
FileName=@nm_arq,
Format='DELIMITED',
OpenMode='Read',
IndicatorMode='N',
OpenQuoteMark='"',
CloseQuoteMark='"',
TextDelimiter='|',
RowErrFileName='/spare/projetos/log/err_tpt/stg_pae_compcadun.tpt.err'
);

DEFINE OPERATOR LOAD_TGT
TYPE LOAD
SCHEMA TB
ATTRIBUTES
(
username=@UsrID,
userPassword=@Pwd,
LogTable='P_ODS_ERR.TB_COMPOSICAO_CADUNICO_LOG',
TargetTable='P_ODS_STG.TB_COMPOSICAO_CADUNICO',
ErrorTable1='P_ODS_ERR.TB_COMPOSICAO_CADUNICO_E1',
ErrorTable2='P_ODS_ERR.TB_COMPOSICAO_CADUNICO_E2',
PrivateLogName='/spare/projetos/log/compcadunicolog',
TdpId=@Srv
);

DEFINE OPERATOR DELETE_ALL
TYPE DDL
ATTRIBUTES
(
username=@UsrID,
userPassword=@Pwd,
TdpId=@Srv,
PrivateLogName='cadun_del_all'
);
STEP PRE_SQL
(
APPLY
(
'DELETE P_ODS_STG.TB_COMPOSICAO_CADUNICO ALL'
)
TO OPERATOR (DELETE_ALL);
);
STEP CARGA
(
APPLY
(
'INSERT INTO P_ODS_STG.TB_COMPOSICAO_CADUNICO
(
CD_FAMILIAR,
NU_NIS_INTEGRANTE,
CD_CHV_NATURAL_PESSOA,
CD_IBGE_FAMILIA,
NU_ORDEM_PESSOA,
NU_NIS_RF,
NU_ORDEM_RF,
CD_PARENTESCO,
DT_ULTIMA_ALT_DOMIC,
DT_INCLUSAO_DOMIC,
DT_ULTIMA_ALT_PESSOA,
DT_CADASTRAMENTO_MEMB,
DT_EXTRACAO_CADASTRO,
RF_CARGA ,
IN_ORIGEM_DADO,
KEY_PARTITION
)
VALUES
(
:CD_FAMILIAR,
:NU_NIS_INTEGRANTE,
:CD_CHV_NATURAL_PESSOA,
:CD_IBGE_FAMILIA,
:NU_ORDEM_PESSOA,
:NU_NIS_RF,
:NU_ORDEM_RF,
:CD_PARENTESCO,
:DT_ULTIMA_ALT_DOMIC,
:DT_INCLUSAO_DOMIC,
:DT_ULTIMA_ALT_PESSOA,
:DT_CADASTRAMENTO_MEMB,
:DT_EXTRACAO_CADASTRO,
:RF_CARGA ,
:IN_ORIGEM_DADO,
:KEY_PARTITION
);'
)
TO OPERATOR
(
LOAD_TGT
)
SELECT
CD_FAMILIAR,
NU_NIS_INTEGRANTE,
CD_CHV_NATURAL_PESSOA,
CD_IBGE_FAMILIA,
NU_ORDEM_PESSOA,
NU_NIS_RF,
NU_ORDEM_RF,
CD_PARENTESCO,
DT_ULTIMA_ALT_DOMIC,
DT_INCLUSAO_DOMIC,
DT_ULTIMA_ALT_PESSOA,
DT_CADASTRAMENTO_MEMB,
DT_EXTRACAO_CADASTRO,
RF_CARGA ,
IN_ORIGEM_DADO,
KEY_PARTITION
FROM OPERATOR
(
SRC_FILE
);
);
);

In advance, I thank you guys for the help.

Best regards.