Error Table Mload

Tools & Utilities

Error Table Mload

On running the following mload script :

.LOGTABLE EDB.LOG_prepd_usge;
.LOGON abc.net/user,pass;
DATABASE EITOPSDB;
.BEGIN IMPORT MLOAD TABLES prepd_usge
WORKTABLES EDB.WT_prepd_usge
ERRORTABLES EDB.ET_prepd_usge
EDB.UV_prepd_usge
ERRLIMIT 1000
CHECKPOINT 0
TENACITY 2
SESSIONS 8
SLEEP 6;

.LAYOUT prepd_usge;

.FIELD IN_dct_acct_id * VARCHAR(3);
.FIELD IN_dct_acct_bal_amt * VARCHAR(18);
.FIELD IN_accum_1_tot * VARCHAR(18);

.DML Label UPSERT_DML
Do insert for missing update rows;

UPDATE prepd_usge
SET
dct_acct_id = COALESCE(Cast(substr(:IN_dct_acct_id,1,2) as decimal(2,0)),-9)
,dct_acct_bal_amt = cast(:IN_dct_acct_bal_amt as DECIMAL(15,2))
,accum_1_tot = cast(:IN_accum_1_tot as DECIMAL(15,2))

INSERT INTO prepd_usge
(
dct_acct_id
,dct_acct_bal_amt
,accum_1_tot

) VALUES (

COALESCE(Cast(substr(:IN_dct_acct_id,1,2) as decimal(2,0)),-9)
,cast(:IN_dct_acct_bal_amt as DECIMAL(15,2))
,cast(:IN_accum_1_tot as DECIMAL(15,2))

);

.IMPORT INFILE /home/tm7571/Cingular_BID.txt
Layout prepd_usge
FORMAT VARTEXT '|'
APPLY UPSERT_DML;

.End MLOAD;
.LOGOFF;

All the data is getting inserted into ET table .

I'm not able to find why .

Any help would be aprreciated.

If more info is needed ,please let me know.

Tags (1)
5 REPLIES
Teradata Employee

Re: Error Table Mload

Hi,

1. Can you let us know the error code recorded in the error table?

If it is in the Application Error Table, the column name is DBCErrorCode;

If it is in the Acquisition Error Table, the column name is ErrorCode.

Or

2. Can you share with us your test data file? We will take a further look.

Thanks!

Re: Error Table Mload

Hi Ivyuan,

Cingular_BID.txt data file contains pipe delimited records as below.

U|3168372484|3043606542|3043606542|75|Terminating|0|20160802000529|-05:00|20160802050529|Peak|13044126356|13043606542|HOME|LOCAL|3104103594010888|*310410||LOCAL|9|0|0|0|1|0.00|20160802000538|20160802050538|45.00|||0||||||||fbuAnyMin|

U|AC;1388980276;63757951;12406869633;20160802;000400|2406869633|2406869633|75|Originating SMS|4|20160802000400|-04:00|20160802040400|Peak|12406869633|12403510348|HOME|LOCAL||50||SMS|0|0|0|0|1|0.00|20160802000400|20160802040400|0.44||||0|||||||fbuSMS|

U|3189437185|3043606542|3043606542|75|Originating|0|20160802000521|-05:00|20160802050521|Peak|13043606542|13044126356|HOME|LOCAL|3104103594010888|*310410||LOCAL|15|0|0|0|1|0.00|20160802000536|20160802050536|45.00|||0||||0||||fbuAnyMin|

2665 is the Errorcode I see in ET table EDB.ET_prepd_usge.

But the script run ends with Highest return code encountered = '0' .

Thanks .

Re: Error Table Mload

Hi Ivyuan ,

Adding all the details below.

$ mload < prepd.mload.ctl

     ========================================================================

     =                                                                      =

     =          MultiLoad Utility    Release MLOD.15.10.01.00               =

     =          Platform SOLARIS/SPARC                                      =

     =          PID      16939                                              =

     =                                                                      =

     ========================================================================

     =                                                                      =

     =     Copyright 1990-2015 Teradata Corporation. ALL RIGHTS RESERVED.   =

     =                                                                      =

     ========================================================================

**** 01:11:20 UTY2411 Processing start date: TUE AUG 30, 2016

     ========================================================================

     =                                                                      =

     =          Logon/Connection                                            =

     =                                                                      =

     ========================================================================

0001 .LOGTABLE EDB.LOG_prepd_usge;

0002 .LOGON abc.net/user,;

**** 01:11:21 UTY8400 Teradata Database Release: 15.10.01.04

**** 01:11:21 UTY8400 Teradata Database Version: 15.10.01.04

**** 01:11:21 UTY8400 Default character set: ASCII

**** 01:11:21 UTY8400 Current RDBMS has interval support

**** 01:11:21 UTY8400 Current RDBMS has UDT support

**** 01:11:21 UTY8400 Current RDBMS has Large Decimal support

**** 01:11:21 UTY8400 Current RDBMS has TASM support

**** 01:11:21 UTY8400 Maximum supported buffer size: 1M

**** 01:11:21 UTY8400 Data Encryption supported by RDBMS server

**** 01:11:21 UTY6211 A successful connect was made to the RDBMS.

**** 01:11:21 UTY6217 Logtable 'EITOPSDB.LOG_prepd_usge' has been

     created.

     ========================================================================

     =                                                                      =

     =          Processing Control Statements                               =

     =                                                                      =

     ========================================================================

0003 DATABASE EITOPSDB;

**** 01:11:21 UTY1016 'DATABASE' request successful.

0004 .BEGIN IMPORT MLOAD TABLES prepd_usge

             WORKTABLES  EITOPSDB.WT_prepd_usge

             ERRORTABLES EITOPSDB.ET_prepd_usge

                         EITOPSDB.UV_prepd_usge

           ERRLIMIT 1000

           CHECKPOINT 0

           TENACITY 2

           SESSIONS 8

           SLEEP 6;

     ========================================================================

     =                                                                      =

     =          Processing MultiLoad Statements                             =

     =                                                                      =

     ========================================================================

0005 .LAYOUT prepd_usge;

0006       .FIELD IN_rec_type                   * VARCHAR(1);

0007       .FIELD IN_Call_cd                    * VARCHAR(255);

0008       .FIELD IN_sub_msisdn_nbr             * VARCHAR(10);

0009       .FIELD IN_mstr_msisdn_nbr            * VARCHAR(10);

0010       .FIELD IN_cos_cd                     * VARCHAR(5);

0011       .FIELD IN_call_type_cd               * VARCHAR(30);

0012       .FIELD IN_tsrv_cd                    * VARCHAR(5);

0013       .FIELD IN_usge_start_dt_tm           * VARCHAR(20);

0014       .FIELD IN_tmzn_offst_txt             * VARCHAR(6);

0015       .FIELD IN_utc_usge_start_dt_tm       * VARCHAR(20);

0016       .FIELD IN_tarf_period_cd             * VARCHAR(20);

0017       .FIELD IN_callng_nbr                 * VARCHAR(30);

0018       .FIELD IN_calld_nbr                  * VARCHAR(30);

0019       .FIELD IN_callng_area_ntwrk_cd       * VARCHAR(4);

0020       .FIELD IN_callng_area_sub_ntwrk_cd   * VARCHAR(40);

0021       .FIELD IN_loc_nbr                    * VARCHAR(30);

