DB2 - function to return max value from specified column

Question

I'm new to DB2 PL/SQL and ran into some trouble, as there is not much community resources except for the official documentation which doesn't answer to all my questions.

I'm doing some data migration, and need a function that would return a max value from a specified column, from a specified table. In the last 2 hours I've come up with two approaches, but none of these works - due to my lack of knowledge of DB2 PL/SQL.

First one is preparing a query and executing it, but I can't execute a select query into a variable. Here's the code that doesn't execute:

CREATE OR REPLACE FUNCTION getMaxColValue (schemaName VARCHAR(30), 
    tableName VARCHAR(30), columnName VARCHAR(30))
-- function used to get max ID of a column during data migration
RETURNS INTEGER
LANGUAGE SQL
BEGIN
    DECLARE query VARCHAR(1000);
    DECLARE maxColValue INT;
    DECLARE stmt STATEMENT;

  SET query = 'select max(' || columnName || ') from ' || schemaName || '.' || tableName || '';
    PREPARE stmt FROM query;
    EXECUTE query INTO maxColValue;
    RETURN maxColValue;

END

Error returned:

Lookup Error - DB2 Database Error: ERROR [07003] [IBM][DB2/AIX64] SQL0518N  The statement named in the EXECUTE statement is not in a prepared state or is a SELECT or VALUES statement.

I've also tried something like this, returning scalar SQL value:

CREATE FUNCTION getMaxColValue_2 (schemaName VARCHAR(30), tableName VARCHAR(30), columnName VARCHAR(30))
     RETURNS INT 
     LANGUAGE SQL
     READS SQL DATA
     NO EXTERNAL ACTION
     DETERMINISTIC
     RETURN
       SELECT max(columnName)
         FROM schemaName.tableName;

Error returned:

Lookup Error - DB2 Database Error: ERROR [42704] [IBM][DB2/AIX64] SQL0204N  "SCHEMANAME.TABLENAME" is an undefined name.

but I guess it's harder to pass schemaname and tablename as variables here. I'll be gateful for any help. Window function is not much of an option, as I need to use this function in migration procedures and not simple select statements.

There are some syntax errors, but what's worse there are probably some logical errors due to my lack of knowledge of PL/SQL.

Cheers, Jony

Please edit your question with the error you get. It could simply be a permissions error, for instance. — Gordon Linoff
– Gordon Linoff, Commented Apr 1, 2014 at 11:19
Done, but I guess there will be some more errors after that since I don't fully grasp function types and logic yet. — Jan Siekierski
– Jan Siekierski, Commented Apr 1, 2014 at 11:42
@Gordon_Linoff After replacing 'BEGIN ATOMIC' with 'BEGIN' the function executed, but when I try to run: select ub.getMaxColValue('pk', 'pk_reguly', 'reg_id') from sysibm.sysdummy1 I get: Lookup Error - DB2 Database Error: ERROR [07003] [IBM][DB2/AIX64] SQL0518N The statement named in the EXECUTE statement is not in a prepared state or is a SELECT or VALUES statement. I've edited the original post, so the first function is now able to execute. — Jan Siekierski
– Jan Siekierski, Commented Apr 1, 2014 at 12:25

mustaccio · Accepted Answer · 2014-04-01 13:00:54Z

2

You cannot EXECUTE a SELECT statement, which is exactly what the error message is telling you.

Instead, you should declare a cursor, open it, then fetch the result into your variable:

CREATE OR REPLACE FUNCTION getMaxColValue (schemaName VARCHAR(30), 
tableName VARCHAR(30), columnName VARCHAR(30))
RETURNS INTEGER
LANGUAGE SQL
not deterministic
reads sql data
begin
 declare l_max int;
 declare c_cur cursor for l_stmt;

 prepare l_stmt from 'select max(' || columnName || ') from ' || rtrim(schemaName) ||
 '.' || tableName;
 open c_cur;
 fetch c_cur into l_max;
 close c_cur;
 return l_max;
end

answered Apr 1, 2014 at 13:00

mustaccio

19.1k16 gold badges51 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jan Siekierski Over a year ago

Thanks so much mate, that's exactly what I needed. I've tried some shananigans with cursor before but didn't make it that far :)

Collectives™ on Stack Overflow

DB2 - function to return max value from specified column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related