APT_Operator::UnControlledTermination

sohasaid · Post by **sohasaid** » Thu Jan 07, 2016 2:05 pm

Hi,

We've deployed one job to the prod environment but it's aborting due to below errors. The job is working fine on dev environment and dev & prod servers with same version 11.3.0.0

Error #1:
SUB_STATUS_QUERY,0: The following error occurred on the parallel engine: APT_Operator::UnControlledTermination: From: UnControlledTermination via exception.. (CC_PXAccessor::finishItemInternal, file CC_PXAccessor.cpp, line 1,947)

Error #2:
Reason: [SQLCODE=HY000][Native=37] Error occured while writing into file (CC_NZCommon::checkThreadStatusThrow, file CC_NZCommon.cpp, line 425)

When compiling and rerunning the job, the first error is generated again but for another source stage in the job but the second error didn't show up.

We tried to load the data into a data set in one job has only two stages: source stage and data set and it worked fine. Source stage in Netezza connector and I ensured also that data types are matching the meta data defined in the stage.

I didn't find much posts on 'APT_Operator::UnControlledTermination' error and I'm attaching the full job log here for reference.

Code: Select all


DataStage Report - Detail Log for job: CopyOfSOR_AS_AR_X_LIFE_CYC_RLTNP_INCRMTL_LDR1 
Produced on: 1/7/2016 11:45:35 PM
Project: TEST
Host system: DX954
Items: 1 - 34
Sorted on: Date Sorter

   Item #: 1
   Event ID: 235
   Timestamp: 2016-01-06 20:02:29
   Type: Control
   User Name: dsadm
   Message Id: IIS-DSTAGE-RUN-I-0070
   Message: Starting Job CopyOfSOR_AS_AR_X_LIFE_CYC_RLTNP_INCRMTL_LDR1.
BusinessDate = 2015-12-27
AR_X_AR_TableName = AR_X_AR_RLTNP
AR_X_Lif_Cyc_TableName = AR_X_AR_LIFE_CYC_RLTNP
Takeover_Cessation_UN_ID = -8888
Takeover_Activation_UN_ID = -5555
Genereated_Ceassed_Reason = -9999

   Item #: 4
   Event ID: 238
   Timestamp: 2016-01-06 20:02:29
   Type: Info
   User Name: dsadm
   Message Id: IIS-DSTAGE-RUN-I-0121
   Message: Parallel job initiated

   Item #: 5
   Event ID: 239
   Timestamp: 2016-01-06 20:02:29
   Type: Info
   User Name: dsadm
   Message Id: IIS-DSTAGE-RUN-I-0133
   Message: Parallel job default NLS map UTF-8, default locale OFF

   Item #: 6
   Event ID: 240
   Timestamp: 2016-01-06 20:02:29
   Type: Info
   User Name: dsadm
   Message Id: IIS-DSEE-TFCN-00001
   Message: main_program: IBM InfoSphere DataStage Enterprise Edition 11.3.0.7169 
Copyright (c) 2001, 2005-2014 IBM Corporation. All rights reserved

   Item #: 7
   Event ID: 241
   Timestamp: 2016-01-06 20:02:29
   Type: Info
   User Name: dsadm
   Message Id: IIS-DSEE-TUTL-00031
   Message: main_program: The open files limit is 1024; raising to 10240.

   Item #: 8
   Event ID: 242
   Timestamp: 2016-01-06 20:02:29
   Type: Info
   User Name: dsadm
   Message Id: IIS-DSEE-TFCN-00006
   Message: main_program: conductor uname: -s=Linux; -r=2.6.32-504.12.2.el6.x86_64; -v=#1 SMP Sun Feb 1 12:14:02 EST 2015; -n=dx954; -m=x86_64

   Item #: 9
   Event ID: 243
   Timestamp: 2016-01-06 20:02:29
   Type: Info
   User Name: dsadm
   Message Id: IIS-DSEE-TOSH-00002
   Message: main_program: orchgeneral: loaded
