I am running below query using user defined SQL in oracle OCI--Trasformer--To OCI stage
Code: Select all
SELECT
CMSITEM.CUST_ACCT_ID ,
CA.PRI_CUST_ACCT_ID,
CMSITEM.ITEM_NUM,
I2.EM_ITEM_NUM ALT_EQ_ITEM_NUM,
PRC.CNTRC_BID_PRC ALT_EQ_ITEM_PRC
FROM
(
SELECT
CMS.ITEM_NUM
, CMS.CUST_ACCT_ID
, I.GNRC_ID GNRC_ID
FROM L_CMS_RLZD_SAV CMS,
L_ITEM I
WHERE CMS.ITEM_NUM = I.EM_ITEM_NUM
) CMSITEM
INNER JOIN L_ITEM I2
ON CMSITEM.GNRC_ID = I2.GNRC_ID
INNER JOIN L_CUST_ACCT CA
ON CMSITEM.CUST_ACCT_ID = CA.CUST_ACCT_ID
INNER JOIN S_DRUG_ITEM_PRC PRC
ON (I2.EM_ITEM_NUM = PRC.ITEM_NUM and CA.PRI_CUST_ACCT_ID = trim(PRC.cust_acct_id))
L_ITEM is having 142000 Rows
L_CUST_ACCT is having 188000 Records
S_DRUG_ITEM_PRC is having 400 millions records
The Job is not returning any rows in datastage in testing envirnment, and In dev where in S_DRUG_ITEM_PRC PRC contains 72 millions the datastage job is taking 2 hours and giving out result, But in production the S_DRUG_ITEM_PRC PRC will be having 400 millions rows, I tried running the sql in TOAD in testing env, it giving results in 2 mins but in datastage job the sql is not giving answer and the job keeps running,
Please help me with an approach of solving this problem
I tried dividing the SQL taking out only S_DRUG_ITEM_PRC PRC . Without S_DRUG_ITEM_PRC PRC the query gives 1.2 millions, I stored that result in Hashed file and tried to inner join to S_DRUG_ITEM_PRC PRC table
When i create the hashed file I have to give all the 4 colomn as keys or else rows are getting rejected. After that I tried to do look up with the 2 keys of S_DRUG_ITEM_PRC PRC, when i tried to view data, after deselecting 2 colomn not required for joining , I am not able to view data and after job running for 5 hours , it loaded 0 rows in target,
Experts Please help how to run this job successfully in datastage server ,
Thanks a lot for reading this with patience
How to Achieve sucessful running and loading of the target
Why is the query running in Toad with 400 million and refuses to gives result in DataStae
How to join Hashed file with 4 keys with 2 joining keys( I deselected 2 keys of hashed file) I know the something is terrible wrong
I had talked with DBA and he is proposing function index on S_DRUG_ITEM_PRC PRC, S_DRUG_ITEM_PRC PRC is a View off four tables with 148, 72,72 , 108 million records as T_DRUG_ITEM_PRC_A1..A2..A3...A4 respectively
Thanks in advance