Archives of the TeradataForum

Message Posted: Wed, 27 Feb 2008 @ 20:04:51 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi,

We've been running a job that exports data from one Teradata box and mloads it to another. Normally, this runs without any issues; however, the mload failed this time on the first record with a data out of range error (but no message number) as follows:

     **** 11:10:36 UTY4203 Attempted to access out of range
           input data in field 'PERCENT_ACCESS',
           file 'C:\EDW_CAP_MGMT\EXPORTS\TBL_VW_ACC_XP_0226.TXT',
           record number '1'.

My immediate action was to examine the data on the incoming record, but it looks fine.

     (Date-2)    DatabaseName    TVMName           Count(*)  ((Count(*)/TOTAL_CNT)*100)  Current_Timestamp()
     25/02/2008  GRP_DBMB        LDREPORT_TMP_INV    474022                       26.00  2008-02-27 14:15:31.610000
     25/02/2008  ETL_DOWNSTREAM  #VEVNT_STG0         270220                       15.00  2008-02-27 14:15:31.610000

Here is the fastexport SQL excerpt:

     .EXPORT OUTFILE C:\EDW_CAP_MGMT\EXPORTS\TBL_VW_ACC_XP.TXT MODE RECORD
     FORMAT TEXT;

     SELECT DATE-1 (DATE),DATABASENAME (VARCHAR(30))
                ,TVMNAME (VARCHAR(30))
                ,COUNT(*) (INTEGER)
                ,(COUNT(*) (DECIMAL(15,2))) / (total_cnt (DECIMAL(15,2))) * 100
           (DECIMAL(9,2))
            ,CURRENT_TIMESTAMP(6) (TIMESTAMP(6))
           FROM DBC.ACCESSLOG A
                ,( SELECT COUNT (*) AS TOTAL_CNT
                 FROM      DBC.ACCESSLOG
                 WHERE     LOGDATE=DATE-1
                 AND      DATABASENAME NOT IN ('SYSDBA_SPOOL', 'Sysdba_Spool')
                ) AS TOTAL_COUNT , ...

etc.

Multiload SQL excerpt:

     .LAYOUT FILEIN;
       .FIELD COLLECTION_DATE * DATE;
       .FIELD DATABASENAME * VARCHAR(30);
       .FIELD TABLE_VEIW_NM  * VARCHAR(30);
       .FIELD ACCESS_COUNT * INTEGER;
       .FIELD PERCENT_ACCESS * DECIMAL(9,2);
       .FIELD MOD_DT_TM  *  CHAR(26);

     INSERT INTO EDW_CAP_MGMT.TBL_VW_ACC
       (COLLECTION_DATE
         ,DATABASENAME
         ,TABLE_VEIW_NM
         ,ACCESS_COUNT
         ,PERCENT_ACCESS
         ,MOD_DT_TM)
     VALUES
       (:COLLECTION_DATE
         ,:DATABASENAME
         ,:TABLE_VEIW_NM
         ,:ACCESS_COUNT
         ,:PERCENT_ACCESS
         ,:MOD_DT_TM);

     .IMPORT INFILE C:\EDW_CAP_MGMT\EXPORTS\TBL_VW_ACC_XP.TXT
       FORMAT TEXT
       LAYOUT FILEIN
       APPLY INSERTS;

Layout of target table:

     CREATE MULTISET TABLE EDW_CAP_MGMT.TBL_VW_ACC , ...
     ...
           (COLLECTION_DATE DATE FORMAT 'yyyy-mm-dd',
            DATABASENAME VARCHAR(30) CHARACTER SET LATIN CASESPECIFIC,
            TABLE_VEIW_NM VARCHAR(30) CHARACTER SET LATIN CASESPECIFIC,
            ACCESS_COUNT INTEGER,
            PERCENT_ACCESS DECIMAL(9,2),
            MOD_DT_TM TIMESTAMP(6) NOT NULL) ...

Again, this job has been running in production for several months with no errors. My immediate action was to examine the data on the incoming record, but it looks fine. Any suggestions?

Thanks,

Tim

	<Prev	Next>		<<First	<Prev	Next>	Last>>