orchsort: loaded
orchstats: loaded

   Item #: 10
   Event ID: 244
   Timestamp: 2016-01-06 20:02:29
   Type: Info
   User Name: dsadm
   Message Id: IIS-CONN-NZ-01059
   Message: SUB_STATUS_QUERY: The connector will run in sequential mode.

   Item #: 11
   Event ID: 245
   Timestamp: 2016-01-06 20:02:30
   Type: Info
   User Name: dsadm
   Message Id: IIS-CONN-NZ-01045
   Message: SUB_STATUS_QUERY: Using case-insensitive indentifiers

   Item #: 12
   Event ID: 246
   Timestamp: 2016-01-06 20:02:30
   Type: Info
   User Name: dsadm
   Message Id: IIS-CONN-NZ-01059
   Message: AR_X_AR_LIFE_CYC_RLTNP: The connector will run in sequential mode.

   Item #: 13
   Event ID: 247
   Timestamp: 2016-01-06 20:02:30
   Type: Info
   User Name: dsadm
   Message Id: IIS-CONN-NZ-01045
   Message: AR_X_AR_LIFE_CYC_RLTNP: Using case-insensitive indentifiers

   Item #: 14
   Event ID: 248
   Timestamp: 2016-01-06 20:02:30
   Type: Info
   User Name: dsadm
   Message Id: IIS-CONN-NZ-01100
   Message: AR_X_AR_LIFE_CYC_RLTNP: The following column(s): BSN_DT from the result set of the query SELECT *
FROM   (
SELECT AR_X_AR_LIFE_CYC_RLTNP_ID, 
               AR_X.AR_ID, 
               AR_X.AR_UNQ_ID_IN_SRC_STM AS ACCOUNT_ID, 
               AR_X.CV_ID, 
               CV.SHRT_NM, 
               AR_X.EFF_DT               AS START_DATE, 
               CASE WHEN  AR_X.END_DT  :: DATE >  '2015-12-27' THEN   '9999-12-31 00:00:00'  ELSE  AR_X.END_DT    END AS END_DATE ,
			   AR_X.BSN_DT, 
               AR_X.CRDT_STATUS_ID, 
               AR_X.CRDT_REASON_ID, 
               AR_X.CL_SCM_ID, 
               AR_X.SRC_STM_ID, 
               AR_X.UNQ_ID_IN_SRC_STM, 
               AR_X.ST, 
               AR_X.CV_ID_REASON, 
               AR_X.CL_SCM_ID_REASON, 
               AR_X.REASON_UNQ_ID_IN_SRC_STM 
        FROM    AR_X_AR_LIFE_CYC_RLTNP AR_X, 
               CL_CV CV 
        WHERE  AR_X.CV_ID = CV.CL_CV_ID 
               AND ( AR_X.BSN_DT::DATE <= '2015-12-27' OR AR_X.END_DT::DATE <= '2015-12-27' ) 
               AND AR_X_AR_LIFE_CYC_RLTNP_TP_ID = 2
--			  and AR_UNQ_ID_IN_SRC_STM = 695952364
			) A   
			WHERE END_DATE = '9999-12-31 00:00:00' were not found in the output link schema. These columns will be ignored.

   Item #: 15
   Event ID: 249
   Timestamp: 2016-01-06 20:02:30
   Type: Info
   User Name: dsadm
   Message Id: IIS-CONN-NZ-01059
   Message: Party_Changes: The connector will run in sequential mode.

   Item #: 16
   Event ID: 250
   Timestamp: 2016-01-06 20:02:30
   Type: Info
   User Name: dsadm
   Message Id: IIS-CONN-NZ-01045
   Message: Party_Changes: Using case-insensitive indentifiers

   Item #: 17
   Event ID: 251
   Timestamp: 2016-01-06 20:02:30
   Type: Info
   User Name: dsadm
   Message Id: IIS-CONN-NZ-01045
   Message: AR_X_AR_LIFE_CYC_RLTNP_INC_INTERM: Using case-insensitive indentifiers

   Item #: 18
   Event ID: 252
   Timestamp: 2016-01-06 20:02:30
   Type: Info
   User Name: dsadm
   Message Id: IIS-CONN-NZ-01060
   Message: AR_X_AR_LIFE_CYC_RLTNP_INC_INTERM: Generated table action SQL: DROP TABLE AR_X_AR_LIFE_CYC_RLTNP_INC_INTERM

   Item #: 19
   Event ID: 253
   Timestamp: 2016-01-06 20:02:30
   Type: Info
   User Name: dsadm
   Message Id: IIS-CONN-NZ-01050
   Message: AR_X_AR_LIFE_CYC_RLTNP_INC_INTERM: Table action DROP completed successfully.

   Item #: 20
   Event ID: 254
   Timestamp: 2016-01-06 20:02:30
   Type: Info
   User Name: dsadm
   Message Id: IIS-CONN-NZ-01060
   Message: AR_X_AR_LIFE_CYC_RLTNP_INC_INTERM: Generated table action SQL: CREATE TABLE AR_X_AR_LIFE_CYC_RLTNP_INC_INTERM (END_DATE TIMESTAMP NOT NULL, SUBBASE_END_DATE TIMESTAMP NOT NULL, UNQ_ID_IN_SRC_STM INT, INCRT_DATE DATE NOT NULL, SUB_STATUS VARCHAR(100) NOT NULL, ACC_STATUS VARCHAR(100) NOT NULL, REASON_UNQ_ID_IN_SRC_STM INT, RSN_ID INT, CLS_RSN_ID INT, CLS_RSN_CD VARCHAR(40), START_DATE TIMESTAMP NOT NULL, ACCOUNT_ID INT NOT NULL, SUB_ID INT NOT NULL, ACCOUNT_LIFE_CYC_RLTNP_ID BIGINT NOT NULL, AR_ID INT NOT NULL, CV_ID INT NOT NULL, CRDT_STATUS_ID INT NOT NULL, CRDT_REASON_ID INT NOT NULL, CL_SCM_ID INT, SRC_STM_ID INT, ST INT, CV_ID_REASON INT, CL_SCM_ID_REASON INT) DISTRIBUTE ON RANDOM

   Item #: 21
   Event ID: 255
   Timestamp: 2016-01-06 20:02:30
   Type: Info
   User Name: dsadm
   Message Id: IIS-CONN-NZ-01051
   Message: AR_X_AR_LIFE_CYC_RLTNP_INC_INTERM: Table action CREATE completed successfully.

   Item #: 22
   Event ID: 256
   Timestamp: 2016-01-06 20:02:36
   Type: Info
   User Name: dsadm
   Message Id: IIS-DSEE-TFSC-00001
   Message: main_program: APT configuration file: /opt/ibm/InformationServer/Server/Configurations/default.apt
{
	node "node1"
	{
		fastname "dx954"
		pools ""
		resource disk "/opt/ibm/InformationServer/Server/Datasets" {pools ""}
		resource scratchdisk "/opt/ibm/InformationServer/Server/Scratch" {pools ""}
	}
}

   Item #: 23
   Event ID: 257
   Timestamp: 2016-01-06 20:02:36
   Type: Fatal
   User Name: dsadm
   Message Id: IIS-CONN-DAAPI-00495
   Message: SUB_STATUS_QUERY,0: The following error occurred on the parallel engine: APT_Operator::UnControlledTermination: From: UnControlledTermination via  exception.. (CC_PXAccessor::finishItemInternal, file CC_PXAccessor.cpp, line 1,947)

   Item #: 24
   Event ID: 258
   Timestamp: 2016-01-06 20:02:36
   Type: Fatal
   User Name: dsadm
   Message Id: IIS-CONN-NZ-01004
   Message: SUB_STATUS_QUERY,0: Failed to execute SQL statement: INSERT INTO NZCC_20160106200232602697_65451_0 SELECT 
