Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2015
    Posts
    36
    Provided Answers: 2

    Answered: sql/pl Dynamic queries with cursor variables

    I want to iterate dynamic sql but couldn't find solution.

    pl/sql it is possible through IBM DB2 PL/SQL Dynamic queries with cursor variables

    my current code (not dynamiclly)
    Code:
    CREATE OR REPLACE PROCEDURE CREATE_VIEWS(IN FED_NAME varchar(120),
    													IN REMOTE_SCHEMA varchar(120))
      LANGUAGE SQL
    BEGIN
    	DECLARE EOF INT DEFAULT 0;
    	DECLARE STMT VARCHAR(200);
    	DECLARE R_TAB VARCHAR(200);
    
    	DECLARE C_TABS CURSOR FOR
    		select tabname
    		from FED_NAME.syscat.tables 
    		where tabschema=REMOTE_SCHEMA;
    
    	DECLARE CONTINUE HANDLER FOR NOT FOUND
    		SET EOF = 1;
    
    	OPEN C_TABS;
    	WHILE EOF = 0 DO
    		FETCH FROM C_TABS INTO R_TAB;
    		SET STMT = 'create or replace view ' || R_TAB  || ' as select * from '|| FED_NAME  ||'.'|| REMOTE_SCHEMA || '.'||R_TAB;
    		EXECUTE IMMEDIATE STMT;
    	END WHILE;
    	CLOSE C_TABS;
    END

    Where the problem is in
    Code:
    from FED_NAME.syscat.tables
    So I need to change the cursor to be by string.

    All the examples I saw are using execute immediate with isn't helping much

  2. Best Answer
    Posted by mark.b

    "try this:
    Code:
    begin
      DECLARE STMT VARCHAR(200);
      DECLARE R_TAB VARCHAR(200);
    
      DECLARE C_TABS CURSOR FOR S1;
      SET STMT='select tabname from syscat.tables where tabschema=?';
      PREPARE S1 FROM STMT;
      OPEN C_TABS USING 'SYSCAT';
      FETCH C_TABS INTO R_TAB;
      -- WHILE LOOP
      CLOSE C_TABS;
    end@
    "


  3. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    try this:
    Code:
    begin
      DECLARE STMT VARCHAR(200);
      DECLARE R_TAB VARCHAR(200);
    
      DECLARE C_TABS CURSOR FOR S1;
      SET STMT='select tabname from syscat.tables where tabschema=?';
      PREPARE S1 FROM STMT;
      OPEN C_TABS USING 'SYSCAT';
      FETCH C_TABS INTO R_TAB;
      -- WHILE LOOP
      CLOSE C_TABS;
    end@
    Regards,
    Mark.

  4. #3
    Join Date
    Nov 2015
    Posts
    36
    Provided Answers: 2
    Compiled! Thanks a lot (i have some things to setup before testing it)

    BTW I try to catch the code for error in this procedure but the example in the net are not compiling, maybe you have an idea.

    Code:
    DECLARES...
    
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING  
          BEGIN
    		SET EOF = 1;
    		VALUES (SQLCODE,SQLSTATE) INTO V_SQLCODE,V_SQLSTATE;
                    --also tried this
                   -- V_SQLCODE=SQLCODE;
    		-- V_SQLSTATE=SQLSTATE;
                   insert into t_create_views_log(table_name, error, create_date) 
    		values (R_TAB ,'SQLCODE: '|| V_SQLCODE || ', SQLSTATE: ' || V_SQLSTATE,current date);
    		commit;
          END;
    
    CODE...
    the SQLCODE is not recognizable
    Code:
    SQL0206N  "SQLCODE" is not valid in the context where it is used.  LINE
    NUMBER=19.  SQLSTATE=42703

  5. #4
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Code:
      DECLARE STMT VARCHAR(200);
      DECLARE R_TAB VARCHAR(200);
      DECLARE SQLCODE, V_SQLCODE INT DEFAULT 0;
      DECLARE SQLSTATE, V_SQLSTATE CHAR(5) DEFAULT '00000';
      DECLARE V_MSG VARCHAR(128);
      DECLARE EOF INT DEFAULT 0;
      
      DECLARE C_TABS CURSOR FOR S1;
      
      DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING  
        BEGIN
          --GET DIAGNOSTICS EXCEPTION 1 V_MSG = MESSAGE_TEXT;
          SET (V_SQLCODE, V_SQLSTATE)=(SQLCODE, SQLSTATE);
          SET EOF = 1;
        END;
    Notes:
    - You must save the values of the SQLCODE or/and SQLSTATE variables in the 1-st statement of the handler. They are reset after each statement, so in your example they are reset after the setting of the EOF variable and you lose the original values. The exception is GET DIAGNOSTICS statement - these variables are not reset by this statement.
    - You shouldn't issue the COMMIT statement in a procedure unless it's absolutely necessarily due to some special logic of the procedure. Calling application should make a decision on this generally.
    Regards,
    Mark.

  6. #5
    Join Date
    Nov 2015
    Posts
    36
    Provided Answers: 2
    great it helped!

    back to the original problem, I get:

    Code:
    SQLCODE: -1822, SQLSTATE: 560BD	
    SQLCODE: -501, SQLSTATE: 24501
    SQL1822N
    SQL-501 cursor not open

    i've set a simple case that reproduce the problem and it seems that selecting from syscat.tables over federation is not supported - is it?

    Code:
    DB21034E  The command was processed as an SQL statement because it was not a
    valid Command Line Processor command.  During SQL processing it returned:
    SQL1822N  Unexpected error code "42808" received from data source "MLZ3_SRV".
    Associated text and tokens are "func="do_prep" msg=" SQL0151N  The column
    "TABLEORG" ca".  SQLSTATE=560BD
    For
    Code:
    begin
      DECLARE STMT VARCHAR(200);
      DECLARE R_TAB VARCHAR(200);
      DECLARE EOF INT DEFAULT 0;
    
      DECLARE C_TABS CURSOR FOR S1;
    
      DECLARE CONTINUE HANDLER FOR NOT FOUND
    		SET EOF = 1;
    		
      SET STMT='select tabname from MLZ3_SRV.syscat.tables where tabschema=?';   -- where MLZ3_SRV is the federation name as of 'create server' command
      PREPARE S1 FROM STMT;
      OPEN C_TABS USING 'SYSCAT';
      WHILE EOF = 0 DO
    	  FETCH C_TABS INTO R_TAB;
    	  insert into t_create_views_log values(R_TAB,'',current date);
      END WHILE;
      CLOSE C_TABS;
    end@

    EDIT:
    It's seems the problem is selecting from syscat.tables using federation and cursor
    Last edited by AntiLopa; 01-25-16 at 08:40.

  7. #6
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Try this:

    Code:
      ...
      SET STMT='select tabname from MLZ3_SRV.syscat.tables where tabschema=? for read only';
      PREPARE S1 FROM STMT;
      OPEN C_TABS USING 'SYSCAT';
      FETCH C_TABS INTO R_TAB;
      WHILE EOF = 0 DO
    	  insert into t_create_views_log values(R_TAB,'',current date);
    	  FETCH C_TABS INTO R_TAB;
      END WHILE;
      CLOSE C_TABS;
      ...
    Regards,
    Mark.

  8. #7
    Join Date
    Nov 2015
    Posts
    36
    Provided Answers: 2
    The 'for read only' solved it.
    Can you explain why?

    Thanks

  9. #8
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    I'm not sure but it may be some federation "feature": it can add 'for update' clause implicitly or such an ambiguous cursor is considered as updatable and not read-only.
    But updatable federative cursors are not supported afaik.
    So, the option is either to add 'for read only' clause or make the cursor read-only using something like this:
    'select tabname from MLZ3_SRV.syscat.tables, table(values 1) t(i) where tabschema=?'
    Regards,
    Mark.

Posting Permissions

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