0022       .FIELD IN_loc_ind_cd                 * VARCHAR(30);

0023       .FIELD IN_fam_frnd_ind_nbr           * VARCHAR(3);

0024       .FIELD IN_rtg_cd                     * VARCHAR(20);

0025       .FIELD IN_dur_val                    * VARCHAR(15);

0026       .FIELD IN_chrg_dur_val               * VARCHAR(15);

0027       .FIELD IN_vol                        * VARCHAR(15);

0028       .FIELD IN_chrg_vol                   * VARCHAR(15);

0029       .FIELD IN_evt_cnt                    * VARCHAR(15);

0030       .FIELD IN_tot_cst_amt                * VARCHAR(18);

0031       .FIELD IN_acct_bal_dt_tm             * VARCHAR(20);

0032       .FIELD IN_utc_acct_bal_dt_tm         * VARCHAR(20);

0033       .FIELD IN_main_acct_bal_amt          * VARCHAR(18);

0034       .FIELD IN_dct_acct_id                * VARCHAR(3);

0035       .FIELD IN_dct_acct_bal_amt           * VARCHAR(18);

0036       .FIELD IN_accum_1_tot                * VARCHAR(18);

0037       .FIELD IN_accum_2_tot                * VARCHAR(18);

0038       .FIELD IN_accum_3_tot                * VARCHAR(18);

0039       .FIELD IN_accum_4_tot                * VARCHAR(18);

0040       .FIELD IN_accum_5_tot                * VARCHAR(18);

0041       .FIELD IN_tar_accum_id               * VARCHAR(2);

0042       .FIELD IN_tar_accum_dlta_cnt         * VARCHAR(18);

0043       .FIELD IN_blt_on_feat_grp_cd         * VARCHAR(1500);

0044       .FIELD IN_bsc_feat_cd                   * VARCHAR(1500);

0045 .DML Label UPSERT_DML

     Do insert for missing update rows;

0046 UPDATE  prepd_usge

     SET

         mstr_msisdn_nbr                = :IN_mstr_msisdn_nbr

        ,cos_cd                         = COALESCE(cast(:IN_cos_cd as DECIMAL(5,0)),'-999')

        ,tsrv_cd                        = COALESCE(cast(:IN_tsrv_cd as DECIMAL(5,0)),'-999')

        ,tmzn_offst_txt                 = cast(:IN_tmzn_offst_txt as CHAR(6))

        ,tarf_period_cd                 = COALESCE(:IN_tarf_period_cd, '*')

        ,callng_nbr                     = :IN_callng_nbr

        ,calld_nbr                      = :IN_calld_nbr

        ,callng_area_ntwrk_cd           = COALESCE(cast(:IN_callng_area_ntwrk_cd as

     CHAR(4)), '*')

        ,callng_area_sub_ntwrk_cd       = COALESCE(:IN_callng_area_sub_ntwrk_cd, '*')

        ,loc_nbr                        = :IN_loc_nbr

        ,loc_ind_cd                     = COALESCE(:IN_loc_ind_cd,'*')

        ,fam_frnd_ind_nbr               = cast(:IN_fam_frnd_ind_nbr as DECIMAL(3,0))

        ,rtg_cd                         = COALESCE(:IN_rtg_cd, '*')

        ,dur_val                        = COALESCE(cast(:IN_dur_val as DECIMAL(15,0)), 0)

        ,chrg_dur_val                   = cast(:IN_chrg_dur_val as DECIMAL(15,0))

        ,vol                            = cast(:IN_vol as DECIMAL(15,0))

        ,chrg_vol                       = cast(:IN_chrg_vol as DECIMAL(15,0))

        ,evt_cnt                        = cast(:IN_evt_cnt as DECIMAL(15,0))

        ,tot_cst_amt                    = COALESCE(cast(:IN_tot_cst_amt as DECIMAL(15,2)), 0 )

        ,acct_bal_dt_tm                 =

     cast((substr(:IN_acct_bal_dt_tm,1,4)||'-'||substr(:IN_acct_bal_dt_tm,5,2)||

     '-'||

     substr(:IN_acct_bal_dt_tm,7,2)||' '||substr(:IN_acct_bal_dt_tm,9,2)||':'||

     substr(:IN_acct_bal_dt_tm,11,2)||':'||substr(:IN_acct_bal_dt_tm,13,2)) as

     TIMESTAMP(0) format 'YYYY-MM-DD BHH:MI:SS')

        ,utc_acct_bal_dt_tm             =

     cast((substr(:IN_utc_acct_bal_dt_tm,1,4)||'-'||substr(:IN_utc_acct_bal_dt_t

     m,5,2)||'-'||

     substr(:IN_utc_acct_bal_dt_tm,7,2)||'

     '||substr(:IN_utc_acct_bal_dt_tm,9,2)||':'||

     substr(:IN_utc_acct_bal_dt_tm,11,2)||':'||substr(:IN_utc_acct_bal_dt_tm,13,

     2)) as TIMESTAMP(0) format 'YYYY-MM-DD BHH:MI:SS')

        ,main_acct_bal_amt              = COALESCE(cast(:IN_main_acct_bal_amt as

     DECIMAL(15,2)), 0 )

        ,dct_acct_id                    = COALESCE(Cast(substr(:IN_dct_acct_id,1,2) as

     decimal(2,0)),-9)

        ,dct_acct_bal_amt               = cast(:IN_dct_acct_bal_amt as DECIMAL(15,2))

        ,accum_1_tot                    = cast(:IN_accum_1_tot as DECIMAL(15,2))

        ,accum_2_tot                    = cast(:IN_accum_2_tot as DECIMAL(15,2))

        ,accum_3_tot                    = cast(:IN_accum_3_tot as DECIMAL(15,2))

        ,accum_4_tot                    = cast(:IN_accum_4_tot as DECIMAL(15,2))

        ,accum_5_tot                    = cast(:IN_accum_5_tot as DECIMAL(15,2))

        ,tar_accum_id                   = cast(:IN_tar_accum_id as DECIMAL(2,0))

        ,tar_accum_dlta_cnt             = cast(:IN_tar_accum_dlta_cnt as DECIMAL(15,2))

        ,blt_on_feat_grp_cd             = COALESCE(:IN_blt_on_feat_grp_cd,'*')

        ,bsc_feat_cd                    = COALESCE(:IN_bsc_feat_cd,'*')

        ,nw_ind                         = CASE WHEN (1+(POSITION(''||CAST(CAST('&TRANS_DT' AS DATE

     FORMAT 'YYYYMMDD') AS FORMAT 'E3') IN 'SunMonTueWedThuFriSat')/3)) in

     (2,3,4,5,6)

                THEN CASE WHEN cast(substr(TRIM (both from

     :IN_usge_start_dt_tm),9,2) as INTEGER) in

     (7,8,9,10,11,12,13,14,15,16,17,18,19,20)

           THEN cast('N' as char(1))

           ELSE cast('Y' as char(1)) END

           ELSE cast('Y' as char(1)) END

     WHERE

     Call_cd                                            = trim (both from

     :IN_Call_cd)

     and sub_msisdn_nbr                              = :IN_sub_msisdn_nbr

     and usge_start_dt_tm                            =

     cast((substr(:IN_usge_start_dt_tm,1,4)||'-'||substr(:IN_usge_start_dt_tm,5,

     2)||'-'||

     substr(:IN_usge_start_dt_tm,7,2)||'

     '||substr(:IN_usge_start_dt_tm,9,2)||':'||

     substr(:IN_usge_start_dt_tm,11,2)||':'||substr(:IN_usge_start_dt_tm,13,2))

     as TIMESTAMP(0) format 'YYYY-MM-DD BHH:MI:SS')

     and call_type_cd                                =

     COALESCE(:IN_call_type_cd, '*');

