03-13-2015 11:40 AM
I have a query on SAS DI Studio.
How to do restart-ability and check points in SAS DI studio at oracle level.
I have a job which loads (10000) records from SAS dataset to Oracle table. After executing the job 1000 records are loaded, after that job fails with other reasons .So after fixing the issue i dont want to run the job from starting .I just want to load from 1001 record.(i.e.where it is failed from their i want to load the records).
The main purpose is to improve the job perfromance.
Can any one suggest what ways we have in SAS.
03-16-2015 03:52 AM
How can it fail DURING the Oracle load "for other reasons"?
I'd probably opt for only sending an Oracle commit after everything has been loaded so that Oracle rolls-back in case the load fails somewhere underway. This way you can re-run your job but you would start loading from record 1.
I'd worry less about performance. A failing load should be rare so your main focus should be on not risking to load the same data twice.
03-16-2015 05:22 AM
Export your data to a file that can be loaded into Oracle, then have your database people do the import in the way they consider best. They know better.
03-16-2015 06:01 AM
That's not how SAS DI implementations work. The load into Oracle is most of the time done using SAS DI Studio so the SAS developer needs to acquire some Oracle knowledge. You then want a senior Oracle DBA/Developer accessible for advise and to support fine tuning.
If done right SAS doesn't do a too bad job in loading data into Oracle once you've tweaked library definition to Oracle (so a simple Proc Append code might then actually perform a bulk load).
03-16-2015 06:09 AM
It seems to me the "other reasons" are caused by conditions in the oracle DB. I personally prefer to run SAS with native tables:
- no DB knowledge needed
- no hassles with the DB "getting into the way"
- no additional resources for the DB needed
- no additional license costs
03-16-2015 06:24 AM
Yep, using SAS tables is normally easier but then: I'm right now implementing a table where I need to append and look-up data - and this table will grow to up to 900M rows. I'm really happy to have such a table in Oracle where I can use table partitioning and multi-threading - and as I know the partition names I can do look-ups in a single partition. It would be much more difficult to implement something that performing using SAS tables.
So yes, I agree, using SAS tables is easier - but using a database gives access to the best of both worlds so the extra effort is sometimes worth it.
03-16-2015 08:25 AM
The other reasoons like ,if the job is failed with intigrity constraints or unique constraints.
So afer fixing the issue or ignoring the junk data and i want to load remain records.
If suppose the job is failed at 50 th record and it is a junk data and it can be ignore .So i want to load from 51 record and job shouldn't fail.
Can we doi it in SAS DI studio or in Oracle level.I don't have much knowledge on Oracle ..I am using table loader transforamtion in job for loading records from SAS to Oralce and the load technique is Append or Insert/update.
Thanks all ..
03-16-2015 06:09 PM
You want to set the option for commit (I believe it's a library option called dbcommit) to a value so that Oracle only commits at the end of the insert/update operation. So if anything falls over then no data at all will get loaded. You fix your data and then re-run the whole load job.
If something is wrong with your data then you need to fix this before loading (eg. by implementing data validation which moves invalid records to error & exception tables).