T2.AR_X_AR_LIFE_CYC_RLTNP_ID::BIGINT  AR_X_AR_LIFE_CYC_RLTNP_ID,
T2.AR_ID::INTEGER AR_ID , 
T1.ACCOUNT_ID::INTEGER ACCOUNT_ID, 
T1.SUB_ID::INTEGER SUB_ID, 
T2.CV_ID::INTEGER CV_ID,
T2.SHRT_NM::character varying(100) as SHRT_NM ,
T2.CRDT_STATUS_ID::INTEGER CRDT_STATUS_ID,  
T2.CRDT_REASON_ID::INTEGER CRDT_REASON_ID,
T2.CL_SCM_ID::INTEGER CL_SCM_ID, 
T2.SRC_STM_ID::INTEGER SRC_STM_ID, 
T2.UNQ_ID_IN_SRC_STM::INTEGER UNQ_ID_IN_SRC_STM, 
T2.ST::INTEGER ST,
T2.CV_ID_REASON::INTEGER CV_ID_REASON, 
T2.CL_SCM_ID_REASON::INTEGER CL_SCM_ID_REASON, 
T2.REASON_UNQ_ID_IN_SRC_STM::INTEGER REASON_UNQ_ID_IN_SRC_STM ,
CASE WHEN T1.EFF_FROM > T2.START_DATE THEN T1.EFF_FROM ELSE T2.START_DATE END AS START_DATE,
CASE WHEN NVL(T1.EFF_TILL,CURRENT_DATE) < NVL(T2.END_DATE,CURRENT_DATE) THEN NVL(T1.EFF_TILL,TO_DATE('9999-12-31','YYYY-MM-DD')) ELSE NVL(T2.END_DATE,TO_DATE('9999-12-31','YYYY-MM-DD')) END AS END_DATE, 
T1.RSN_ID::INTEGER RSN_ID,
T1.CLS_RSN_ID::INTEGER CLS_RSN_ID, 
T1.CLS_RSN_CD::character varying(40) as CLS_RSN_CD
FROM 
(SELECT AR_X.AR_UNQ_ID_IN_SRC_STM AS ACCOUNT_ID, 
AR_X.SBJ_AR_ID AS SUB_ID, 
AR_X.EFF_DT AS EFF_FROM, 
AR_X.AC_END_DT   AS EFF_TILL, 
AR_X.RSN_ID, 
AR_X.CLS_RSN_ID, 
CV.CL_CODE AS CLS_RSN_CD
	   FROM AR_X_AR_RLTNP AR_X LEFT OUTER JOIN CL_CV CV
	   ON AR_X.CLS_RSN_ID = CV.CL_CV_ID 
       WHERE 
--exclude the multiline data
AR_X.AR_X_AR_RLTNP_TP_ID = 
(
SELECT B.CL_CV_ID 
fROM CL_SCM A,  CL_CV B
WHERE A.CL_SCM_ID = B.CL_SCM_ID
AND UPPER(A.CL_SCM_NM) = 'ARRANGEMENT / ARRANGEMENT RLTNP'
AND UPPER(B.CL_CODE) = 'ARRANGEMENT TO SUBSCRIPTION')                         
) T1,
(
SELECT  
AR_X_AR_LIFE_CYC_RLTNP_ID,
AR_X.AR_ID, AR_X.AR_UNQ_ID_IN_SRC_STM AS ACCOUNT_ID
	   , AR_X.CV_ID, CV.SHRT_NM, AR_X.CRDT_STATUS_ID, AR_X.CRDT_REASON_ID
	   , AR_X.CL_SCM_ID, AR_X.SRC_STM_ID, AR_X.UNQ_ID_IN_SRC_STM, AR_X.ST, AR_X.CV_ID_REASON, AR_X.CL_SCM_ID_REASON, AR_X.REASON_UNQ_ID_IN_SRC_STM
       , date_trunc('second', AR_X.EFF_DT) AS START_DATE
       , date_trunc('second', AR_X.END_DT) AS END_DATE     
	   FROM  AR_X_AR_LIFE_CYC_RLTNP AR_X, CL_CV CV
	   WHERE AR_X.CV_ID = CV.CL_CV_ID
AND (AR_X.BSN_DT::DATE =  '2015-12-27' OR AR_X.END_DT::DATE =  '2015-12-27')
AND TO_CHAR(AR_X.EFF_DT, 'YYYY-MM-DD HH:MM:SS') <> TO_CHAR(AR_X.END_DT, 'YYYY-MM-DD HH:MM:SS')
and AR_X.EFF_DT < AR_X.END_DT
-- exclude invalid records with same start & end dates
AND  AR_X_AR_LIFE_CYC_RLTNP_TP_ID = 2   
) T2
WHERE T1.ACCOUNT_ID = T2.ACCOUNT_ID
AND T1.EFF_FROM < NVL(T2.END_DATE,CURRENT_DATE)
AND NVL(T1.EFF_TILL, CURRENT_DATE) > T2.START_DATE
--and t1.account_id = 586482750
---and sub_id = 892936
order by SUB_ID, START_DATE asc
. Reason: [SQLCODE=HY000][Native=37] Error occured while writing into file (CC_NZCommon::checkThreadStatusThrow, file CC_NZCommon.cpp, line 425)

   Item #: 25
   Event ID: 259
   Timestamp: 2016-01-06 20:02:36
   Type: Fatal
   User Name: dsadm
   Message Id: IIS-DSEE-TFOR-00089
   Message: SUB_STATUS_QUERY,0: The runLocally() of the operator failed.

   Item #: 26
   Event ID: 260
   Timestamp: 2016-01-06 20:02:36
   Type: Info
   User Name: dsadm
   Message Id: IIS-DSEE-TFOR-00094
   Message: SUB_STATUS_QUERY,0: Output 0 produced 0 records.

   Item #: 27
   Event ID: 261
   Timestamp: 2016-01-06 20:02:37
   Type: Fatal
   User Name: dsadm
   Message Id: IIS-DSEE-TFPM-00040
   Message: SUB_STATUS_QUERY,0: Operator terminated abnormally: runLocally() did not return APT_StatusOk

   Item #: 28
   Event ID: 262
   Timestamp: 2016-01-06 20:02:37
   Type: Fatal
   User Name: dsadm
   Message Id: IIS-DSEE-TFPM-00192
   Message: node_node1: Player 1 terminated unexpectedly.

   Item #: 29
   Event ID: 263
   Timestamp: 2016-01-06 20:02:37
   Type: Fatal
   User Name: dsadm
   Message Id: IIS-DSEE-TFPM-00339
   Message: main_program: APT_PMsectionLeader(1, node1), player 1 - Unexpected termination by Unix signal 9(SIGKILL).

   Item #: 30
   Event ID: 264
   Timestamp: 2016-01-06 20:02:42
   Type: Fatal
   User Name: dsadm
   Message Id: IIS-DSEE-TFPM-00338
   Message: main_program: APT_PMsectionLeader(1, node1), player 4 - Unexpected exit status 1.