0047 INSERT INTO prepd_usge

           (

            Call_cd

           ,sub_msisdn_nbr

           ,mstr_msisdn_nbr

           ,cos_cd

           ,call_type_cd

           ,tsrv_cd

           ,usge_start_dt_tm

           ,nw_ind

           ,tmzn_offst_txt

           ,utc_usge_start_dt_tm

           ,tarf_period_cd

           ,callng_nbr

           ,calld_nbr

           ,callng_area_ntwrk_cd

           ,callng_area_sub_ntwrk_cd

           ,loc_nbr

           ,loc_ind_cd

           ,fam_frnd_ind_nbr

           ,rtg_cd

           ,dur_val

           ,chrg_dur_val

           ,vol

           ,chrg_vol

           ,evt_cnt

           ,tot_cst_amt

           ,acct_bal_dt_tm

           ,utc_acct_bal_dt_tm

           ,main_acct_bal_amt

           ,dct_acct_id

           ,dct_acct_bal_amt

           ,accum_1_tot

           ,accum_2_tot

           ,accum_3_tot

           ,accum_4_tot

           ,accum_5_tot

           ,tar_accum_id

           ,tar_accum_dlta_cnt

           ,blt_on_feat_grp_cd

           ,bsc_feat_cd

      ) VALUES (

            TRIM (both from :IN_Call_cd)

           ,cast(:IN_sub_msisdn_nbr as CHAR(10))

           ,cast(:IN_mstr_msisdn_nbr as CHAR(10))

           ,COALESCE(cast(:IN_cos_cd as DECIMAL(5,0)),'-999')

           ,COALESCE(:IN_call_type_cd, '*')

           ,COALESCE(cast(:IN_tsrv_cd as DECIMAL(5,0)),'-999')

     ,cast((substr(:IN_usge_start_dt_tm,1,4)||'-'||substr(:IN_usge_start_dt_tm,5

     ,2)||'-'||

     substr(:IN_usge_start_dt_tm,7,2)||'

     '||substr(:IN_usge_start_dt_tm,9,2)||':'||

     substr(:IN_usge_start_dt_tm,11,2)||':'||substr(:IN_usge_start_dt_tm,13,2))

     as TIMESTAMP(0) format 'YYYY-MM-DD BHH:MI:SS')

           ,CASE WHEN (1+(POSITION(''||CAST(CAST('&TRANS_DT' AS DATE FORMAT

     'YYYYMMDD') AS FORMAT 'E3') IN 'SunMonTueWedThuFriSat')/3)) in (2,3,4,5,6)

                THEN CASE WHEN cast(substr(TRIM (both from

     :IN_usge_start_dt_tm),9,2) as INTEGER) in

     (7,8,9,10,11,12,13,14,15,16,17,18,19,20)

           THEN cast('N' as char(1))

           ELSE cast('Y' as char(1)) END

           ELSE cast('Y' as char(1)) END

           ,cast(:IN_tmzn_offst_txt as CHAR(6))

     ,cast((substr(:IN_utc_usge_start_dt_tm,1,4)||'-'||substr(:IN_utc_usge_start

     _dt_tm,5,2)||'-'||

     substr(:IN_utc_usge_start_dt_tm,7,2)||'

     '||substr(:IN_utc_usge_start_dt_tm,9,2)||':'||

     substr(:IN_utc_usge_start_dt_tm,11,2)||':'||substr(:IN_utc_usge_start_dt_tm

     ,13,2)) as TIMESTAMP(0) format 'YYYY-MM-DD BHH:MI:SS')

           ,COALESCE(:IN_tarf_period_cd, '*')

           ,:IN_callng_nbr

           ,:IN_calld_nbr

           ,COALESCE(cast(:IN_callng_area_ntwrk_cd as CHAR(4)), '*')

           ,COALESCE(:IN_callng_area_sub_ntwrk_cd, '*')

           ,:IN_loc_nbr

           ,COALESCE(:IN_loc_ind_cd,'*')

           ,cast(:IN_fam_frnd_ind_nbr as DECIMAL(3,0))

           ,COALESCE(:IN_rtg_cd, '*')

           ,COALESCE(cast(:IN_dur_val as DECIMAL(15,0)), 0 )

           ,cast(:IN_chrg_dur_val as DECIMAL(15,0))

           ,cast(:IN_vol as DECIMAL(15,0))

           ,cast(:IN_chrg_vol as DECIMAL(15,0))

           ,cast(:IN_evt_cnt as DECIMAL(15,0))

           ,COALESCE(cast(:IN_tot_cst_amt as DECIMAL(15,2)), 0 )

     ,cast((substr(:IN_acct_bal_dt_tm,1,4)||'-'||substr(:IN_acct_bal_dt_tm,5,2)|

     |'-'||

     substr(:IN_acct_bal_dt_tm,7,2)||' '||substr(:IN_acct_bal_dt_tm,9,2)||':'||

     substr(:IN_acct_bal_dt_tm,11,2)||':'||substr(:IN_acct_bal_dt_tm,13,2)) as

     TIMESTAMP(0) format 'YYYY-MM-DD BHH:MI:SS')

     ,cast((substr(:IN_utc_acct_bal_dt_tm,1,4)||'-'||substr(:IN_utc_acct_bal_dt_

     tm,5,2)||'-'||

     substr(:IN_utc_acct_bal_dt_tm,7,2)||'

     '||substr(:IN_utc_acct_bal_dt_tm,9,2)||':'||

     substr(:IN_utc_acct_bal_dt_tm,11,2)||':'||substr(:IN_utc_acct_bal_dt_tm,13,

     2)) as TIMESTAMP(0) format 'YYYY-MM-DD BHH:MI:SS')

           ,COALESCE(cast(:IN_main_acct_bal_amt as DECIMAL(15,2)), 0 )

           ,COALESCE(Cast(substr(:IN_dct_acct_id,1,2) as decimal(2,0)),-9)

           ,cast(:IN_dct_acct_bal_amt as DECIMAL(15,2))

           ,cast(:IN_accum_1_tot as DECIMAL(15,2))

           ,cast(:IN_accum_2_tot as DECIMAL(15,2))

           ,cast(:IN_accum_3_tot as DECIMAL(15,2))

           ,cast(:IN_accum_4_tot as DECIMAL(15,2))

           ,cast(:IN_accum_5_tot as DECIMAL(15,2))

           ,cast(:IN_tar_accum_id as DECIMAL(2,0))

           ,cast(:IN_tar_accum_dlta_cnt as DECIMAL(15,2))

           ,COALESCE(:IN_blt_on_feat_grp_cd,'*')

           ,COALESCE(:IN_bsc_feat_cd,'*')

     );

0048 .IMPORT INFILE /home/tm7571/Cingular_BID.txt

     Layout prepd_usge

     FORMAT VARTEXT '|'

     APPLY UPSERT_DML;

0049 .End MLOAD;

     ========================================================================

     =                                                                      =

     =          MultiLoad Initial Phase                                     =

     =                                                                      =

     ========================================================================

