[IBM Cloud] I tried to access the Db2 on Cloud table from Cloud Funtions (python)

Introduction

Speaking of cloud services, there was an image like AWS, but IBM also provides services as "IBM Cloud". When I looked it up, many services were provided to IBM Cloud, and some services were available for free. In particular, I think it is quite large that a typical IBM RDBMS "DB2" can be used for free if it is 200MB or less (as of 2020/07). If you do various things individually, 200MB will be enough. Similarly, in cooperation with "IBM Cloud Funtions" which can be used for free if the number of requests is less than a certain amount, I tried to describe until it becomes possible to browse / update the table on Db2 on Cloud.

What i did

① Try to create a DB2 service

It starts after creating an IBM Cloud account. IBM Cloud login screen (https://cloud.ibm.com/login)

Type db2 in the search box to open the db2 service screen.

Select London or Dallas as the region and Lite as the rate plan. Please note that there is no Lite plan in regions other than these two. The create button at the bottom right will create the db2 service

The db2 service has been created. Before making a table or something, create the credentials. You will need it when connecting to the database from Cloud Function. Open "Service Credentials" from the menu on the left.

Immediately after creating the db2 service, I think there are no credentials. ① Click the "Create Credentials" button. You will be asked to enter a name, so enter something. ② Credentials will be created, so press the part like "V" on the left side of the name to open the information. ③ Make a note of the part starting with "ssldsn" as it is required for connection.

After creating the credentials, open the db2 console. Select "Manage" from the left menu and press the "Open Console" button.

Now that the db2 service has been created, create a table. This time, we will create a table by reading the csv file prepared in advance. Click the green three-line icon in the upper left and open it in the order of LOAD → Load Data.

This time, we will use the following csv file.

table_test.csv

NUMBER,NAME,UPDATE_TIME
1,Taro,2020-01-01 18:00:00
2,Hanako,2020-02-03 21:00:00
3,Mike,2020-02-14 14:30:00

Drag and drop the target csv file to "File Selection" near the center. After uploading the file, click the "Next" button at the bottom right to proceed.

The screen for selecting the schema / table will be displayed. This time, we will create a new table with the existing schema. For Schema, apart from the schema with systematic names such as "AUDIT" and "DB2INST1", I think there is a schema with 3 English letters + 5 digits, so select that. For the next Table, click "New Table" in the upper right. In Create a New Table, enter the table name and click the "Create" button. Finally, press the "Next" button at the bottom right to go to the next screen.

It will be a screen to set the column name etc. This time, just click the "Next" button at the bottom right to proceed.

Click the "Begin Load" button at the bottom right.

Wait a moment for the load to take place. After loading is complete, you can check the loaded table with the "View Table" button. It seems that you can see the percentage of records that succeeded / failed to load in the pie chart.

This completes the setup / table creation on the Db2 side.

Let's create a Function to SELECT

Next, prepare Cloud Functions (actions) to run python. Go back to the IBM Cloud home page and type function in the search box to open "Functions".

Click the "Start Creation" button.

This time select "Action".

Enter the action name. Since we are coding in python this time, select "Python 3.7" as the runtime and press the "Create" button.

It became a coding screen. It seems that the process described in the main function prepared by default is executed by invoking the action.

In the main function, write the process with python. This time, I made the code below to get a specific row from the test table. At a minimum, only the processing to operate is described, so when describing the processing to be actually operated, it is better to have error processing and processing status log output.

select_test

import sys
import ibm_db

def main(dict):
    
    #Assign the contents of the credentials to the ssldsn variable
    # "ssldsn":"DATABASE=XXXXX;HOSTNAME=XXXXX;PORT=XXXXX;PROTOCOL=TCPIP;UID=XXXXX;PWD=XXXXX;Security=SSL;"
    ssldsn = "DATABASE=XXXXX;HOSTNAME=XXXXX;PORT=XXXXX;PROTOCOL=TCPIP;UID=XXXXX;PWD=XXXXX;Security=SSL;"
    
    #DB connection
    db_conn = ibm_db.connect(ssldsn,"","")
    
    #SQL assembly
    sql = "SELECT * FROM TEST_TABLE WHERE NUMBER = ?"
    db_stmt = ibm_db.prepare(db_conn,sql)
    number = 3
    ibm_db.bind_param(db_stmt,1,number)
    
    #SQL execution
    ibm_db.execute(db_stmt)
    row = ibm_db.fetch_tuple(db_stmt)
    
    #DB disconnection
    ibm_db.close(db_conn)
    
    return {'name' : row[1] }

After coding, save it with the "Save" button in the upper right.

Once saved, a "Start" button will appear in the same location. When you press it, the written code will be executed and the processing result will be displayed.

It's a good idea to keep in mind when running Functions, which is calculated by multiplying (memory size used) x (cumulative seconds used) (as of 2020/07, 400,000 GB can be used as a free tier. ). Therefore, it is better to keep the memory size small when using it for trial purposes. You can change the memory size used from "Runtime" in the menu on the left.

Try to create a Function (with arguments) to INSERT

Next, let's create Functions that add rows to the test table. At that time, try to get the value of the "NAME" column from the argument at the time of executing the action. Also, make sure that the "UPDATE_TIME" column contains the time when the action was executed. Follow the same steps as in the previous section to create a new action in Funts. This time I named it "insert_test". The code content is as follows.

insert_test

import sys
import ibm_db
import datetime

def main(dict):
    #Copy the contents of the credential information
    ssldsn = "DATABASE=XXXXX;HOSTNAME=XXXXX;PORT=XXXXX;PROTOCOL=TCPIP;UID=XXXXX;PWD=XXXXX;Security=SSL;"    
    
    #DB connection
    db_conn = ibm_db.connect(ssldsn,"","")
    
    #SQL statement construction
    sql = "INSERT INTO TEST_TABLE VALUES(?,?,?)"
    db_stmt = ibm_db.prepare(db_conn,sql)
    
    id = 5
    name = dict['name']
    update_time = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    
    ibm_db.bind_param(db_stmt,1,id)
    ibm_db.bind_param(db_stmt,2,name)
    ibm_db.bind_param(db_stmt,3,update_time)
    
    #Execution of SQL statement
    rtn = ibm_db.execute(db_stmt)
    
    #DB disconnection
    rc = ibm_db.close(db_conn)
    
    return { 'return-code': rtn }

To take an action with arguments, click the "Launch with Parameters" button to the left of the "Launch" button.

The following pop-up will be displayed, so enter the argument information in json format. The described contents are stored in the argument dict of the main function. This time, I want to get the value of'Jiro'from the dict with the key'name', so { 'name' : 'Jiro'} And press the "Apply" button.

Then press the "Launch" button to perform the action.

Make sure the rows are added correctly to the test table. Open the Db2 console and click the green three-line icon in the upper left to open RUN SQL.

A console for writing SQL opens. Write SQL here and press "Run all" at the bottom left to execute the SQL statement. This time I want to check the contents of the test table, so SELECT * FROM TEST_TABLE; When I executed the SQL of, I was able to confirm that the line with the Name'Jiro' was added.

At the end

I've just started IBM Cloud and it seems that I can make something that works like this even with a Lite account. I think it is perfect for the experience of serverless processing in a cloud environment. In the future, I would like to extend it so that actions can be executed by triggers (time arrival / file upload) and processing results can be passed to another service.