APT_PMsectionLeader(1, node1), player 5 - Unexpected exit status 1.
APT_PMsectionLeader(1, node1), player 8 - Unexpected exit status 1.
APT_PMsectionLeader(1, node1), player 10 - Unexpected exit status 1.
APT_PMsectionLeader(1, node1), player 12 - Unexpected exit status 1.
APT_PMsectionLeader(1, node1), player 13 - Unexpected exit status 1.
APT_PMsectionLeader(1, node1), player 14 - Unexpected exit status 255.
APT_PMsectionLeader(1, node1), player 15 - Unexpected exit status 1.
APT_PMsectionLeader(1, node1), player 16 - Unexpected exit status 1.
APT_PMsectionLeader(1, node1), player 17 - Unexpected exit status 1.
APT_PMsectionLeader(1, node1), player 18 - Unexpected exit status 1.

   Item #: 31
   Event ID: 265
   Timestamp: 2016-01-06 20:02:42
   Type: Fatal
   User Name: dsadm
   Message Id: IIS-DSEE-TFSC-00011
   Message: main_program: Step execution finished with status = FAILED.

   Item #: 32
   Event ID: 266
   Timestamp: 2016-01-06 20:02:42
   Type: Info
   User Name: dsadm
   Message Id: IIS-DSEE-TCOS-00026
   Message: main_program: Startup time, 0:08; production run time, 0:04.

   Item #: 33
   Event ID: 267
   Timestamp: 2016-01-06 20:02:42
   Type: Control
   User Name: dsadm
   Message Id: IIS-DSTAGE-RUN-I-0075
   Message: Job CopyOfSOR_AS_AR_X_LIFE_CYC_RLTNP_INCRMTL_LDR1 aborted.

   Item #: 34
   Event ID: 268
   Timestamp: 2016-01-07 23:35:59
   Type: Reset
   User Name: dsadm
   Message Id: IIS-DSTAGE-REP-I-0066
   Message: Log prior to last run cleared by user

