datastage job not started but it is showing running state

[arasan]

I am calling a store procedure through ODBC stage when i run the JOb the status of the job is showing as running state.

when i looked the status it states that the job start date is not there in the director.what might be the cause for this problem.

i mannually executed the store procedure it is executing fine.

i have called many store proceduces they executed properly through datastage.

this one is not throwing any error message.i am thinking that if the datastage job is in running state ,then it should throw some error but in this case i hope it is not started at all but showing as running state.

[ArndW]

Do you have a before-job subroutine call? Also, do any other jobs show this problem or just this one?

[arasan]

no i dont have before_job_subroutine call,

only this one is causing problem.

[ray.wurlod]

Is there a DSD.RUN process on the server associated with this job? If not, does clearing the job's status file return it to a known status? You may then want to put some diagnostic routines in as before/after subroutines to see how far the job gets.

[arasan]

Hi ray,

if i call other store procedure through datastage it is succesful how come this.since evry thing runs under same server.

[chulett]

I don't see how you can expect anyone to be able to answer that - anyone other than you, that is. We know nothing about your procedures or what may or may not be different or special about this one.

[arasan]

if i exec the store procedure it is running fine and getting the expected resuld but when i try to call the store procedure it is in running state not throwing any error msg.

CREATE PROCEDURE [DBO].[POLICY_LOAD] AS


CREATE TABLE #UPDATEROWS (
[POLICY_NBR_ID] [char] (8) NOT NULL ,
[POLICY_FORM_CD] [char] (4) NOT NULL ,
[POLICY_EFF_DT] [datetime] NOT NULL ,
[POLICY_EXP_DT] [datetime] NULL ,
[PROD_CD] [char] (5) NOT NULL ,
[LINES_COVERED_CD] [varchar] (40) NULL ,
[PD_DEDUCT_AMT] [decimal](10, 2) NULL ,
[DIAGNOSTIC_EQUIP_COV_IND] [bit] NULL ,
[PRODUCTION_MACH_COV_IND] [bit] NULL ,
[BI_DEDUCT_TYPE_CD] [char] (1) NULL ,
[BI_DEDUCT_AMT] [decimal](11, 2) NULL ,
[AMMONIA_LIMT_AMT] [decimal](10, 0) NULL ,
[AMMONIA_DEDUCT_AMT] [decimal](9, 0) NULL ,
[SERVICE_INTERRUPT_IND] [bit] NOT NULL ,
[SPOIL_DAMG_IND] [bit] NOT NULL,
[CONT_TIME_ELE_IND] [bit] NOT NULL,
[POL_PREM_AMT] [decimal](10, 2) NULL ,
[BUSINESS_UNIT_CD] [char] (4) NOT NULL ,
[BUSINESS_LEVEL_CD] [char] (1) NULL ,
[POL_DATA_SOURCE_CD] [char] (4) NULL ,
[POLICY_CANCEL_DT] [datetime] NULL ,
[REINS_COMPANY_CD] [char] (3) NULL ,
[POL_STATUS_CD] [char] (1) NOT NULL ,
[ACTIVE_POL_IND] [char] (1) NULL ,
[REPORTING_OFFICE_CD] [char] (3) NULL ,
[OCCUPANCY_CD] [varchar] (5) ,
[SIC_CD] [varchar] (4) ,
[AMMONIA_TYPE_CD] [char] (1) NULL ,
[OTH_CARR_POL_NBR] [char] (20) NULL ,
[OMNIBUS_IND] [bit] NOT NULL,
[ACCT_NBR] [char] (9) NOT NULL,
[INSURED_NM] [varchar] (45) NULL ,
[LAST_CHG_DT] [datetime] NULL ,
[LAST_CHG_ID_CD] [varchar] (15) NULL

)

