Issue running db2.ipynb

[ecrooks]

I'm running anaconda3 (Python 3.7.1 (default, Dec 14 2018, 19:28:38) [GCC 7.3.0]) on ubuntu, and trying to run the db2 extensions, I get:

I can connect to db2 just fine from the standard sql magic, but I can't run the db2 extensions. I followed the installation instructions. Is there anything I'm missing here?

[pazamorta]

I am receiving the exact same issue with Python 3.7.3 with Jupyter Labs running on Mac OS 10.14.4. Has anybody been able to resolve this issue?

[jmuellerDO]

The same for me, any solutions for the db2.ipynb issue?

[baklarz]

I hope the latest version of db2.ipynb fixes this issue. Just working on adding new examples for Db2 11.5 which was just released as well as some small extensions to the command for dealing with JSON a little better (did I say I hate quotes in Db2....).

[sgonchigar]

How can the width of the graph plot be increased.

[baklarz]

The program uses the default values that Pandas dataframes uses. So under the covers, the code just calls the plot routine.

If you want to try changing the plot parameters, you can do the following: answer = %sql select workdept, count(*) from employees group by workdept answer.plot(kind='bar',figsize=(10,5))

So the answer variable is just a pandas dataframe that you can manipulate using all of the plot functions available with that package. The figsize parameter will make the plot larger.

[sgonchigar]

For IBM i, connection without prompt works fine using either %sql db2+ibm_db:..... or % sql connect to.... However, when trying to use the connection prompt to connect to IBM i, I get the following error. Enter the database connection details (Any empty value will cancel the connection) Enter the database name: as400 Enter the HOST IP address or symbolic name: as400 Enter the PORT number: 446 Enter Userid on the DB2 system: userid Password [password]: ········

NameError Traceback (most recent call last)

in ----> 1 get_ipython().run_line_magic('sql', 'connect ') ~\Anaconda\lib\site-packages\IPython\core\interactiveshell.py in run_line_magic(self, magic_name, line, _stack_depth) 2312 kwargs['local_ns'] = sys._getframe(stack_depth).f_locals 2313 with self.builtin_trap: -> 2314 result = fn(*args, **kwargs) 2315 return result 2316 in sql(self, line, cell, local_ns) ~\Anaconda\lib\site-packages\IPython\core\magic.py in (f, *a, **k) 185 # but it's overkill for just that one bit of state. 186 def magic_deco(arg): --> 187 call = lambda f, *a, **k: f(*a, **k) 188 189 if callable(arg): in sql(self, line, cell, local_ns) 57 58 if (sqlType == "CONNECT"): # A connect request ---> 59 parseConnect(SQL1) 60 return 61 elif (sqlType == "MACRO"): # Create a macro from the body in parseConnect(inSQL) 103 cnt = cnt + 1 104 --> 105 _ = db2_doConnect() in db2_doConnect() 8 if len(_settings["database"]) == 0: 9 connected_help() ---> 10 if (connected_prompt() == False): 11 print("Connection canceled.") 12 return False in connected_prompt() 18 19 _settings["database"] = _database.strip() ---> 20 _settings["hostname"] = _hostname.strip() 21 _settings["port"] = _port.strip() 22 _settings["uid"] = _uid.strip()

[baklarz]

I've posted an updated version of the db2.ipynb file for you to try. What was the trap message associated with the line _settings["hostname"] = .... Not sure it will solve this problem because I can't tell why it would fail on hostname! Anyway, I'm impressed that you managed to connect to a AS400 server since I have never tested against that platform. Hopefully we can figure out why this failed.

[sgonchigar]

Thank you. It works now with the additional prompt. Enter the database connection details (Any empty value will cancel the connection) Enter the database name: as400 Enter the HOST IP address or symbolic name: as400 Enter the PORT number: 446 Is this a secure (SSL) port (y or n)n Enter Userid on the DB2 system: user Password [password]: ········ Connection successful.

[sgonchigar]

Any possibility to add additional options or ability to do this?. Thank you! %sql set option commit = NONE, naming = SYS, datFmt = ISO, datSep = '-', dynUsrPrf = Owner dlyPrp = *YES ;

[baklarz]

I believe those are AS400 specific options, correct? For commits, I do have %sql AUTOCOMMIT OFF or ON but I am not aware how the other options would be used. The underlying driver (ibm_db) may allow some of these options. Is this something that is normally done on a connection string?

[sgonchigar]

that's correct. Below is the snapshot for ODBC. I am using DB2 Connect. Yet to find equivalent options in DB2 Connect.
Not sure if below link will help. https://github.com/ibmdb/python-ibmdb/issues/389#issuecomment-557719191