Tuning SAS Program

Vidhya Kalyanasundaram · New User Joined: 19 Jul 2007 Posts: 30 Location: chennai

Hi Friends,

I have a query regarding a SAS code. I have attached below the SAS macro that is used to extract DB2 accounting information from SAS dataset - P.SMFDB2.DATA. This step consumes around 20mins of CPU and 30 mins of Elapsed time. Is there any way to tune this program?

Vidhya Kalyanasundaram · New User Joined: 19 Jul 2007 Posts: 30 Location: chennai

Or is there any way to extract this information with any other utility which consumes less time rather than using this SAS macro?

Vidhya Kalyanasundaram · New User Joined: 19 Jul 2007 Posts: 30 Location: chennai

Sorry, missed to include the SOURCLIB statements.

%%%INCLUDE SOURCLIB(EXDB2ACC);
- OUTPUT _WDB2ACC;

%INCLUDE SOURCLIB(TYPEDB2);
-
%INCLUDE SOURCLIB(VMACSMF,VMACDB2,IMACKEEP);
DATA
_VARDB2
_SMF
_CDEDB2
%INCLUDE SOURCLIB(DIFFDB2);

Could anyone please help regarding this?

Robert Sample · Posted: Wed Apr 08, 2009 4:55 pm

Use WHERE instead of IF statements to select your records.

Use KEEP to restrict the number of variables you're retrieving.

Contact support@mxg.com to get help -- I've found Barry Merrill to be very good at providing MXG help when needed.

Recognize, however, that DB2 can generate a large number of records in SMF and sometimes you have to bite the bullet and accept that the job is going to run for awhile.

Vidhya Kalyanasundaram · New User Joined: 19 Jul 2007 Posts: 30 Location: chennai

I replaced the IF statement with WHERE clause as given below.

WHERE QMDACNAM='BATCH' OR QMDACTYP='BATCH' ;

But i am getting error. Could you please tell me whether my syntax is wrong?

As you said, i have sent a mail to support@mxg.com. Will post the updates in the forum.

Thanks,
Vidhya

sbxjld · New User Joined: 15 Apr 2009 Posts: 3 Location: tervuren

Could you please tell me what errro you get in the log ?
Jean-Louis

ABaluchamy · New User Joined: 29 Dec 2006 Posts: 34 Location: INDIA

I have attached the log file with this
SASLOG

Vidhya Kalyanasundaram · New User Joined: 19 Jul 2007 Posts: 30 Location: chennai

Unfortunately, the _NDB2 token only prevents the OUTPUT of the
other DB2 datasets, but they are all being read and decoded.

1. You can use the IMACFILE exit to read only the SMF 101 Subtype 0 (IFCID=0003) record since you only want to create DB2ACCT.

2. You can use the IHDRDB2H "DB2 Header" exit to further select only the account records you want for batch.

You could put your code in those members of your "USERID.SOURCLIB" tailoring library, but then they would always apply if that DSNAME is used in the
//SOURCLIB concatenation, so, instead, use their "instream" equivalent macro variables:

//SYSIN DD *
%LET MACFILE=
%QUOTE (
IF ID=101 AND SUBTYPE=0;
) ;

%LET IHDRDB2H=
%QUOTE (
IF OFFQMDA GT 0 AND NRQMDA GT 0;
OFFTEST=OFFQMDA-3+OFFSMF;
INPUT @OFFTEST+41 TESTCNAM $EBCDIC8.
TESTCTYP $EBCDIC8.
@;
IF TESTCNAM='BATCH' OR TESTCTYP='BATCH';
);

the rest of your code.

But still the code is taking 25mins to process the records.

Will update further if i get reply from Barry.

Thanks !!!

Robert Sample · Posted: Tue Apr 21, 2009 9:16 pm

25 minutes is pretty good -- our MXG daily process takes 4.5 to 5.5 hours per day to go through all the SMF records. But then we're not looking only at one type but handling them all.

Vidhya Kalyanasundaram · New User Joined: 19 Jul 2007 Posts: 30 Location: chennai

Hi All,

With the help from SAS library and tips from Barry, i tuned the SAS macro. The CPU time got reduced from 20 mins to 5 mins !!!
Note : I have attached below the tuned code

Thanks a lot for all your help !!!!
:-)

Robert Sample · Posted: Sat May 23, 2009 5:09 pm

Glad to hear you got the time down.