**** 01:11:22 UTY0829 Options in effect for this MultiLoad import task:

     .       Sessions:    8 session(s).

     .       Checkpoint:  No rate in effect.

     .       Tenacity:    2 hour limit to successfully connect load sessions.

     .       Sleep   :    6 minute(s).

     .       Errlimit:    1000 rejected record(s).

     .       AmpCheck:    In effect for apply phase transitions.

**** 01:11:22 UTY0817 MultiLoad submitting the following request:

     Select NULL from EITOPSDB.LOG_prepd_usge where (LogType = 125)

     and (Seq = 1) and (MloadSeq = 0);

**** 01:11:22 UTY0817 MultiLoad submitting the following request:

     Select NULL from EITOPSDB.LOG_prepd_usge where (LogType = 120)

     and (Seq = 1);

**** 01:11:22 UTY0817 MultiLoad submitting the following request:

     SET QUERY_BAND='UTILITYNAME=MULTLOAD;' UPDATE FOR SESSION;

**** 01:11:22 UTY0817 MultiLoad submitting the following request:

     CHECK WORKLOAD FOR BEGIN MLOAD prepd_usge;

**** 01:11:22 UTY0817 MultiLoad submitting the following request:

     CHECK WORKLOAD END;

**** 01:11:23 UTY0812 MLOAD session(s) requested: 8.

**** 01:11:23 UTY0815 MLOAD session(s) connected: 8.

**** 01:11:23 UTY0817 MultiLoad submitting the following request:

     BEGIN MLOAD prepd_usge WITH INTERVAL;

**** 01:11:24 UTY0817 MultiLoad submitting the following request:

     INS EITOPSDB.LOG_prepd_usge (LogType, Seq, MLoadSeq)VALUES(130,

     1, 10);

**** 01:11:24 UTY0817 MultiLoad submitting the following request:

     MLOAD prepd_usge with EITOPSDB.WT_prepd_usge

     errortables EITOPSDB.ET_prepd_usge,

     EITOPSDB.UV_prepd_usge;

     ========================================================================

     =                                                                      =

     =          MultiLoad DML Transaction Phase                             =

     =                                                                      =

     ========================================================================

**** 01:11:24 UTY0817 MultiLoad submitting the following request:

     BT;

**** 01:11:24 UTY0817 MultiLoad submitting the following request:

     USING IN_REC_TYPE(VARCHAR(1)), IN_CALL_CD(VARCHAR(255)),

     IN_SUB_MSISDN_NBR(VARCHAR(10)), IN_MSTR_MSISDN_NBR(VARCHAR(10)),

     IN_COS_CD(VARCHAR(5)), IN_CALL_TYPE_CD(VARCHAR(30)),

     IN_TSRV_CD(VARCHAR(5)), IN_USGE_START_DT_TM(VARCHAR(20)),

     IN_TMZN_OFFST_TXT(VARCHAR(6)), IN_UTC_USGE_START_DT_TM(VARCHAR(20)),

     IN_TARF_PERIOD_CD(VARCHAR(20)), IN_CALLNG_NBR(VARCHAR(30)),

     IN_CALLD_NBR(VARCHAR(30)), IN_CALLNG_AREA_NTWRK_CD(VARCHAR(4)),

     IN_CALLNG_AREA_SUB_NTWRK_CD(VARCHAR(40)), IN_LOC_NBR(VARCHAR(30)),

     IN_LOC_IND_CD(VARCHAR(30)), IN_FAM_FRND_IND_NBR(VARCHAR(3)),

     IN_RTG_CD(VARCHAR(20)), IN_DUR_VAL(VARCHAR(15)),

     IN_CHRG_DUR_VAL(VARCHAR(15)), IN_VOL(VARCHAR(15)),

     IN_CHRG_VOL(VARCHAR(15)), IN_EVT_CNT(VARCHAR(15)),

     IN_TOT_CST_AMT(VARCHAR(18)), IN_ACCT_BAL_DT_TM(VARCHAR(20)),

     IN_UTC_ACCT_BAL_DT_TM(VARCHAR(20)), IN_MAIN_ACCT_BAL_AMT(VARCHAR(18)),

     IN_DCT_ACCT_ID(VARCHAR(3)), IN_DCT_ACCT_BAL_AMT(VARCHAR(18)),

     IN_ACCUM_1_TOT(VARCHAR(18)), IN_ACCUM_2_TOT(VARCHAR(18)),

     IN_ACCUM_3_TOT(VARCHAR(18)), IN_ACCUM_4_TOT(VARCHAR(18)),

     IN_ACCUM_5_TOT(VARCHAR(18)), IN_TAR_ACCUM_ID(VARCHAR(2)),

     IN_TAR_ACCUM_DLTA_CNT(VARCHAR(18)), IN_BLT_ON_FEAT_GRP_CD(VARCHAR(1500)),

     IN_BSC_FEAT_CD(VARCHAR(1500)) UPDATE  prepd_usge

     SET

         mstr_msisdn_nbr                = :IN_mstr_msisdn_nbr

        ,cos_cd                         = COALESCE(cast(:IN_cos_cd as DECIMAL(5,0)),'-999')

        ,tsrv_cd                        = COALESCE(cast(:IN_tsrv_cd as DECIMAL(5,0)),'-999')

        ,tmzn_offst_txt                 = cast(:IN_tmzn_offst_txt as CHAR(6))

        ,tarf_period_cd                 = COALESCE(:IN_tarf_period_cd, '*')

        ,callng_nbr                     = :IN_callng_nbr

        ,calld_nbr                      = :IN_calld_nbr

        ,callng_area_ntwrk_cd           = COALESCE(cast(:IN_callng_area_ntwrk_cd as

     CHAR(4)), '*')

        ,callng_area_sub_ntwrk_cd       = COALESCE(:IN_callng_area_sub_ntwrk_cd, '*')

        ,loc_nbr                        = :IN_loc_nbr

        ,loc_ind_cd                     = COALESCE(:IN_loc_ind_cd,'*')

        ,fam_frnd_ind_nbr               = cast(:IN_fam_frnd_ind_nbr as DECIMAL(3,0))

        ,rtg_cd                         = COALESCE(:IN_rtg_cd, '*')

        ,dur_val                        = COALESCE(cast(:IN_dur_val as DECIMAL(15,0)), 0)

        ,chrg_dur_val                   = cast(:IN_chrg_dur_val as DECIMAL(15,0))

        ,vol                            = cast(:IN_vol as DECIMAL(15,0))

        ,chrg_vol                       = cast(:IN_chrg_vol as DECIMAL(15,0))

        ,evt_cnt                        = cast(:IN_evt_cnt as DECIMAL(15,0))

        ,tot_cst_amt                    = COALESCE(cast(:IN_tot_cst_amt as DECIMAL(15,2)), 0 )

        ,acct_bal_dt_tm                 =

     cast((substr(:IN_acct_bal_dt_tm,1,4)||'-'||substr(:IN_acct_bal_dt_tm,5,2)||

     '-'||

     substr(:IN_acct_bal_dt_tm,7,2)||' '||substr(:IN_acct_bal_dt_tm,9,2)||':'||

     substr(:IN_acct_bal_dt_tm,11,2)||':'||substr(:IN_acct_bal_dt_tm,13,2)) as

     TIMESTAMP(0) format 'YYYY-MM-DD BHH:MI:SS')

        ,utc_acct_bal_dt_tm             =

     cast((substr(:IN_utc_acct_bal_dt_tm,1,4)||'-'||substr(:IN_utc_acct_bal_dt_t

     m,5,2)||'-'||

     substr(:IN_utc_acct_bal_dt_tm,7,2)||'

     '||substr(:IN_utc_acct_bal_dt_tm,9,2)||':'||

     substr(:IN_utc_acct_bal_dt_tm,11,2)||':'||substr(:IN_utc_acct_bal_dt_tm,13,

     2)) as TIMESTAMP(0) format 'YYYY-MM-DD BHH:MI:SS')

        ,main_acct_bal_amt              = COALESCE(cast(:IN_main_acct_bal_amt as

     DECIMAL(15,2)), 0 )

        ,dct_acct_id                    = COALESCE(Cast(substr(:IN_dct_acct_id,1,2) as

     decimal(2,0)),-9)

        ,dct_acct_bal_amt               = cast(:IN_dct_acct_bal_amt as DECIMAL(15,2))

        ,accum_1_tot                    = cast(:IN_accum_1_tot as DECIMAL(15,2))

        ,accum_2_tot                    = cast(:IN_accum_2_tot as DECIMAL(15,2))

        ,accum_3_tot                    = cast(:IN_accum_3_tot as DECIMAL(15,2))

        ,accum_4_tot                    = cast(:IN_accum_4_tot as DECIMAL(15,2))

        ,accum_5_tot                    = cast(:IN_accum_5_tot as DECIMAL(15,2))

        ,tar_accum_id                   = cast(:IN_tar_accum_id as DECIMAL(2,0))

        ,tar_accum_dlta_cnt             = cast(:IN_tar_accum_dlta_cnt as DECIMAL(15,2))

        ,blt_on_feat_grp_cd             = COALESCE(:IN_blt_on_feat_grp_cd,'*')

        ,bsc_feat_cd                    = COALESCE(:IN_bsc_feat_cd,'*')

        ,nw_ind                         = CASE WHEN (1+(POSITION(''||CAST(CAST('&TRANS_DT' AS DATE

     FORMAT 'YYYYMMDD') AS FORMAT 'E3') IN 'SunMonTueWedThuFriSat')/3)) in

     (2,3,4,5,6)

                THEN CASE WHEN cast(substr(TRIM (both from

     :IN_usge_start_dt_tm),9,2) as INTEGER) in

     (7,8,9,10,11,12,13,14,15,16,17,18,19,20)

           THEN cast('N' as char(1))

           ELSE cast('Y' as char(1)) END

           ELSE cast('Y' as char(1)) END

     WHERE

     Call_cd                                            = trim (both from

     :IN_Call_cd)

     and sub_msisdn_nbr                              = :IN_sub_msisdn_nbr

     and usge_start_dt_tm                            =

     cast((substr(:IN_usge_start_dt_tm,1,4)||'-'||substr(:IN_usge_start_dt_tm,5,

     2)||'-'||

     substr(:IN_usge_start_dt_tm,7,2)||'

     '||substr(:IN_usge_start_dt_tm,9,2)||':'||

     substr(:IN_usge_start_dt_tm,11,2)||':'||substr(:IN_usge_start_dt_tm,13,2))

     as TIMESTAMP(0) format 'YYYY-MM-DD BHH:MI:SS')

     and call_type_cd                                =

     COALESCE(:IN_call_type_cd, '*');

