Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

db2 sqoop default schema issue

db2 sqoop default schema issue

New Contributor

Hello,

I want to use default schema in my sqoop connection. But getting below error message.

sqoop list-tables --driver com.ibm.db2.jcc.DB2Driver --connect jdbc:db2://xxx.xx.xx.xx:xxxx/xxxx:currentSchema=xxxx --username xxxx --password xxxx

Invalid database URL syntax: jdbc:db2://xxx.xx.xx.xx:xx/xx:currentSchema=xxx. ERRORCODE=-4461, SQLSTATE=42815 at com.ibm.db2.jcc.am.dd.a(dd.java:676) at com.ibm.db2.jcc.am.dd.a(dd.java:60) at com.ibm.db2.jcc.am.dd.a(dd.java:85) at com.ibm.db2.jcc.DB2Driver.tokenizeURLProperties(DB2Driver.java:899) at com.ibm.db2.jcc.DB2Driver.connect(DB2Driver.java:404) at java.sql.DriverManager.getConnection(DriverManager.java:571) at java.sql.DriverManager.getConnection(DriverManager.java:215) at org.apache.sqoop.manager.SqlManager.makeConnection(SqlManager.java:904) at org.apache.sqoop.manager.GenericJdbcManager.getConnection(GenericJdbcManager.java:52) at org.apache.sqoop.manager.SqlManager.listTables(SqlManager.java:539) at org.apache.sqoop.tool.ListTablesTool.run(ListTablesTool.java:49) at org.apache.sqoop.Sqoop.run(Sqoop.java:147) at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76) at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183) at org.apache.sqoop.Sqoop.runTool(Sqoop.java:225) at org.apache.sqoop.Sqoop.runTool(Sqoop.java:234) at org.apache.sqoop.Sqoop.main(Sqoop.java:243) Caused by: java.util.NoSuchElementException at java.util.StringTokenizer.nextToken(StringTokenizer.java:349) at java.util.StringTokenizer.nextToken(StringTokenizer.java:377) at com.ibm.db2.jcc.DB2Driver.tokenizeURLProperties(DB2Driver.java:887) ... 13 more Could not retrieve tables list from server ERROR tool.ListTablesTool: manager.listTables() returned null

10 REPLIES 10

Re: db2 sqoop default schema issue

Master Collaborator

Can you try putting a semicolon at the end?

jdbc:db2://xxx.xx.xx.xx:xxxx/xxxx:currentSchema=xxxx;

Re: db2 sqoop default schema issue

Master Collaborator

To import tables use import-all-tables, to exclude certain tables use --exclude-tables <comma separated table list>.

Re: db2 sqoop default schema issue

New Contributor

Hi Deepesh, yes I have added semicoln into the syntax. but its not importing all the tables with the given schema. Rather it's searching all the tables from the DB. i want tables only with the specific schema.

Re: db2 sqoop default schema issue

New Contributor

You need to use sqoop import-all-tables not sqoop list-tables (It will list all the tables in a given schema)

Re: db2 sqoop default schema issue

Expert Contributor

@dnyanesh kulkarnniyou can try by giving -- --schema option,

sqoop list-tables --driver com.ibm.db2.jcc.DB2Driver --connect jdbc:db2://<database_host>:<database_port>/database  --username XXXXX --password XXXXX -- --schema dbo

Re: db2 sqoop default schema issue

New Contributor
I used below command,
sqoop import-all-tables --driver com.ibm.db2.jcc.DB2Driver --connect jdbc:db2://<database_host>:<database_port>/database  --username XXXXX --password XXXXX --schema xxx 

but its giving below error
ERROR tool.BaseSqoopTool: Error parsing arguments for import-all-tables:
ERROR tool.BaseSqoopTool: Unrecognized argument: --schema
ERROR tool.BaseSqoopTool: Unrecognized argument: xxx

Re: db2 sqoop default schema issue

Expert Contributor

schema option should be preceed by double pair of quotes and space in between, i.e "-- --schema dbo"

Re: db2 sqoop default schema issue

New Contributor

I tried above command

sqoop import-all-tables --driver com.ibm.db2.jcc.DB2Driver --connect jdbc:db2://<database_host>:<database_port>/database  --username XXXXX --password XXXXX " -- --schema xxx" 

but its still giving the error

ERROR tool.BaseSqoopTool: Unrecognized argument: -- --schema xxx

Re: db2 sqoop default schema issue

New Contributor
Below command is able to coonect 


sqoop import-all-tables --driver com.ibm.db2.jcc.DB2Driver --connect jdbc:db2://<database_host>:<database_port>/database  --username XXXXX --password XXXXX -- --schema xxx


But when I tried its giving below error
ERROR tool.ImportAllTablesTool: Error during import: No primary key could be found for table ADVISE_INDEX. Please specify one with --split-by or perform a sequential import with '-m 1'.

So I applied,
sqoop import-all-tables --driver com.ibm.db2.jcc.DB2Driver --connect jdbc:db2://<database_host>:<database_port>/database  --username XXXXX --password XXXXX -- --schema xxx -m 1

but again its the same error i am getting
ERROR tool.ImportAllTablesTool: Error during import: No primary key could be found for table ADVISE_INDEX. Please specify one with --split-by or perform a sequential import with '-m 1'.




Don't have an account?
Coming from Hortonworks? Activate your account here