fastload insertion

Tools & Utilities
Enthusiast

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

regards
7 REPLIES
Enthusiast

Re: fastload insertion

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);
Teradata Employee

Re: fastload insertion

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).
-- SteveF
Enthusiast

Re: fastload insertion

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.
Enthusiast

Re: fastload insertion

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
Teradata Employee

Re: fastload insertion

Hello,

I have encountered the same problem while trying to insert values in the table TB_AGR_PAT_MOB_GRP:

         STEP LOAD_EXP_TABLE (

        APPLY (

            'INSERT INTO ' || @DB_SCI_STG ||'.TB_AGR_PAT_MOB_GRP

            (

                ANNEE

                ,MOIS

                ,DAT_ARR_FIC

                ,IDDIIP   

                ,TYPGRP   

                ,MTDSGE   

                ,MTVBFO   

                ,MTASGE   

                ,MTTAVC   

                ,MTTNSG               

                ,TYP_SG   

            )

            VALUES

            (

             ' || @ANNEE ||

             ',' || @MOIS ||

             ',' || '''' || @DAT_ARR_FIC || '''' ||

             ',:IDDIIP

              ,:TYPGRP

              ,:MTDSGE/100

              ,:MTVBFO/100

              ,:MTASGE/100

              ,:MTTAVC/100

              ,:MTTNSG/100

              ,:TYP_SG

            );'

        )

The TPT output was:

Teradata Parallel Transporter Version 14.00.00.03

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 14.00.00.03

Teradata Parallel Transporter Load Operator Version 14.00.00.03

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: TPT19003 ECI operator ID: FILE_READER_TB_AGR_PAT_MOB_GRP-1429654

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.

Teradata Employee

Re: fastload insertion

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.

-- SteveF
Teradata Employee

Re: fastload insertion

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