title: In a procedure connect to 2 databases: one local and the other remote
body: <p>I'm trying to write a stored procedure, in DB2 for AS400, which runs a query on the local database (the same where the procedure is stored; it's named <code>DBLocale</code>) and an other on a remote database (let's call it <code>DBRemoto</code>). In iSeries Navigator I can see both of them selecting "Database" node.</p>
  
  <p>I tried something with <code>CONNECT TO DBRemoto;</code> and/or <code>SET CONNECTION DBRemoto;</code>, but got weird results: "connection already exists", "connection doesn't exist", "CALL instruction completed" (but no result set) etc. .</p>
  
  <p>This would be what I expect to do:</p>
  
  <pre><code>CREATE PROCEDURE MYLIB.TEST_CONNECT_INSIDE_PROCEDURE
  DYNAMIC RESULT SETS 1 
  LANGUAGE SQL 
  READS SQL DATA
  BEGIN
      DECLARE sql_string VARCHAR(1024) DEFAULT '';
      DECLARE locale INT DEFAULT -1;
      DECLARE remoto INT DEFAULT -1;
      DECLARE cur CURSOR FOR stmt;
      DECLARE curOut CURSOR FOR select locale, remoto from SYSIBM.SYSDUMMY1;
  
      -- run the same query in both DBs; I checked, results must be different
      SET sql_string = 'select count(*) as QTY from MYLIB.MYTABLE';
  
      -- run locally
      CONNECT TO DBLocale; --it's one of my attempts
      PREPARE stmt FROM sql_string;
      OPEN cur;
      FETCH cur INTO locale;
      CLOSE cur;
  
      -- run remotely
      CONNECT TO DBRemoto; --it's one of my attempts
      PREPARE stmt FROM sql_string;
      OPEN cur;
      FETCH cur INTO remoto;
      CLOSE cur;
  
      -- output results
      OPEN curOut;
      SET RESULT SETS CURSOR curOut;
  END;
  </code></pre>
  
  <p>I call it with</p>
  
  <pre><code>call MYLIB.TEST_CONNECT_INSIDE_PROCEDURE()
  </code></pre>
  
  <p>Can someone tell me if it is possible and clarify how it works, please? If it's not possible in a procedure, is there a workaround? Many thanks!</p>