Db2

 View Only

 IBM DB2 LUW stored procedure with dynamic SQL

  • IBMChampion
Mirza Tinjak's profile image
Mirza Tinjak posted Sat February 08, 2025 07:07 AM

Hi there,

I am working with IBM DB2 stored procedures, especially stored procedure with dynamic SQL. Problem occurs when I am trying to fetch single value using dynamic SQL. I checked up  documentation  regarding DB2 dynamic SQL but it seems so confusing with not enough stored procedure examples. I will provide sample of code for discussion:

FETCH_VALUE: BEGIN
DECLARE TEXT VARCHAR(2000);
DECLARE SQL_QUERY VARCHAR(2000);
DECLARE NOT_FOUND INTEGER DEFAULT 0;
DECLARE TEST VARCHAR(100);
DECLARE test_c CURSOR FOR SQL_QUERY;
SET TEXT = 'SELECT DISTINCT ''EXISTS'' TEST FROM SCHEMA_TEST.TEST_TABLE';
PREPARE SQL_QUERY FROM TEXT;

OPEN test_c;
FETCH FROM test_c INTO TEST;
INSERT INTO SCHEMA_TEST.LOG_TEST(ENTRY,DATE)
VALUES ('test: ' || TEST,sysdate);
END FETCH_VALUE;

This code works. But, I am curious regarding this: "Is there simpler way to get single value using dynamic SQL?"

If I'm in the wrong subforum for the discussion, please have a moderator move it to the appropriate section.


Any suggestions will be helpful.


Best regards,

Mirza

malek shabou's profile image
malek shabou posted Sun February 09, 2025 02:09 PM

Hi,

if you want only to store the result of the select into a table you can do it in one step, prepare and execute this dynamic SQL:
INSERT INTO SCHEMA_TEST.LOG_TEST(ENTRY,DATE) select distinct 'test: EXISTS',sysdate FROM SCHEMA_TEST.TEST_TABLE

you can execute directly the prepared statement without having to passe by a cursor.

Mirza Tinjak's profile image
Mirza Tinjak posted Sun February 09, 2025 02:21 PM

Thank you Malek on response.

The code i have provided is for presentational purposes. Main idea is to discuss fetching single value using dynamic sql. I know this can be done without dynamic sql, but that is not main goal.

Thank you anyway on posting.

Best regards, 

Mirza

malek shabou's profile image
malek shabou posted Sun February 09, 2025 02:27 PM

Hi Mirza,

since SELECT INTO cannt be used as dynamic SQL the is no other way, may be you can embed the code in a function a use the function as many time as you wish

Mirza Tinjak's profile image
Mirza Tinjak posted Sun February 09, 2025 02:36 PM

Malek, thank you again

Could you provide one function that would do the job?

Thank you in forward.

Mark Barinstein's profile image
Mark Barinstein posted Mon February 10, 2025 02:04 AM

The simpler way to get a single value (or a single row) in DB2 for LUW is use of the SET statement.

--#SET TERMINATOR @
 
SET SERVEROUTPUT ON@
 
BEGIN
  DECLARE V_TABSCHEMA VARCHAR(128) DEFAULT 'SYSCAT';
  DECLARE V_TABNAME   VARCHAR(128);
  DECLARE V_COLCOUNT  INT;
  PREPARE S1 FROM 'SET (?, ?) = (SELECT TABNAME, COLCOUNT FROM SYSCAT.TABLES WHERE TABSCHEMA = ? FETCH FIRST 1 ROW ONLY)';
  EXECUTE S1 INTO V_TABNAME, V_COLCOUNT USING V_TABSCHEMA;
  CALL DBMS_OUTPUT.PUT_LINE('Tabname: ' || V_TABNAME || ', Column count: ' || TO_CHAR(V_COLCOUNT));
END@
 
SET SERVEROUTPUT OFF@

Note, that it's your responsibility to make the subselect return not more than 1 row, because you get an error otherwise.
I achieve this with the FETCH FIRST clause in the example above, since the subselect may return multiple rows without it.

Mirza Tinjak's profile image
Mirza Tinjak posted Mon February 10, 2025 03:26 AM

Thank you Mark for replying.

Your solution is obviously simpler and gives expected result. Great job with using SET. Question of thread seems to be answered.

Thank you for patience.

Best regards,

Mirza

Damir Wilder's profile image
Damir Wilder IBM Champion posted Mon February 10, 2025 03:54 AM

Hi Mirza,

In addition to Mark's response, the general syntax for dynamic SQL in stored procs is:

>>-PREPARE--statement-name--FROM--host-variable--------><

>>-EXECUTE--statement-name-------------------------------------->
>--+----------------------------------------------+------------->
   |         .-,--------------------.             |
   |         V                      |             |
   '-INTO--+---result-host-variable-+-----------+-'
           '-DESCRIPTOR--result-descriptor-name-'

>--+----------------------------------------------+------------><
   |          .-,-------------------.             |
   |          V                     |             |
   '-USING--+---input-host-variable-+-----------+-'
            '-DESCRIPTOR--input-descriptor-name-'
So, you can do both updating (via USING) and selecting (via INTO) in a dynamic SQL (or possibly the combination of the two, haven't tried it though).
Regards, Damir

Related Content