PROC DS2 with connect to db2

[tampham92]

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

[SASKiwi]

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]

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]

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]

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]

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