db2Dean a.k.a. Dean Compher a.k.a “The Dean of DB2” is here to answer your DB2 Questions !
|
Utah Events None currently Scheduled
Nevada Events
|
Just for Grins Watch:
Add your insights to the:
|
|
|
Online Events
Other Events None Currently Scheduled
|
Welcome to db2Dean’s web site. I’m Dean Compher an IBM IT Specialist who, along with my team, helps customers and prospective customers with DB2 on Linux, UNIX and Windows (LUW) technical questions and issues. As this page makes painfully clear, I am a DBA and not a web designer, but I would be happy to get your DB2 questions answered or talk to you about the great features of DB2 or IBM Integration Products. If you are looking at a new database solution or want to compare us to your existing database vendor, please do not hesitate to contact me about getting a presentation or just to ask questions. My e-mail address is dean@db2Dean.com
I am located in Utah and primarily serve DB2/LUW and dashDB customers in the Western US, but I can forward requests to my peers in other technology and geographic areas as well. My team also covers Big Data and Informix and products. There are questions that I get on a regular basis, and I will write articles relating to them here. I hope that you find them useful. I also welcome suggestions for future content. Click here for more information about me.
|
|
Dean Compher 29 September 2017
The new Db2 Developer Community edition with all of the advanced Db2 features like compression, DSM and BLU can be added to your workstation or small server more easily than using the Db2 installer. This even works on Mac so you can now have the full Db2 client and server on your Mac! The reason for this ease of install is because Db2 comes pre-installed in a Docker container, so you can get a fully functioning Db2 database without having to know about the install options. Don’t worry if you don’t already have Docker because the install process installs Docker if you don’t already have it. You can think of Docker containers kind of like virtual machine images, but this is not a perfect analogy and Docker is much lighter than running a virtualized image. You also get Data Server Manager (DSM) and optionally IBM Data Studio and they are installed in their own containers. In this article I’ll share my experience using it and provide some tips that make it easier to use. Please note that Irina Delidjakova patiently answered my many questions and provide a significant amount of information for this article.
Supported Operating Systems: · Mac OS: 10.12+ · Windows: Win 7 Pro, Win 10 Pro, Enterprise · Linux x68: SLES 12, Fedora 25, Ubuntu 16.04+
Doing the Install
The install process details are in the Getting Started Guide and I’m not going to repeat them here, but I will provide a few tips that I think are helpful. First, I recommend going to the Db2 Developer Community download site and getting the “IBM Db2 Developer Community Edition Get Started Guide” and reading it. You will need to enter your IBM ID and password to do this. Please note that even though these instructions say that Docker is a prerequisite, you don’t need to install Docker on Windows and Mac. The installer installs it for you and then proceeds to load the Db2 and related containers for you. You get the free Community Edition of Docker.
On one of the install panels you enter the user id’s and passwords for Db2 and DSM. Make sure to write down all of them! I didn’t do that and had to do extra work to find them!
A Word About Containers
The install will create one container for Db2, one for DSM and, if you chose to install it, one for Data Studio. In addition to providing the functionality of Db2, DSM and Data Studio, you also get a great Linux command line interface in the containers. So if you are on Windows, you get the ability to run awk, grep and other great UNIX/Linux commands on your PC, as well as writing Linux shell scripts. On each of these containers you also get one of your local machine directories mounted on a file system in the container so you can easily move files like Db2 scripts into and out of your container, except for Windows 7.
From a command line on the machine where you installed Db2 on Mac, Linux or Windows 10, you can now see what containers are running with this command:
docker ps
For Windows 7 machines run you must run the following command before running docker ps: @FOR /f "tokens=*" %i IN ('docker-machine env ibm-db2') DO @%i
This command must be run in any command prompt window before running other commands on Windows 7. This is because on Windows 7 you are using the Docker Toolbox, whereas on the other operating systems run Docker natively.
The Install is Done, Now What?
You will notice that you have a new application installed called the IBM Db2 Developer Community Edition. I created a shortcut to it on my desktop. Start it up and click the “hamburger” menu in the top left to see the facilities provided.
Db2 Command Line Interface
While I like doing many things through Data Studio, I still prefer to do some things through the command line interface. To use it, just execute the command provided ty the Open Db2 Terminal as described in the table above. For Mac you open a Terminal window and enter the following command that logs you in as the db2 instance owner. This is also the command used at the command line for Linux and Windows 10:
docker exec -it db2server bash -c "su - db2inst1"
For Windows 7, run this command instead: eval $(docker-machine env ibm-db2) && docker exec -it db2server bash -c "su - db2inst1"
If you prefer to open the container as root, then you can use the following command.
Mac, Linux Windows 10: docker exec -it db2server bash Windows 7: eval $(docker-machine env ibm-db2) && docker exec -it db2server
Once logged in as the instance owner you will be able to create a new Db2 database or catalog external databases and connect to them through this container. You can even schedule scripts to run because the container has crontab and most Linux commands like “db2”, “awk” and “grep”.
One of the reasons I like the command line is because I have a number of Db2 scripts and shell scripts for Db2 that I like to use. Further, it is pretty easy to share files and directories from your native OS directories to and from your container. The exception to this is Windows 7, but you can still pretty easily copy files into and out of the container running under it. For most supported operating systems, there is a directory that is shared between the container and your OS. Any files or directories you add to the shared directory on your OS becomes available in the container and anything you put in the directory in the container becomes available to your OS. Here’s an example of how this might be used:
1. On your native operating system, copy an export script from an existing directory on your laptop to the shared directory that exports a Db2 table in delimited format. 2. Start the Db2 container command line interface a. Catalog the remote database and connect to it. b. cd to the shared directory. c. Run the export from step 1, putting the exported data file in the current directory. 3. After the export completes, go back to your native OS start Excel or another spread sheet, navigate to the shared directory and open the delimited file.
From within the Db2 container you will find the shared file system in this directory:
/db2fs
Just “cd” to that directory within the container and you can see anything put in in from the OS side. This directory also houses the database containers. The directory name on the OS side will vary by OS:
Mac /Users/<your username>/Library/Application Support/ibm-db2-developerc/DB2/db2fs
Windows 10 ~\AppData\Roaming\ibm-db2-developerc\DB2\db2fs
Linux <user_home_dir>/.config/ibm-db2-developerc/DB2/db2fs
For Windows 7 you will need to explicitly copy files using the Windows command line using Docker commands. Here is an example of copying a script from the “Documents” directory on Windows 7 to the Db2 container.
docker-machine scp ~\Documents\myscript.sql ibm-db2:/db2fs/myscript.sql
Here’s the documentation link for the docker-machine scp command.
There is no database in this set of containers for the DSM Repository, so if you want to keep historical measurements of your databases in DSM, then you will need to create one. I suggest creating it in this Db2 container. An easy way to create it is to log into the Db2 container as the Db2 instance owner as shown above. Then execute this command in your container command line to create a database called dsmdb.
db2 create db dsmdb pagesize 8 k
Data Server Manager
To administer and use DSM, open the “IBM Db2 Developer Community Edition” application and choose the Data Server Manager menu item and then log in.
If you prefer using your browser, just get connection information from the Connection Details menu choice, enter the address in your browser and bookmark the entry. From then on you can just access DSM from your browser. If you later find that your bookmark does not work, it may be that your workstation IP Address is dynamic (DHCP) and has changed. Check the Connections Details page again to verify this and change the address in your browser if needed.
For any databases you have anywhere that are licensed to do so, you can use this copy of DSM do DSM Enterprise Edition monitoring and to keep historical measurements in the repository. You just need to configure this copy of DSM to point to a database to be used for the repository. One good choice is the dsmdb database I discussed earlier. Like the IP address of the DSM container the database container address may also change with your dynamic IP, so when configuring DSM to point to your DB2 container use the “Internal container IP Address” as the hostname of the database connection.
If you need to, you can log into the container hosting the DSM server with this command:
docker exec -it dsm bash
For example you can look at your dsm properties file by going to the “/opt/ibm-datasrvrmgr/Config” directory and browsing the “dswebserver.properties” file. In it you can see things like the admin_user. That value will also be in the Connection Details of the application.
If you need to copy files between the DSM container and the OS, you can use the shared folder like I described in the Db2 Command Line Interface section above. As a matter of fact, it uses the same directories, so anything you share in that directory is shared between this container, the Db2 Container and the OS directory. Within the DSM container the shared directory is /db2fs and the OS directories are the same as describe above.
Data Studio
To use Data Studio, open the “IBM Db2 Developer Community Edition” application and choose the Data Studio menu.
This presents Data Studio in a VNC viewer on a Linux GUI. With a bit of experimentation, I was able to resize Data Studio window so that I could see the whole window without scrolling. After connecting to the SAMPLE database if you allowed the install to create it, the next thing you probably want to do is to create connections to other databases. If you ever find that you are unable to enter keystrokes into a window like the connections properties window scroll all the way to the top of the VNC viewer and click the “Send CtrlAltDel” button in the upper right.
If you already have data studio like I did, you may want to import your projects and database connections into this Data Studio. To do this, export your projects into a directory and your database connections into a file from your current Data Studio using the application’s export commands. The Data Studio container has a shared directory like the other containers, but uses a different directory on your OS. From within the container the directory appears as:
/headless/IBM/rationalsdp/workspace
From your OS, this directory will appear as one of the following:
Mac /Users/<your username>/Library/Application Support/ibm-db2-developerc/DataStudio/RemoteSystemsTempFiles
Windows 10 ~\AppData\Roaming\ibm-db2-developerc\DataStudio\RemoteSystemsTempFiles Linux <user_home_dir>/.config/ibm-db2-developerc/DataStudio/RemoteSystemsTempFiles
For Windows 7 use the “docker-machine scp” command to copy files to and from the container as shown earlier in this article.
Copy the exported Data Studio directories into the appropriate shared directory. Note that there will be at least one hidden directory that has a period for the first character of the directory name. Then from the new Data Studio window that is open under VNC, run the import wizards using the exported directories by navigating to the shared folder.
While in the VNC viewer if you get stuck and in a window and there is no “Send CtrlAltDelete” button, try pressing ctrl F3. This worked for me a few times to get the VNC buttons back.
If you need to, you can log into the container hosting the Data Studio with this command:
docker exec -it datastudio bash
***
I’m sure that there are quite a few other interesting things that you can do with these containers. If you find any or have any other useful tips, please share them on my Facebook Page or my db2Dean and Friends Community along with any other comments you may have.
***
George Baklarz provided this additional information: If you already have Db2 on your Windows 7 system, then Db2 Community Edition will not install because it wants exclusive use of port 50000. So you would have to change the current port that Db2 uses on your laptop. So if you have DSM, DS, or Db2 installed locally, the docker install will fail.
If you want to try out the new Db2 features by using Jupyter notebooks, you can create a new docker image using this command from the Docker CLI in Kitematic:
docker build -t db2jupyter http://ibm.biz/db2jupyter
This will load a container with db2 extensions and notebooks that you can use to play with the shiny new Db2 you have running in a container.
|
Db2Dean’s Other Interests
Mary Lou’s Stained Glass
|
