All Forums Tools
harimon 22 posts Joined 05/09
11 Jul 2010
fastload insertion

hi all,
i am trying for a fastload inside a unix script,where in the table being inserted have six columns ,out of which three columns gets their data from flatfile,while the other three are date columns which have to be manipulated based on the date which comes along with the flat file name.

date manipulation means adding or subtracting months.

i suppose we can't go in for these manipulation inside fastload ,so i have do these manipulation at unix side.but is there any commands for date manipulation in unix.

please kindly advise


robpaller 159 posts Joined 05/09
13 Jul 2010

You should be able to do the date manipulations in the FastLoad script. Have you tried:

INSERT INTO target VALUES (:Field1, :Field2, :Date1, ADD_MONTHS(:Date1, 2), CASE WHEN EXTRACT MONTH FROM :Date1 > 4 THEN ADD_MONTHS(:Date1, -3) ELSE :DATE1 END);

feinholz 1234 posts Joined 05/08
13 Jul 2010

I am not quite sure that the DBS FastLoad protocol will allow CASE logic in an INSERT statement. The use of the INSERT statement with FastLoad is very restrictive (DBS restriction, not client restriction).


Jimm 298 posts Joined 09/07
13 Jul 2010

I agree with Steve.
You can use Rob's Insert in Multiload, or put the "raw" columns from the file in a work table and calculate the derived columns on the move to the target table.
Alternatively, just load the columns from the file and put the derived columns in a view.

harimon 22 posts Joined 05/09
19 Jul 2010

Thanks Jimm,yes it works in Mload but not in fastload,it shows "RDBMS error 3618: Expression not allowed in Fast Load ".
Thanks Fein and Rob

cristi_1981 5 posts Joined 09/13
03 Sep 2013

I have encountered the same problem while trying to insert values in the table TB_AGR_PAT_MOB_GRP:
        APPLY (
            'INSERT INTO ' || @DB_SCI_STG ||'.TB_AGR_PAT_MOB_GRP
             ' || @ANNEE ||
             ',' || @MOIS ||
             ',' || '''' || @DAT_ARR_FIC || '''' ||

The TPT output was:

Teradata Parallel Transporter Version
Job log: /opt/teradata/client/14.00/tbuild/logs/x121408-765.out
Job id is x121408-765, running on dmut3
Found CheckPoint file: /opt/teradata/client/14.00/tbuild/checkpoint/x121408LVCP
This is a restart job; it restarts at step LOAD_EXP_TABLE.
Teradata Parallel Transporter DataConnector Version
Teradata Parallel Transporter Load Operator Version
LOAD_TB_AGR_PAT_MOB_GRP: private log not specified
FILE_READER_TB_AGR_PAT_MOB_GRP: TPT19008 DataConnector Producer operator Instances: 1
FILE_READER_TB_AGR_PAT_MOB_GRP: TPT19222 Operator instance 1 processing file 'AGR_PAT_MOB_GRP.dat'.
LOAD_TB_AGR_PAT_MOB_GRP: connecting sessions
LOAD_TB_AGR_PAT_MOB_GRP: preparing target table
LOAD_TB_AGR_PAT_MOB_GRP: entering Acquisition Phase
LOAD_TB_AGR_PAT_MOB_GRP: TPT10508: RDBMS error 3618: Expression not allowed in Fast Load Insert, column MTDSGE.
FILE_READER_TB_AGR_PAT_MOB_GRP: TPT19221 Total files processed: 0.
LOAD_TB_AGR_PAT_MOB_GRP: disconnecting sessions
LOAD_TB_AGR_PAT_MOB_GRP: Total processor time used = '2.89334 Second(s)'
LOAD_TB_AGR_PAT_MOB_GRP: Start : Tue Sep  3 17:06:22 2013
LOAD_TB_AGR_PAT_MOB_GRP: End   : Tue Sep  3 17:06:32 2013
Job step LOAD_EXP_TABLE terminated (status 12)
Job x121408 terminated (status 12)
It seems that TPT does not allow operations like :MTDSGE/100 in the value list. Do you have a solution on this matter, please?
Thank you.

feinholz 1234 posts Joined 05/08
03 Sep 2013

The message is "RDBMS", meaning it came from Teradata.
It is not a TPT error.
You have introduced syntax that is not allowed by Teradata for the FastLoad protocol.
You will have to switch to a different operator.
Try the Update operator.


cristi_1981 5 posts Joined 09/13
04 Sep 2013

Thank you, Steven. Using UPDATE operator I was able to finish successfully the TPT operation.

You must sign in to leave a comment.