Date criteria in SQL query (+FETCH) 1

[sunny567]

Hi, I am using SAS to access DB2 tables. I have a very complex join of large number of tables which is taking ages to complete running (batch).

I would like to add a date criteria to the query. Will this work - in SQL query -> where date_field > date()-60. Don't I need to convert the value returned by SAS function date() and minus 60 into a DB2 date format? I really am not sure how to do that. Din't find any relevant information on net.

Also to test the change I would like to apply FETCH FIRST 100 ROWS ONLY clause. But SAS doesnot recognise it. It is throwing a syntax error.

Can any mate out there please help. Cheers!!!

 

[ChrisW75]

Please post your code, I might be able to help speed your process up. I've previously had success trimming an 8 hour query to under 8 minutes (seriously, I ran them one after the other to test the improvement, it was astounding).

One thing that is a little counter intuitive is that it is actually faster to split your query up into bunches of 2 table joins than to have alot of table all joined in one query.
Also, the DBKEY option speeds up the joining of a SAS dataset to a an RDBMS table by the sort of improvement I mentioned before. Doing these 2 steps was all it took to change a query joining 4 tables which took 8 hours to run into a 3 queries joining the same 4 tables which took just under 8 minutes.

To pass the date part of your query to the RDBMS (DB2 in this instance), I'd recommend creating a date string in DATE9. format to pass in (say as a macro variable) as this format is well understood by most database systems.

One thing to note about working with SAS on RDBMSs is that when you run a query, generally SAS does not pass the WHERE parts of the query to the RDBMS, it will extract ALL the data out to a temporary SAS dataset, and then apply the WHERE, so it can be extrememly inefficient.

 

[sunny567]

Chris, thanks for the reply. I preferred to change the design and not to use SAS for this. Some tables have 250million records and its worth doing DB2 unloads and processing the data using DFSORT (mainframe world!!!)

For the date logic you mentioned, can you please share sample code or any examples available on net?
"To pass the date part of your query to the RDBMS (DB2 in this instance), I'd recommend creating a date string in DATE9. format to pass in (say as a macro variable) as this format is well understood by most database systems."

Thx.

 

[ChrisW75]

Well, I never worked with DB2, but for oracle, we'd frequently do something like this

data _null_;
  length formatted $9;
  exampledt = '10JUN2004'd;
  formatted = put(exampledt,DATE9.);
  call symput('EXAMPLDT',formatted);
run;

PROC SQL;
  create table temp as
  select *
  from connection to oracle
     (select blah
            ,blah
            ,blah
     from the_table
     Where mydate = &exampldt
      ;
     )
  ;
quit;

Alternatively you would do something like this

%let EXAMPLDT = 19/06/2006;
proc sql;
  create table ...
  ...
  from connection to oracle
  (select *
   from thetable
   where thedate = to_Date("EXAMPLDT",'ddmmyyyy')
   ;
   )
  ;
quit;

There was generally some fun and games with quotes requireing some use of the %BQUOTE type macro functions.
Enjoy.

 

[sunny567]

Chris, the second sample is similar to what I was trying to achieve. Thanks for that.

Do you normally use pass-through SQL? I have read in SUGI papers that pass-through SQL is more efficient than access with LIBNAME.

 

[ChrisW75]

Usually we recommend using pass-through, however you can achieve extremely fast results using the LIBNAME with proc SQL and adding in the DBKEY= option.
This is particularly effective if for instance you pull down a large chunk of data which you can only filter are some processing in SAS. This wa, you bring down one table you need to do your initial work, then can join the results back to the RDBMS table alot faster. I've used it for appending address details to extracted client records for example.

proc sql;
  create table everything as
  select a.*
        ,b.address1
        ,...
  from WORK.mydata   a
      ,ORADB.address_table(dbkey=address_id dbnullkeys=no) b
  where a.address_id = b.address_id
  ;
quit;

This can be incredibly quick compared to normal libname processing, and is something that cannot be done using passthrough.
What this actually does is pass through multiple queries, one for each record in WORK.mydata, sending out the address_id that needs to be retrieved. It sounds like it should be slower, but due to the way RDBMSs work, it surprisingly fast. SAS themselves recommend this for joining "small" SAS datasets to "large" RDBMS tables. I've used this for up to 10,000 records on the SAS side joining to several million on the RDBMS side and still found it pretty quick. I've even, as suggested above, used it to break a passthrough SQL step which joined several RDBMS tables together into multiple steps with huge improvements in performance.