BTEQ Import - The key to load delta data at near Real-time frequency - Sample script included

TPump is hands-down the best utility to maintain an active data-warehouse. 
But many architects feel that if the data can be refreshed every 3 hours, and the Teradata System Peaks are not regular, we can use BTEQ Import.

Its easy to use, uses minimal system resources and ideal for small Delta loads.

Example script:

.logon edw/developer1,test;

.IMPORT REPORT FILE = /home/dev1/TRAN.MAR262013.csv

.REPEAT * PACK 250

USING

(

         IN_REC_TYP            CHAR(2)

        ,IN_CUS_NO         CHAR(18)

        ,IN_FTY_NO          CHAR(7)

        ,IN_REG_NO             CHAR(3)

        ,IN_TRS_NO              CHAR(4)

        ,IN_TRS_DT               CHAR(10)

        ,IN_TRS_TM               CHAR(8)

        ,IN_APP_ID         CHAR(9)

        ,IN_AMT               CHAR(7)

        ,IN_ADD_DWN_PMT_AM CHAR(7)

        ,IN_FST_PMT_AM     CHAR(7)

)

INSERT INTO Database1.Pos_register

   (

         CUS_ID_NO

        ,IAN_TYP_CD

        ,FTY_NO

        ,REG_NO

        ,TRS_NO

        ,TRS_DT

        ,TRS_TM

        ,APP_ID

        ,AMT

        ,ADD_DWN_PMT_AM

        ,FST_PMT_AM

   )

VALUES

   (

        :IN_CUS_ID_NO

        ,'RS'

        ,TRIM(:IN_FTY_NO)

        ,TRIM(:IN_REG_NO)

        ,TRIM(:IN_TRS_NO)

        ,:IN_trs_dt (DATE,FORMAT'YYYY-MM-DD')

        ,:IN_trs_tm (TIME(0))

        ,NULLIF(:IN_APP_ID, '         ')

        ,CASE WHEN TRIM(:IN_LSE_AM) = ''

              THEN 0

         ELSE (TRIM(:IN_AMT))

         END

        ,CASE WHEN TRIM(:IN_ADD_DWN_PMT_AM/100) = ''

              THEN 0

         ELSE (TRIM(:IN_ADD_DWN_PMT_AM/100))

         END

        ,CASE WHEN TRIM(:IN_FST_PMT_AM) = ''

              THEN 0

         ELSE (TRIM(:IN_FST_PMT_AM/100))

         END

   );

.IF ERRORCODE <> 0 THEN .QUIT ERRORCODE;

Advantages of BTEQ Import:

1. Bteq import is light and uses less system resources as compared to its heavy-duty cousins.

2. Bteq import allows manipulation of the incoming data during the load. See the example above, where we have divided the :IN_ADD_DWN_PMT_AM 

3. The PACK option helps us to load multiple rows based on the value set. In the above example data is loaded in 250 rows per session.

Let us know if this topic helped you. Share our blog on Facebook on Google+.

Please donate using the button on the right. Your donation will help someone.