topic Re: Extraction logic in Talend Studio

Extraction logic

Anonymous — Thu, 20 Jul 2017 05:08:10 GMT

Extraction logic: Extract the data from oracle to stage table.

1) what is best way to write below code in talend.

SELECT f_amt,
l_amt,
NULL flag,
NULL id,
batch_no batch_no,
ac_branch t_br_code,
rela_acc related_acc,
drcr_ind drcr_ind
FROM rec_hist a, rec_tab_hist s
WHERE a.trn_ref_no = s.trn_ref_no
AND a.trn_dt = (SELECT MAX(trunc(starttime)) FROM xyz)
AND a.ac_no IN (SELECT VALUE
FROM rec_tab_process_param
WHERE pro_name = 'MTN'
AND param_name = 'CODE')
UNION
SELECT f_amt,
l_amt,
NULL flag,
NULL id,
batch_no batch_no,
ac_branch t_br_code,
rela_acc related_acc,
drcr_ind drcr_ind
FROM rec_hist a
WHERE a.trn_ref_no = s.trn_ref_no
AND a.trn_dt = (SELECT MAX(trunc(starttime)) FROM xyz)
AND a.ac_no IN (SELECT VALUE
FROM rec_tab_process_param
WHERE pro_name = 'MTN'
AND param_name = 'CODE')

Thanks

Re: Extraction logic

Anonymous — Thu, 20 Jul 2017 08:23:08 GMT

Take the whole SQL statement and put it in tOracleInput component. If it need parameterization, then use context variables or globalMap variables to build the whole string. But put it in the query part of the tOracleInput. This way you leverage your DB power to extract only what you need. That will be the fastest way. And turn on the cursors in the Oracle component.

Re: Extraction logic

Anonymous — Thu, 20 Jul 2017 08:37:56 GMT

Thanks for valuable input.

Could you please give me example on parameterization using context variables or globalMap variables.

and one more point as you said, turn on the cursors in the Oracle component.

In advance setting "Use cursor" ? How it will faster. Can you please elaborate few points on this option.

Thanks

Shridhar

Re: Extraction logic

Anonymous — Thu, 20 Jul 2017 09:21:47 GMT

Hi,

The query statement is just a string. You will build your string with variables concatenated to it

"Select blabla from table where column=" + context.somevariable

Turning on the cursors is 1 checkbox away:-) You can easily test it yourself on your system to identify the performance difference. Depending on what your are doing, you will see the performance difference. With Oracle, our experience is that using cursors is faster.

Re: Extraction logic

Anonymous — Thu, 20 Jul 2017 10:07:42 GMT

Thanks for your suggestion.

Using context variable or globalMap variables, which is best way to do any examples.

Regards

Shridhar

Re: Extraction logic

Anonymous — Thu, 20 Jul 2017 10:35:26 GMT

globalMap if you want keep control of the variables within the Job. Context variable if you want to allow extenal values to be passed to the job when it is started. I generally recommend globalMap to avoid having too many context variables. Context variables should be kept to a minimum. As per defensive programming, if you don't want sql injection issues, then you will use globalMap and control the values in your variables:-)

Re: Extraction logic

Anonymous — Thu, 20 Jul 2017 10:50:37 GMT

Thanks for your time and inputs.

Lot of valuable information.

Regards

Shridhar