Topic
  • 19 replies
  • Latest Post - ‏2014-04-17T14:37:31Z by kennetheld
canutri
canutri
357 Posts

Pinned topic Passing a recordset from SQL Server to DB2/400

‏2008-02-21T23:15:09Z |
All,

How I can fetch a recordset from SQL Server and push it to DB2/400? I'm not sure how I can get an EGL project to reference two different connections. I can do this in pure java, but would like to use EGL if possible.

One consideration was to create a seperate project with SQL server defined as the data source to fetch the recordset as a web service. Another project would reference a connection to DB2/400 and consume the webservice and write the records to DB2.

Any help would be greatly appreciated.

Thanks, Daron
Updated on 2008-03-28T20:22:10Z at 2008-03-28T20:22:10Z by canutri
  • markevans
    markevans
    2843 Posts

    Re: Passing a recordset from SQL Server to DB2/400

    ‏2008-02-21T23:51:21Z  
    Daron,

    You can read the records from SQL server (something like the GET array statement). Then you can use the sqllib.connect(connectionURL, userid, password) statement to connect to DB2 on the iSeries.

    If you want to switch back to SQL Server, then you would use Sqllib.disconnect() and then connect back to SQL Server (using the sqllib.connect).

    If this is a batch application, the one trick I found (just a week or two ago), is that when you generate you need to specify both JDBC drivers in the sqlJDBCDriverClass build descriptor option (the class for SQL server and db2 on iSeries). The class names would be separated with a comma. Also, of course, the jar files containing the classes needs to be in the Java Build Path of the project.

    If this is a web application, then the connectionURL would be the JNDI name that you have defined a datasource for. You can define both resource references in the web.xml and datasources in the EAR file/app server.

    hope this helps.
  • canutri
    canutri
    357 Posts

    Re: Passing a recordset from SQL Server to DB2/400

    ‏2008-02-22T03:30:22Z  
    • markevans
    • ‏2008-02-21T23:51:21Z
    Daron,

    You can read the records from SQL server (something like the GET array statement). Then you can use the sqllib.connect(connectionURL, userid, password) statement to connect to DB2 on the iSeries.

    If you want to switch back to SQL Server, then you would use Sqllib.disconnect() and then connect back to SQL Server (using the sqllib.connect).

    If this is a batch application, the one trick I found (just a week or two ago), is that when you generate you need to specify both JDBC drivers in the sqlJDBCDriverClass build descriptor option (the class for SQL server and db2 on iSeries). The class names would be separated with a comma. Also, of course, the jar files containing the classes needs to be in the Java Build Path of the project.

    If this is a web application, then the connectionURL would be the JNDI name that you have defined a datasource for. You can define both resource references in the web.xml and datasources in the EAR file/app server.

    hope this helps.
    Hi Mark,

    Thanks for the info, it should prove to be very helpful as always. My initial plan is to make this a batch application that can be scheduled job or called from a web application.

    I knew it was possible to define multiple datasources in the web.xml, but wasn't certain how the EGL parts knew which datasource to reference. If my primary connection is to my DB2 db and I issue the sqllib.connect to connect to SQL Server, does this terminate my DB2 connection. In other words, can there only be one connection open at a time? Or does my DB2 connection remain active? Should I issue another sqllib.connect to re-connect to DB2 when I'm done fetching records from SQL Server?

    Being curious as I am, I googled - EGL sqllib.connect - and came across
    RDBe-EGL Tech Corner - Issues #3
    http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=IBMA,IBMA:2006-43,IBMA:en&q=egl+sqllib%2econnect
    Page 14 demonstrates the use of sqllib.connect

    Message was edited by: canutri
    Updated on 2008-02-22T03:30:22Z at 2008-02-22T03:30:22Z by canutri
  • Arco_Synobsys
    Arco_Synobsys
    224 Posts

    Re: Passing a recordset from SQL Server to DB2/400

    ‏2008-02-22T11:22:05Z  
    • canutri
    • ‏2008-02-22T03:18:50Z
    Hi Mark,

    Thanks for the info, it should prove to be very helpful as always. My initial plan is to make this a batch application that can be scheduled job or called from a web application.

    I knew it was possible to define multiple datasources in the web.xml, but wasn't certain how the EGL parts knew which datasource to reference. If my primary connection is to my DB2 db and I issue the sqllib.connect to connect to SQL Server, does this terminate my DB2 connection. In other words, can there only be one connection open at a time? Or does my DB2 connection remain active? Should I issue another sqllib.connect to re-connect to DB2 when I'm done fetching records from SQL Server?

    Being curious as I am, I googled - EGL sqllib.connect - and came across
    RDBe-EGL Tech Corner - Issues #3
    http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=IBMA,IBMA:2006-43,IBMA:en&q=egl+sqllib%2econnect
    Page 14 demonstrates the use of sqllib.connect

    Message was edited by: canutri
    Why not just define two different entries in your rununit.properties file, one for the function reading SQL and one for the function writing DB2.
  • markevans
    markevans
    2843 Posts

    Re: Passing a recordset from SQL Server to DB2/400

    ‏2008-02-22T13:47:40Z  
    Why not just define two different entries in your rununit.properties file, one for the function reading SQL and one for the function writing DB2.
    Arco,

    The way I remember (did not try today) is that the rununit.properties define the "initial connection" for the rununit.

    In other words, if you have two rununit.properties

    (vgj.default.database.<name>, vgj.default.database.<name2>),

    then the "default" connection is whichever <name> is executed first... This establishes the database connection for the rununit (across calls, library invocations, etc) ...unless overridden by a
    sqllib.Connect() statement.

    Daron,

    have to check on your questions..... What needs to be done may depend on the capabilities of the drivers.
  • canutri
    canutri
    357 Posts

    Re: Passing a recordset from SQL Server to DB2/400

    ‏2008-02-22T19:50:06Z  
    • markevans
    • ‏2008-02-21T23:51:21Z
    Daron,

    You can read the records from SQL server (something like the GET array statement). Then you can use the sqllib.connect(connectionURL, userid, password) statement to connect to DB2 on the iSeries.

    If you want to switch back to SQL Server, then you would use Sqllib.disconnect() and then connect back to SQL Server (using the sqllib.connect).

    If this is a batch application, the one trick I found (just a week or two ago), is that when you generate you need to specify both JDBC drivers in the sqlJDBCDriverClass build descriptor option (the class for SQL server and db2 on iSeries). The class names would be separated with a comma. Also, of course, the jar files containing the classes needs to be in the Java Build Path of the project.

    If this is a web application, then the connectionURL would be the JNDI name that you have defined a datasource for. You can define both resource references in the web.xml and datasources in the EAR file/app server.

    hope this helps.
    Mark,

    Presently in the project build descriptor - <+project+>.eglbld - the sqlJDBCDriverClass has is empty. Am I looking in the wrong place to add the JDBC driver classes?

    While adding my SQL Server jar to the Java Build Path, I noticed there was no entry for jt400.jar. Is this the defference between a batch and a web application in that a batch application uses the Java Build Path, whereas a web application uses JNDI?
  • markevans
    markevans
    2843 Posts

    Re: Passing a recordset from SQL Server to DB2/400

    ‏2008-02-22T20:00:04Z  
    • canutri
    • ‏2008-02-22T19:50:06Z
    Mark,

    Presently in the project build descriptor - <+project+>.eglbld - the sqlJDBCDriverClass has is empty. Am I looking in the wrong place to add the JDBC driver classes?

    While adding my SQL Server jar to the Java Build Path, I noticed there was no entry for jt400.jar. Is this the defference between a batch and a web application in that a batch application uses the Java Build Path, whereas a web application uses JNDI?
    Daron,

    You got the idea...
    The use of the sqlJDBCDriverClass and the Java Build Path (CLASSPATH) for jar files is for batch...

    For Web apps, EGL uses the JNDI name /datasource which specify the class names and jar files.

    The Build descriptor J2EE=YES|NO tells the generator which is needed/important.
  • canutri
    canutri
    357 Posts

    Re: Passing a recordset from SQL Server to DB2/400

    ‏2008-02-22T20:11:28Z  
    • markevans
    • ‏2008-02-22T20:00:04Z
    Daron,

    You got the idea...
    The use of the sqlJDBCDriverClass and the Java Build Path (CLASSPATH) for jar files is for batch...

    For Web apps, EGL uses the JNDI name /datasource which specify the class names and jar files.

    The Build descriptor J2EE=YES|NO tells the generator which is needed/important.
    Mark,

    You're not only good, but fast on the reply also.

    Thanks for the clarification. I see now that I was thinking my SQL connection would be made in a batch job, but since it will play a small role in a larger Web application project I need to use the JNDI method.
  • canutri
    canutri
    357 Posts

    Re: Passing a recordset from SQL Server to DB2/400

    ‏2008-03-26T13:23:03Z  
    • markevans
    • ‏2008-02-21T23:51:21Z
    Daron,

    You can read the records from SQL server (something like the GET array statement). Then you can use the sqllib.connect(connectionURL, userid, password) statement to connect to DB2 on the iSeries.

    If you want to switch back to SQL Server, then you would use Sqllib.disconnect() and then connect back to SQL Server (using the sqllib.connect).

    If this is a batch application, the one trick I found (just a week or two ago), is that when you generate you need to specify both JDBC drivers in the sqlJDBCDriverClass build descriptor option (the class for SQL server and db2 on iSeries). The class names would be separated with a comma. Also, of course, the jar files containing the classes needs to be in the Java Build Path of the project.

    If this is a web application, then the connectionURL would be the JNDI name that you have defined a datasource for. You can define both resource references in the web.xml and datasources in the EAR file/app server.

    hope this helps.
    Can batch and web applications be combined into the same project? Or does this confuse the use of web db access via JNDI and batch db access via JDBC drivers?

    Our application is mostly web UI, but will include a process to fetch data from an SQL Server db as a scheduled daily job (batch) or on-demand via a web request.

    Having sucessfully completed a web UI to display a resultset from SQL Server in a browser, I attempted to use the same access library in conjuction with a batch (EGL/java) program. If I applied the correct sqlJDBCDriverClass (com.ibm.websphere.jdbcx.sqlserver.SQLServerDataSource) in the build descriptor and included the appropriate external jar's (sqlserver.jar, base.jar, util.jar) in the Java Build Path I cannot run the java program. I get the following error:

    EGL0507E Error loading JDBC drivers: egl.core.RuntimeException: EGL0009E The property vgj.jdbc.drivers must be given a value

    Should I have a seperate batch project or did I not apply the correct components for batch db access?

    Daron
  • Arco_Synobsys
    Arco_Synobsys
    224 Posts

    Re: Passing a recordset from SQL Server to DB2/400

    ‏2008-03-26T15:21:39Z  
    • canutri
    • ‏2008-03-26T13:23:03Z
    Can batch and web applications be combined into the same project? Or does this confuse the use of web db access via JNDI and batch db access via JDBC drivers?

    Our application is mostly web UI, but will include a process to fetch data from an SQL Server db as a scheduled daily job (batch) or on-demand via a web request.

    Having sucessfully completed a web UI to display a resultset from SQL Server in a browser, I attempted to use the same access library in conjuction with a batch (EGL/java) program. If I applied the correct sqlJDBCDriverClass (com.ibm.websphere.jdbcx.sqlserver.SQLServerDataSource) in the build descriptor and included the appropriate external jar's (sqlserver.jar, base.jar, util.jar) in the Java Build Path I cannot run the java program. I get the following error:

    EGL0507E Error loading JDBC drivers: egl.core.RuntimeException: EGL0009E The property vgj.jdbc.drivers must be given a value

    Should I have a seperate batch project or did I not apply the correct components for batch db access?

    Daron
    Daron,

    EGL java batch applications use the rununit.properties file to get there connection information from.
    It can be found at the java source folder.
  • canutri
    canutri
    357 Posts

    Re: Passing a recordset from SQL Server to DB2/400

    ‏2008-03-27T19:23:30Z  
    Daron,

    EGL java batch applications use the rununit.properties file to get there connection information from.
    It can be found at the java source folder.
    There was no rununit.properties file. Maybe this was because I started this project as a Web project. So I dropped the rununit.properties file from another batch project accessing the same SQL Server db table into my web project's java resource folder. I'm still getting the same error.

    I've change the sqllib.connect to use the correct jdbc connection string. It's my hope to pass a constant to the access routine to know when to use a JDNI source or jdbc connection string depending on who's calling - a web or batch resource.
  • markevans
    markevans
    2843 Posts

    Re: Passing a recordset from SQL Server to DB2/400

    ‏2008-03-27T21:24:11Z  
    • canutri
    • ‏2008-03-27T19:23:30Z
    There was no rununit.properties file. Maybe this was because I started this project as a Web project. So I dropped the rununit.properties file from another batch project accessing the same SQL Server db table into my web project's java resource folder. I'm still getting the same error.

    I've change the sqllib.connect to use the correct jdbc connection string. It's my hope to pass a constant to the access routine to know when to use a JDNI source or jdbc connection string depending on who's calling - a web or batch resource.
    Hi,

    You should be able to run batch and web applicatoins from a web project (I have).

    The problem may be due to a couple of factors:

    1.) The use of a JDBC driver and connectionURL vs datasource/JNDi name is based on what type of application is started first in the rununit.
    In other words,
    • if a JSF handler is started first, then any subsequent library invocation thinks it is running in an web application (J2EE apps) and it will look for a JNDI definition and properties in the web.xml

    • If a generated program is the started first, then it reads the rununit.properties, all data access runs as a non-J2EE application and therefore uses the JDBC driever and connectionURL's for the database application.

    2.) Since you want to share a data access library, this may present a problem. When you generate it, you have to specify J2EE Yes|no in the build descriptor. So, it has to be one way or the other. So, not sure how you can share libraries across batch and web use.

    Let me know if I did not understand things right...and maybe there is something else we can figure out....or work together to see if I missing something based on the text here.
  • canutri
    canutri
    357 Posts

    Re: Passing a recordset from SQL Server to DB2/400

    ‏2008-03-28T20:22:10Z  
    • markevans
    • ‏2008-03-27T21:24:11Z
    Hi,

    You should be able to run batch and web applicatoins from a web project (I have).

    The problem may be due to a couple of factors:

    1.) The use of a JDBC driver and connectionURL vs datasource/JNDi name is based on what type of application is started first in the rununit.
    In other words,
    • if a JSF handler is started first, then any subsequent library invocation thinks it is running in an web application (J2EE apps) and it will look for a JNDI definition and properties in the web.xml

    • If a generated program is the started first, then it reads the rununit.properties, all data access runs as a non-J2EE application and therefore uses the JDBC driever and connectionURL's for the database application.

    2.) Since you want to share a data access library, this may present a problem. When you generate it, you have to specify J2EE Yes|no in the build descriptor. So, it has to be one way or the other. So, not sure how you can share libraries across batch and web use.

    Let me know if I did not understand things right...and maybe there is something else we can figure out....or work together to see if I missing something based on the text here.
    Mark,

    I get it now - a project cannot be both a batch and web. To get my batch program to work, I had to change to J2EE NO as you indicated.

    It was my hope to have a scheduled batch job which will run nightly use the same data access library as the web interface. My project involves opening a remote connection to an SQL Server database to collect inventory transactions. Once I have this resultset, I need to upload them into our DB2/400 database to await further processing in our ERP system. Fetching the resultset will occur primarily as a batch job, but on occasion the administrator may want to fetch records for selected warehouses on demand.

    Maybe another approach would simply have the web interface call the batch program to fetch the SQL Server db data. Once that call is complete and the data is ported into DB2/400, I can rebuild the web dataTable view. Will this work?

    Daron
  • kennetheld
    kennetheld
    38 Posts

    Re: Passing a recordset from SQL Server to DB2/400

    ‏2014-04-11T20:58:10Z  
    • markevans
    • ‏2008-02-21T23:51:21Z
    Daron,

    You can read the records from SQL server (something like the GET array statement). Then you can use the sqllib.connect(connectionURL, userid, password) statement to connect to DB2 on the iSeries.

    If you want to switch back to SQL Server, then you would use Sqllib.disconnect() and then connect back to SQL Server (using the sqllib.connect).

    If this is a batch application, the one trick I found (just a week or two ago), is that when you generate you need to specify both JDBC drivers in the sqlJDBCDriverClass build descriptor option (the class for SQL server and db2 on iSeries). The class names would be separated with a comma. Also, of course, the jar files containing the classes needs to be in the Java Build Path of the project.

    If this is a web application, then the connectionURL would be the JNDI name that you have defined a datasource for. You can define both resource references in the web.xml and datasources in the EAR file/app server.

    hope this helps.

    Mark,

    I know I am replying to an OLD post but this is EXACTLY what I am trying to do now and hoping something has changed.  With 5 plus years gone is there a good way to connect to a MS SQL Server, create a result set to process through and dump those records to the iSeries as I loop through?  I have tried to put both classes on the SQL driver class line like you mentioned early in this feed but that does not work.  Any current day suggestions would be GREATLY appreciated.  Thanks.

  • markevans
    markevans
    2843 Posts

    Re: Passing a recordset from SQL Server to DB2/400

    ‏2014-04-14T19:50:16Z  

    Mark,

    I know I am replying to an OLD post but this is EXACTLY what I am trying to do now and hoping something has changed.  With 5 plus years gone is there a good way to connect to a MS SQL Server, create a result set to process through and dump those records to the iSeries as I loop through?  I have tried to put both classes on the SQL driver class line like you mentioned early in this feed but that does not work.  Any current day suggestions would be GREATLY appreciated.  Thanks.

    Hey,

    My understanding is that EGL only maintains one connection per rununit at a time.   So, this means you cannot have both a SQL Server and Jt400 connection open concurrently.  

    Maybe others have come up with some solution to have two rununits active.

    A couple of other possibilities:

    - Read all the rows from SQL record into a dynamic array

    - Switch to the iSeries connection

    - write all rows to iSeries DB

    or

    - Read all the rows from SQL record and write into temporary serial file

    - switch the connection

    - Read the serial file and write to iSeries DB

    or

    - Use MQ and read from SQL Server and write to MQ Queue

    - Have trigger on MQ Queue and when it gets data, start batch program which reads from Queue and writes to iSeries DB

     

    That is all I can think of for now.

     

    Mark

  • kennetheld
    kennetheld
    38 Posts

    Re: Passing a recordset from SQL Server to DB2/400

    ‏2014-04-15T14:20:12Z  
    • markevans
    • ‏2014-04-14T19:50:16Z

    Hey,

    My understanding is that EGL only maintains one connection per rununit at a time.   So, this means you cannot have both a SQL Server and Jt400 connection open concurrently.  

    Maybe others have come up with some solution to have two rununits active.

    A couple of other possibilities:

    - Read all the rows from SQL record into a dynamic array

    - Switch to the iSeries connection

    - write all rows to iSeries DB

    or

    - Read all the rows from SQL record and write into temporary serial file

    - switch the connection

    - Read the serial file and write to iSeries DB

    or

    - Use MQ and read from SQL Server and write to MQ Queue

    - Have trigger on MQ Queue and when it gets data, start batch program which reads from Queue and writes to iSeries DB

     

    That is all I can think of for now.

     

    Mark

    Mark,

     
    Thanks for your response.  However, for future reference you can have more than two db connections open and dynamically switch between them using setCurrentDatabase.  It seems for this to work properly you have to manually set things in Rununit and set the GenProperties to NO for the Build options. I have also determined if you manually enter you rununit parms you can also have genproperties of NO for reading and outputing to windows flatfiles.  Just thought you might be interested to know this and you would be able to pass along if other hit the same issues.  
     
    If you get a chance please let me know a quick and easy way I can either delete windows flatfiles from EGL or do a easy clear.  In most languages you can do a delete or open as OUTPUT which clears the file.  Thanks for your thoughts and help.
  • markevans
    markevans
    2843 Posts

    Re: Passing a recordset from SQL Server to DB2/400

    ‏2014-04-15T14:36:53Z  

    Mark,

     
    Thanks for your response.  However, for future reference you can have more than two db connections open and dynamically switch between them using setCurrentDatabase.  It seems for this to work properly you have to manually set things in Rununit and set the GenProperties to NO for the Build options. I have also determined if you manually enter you rununit parms you can also have genproperties of NO for reading and outputing to windows flatfiles.  Just thought you might be interested to know this and you would be able to pass along if other hit the same issues.  
     
    If you get a chance please let me know a quick and easy way I can either delete windows flatfiles from EGL or do a easy clear.  In most languages you can do a delete or open as OUTPUT which clears the file.  Thanks for your thoughts and help.

    Hey,

    Thanks for filling me in and the process for future reference.  This will teach me to answer without trying it first.

    On the serial file, look at the "REPLACE" property of the resource association entry.  Here is the text from the EGL Helps (Generation Guide):

    replace

    Specifies whether adding a record to the file replaces the file rather than appending to the file. This entry is used only in these cases:
    • You are generating Java code; and
    • The fileType of the file is seqws.
    Select one of these values:
    NO (the default)
    Append to the file.
    YES
    Replace the file. The first add statement for the serial file in the program, or the first add statement for the serial file after a close statement, adds the data to the beginning of the serial file, replacing all the previous contents.

    Hope that helps.

     

    Mark

  • kennetheld
    kennetheld
    38 Posts

    Re: Passing a recordset from SQL Server to DB2/400

    ‏2014-04-15T16:16:32Z  
    • markevans
    • ‏2014-04-15T14:36:53Z

    Hey,

    Thanks for filling me in and the process for future reference.  This will teach me to answer without trying it first.

    On the serial file, look at the "REPLACE" property of the resource association entry.  Here is the text from the EGL Helps (Generation Guide):

    replace

    Specifies whether adding a record to the file replaces the file rather than appending to the file. This entry is used only in these cases:
    • You are generating Java code; and
    • The fileType of the file is seqws.
    Select one of these values:
    NO (the default)
    Append to the file.
    YES
    Replace the file. The first add statement for the serial file in the program, or the first add statement for the serial file after a close statement, adds the data to the beginning of the serial file, replacing all the previous contents.

    Hope that helps.

     

    Mark

    Mark,

    Thanks for the quick reply.  Is there a way to read the  contents of a particular directory, whether that be the iSeries IFS or a windows environment?  I would like to look for files that begin with a certain string, process and then remove the files.  That also takes me back to the question of whether there is an EGL method to delete a file once processed.  Your response is greatly appreciated.  Thanks.

  • markevans
    markevans
    2843 Posts

    Re: Passing a recordset from SQL Server to DB2/400

    ‏2014-04-15T16:37:45Z  

    Mark,

    Thanks for the quick reply.  Is there a way to read the  contents of a particular directory, whether that be the iSeries IFS or a windows environment?  I would like to look for files that begin with a certain string, process and then remove the files.  That also takes me back to the question of whether there is an EGL method to delete a file once processed.  Your response is greatly appreciated.  Thanks.

    I am not aware of a way to inquire on a directory from within EGL or do general processing.   EGL works just on the associated file.

    To do this level of processing, you would need to call out to some system level code (either Java, CL, cmd files, etc). 

     

  • kennetheld
    kennetheld
    38 Posts

    Re: Passing a recordset from SQL Server to DB2/400

    ‏2014-04-17T14:37:31Z  
    • markevans
    • ‏2014-04-15T16:37:45Z

    I am not aware of a way to inquire on a directory from within EGL or do general processing.   EGL works just on the associated file.

    To do this level of processing, you would need to call out to some system level code (either Java, CL, cmd files, etc). 

     

    Mark,

    Let me start by saying I know I have committed a no no by asking another question and a different feed.  However, I went to the EGL page and could not find a link to start a new subject.  My question is, when using the Java External Type does the package definition start with the JAVA class path?  If not, where does it start.  If you use the older code of javalib.store and invoke where do the java classes have to be in order to be found?

    I am sure I am missing something simple in starting a new feed but did not see it.  Thanks for your help.