**** 01:11:24 UTY0817 MultiLoad submitting the following request:

     USING IN_REC_TYPE(VARCHAR(1)), IN_CALL_CD(VARCHAR(255)),

     IN_SUB_MSISDN_NBR(VARCHAR(10)), IN_MSTR_MSISDN_NBR(VARCHAR(10)),

     IN_COS_CD(VARCHAR(5)), IN_CALL_TYPE_CD(VARCHAR(30)),

     IN_TSRV_CD(VARCHAR(5)), IN_USGE_START_DT_TM(VARCHAR(20)),

     IN_TMZN_OFFST_TXT(VARCHAR(6)), IN_UTC_USGE_START_DT_TM(VARCHAR(20)),

     IN_TARF_PERIOD_CD(VARCHAR(20)), IN_CALLNG_NBR(VARCHAR(30)),

     IN_CALLD_NBR(VARCHAR(30)), IN_CALLNG_AREA_NTWRK_CD(VARCHAR(4)),

     IN_CALLNG_AREA_SUB_NTWRK_CD(VARCHAR(40)), IN_LOC_NBR(VARCHAR(30)),

     IN_LOC_IND_CD(VARCHAR(30)), IN_FAM_FRND_IND_NBR(VARCHAR(3)),

     IN_RTG_CD(VARCHAR(20)), IN_DUR_VAL(VARCHAR(15)),

     IN_CHRG_DUR_VAL(VARCHAR(15)), IN_VOL(VARCHAR(15)),

     IN_CHRG_VOL(VARCHAR(15)), IN_EVT_CNT(VARCHAR(15)),

     IN_TOT_CST_AMT(VARCHAR(18)), IN_ACCT_BAL_DT_TM(VARCHAR(20)),

     IN_UTC_ACCT_BAL_DT_TM(VARCHAR(20)), IN_MAIN_ACCT_BAL_AMT(VARCHAR(18)),

     IN_DCT_ACCT_ID(VARCHAR(3)), IN_DCT_ACCT_BAL_AMT(VARCHAR(18)),

     IN_ACCUM_1_TOT(VARCHAR(18)), IN_ACCUM_2_TOT(VARCHAR(18)),

     IN_ACCUM_3_TOT(VARCHAR(18)), IN_ACCUM_4_TOT(VARCHAR(18)),

     IN_ACCUM_5_TOT(VARCHAR(18)), IN_TAR_ACCUM_ID(VARCHAR(2)),

     IN_TAR_ACCUM_DLTA_CNT(VARCHAR(18)), IN_BLT_ON_FEAT_GRP_CD(VARCHAR(1500)),

     IN_BSC_FEAT_CD(VARCHAR(1500)) INSERT INTO prepd_usge

           (

            Call_cd

           ,sub_msisdn_nbr

           ,mstr_msisdn_nbr

           ,cos_cd

           ,call_type_cd

           ,tsrv_cd

           ,usge_start_dt_tm

           ,nw_ind

           ,tmzn_offst_txt

           ,utc_usge_start_dt_tm

           ,tarf_period_cd

           ,callng_nbr

           ,calld_nbr

           ,callng_area_ntwrk_cd

           ,callng_area_sub_ntwrk_cd

           ,loc_nbr

           ,loc_ind_cd

           ,fam_frnd_ind_nbr

           ,rtg_cd

           ,dur_val

           ,chrg_dur_val

           ,vol

           ,chrg_vol

           ,evt_cnt

           ,tot_cst_amt

           ,acct_bal_dt_tm

           ,utc_acct_bal_dt_tm

           ,main_acct_bal_amt

           ,dct_acct_id

           ,dct_acct_bal_amt

           ,accum_1_tot

           ,accum_2_tot

           ,accum_3_tot

           ,accum_4_tot

           ,accum_5_tot

           ,tar_accum_id

           ,tar_accum_dlta_cnt

           ,blt_on_feat_grp_cd

           ,bsc_feat_cd

      ) VALUES (

            TRIM (both from :IN_Call_cd)

           ,cast(:IN_sub_msisdn_nbr as CHAR(10))

           ,cast(:IN_mstr_msisdn_nbr as CHAR(10))

           ,COALESCE(cast(:IN_cos_cd as DECIMAL(5,0)),'-999')

           ,COALESCE(:IN_call_type_cd, '*')

           ,COALESCE(cast(:IN_tsrv_cd as DECIMAL(5,0)),'-999')

     ,cast((substr(:IN_usge_start_dt_tm,1,4)||'-'||substr(:IN_usge_start_dt_tm,5

     ,2)||'-'||

     substr(:IN_usge_start_dt_tm,7,2)||'

     '||substr(:IN_usge_start_dt_tm,9,2)||':'||

     substr(:IN_usge_start_dt_tm,11,2)||':'||substr(:IN_usge_start_dt_tm,13,2))

     as TIMESTAMP(0) format 'YYYY-MM-DD BHH:MI:SS')

           ,CASE WHEN (1+(POSITION(''||CAST(CAST('&TRANS_DT' AS DATE FORMAT

     'YYYYMMDD') AS FORMAT 'E3') IN 'SunMonTueWedThuFriSat')/3)) in (2,3,4,5,6)

                THEN CASE WHEN cast(substr(TRIM (both from

     :IN_usge_start_dt_tm),9,2) as INTEGER) in

     (7,8,9,10,11,12,13,14,15,16,17,18,19,20)

           THEN cast('N' as char(1))

           ELSE cast('Y' as char(1)) END

           ELSE cast('Y' as char(1)) END

           ,cast(:IN_tmzn_offst_txt as CHAR(6))

     ,cast((substr(:IN_utc_usge_start_dt_tm,1,4)||'-'||substr(:IN_utc_usge_start

     _dt_tm,5,2)||'-'||

     substr(:IN_utc_usge_start_dt_tm,7,2)||'

     '||substr(:IN_utc_usge_start_dt_tm,9,2)||':'||

     substr(:IN_utc_usge_start_dt_tm,11,2)||':'||substr(:IN_utc_usge_start_dt_tm

     ,13,2)) as TIMESTAMP(0) format 'YYYY-MM-DD BHH:MI:SS')

           ,COALESCE(:IN_tarf_period_cd, '*')

           ,:IN_callng_nbr

           ,:IN_calld_nbr

           ,COALESCE(cast(:IN_callng_area_ntwrk_cd as CHAR(4)), '*')

           ,COALESCE(:IN_callng_area_sub_ntwrk_cd, '*')

           ,:IN_loc_nbr

           ,COALESCE(:IN_loc_ind_cd,'*')

           ,cast(:IN_fam_frnd_ind_nbr as DECIMAL(3,0))

           ,COALESCE(:IN_rtg_cd, '*')

           ,COALESCE(cast(:IN_dur_val as DECIMAL(15,0)), 0 )

           ,cast(:IN_chrg_dur_val as DECIMAL(15,0))

           ,cast(:IN_vol as DECIMAL(15,0))

           ,cast(:IN_chrg_vol as DECIMAL(15,0))

           ,cast(:IN_evt_cnt as DECIMAL(15,0))

           ,COALESCE(cast(:IN_tot_cst_amt as DECIMAL(15,2)), 0 )

     ,cast((substr(:IN_acct_bal_dt_tm,1,4)||'-'||substr(:IN_acct_bal_dt_tm,5,2)|

     |'-'||

     substr(:IN_acct_bal_dt_tm,7,2)||' '||substr(:IN_acct_bal_dt_tm,9,2)||':'||

     substr(:IN_acct_bal_dt_tm,11,2)||':'||substr(:IN_acct_bal_dt_tm,13,2)) as

     TIMESTAMP(0) format 'YYYY-MM-DD BHH:MI:SS')

     ,cast((substr(:IN_utc_acct_bal_dt_tm,1,4)||'-'||substr(:IN_utc_acct_bal_dt_

     tm,5,2)||'-'||

     substr(:IN_utc_acct_bal_dt_tm,7,2)||'

     '||substr(:IN_utc_acct_bal_dt_tm,9,2)||':'||

     substr(:IN_utc_acct_bal_dt_tm,11,2)||':'||substr(:IN_utc_acct_bal_dt_tm,13,

     2)) as TIMESTAMP(0) format 'YYYY-MM-DD BHH:MI:SS')

           ,COALESCE(cast(:IN_main_acct_bal_amt as DECIMAL(15,2)), 0 )

           ,COALESCE(Cast(substr(:IN_dct_acct_id,1,2) as decimal(2,0)),-9)

           ,cast(:IN_dct_acct_bal_amt as DECIMAL(15,2))

           ,cast(:IN_accum_1_tot as DECIMAL(15,2))

           ,cast(:IN_accum_2_tot as DECIMAL(15,2))

           ,cast(:IN_accum_3_tot as DECIMAL(15,2))

           ,cast(:IN_accum_4_tot as DECIMAL(15,2))

           ,cast(:IN_accum_5_tot as DECIMAL(15,2))

           ,cast(:IN_tar_accum_id as DECIMAL(2,0))

           ,cast(:IN_tar_accum_dlta_cnt as DECIMAL(15,2))

           ,COALESCE(:IN_blt_on_feat_grp_cd,'*')

           ,COALESCE(:IN_bsc_feat_cd,'*')

     );

