Multiple "select" statements using 1 db connection?

[Mary001]

Hi All,

I'm a newbie and I need to run a query against 400+ tables in a DB2 database to capture the max last update. Is it possible to have multiple "select" statements using 1 DB2 connection?

Also, how can get the results in a form of a list, for example:

table1	1/1/2021
table2	1/2/2021
table3	1/3/2021
table4	1/4/2021
table5	1/5/2021

Here's the program and when I run it, it only returns the result from the 1st select statement. No errors in the log.

%udb(DB2P)
SELECT * FROM CONNECTION TO DB2(
SELECT MAX(DT_LST_UPDT) AS CD_EVNT_ONE_DT_LST_UPDT FROM DB2PROD.CD_EVNT_ONE;
SELECT MAX(DT_LST_UPDT) AS CD_EVNT_TWO_DT_LST_UPDT FROM DB2PROD.CD_EVNT_TWO;
SELECT MAX(DT_LST_UPDT) AS CD_EVNT_THREE_DT_LST_UPDT FROM DB2PROD.CD_EVNT_THREE;
SELECT MAX(DT_LST_UPDT) AS CD_EVNT_FOUR_DT_LST_UPDT FROM DB2PROD.CD_EVNT_FOUR;
SELECT MAX(DT_LST_UPDT) AS CD_EVNT_FIVE_DT_LST_UPDT FROM DB2PROD.CD_EVNT_FIVE;
); quit;

Thank you so much in advance!

Mary

[ChrisNZ]

i would create a libname, and not use passthrough here. 

Your query is simple enough that implicit pass-through can be used with no adverse effect.

libname DB2PROD   ....   ;
proc sql;
SELECT MAX(DT_LST_UPDT) AS CD_EVNT_ONE_DT_LST_UPDT FROM DB2PROD.CD_EVNT_ONE;
SELECT MAX(DT_LST_UPDT) AS CD_EVNT_TWO_DT_LST_UPDT FROM DB2PROD.CD_EVNT_TWO;
SELECT MAX(DT_LST_UPDT) AS CD_EVNT_THREE_DT_LST_UPDT FROM DB2PROD.CD_EVNT_THREE;
SELECT MAX(DT_LST_UPDT) AS CD_EVNT_FOUR_DT_LST_UPDT FROM DB2PROD.CD_EVNT_FOUR;
SELECT MAX(DT_LST_UPDT) AS CD_EVNT_FIVE_DT_LST_UPDT FROM DB2PROD.CD_EVNT_FIVE;
quit;

[Tom · Accepted solution]

You just want to print the results into the output window? You don't want to store them somewhere?

Either run each query separately.

SELECT * FROM CONNECTION TO DB2(
  SELECT MAX(DT_LST_UPDT) AS CD_EVNT_ONE_DT_LST_UPDT FROM DB2PROD.CD_EVNT_ONE
);
SELECT * FROM CONNECTION TO DB2(
  SELECT MAX(DT_LST_UPDT) AS CD_EVNT_TWO_DT_LST_UPDT FROM DB2PROD.CD_EVNT_TWO
);
...

Or tell the remote DB you want it run ONE query that returns all of the results: (Note every implementation of SQL has its own quirks so syntax might need some tweaking). In which case you probably need to add a second variable to tell where the 

SELECT * FROM CONNECTION TO DB2(
  SELECT 'DB2PROD.CD_EVNT_ONE' as source,MAX(DT_LST_UPDT) AS DT_LST_UPDT FROM DB2PROD.CD_EVNT_ONE
  union
  SELECT 'DB2PROD.CD_EVNT_TWO' as source,MAX(DT_LST_UPDT) AS DT_LST_UPDT FROM DB2PROD.CD_EVNT_TWO
  ...
);

But you probably want to make a TABLE and not just print the results. 

create table want as 
SELECT * FROM CONNECTION TO DB2(
  SELECT 'DB2PROD.CD_EVNT_ONE' as source,MAX(DT_LST_UPDT) AS DT_LST_UPDT FROM DB2PROD.CD_EVNT_ONE
  union
  SELECT 'DB2PROD.CD_EVNT_TWO' as source,MAX(DT_LST_UPDT) AS DT_LST_UPDT FROM DB2PROD.CD_EVNT_TWO
  ...
);

[Mary001]

THANK YOU SO MUCH, Tom and Chris!!! You guys are a lifesaver!