I am trying to use DMC as a nice cetralized space to monitor for RUNSTATS and REBINDS on our customer databases (130+) that are supposed to be scheduled weekly.  I've written a nice query that pulls from SYSCAT.TABLEs and SYSCAT.PACKAGES for the info I'm looking for.  I would like the query to not return data if everything has been updated within the last 30 days.  If outside of the 30 day window, I would like to see the records that are returned.  Unfortunately, I currently see a SUCCESS completion regardless, I would like to see a WARNING if records are retuned so that I know what customers need reviewing and which ones don't.

Any suggestions.  I have not gone to a CLP or Shell script as we do not have OpenSSH on the customer servers yet.

Thank you

------------------------------
Freddie Callander
------------------------------

I am trying to use DMC as a nice cetralized space to monitor for RUNSTATS and REBINDS on our customer databases (130+) that are supposed to be scheduled weekly.  I've written a nice query that pulls from SYSCAT.TABLEs and SYSCAT.PACKAGES for the info I'm looking for.  I would like the query to not return data if everything has been updated within the last 30 days.  If outside of the 30 day window, I would like to see the records that are returned.  Unfortunately, I currently see a SUCCESS completion regardless, I would like to see a WARNING if records are retuned so that I know what customers need reviewing and which ones don't.

Any suggestions.  I have not gone to a CLP or Shell script as we do not have OpenSSH on the customer servers yet.

Thank you

Hi Freddie,

I am not familiar with DMC however, it appears that when you submit SQL statements directly DMC is using a JDBC connection to the database, so this may be worth a try,

I believe you can submit multiple statements in a job, so you create two statements.

The original statement to list the packages and tables

 SELECT PKGSCHEMA, PKGNAME 
  FROM SYSCAT.PACKAGE 
 WHERE LAST_BIND_TIME < CURRENT TIMESTAMP - 30 DAYS;

Then a second statement

 SELECT 'X' 
   FROM SYSIBM.SYSDUMMY1                   
  WHERE (SELECT 'X'  AS MARKER1
           FROM SYSCAT.PACKAGE 
          WHERE LAST_BIND_TIME < CURRENT TIMESTAMP - 30 DAYS
         UNION ALL                                     
         SELECT 'X' AS MARKER1 FROM SYSIBM.SYSDUMMY1              
        ) = 'X'                                        
   ;                                                   

The second statement should error out if one or more rows are returned from syscat.package and the job will end with an error(not a warning).  If no rows are returned it will be successful.

I am trying to use DMC as a nice cetralized space to monitor for RUNSTATS and REBINDS on our customer databases (130+) that are supposed to be scheduled weekly.  I've written a nice query that pulls from SYSCAT.TABLEs and SYSCAT.PACKAGES for the info I'm looking for.  I would like the query to not return data if everything has been updated within the last 30 days.  If outside of the 30 day window, I would like to see the records that are returned.  Unfortunately, I currently see a SUCCESS completion regardless, I would like to see a WARNING if records are retuned so that I know what customers need reviewing and which ones don't.

Any suggestions.  I have not gone to a CLP or Shell script as we do not have OpenSSH on the customer servers yet.

Thank you

Tommy, 

Thank you so much for the query.  This works exactly the way I need it to.  This will help a lot in automating our monitoring.  

Hi Freddie,

I am not familiar with DMC however, it appears that when you submit SQL statements directly DMC is using a JDBC connection to the database, so this may be worth a try,

I believe you can submit multiple statements in a job, so you create two statements.

The original statement to list the packages and tables

 SELECT PKGSCHEMA, PKGNAME 
  FROM SYSCAT.PACKAGE 
 WHERE LAST_BIND_TIME < CURRENT TIMESTAMP - 30 DAYS;

Then a second statement

 SELECT 'X' 
   FROM SYSIBM.SYSDUMMY1                   
  WHERE (SELECT 'X'  AS MARKER1
           FROM SYSCAT.PACKAGE 
          WHERE LAST_BIND_TIME < CURRENT TIMESTAMP - 30 DAYS
         UNION ALL                                     
         SELECT 'X' AS MARKER1 FROM SYSIBM.SYSDUMMY1              
        ) = 'X'                                        
   ;                                                   

The second statement should error out if one or more rows are returned from syscat.package and the job will end with an error(not a warning).  If no rows are returned it will be successful.

I am trying to use DMC as a nice cetralized space to monitor for RUNSTATS and REBINDS on our customer databases (130+) that are supposed to be scheduled weekly.  I've written a nice query that pulls from SYSCAT.TABLEs and SYSCAT.PACKAGES for the info I'm looking for.  I would like the query to not return data if everything has been updated within the last 30 days.  If outside of the 30 day window, I would like to see the records that are returned.  Unfortunately, I currently see a SUCCESS completion regardless, I would like to see a WARNING if records are retuned so that I know what customers need reviewing and which ones don't.

Any suggestions.  I have not gone to a CLP or Shell script as we do not have OpenSSH on the customer servers yet.

Thank you