**** 01:11:24 UTY0817 MultiLoad submitting the following request:

     INS EITOPSDB.LOG_prepd_usge (LogType, Seq, MLoadSeq)VALUES(130,

     1, 20);

**** 01:11:24 UTY0817 MultiLoad submitting the following request:

     ET;

     ========================================================================

     =                                                                      =

     =          MultiLoad Acquisition Phase                                 =

     =                                                                      =

     ========================================================================

**** 01:11:24 UTY1818 As requested, no record checkpoints will be performed for

     this task.

**** 01:11:24 UTY0817 MultiLoad submitting the following request:

     BEGIN TRANSACTION;

**** 01:11:24 UTY0817 MultiLoad submitting the following request:

     CHECKPOINT LOADING INTERVAL 0;

**** 01:11:24 UTY0817 MultiLoad submitting the following request:

     CHECKPOINT LOADING INTERVAL 0;

**** 01:11:25 UTY0817 MultiLoad submitting the following request:

     CHECKPOINT LOADING INTERVAL 0;

**** 01:11:26 UTY0817 MultiLoad submitting the following request:

     USING Ckpt(VARBYTE(1024)) INSERT EITOPSDB.LOG_prepd_usge

     (Logtype, Seq, MLoadSeq, MLoadImpSeq, MLoadSrcSeq,

     MiscInt1,MiscInt2,MiscInt3,MiscInt4,

     MiscInt5,MiscInt6,MiscInt7,MiscInt8,CkptInterval,byteflag,MLoadCkpt)

      VALUES (110, 1, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, :Ckpt);

**** 01:11:28 UTY1802 Processing Import Sequence 1, Source Sequence 100000.

**** 01:11:30 UTY1802 Processing Import Sequence 1, Source Sequence 200000.

**** 01:11:32 UTY1802 Processing Import Sequence 1, Source Sequence 300000.

**** 01:11:33 UTY1802 Processing Import Sequence 1, Source Sequence 400000.

**** 01:11:35 UTY1802 Processing Import Sequence 1, Source Sequence 500000.