End of report.

Your support is appreciated.
Thanks

ray.wurlod · Post by **ray.wurlod** » Thu Jan 07, 2016 3:43 pm

It appears that your source is Netezza. Have the DBAs search for any connection problem (e.g. timeout) that may have occurred.

I once worked at a site where the maximum time they would allow for a query was 8 minutes. This was unhelpful when trying to do profiling!

sohasaid · Post by **sohasaid** » Fri Jan 08, 2016 3:48 am

Thanks Ray for your reply.

But I think if was about the connection timeout, the test job wouldn't have run with a dataset as a target. Also the query is not that complex and it retrieves results quickly while running from tools like DBVisualizer.

I don't really understand the error message, and not sure about the root cause behind.

Code: Select all

The following error occurred on the parallel engine: APT_Operator::UnControlledTermination: From: UnControlledTermination via exception.. (CC_PXAccessor::finishItemInternal, file CC_PXAccessor.cpp, line 1,947)

Thanks

sohasaid · Post by **sohasaid** » Thu Jan 14, 2016 11:53 am

We had to create a separate job with source stage and dataset stage only and use the dataset later on the original job instead of the source stage.

But we didn't manage know the root cause of such an issue.

Thanks for your support.

qt_ky · Post by **qt_ky** » Fri Jan 15, 2016 6:04 am

To find the cause, you would need to contact Support. Send the full detailed job log. Also highlight the "[SQLCODE=HY000]" fatal error. That one usually means something went wrong with your database connection. It could have any number of causes.