BODS Long_to_Varchar function performance issue

[Former Member]

Dear BODS Group members,

Good Day!

Am facing an performance issue in SAP BODS.

We have an job which has oracle table(oracle 11g) as source and loads it to the BW targets.

In the source table we have an column desc with data type Long and loading it to BW target with column zz_desc of data type varchar(250).

So we mapped this columns with the function long_to_varchar(des) to zz_desc

But, while running the job its running very very slow especially for this long_to_vrachar function and it's taking 16 hours for just 1.5 million records(i tested with one to one map it loaded in 10 mins).

The ds version is 4.2 and the os platform is unix.

1. i tried to increase the arrray fetch size and rows per limit to 5000 and also increased number of loaders but, no luck
2. also tried with the degree of parallelism no luck
3. tried with almost all optimising options no luck
4. also changed target to some template table and tried same issue no luck

Job design

Source(oracle table)--->query(Long_to_varchar function)--->target(BW datsource)

Anyone please help me on this.

Regards,
Prasanna

[former_member187605 · Accepted solution]

Are you sure your column is LONG and not CLOB? If I am not mistaken, LONG was deprecated in Oracle, many years ago.

If you're only interested in the first 250 characters, you can define a view in Oracle casting your clob to varchar(250) and use it as the source of your data flow.

[Former Member · Accepted solution]

Dear Drik,

Many thanks for the reply. Yes in the oracle table it is in CLOB data type when we import into your BODS it's showing in Long data type for that column.

Also, we need till 3500 characters from that column.

Please help me out on this issue.

DESC          CLOB           DEFAULT EMPTY_CLOB(),

Regards,

Prasannna

[former_member187605 · Accepted solution]

create view <oracle view> as select <any other columns you need>, to_char(substr(DESC,1,3500)) as DESC from <oracle table>

[Former Member · Accepted solution]

Dear Dirk,

Thanks for the useful information. I will go with your suggestion.

But, In BODS can we do something by SQL transformation. anything possible from this transformation to optimise my job.

Regards,

Prasanna

[former_member187605 · Accepted solution]

Sure, you can use

select <any other columns you need>, to_char(substr(DESC,1,3500)) as DESC from <oracle table>

in a SQL transform as a source for your data flow.

It's only that I try to avoid SQL transforms because of problems in

• metadata: you loose lineage
• performance: joins with other tables from the same source are not pushed to database
• maintenance: reimporting metadata vs. changing code

[Former Member · Accepted solution]

Dear Dirk,

I proceeded with the SQL transform and it worked for and job completed in 30 minutes.

Many thanks for your suggestion and tips.

[Former Member]

Dear Dirk,

Good Day!

I used the SQL transform and the job is running fine. but, we have some columns with the data type as timestamp in source DB. but, while we do import schema in SQL transform it shows the data type as varchar(13) for those column.

Example:-Arrival_date in table it is of data type timestamp. but, in the sql tranform if we do update schema it is showing varchar(13) for the same column.

Please help me to resolve this issue