SQL Magic in Notebooks in the IBM Data Science Experience

New Notebook in IBM Data Science Experience

At the recent IDUG DB2 Tech Conference in Brussels I gave a talk on using Jupyter Notebooks with IBM DB2 or dashDB. For the presentation I used a local installation of the notebooks and DB2 (never trust Internet connectivity). Part of the talk was about using SQL Magic in a notebook as simple interface to the database, e.g., for testing and prototyping.
After the conference I received a question about whether it is possible to use the SQL Magic with Jupyter Notebooks in the IBM Data Science Experience. The answer is yes and here is how.

Stuff - The Day of the BLOB and Object Storage

Regardless of whether it is turkey, cranberry sauce, stuffing, gravy, sweet potatoe pie, mashed potatoes or more that you eat, independent of whether it is a new iPhone, tablet, big screen, Bluetooth soundbar, household robot or other gadget on sale,  good to know that you can stuff almost anything into a DB2 BLOB or into the Bluemix Object Storage or Block Storage service.

In that sense "Happy Thanksgiving"! I am currently looking into the Content Delivery Network service to get my stuff faster to my folks. Talking about "stuff", enjoy this classic on "stuff" and "storage":

DB2/dashDB Security: Implicit Privileges Through Group Membership

DB2 Data Security

I recently saw an interesting DB2 question on Stack Overflow. Someone asked how it is possible to find out privileges for a user when the privileges were granted to a group the user is member of. DB2 does not manage group membership within the database, it is done in the operating system. But DB2 offers functions and views to retrieve that information and to simplify analysis of the security-related metadata. And remember that this applies to IBM dashDB as well.

To look up which groups a specific user belongs to, DB2 offers the table function AUTH_LIST_GROUPS_FOR_AUTHID. The returned groups are not necessarily used within the database and can be any operating system group. The following query returned several of those typical Linux groups:

SELECT * FROM TABLE (SYSPROC.AUTH_LIST_GROUPS_FOR_AUTHID('HLOESER')) as T

An administration view that comes handy is SYSIBMADM.AUTHORIZATIONIDS. It lists all authorization IDs along their respective type, i.e., groups, users and roles. When combined with another view, SYSIBMADM.PRIVILEGES, which lists all explicit privileges for all authorization IDs (that is for users, groups and roles), it allows to filter, e.g., for specific group or role privileges. Joining in the groups for a specific user and not forgetting to factor in PUBLIC privileges, I came up with the following query. It should list all the implicit privileges I have.

SELECT distinct p.AUTHID, p.PRIVILEGE, p.OBJECTNAME, p.OBJECTSCHEMA, p.OBJECTTYPE
FROM SYSIBMADM.PRIVILEGES P, SYSIBMADM.AUTHORIZATIONIDS A,
     TABLE (SYSPROC.AUTH_LIST_GROUPS_FOR_AUTHID('HLOESER')) as U 
WHERE p.privilege='CREATEIN' AND a.authidtype='G'
      AND a.authid=p.authid
      AND (u.group=a.authid or a.authid='PUBLIC')


If you want to know all your privileges, just UNION the result above with a query on SYSIBMADM.PRIVILEGES for your authid:

SELECT distinct p.AUTHID, p.PRIVILEGE, p.OBJECTNAME, p.OBJECTSCHEMA, p.OBJECTTYPE
FROM SYSIBMADM.PRIVILEGES P, SYSIBMADM.AUTHORIZATIONIDS A, TABLE (SYSPROC.AUTH_LIST_GROUPS_FOR_AUTHID('HLOESER')) as U 
WHERE p.privilege='CREATEIN' and a.authidtype='G' and a.authid=p.authid
AND (u.group=a.authid or a.authid='PUBLIC')
UNION
SELECT distinct p.AUTHID, p.PRIVILEGE, p.OBJECTNAME, p.OBJECTSCHEMA, p.OBJECTTYPE
FROM SYSIBMADM.PRIVILEGES P
WHERE p.authid='HLOESER'

IDUG EMEA: Henrik's Session Recommendations

Screenshot of Weather Graph

Next week will see the IDUG EMEA Tech Conference in Brussels, Belgium. I hope to meet many DB2 users again. Part of preparing for a conference is to read over the agenda and get a rough idea of which sessions to attend. Here are the sessions you will likely find me in the audience (or on stage), my focus is DB2 LUW.


Monday and Tuesday start off with keynotes in the morning. The DB2 conference closes with a keynote on Thursday. All are a must. For the rest of the time there are up to 6 tracks to choose from and here are some of the sessions I already picked and recommend:

• Monday, 10:40 to 11:40, C01 - DB2 LUW 11.1 : A Technical Overview with Matt Huras is kind a must for the DB2 LUW crowd to get all the insights into the current release.
• Monday, 12 to 13, D02 - To Cloud, or not To Cloud? Reasons to go "off premise" with DB2 with Steve Rees will probably see great discussions. This is a topic I am discussing with many customers.
• Tuesday, 14:30 to 15:30, F06 - R You Ready to be a Data Scientist? with Paul Turpin hopefully gives me more background in R and data analysis. That session also nicely plays with:
• Tuesday, 17 to 18, E08 - Interactive Reports and Presentations - Powered by DB2 and Jupyter Notebooks with Henrik Loeser. In that session I am going to show notebooks as interactive computational environments and using different APIs for DB2.
• Wednesday, 8:30 to 9:30, C09 - 11 Great DB2 Questions from Stack Overflow (Answers Included) with Henrik Loeser. The day starts early with this introduction into how to use the Q&A site Stack Overflow to solve your DB2 problems and to make utilize their "open data" knowledge base for deeper analysis.
• Wednesday, 9:40 to 10:40, C10 - OMG! Experience is a Hard Teacher – Lessons Learned #1with Melanie Stopfer. I plan to stay in the same room for an entertaining and experience-rich session with Melanie.
• Wednesday, 13:40-14:40, is the DB2 LUW Expert Panel, another must.
• Wednesday, 16:20 to 17:20, D13 - db2audit in a nutshell – advantages and challenges with Markus Fraune covers a topic many companies are looking into due to increased compliance requirements and rules & regulations.
• Thursday, 8:30 to 9:30, C15 - Exploiting Cloud Storage with DB2 for LUW with Phil Nelson is another early, but interesting session digging into new territory.

See you in Brussels next week!