TPT status code 12 during Insert

Tools
Teradata Employee

TPT status code 12 during Insert

Hi,

I am trying to insert data using the export/update operator into multiple tables using a single SELECT. But the query always terminates at the insert step with status code 12. Sometimes it inserts data and then terminates and sometimes it terminates before the insert. All work, error and log tables are being deleted in the beginnin gof the script. All target tables are multiset.

Any help would be higly appreciated.

Thanks in advance.

here is the code:

/*

 ############################################################################

 #  Defining LOAD Operator to load data tp ${DX_CDM_LDB}.MSC_NET_ACT_LD

 ############################################################################

 */

 DEFINE OPERATOR LOAD_LDB_TMP

 TYPE UPDATE

 SCHEMA *

 ATTRIBUTES

 (

  INTEGER BufferSize = 200,

  INTEGER ErrorLimit=4000,

  INTEGER MaxSessions       = 8,

  INTEGER MinSessions       = 1,

  INTEGER TenacityHours     = 1,

  INTEGER TenacitySleep     = 4,

  VARCHAR TdpId             = '${Tdp_Id}',

  VARCHAR UserName          = '${User_Name}',

  VARCHAR UserPassword      = '${User_Password}',

  VARCHAR LOGTABLE          = '${DX_LOG}.MSC_NET_ACT_LOG',

  VARCHAR TargetTable       = ['${DX_CDM_LDB}.MSC_NET_ACT_LD',

         '${DX_CDM_LDB}.NETWORK_ACT_WIRELESS_MSC_LD',

         '${DX_CDM_LDB}.NETWORK_INTERCHANGE_CDR_MSC_LD'],

  VARCHAR ARRAY WorkTable   = ['${DX_WRK}.MSC_NET_ACT_WT',

       '${DX_WRK}.NETWORK_ACT_WIRELS_LD_MSC_WT',

       '${DX_WRK}.NETWORK_IC_CDR_LD_MSC_WT'],

  VARCHAR ARRAY ErrorTable1 =  ['${DX_UTL}.MSC_NET_ACT_ET1',

       '${DX_UTL}.NETWORK_ACT_WIRELS_LD_MSC_ET1',

       '${DX_UTL}.NETWORK_IC_CDR_MSC_ET1'],

  VARCHAR ARRAY ErrorTable2 =   ['${DX_UTL}.MSC_NET_ACT_UV',

       '${DX_UTL}.NET_ACT_WIRELS_LD_MSC_UV',

       '${DX_UTL}.NETWORK_IC_CDR_LD_MSC_UV;']

 );



 /*

 ############################################################################

 #  Defining Export Operator get data from ${DX_CDM_LDB}.NEW_MSC_FILE

 ############################################################################

 */

 DEFINE OPERATOR Export_NA_TMP

 TYPE EXPORT

 SCHEMA MSC_NET_ACT_LD

 ATTRIBUTES

 (

  INTEGER MaxSessions       = 8,

  VARCHAR UserName       = '${User_Name}',

  VARCHAR UserPassword   = '${User_Password}',

  VARCHAR TdpId          = '${Tdp_Id}',

  VARCHAR SelectStmt

  

 );

 /*

 ############################################################################

 #  Inserting Data to LD Table

 ############################################################################

 */

  STEP C_Load_MSC_NET_ACT_LD_Table

  (

  APPLY

   (

   'INSERT INTO ${DX_CDM_LDB}.MSC_NET_ACT_LD

    (

     Network_Act_Id

     ,Act_Start_Dt

     ,Act_End_Dt

     ,Act_Duration_Meas

     ,CHARGE_DURATION_MEAS

     ,CALL_COMPLETION_TYPE_CD

     ,Orig_Number_Val

     ,Term_Number_Val

     ,DIALED_NUMBER_VAL

     ,CALL_RATE_PERIOD_CD

     ,PRE_POST_IND

     ,Act_Type_Cd

     ,CHARGE_DURATION_UOM_CD

     ,Act_Duration_Uom_Cd

     ,Act_Start_Tm     

     ,Sbscrp_Id

     ,Offrng_Id

     ,Accs_Meth_Id

     ,Act_Dir_Type_Cd

     ,Orig_Party_Id

     ,Term_Party_Id

     ,BATCH_ID

    )

    VALUES

    (

     :Network_Act_Id

     ,:Act_Start_Dt

     ,:Act_End_Dt

     ,:Act_Duration_Meas

     ,:CHARGE_DURATION_MEAS

     ,:CALL_COMPLETION_TYPE_CD

     ,:Orig_Number_Val

     ,:Term_Number_Val

     ,:DIALED_NUMBER_VAL

     ,:CALL_RATE_PERIOD_CD

     ,:PRE_POST_IND

     ,:Act_Type_Cd

     ,:CHARGE_DURATION_UOM_CD

     ,:Act_Duration_Uom_Cd

     ,:Act_Start_Tm

     ,:Sbscrp_Id

     ,:Offrng_Id

     ,:Accs_Meth_Id

     ,:Act_Dir_Type_Cd

     ,:Orig_Party_Id

     ,:Term_Party_Id

     ,:BATCH_ID

    );'

   ) ,

   ('INSERT INTO ${DX_CDM_LDB}.NETWORK_ACT_WIRELESS_MSC_LD

    (

     Network_Act_Id, 

     Orig_Imei_Id,

     Orig_Imsi_Id,

     CELL_SITE_ID,

     WIRELESS_ROAMER_TYPE_CD,

     Cell_Lac_Id,

     Sbscrp_Id,

     Act_Start_Dt,

     BATCH_ID

    )

    VALUES

    (

     :Network_Act_Id, 

     :Orig_Imei_Id,

     :Orig_Imsi_Id,

     :CELL_SITE_ID,

     :WIRELESS_ROAMER_TYPE_CD,

     :Cell_Lac_Id,

     :Sbscrp_Id,

     :Act_Start_Dt,

     :BATCH_ID

    );'

   ) ,



   ('INSERT INTO ${DX_CDM_LDB}.NETWORK_INTERCHANGE_CDR_MSC_LD

    (

     Signaling_Act_Id

     ,Orig_Trunk_Grp_Id

     ,Term_Trunk_Grp_Id

     ,MSC_ADDRESS_CD

     ,Call_Int_Dom_Ind

     ,Sbscrp_Id

     ,Act_Start_Dt

     ,BATCH_ID

    )

    VALUES

    (

     :Signaling_Act_Id

     ,:Orig_Trunk_Grp_Id

     ,:Term_Trunk_Grp_Id

     ,:MSC_ADDRESS_CD

     ,:Call_Int_Dom_Ind

     ,:Sbscrp_Id

     ,:Act_Start_Dt

     ,:BATCH_ID

    );'

   )    

   TO OPERATOR (LOAD_LDB_TMP)

   SELECT * FROM OPERATOR (Export_NA_TMP

   ATTR (

    SELECTSTMT =

     '

    SELECT

     CAST (A.Network_Act_Id AS DECIMAL(18,0)) AS Network_Act_Id,

     CAST( A.Act_Start_Dt AS DATE FORMAT ''YYYY-MM-DD'') AS Act_Start_Dt,

     CAST(A.Act_End_Dt  AS DATE FORMAT ''YYYY-MM-DD'') AS Act_End_Dt,

     CAST(A.Act_Duration_Meas AS DECIMAL(9,2)) AS Act_Duration_Meas,

     CAST(A.CHARGE_DURATION_MEAS AS DECIMAL(9,2)) AS CHARGE_DURATION_MEAS,

     CAST(A.CALL_COMPLETION_TYPE_CD AS BYTEINT) AS CALL_COMPLETION_TYPE_CD,

     CAST(A.Orig_Number_Val AS VARCHAR(30)) AS Orig_Number_Val, 

     CAST(A.Term_Number_Val AS VARCHAR(30)) AS Term_Number_Val,

     CAST(A.DIALED_NUMBER_VAL AS VARCHAR(30)) AS DIALED_NUMBER_VAL,    

     CAST(A.CALL_RATE_PERIOD_CD AS BYTEINT)  AS CALL_RATE_PERIOD_CD,

     CAST(COALESCE(D.Sbscrp_Prepaid_Ind,2) AS BYTEINT )  AS PRE_POST_IND, 

     CAST(A.Act_Type_Cd AS BYTEINT)  AS Act_Type_Cd,

     CAST(A.CHARGE_DURATION_UOM_CD  AS BYTEINT)  AS CHARGE_DURATION_UOM_CD,

     CAST(A.Act_Duration_Uom_Cd  AS BYTEINT)   AS Act_Duration_Uom_Cd,

     A.Act_Start_Dt (FORMAT ''HH:MI:SS'') (CHAR(8)) (INTEGER, FORMAT ''99:99:99'') AS Act_Start_Tm,

     CAST(COALESCE (C.Sbscrp_Id, B.ACCS_METH_ID)  AS INTEGER) AS Sbscrp_Id,

     CAST(COALESCE(DRV_OFFR.Offrng_Id,0)AS SMALLINT) AS Offrng_Id,

     CAST(B.ACCS_METH_ID AS INTEGER)   AS ACCS_METH_ID,

     CAST(A.Act_Dir_Type_Cd AS BYTEINT) AS Act_Dir_Type_Cd,

     CAST(A.Orig_Party_Id AS INTEGER) AS Orig_Party_Id,

     CAST(A.Term_Party_Id AS INTEGER) AS Term_Party_Id,

     CAST(A.Orig_Imei_Id AS INTEGER) AS Orig_Imei_Id,

     CAST(A.Orig_Imsi_Id AS INTEGER) AS Orig_Imsi_Id,

     CAST(A.CELL_SITE_ID AS INTEGER) AS CELL_SITE_ID,

     CAST(A.WIRELESS_ROAMER_TYPE_CD AS BYTEINT) AS WIRELESS_ROAMER_TYPE_CD,

     CAST(A.Cell_Lac_Id AS INTEGER) AS Cell_Lac_Id,

     CAST(A.Network_Act_Id AS DECIMAL(18,0)) AS Signaling_Act_Id,

     CAST(A.Orig_Trunk_Grp_Id AS INTEGER) AS Orig_Trunk_Grp_Id,

     CAST(A.Term_Trunk_Grp_Id AS INTEGER) AS Term_Trunk_Grp_Id,

     CAST(A.MSC_ADDRESS_CD AS VARCHAR(50)) AS MSC_ADDRESS_CD,

     CAST(A.Call_Int_Dom_Ind AS BYTEINT) AS Call_Int_Dom_Ind,

     CAST(A.BATCH_ID AS INTEGER) AS BATCH_ID

    FROM 

    ${DX_CDM_LDB}.TMP_MSC_NET_ACT  A  

    INNER JOIN

    ${DX_HLP}.HLP_ACCS_METH  B

    ON

    A.ACCESS_METHOD_NUM = B.ACCS_METH_NUM

    LEFT JOIN

    ${DX_CDM}.ACCS_METH_SBSCRP C

    ON C.Accs_Meth_Id = B.ACCS_METH_ID

    AND

    CAST(A.ACT_START_DT AS TIMESTAMP(0) FORMAT ''DD-MM-YYHH:MI:SS'') >= C.Accs_Meth_Sbscrp_Start_Dttm

    AND

    CAST(A.ACT_START_DT AS TIMESTAMP(0) FORMAT ''DD-MM-YYHH:MI:SS'') < COALESCE(C.Accs_Meth_Sbscrp_End_Dttm, current_timestamp(0))

    LEFT JOIN

    (

    SELECT

     Offrng_Id,

     Sbscrp_Id,

     Sbscrp_Offrng_Start_DTTM,

     Sbscrp_Offrng_End_DTTM

    FROM

     ${DX_CDM}.SBSCRP_OFFRNG_STAT

    )

    DRV_OFFR (Offrng_Id,Sbscrp_Id,Sbscrp_Offrng_Start_DTTM,Sbscrp_Offrng_End_DTTM)

    ON

    C.Sbscrp_Id = DRV_OFFR.Sbscrp_Id

    AND

    CAST(A.ACT_START_DT AS TIMESTAMP(0) FORMAT ''DD-MM-YYHH:MI:SS'') >= DRV_OFFR.Sbscrp_Offrng_Start_DTTM

    AND

    CAST(A.ACT_START_DT AS TIMESTAMP(0) FORMAT ''DD-MM-YYHH:MI:SS'') < COALESCE(DRV_OFFR.Sbscrp_Offrng_End_DTTM, current_timestamp(0))

    LEFT JOIN

    ${DX_CDM}.SBSCRP D

    ON

    C.Sbscrp_Id = D.Sbscrp_Id

   ;'  )

  );

 );

and the Error is as follows:

Teradata Parallel Transporter Version 13.10.00.10

Job log: /opt/teradata/client/13.10/tbuild/logs/MSC_NET_ACT_P2_Group_MSC-14056.out

Job id is MSC_NET_ACT_P2_Group_MSC-14056, running on tpls

Teradata Parallel Transporter Update Operator Version 13.10.00.04

LOAD_LDB_TMP: private log not specified

Teradata Parallel Transporter Export Operator Version 13.10.00.06

Export_NA_TMP: private log not specified

LOAD_LDB_TMP: connecting sessions

Export_NA_TMP: connecting sessions

LOAD_LDB_TMP: preparing target table(s)

LOAD_LDB_TMP: disconnecting sessions

LOAD_LDB_TMP: Total processor time used = '0.17 Second(s)'

LOAD_LDB_TMP: Start : Wed Dec 12 11:45:03 2012

LOAD_LDB_TMP: End   : Wed Dec 12 11:45:10 2012

Export_NA_TMP: disconnecting sessions

Export_NA_TMP: Total processor time used = '0.17 Second(s)'

Export_NA_TMP: Start : Wed Dec 12 11:45:03 2012

Export_NA_TMP: End   : Wed Dec 12 11:45:11 2012

Job step C_Load_MSC_NET_ACT_LD_Table terminated (status 12)

Job MSC_NET_ACT_P2_Group_MSC terminated (status 12)

1 REPLY
Teradata Employee

Re: TPT status code 12 during Insert

You will need to get the entire log (the .out file in the "logs" directory) for this job and use tlogview to output all of the detailed information from all of the operators.

-- SteveF