All Forums Tools
sharatbalaji 14 posts Joined 02/13
29 Oct 2014
Mload Performance Issue

I have an Informatica mapping trying to load data from a flatfile to a table. The target table uses MLOAD to load the table in one of the database in teradata development database.
Till yesterday the mapping was taking 2 minutes and from today the mapping was running for more than 2 hours with the same source file. I tried to compare the MLOAD loader log and this is what i found out
Below is the output from mload loader log from yesterday:
**** 11:09:11 UTY0826 A checkpoint has been taken, recording that end of file
     has been reached for IMPORT 1 of this MultiLoad Import task.
**** 11:09:12 UTY1812 A checkpoint is being initiated because 10000 output
     records have been sent to the RDBMS.
**** 11:09:12 UTY0817 MultiLoad submitting the following request:
     CHECKPOINT LOADING INTERVAL 1;
**** 11:09:13 UTY0817 MultiLoad submitting the following request:
     USING Ckpt(VARBYTE(1024)) INSERT DB_T_STAG_MEMBXREF_DEV.ML_POL_CLAIMS_LIST
            (Logtype, Seq, MLoadSeq, MLoadImpSeq, MLoadSrcSeq,       
     MiscInt1,MiscInt2,MiscInt3,MiscInt4,       
     MiscInt5,MiscInt6,MiscInt7,MiscInt8,CkptInterval,bytef lag,MLoadCkpt)      
      VALUES (110, 1, 1, 1, 10000, 10000, 0, 10000, 0, 0, 0, 0, 0, 1, 1,
     :Ckpt);
**** 11:09:13 UTY0827 A checkpoint has been taken, recording that input record
     10000 has been processed for IMPORT 1 of this MultiLoad Import task.
**** 11:09:13 UTY1812 A checkpoint is being initiated because 20000 output
     records have been sent to the RDBMS.
**** 11:09:13 UTY0817 MultiLoad submitting the following request:
     CHECKPOINT LOADING INTERVAL 2;
**** 11:09:14 UTY0817 MultiLoad submitting the following request:
     USING Ckpt(VARBYTE(1024)) INSERT DB_T_STAG_MEMBXREF_DEV.ML_POL_CLAIMS_LIST
            (Logtype, Seq, MLoadSeq, MLoadImpSeq, MLoadSrcSeq,       
     MiscInt1,MiscInt2,MiscInt3,MiscInt4,       
     MiscInt5,MiscInt6,MiscInt7,MiscInt8,CkptInterval,bytef lag,MLoadCkpt)      
      VALUES (110, 1, 2, 1, 20000, 20000, 0, 20000, 0, 0, 0, 0, 0, 2, 1,
     :Ckpt);
Below is the output from mload loader log from today:
**** 09:38:15 UTY1812 A checkpoint is being initiated because 10000 output
     records have been sent to the RDBMS.
**** 09:38:22 UTY0817 MultiLoad submitting the following request:
     CHECKPOINT LOADING INTERVAL 1;
**** 09:38:23 UTY0817 MultiLoad submitting the following request:
     USING Ckpt(VARBYTE(1024)) INSERT DB_T_STAG_MEMBXREF_DEV.ML_POL_CLAIMS_LIST
            (Logtype, Seq, MLoadSeq, MLoadImpSeq, MLoadSrcSeq,       
     MiscInt1,MiscInt2,MiscInt3,MiscInt4,       
     MiscInt5,MiscInt6,MiscInt7,MiscInt8,CkptInterval,bytef lag,MLoadCkpt)      
      VALUES (110, 1, 1, 1, 10000, 10000, 0, 10000, 0, 0, 0, 0, 0, 1, 1,
     :Ckpt);
**** 09:38:23 UTY0827 A checkpoint has been taken, recording that input record
     10000 has been processed for IMPORT 1 of this MultiLoad Import task.
**** 09:43:42 UTY1812 A checkpoint is being initiated because 20000 output
     records have been sent to the RDBMS.
**** 09:43:49 UTY0817 MultiLoad submitting the following request:
     CHECKPOINT LOADING INTERVAL 2;
**** 09:43:50 UTY0817 MultiLoad submitting the following request:
     USING Ckpt(VARBYTE(1024)) INSERT DB_T_STAG_MEMBXREF_DEV.ML_POL_CLAIMS_LIST
            (Logtype, Seq, MLoadSeq, MLoadImpSeq, MLoadSrcSeq,       
     MiscInt1,MiscInt2,MiscInt3,MiscInt4,       
     MiscInt5,MiscInt6,MiscInt7,MiscInt8,CkptInterval,bytef lag,MLoadCkpt)      
      VALUES (110, 1, 2, 1, 20000, 20000, 0, 20000, 0, 0, 0, 0, 0, 2, 1,
     :Ckpt);
I am not sure what changed from yesterday to today. Can someone help me to resolve it?
 

Tags:
sharatbalaji 14 posts Joined 02/13
29 Oct 2014

I have an Informatica mapping trying to load data from a flatfile to a table. The target table uses MLOAD to load the table in one of the database in teradata development database.
Till yesterday the mapping was taking 2 minutes and from today the mapping was running for more than 2 hours with the same source file. I tried to compare the MLOAD loader log and this is what i found out
 
Below is the output from mload loader log from yesterday:
 
