db2 sqoop default schema issue

[#1]

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

[#2]

Can you try putting a semicolon at the end?

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

[#3]

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

[#4]

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.

[#5]

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

[#6]

@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

[#7]

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

[#8]

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

[#9]

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

[#10]

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

[#11]

Below command successfully ran 
sqoop import-all-tables  --driver com.ibm.db2.jcc.DB2Driver --num-mappers 1 --connect jdbc:db2://<database_host>:<database_port>/database  --username XXXXX --password XXXXX -- --schema xxx

But dont know why its accessing the different tables from other schemas?