Re: Need help with jdbcr4

[Scott Klement <rpg400-l@xxxxxxxxxxxxxxxx>]

Hi Mike,

Your DB2 and SQL Server databases are two completely different database 
engines.  You can't issue a single SQL statement that reads from DB2 and 
writes to SQL Server (or vice-versa)... which database engine would 
intepret the SQL statement?  Whichever one it was would need to 
understand both types of databases...

MySQL may reach that point some day.  It's already able to work on DB2 
databases with the new storage engine.  Maybe some day it'll have 
something similar for SQL Server?  (I don't know?)  but if it did, you'd 
be able to use MySQL to do what you're attempting.  One database engine 
and multiple underlying databases.

But, at the moment you can't do what you're attempting.  I hope you see why.

You CAN, however, write it like this (off the top of my head -- haven't 
tested it)

conndb2 = JDBC_connProp( parms for DB2);
connMS  = JDBC_connProp( parms for SQL Server);

stmt = JDBC_prepStmt( connMS: 'insert into woindx00 values(?,?,?,?)');

rs = JDBC_execQry( conndb2: 'select * from woindx00');
rsmd = JDBC_getMetaData(rs);

dow JDBC_nextRow(rs);
   for x= 1 to JDBC_getColCount(rsmd);
       // get column X
       // set parameter marker X
   endfor;
   JDBC_execPrepUpd(stmt);
enddo;

But, as I said in the COMMON session, it might be faster to export the 
data from DB2 (CPYTOIMPF) and import it into SQL Server.  Folks have 
told me that in the past, anyway -- JDBC isn't the most efficient method 
for a "mass update".   It's more appropriate for when doing a handful of 
records and trying to keep them "real time" rather than a "daily batch" 
type of processing.


Smith, Mike wrote:
> Just back from COMMON and after attending Scott's class on JDBCR4, I have decided to give it a try.  
>
> I have successfully connected to a sql server database and queried the data.  
>
> I'm now trying to take a db2 database and populate the sql server table but am unable to make it work 
>
> I have tried both of these commands.  (both the sql server table and the db2 database have the same name.  
>
> Insert into woindx00 select * from woindx00 
>
> Bulk insert into woindx00 select * from tstmis/woindx00 
>
> I guess when I call jdbc_execupd it doesn't see woindx00(db2).  
>
> Any idea how to resolve. 
>
> Thanks 
>
> Mike