|
Archives of the TeradataForumMessage Posted: Wed, 27 Feb 2008 @ 20:04:51 GMT
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
| ||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||
Copyright 2016 - All Rights Reserved | ||||||||||||||||||||||||||||||||||||||||||||||||
Last Modified: 15 Jun 2023 | ||||||||||||||||||||||||||||||||||||||||||||||||