Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2010
    Posts
    3

    Question Unanswered: Stored Procedure Fetch first parameter dont work

    hello, i would like to use a parameter to control the fetch first rows. But the db2 system doesnt accept the command.

    Code:
    	CREATE PROCEDURE MOVEPGM.GETLIST(
    IN PMAXRESULTS INTEGER
    	) 
    RESULT SETS 1                                
    LANGUAGE SQL                                 
    BEGIN                                        
    DECLARE c1 CURSOR WITH RETURN FOR            
    select * from table1 FETCH FIRST PMAXRESULTS ROWS ONLY; 
    OPEN C1;
    END ;

    thanks
    Last edited by ZeroQool; 08-14-10 at 14:08. Reason: forget quote

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    And the question is?

  3. #3
    Join Date
    Aug 2010
    Posts
    3
    Quote Originally Posted by n_i View Post
    And the question is?
    the create sp command doesnt work

    exception: "PMAXRESULTS" isnt a valid parm

    SQL-Status: 42601
    Vendorencode: -104
    Nachricht: [SQL0104] Token PMAXRESULTS ungültig. Gültige Token: ROW ROWS. Ursache . . . . : Bei Token PMAXRESULTS wurde ein Syntaxfehler entdeckt. Token PMAXRESULTS ist kein gültiges Token. ROW ROWS ist eine Teilliste gültiger Token. Diese Liste geht davon aus, dass die Anweisung bis zu diesem Token korrekt ist. Es kann eine frühere Stelle in der Anweisung fehlerhaft sein, aber bis zu dieser Stelle scheint die Syntax gültig zu sein. Fehlerbeseitigung: Einen der folgenden Schritte durchführen und die Anforderung wiederholen: - SQL-Anweisung im Bereich des Token PMAXRESULTS überprüfen. Anweisung korrigieren. Der Fehler kann ein fehlendes Komma oder Anführungszeichen sein, er kann in einem falsch geschriebenen Wort oder in der Anordnung der Klauseln begründet sein. - Ist das fehlerhafte Token <ANWEISUNGSENDE>, die Anweisung korrigieren, da sie mit einer ungültigen Klausel endet.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The fetch first clause cannot contain a variable when using static (embedded) SQL. You could create the entire cursor with dynamic SQL in order to use a variable.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Aug 2010
    Posts
    3
    Quote Originally Posted by Marcus_A View Post
    The fetch first clause cannot contain a variable when using static (embedded) SQL. You could create the entire cursor with dynamic SQL in order to use a variable.
    thanks! exist a performance different between dynamic and static?

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by ZeroQool View Post
    thanks! exist a performance different between dynamic and static?
    There is some performance difference because you have to prepare the statement and then execute it. Also, I beleive that you will need to make all the other statements (open, fetch, etc) that refer to the cursor also be dynamic (unless there is some enhancement in a recent release of DB2 that I don't know about).

    However, since most dynamic statements should already be in package cache from previous executions (if they are identical), a dynamic statement may not need to be re-compiled (to determine access plan) each time it is executed. Static (embedded) SQL statements are always compiled ahead of time when the SP is created (there is a package that is created along with the SP that contains the compiled SQL).

    Overall, static SQL is usually faster, but the difference may not be significant. You should do testing to determine the difference if the transaction is critical.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by ZeroQool View Post
    the create sp command doesnt work

    exception: "PMAXRESULTS" isnt a valid parm
    This is not a question; this is a statement. What is your question?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •