DSXchange

Hi all, I am analyzaing a server job for performance issues. This is a server job which uses 5 look ups (Uses DRS stage). Each of the stage executes SQL queries that has multiple joins on tables (some of the tables have close to 300,000 records). In a lower environment the job completes in 4 minutes...

hello, We are revisiting the usage of hashed files in our jobs. Some of the jobs use hashed files for lookup and these hashed files are created using a job which runs at a scheduled time every day. We have observed that few of the jobs are not using hashed files and instead use direct table lookups....

Thanks.

Is there a way to define Read cache at job level than at Project level?
We are only doing Inserts using the DRS stage (Insert without clearing).

Thanks. The lookups that we perform vary from few hundred records to 200 or 3000 thousand records. My understanding is that the Read cache and Write cache are set at Project level. In this case the other jobs that do lookup for fewer records may not require the lookups to happen in memory. I just wa...

What kind of "writes" are happening in the target? Any large fields involved, like perhaps a CLOB? Are the lookups against hashed files? Are they cached? Properly sized? There are no large fields involved, like CLOB. Currently one lookup is done in such a way that a hashed file is created...

Hello all, I am analyzing a set of Data Stage server jobs for performance enhancement. The job reads a source file (Aproximately 338420 records), does 2-3 lookups and then writes to 2 Oracle tables (DRS stage is used). It was noted that one of the jobs calls a routine for each record (Aproximately 3...

Hello, I am analyzing a set of Server jobs for performance analysis. One of the jobs has a design as Sequential file -> Transformer(Hashed file lookup) -> Transformer (hashed file lookup) -> Load Oracle table (DRS stage). 916990 records are passed into 2nd transformer and the lookup is against hashe...

Hi all, I am currently analyzing a set of existing Server jobs. I found that the in the hashed file stage, hashed files are created using account name and not the Directory path. I am interested in knowing which is the best option of these 2 and the reasons for that and the advantages of one over ot...

Hello all,

I am analyzing a paralel job for performance improvemnets. I have found that in the transformer stage there is a filter which does input column value <> Space(1). Is there any performance benefit in using Space(1) rather than directly checking cinput column value <> ' '

Thanks Craig. That helped. Now I am able to generate the same sql statement within the routine that when executed against a database inserts the record correctly. Unfortunately, for some reason, the insert via DSExecute is still not happening. Job completed without any error, DSExecute returns 0 and...

I tried Cmd3 ='INSERT INTO curve_term_value (upd_ts,snap_date_time,term_id,value) values(to_date(SQUOTE(parmUpdTs),'MM/DD/YYYY HH24:MI:SS',to_date(SQUOTE(parmSnapDateTime),'MM/DD/YYYY HH24:MI:SS'),parmTermId,parmValue)'; and still get warnings from routine as WARNING: Variable 'MM' never assigned a ...

The whole code snippet as below. When I take the to_date from the insert, it does not do either . I added it because the same statement when executed from PL/SQL developer threw error with invalid month. I am wondering why the to_date is required since I am already converting to date format with Oco...

Thanks Craig. I have tried the following. datepartupdts=Field(parmUpdateTs,' ',1) timepartupdts=Field(parmUpdateTs,' ',2) parmSnapDateTime = Oconv(IConv(datepartupdts,'D-YMD[4,2,2]'),'D/MDY[2,2,4]') : ' ' : OConv(IConv(timepartupdts,"MTS"),"MTS") When I print the values, they get...

Hi,

I have data coming in as 2010-02-19 16:00:00 and I want to convert it to format MM/DD/YYYY HH:MI:SS AM/PM.

Can someone suggest the Oconv and Iconv combination to achieve this.

Thanks Craig and sorry for not addressing you properly in the previous reply. Your suggestion for the Cmd variable worked and there is no error from DSExecute command and it returns 0. Interestingly delete and update statements worked perfectly! But for some reason, the insert is not happening. In t...