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


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. 02.db2を選択.jpg

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 03.db2サービス作成.png

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. 04.db2サービス作成した後.png

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. 07.資格情報作成の後_copy.jpg

After creating the credentials, open the db2 console. Select "Manage" from the left menu and press the "Open Console" button. 08.コンソールを開く.jpg

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. 09.LoadData.jpg

This time, we will use the following csv file.


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. 10.FileUpload.jpg

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. 11.CreateTable.png

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

Click the "Begin Load" button at the bottom right. 14.ReviewSetting.jpg

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. 15.LoadDetail.jpg

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". 16.FindFunction.jpg

Click the "Start Creation" button. 17.BeginFunction.png

This time select "Action". 16-2.SelectFunction.jpg

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

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

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.


import sys
import ibm_db

def main(dict):
    #Assign the contents of the credentials to the ssldsn variable
    #DB connection
    db_conn = ibm_db.connect(ssldsn,"","")
    #SQL assembly
    db_stmt = ibm_db.prepare(db_conn,sql)
    number = 3
    #SQL execution
    row = ibm_db.fetch_tuple(db_stmt)
    #DB disconnection
    return {'name' : row[1] }

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

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. 20.ActionExec.jpg

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. 26.Runtime.jpg

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.


import sys
import ibm_db
import datetime

def main(dict):
    #Copy the contents of the credential information
    #DB connection
    db_conn = ibm_db.connect(ssldsn,"","")
    #SQL statement construction
    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")
    #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. 22.ActionInput.jpg

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. 23.OpenSQLConsole.jpg

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. 24.RunSQL.png

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.

Recommended Posts

[IBM Cloud] I tried to access the Db2 on Cloud table from Cloud Funtions (python)
I tried changing the python script from 2.7.11 to 3.6.0 on windows10
I tried to deliver mail from Node.js and Python using the mail delivery service (SendGrid) of IBM Cloud!
[Python] I tried to visualize the night on the Galactic Railroad with WordCloud!
I tried with the top 100 PyPI packages> I tried to graph the packages installed on Python
Try to poke DB on IBM i with python + JDBC using JayDeBeApi
[Python] I tried to get the type name as a string from the type function
I tried to graph the packages installed in Python
I tried Python on Mac for the first time.
I tried to implement Minesweeper on terminal with python
I tried to touch the CSV file with Python
I tried to solve the soma cube with python
I tried python on heroku for the first time
I wanted to use the Python library from MATLAB
From python to running instance on google cloud platform
[Python] I tried to graph the top 10 eyeshadow rankings
I tried to solve the problem with Python Vol.1
I tried to access Google Spread Sheets using Python
I tried to notify the honeypot report on LINE
I tried to summarize the string operations of Python
Access Oracle DB from Python
I tried to create API list.csv in Python from swagger.yaml
I tried using the Python library from Ruby with PyCall
I tried face recognition from the video (OpenCV: python version)
[Python] I tried to visualize the follow relationship of Twitter
I tried to implement the mail sending function in Python
I tried to enumerate the differences between java and python
I tried to launch ipython cluster to the minimum on AWS
I tried to divide the file into folders with Python
I tried to get various information from the codeforces API
I tried to output the access log to the server using Node.js
I tried to display the point cloud data DB of Shizuoka prefecture with Vue + Leaflet
I made a server with Python socket and ssl and tried to access it from a browser
I tried to summarize the languages that beginners should learn from now on by purpose
I tried to predict the genre of music from the song title on the Recurrent Neural Network
I tried AdaNet on table data
I tried to touch Python (installation)
How to access wikipedia from python
I tried to move the ball
I tried to estimate the interval.
Update Python on Mac from 2 to 3
I tried to solve the ant book beginner's edition with python
I tried Python! ] Can I post to Kaggle on my iPad Pro?
Post a message from IBM Cloud Functions to Slack in Python
I tried to digitize the stamp stamped on paper using OpenCV
I tried to register a station on the IoT platform "Rimotte"
I tried to get started with Bitcoin Systre on the weekend
[Python + heroku] From the state without Python to displaying something on heroku (Part 1)
I tried to summarize everyone's remarks on slack with wordcloud (Python)
I tried to cut out a still image from the video
[Python + heroku] From the state without Python to displaying something on heroku (Part 2)
How to update the python version of Cloud Shell on GCP
I tried to improve the efficiency of daily work with Python
I made something with python that NOW LOADING moves from left to right on the terminal
[Python] I tried to make a simple program that works on the command line using argparse.
About the error I encountered when trying to use Adafruit_DHT from Python on a Raspberry Pi
I tried to summarize Python exception handling
I tried to implement PLSA in Python
I was able to print the thermal printer "PAPERANG" from Python (Windows10, Python3.6)
I tried to refer to the fun rock-paper-scissors poi for beginners with Python
I tried to execute SQL from the local environment using Looker SDK