My db2 database is running on a server whose ssh password login is disabled and allows only private key ssh. I'm trying to import some data into a table in this database (the data is also sitting on the server). The ssh connection fails because of obvios reason which is it doesn't have the private key. How can I configure data studio so it ssh using the private key?
The Data Studio 4.1.3 is running on a windows machine and the database is running on a Linux box. I can SSH or SFTP to the Linux box using the private key and there is no issue.
Different options exist.
If you have a business need to ssh to the Db2-server and only public-private key authentication is supported, then you (or an account like the Db2-instance owner) need to have the relevant key file and passphrase to do your job.
If you already have the relevant key file(s), then please follow this link and all related linked pages for details of how to configure IBM Data Studio for remote operations. For Version 4.1.3 of Data-Studio, use menu Window > Preferences > General > Network Connections > SSH2. In this section you can add the private key, configure known hosts, generate keys etc, so if your target Db2-server already has a working sshd/sftpd, Data Studio will just work (it asks for the passphrase only when you utilize the ssh connection via some remote action). If the target Db2-server runs Microsoft-Windows, you can choose to ignore ssh and just DAS instead (if you previously created a DAS on your Db2-server and got it working, it's not default), or alternatively configure the target MS-Windows server with cygwin (as documented in the above link) to provide the sshd/sftpd although this is clunky and awkward. Additionally, if you already have a private-key and passphrase, why not simply use an ssh-client to ssh to the target Db2-server to do whatever is required. Equally if the target Db2-server runs MS-Windows, mstsc is available, if provisioned/enabled. A GUI is not essential for a Db2-import action.
If the file-to-import is already on the Db2-server, then you can use plain SQL to perform the IMPORT action from within Data Studio. The key detail is to use the SYSPROC.ADMIN_CMD stored procedure to perform the import, and to specify in its argument the fully qualified path of the import file. Refer to the Knowledge Center for your version of Db2 to find the details and examples, for example here. You will need additional code to verify the result of the import action.
Awkward workaround: Anyone with ssh/scp access to the Db2-server may also be able to scp/secure-ftp the file from the Db2-server to your workstation (or otherwise accessible shared location on the network) , thereby allowing you to perform the import from your workstation.