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

Highlighted

db2 sqoop default schema issue

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
Highlighted

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;
Highlighted

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>.

Highlighted

Re: db2 sqoop default schema issue

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.

Highlighted

Re: db2 sqoop default schema issue

Explorer

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

Highlighted

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
Highlighted

Re: db2 sqoop default schema issue

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

Highlighted

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"

Highlighted

Re: db2 sqoop default schema issue

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

Highlighted

Re: db2 sqoop default schema issue

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