DSXchange

Ok the problem has been found and it had nothing to do with the query. :oops: The query was running as it should have, however there were a few other dependant jobs which were not. It turns out that another job which populated one of the reference tables ran late that day so not all the operator ids...

Could it be the database is accepting one row and rejecting the other for some type of key violation? Very unlikely, if not impossible. The nested select statement returns distinct customer ids, and this rounds out the key of the destination table. Also investigation has shown that one of the retur...

You missed a right parenthesis, but that's just your keyboard! What's different between development and production? Can you create a job that just does the internal SELECT, perhaps into a text file? What happens with Array Size set to 1? (1000 is ridiculously high for a query that is only going to ...

Hi All, I am having an issue with a user defined sql query using an ORAOCI8 stage. The query is as follows: INSERT INTO TABLE_1 SELECT :1 AS TRANSACTION_ID, :2 AS TRANSACTION_RELATIONSHIP_CODE, :3 AS BEFORE_OR_AFTER_IMAGE, CUSTOMER_ID, CUSTOMER_TYPE, CUSTOMER_STATUS_CODE, CUSTOMER_UPDATED FROM (SELE...

Will increasing the arrary size mean that some records could be missed for an update because they both come through in the same chunk of records? Or is each record written to the hash file and available immediately for reference regardless of the array size used in the database? The latter. The has...

Just an update: It turns out it wasn't the sort stage but rather the database load that was taking the majority of the time. Implementing the hash file solution has cut loading time in half. Also we set the transaction size to 0. We have also tested with the Unix sort and it does give about a threef...

Ahh of course. Completely slipped my mind that we could sort it in Unix first.

Thanks again for your help on this Craig. I hope I can repay the favour one day (if not to you then to others on this forum).

Ahh of course. Completely slipped my mind that we could sort it in Unix first.

Thanks again for your help on this Craig. I hope I can repay the favour one day (if not to you then to others on this forum).

My initial thoughts are that the slow speed is caused by the sort stages but these are required to ensure the earliest start dates are loaded first. Is it possible that it is fact caused at the DB level? That should be readily apparent... how much of the 3 hours are spent in the Sort stage? Once re...

Hi Everyone, Ok this is pretty long winded but please stick with me. I have been requested to see if its possible to optimise a current job in order to make it run faster. Currently in Production the job is taking approx 3 hours to complete. Job Overview: Records inserted into a database currently h...

Alternatively write your master job to contain a list of code and hold your validation rules in a separate table which will dictate the actions and its seriousness / importance with relation to that particular recordset. Thanks for that Sainath. The first option you mention won't really work as I d...

Sorry one thing i forgot to mention was that I have tried to implement a hash file lookup but it didn't work as the primary field used is the account number, or to be precise the account number format. In the routine at present I use the Match function on the account number as part of the determinat...

Hi Everyone Is it possible to have a dynamic variable name? I ask as I have a particular job that must use several fields in order to determine a customer SAP grouping code. My problem is as such: The source system is worldwide, I receive a flat file which will be same for every country. Whilst the ...

This might not add to much value to the topic but if you can't isolate the column correctly with the comma delimiter, can you get the delimiter changed to somthing that is less likely to appear in the data like the pipe | delimiter. Admittedly this will only help if the delimiter chosen can never ap...

Not sure of the full details but in SAP you would create a function module and in the "attributes" section you choose "Remote-enabled module". In that function you'd do whatever logic you wanted (eg. in your case you could call whatever transaction/program it is that you need). T...