Support Questions
Find answers, ask questions, and share your expertise

Is there a way to connect DB2 (on zOS) using Sqoop to load into AWS S3 without using JDBC connector (i.e. can another connector be used)?

 
4 REPLIES 4

New Contributor

Terri, I want to make sure I understand your situation. Are you running Sqoop on AWS, needing to copy data from an on-prem DB2 on zOS system into S3?

Yes that is correct. We need to run Sqoop on AWS and copy data from an on-prem DB2 on zOS system into S3. We are just wondering if a JDBC connector has to be used or to find out what other connectors could be used to do this.

New Contributor

Being upfront - I'm with a vendor, Progress, who makes connectivity solutions for this kind of situation.

Not sure of alternatives other than the JDBC connector if you want to use Sqoop. Here's a blog by one of my coworkers describing how to solve a virtually identical problem. He details how to use our AWS-resident SaaS offering, DataDirect Cloud, and its associated On-Premise Connector (OPC), to use AWS Data Pipeline and JDBC to extract data from an on-premises DB2 system. The OPC opens a secure tunnel to the service so that AWS services can communicate with services inside a corporate firewall. Our service supports DB2 on LUW, z, or i, and many other data sources.

Here's a rough diagram of this scenario.

13041-sqoop.jpg

An alternative that bypasses Sqoop but still ends up with the data in S3 is something I've cobbled together using a great open-source (and FREE!) utility called SQL Workbench in conjunction with a neat retail utility that runs on Windows and Macs from ExpanDrive which allows you to map your S3 (and other) storage services to your local desktop without actually consuming local storage space. This also requires proper privileges/credentials to logon to your DB2 system.

SQL Workbench can be used in GUI mode or via the command-line, including in scripts. In GUI mode, I defined a connection to our DB2 system from a laptop sitting inside our firewall.

Sample script:

WbExport -file="/Volumes/Amazon S3/<your bucket>/Theatres.csv" -delimiter=","; select * from THEATRES;

This sample script says I want to build a comma-delimited output from the results of this SQL statement. You can also specify whole tables without writing Select statements. More on the options for using WbExport are available in their docs here.

The script tells SQL Workbench to export that data to a file called "Theatres.csv" and store it to my file system. But using ExpanDrive, my file system thinks it has an external drive, but it is actually a virtual drive that is mapped to my S3 storage. So SQL Workbench is effectively outputting directly to S3. No changes required to the S3 environment; you just need proper credentials.

I've just started dabbling with SQL Workbench, but I'm pretty impressed with what I've seen so far.

13042-expandrive.jpg

Apologies for the length. Hope this adheres to guidelines.

Thank you very much, the information is greatly appreciated!!!