469,307 Members | 2,075 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,307 developers. It's quick & easy.

Execute Immediate in cursor on ibm db2?

1
I'm having difficulties creating a SP in which I pass in a name of a table and query the SYS2 library to find out if it has an auto-increment field. If it does I query for the max value of that field in the table and then alter the table so the next used value is that result plus 1. This is for use when migrating production data over to development.

I'm not sure if it is possible to use "Execute Immediate" as part of a cursor declaration. I'm still fairly new to db2 in general, never mind for IBM. So any assistance would be greatly appreciated.

I'm getting the error on the Cursor declaration (line 10), but here is the exact error code I'm getting:

Expand|Select|Wrap|Line Numbers
  1. SQL State: 42601
  2. Vendor Code: -199
  3. Message: [SQL0199] Keyword IMMEDIATE not expected. Valid tokens: <END-OF-STATEMENT>. Cause . . . . . :   The keyword IMMEDIATE was not expected here.  A syntax error was detected at keyword IMMEDIATE.  The partial list of valid tokens is <END-OF-STATEMENT>. This list assumes that the statement is correct up to the unexpected keyword.  The error may be earlier in the statement but the syntax of the statement seems to be valid up to this point. Recovery  . . . :   Examine the SQL statement in the area of the specified keyword.  A colon or SQL delimiter may be missing. SQL requires reserved words to be delimited when they are used as a name. Correct the SQL statement and try the request again.
  4.  
And then finally here is my SP

Expand|Select|Wrap|Line Numbers
  1. /* Creating procedure DLLIB.SETNXTINC@ */
  2. CREATE OR REPLACE PROCEDURE DLLIB.SETNXTINC@(IN TABLE CHARACTER (10) ) LANGUAGE SQL CONTAINS SQL PROGRAM TYPE SUB CONCURRENT ACCESS RESOLUTION DEFAULT DYNAMIC RESULT SETS 0 OLD SAVEPOINT LEVEL COMMIT ON RETURN NO 
  3. SET @STMT1 = 'SELECT COLUMN_NAME ' || 
  4. 'FROM QSYS2.SYSCOLUMNS ' ||
  5. 'WHERE TABLE_SCHEMA =''DLLIB'' and table_name = ''' || TRIM(TABLE) || '''' ||
  6. 'AND HAS_DEFAULT = ''I'' ' ||
  7. 'OR HAS_DEFAULT = ''J'';';
  8.  
  9. DECLARE cursor1 CURSOR FOR
  10. EXECUTE IMMEDIATE @STMT1;
  11.  
  12. OPEN cursor1;
  13.  
  14. WHILE (sqlcode == 0){
  15. FETCH cursor1 INTO field;
  16. SET @STMT2 = 'ALTER TABLE DLLIB.' || TRIM(TABLE) || ''' ' ||
  17. 'ALTER COLUMN ' || TRIM(field) || ' RESTART WITH ( ' || 
  18.     'SELECT MAX(' || TRIM(field) || ') ' || 
  19.     'FROM   DLLIB.' || TRIM(TABLE) || ');';
  20. EXECUTE IMMEDIATE @STMT2;
  21. };;
  22.  
  23. /* Setting label text for DLLIB.SETNXTINC@ */
  24. LABEL ON ROUTINE DLLIB.SETNXTINC@ ( CHAR() )  IS 'Set the next auto-increment';
  25.  
  26. /* Setting comment text for DLLIB.SETNXTINC@ */
  27. COMMENT ON PARAMETER ROUTINE DLLIB.SETNXTINC@ ( CHAR() ) (TABLE IS 'Table from DLLIB' ) ;
  28.  
Mar 26 '15 #1
0 1757

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by finlma | last post: by
1 post views Thread by lakon15 | last post: by
3 posts views Thread by nghivo | last post: by
3 posts views Thread by Rahul Babbar | last post: by
6 posts views Thread by Oliver | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.