CREATE TABLE #INSERTROWS (
[POLICY_NBR_ID] [char] (8) NOT NULL ,
[POLICY_FORM_CD] [char] (4) NOT NULL ,
[POLICY_EFF_DT] [datetime] NOT NULL ,
[POLICY_EXP_DT] [datetime] NULL ,
[PROD_CD] [char] (5) NOT NULL ,
[LINES_COVERED_CD] [varchar] (40) NULL ,
[PD_DEDUCT_AMT] [decimal](10, 2) NULL ,
[DIAGNOSTIC_EQUIP_COV_IND] [bit] NULL ,
[PRODUCTION_MACH_COV_IND] [bit] NULL ,
[BI_DEDUCT_TYPE_CD] [char] (1) NULL ,
[BI_DEDUCT_AMT] [decimal](11, 2) NULL ,
[AMMONIA_LIMT_AMT] [decimal](10, 0) NULL ,
[AMMONIA_DEDUCT_AMT] [decimal](9, 0) NULL ,
[SERVICE_INTERRUPT_IND] [bit] NOT NULL ,
[SPOIL_DAMG_IND] [bit] NOT NULL,
[CONT_TIME_ELE_IND] [bit] NOT NULL,
[POL_PREM_AMT] [decimal](10, 2) NULL ,
[BUSINESS_UNIT_CD] [char] (4) NOT NULL ,
[BUSINESS_LEVEL_CD] [char] (1) NULL ,
[POL_DATA_SOURCE_CD] [char] (4) NULL ,
[POLICY_CANCEL_DT] [datetime] NULL ,
[REINS_COMPANY_CD] [char] (3) NULL ,
[POL_STATUS_CD] [char] (1) NOT NULL ,
[ACTIVE_POL_IND] [char] (1) NULL ,
[REPORTING_OFFICE_CD] [char] (3) NULL ,
[OCCUPANCY_CD] [varchar] (5) ,
[SIC_CD] [varchar] (4) ,
[AMMONIA_TYPE_CD] [char] (1) NULL ,
[OTH_CARR_POL_NBR] [char] (20) NULL ,
[OMNIBUS_IND] [bit] NOT NULL,
[ACCT_NBR] [char] (9) NOT NULL,
[INSURED_NM] [varchar] (45) NULL ,
[LAST_CHG_DT] [datetime] NULL ,
[LAST_CHG_ID_CD] [varchar] (15) NULL

)




INSERT INTO #INSERTROWS
SELECT
STG_POL.POLICY_NBR_ID,
STG_POL.POLICY_FORM_CD,
STG_POL.POLICY_EFF_DT,
STG_POL.POLICY_EXP_DT,
STG_POL.PROD_CD,
STG_POL.LINES_COVERED_CD,
STG_POL.PD_DEDUCT_AMT,
STG_POL.DIAGNOSTIC_EQUIP_COV_IND,
STG_POL.PRODUCTION_MACH_COV_IND,
STG_POL.BI_DEDUCT_TYPE_CD,
STG_POL.BI_DEDUCT_AMT,
STG_POL.AMMONIA_LIMT_AMT,
STG_POL.AMMONIA_DEDUCT_AMT,
STG_POL.SERVICE_INTERRUPT_IND,
STG_POL.SPOIL_DAMG_IND,
STG_POL.CONT_TIME_ELE_IND,
STG_POL.POL_PREM_AMT,
STG_POL.BUSINESS_UNIT_CD,
STG_POL.BUSINESS_LEVEL_CD,
STG_POL.POL_DATA_SOURCE_CD,
STG_POL.POLICY_CANCEL_DT,
STG_POL.REINS_COMPANY_CD,
STG_POL.POL_STATUS_CD,
NULL,
STG_POL.REPORTING_OFFICE_CD,
STG_POL.OCCUPANCY_CD,
STG_POL.SIC_CD,
STG_POL.AMMONIA_TYPE_CD,
STG_POL.OTH_CARR_POL_NBR,
STG_POL.OMNIBUS_IND,
STG_POL.ACCT_NBR,
STG_POL.INSURED_NM,
STG_POL.LAST_CHG_DT,
STG_POL.LAST_CHG_ID_CD
FROM LPEBI_STG.dbo.POLICY STG_POL




INSERT INTO #UPDATEROWS
SELECT
STG_POL.POLICY_NBR_ID,
STG_POL.POLICY_FORM_CD,
STG_POL.POLICY_EFF_DT,
STG_POL.POLICY_EXP_DT,
STG_POL.PROD_CD,
STG_POL.LINES_COVERED_CD,
STG_POL.PD_DEDUCT_AMT,
STG_POL.DIAGNOSTIC_EQUIP_COV_IND,
STG_POL.PRODUCTION_MACH_COV_IND,
STG_POL.BI_DEDUCT_TYPE_CD,
STG_POL.BI_DEDUCT_AMT,
STG_POL.AMMONIA_LIMT_AMT,
STG_POL.AMMONIA_DEDUCT_AMT,
STG_POL.SERVICE_INTERRUPT_IND,
STG_POL.SPOIL_DAMG_IND,
STG_POL.CONT_TIME_ELE_IND,
STG_POL.POL_PREM_AMT,
STG_POL.BUSINESS_UNIT_CD,
STG_POL.BUSINESS_LEVEL_CD,
STG_POL.POL_DATA_SOURCE_CD,
STG_POL.POLICY_CANCEL_DT,
STG_POL.REINS_COMPANY_CD,
STG_POL.POL_STATUS_CD,
NULL,
STG_POL.REPORTING_OFFICE_CD,
STG_POL.OCCUPANCY_CD,
STG_POL.SIC_CD,
STG_POL.AMMONIA_TYPE_CD,
STG_POL.OTH_CARR_POL_NBR,
STG_POL.OMNIBUS_IND,
STG_POL.ACCT_NBR,
STG_POL.INSURED_NM,
STG_POL.LAST_CHG_DT,
STG_POL.LAST_CHG_ID_CD
FROM LPEBI_STG.dbo.POLICY STG_POL
INNER JOIN POLICY LPEBI_POL
ON STG_POL.POLICY_NBR_ID=LPEBI_POL.POL_NBR_ID
AND STG_POL.POLICY_FORM_CD=LPEBI_POL.POL_FORM_CD_ID
AND STG_POL.POLICY_EFF_DT=LPEBI_POL.POL_EFF_DT_ID