**** 11:09:11 UTY0826 A checkpoint has been taken, recording that end of file
has been reached for IMPORT 1 of this MultiLoad Import task.
**** 11:09:12 UTY1812 A checkpoint is being initiated because 10000 output
records have been sent to the RDBMS.
**** 11:09:12 UTY0817 MultiLoad submitting the following request:
CHECKPOINT LOADING INTERVAL 1;
**** 11:09:13 UTY0817 MultiLoad submitting the following request:
USING Ckpt(VARBYTE(1024)) INSERT DB_T_STAG_MEMBXREF_DEV.ML_POL_CLAIMS_LIST
(Logtype, Seq, MLoadSeq, MLoadImpSeq, MLoadSrcSeq,
MiscInt1,MiscInt2,MiscInt3,MiscInt4,
MiscInt5,MiscInt6,MiscInt7,MiscInt8,CkptInterval,bytef lag,MLoadCkpt)
VALUES (110, 1, 1, 1, 10000, 10000, 0, 10000, 0, 0, 0, 0, 0, 1, 1,
:Ckpt);
**** 11:09:13 UTY0827 A checkpoint has been taken, recording that input record
10000 has been processed for IMPORT 1 of this MultiLoad Import task.
**** 11:09:13 UTY1812 A checkpoint is being initiated because 20000 output
records have been sent to the RDBMS.
**** 11:09:13 UTY0817 MultiLoad submitting the following request:
CHECKPOINT LOADING INTERVAL 2;
**** 11:09:14 UTY0817 MultiLoad submitting the following request:
USING Ckpt(VARBYTE(1024)) INSERT DB_T_STAG_MEMBXREF_DEV.ML_POL_CLAIMS_LIST
(Logtype, Seq, MLoadSeq, MLoadImpSeq, MLoadSrcSeq,
MiscInt1,MiscInt2,MiscInt3,MiscInt4,
MiscInt5,MiscInt6,MiscInt7,MiscInt8,CkptInterval,bytef lag,MLoadCkpt)
VALUES (110, 1, 2, 1, 20000, 20000, 0, 20000, 0, 0, 0, 0, 0, 2, 1,
:Ckpt);

Below is the output from mload loader log from today:

**** 09:38:15 UTY1812 A checkpoint is being initiated because 10000 output
records have been sent to the RDBMS.
**** 09:38:22 UTY0817 MultiLoad submitting the following request:
CHECKPOINT LOADING INTERVAL 1;
**** 09:38:23 UTY0817 MultiLoad submitting the following request:
USING Ckpt(VARBYTE(1024)) INSERT DB_T_STAG_MEMBXREF_DEV.ML_POL_CLAIMS_LIST
(Logtype, Seq, MLoadSeq, MLoadImpSeq, MLoadSrcSeq,
MiscInt1,MiscInt2,MiscInt3,MiscInt4,
MiscInt5,MiscInt6,MiscInt7,MiscInt8,CkptInterval,bytef lag,MLoadCkpt)
VALUES (110, 1, 1, 1, 10000, 10000, 0, 10000, 0, 0, 0, 0, 0, 1, 1,
:Ckpt);
**** 09:38:23 UTY0827 A checkpoint has been taken, recording that input record
10000 has been processed for IMPORT 1 of this MultiLoad Import task.
**** 09:43:42 UTY1812 A checkpoint is being initiated because 20000 output
records have been sent to the RDBMS.
**** 09:43:49 UTY0817 MultiLoad submitting the following request:
CHECKPOINT LOADING INTERVAL 2;
**** 09:43:50 UTY0817 MultiLoad submitting the following request:
USING Ckpt(VARBYTE(1024)) INSERT DB_T_STAG_MEMBXREF_DEV.ML_POL_CLAIMS_LIST
(Logtype, Seq, MLoadSeq, MLoadImpSeq, MLoadSrcSeq,
MiscInt1,MiscInt2,MiscInt3,MiscInt4,
MiscInt5,MiscInt6,MiscInt7,MiscInt8,CkptInterval,bytef lag,MLoadCkpt)
VALUES (110, 1, 2, 1, 20000, 20000, 0, 20000, 0, 0, 0, 0, 0, 2, 1,
:Ckpt);

I am not sure what changed from yesterday to today. Can someone help me to resolve it?
 

Raja_KT 1246 posts Joined 07/09
29 Oct 2014

Did you talk to your DBA, if he sets priorities? It may be the size has increased by leaps and bounds.....How about network..... my initial thought

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

sharatbalaji 14 posts Joined 02/13
30 Oct 2014

Yes I Spoke with my DBA and he said that he has changed nothing on the settings.
I think my DBA would have installed the MLOAD utility in informatica server and i want to see the settings of the MLOAD loader. I am not sure where to see those settings?

Ivyuan 63 posts Joined 01/10
04 Nov 2014

Comparing with the old output, this part looks suspicious:
**** 09:38:23 UTY0827 A checkpoint has been taken, recording that input record
     10000 has been processed for IMPORT 1 of this MultiLoad Import task.
**** 09:43:42 UTY1812 A checkpoint is being initiated because 20000 output
     records have been sent to the RDBMS.
Was  the data fed in the same way? This could be checked by reviewing the "IMPORT" command in the mload script.
--Ivy.

You must sign in to leave a comment.