BookmarkSubscribeRSS Feed
tampham92
Obsidian | Level 7

I tried so speed up the run time with proc ds2 instead of normal proc sql, but it looks like something is wrong with my codes. The normal proc sql gave me real time of 13:30, while proc ds2 gave me 16:04. To my understanding, proc ds2 should be faster cause it uses multiple thread, but that is not what I've seen. I copied the code below. If someone can help me out, that will be great

 

I also try PROC FEDSQL, but the run time is similar to a normal proc sql.

 

Thanks.

 

%macro loopthrough(from_date, to_date);

 

LIBNAME test1 db2 DSN='xxx' USER='xxx' PASSWORD='xxx';

 

 

    %local filedate;

 

    %do filedate=&from_date %to &to_date;

 

        %if %substr(&filedate,5,2) ge 01 AND %substr(&filedate,5,2) le 12 %then %do;

/*        %if %substr(&filedate,5,2) = 12 %then %do;*/

            data _null_;

                tmp = intnx('month',input("&filedate",yymmn6.),0,'e');

                call symput('adate',put(tmp, yymmdd10.));

            run;

Proc DS2;

Data QRM_DEP_REF_&filedate (overwrite=yes);

    method run();

     set {

 

    SELECT * FROM connection to test1

 

     (SELECT

     DW.RLTN_DIM.A                        AS ACCT_NBR,

     ORG_HIST_INT.B                      AS AffiliateNumber,

...

 FROM

...

WHERE

...

)};

end;
enddata;
run;

QUIT;


%end;
%end;

%mend;

%loopthrough(from_date=201303, to_date=201303);

 

 

 

5 REPLIES 5
SASKiwi
PROC Star

From what I understand DS2 threads are for parallelizing computational processing not vanilla SQL queries and are primarily for SAS processing not for external database querying. The one exception is the SAS In-Database Code Accelerator as referenced here: https://communities.sas.com/t5/Ask-the-Expert/Moving-Your-Programs-to-the-World-of-DS2/ta-p/364584. This is however for moving your SAS computational code into an external database, not for straight querying. In other words DS2 isn't going to speed up your queries.

 

To speed up DB2 queries I would investigate any features that will parallelize queries within that database. I know Oracle has that capability as I've used it myself. I think you would be better off checking DB2 forums. Alternatively you could consider running several SAS jobs at  the same time querying different slices of data. This can be easily done via SAS batch jobs or making use of the parallel SAS session capabilities of SAS/CONNECT.

tampham92
Obsidian | Level 7

Thanks for letting me know.

 

So do you know whether it would be faster to use FEDSQL? My test showed me the same run time, but I am not sure whether you have any different opinion. Also, do you know any recommendations on DB2 forum that I can utilize for my purpose.

 

 

SASKiwi
PROC Star

A Google search popped up this link: http://robertsdb2blog.blogspot.com/2011/09/managing-db2-for-zos-cpu-parallelism.html

 

You should first talk to your DB2 DBA to see if parallelism is enabled and his recommendations for using it.

 

I don't think it matters what tool you use in SAS, except to say SQL passthru will give you the most control.

s_lassen
Meteorite | Level 14

SAS and DB2 can actually do some parallel processing in normal datasteps and SQL as well. On DB2 (in all environments, it seems) you can set up DB2 to deliver data in parallel by tweaking the DBSLICEPARM dataset or libname option. That may speed up things in standard SAS SQL. 

 

It looks like you are often looping through several dates. I may also be possible to run this in parallel. On z/OS, you can submit batch jobs in parallel by writing JCL to the INTRDR device, see this link.

 

In Windows or Unix you can use the SYSTASK statement to start SAS jobs in parallel.

 

 

 

 

 

 

 

rajdeep
Pyrite | Level 9

HI tampham92,

 

The code what you are trying to execute, if it's working fine then you can try the below code and see if there is any difference in the execution time.

 

%macro loopthrough(from_date, to_date);

 

LIBNAME test1 db2 DSN='xxx' USER='xxx' PASSWORD='xxx';

 

 

    %local filedate;

 

    %do filedate=&from_date %to &to_date;

 

        %if %substr(&filedate,5,2) ge 01 AND %substr(&filedate,5,2) le 12 %then %do;

/*        %if %substr(&filedate,5,2) = 12 %then %do;*/

            data _null_;

                tmp = intnx('month',input("&filedate",yymmn6.),0,'e');

                call symput('adate',put(tmp, yymmdd10.));

            run;

Proc DS2;
thread newton2/overwrite=yes;


    method run();

     set {

 

    SELECT * FROM connection to test1

 

     (SELECT

     DW.RLTN_DIM.A                        AS ACCT_NBR,

     ORG_HIST_INT.B                      AS AffiliateNumber,

...

 FROM

...

WHERE

...

)};

end;
endthread;
data Data QRM_DEP_REF_&filedate/overwrite=yes;
		dcl thread newton2 frac1; /* Declare an Instance of the newton thread */

		method run();
			set from frac1 threads=4; /* <--- You Change the number of threads to control the threaded reads */
		end;
	enddata;
run;
quit;


%end;
%end;

%mend;

%loopthrough(from_date=201303, to_date=201303);

I am actually still learning the PROC DS2, because it's bit different from the PROC SQL and we need to think everything from the ANSI C Standard prospective.

 

Please try and check. I believe somehow the real time will be reduced.

 

Thanks

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1357 views
  • 0 likes
  • 4 in conversation