**** 01:11:37 UTY1802 Processing Import Sequence 1, Source Sequence 600000.

**** 01:11:38 UTY1802 Processing Import Sequence 1, Source Sequence 700000.

**** 01:11:40 UTY1802 Processing Import Sequence 1, Source Sequence 800000.

**** 01:11:41 UTY1802 Processing Import Sequence 1, Source Sequence 900000.

**** 01:11:43 UTY1802 Processing Import Sequence 1, Source Sequence 1000000.

**** 01:11:45 UTY1802 Processing Import Sequence 1, Source Sequence 1100000.

**** 01:11:46 UTY1802 Processing Import Sequence 1, Source Sequence 1200000.

**** 01:11:48 UTY1802 Processing Import Sequence 1, Source Sequence 1300000.

**** 01:11:49 UTY1802 Processing Import Sequence 1, Source Sequence 1400000.

**** 01:11:51 UTY1802 Processing Import Sequence 1, Source Sequence 1500000.

**** 01:11:52 UTY1802 Processing Import Sequence 1, Source Sequence 1600000.

**** 01:11:54 UTY1802 Processing Import Sequence 1, Source Sequence 1700000.

**** 01:11:56 UTY1802 Processing Import Sequence 1, Source Sequence 1800000.

**** 01:11:57 UTY1802 Processing Import Sequence 1, Source Sequence 1900000.

**** 01:11:59 UTY1802 Processing Import Sequence 1, Source Sequence 2000000.

**** 01:12:00 UTY1802 Processing Import Sequence 1, Source Sequence 2100000.

**** 01:12:02 UTY1802 Processing Import Sequence 1, Source Sequence 2200000.

**** 01:12:04 UTY1802 Processing Import Sequence 1, Source Sequence 2300000.

**** 01:12:05 UTY1802 Processing Import Sequence 1, Source Sequence 2400000.

**** 01:12:07 UTY1802 Processing Import Sequence 1, Source Sequence 2500000.

**** 01:12:09 UTY1802 Processing Import Sequence 1, Source Sequence 2600000.

**** 01:12:10 UTY1802 Processing Import Sequence 1, Source Sequence 2700000.

**** 01:12:12 UTY1802 Processing Import Sequence 1, Source Sequence 2800000.

**** 01:12:14 UTY1802 Processing Import Sequence 1, Source Sequence 2900000.

**** 01:12:15 UTY1802 Processing Import Sequence 1, Source Sequence 3000000.

**** 01:12:17 UTY1802 Processing Import Sequence 1, Source Sequence 3100000.

**** 01:12:19 UTY1802 Processing Import Sequence 1, Source Sequence 3200000.

**** 01:12:21 UTY1802 Processing Import Sequence 1, Source Sequence 3300000.

**** 01:12:23 UTY1802 Processing Import Sequence 1, Source Sequence 3400000.

**** 01:12:25 UTY1802 Processing Import Sequence 1, Source Sequence 3500000.

**** 01:12:25 UTY0817 MultiLoad submitting the following request:

     CHECKPOINT LOADING INTERVAL 0;

**** 01:12:26 UTY0817 MultiLoad submitting the following request:

     USING Ckpt(VARBYTE(1024)) INSERT EITOPSDB.LOG_prepd_usge

     (Logtype, Seq, MLoadSeq, MLoadImpSeq, MLoadSrcSeq,

     MiscInt1,MiscInt2,MiscInt3,MiscInt4,

     MiscInt5,MiscInt6,MiscInt7,MiscInt8,CkptInterval,byteflag,MLoadCkpt)

      VALUES (110, 1, 1, 1, 0, 3524630, 3524630, 3524630, 3524630, 0, 0, 0, 0,

     0, 1, :Ckpt);

**** 01:12:26 UTY0826 A checkpoint has been taken, recording that end of file

     has been reached for IMPORT 1 of this MultiLoad Import task.

**** 01:12:26 UTY1803 Import processing statistics

     .                                       IMPORT  1     Total thus far

     .                                       =========     ==============

     Candidate records considered:........     3524630.......     3524630

     Apply conditions satisfied:..........     3524630.......     3524630

     Candidate records not applied:.......           0.......           0

     Candidate records rejected:..........           0.......           0

**** 01:12:26 UTY1821 Aquisition Phase statistics

     Elapsed time:  00:01:02

     CPU time:      46.47 Seconds

     MB/sec:        16.318

     MB/cpusec:     21.7714

**** 01:12:26 UTY0817 MultiLoad submitting the following request:

     CHECKPOINT LOADING INTERVAL 0;

**** 01:12:27 UTY0817 MultiLoad submitting the following request:

     CHECKPOINT LOADING INTERVAL 0 END;

**** 01:12:30 UTY0817 MultiLoad submitting the following request:

     INS EITOPSDB.LOG_prepd_usge (LogType, Seq, MLoadSeq)VALUES(130,

     1, 30);

**** 01:12:30 UTY0817 MultiLoad submitting the following request:

     ET;

     ========================================================================

     =                                                                      =

     =          MultiLoad Application Phase                                 =

     =                                                                      =

     ========================================================================

**** 01:12:30 UTY0817 MultiLoad submitting the following request:

     EXEC MLOAD prepd_usge;

**** 01:13:00 UTY0818 Statistics for table prepd_usge:

        Inserts:                    0

        Updates:                    0

        Deletes:                    0

**** 01:13:00 UTY1822 Application Phase statistics

     Elapsed time:  00:00:30

**** 01:13:00 UTY0817 MultiLoad submitting the following request:

     INS EITOPSDB.LOG_prepd_usge (LogType, Seq) VALUES (115, 1)

**** 01:13:00 UTY0817 MultiLoad submitting the following request:

     BEGIN TRANSACTION;

**** 01:13:01 UTY0817 MultiLoad submitting the following request:

     END MLOAD;

**** 01:13:01 UTY0817 MultiLoad submitting the following request:

     INS EITOPSDB.LOG_prepd_usge (LogType, Seq) VALUES (120, 1)

**** 01:13:01 UTY0817 MultiLoad submitting the following request:

     USING Ckpt(VARBYTE(1024)) INS EITOPSDB.LOG_prepd_usge (LogType,

     Seq, MLoadCkpt)VALUES(135, 1, :Ckpt);

**** 01:13:01 UTY0817 MultiLoad submitting the following request:

     END TRANSACTION;

     ========================================================================

     =                                                                      =

     =          MultiLoad Task Cleanup                                      =

     =                                                                      =

     ========================================================================

**** 01:13:01 UTY0817 MultiLoad submitting the following request:

     SELECT CAST(COUNT(*) AS BIGINT) FROM EITOPSDB.ET_prepd_usge;

**** 01:13:01 UTY0846 Error table EITOPSDB.ET_prepd_usge contains

     3524630 rows.

**** 01:13:01 UTY0817 MultiLoad submitting the following request:

     SELECT CAST(COUNT(*) AS BIGINT) FROM EITOPSDB.UV_prepd_usge;

**** 01:13:01 UTY0821 Error table EITOPSDB.UV_prepd_usge is EMPTY,

     dropping table.

**** 01:13:01 UTY0817 MultiLoad submitting the following request:

     BEGIN TRANSACTION;

**** 01:13:01 UTY0817 MultiLoad submitting the following request:

     INS EITOPSDB.LOG_prepd_usge (LogType, Seq, MLoadSeq)VALUES(125,

     1, 2)

