<jupyter_start><jupyter_text>Managing the Db2 Data Management ConsoleThis Jupyter Notebook contains examples of how to setup and manage the Db2 Data Management Console. It covers how to add additional users using database authentication, how to explore and manage connections and setup and manage monitoring profiles.The Db2 Data Management Console is more than a graphical user interface. It is a set of microservices that you can use to script your use of Db2.  Launching the Db2 ConsoleYou can launch the Db2 Console using the browser running in the Virtual desktop or from your own desktop. Inside the virtual machine desktop you can follow the link: http://localhost:11080.To use the Db2 Console from your own browser, look for the link in your welcome note. It will start with **services-uscentral.skytap.com**You can log into the console using:* userid: db2inst1* password: <PASSWORD>  Db2 Console Open APIThis Jupyter Notebook contains examples of how to use the Open APIs and the composable interface that are available in the Db2 Data Management Console. Everything in the User Interface is also available through an open and fully documented RESTful Services API. The full set of APIs are documented as part of the Db2 Data Management Console user interface. In this hands on lab you can connect to the documentation directly through this link: [Db2 Data Management Console RESTful APIs](http://localhost:11080/dbapi/api/index_enterprise.html).   Where to find this sample onlineYou can find a copy of this notebook at https://github.com/Db2-DTE-POC/db2dmc.  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.The full set of APIs are documents as part of the Db2 Data Management Console user interface. In this hands on lab you can connect to that directly through this link: [Db2 Data Management Console RESTful APIs](http://localhost:11080/dbapi/api/index_enterprise.html).<jupyter_code># Run the Db2 Class library
# Used to construct and reuse an Autentication Key
# Used to construct RESTAPI URLs and JSON payloads
class Db2Console():
    
    def __init__(self, url, verify = False, proxies=None, ):
        self.url = url
        self.proxies = proxies
        self.verify = verify

    def authenticate(self, userid, password, profile=""):
        credentials = {'userid':userid, 'password':password}
        r = requests.post(self.url+'/auth/tokens', verify=self.verify, json=credentials, proxies=self.proxies)
        if (r.status_code == 200):
            bearerToken = r.json()['token']
            if profile == "":
                self.headers = {'Authorization': 'Bearer'+ ' '+bearerToken}
                return True;
            else:
                self.headers = {'Authorization': 'Bearer'+ ' '+bearerToken, 'X-DB-Profile': profile}
                return True;
        else:
            print ('Unable to authenticate, no bearer token obtained')
            return False;
        
    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 putRequest(self, api, json=None):
        return requests.put(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 runSQL(self, script, limit=10, separator=';', stopOnError=False):
        sqlJob = {'commands': script, 'limit':limit, 'separator':separator, 'stop_on_error':str(stopOnError)}
        return self.postRequest('/sql_jobs',sqlJob)
        
    def getSQLJobResult(self, jobid):
        return self.getRequest('/sql_jobs/'+jobid)
    
    def getUserPriviledges(self, profile=''):
        if profile == '' :
            return self.getRequest('/userProfilePrivileges')
        else : 
            return self.getRequest('/userProfilePrivileges/'+profile)
    
    def assignUserPrivileges(self, profile, user):
        json = [{'profileName': profile, 'USER':[user], 'OWNER':[]}]
        return self.postRequest('/userProfilePrivileges?action=assign', json) 
 
    def assignOwnerPrivileges(self, profile, owner):
        json = [{'profileName': profile, 'USER':[], 'OWNER':[owner]}]
        return self.postRequest('/userProfilePrivileges?action=assign', json) 
    
    def revokeProfilePrivileges(self, profile, user):
        json = [{'profileName': profile, 'USER':[user]}]
        return self.postRequest('/userProfilePrivileges?action=revoke', json) 

    def getConnectionProfile(self,profile):
        return self.getRequest('/dbprofiles/'+profile)    
    
    def getMonitorStatus(self):
        return self.getRequest('/monitor') 
    
    def getConnectionProfiles(self):
        return self.getRequest('/dbprofiles')  
    
    def getConsoleRepository(self):
        return self.getRequest('/repository')    

    def getExportConnectionProfiles(self):
        return self.getRequest('/dbprofiles/transfer/export?exportCred=true')
    
    def postConnectionProfile(self, connectionName, dbName, port, host, userid, password, comment):
        json = {"name":connectionName,"location":"","databaseName":dbName,"dataServerType":"DB2LUW","port":port,"host":host,"URL":"jdbc:db2://"+host+":"+port+"/"+dbName+":retrieveMessagesFromServerOnGetMessage=true;","sslConnection":"false","disableDataCollection":"false","collectionCred":{"securityMechanism":"3","user":userid,"password":password},"operationCred":{"securityMechanism":"3","user":userid,"password":password,"saveOperationCred":"true"},"comment":comment}
        return self.postRequest('/dbprofiles', json)
    
    def putConnectionProfileUpdate(self, connectionName, dbName, port, host, userid, password, comment):
        json = {"name":connectionName,"location":"","databaseName":dbName,"dataServerType":"DB2LUW","port":port,"host":host,"URL":"jdbc:db2://"+host+":"+port+"/"+dbName+":retrieveMessagesFromServerOnGetMessage=true;","sslConnection":"false","disableDataCollection":"false","collectionCred":{"securityMechanism":"3","user":userid,"password":password},"operationCred":{"securityMechanism":"3","user":userid,"password":password,"saveOperationCred":"true"},"comment":comment}
        return self.putRequest('/dbprofiles/'+connectionName, json)
    
    def postTestConnection(self, dbName, port, host, userid, password):
        json = {"name":"","location":"","databaseName":dbName,"dataServerType":"DB2LUW","port":port,"host":host,"URL":"jdbc:db2://"+host+":"+port+"/"+dbName+":retrieveMessagesFromServerOnGetMessage=true;","sslConnection":"false","disableDataCollection":"false","operationCred":{"securityMechanism":"3","user":userid,"password":password}}
        return self.postRequest('/dbprofiles/testConnection', json)
    
    def deleteConnectionProfile(self, connectionName):
        return self.deleteRequest('/dbprofiles/'+connectionName)

    def getMonitoringProfiles(self):
        return self.getRequest('/monitorprofile/front')
    
    def getMonitoringProfile(self, profileID):
        return self.getRequest('/monitorprofile/front/'+profileID)
    
    def putMonitoringProfile(self, profileID, json):
        return self.putRequest('/monitorprofile/front/'+profileID, json)   
    
    def getProfileIndex(self, profileName):
        r = self.getMonitoringProfiles()
        if (self.getStatusCode(r)==200):
            json = self.getJSON(r) 
            profileList = pd.DataFrame(json_normalize(json['resources']))[['name','id']]
            display(profileList)
            profileList.set_index('name',inplace=True)
            try:
                profileIndex = profileList.loc[profileName][0]
            except KeyError:
                profileIndex = 0
                print(profileName + " not found")
            return profileIndex
        else:
            print(self.getStatusCode(r))<jupyter_output><empty_output><jupyter_text>Db2 Data Management Console ConnectionTo connect to the Db2 Data Management Console service you need to provide the URL, the service name (v4) and profile the console user name and password as well as the name of the connection profile used in the console to connect to the database you want to work with. For this lab we are assuming that the following values are used for the connection:* Userid: db2inst1* Password: <PASSWORD>* Connection: sample**Note:** If the Db2 Data Management Console has not completed initialization, the connection below will fail. Wait for a few moments and then try it again.<jupyter_code># Connect to the Db2 Data Management Console service
Console  = 'http://localhost:11080'
user     = 'peterconsole'
password = '<PASSWORD>'

# Set up the required connection
databaseAPI = Db2Console(Console+'/dbapi/v4')
if databaseAPI.authenticate(user, password) :
    print("Token Created")
else : 
    print("Token Creation Failed")
database = Console<jupyter_output><empty_output><jupyter_text>Confirm the connectionTo confirm that your connection is working you can the Console connection profiles.<jupyter_code># Get Console Connection Profiles
r = databaseAPI.getConnectionProfiles()
if (databaseAPI.getStatusCode(r)==200):
    json = databaseAPI.getJSON(r)
    display(pd.DataFrame(json_normalize(json)))
else:
    print(databaseAPI.getStatusCode(r))<jupyter_output><empty_output><jupyter_text>Get Repository ConfigurationYou can also get details on the repository configuration. You can see that we are using a local database name HISTORY to store all the monitoring data collected by the console.<jupyter_code># Get Console Repository Configuration
r = databaseAPI.getConsoleRepository()
if (databaseAPI.getStatusCode(r)==200):
    json = databaseAPI.getJSON(r) 
    display(pd.DataFrame(json_normalize(json)[['databaseName','status','host','port','collectionCred.user']]).transpose())
else:
    print(databaseAPI.getStatusCode(r))<jupyter_output><empty_output><jupyter_text>Running SQL RoutinesWe need to run some SQL scripts later in the lab. So run the next two cells. They define routines that run SQL scripts and display the results of those scripts. If you want to learn more about how these routines were developed, check out the [Analyzing SQL Workloads notebook](http://localhost:8888/notebooks/Db2_Data_Management_Console_SQL.ipynb).<jupyter_code>def runSQL(profile,user, password, sqlText):
    
    if databaseAPI.authenticate(user, password, profile) :

        runID = databaseAPI.getJSON(databaseAPI.runSQL(sqlText))['id'] 

        json = databaseAPI.getJSON(databaseAPI.getSQLJobResult(runID))
        while 'results' not in json :
                json = databaseAPI.getJSON(databaseAPI.getSQLJobResult(runID))
        fulljson = json

        while json['results'] != [] or (json['status'] != "completed" and json['status'] != "failed") :
            json = databaseAPI.getJSON(databaseAPI.getSQLJobResult(runID))
            while 'results' not in json :
                json = databaseAPI.getJSON(databaseAPI.getSQLJobResult(runID))
            for results in json['results'] :
                fulljson['results'].append(results)
            time.sleep(1) 
        return fulljson
    else :
        print('Could not authenticate')
print('runSQL routine defined')
def displayResults(json):

    for results in json['results']:
        print('Statement: '+str(results['index'])+': '+results['command'])
        if 'error' in results : 
            print(results['error'])
        elif 'rows' in results :
            df = pd.DataFrame(results['rows'],columns=results['columns'])
            print(df)
        else :
            print('No errors. Row Affected: '+str(results['rows_affected']))
        print()
print('displayResults routine defined')<jupyter_output><empty_output><jupyter_text>Accessing the Db2 Console through custom URLsThis lab provides direct links to specific pages in the Db2 Console to make it easy to navigate during the lab. Since you can use this lab from a browser running in the virtual desktop or from your own browser on your own desktop, we just need to save the location of the console you are using. By default it is setup to use links that will work from the virtual machine desktop. To run from your own browser on your own desktop, enter the port location of the Db2 Console provided in your welcome note and run the cell below.<jupyter_code>externalConsole = "http://services-uscentral.skytap.com:18995"
database = externalConsole<jupyter_output><empty_output><jupyter_text>Testing the Custom URLRun the cell below. It will generate a link to the console that works for your selected environment. Notice that it brings up the whole console including the full navigation menu. Right click on the link and select **Open Link in New Tab** or **Open Link in New Window**<jupyter_code>print(database+'/console')<jupyter_output><empty_output><jupyter_text>You can also navigate to a specific page in the console by specifying the full URL or the page. For example, run the following cell and click on the link to navigate to the page that displays the tables in the Sample database:<jupyter_code>print(database+'/console/#explore/table?profile=Sample')<jupyter_output><empty_output><jupyter_text>You can also choose to create a URL that just links a page without the full navigation menu. Simply add **?mode=compact** to the URL as an option. For example:<jupyter_code>print(database+'/console/?mode=compact#explore/table?profile=Sample')<jupyter_output><empty_output><jupyter_text>Setting up Repository Authentication  Reviewing current console authentication and switching to repository authentication Let's start by looking at the current authentication settings in the console. To see the authentication settings you can either:Use the full console to switch to Repository Database Authentication:1. Open the Console2. Click the gear icon at the top right of the page3. Select **Authentication Setting**Use the embedded page from the console to switch to Repostory Database Authentication. 1. Run the cell below. 2. Click the link in the cell.<jupyter_code>print(database+'/console/?mode=compact#settings/authentication')<jupyter_output><empty_output><jupyter_text>To support multiple users you need a way to authenticate those other users. There are two choices. 1. **Repository Database Authentication**. This delegates the authentication of console users to the Db2 repository database used by the console to store historical monitoring data. 2. **LDAP**. This delegates the authentication to an external LDAP service.In this example we use the Repository Database to authenticate console users. You associate console users and administrators to Db2 Authorities, Groups, UDFs or Role. In this lab we are going to setup the first option, Repository Database Authentication. Before switching to Repository Database Authentication lets create some new users in the operating system that Db2 can recognize.   Adding Operating System UsersThe repository database uses standard Db2 authentiation. The simplest way to set this up is to use the userid authentication that is built into the operating system that your Db2 database is running on. To add new users to the console you need to add new users to your operating system. In this hands-on lab you can create those new users through the Linux settings console. 1. Click **Applications** at the upper-left of the screen1. Click **Sundry** in the menu 1. Click **Users and Groups** in the menu1. Enter the db2inst1 password **<PASSWORD>**1. Click **Add User** at the upper-left of the page1. Type **peterconsole** into the **Username** field.1. Type **Peter** into the **Full Name** field1. Enter **DataConsole** into the **password fields**1. Click **OK**Repeat steps 5 to 10 for the following new users:1. **paulconsole** with full name **Paul**1. **maryconsole** with full name **Mary**  Creating Roles for AuthenticationNow that you have three new users we need to create two database roles to classify the access allowd for each id. Either an Admin or User role (CONSOLE_ADM, CONSOLE_USR).<jupyter_code>user = 'DB2INST1'
password = '<PASSWORD>'
profile = 'Repository'
    
script = 'CREATE ROLE CONSOLE_ADM; CREATE ROLE CONSOLE_USR'        
print('Adding Roles')
displayResults(runSQL(profile, user, password, script))
print('done')<jupyter_output><empty_output><jupyter_text>Adding Users to the Console using Repository AuthenticationNow that you have three new users lets add them grant them database authorities and CONSOLE roles. The following cell will grant a user to either an Admin or User role (CONSOLE_ADM, CONSOLE_USR).<jupyter_code>userList = {'newUser':['peterconsole', 'paulconsole', 'maryconsole'], 'Type':['Admin', 'User', 'User']} 
userListDF = pd.DataFrame(userList) 
display(userListDF)
  
user = 'db2inst1'
password = '<PASSWORD>'
profile = 'Repository'

for row in range(0, len(userListDF)):
    Type = userListDF['Type'].iloc[row]
    newUser = userListDF['newUser'].iloc[row]
    
    script = 'GRANT DBADM, CREATETAB, BINDADD, CONNECT, CREATE_NOT_FENCED, IMPLICIT_SCHEMA, LOAD ON DATABASE TO USER '+newUser+';'
    if userListDF['Type'].iloc[row] == 'Admin' :
        script = script + 'GRANT ROLE "CONSOLE_ADM" TO USER '+newUser+' WITH ADMIN OPTION;'
    else : 
        script = script + 'GRANT ROLE "CONSOLE_USR" TO USER '+newUser+';'        
    print('Adding User: '+newUser)
    displayResults(runSQL(profile, user, password, script))
print('done')<jupyter_output><empty_output><jupyter_text>Switch Authentication Method in the ConsoleNext that we can users that are assigned to the correct role, we can switch from using the single default user to using Repository Database authentication for multiple users. You can do this through the Db2 Data Management Console and navigating to the **Repository Setting** page or by running the next cell below. Pick one of the two methods below to get to the Repository Settings page. Then follow the 10 steps to change the authentication method.Use the full console to switch to Repository Database Authentication:1. Click the link http://localhost:11080/console2. Click the gear icon at the top right of the page3. Select **Authentication**Use the embedded page from the console to switch to Repostory Database Authentication1. Run the cell below<jupyter_code>URL = database+'/console/?mode=compact#settings/authentication'
print(URL)<jupyter_output><empty_output><jupyter_text>Now configure the console to use the Repository database to authenticate users. 1. Select **Repository** from the list of available Authentiation Types.2. Scroll down and select **Db2 Roles** from the list of User role matting methods3. Enter CONSOLE_ADM in the Admin roles field4. Enter CONSOLE_USR in the User roles field5. Click Next6. Enter **Peter** in the Test user ID field7. Enter **DataConsole** in the Test user password field8. Click **Test**. You should see confirmation that the test succeeded9. Select **Save** at the bottom right of the page. A confirmation dialog appears.10. Click **Yes**. You should now see that Repository authentication is set and enabled successfully  Test a New Console UserTo test the new user and authentication method you can log out and into the console and you can run the cell below. After signing in you run SQL for the first time. This prompts a new user to enter a userid and password to authenticate against the Db2 database. You can save this authentication pair if you want to run SQL through the service later.1. Open the console http://localhost:11080/console2. Click the user icon at the very top right of the screen3. Select **sign out** a confirmation dialog appears4. Select **Yes**5. Enter **Peter** in the user field6. Enter **<PASSWORD>Console** in the password field7. Click **History** Connection name in the Databases homepage8. Click the **Run SQL** icon at the left of the page9. Enter **select TABSCHEMA, TABNAME from syscat.tables**9. Click **Run All**9. Enter **Peter** and **DataConsole** in the userid and password field10. IMPORTANT - Click **Save Credentials to repository** 11. Click **Test Connection**12. Click **Save** This next cell runs a simple query through the console service using one of the new users. This only works if you chose to save Peter's credentials to the repository.<jupyter_code>sql = 'select TABSCHEMA, TABNAME from syscat.tables'
displayResults(runSQL('Repository', 'peterconsole', 'DataConsole', sql))<jupyter_output><empty_output><jupyter_text>Grant Authority to DBINST1 to Access the ConsoleWhen you added **Peter** to the CONSOLE_ADM role you also included the **WITH ADMIN OPTION** that means that **Peter** can now grant other users access to the **CONSOLE_ADM** group. Run the SQL in the next cell to grant access to DB2INST to the Console as an Administrator.<jupyter_code>sql = 'GRANT ROLE "CONSOLE_ADM" TO USER "DB2INST1"'
displayResults(runSQL('Repository', 'peterconsole', 'DataConsole', sql))<jupyter_output><empty_output><jupyter_text>Now lets try running the previous select statement using the **DB2INST1** user id.<jupyter_code>sql = 'select TABSCHEMA, TABNAME from syscat.tables'
displayResults(runSQL('Repository', 'db2inst1', 'db2inst1', sql))<jupyter_output><empty_output><jupyter_text>Manage Console User PriviledgesIn the console you can control which users can access specific database connections. These are divided into Owners, who can grant privleges, and users who can only use the connection.  Get User Connection Profile PrivilegesThe next cell lets you control exactly which users can access specific databases. You can also access this through the console **Users and Privileges** settings page in the main console.<jupyter_code>URL = database+'/console/?mode=compact#settings/users'
print(URL)<jupyter_output><empty_output><jupyter_text>If you have a lot to users and databases to manage it may be useful to script these changes. The next cell includes an example of how to retrieve the current privileges through the console APIs.<jupyter_code># Get User Connection Profile Privleges
r = databaseAPI.getUserPriviledges()
if (databaseAPI.getStatusCode(r)==200):
    json = databaseAPI.getJSON(r)
    display(pd.DataFrame(json_normalize(json)))
else:
    print(databaseAPI.getStatusCode(r))<jupyter_output><empty_output><jupyter_text>Change User Connection Profile PrivilegesIf you have a lot of changes to make at once, the following cells are examples of how to script changes to multiple user and database connection priviledges. The first two cells in this section define reusable routines that let you add and revoke privileges with a single call.<jupyter_code># Change User Connection Profile Privileges
def addProfilePrivileges(profile, name, userType):

    if userType == 'user':
        r = databaseAPI.assignUserPrivileges(profile, name)
    else:
        r = databaseAPI.assignOwnerPrivileges(profile, name)
    if (databaseAPI.getStatusCode(r)==201):
        print(name+' added to: '+profile+" as a new "+userType+".")
    else:
        print(databaseAPI.getStatusCode(r)) 
    
print('Created routine to add profile privileges')
# Revoke User Connection Profile Privileges
def revokeProfilePrivileges(profile, name) :

    r = databaseAPI.revokeProfilePrivileges(profile, name)

    if (databaseAPI.getStatusCode(r)==201):
        print(name+' privilege revoked from: '+profile)
    else:
        print(databaseAPI.getStatusCode(r)) 
    
print('Created routine to revoke profile privileges')
addProfilePrivileges('Repository', 'paulconsole', 'owner')
revokeProfilePrivileges('Repository', 'paulconsole')<jupyter_output><empty_output><jupyter_text>These next two examples, show how to process an entire list of changes through a simple loop. Both to grant and revoke user privileges.<jupyter_code>userList = {'userName':['peterconsole', 'paulconsole', 'maryconsole', 'peterconsole', 'paulconsole', 'maryconsole'], 'Profile':['Repository', 'Repository', 'Repository', 'Sample', 'Sample','Sample'],'Type':['owner', 'user', 'user', 'owner', 'user', 'user']} 
userListDF = pd.DataFrame(userList) 
display(userListDF)

for row in range(0, len(userListDF)):
    profile = userListDF['Profile'].iloc[row]
    userType = userListDF['Type'].iloc[row]
    name = userListDF['userName'].iloc[row]   
    addProfilePrivileges(profile, name, userType)
print('done')
userList = {'userName':['peterconsole', 'paulconsole', 'maryconsole', 'peterconsole', 'paulconsole', 'maryconsole'], 'Profile':['Repository', 'Repository', 'Repository', 'Sample', 'Sample','Sample']} 
userListDF = pd.DataFrame(userList) 
display(userListDF)

for row in range(0, len(userListDF)):
    profile = userListDF['Profile'].iloc[row]
    name = userListDF['userName'].iloc[row]   
    revokeProfilePrivileges(profile, name)
print('done')<jupyter_output><empty_output><jupyter_text>Managing Database Connection ProfilesTo monitor and manage more Db2 databases through the console, you need to add a new connection profile for each database. The connection profile includes information on how to connect to the database (host, port, and database name) as well as log in information for monitoring and management.   Adding Connection Profiles Let's start by checking the list of current database profiles available in the console. Run the next two cells. The first cell opens the Connection Profile setting page. You can access this through the full console (http://localhost:11080/console) by selecting **Connection Profile** from the settings menu at the top right of the screen. The second cell lists the connection information by accessing the console API.<jupyter_code>print(database+'/console/?mode=compact#connection/manager')
# Get Console Connection Profiles
r = databaseAPI.getConnectionProfiles()
if (databaseAPI.getStatusCode(r)==200):
    json = databaseAPI.getJSON(r)
    display(pd.DataFrame(json_normalize(json)))
else:
    print(databaseAPI.getStatusCode(r))<jupyter_output><empty_output><jupyter_text>Export Connection Profile ListTo copy information from one instance of the Db2 Data Management Console to another you can export the current connection profile by selecting the **up arrow** icon at the top right of the Connection Profile page. You can then import the same file back into another console to move connection information from on console to another. You can also export the connection profile list through the console API. The example cells below create a csv file with the full connection profile information. You can then read back the csv file to see the contents of the csv file.<jupyter_code># Export Console Connection Profiles
r = databaseAPI.getExportConnectionProfiles()

if (databaseAPI.getStatusCode(r)==200):
    with open('connectionProfile.csv','wb') as file:
        file.write(r.content)
        print('Created connectionProfile.csv')
else:
    print(databaseAPI.getStatusCode(r))  
    
# Import Console Connection Profile
df = pd.read_csv("connectionProfile.csv") 
df = df.drop(df.index[1]) #Drop the second row
df = df.iloc[:, :-1] # Drop the last column
display(df)<jupyter_output><empty_output><jupyter_text>Scroll to the right and find the he **collectionCred** column.This is an encrypted version of the userid and password used to connect to the database.Anytime that the Db2 Data Management Console stores a userid and password, it encrypts it using a routine included with the console. If you export and import a connection profile you can choose to export with our without credentials. If you choose to include the credentials you can use the same userid and passwords from the original connection profile list when you import into a new console.   Create a new Db2 DatabaseIn this next section you will create a new and add it to the Db2 Data Management Console.  To create a new Db2 database you need to access the Db2 Terminal:1. Click the **Terminal** icon at the bottom left of the screen2. Switch to the DB2INST1 USER by running the following command```su - db2inst1```3. Enter the password **<PASSWORD>**4. Create a database with the following command where **dbone** is the name of the new database```db2 create database dbone```  Create a new Database Connection Profile through the User InterfaceYou can add the database through the Connection Profile page or though the console API. Let's try with the console first. Either open the Connection Profile page through the full console (http://localhost:11080/console) and select **Connection Profile** from the settings menu at the top right of the screen, or run the cell below.<jupyter_code>print(database+'/console/?mode=compact#connection/manager/add')<jupyter_output><empty_output><jupyter_text>To create a new connection:1. Enter **databaseOne** in the **Connection name** field2. Enter **localhost** in the **Host** field3. Enter **50001** in the **Port** field4. Enter **dbone** in the **Database** field5. Enter **db2inst1** in the **User name** field6. Enter **db2inst1** in the **Password** field7. Select **Enable operation**8. Enter **db2inst1** in the **User name** field9. Enter **db2inst1** in the **Password** field10. Select **Save credentials to repository**11. Click **Save** Now, lets delete the connection you just created so we can recreate it using the console API.1. Click the checkbox beside **databaseone**2. Click **delete**3. Click **Yes** to confirm that you want to delete the databaseone connection. This does not drop the actual database.   List the current Connection ProfilesNow we can perform the same actions using the console API. Let's start by listing the current database connections. Run the next cell.<jupyter_code># Get Console Connection Profiles
r = databaseAPI.getConnectionProfiles()
if (databaseAPI.getStatusCode(r)==200):
    json = databaseAPI.getJSON(r)
    display(pd.DataFrame(json_normalize(json)))
else:
    print(databaseAPI.getStatusCode(r))<jupyter_output><empty_output><jupyter_text>Check Connection Information without Creating a new ProfileJust like the user interface has a **Test connection** button you can test that your userid and password connects to the database before you create a connection profile. The next cell will check that the following connection information is valid.<jupyter_code>dbName = 'dbone'
port = '50001'
host = 'localhost'
userid = 'db2inst1'
password = '<PASSWORD>'
r = databaseAPI.postTestConnection(dbName, port, host, userid, password)
if (databaseAPI.getStatusCode(r)==200):
    print(databaseAPI.getJSON(r))
else:
    print(databaseAPI.getStatusCode(r))<jupyter_output><empty_output><jupyter_text>Create a new Database Connection ProfileThe next cell adds a connection to the **dbone** database.<jupyter_code>connectionName = 'databaseOne'
dbName = 'dbone'
port = '50001'
host = 'localhost'
userid = 'db2inst1'
password = '<PASSWORD>'
comment = 'New connection profile test'
r = databaseAPI.postConnectionProfile(connectionName, dbName, port, host, userid, password, comment)
if (databaseAPI.getStatusCode(r)==201):
    print("Created connection profile "+connectionName)
else:
    print(databaseAPI.getStatusCode(r))<jupyter_output><empty_output><jupyter_text>List the current Connection ProfilesNext run the routine to list the connection profiles to confirm that the connection to dbone is now available.<jupyter_code># Get Console Connection Profiles
r = databaseAPI.getConnectionProfiles()
if (databaseAPI.getStatusCode(r)==200):
    json = databaseAPI.getJSON(r)
    display(pd.DataFrame(json_normalize(json)))
else:
    print(databaseAPI.getStatusCode(r))<jupyter_output><empty_output><jupyter_text>Update an Existing Database Connection ProfileYou can also update an exsiting connection profile. The next cell is an example of updating the comment field. You can use the same routine to update the userid and password, host or portnumber as well.<jupyter_code>connectionName = 'databaseOne'
dbName = 'dbone'
port = '50001'
host = 'localhost'
userid = 'db2inst1'
password = '<PASSWORD>'
comment = 'Updated Comment'
r = databaseAPI.putConnectionProfileUpdate(connectionName, dbName, port, host, userid, password, comment)
if (databaseAPI.getStatusCode(r)==200):
    print("Updated connection profile "+connectionName)
else:
    print(databaseAPI.getStatusCode(r))<jupyter_output><empty_output><jupyter_text>Check that the comment field has been updated with the new comment. Run the cell below and check the comment field near the bottom of the list.<jupyter_code># Check Connection Profile
connectionName = 'databaseOne'

r = databaseAPI.getConnectionProfile(connectionName)
if (databaseAPI.getStatusCode(r)==200):
    json = databaseAPI.getJSON(r)
    display(pd.DataFrame(json_normalize(json)).transpose())
else:
    print(databaseAPI.getStatusCode(r))<jupyter_output><empty_output><jupyter_text>Delete a connection profileFinally you can delete existing database connection profiles from the console using a single API call.<jupyter_code>connectionName = 'databaseOne'

r = databaseAPI.deleteConnectionProfile(connectionName)
if (databaseAPI.getStatusCode(r)==200):
    print("Deleted connection profile "+connectionName)
else:
    print(databaseAPI.getStatusCode(r))<jupyter_output><empty_output><jupyter_text>Manage Monitoring ProfilesEach monitored database is associated with a monitoring profile. The profile defines what kind of data is collected, when it is collected and how long it is retained in the repository database. The monitoring profile also configures which alerts are enabled.Multiple database can be associated with each profile. There is also one default profile that any new database connection profile is added to when it is created.   List Monitor ProfilesLet's start by listing the current monitoring profiles. You can access this through the full console (http://localhost:11080/console) by selecting **Monitoring Profile** from the settings menu at the top right of the screen. You can also go directly to the monitoring profiles page by running the next cell. 1. Click the elipses (...) in the **Actions** column at the right side of the list and click **Edit**2. Browse through the monitoring profile page to see the default settings for this database.3. Click **Cancel** to return to the monitoring profile list.<jupyter_code>print(database+'/console/?mode=compact#monitorprofile')<jupyter_output><empty_output><jupyter_text>Creating a New Monitoring ProfileUse the user interface to create a new profile. 1. Click **New profile** at the top right of the screen2. Enter **<NAME>** in the **Profile name (New)** field3. Click **Select database list**4. Click the checkbox beside **SAMPLE**5. Click **Save** to add asign the SAMPLE database to the new monitoring profile6. Click **Save** to create the new profile  Using the API to access Monitoring ProfilesYou can access the same information throug the console API. The next cell lists all the available monitoring profiles.<jupyter_code>r = databaseAPI.getMonitoringProfiles()
if (databaseAPI.getStatusCode(r)==200):
    json = databaseAPI.getJSON(r) 
    profileList = pd.DataFrame(json_normalize(json['resources']))
    display(profileList)
else:
    print(databaseAPI.getStatusCode(r))<jupyter_output><empty_output><jupyter_text>Get a Monitor ProfileTo get the details of a single monitoring profile, run the cell below. The complete JSON string that is returned can be long. So we only display some key information. However you can uncomment the line that prints the entire JSON string if you want to see everything.<jupyter_code>profileName = "Test"

# Find the identifier used to access this profile
profileID = str(databaseAPI.getProfileIndex(profileName))

# Retrieve the monitoring profile information for the identifier
r = databaseAPI.getMonitoringProfile(profileID)
if (databaseAPI.getStatusCode(r)==200):
    json = databaseAPI.getJSON(r) 
    # print(json)
    # Print out the base information of the monitoring profile
    print(json['base_info'])
    # Print the common monitoring settings
    print(json['monitor_config']['common_settings'])
else:
    print(databaseAPI.getStatusCode(r))<jupyter_output><empty_output><jupyter_text>Change a Monitoring ProfileNow that we have the complete monitoring profile as a JSON string, we can update the string and put it back to update the monitoring profile settings. In this example you change the database included in the profile and change the data collection period.<jupyter_code>profileName = "Test"

# Get the existing json for the monitoring profile
profileID = str(databaseAPI.getProfileIndex(profileName))
r = databaseAPI.getMonitoringProfile(profileID)
if (databaseAPI.getStatusCode(r)==200):
    json = databaseAPI.getJSON(r) 
else:
    print(databaseAPI.getStatusCode(r))    

# Change the json that describes the list of databases assigned to this monitoring profile
json['base_info']['assign_database_list'] = "HISTORY"
json['monitor_config']['common_settings']['collect_data_every'] = 10  

# Put the updated profile back
r = databaseAPI.putMonitoringProfile(profileID, json)
if (databaseAPI.getStatusCode(r)==200):
    print(profileName+' Monitoring Profile Updated')
    print(json['base_info'])
    print(json['monitor_config']['common_settings'])
else:
    print(databaseAPI.getStatusCode(r))<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09086996151509014, 'fraction_numerical': 0.008304638444399434, 'mean_word_length': 5.029155854068081, 'pattern_counts': {'":': 58, '<': 153, '<?xml version=': 0, '>': 153, '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': '35', 'dir': 'jupyter-structured-clean-dedup', 'id': '498926', 'license': 'Apache-2.0', 'n_tokens_mistral': 10717, 'n_tokens_neox': 10374, 'n_words': 4129, 'path': 'console/Db2_Data_Management_Console_Management.ipynb', 'repo_name': 'Db2-DTE-POC/modernization', 'size': '39479'}