I recently had the challenge of connecting to an IBM DB2 database via Unity. After some struggles with setting up DB2 and getting the user access all correct, it ended up being fairly easy. This quick tutorial will show you how to do it and point out some of the pitfalls you might encounter along the way.
Step 1 – Set up IBM DB2
The installation isn’t difficult, but the installer UI looks like something out of the 90s for Windows 3.1. Basically, just follow the prompts for the typical installation. You’ll be asked to create a user and password. This actually creates a new user on the Windows OS. It wasn’t clear that would happen at the time of installation and I probably could have gone with my standard Windows user credentials. This is an important factor to note. After setup this account that you choose is the one with default rights to create the database.
So after lots of digging around, I found out this was a problem I had and was slowing me down. Eventually, I found out that the Windows user you’re logged in as has to belong to a user group that DB2 set up. It sets up two user groups: DB2ADMNS and DB2USERS. I made it so that my main Windows account belonged to both groups. This can be done via lusrmgr.msc. Right click on the Start Menu, select Run and type in lusrmgr.msc and you’ll get the Local Users and Groups Manager window. In here you can select your username, right click to get to properties, then in the Member Of tab you can add the user to the user group. I think that only DB2ADMNS group is required, but I did both.
Once you get that all corrected you may also have to make some adjustments to DB2’s configuration. This is done via the newly installed DB2 Command Window. I always run the Administrator version so that I’m sure I have all the access I need. Once you launch this you can run the command db2 get dbm cfg. This will print out the config details in the terminal window. If this doesn’t work right away then try db2start, which starts the DB2 service. Now you’ll want to change a few of these settings. specifically: SYSADM_GROUP, SYSCTRL_GROUP, SYS_MAINT_GROUP, and SYSMON_GROUP. You’ll want all of these to be set to DB2ADMNS which is the user group in Windows that has full access to modify the database. Use the following command to set these:
db2 update dbm cfg using SYSADM_GROUP DB2ADMNS
Do this for each setting.
Now things should be pretty happy. You can try creating a new database with:
db2 create database YOURDATABASENAME AUTOMATIC STORAGE YES
If all is set up correctly then you should get a success message.
I don’t overly enjoy working via the command line terminal so I went and downloaded IBM Data Studio. It was a pretty simple setup and I just went with the defaults. From there you can create your databases, tables, import data and run queries. One major annoyance I found with this is that when you type a lowercase name for a table or column name this program will surround it with double quotes. Which means that if you create a table named mytable then to query it you’ll need to use “mytable” in your queries. Not cool, IBM, not cool. I eventually just named everything with caps, but Data Studio prepares SQL statements for you that you can edit and remove the unnecessary quotes when creating your tables. I still can’t get Data Studio to create a database, so I do that by command line. Not sure what the problem is, I’ve even tried by running it as Administrator and even in the user account that it set up. No go. Command line, no problem. Go figure…
Also when you set up DB2 you have the option to create a sample database. My installation said this had failed, but after going in to Data Studio the database was present.
Last caveat: You’ll need to make sure that your user has access privileges to the database. Do that in Data Studio via the database’s properties.
Step 2 – Connect from Unity
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 |
using UnityEngine; using System; using System.Text; //C:\Program Files\IBM\SQLLIB\BIN may need to be in environment path. using System.Data.Odbc; public class DB2Service : MonoBehaviour { void Start() { Test(); } void Test() { // Set up a connection string. The format is pretty specific, just change the YOUR...HERE to real values. string connectionStringODBC = "Driver={IBM DB2 ODBC DRIVER};Database=YOURDATABASENAMEHERE;Hostname=localhost;Port=50000;Protocol=TCPIP;Uid=YOURUSERNAMEHERE;Pwd=YOURPASSWORDHERE;"; // Make the connection and open it OdbcConnection odbcCon = new OdbcConnection(connectionStringODBC); odbcCon.Open(); // Try out a simple command/query - make sure to change SOMEREALTABLENAME to your table's name OdbcCommand command = new OdbcCommand("SELECT COUNT(*) FROM SOMEREALTABLENAME", odbcCon); int count = Convert.ToInt32(command.ExecuteScalar()); Debug.Log("count: " + count); // Try a full select query OdbcCommand command2 = new OdbcCommand("SELECT * FROM SOMEREALTABLENAME", odbcCon); StringBuilder sb = new StringBuilder(); using (OdbcDataReader reader = command2.ExecuteReader()) { // Add the column names to the string builder for (int i = 0; i < reader.FieldCount; i++) { sb.Append(reader.GetName(i)); if (i < reader.FieldCount - 1) sb.Append(","); } sb.AppendLine(); // Step through the query's results and add those to the string builder. while (reader.Read()) { // Separate each column with a comma for (int i = 0; i < reader.FieldCount; i++) { sb.Append(reader.GetString(i).Trim()); if (i < reader.FieldCount - 1) sb.Append(","); } sb.AppendLine(); } // Output the results to the console Debug.Log(sb.ToString()); } // Close up that connection! odbcCon.Close(); } } |
That’s it. Hopefully the code is self-explanatory with all of the comments. I’ve set up a repository of this you can grab from GitHub that has a simple UI so you can do a full build and test it out on various machines or modify it to your own purposes. Check it out here.
I hope this saves someone from the many headaches I encountered over the past few days of trying to get this to work. There are a lot of outdated examples on the internet and none specific to Unity. Next up I have to test this with some other database management systems (DBMS) such as MySQL, SQLLite, Oracle, and SQL Server. Should be a fun time setting those up!
I was testing this for an additional feature of an app I’m working on called vuzop. It is a data visualization tool that allows you to view your data in 3D or with a VR headset. Check out more information on vuzop here.
As always,
Thanks for reading!
The package in Unity that needs to be installed in order to access:
System.Data.dll
System.EnterpriseServices.dll
is “Input System” in Package Manager
correction. Unity System is incorrect. It clears all compiler errors for a moment and reappeared.
In newer versions of Unity (2021 at least). You don’t seem to need to add these libraries anymore. I can’t fully test because I no longer have a IBM DB2 database set up and don’t really want to. If you post your full log trace here, Unity version, and platform you’re running on then I could potentially take a look but it compiles for me no problem in 2021.3.2f1.
Good luck! You might be better off converting the DB to something more popular like SQLLite.