Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

SQOOP - IBM DB2 Connection

avatar
New Contributor

Hi,

 

Has any one imported data from IBM DB2 data base ( which is on ZOS ) using SQOOP ?

 

Thanks

 

5 REPLIES 5

avatar
Contributor

No experience with connecting hadoop with ZOS DB2, but I don't see why it won't work.  DB2 has a JDBC driver, right?  So the configuration would be the same for DB2 as for other databases.  

 

This IBM website has the details on what parameter values you need to connect to DB2:

http://pic.dhe.ibm.com/infocenter/bigins/v2r0/index.jsp?topic=%2Fcom.ibm.swg.im.infosphere.biginsigh...

avatar
Explorer

The command that is working for me:

sqoop import --driver com.ibm.db2.jcc.DB2Driver --connect jdbc:db2://IP:PORT/DSDB2D01 --username XXX -P --table "tablename"

 

But before that you have to copy the jar file to /var/lib/sqoop.

avatar
New Contributor

Just finished up testing import/export with DB2 for Z. First step is to copy jdbc driver into your sqoop/lib dir.  With a 10 system cluster running and having an index on column C1 I was able to use 9 maps for parrallelism.  Also the options below make sqoop use the generic jdbc driver, we aren't using the DB2 specifc connector which is for LUW only.  My import from DB2 Z into hadoop looked like:

 

sqoop import --driver com.ibm.db2.jcc.DB2Driver --connect jdbc:db2://192.168.1.1:59000/DB2LOC1 --username SYSADM --password password --table SYSADM.TB1  --split-by C1 --target-dir "/user/biadmin/1GBTBL" -m 9

 

 

avatar
Contributor

I am having a very similar issue that I need some help on.  When I try to ingest data from a DB2 z/OS system here is the output:

 

16/01/25 16:57:03 WARN sqoop.ConnFactory: Parameter --driver is set to an explicit driver however appropriate connection manager is not being set (via --connection-manager). Sqoop is going to fall back to org.apache.sqoop.manager.GenericJdbcManager. Please specify explicitly which connection manager should be used next time.
16/01/25 16:57:03 INFO manager.SqlManager: Using default fetchSize of 1000
16/01/25 16:57:03 INFO tool.CodeGenTool: Beginning code generation
16/01/25 16:57:04 ERROR manager.SqlManager: Error executing statement: com.ibm.db2.jcc.am.SqlSyntaxErrorException: [jcc][10165][10036][4.15.134] Invalid database URL syntax: jdbc:db2://<DB IP>:<DB Port>/<Location or SSID Name>/. ERRORCODE=-4461, SQLSTATE=42815
com.ibm.db2.jcc.am.SqlSyntaxErrorException: [jcc][10165][10036][4.15.134] Invalid database URL syntax: jdbc:db2://<DB IP>:<DB Port>/<Location or SSID Name>/. ERRORCODE=-4461, SQLSTATE=42815

 

If I do a "telnet <DB IP>  <DB Port>" command it comes back with a response so I know it is there.

 

I have put the "db2jcc4.jar" into the /var/lib/sqoop/  directory.

 

Please advise.

avatar
New Contributor

hi BrettM ,

Did you find a solution for this ?

I am trying to connect DB2 on Z/OS using Sqoop .