delete #INSERTROWS from #INSERTROWS IR
inner JOIN #UPDATEROWS UP
ON IR.POLICY_NBR_ID=UP.POLICY_NBR_ID
AND IR.POLICY_FORM_CD=UP.POLICY_FORM_CD
AND IR.POLICY_EFF_DT=UP.POLICY_EFF_DT

--select count(*) from #UPDATEROWS
--select count(*) from #INSERTROWS


UPDATE #UPDATEROWS
SET ACTIVE_POL_IND ='Y'
WHERE POL_STATUS_CD='A'

UPDATE #UPDATEROWS
SET ACTIVE_POL_IND ='N'
WHERE ACTIVE_POL_IND is null


UPDATE #INSERTROWS
SET ACTIVE_POL_IND ='Y'
WHERE POL_STATUS_CD='A'

UPDATE #INSERTROWS
SET ACTIVE_POL_IND ='N'
WHERE ACTIVE_POL_IND is null



INSERT INTO POLICY
(POL_NBR_ID, POL_FORM_CD_ID, POL_EFF_DT_ID, POL_END_DT, REINS_COMPANY_CD, BUSINESS_LEVEL_CD, PRODUCER_CD, OCCUPANCY_CD, OTH_CARR_POL_NBR, ACTIVE_POL_IND, UWO_CD_ID, SAI_NBR_ID, ENGR_BUS_UNIT_CD)
Select
POLICY_NBR_ID,
POLICY_FORM_CD,
POLICY_EFF_DT,
POLICY_EXP_DT,
REINS_COMPANY_CD,
BUSINESS_LEVEL_CD,
PROD_CD,
OCCUPANCY_CD,
OTH_CARR_POL_NBR,
ACTIVE_POL_IND,
REPORTING_OFFICE_CD,
ACCT_NBR,
BUSINESS_UNIT_CD
from #INSERTROWS

--select @@ROWCOUNT as 'INSERTED ROWS'


UPDATE POLICY
SET POL_END_DT = UP.POLICY_EXP_DT,
REINS_COMPANY_CD = UP.REINS_COMPANY_CD,
BUSINESS_LEVEL_CD = UP.BUSINESS_LEVEL_CD,
PRODUCER_CD = UP.PROD_CD,
OCCUPANCY_CD = UP.OCCUPANCY_CD,
OTH_CARR_POL_NBR = UP.OTH_CARR_POL_NBR,
ACTIVE_POL_IND = UP.ACTIVE_POL_IND,
UWO_CD_ID = UP.REPORTING_OFFICE_CD,
SAI_NBR_ID = UP.ACCT_NBR,
ENGR_BUS_UNIT_CD = UP.BUSINESS_UNIT_CD
FROM POLICY POL, #UPDATEROWS UP
WHERE (POL.POL_NBR_ID = UP.POLICY_NBR_ID
AND POL.POL_FORM_CD_ID = UP.POLICY_FORM_CD
AND POL.POL_EFF_DT_ID = UP.POLICY_EFF_DT)

--select @@ROWCOUNT as 'UPDATED ROWS'



DROP TABLE #UPDATEROWS
DROP TABLE #INSERTROWS

SELECT 'POLICY_LOAD_OK' + ': ' + Cast(GetDate() as Char(23)) AS RSLT

Code: Select all

[DSguru2B]

It might still be running. Be patient. What happens when you run it from your favourite sql tool. How much time does it take for the stored proc. to complete outside datastage?

[chulett]

Ugg... that was not a request for you to post the procedure code.

How long do you give it before you declare that it is 'hung'?

[arasan]

the time taken to exec the store proc at sql server is approximately 9 min but when i call through DS it takes more time dont know what is happening at the back end.

if it is a DS job then it shows where we went wrong but for store proc there is no info.

[chulett]

if it is a DS job then it shows where we went wrong but for store proc there is no info.

Of course - any error handling has to happen inside the procedure. All DataStage is doing is running the dang thing. I don't see any exception handling or any mechanism to raise an error. You'd have to enlist the help of your DBA to see if they could trace what is going on while the procedure runs.

[OT] Personally, I don't see the fascination with doing things this way. All that proc work could be easily done in a DataStage job where you would have full access to the metadata, business logic, process metrics, yada yada yada. Kind of like buying a shiny new sports car and all you use it for is to tow your old car from place to place.