**** 01:13:01 UTY0817 MultiLoad submitting the following request:

     DROP TABLE EITOPSDB.UV_prepd_usge;

**** 01:13:02 UTY0817 MultiLoad submitting the following request:

     END TRANSACTION;

**** 01:13:02 UTY0817 MultiLoad submitting the following request:

     BEGIN TRANSACTION;

**** 01:13:02 UTY0817 MultiLoad submitting the following request:

     INS EITOPSDB.LOG_prepd_usge (LogType, Seq, MLoadSeq)VALUES(125,

     1, 3)

**** 01:13:02 UTY0817 MultiLoad submitting the following request:

     DROP TABLE EITOPSDB.WT_prepd_usge;

**** 01:13:02 UTY0817 MultiLoad submitting the following request:

     END TRANSACTION;

**** 01:13:02 UTY0825 Error table statistics for:

     Target table 1: prepd_usge

     Number of Rows        Error Table Name

     ====================  ==================================================

                  3524630  EITOPSDB.ET_prepd_usge

                        0  EITOPSDB.UV_prepd_usge

**** 01:13:02 UTY0817 MultiLoad submitting the following request:

     BEGIN TRANSACTION;

**** 01:13:02 UTY0817 MultiLoad submitting the following request:

     USING Ckpt(VARBYTE(1024)) INS EITOPSDB.LOG_prepd_usge (LogType,

     Seq, MLoadCkpt)VALUES(140, 1, :Ckpt);

**** 01:13:02 UTY0817 MultiLoad submitting the following request:

     INS EITOPSDB.LOG_prepd_usge (LogType, Seq) VALUES (125, 1)

**** 01:13:02 UTY0817 MultiLoad submitting the following request:

     END TRANSACTION;

**** 01:13:03 UTY0822 MultiLoad processing complete for this MultiLoad import

     task.

     ========================================================================

     =                                                                      =

     =          MultiLoad Task Complete                                     =

     =                                                                      =

     ========================================================================

**** 01:13:03 UTY1024 Session modal request, 'DATABASE', re-executed.

**** 01:13:03 UTY1024 Session modal request, 'SET

     QUERY_BAND='UTILITYNAME=MULTLOAD;' UPDATE FOR SESSION;', re-executed.

     ========================================================================

     =                                                                      =

     =          Processing Control Statements                               =

     =                                                                      =

     ========================================================================

0050 .LOGOFF;

     ========================================================================

     =                                                                      =

     =          Logoff/Disconnect                                           =

     =                                                                      =

     ========================================================================

**** 01:13:03 UTY6216 The restart log table has been dropped.

**** 01:13:03 UTY6212 A successful disconnect was made from the RDBMS.

**** 01:13:03 UTY2410 Total processor time used = '47.22 Seconds'

     .       Start : 01:11:20 - TUE AUG 30, 2016

     .       End   : 01:13:03 - TUE AUG 30, 2016

     .       Highest return code encountered = '0'.

$

Teradata Employee

Re: Error Table Mload

Hi,

Here is related information on 2665:

2665 Invalid date.

Explanation: This error occurs when date arithmetic is attempted on an invalid date.

Generated By: AMP Steps.

For Whom: End User.

Notes: Dates are stored as integers in the form YYMMDD, where YY is a calendar year beginning with 1900. Values such

as 861332 are invalid as dates and may not be used where the value is to be used as a DATE data type. This error can also

occur when character strings are being converted to DATE and the character data is in the wrong format or conversion

results in an invalid value.

Remedy: Correct and resubmit the request.

I did the following experiment:

Here is my MLOAD script:

.LOGTABLE yy1;

.LOGON tdpid/userid, password;

DROP TABLE BW_CPS_VOL_EXTR_STG;

DROP TABLE UV_CPS_VOL_EXTR_STG;

DROP TABLE ET_CPS_VOL_EXTR_STG;

CREATE TABLE BW_CPS_VOL_EXTR_STG (

c1 integer,

c2 timestamp);

.BEGIN MLOAD TABLES BW_CPS_VOL_EXTR_STG SESSIONS 5 ;

.LAYOUT BW_CPS_VOL_EXTR;

.FIELD col1 * varchar(10);

.FIELD col2 * varchar(20);

.DML LABEL INS;

INSERT INTO BW_CPS_VOL_EXTR_STG(c1, c2) values(:col1, cast((substr(:col2,1,4)||'-'||substr(:col2,5,2)||'-'||substr(:col2,7,2)||' '||substr(:col2,9,2)||':'||substr(:col2,11,2)||':'||substr(:col2,13,2)) as TIMESTAMP(0) format 'YYYY-MM-DD BHH:MI:SS'));

.IMPORT INFILE 'date.txt'  

FORMAT VARTEXT '|'

LAYOUT BW_CPS_VOL_EXTR

APPLY INS;

.END MLOAD;

.LOGOFF;

My data file is similar to yours:

1|20160802040400

2|20160802050521

I was able to successfully load 2 records.

 BTEQ -- Enter your SQL request or BTEQ command:

sel * from iii.BW_CPS_VOL_EXTR_STG;

 *** Query completed. 2 rows found. 2 columns returned.

 *** Total elapsed time was 1 second.

c1            1

c2  2016-08-02 04:04:00.000000

c1            2

c2  2016-08-02 05:05:21.000000

Can you check if you CAST statement is in the right form, specifically, is there a blank space in between

the YYYY-MM-DD and the rest of the stamp?

Thanks!

FYI, the Message manual(B035-1096) could help to get more information of the error codes like 2665.

Thanks!

Re: Error Table Mload

Thank you Ivyuan . Your sample script solved my problem.



/**
* author: [itmo] enot.1.10
* created: 29.08.2016 14:58:17
**/

#define __USE_MINGW_ANSI_STDIO 0
#include <bits/stdc++.h>

#define F first
#define S second
#define pb push_back
#define mp make_pair
#define forn(i, n) for(int i = 0 ; (i) < (n) ; ++i)
#define eprintf(...) fprintf(stderr, __VA_ARGS__),fflush(stderr)
#define sz(a) ((int)(a).size())
#define all(a) (a).begin(),a.end()
#define pw(x) (1LL<<(x))

using namespace std;

typedef long long ll;
typedef double dbl;
typedef vector<int> vi;
typedef pair<int, int> pi;

const int inf = 1.01e9;
const dbl eps = 1e-9;

/* --- main part --- */

const int N = 2e5 + 10;

char s[N][6];

int main()
{
#ifdef home
assert(freopen("1.in", "r", stdin));
assert(freopen("1.out", "w", stdout));
#endif
int n;
scanf
("%d", &n);
forn
(i, n) scanf("%s", s[i]);
int ok = 0;
forn
(i, n)
{
if (s[i][0] == 'O' && s[i][1] == 'O')
{
s
[i][0] = '+';
s
[i][1] = '+';
ok
= 1;
break;
}
if (s[i][3] == 'O' && s[i][4] == 'O')
{
s
[i][3] = '+';
s
[i][4] = '+';
ok
= 1;
break;
}
}
if (ok)
{
printf
("YES\n");
forn
(i, n) printf("%s\n", s[i]);
}
else
{
printf
("NO\n");
}
#ifdef home
eprintf
("time = %d ms\n", (int)(clock() * 1000. / CLOCKS_PER_SEC));
#endif
return 0;
}