QueryBand and Stored Procedure

Database
Highlighted
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
3 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