MIDAS#18 Out of Notes Part 10: To SQL Server/DB2 (Midas LSX)
Out of Notes, part 10: To SQL Server or DB2 is the first in the series since we released the Genii Software Raodmap. As such, it shows how we are dealing with customers in Lane 2, who have a Notes infrastructure but have either no dedicated Notes development staff, or a severely limited staff. It can also apply to customers in Lane 1 who are actively migrating data out of Notes and into a different infrastructure.
In this video, we demonstrate the Export to CSV sample database, a data driven database which empowers a user to export an entire database or portions of it to CSV format. Each form is exported to a separate CSV file, with either every field or selected fields represented. Rich text fields are represented in HTML/XHTML and included in the CSV. Images may be included internally or exported to disk where they can be imported separately. Links between documents may be preserved in a variety of ways.
Once the export is done, the CSV can be imported into SQL Server using a script such as SharePoint or another SQL or other database which takes CSV input. The process is very fast and scales well. No changes are necessary to prepare the databases to be exported, and the Export to CSV will work equally well on databases with a locked design, such as 3rd party databases from a proprietary vendor who is no longer in business or cannot provide details on the databases.
The Export to CSV database requires that Midas LSX 5.x be installed along with a valid license file, but an auto-install document in the database will take care of this installation silently if the license and software have been added to it.
Importing CSV files into SQL Server is easy with a technique such as the one described in How to create and populate a table in a single step as part of a CSV import or using Powershell.
Importing CSV files into DB2 is also easy with a technique such as the one described in Import of CSV-file or using utilities such as DB2eClp.
This is Ben Langhinrichs of Genii Software with Out of Notes Part 10: To SQL or DB2.
We have customers who want to take data and use it in a relational database such as SQL Server or DB2. I'm going to show you how to do this with Midas and our new Roadmap.
This is the Mini-Lessons.info site, and it runs off a database. Each lesson is a separate document with complex data inside it. So, if we go into the Export to CSV database and create an Export Directive, all we have to do is pick the database.
We're going to export everything from the database. We'll go to the c:\export\ directory, use all the forms and all the fields. We'll save it (the Export Directive form), and then we can run the agent. You don't need a developer for this.
There's the c:\export\ directory. Click this (view action) and switch over and we'll see there is now a Lessons subdirectory and there's the CSV file. Every item in the Lessons is now represented by a separate column. The first row has the titles (item names). Down here (in a subdirectory) we have the additional images and attachments.
Similarly, we can take the Lotusphere Sessions database from a couple years back. In this case we don't want everything, we don't want those people and things like that, we just want the sessions, and we just want some of the fields.
We're going to create a new Directive, pick the Sessions database and this time we're going to select the JournalEntry form, which is what stores the Session, and then we'll just put a few of the field names in.
The idea here is to take some Notes data, and without having to have a Notes developer at all - because not everybody has those anymore - you can just specify the information you want and export a high fidelity version of that.
In this case, there's no rich text, so when we look at the CSV file what we're going to find is just the items that we specified, the session id, session title and session abstract.
We can pull that into DB2 or SQL Server and use that as we need it. I've provided some links in the description below about how to take that CSV file and easily put it into SQL Server or DB2.
Thank you for joining us, and visit us again at Mini-Lessons.info