Help using Base SAS procedures

How can i perform commit using Proc sql?

Reply
Occasional Contributor
Posts: 16

How can i perform commit using Proc sql?

A job which is deleting records from db2 table abended and The DBA group said that the job failed because there was no commit during the duration of 4 DB2 checkpoints.

I'm using Proc SQL to perform deletions. Well, I know proc SQL doesn’t support commit and i don’t want to use proc DB2UTIL as it might not be available in new versions of SAS.

Can anyone suggest any solution to this problem.
Thanks in advance
Manish
Super Contributor
Super Contributor
Posts: 3,174

Re: How can i perform commit using Proc sql?

Posted in reply to er_awasthi
SAS/ACCESS for DB2, possibly? I found this relevant SAS conference paper on the topic.

Scott Barry
SBBWorks, Inc.

Improving Performance: Accessing DB2 Data with SAS 9
Scott Fadden, IBM, Portland, OR
http://www2.sas.com/proceedings/sugi29/107-29.pdf
Occasional Contributor
Posts: 16

Re: How can i perform commit using Proc sql?

Posted in reply to er_awasthi
Thanx SSB,
i found DB COMMIT option in the attached document which commits at regular interval in SAS
But still there is one problem, I work on Mainframe(Z/OS) and Mainframe(Z/OS) does not support DB commit option .so I cant use DB commit option for my problem.

http://support.sas.com/documentation/cdl/en/acreldb/61890/HTML/default/a001342265.htm#a001442910

My senior told me,this issue can be resolved by a Macro .So, this weekend i will be learning Macros.
Do u know any other way to solve this problem

Thanx
Manish
Super Contributor
Super Contributor
Posts: 3,174

Re: How can i perform commit using Proc sql?

Posted in reply to er_awasthi
Not sure if you requirement is suitable to breaking up your one PROC SQL into iterative subsets of your deletion requests, where you use a macro and you generate multiple PROC SQL executions.

Yes, the SAS Macro language is one option, although you may find sufficient capability by running a SAS DATA step to generate the PROC SQL executions out to a temporary sequential file (allocated with FILENAME) and then use %INCLUDE ; to invoke/execute your "generated" SAS code.


Scott Barry
SBBWorks, Inc.

DATA Step Interfaces
Interacting With the Macro Facility During DATA Step Execution
http://support.sas.com/documentation/cdl/en/mcrolref/61885/HTML/default/a001072359.htm

Introduction to the Macro Facility
http://support.sas.com/documentation/cdl/en/mcrolref/61885/HTML/default/a002293969.htm

Using Metadata for Data Driven Programming
Brian Varney, Trilogy Consulting, Kalamazoo, Michigan
http://www2.sas.com/proceedings/sugi25/25/cc/25p077.pdf
Occasional Contributor
Posts: 16

Re: How can i perform commit using Proc sql?

Posted in reply to er_awasthi
Hi Thanx for the information SSB . Macro is new concept to me.

Can u help me and plz expalin in detail what i need to do to genrate a Macro to commit at regualr intervals while deleting records from db2 tables.

For the time being , The job is executed successfully by writing the delete data to an output dataset and breaking the dataset into 5 smaller datasets I then ran the delete against each of the 5 new datasets

But More prominent solution is required as it is production job which runs frequently .

ThanK u very much for u help.
Manish
Super Contributor
Super Contributor
Posts: 3,174

Re: How can i perform commit using Proc sql?

Posted in reply to er_awasthi
Please consider reading the suggested reference documentation or at least get started enough where you can learn and understand the SAS macro concept for repetitive code execution. Since you will need to support the code, it would be more suitable that you author the SAS application program to perform the process going forward. Others on this forum may be more about writing the code for you, however I prefer to guide others with learning SAS to accomplish tasks.

Good luck with your SAS programming experience using the macro facility.

Scott Barry
SBBWorks, Inc.
SAS Super FREQ
Posts: 8,864

Re: How can i perform commit using Proc sql?

Posted in reply to er_awasthi
Hi:
Scott has given you some good references. I also find that this is a good introduction to the Macro facility for beginners:
http://www2.sas.com/proceedings/sugi28/056-28.pdf

The essence of the SAS macro facility is text substitution -- either at the statement level or the program level or at the single keyword or variable name level. Think of the SAS Macro Facility as a way to -generate- SAS code. The Macro facility, in and of itself, does not -execute- any code. It merely resolves macro variable references and macro program references into code which -can- be executed by the SAS compiler.

In addition to Scott's links and my introductory link, there are many, many previous postings in these forums and in SAS Global Forum papers and other user group papers -- everything from basic macro tutorials to examples of advanced macro applications.

There is no good way for anyone in the forum, to design, in detail or explain in detail the solution to your problem. You have to understand the macro facility a bit better in order to understand how it works and how you might apply usage of the Macro facility to solve your problem.

The paper I referenced at the beginning will show you the basics of how to start with a simple macro variable and will lead you through 9 steps until you understand how to write a simple macro program. The key to using the Macro facility is to start with a -WORKING- SAS program -- one that you know works and know that it needs to be run repetitively or one that needs to be generalized to run against differeing inputs (to name just a few of the reasons that folks use SAS macro programs). That way, you have a program that produces that desired results -- and against which, you can verify that your macro program is working correctly.

Good luck!
cynthia
Ask a Question
Discussion stats
  • 6 replies
  • 1619 views
  • 0 likes
  • 3 in conversation