QueryBand and Stored Procedure

Database
Enthusiast

QueryBand and Stored Procedure

Hi expert

 

I want to define a QueryBand within a stored procedure. But when I run SQL below, I have the following errors :

  • SPL1027:E(L10), Missing/Invalid SQL statement'E(3707):Syntax error, expected something like an 'END' keyword between ';' and the 'DECLARE' keyword.'.
  • SPL1030:E(L12), Referring to undefined cursor 'cur1'.

 

My procedure :

 

REPLACE PROCEDURE diag_n3.test_rbo (u VARCHAR(30))DYNAMIC RESULT SETS 1

BEGIN
    SET QUERY_BAND = 'APP=HELLO;' FOR TRANSACTION;
    
    DECLARE cur1 CURSOR WITH RETURN ONLY FOR
    SEL grantee, TRIM(TRAILING ',' FROM (XMLAGG(TRIM(rolename) || ',' ORDER BY rolename) (VARCHAR(10000)))) AS liste_role
    FROM dbc.rolemembers WHERE grantee = :u
    GROUP BY 1;
        
    OPEN cur1;      
END;

 

It works fine without set query_band statement.

 

Thanks in advance

 


Accepted Solutions
Enthusiast

Re: QueryBand and Stored Procedure

 

Finally, here is what I did to get the queryband for the session when I called my procedure (thanks PaulDancer) :

 

REPLACE PROCEDURE diag_n3.test_rbo (usertdt VARCHAR(30))DYNAMIC RESULT SETS 1
MAIN: BEGIN 
    DECLARE cur1 CURSOR WITH RETURN ONLY FOR
    SEL grantee, TRIM(TRAILING ',' FROM (XMLAGG(TRIM(rolename) || ',' ORDER BY rolename) (VARCHAR(10000)))) AS liste_role
    FROM dbc.rolemembers WHERE grantee = :usertdt
    GROUP BY 1;
    QB: BEGIN
        DECLARE  vQBStmt VARCHAR(128);
        SET vQBStmt = 'SET QUERY_BAND=''' || 'APP=HELLO;USERTDT=' || usertdt || ';' || '''' ||' FOR SESSION' ||';';
        /* Set the Query Band */
        CALL DBC.SYSEXECSQL(vQBStmt);
    END QB;
    OPEN cur1;      
END MAIN;

CALL diag_n3.test_rbo('inulege');
CALL diag_n3.test_rbo('inubolr');

 Result in DBQLOG :

 

QB_Session.PNG

 

 

1 ACCEPTED SOLUTION
6 REPLIES
Teradata Employee

Re: QueryBand and Stored Procedure

Declarations must precede any SQL statements within a block. Move the SET between the DECLARE and the OPEN.

Enthusiast

Re: QueryBand and Stored Procedure

Hello Fred

I tried your solution.

 

REPLACE PROCEDURE diag_n3.test_rbo (u VARCHAR(30))DYNAMIC RESULT SETS 1
BEGIN
DECLARE cur1 CURSOR WITH RETURN ONLY FOR
SEL grantee, TRIM(TRAILING ',' FROM (XMLAGG(TRIM(rolename) || ',' ORDER BY rolename) (VARCHAR(10000)))) AS liste_role
FROM dbc.rolemembers WHERE grantee = :u
GROUP BY 1; 
SET QUERY_BAND = 'APP=HELLO;' FOR TRANSACTION;
OPEN cur1;
END;

 

It works great but here's what I'm seeing in DBQLOG when i called this procedure :

 

dbqlog.PNG

 

Queryband appears only on the Set Query_Band command.

 

Is it possible to extrapolate by assigning queryband to other requests of the same session?

 

 

 

 

 

 

 

Enthusiast

Re: QueryBand and Stored Procedure

 

Finally, here is what I did to get the queryband for the session when I called my procedure (thanks PaulDancer) :

 

REPLACE PROCEDURE diag_n3.test_rbo (usertdt VARCHAR(30))DYNAMIC RESULT SETS 1
MAIN: BEGIN 
    DECLARE cur1 CURSOR WITH RETURN ONLY FOR
    SEL grantee, TRIM(TRAILING ',' FROM (XMLAGG(TRIM(rolename) || ',' ORDER BY rolename) (VARCHAR(10000)))) AS liste_role
    FROM dbc.rolemembers WHERE grantee = :usertdt
    GROUP BY 1;
    QB: BEGIN
        DECLARE  vQBStmt VARCHAR(128);
        SET vQBStmt = 'SET QUERY_BAND=''' || 'APP=HELLO;USERTDT=' || usertdt || ';' || '''' ||' FOR SESSION' ||';';
        /* Set the Query Band */
        CALL DBC.SYSEXECSQL(vQBStmt);
    END QB;
    OPEN cur1;      
END MAIN;

CALL diag_n3.test_rbo('inulege');
CALL diag_n3.test_rbo('inubolr');

 Result in DBQLOG :

 

QB_Session.PNG

 

 

Re: QueryBand and Stored Procedure

Thanks @RomaricBollore and @pauldancer. This is exactly what i was looking for!

Re: QueryBand and Stored Procedure

what imapct would it have on memory if we keep the cursor open ? I am calling stored procedure from tableau so i would need to keep cursor open. Would appreciate your thoughts.

Junior Contributor

Re: QueryBand and Stored Procedure

Every cursor must be closed sometime.

When you return data from a Stored Procedure using a cursor WITH RETURN, it's not a real cursor, it's a result set.

Tableau should receive the result set and close it after the last row like any other result.