In 1980 my uncle was in the army and a guy told him: “Study computer: that’s the future”.
Fresh out of the army he took a 6 month course in accounting and later found a job in bank.
After the first year some manager told him: “You are the younger here and you are smart, take these books and learn, we are going to use computers and only you can do this”. The books that landed on his desk were about COBOL, Assembly and PL/SQL. The only job my uncle did before that was farming.
In a matter of 2 years a young man from the north Italian country side passed from a millennial hunter-gatherers background to a COBOL, Assembly and PL/SQL programmer. IBM consultants told to my uncle: “This is the first DB2 installed in a bank in Italy.”
DB2 is the past
SQL Server is relatively new, it has been around for less than 20 years. But when you talk about Oracle or DB2 we are talking about a technology that has now 40 years. In IT that’s centuries of distance.
I’m not appealed about the ecosystem of tools around DB2. First of all there are not many of them, they are mostly scripts written in Java. And then there is the official GUI client: IBM Data Studio, an obese >2GB desktop application based on Eclipse that freeze twice a day. Not a joke.
DB2 on Docker is slow
I installed DB2 on Docker following this guide.
The command is one and it’s very easy:
docker run -itd --name db2 --restart unless-stopped -e DBNAME=testdb -v ~/:/database -e DB2INST1_PASSWORD=db2_password -e LICENSE=accept -p 50000:50000 --privileged=true ibmcom/db2
With that command you have created:
- Accepted user licence:
- Set ports to
50000inside the container, and mapped to port
50000outside of the container
- Starts the container in privileged mode
- Restart the container on Docker restart
And if you really want you can now login into it from the command line:
docker exec -it db2 bash -c "su - db2inst1"
And create a SAMPLE database with the command:
…the command took minutes to execute.
DB2 sample databases
Microsoft SQL Server provides you the notorious AdventureWorks (in the flavours: OLTP, Data Warehouse or Lightweight) or if you prefer you can download the StackOverflow database and there are many choices for that too.
But when it comes to DB2 IBM provides only the GSDB sample data and when you click on it the download link opens up the Edge browser on your desktop. You actually have to right click on the download link, copy the link (which is
ftp://ftp.software.ibm.com/software/data/sample/GSDB_DB2_LUW_ZOS_v2r3.zip), open a PowerShell prompt and execute:
Now open a WSL prompt and that will make the job done:
Now I see the importance of Linux on Windows.
IBM Data Studio… you again…
We now need to import the database to DB2.
If you try to import the Book Database the first
create.sql query gets executed in seconds.
But when you try to run the second one, the
data.sql, IBM Data Studio starts… and never ends. It usually freeze after a few minutes. I tried the same on my localhost, an Intel NUC8i7HVK, and the performances weren’t getting better. You basically spend your time watching the down-right corner…
It took 31 minutes to complete the
data.sql query which has 7524 lines that weighted 661 Kb. Then IBM Data Studio freeze again. Maybe freezing means “query completed” in IBM Data Studio, ¯\_(ツ)_/¯ ?
It goes by itself that you cannot use that on production.
But people say: “Use the command line…”
Sure, let’s do this.
- First you have to import the books database inside the Docker container
- Then change the privileges of the files through the CLI root interface
- Connect the the database through the
- Then execute
db2 -tvmf /mnt/books/data.sql
…and it’s true, the data import was executed in 1 minute!
Let’s try with the GSDB sample database: after 5 minutes it ended up with a long list of error like this:
LOADING TABLE GOSALESDW.XGOREV: SQL3508N Error in accessing a file or path of type "LOCATION FILE" during load or load query. Reason code: "1". Path: "/database/data/db2inst1/NODE0000/SQL00003/load/DB20000". Error loading data GOSALES SAMPLE DATABASE SETUP ERRORS: (Please check the log files for more details) Log directory: /mnt/GSDB_DB2_LUW_ZOS_v2r3/DB2/unix/../logs/ --------------------------------------------- Error loading data - see log file GOSALES_LOAD.LOG
No, I never went to see the logs and after 4 days I wanted to finish this blog post here.
God only knows what sort of error there is inside
This was just an experiment and I’m so glad I work with SQL Server and not DB2.
You end up loving Microsoft product because they help you cross the finish line. You don’t only work with SQL Server, as a SQL Server DBA or developer you work with graphical tolls like SSMS, SSIS, SSRS, Power BI, Visual Studio, Azure Data, Studio, you can change AD on Windows Server through a GUI, you can move to the cloud with a right click.
In DB2 I’ll rather drink boiling beer than going to investigate the file
nano is not even provided.
I have 2 blog post as draft about how to migrate DB2 to SQL Server using SSMA and how to query DB2 using Polybase.
Not sure if I’m going to write them… there are better things to blog about.