IBM Db2 is one of the largest and most sophisticated enterprise DBMS packages ever created. Db2 is a product of IBM, the company that invented SQL. This topic is the final step in a five topic sequence showing a complete installation, and then utilization, of Db2 for Developers from the very beginning. Please see Install Db2 topic for basic info on Db2.
Disclaimer:
These topics were accurate when written, using the versions of installation
software indicated. Third party packages can and do change, so this
topic may be out-of-date. It is provided as an example of how such
installations can be approached.
Installing Db2 and readying it for use in typical installations involves five steps:
Connect to Db2
This is the last topic in the above sequence of steps.
So far, we have installed Db2 on our target machine. We enabled use of Db2 through networks by opening a port in Windows firewall for Db2. We have created a database within Db2 called gisdb. We have installed client software on our remote, client machine. Now, we will connect from that client machine to Db2, to the gisdb database, to show an example of loading data from Manifold into Db2.
We are using a 64-bit Windows 10 desktop system on which we have installed Manifold. We have also installed install Db2 Client software on that machine, so we can connect to the server machine on which we installed Db2.
Every machine running Manifold which
will be used to connect to Db2 must
have IBM's Db2 Client software installed. If we want
to connect to our Db2 installation from a remote machine running Manifold,
we must install Db2 Client software
on that remote machine. That is easy to do, as was seen in the Install
Db2 Client topic.
If we have teleported into this topic without reading the preceding topics in this case study, we should make sure to install client software as illustrated in the Install Db2 Client topic.
Launch Manifold.
Right-click into the Project pane and choose Create - New Data Source.
In the dropdown menu we then choose More... to launch the New Data Source dialog.
Enter a descriptive name, Db2 gisdb, and choose Database: db2 as the type. Click the browse [...] button to launch the Database Login dialog.
Enter the server name using the IP address of the server, which in this example is 192.168.2.44.
Choose Use login and password and then enter db2admin as the login. Enter the password we provided for the db2admin user in the Install Db2 topic.
For the Database, enter gisdb. Case is not significant, so we can enter the name of the database in upper or lower case.
It is a good idea to check for errors by pressing the Test button.
If that does not report a connection, we should fix any mistakes. When we see Connection established we press OK.
Back in the Database Login dialog, press OK.
Back in the New Data Source dialog we press Create Data Source.
A new data source called Db2 gisdb appears in our Project pane.
We can expand it, and then within the System Data folder we can double-click the mfd_srid table to open it. That displays the coordinate system SRIDs available within the gisdb database. That shows Db2 is running, the gisdb database has been created, and it has been enabled for spatial work. This also shows we can connect to Db2 and to the database specified from our client machine, connecting to the Db2 server machine through the network.
We will now explore how to upload a drawing into Db2. This is a more manual process than the simple drag and drop or copy/paste used with the other enterprise DBMS packages that are closely supported by Manifold. Expectations are that Db2 will, over time, will also acquire simple, copy/paste support by Manifold.
We open another Manifold session (not illustrated) in which we have stored some drawings of Mexico using different projections. We copy and paste one of those drawings into our working session, as seen above. We have pasted a drawing that shows Mexico using Latitude / Longitude projection, named MexicoLL. shows Mexico using Pseudo-Mercator projection.
In the illustration above, we have opened the MexicoLL drawing.
Choosing the Info pane Component tab, we see the MexicoLL drawing is in Latitude / Longitude projection.
Opening the drawing's table, MexicoLL Table, we see it has various fields, including a Geom field. The table is shown undocked in the view above. We will leave it open while continuing our work in this topic, as well will need it later.
A quick way to see the field types used in the table is to launch the Edit - Schema dialog, which reports all fields and their types.
We will create a new table in the Db2 gisdb data source, and in the act of creating that new table we will duplicate the structure of MexicoLL Table as shown in the above schema.
We right-click on the Db2 gisdb data source and choose Create - New Table. This will create a new table in the gisdb database inside Db2.
We specify the name MexicoLL for the table, so the table within Db2 has a similar name to the table we are copying in our Manifold project.
We press the Edit Schema button.
The Schema dialog starts out with an ID field and an ID_x index Manifold creates by default in new tables created within Db2. In the Schema dialog we press the Add button.
We choose Field to begin adding a new field. We will now add all the fields we want in the new table, one after the other.
We chose a name of Name and a type of nvarchar. We press OK.
The new Name field appears in the Schema, shown in blue, provisional color since we have not yet saved changes to the table's schema.
We repeat the above process to add additional fields, of type float64, to the schema. We now have all fields from MexicoLL Table except for a geometry field. We now will add a geometry field.
We enter a name of Geom and choose type geom. As soon as we choose a geom type, a Coord system control appears to allow us to specify what the coordinate system used by the geometry field will be.
We press the coordinate system
picker button to choose a coordinate system that Db2 supports.
From the drop down menu we choose Edit Coordinate System.
In the Coordinate System dialog, a Database tab appears since we are choosing a coordinate system within the Db2 server. We click on the Database tab to see it lists the various coordinate systems that Db2 supports by default. We choose the last one, WGS84_SRS_4326, which is equivalent to Latitude / Longitude in Manifold and other GIS packages.
Press OK.
That assigns a Db2 coordinate system to the new geometry field that is equivalent to the Latitude / Longitude coordinate system used by our MexicoLL drawing in Manifold.
Press OK.
The new Geom field appears in the Schema along with the other fields we have added, in provisional, bluish color.
We press Save Changes.
Back in the New Table dialog, we press Create Table to create the new table in Db2 with the schema we have put together.
The new table appears within the Db2 gisdb data source, named with the user name we used for the connection as a prefix, along with a virtual, pseudo-drawing that is taken from the geometry field we created.
To provide a consistent, orderly, user interface, Manifold will automatically generate virtual components, such as folders, some Manifold system tables, and some automatically-generated drawings, that appear to be in the database but which are generated on the fly from metadata and are not a new, real table or other new item added to the database. See the Real and Virtual Components topic for discussion and examples.
We open the new DB2ADMIN.MEXICOLL table, and see it is empty, as expected, since we have not yet populated it.
We can populate the new table by copying data from the MexicoLL Table and pasting it into the new DB2ADMIN.MEXICOLL table.
We click on the MexicoLL Table that we opened earlier, and which we left undocked as seen in the illustration above.
We press Ctrl-A to Select All records, and then we press Ctrl-C to copy all of the selected records.
We click on the DB2ADMIN.MEXICOLL table to move the focus there, and then press Ctrl-V to paste all the records that we copied.
All the copied records now populate the DB2ADMIN.MEXICOLL table, converted to Db2 data types as necessary. The Manifold mfd_id identity field is converted to the IBM ID identity field.
If we scroll to the right, we see that the Geom geometry field has been populated as well. Manifold reports the contents using Manifold nomenclature, but the geometry type is the native Db2 Spatial Extender type for area objects.
Now that we have a Db2 table with records for each object, including geometry, we can create a drawing.
Right-click on the DB2ADMIN.MEXICOLL table and choose Create - New Drawing. This is just like creating a drawing based on a table or a query in a Manifold project. The same command works the same way on a table stored in Db2.
In the New Drawing dialog we specify a Db2-ish name for the drawing, DB2ADMIN.MEXICOLL Drawing, we leave the other choices in their defaults. Manifold knows there is a Geom in the table, and it knows it should create a spatial index, and it knows the coordinate system that we specified for the Geom.
We press Create Drawing.
A new drawing, called DB2ADMIN.MEXICOLL Drawing, appears in the Db2 data source. We can double-click it to open it.
The DB2ADMIN.MEXICOLL Drawing opens up to show Mexico, using default gray style.
We can verify the new DB2ADMIN.MEXICOLL Drawing is correctly georeferenced by creating a Bing streets data source (one of the default favorite web server data sources), and dropping that into a new Map we create. We can then drag and drop the DB2ADMIN.MEXICOLL Drawing into the map to see that it aligns perfectly.
If we like, we can Style DB2ADMIN.MEXICOLL Drawing to have the same thematic formatting as the original MexicoLL drawing. We can do that by either using the Style dialog to specify the style for the Db2 drawing, or we can open the Properties dialog for the MexicoLL drawing and then copy and paste the Style properties from the MexicoLL drawing into the Properties for the DB2ADMIN.MEXICOLL Drawing.
We have finished the last topic in the case study on installing Db2.
See the preceding topic: Install Db2 Client
Jump to the beginning of the case study: Install Db2
Why not copy and paste as with other DBMS packages? - Given that we created a table in Db2 and then copied records from a table in the Manifold project and then pasted those records into the new table created in Db2, why doesn't Manifold just copy and paste a drawing and table from the Manifold project into Db2, the way it is done for other enterprise DBMS packages? The reason is the very limited number of coordinate systems available by default for spatial data in a new table within Db2, and the complexity of creating a special coordinate system within Db2 to match what might be used within Manifold. The relatively straightforward approaches used with the other DBMS packages do not work with Db2, which is a bit different.
For now, use the procedure above, taking a moment to re-project data destined for Db2 into Latitude / Longitude projection to avoid any integration problems and to keep things as simple as possible. In the months ahead, Manifold will make it easier to use whatever projection is desired to write new data into Db2. Until then we can read whatever is in Db2 and we can write using the more manual techniques shown above.
File - Create - New Data Source
Big List of Formats and Data Sources
Example: Switching between Manifold and Native Query Engines