SQL on DB2, How do I run version correct TABLE() functions?

Question

I'm writing a monitor for DB2. We have several version running, 9.7, 10.1, 10.5, and I want a monitor that calls monitoring functions. These take the form of "TABLE(procedure(scope calls)):

10.5 - TABLE(mon_get_database(-1))
10.1 - TABLE(snap_get_db('', -1))
9.7 - TABLE(snap_get_db_v97('', -1))

I want the monitor to run against any version and query the version and run the correct SQL against it I can pull the version:

    SELECT FLOAT(PROD_RELEASE) FROM SYSIBMADM.ENV_PROD_INFO 
where license_installed = 'Y' fetch first row only

When I run this, it returns correctly:

create variable dbVersion float;
set dbVersion = (SELECT FLOAT(PROD_RELEASE) FROM SYSIBMADM.ENV_PROD_INFO
where license_installed = 'Y' fetch first row only);
select dbVersion from sysibm.sysdummy1;

It returns 9.7 for example. When I run the 9.7 version of the monitor against the DB, it works:

SELECT (DAYS(current timestamp) - DAYS(last_backup)) 
        FROM sysibm.sysdummy1, TABLE(snap_get_db_v97('', -1));

returns 2, which is correct When I try to put it into a case statement, it hits the first line, misses the conditions, attempts to execute the procedure and fails:

    set dbVersion = (SELECT FLOAT(PROD_RELEASE) FROM SYSIBMADM.ENV_PROD_INFO where license_installed = 'Y' fetch first row only);
    select dbVersion from sysibm.sysdummy1; --this returns the correct version of DB2
    create variable dbCommand varchar(256);
    create variable dbBU float;
    set dbBU= 
    case
        when (SELECT PROD_RELEASE FROM SYSIBMADM.ENV_PROD_INFO 
where license_installed like 'Y') = 10.5 
then (SELECT (DAYS(current timestamp) - DAYS(last_backup))
            FROM sysibm.sysdummy1, TABLE(mon_get_database(-1)))
        when (SELECT FLOAT(PROD_RELEASE) FROM SYSIBMADM.ENV_PROD_INFO 
where license_installed = 'Y' fetch first row only) >= 10.0 
then (SELECT (DAYS(current timestamp) - DAYS(last_backup))
            FROM sysibm.sysdummy1, TABLE(snap_get_db('', -1)))
        when (SELECT FLOAT(PROD_RELEASE) FROM SYSIBMADM.ENV_PROD_INFO 
where license_installed = 'Y' fetch first row only) = 9.7 
then (SELECT (DAYS(current timestamp) - DAYS(last_backup))
            FROM sysibm.sysdummy1, TABLE(snap_get_db_v97('', -1)))
        when (SELECT FLOAT(PROD_RELEASE) FROM SYSIBMADM.ENV_PROD_INFO 
where license_installed = 'Y' fetch first row only) = 9.5 
then (SELECT (DAYS(current timestamp) - DAYS(last_backup))
            FROM sysibm.sysdummy1, TABLE(snap_get_db_v95('', -1)))
        when (SELECT FLOAT(PROD_RELEASE) FROM SYSIBMADM.ENV_PROD_INFO 
where license_installed = 'Y' fetch first row only) = 9.1 
then (SELECT (DAYS(current timestamp) - DAYS(last_backup))
            FROM sysibm.sysdummy1, TABLE(snap_get_db_v91('', -1)))
        else (SELECT (DAYS(current timestamp) - DAYS(last_backup))
            FROM sysibm.sysdummy1, TABLE(snap_get_db('', -1)))
        end;
    select dbBU ;

    commit work;

This returns, from the case "No authorized routine named "MON_GET_DATABASE" of type "FUNCTION" having compatible arguments was found.. SQLCODE=-440, SQLSTATE=42884" Which is correct since that is a DB2 v10.5 procedure.

What am I missing and how do I do this in DB2?

mustaccio · Accepted Answer · 2015-03-10 21:25:38Z

First of all, your code is still not version-independent. The CREATE VARIABLE statement is only available since v9.7, I believe.

All statements in a compound SQL statement will be compiled prior to execution -- DB2 SQL PL is not an interpreted language, so it will always fail to compile when encountering nonexistent objects. You will have to use dynamic SQL to defer compilation until runtime, something like:

begin 
  declare st varchar(1000);
  declare rel float;

  set rel = (SELECT FLOAT(PROD_RELEASE) FROM SYSIBMADM.ENV_PROD_INFO 
      where license_installed = 'Y' fetch first row only);
  set st = 
    case
        when rel = 10.5 
        then 'SELECT (DAYS(current timestamp) - DAYS(last_backup)) FROM TABLE(mon_get_database(-1))'
        when rel >= 10.0 
        then 'SELECT (DAYS(current timestamp) - DAYS(last_backup)) FROM TABLE(snap_get_db('', -1))'
        when rel = 9.7 
        then 'SELECT (DAYS(current timestamp) - DAYS(last_backup)) FROM TABLE(snap_get_db_v97('', -1))'
        when rel = 9.5 
        then 'SELECT (DAYS(current timestamp) - DAYS(last_backup)) FROM TABLE(snap_get_db_v95('', -1))'
        when rel = 9.1 
        then 'SELECT (DAYS(current timestamp) - DAYS(last_backup)) FROM TABLE(snap_get_db_v91('', -1))'
        else 'SELECT (DAYS(current timestamp) - DAYS(last_backup)) FROM TABLE(snap_get_db('', -1))'
    end;
  prepare s from st; 
  execute s into dbBU;

end

Collectives™ on Stack Overflow

SQL on DB2, How do I run version correct TABLE() functions?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related