<jupyter_start><jupyter_text>IBM Cloud Pak for Data Data Virtualization Lab Core RESTful Service Base Classes  Where to find this sample onlineYou can find a copy of this notebook on GITHUB at https://github.com/Db2-DTE-POC/CPDDVLAB.  First we will import a few helper classesWe need to pull in a few standard Python libraries so that we can work with REST, JSON and a library called Pandas. Pandas lets us work with DataFrames, which are a very powerful way to work with tabular data in Python.<jupyter_code># Import the class libraries 
import requests
import ssl
import json
from pprint import pprint
from requests import Response
import pandas as pd
import time
from requests.packages.urllib3.exceptions import InsecureRequestWarning
requests.packages.urllib3.disable_warnings(InsecureRequestWarning)
from IPython.display import IFrame
from IPython.display import display, HTML
from pandas import json_normalize
%matplotlib inline
import matplotlib
import matplotlib.pyplot as plt<jupyter_output><empty_output><jupyter_text>The Db2 ClassNext we will create a Db2 helper class that will encapsulate the Rest API calls that we can use to directly access the Db2 Data Management Console service without having to use the user interface. To access the service we need to first authenticate with the service and create a reusable token that we can use for each call to the service. This ensures that we don't have to provide a userID and password each time we run a command. The token makes sure this is secure. Each request is constructed of several parts. First, the URL and the API identify how to connect to the service. Second the REST service request that identifies the request and the options. For example '/metrics/applications/connections/current/list'. And finally some complex requests also include a JSON payload. For example running SQL includes a JSON object that identifies the script, statement delimiters, the maximum number of rows in the results set as well as what do if a statement fails.<jupyter_code># Run the Db2 Class library
# Used to construct and reuse an Autentication Key
# Used to construct RESTAPI URLs and JSON payloads
class Db2():
    
    def __init__(self, url, verify = False, proxies=None, ):
        self.url = url
        self.proxies = proxies
        self.verify = verify

    def authenticate(self, api, userid, password):
        
        credentials = {'username':userid, 'password':password}
        r = requests.post(self.url+api+'/preauth/signin', verify=self.verify, json=credentials, proxies=self.proxies)
        if (r.status_code == 200):
            bearerToken = "Bearer " + r.cookies["ibm-private-cloud-session"]
            print('Token Retrieved')
            self.headers = {'Content-Type':"application/json", 'Accept':"application/json", 'Authorization': bearerToken, 'Cache-Control': "no-cache"}
        else:
            print ('Unable to authenticate, no bearer token obtained')
        
    def printResponse(self, r, code):
        if (r.status_code == code):
            pprint(r.json())
        else:
            print (r.status_code)
            print (r.content)
    
    def getRequest(self, api, json=None):
        return requests.get(self.url+api, verify = self.verify, headers=self.headers, proxies = self.proxies, json=json)

    def postRequest(self, api, json=None):
        return requests.post(self.url+api, verify = self.verify, headers=self.headers, proxies = self.proxies, json=json) 
    
    def deleteRequest(self, api, json=None):
        return requests.delete(self.url+api, verify = self.verify, headers=self.headers, proxies = self.proxies, json=json) 
        
    def getStatusCode(self, response):
        return (response.status_code)

    def getJSON(self, response):
        return (response.json())
    
    def getSchemas(self):
        return self.getRequest('/icp4data-databases/dv/cp4d/dbapi/v4/schemas')
           
    def getSearchViewList(self, searchtext, show_systems="false"):
        return self.getRequest('/icp4data-databases/dv/cp4d/dbapi/v4/admin/schemas/obj_type/view?search_name='+searchtext+'&show_systems='+str(show_systems)+'&rows_return=200');
    
    def getSearchTableList(self, searchtext):
        return self.getRequest('/icp4data-databases/dv/cp4d/dbapi/v4/admin/schemas/obj_type/table?search_name='+searchtext+'&show_systems=true&rows_return=100');
               
    def postSearchObjects(self, obj_type, search_text, rows_return=100, show_systems='false', is_ascend='true'):     
        json = {"search_name":search_text,"rows_return":rows_return,"show_systems":show_systems,"obj_type":obj_type,"filters_match":"ALL","filters":[]}       
        return self.postRequest('/icp4data-databases/dv/cp4d/dbapi/v4/admin/'+str(obj_type)+'s',json);
            
    def getTablesInSchema(self, schema):
        return self.getRequest('/icp4data-databases/dv/cp4d/dbapi/v4/schemas/'+str(schema)+'/tables'); 
    
    def getVirtualizedTables(self):
        return self.getRequest('/icp4data-databases/dv/cp4d/dvapiserver/v1/dv/mydata/tables')
    
    def getVirtualizedTablesDF(self):
        r = self.getVirtualizedTables()
        if (self.getStatusCode(r)==200):
            json = self.getJSON(r)
            df = pd.DataFrame(json_normalize(json['tables']))
            return df
        else:
            print(self.getStatusCode(r))

    def getVirtualizedViews(self):
        return self.getRequest('/icp4data-databases/dv/cp4d/dvapiserver/v1/dv/mydata/views')
    
    def getVirtualizedViewsDF(self):
        r = self.getVirtualizedViews()
        if (self.getStatusCode(r)==200):
            json = self.getJSON(r)
            df = pd.DataFrame(json_normalize(json['views']))
            return df
        else:
            print(self.getStatusCode(r))
    
    def grantPrivledgeToRole(self, objectName, objectSchema, roleToGrant):
        json =   {"objectName":objectName,"objectSchema":objectSchema,"roleToGrant":roleToGrant}
        return self.postRequest('/icp4data-databases/dv/cp4d/dvapiserver/v1/privileges/roles',json);
 
    def getRole(self, role):
        return self.getRequest('/icp4data-databases/dv/cp4d/dvapiserver/v1/privileges/objects/role/'+str(role));
    
    def foldData(self, sourceName, sourceTableDef, sources ):
        json = {"sourceName":sourceName,"sourceTableDef":sourceTableDef,"sources":sources}
        return self.postRequest('/icp4data-databases/dv/cp4d/dvapiserver/v1/dv/virtualize/tables', json);

    def addUser(self, username, displayName, email, user_roles, password):
        json = {"username":username,"displayName":displayName,"email":email,"user_roles":user_roles,"password":password}
        return self.postRequest('/api/v1/usermgmt/v1/user', json);
    
    def dropUser(self, username):
        return self.deleteRequest('/api/v1/usermgmt/v1/user/'+str(username));
   
    def getUsers(self):
        return self.getRequest('/api/v1/usermgmt/v1/usermgmt/users');
    
    def getUsersDF(self):
        r = self.getUsers()
        if (self.getStatusCode(r)==200):
            json = self.getJSON(r)
            df = pd.DataFrame(json_normalize(json))
            return df
        else:
            print(self.getStatusCode(r));
    
    def addUserToDV(self, display_name, role, usersDF):
        userrow = (usersDF.loc[usersDF['displayName'] == display_name])
        uid = userrow['uid'].values[0]
        username = userrow['username'].values[0]
        
        json = {"users":[{"uid":uid,"username":username,"display_name":display_name,"role":role}],"serviceInstanceID":"1610031237189593"}
        return self.postRequest('/zen-data/v2/serviceInstance/users', json);
    
    def dropUserFromDV(self, display_name, usersDF):
        userrow = (usersDF.loc[usersDF['displayName'] == display_name])
        uid = userrow['uid'].values[0]
        
        json = {"users":[uid],"serviceInstanceID":"1610031237189593"}
        return self.deleteRequest('/zen-data/v2/serviceInstance/users', json);
    
    def deleteVirtualizedTable(self, table_schema, table_name, data_source_table_name):
        payload = {"table_schema":table_schema,"table_name":table_name,"data_source_table_name":data_source_table_name}
        return self.deleteRequest('/icp4data-databases/dv/cp4d/dbapi/v4/dv/federation', payload);
    
    def deleteView(self, schema, view):
        return self.deleteRequest('/icp4data-databases/dv/cp4d/dbapi/v4/dv/federation/views/'+str(schema)+'/'+str(view))

    def getDataSourcesAPI(self):
        return self.getRequest('/icp4data-databases/dv/cp4d/dvapiserver/v1/dv/datasource_nodes')
    
    def getDataSources(self):
        columns = ['cid','connection_id', 'dbname', 'srchostname', 'srcport','srctype','status','usr','uri']
        dfTotal = pd.DataFrame(columns=columns)
        r = self.getDataSourcesAPI()
        if (self.getStatusCode(r)==200):
            json = self.getJSON(r)
            df = pd.DataFrame(json_normalize(json))
            for index, row in df.iterrows():
                if row['agent_class']!='F':
                    dfTotal = pd.concat([dfTotal, pd.DataFrame(json_normalize(row['dataSources']))],ignore_index=True)
            return(dfTotal[['srctype','srchostname', 'srcport', 'dbname', 'usr', 'status']])
        else:
            print(getStatusCode(r))<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.117325618772099, 'fraction_numerical': 0.012214721954355513, 'mean_word_length': 3.526673132880698, 'pattern_counts': {'":': 28, '<': 9, '<?xml version=': 0, '>': 9, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '154402', 'license': 'Apache-2.0', 'n_tokens_mistral': 2888, 'n_tokens_neox': 2798, 'n_words': 636, 'path': 'CPDDVRestClassV35.ipynb', 'repo_name': 'thsonvt/CPDDVLAB', 'size': '9333'}