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
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
...
);
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;
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
...
);
THANK YOU SO MUCH, Tom and Chris!!! You guys are a lifesaver!
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.