Restartability in SAS DI Studio

Reply
Occasional Contributor
Posts: 10

Restartability in SAS DI Studio

HI All,

I have a query on SAS DI Studio.

How to do restart-ability and  check points  in SAS DI studio at oracle level.

Example:

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.

Occasional Contributor
Posts: 10

Re: Restartability in SAS DI Studio

Can anyone suggest on above question.

Any Pass through or oracle quries will help ...

Respected Advisor
Posts: 4,130

Re: Restartability in SAS DI Studio

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.

Super User
Posts: 7,386

Re: Restartability in SAS DI Studio

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Respected Advisor
Posts: 4,130

Re: Restartability in SAS DI Studio

@KurtBremser

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).

Super User
Posts: 7,386

Re: Restartability in SAS DI Studio

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

but YMMV.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Respected Advisor
Posts: 4,130

Re: Restartability in SAS DI Studio

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.

Occasional Contributor
Posts: 10

Re: Restartability in SAS DI Studio

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 ..

Respected Advisor
Posts: 4,130

Re: Restartability in SAS DI Studio

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).

Ask a Question
Discussion stats
  • 8 replies
  • 372 views
  • 0 likes
  • 3 in conversation