Thomas Hampel

 23 September 2014
GDI Business Line is an ERP & CRM software for the small & medium businesses market. It is developed by the German vendor GDI based in Landau in der Pfalz.
A customer wanted to use the address data from the GDI platform in the Notes/Domino environment. Main purpose was to simplify communication with known customers by synchronizing contact names, addresses, and phone numbers to Domino.

We all know integrating Directory Data with Domino is made easy with TDI, so lets see if we can use it here.
The backend database of GDI is based on FirebirdSQL , and they provide a JDBC driver which is all we need to make it work.

Here are step-by-step instructions for connecting TDI with the GDI Address table

Part 1 - TDI Installation
Tivoli Directory Integrator V7.1.1 is provided free of charge as an additional entitlement for Notes/Domino customers.
All you need to download from Passport Advantage is IBM Tivoli Directory Integrator Identity Edition V7.1.1 with the part number that fits you needs

Platform	Part Number	Size
Windows 32Bit	CZUF0ML	555mb
Windows 64Bit	CZUF7ML	567mb
Linux 32bit	CZUF2ML	547mb
Linux 64bit	CZUF3ML	554mb

We are intending to use a local Notes Client connector so we will be using the 32bit version of TDI. In case you're planning to install TDI on a  64bit Domino Server you could also go for that version.
The installation process of version 7.1.1 is not any different than V7.1, so you can just follow instructions for installing Tivoli Directory Integrator on IBM Infocenter or on Connections101 (Thanks gabturtle & Paul Mooney for this site).

Part 2 - Apply TDI Fix Pack
Download the latest fix pack for TDI v7.1.1 from Fix Central which at the time of writing this blog post is Fix Pack 3 and this JRE upgrade
Follow installation instructions provided with the fix pack(s)
Hint : {TDI_install_dir}\bin\Applyupdates.bat  -update [path to FP zip file]

Part 3 - Notes Connector
TDI can establish different types of connections to Notes/Domino, not all of them can be used everywhere (see Supported session types by Connector )
e.g. if you dont want IIOP to be enabled on your Domino server, you'll have to use either the Local Client connector, which requries a Notes Client to be installed on the same machine, or the Local Server Connector, which requires a Domino Server installed on the same machine. My personal preference is the Notes client connector because it just requires a Notes ID and I can connect from my own client workstation to any server regardless if IIOP is enabled or not.

• Copy the file {NotesProgramDir}\jvm\lib\ext\Notes.jar  to  {TDI_install_dir}/jars/3rdparty/IBM  
  (or to the folder defined in the variable "com.ibm.di.loader.userjars" parameter defined in the solution.properties file)
• Append the Notes Directory to the PATH parameter in the following TWO files
  {TDI_install_dir}ibmditk.bat
  {TDI_install_dir}ibmdisrv.bat
  Example:
  set PATH=%TDI_HOME_DIR%;%TDI_JAVA_BIN_DIR%;%TDI_LIB_DIR%;C:\Program Files (x86)\IBM\Notes;%PATH%

Part 4 - Firebird JDBC Connector
As long as there is a JDBC connector, TDI should be able to connect to the database. FirebirdSQL is nothing special here, so this is what you have to do:

• Pick the JDBC driver here (make sure to choose the one for Java 7)
• Extract the ZIP file to a temporary folder of your choice
• Copy the following three files to the folder {TDI_install_dir}\jars\3rdparty\other
  jaybird22.dll, jaybird-2.2.5.jar, jaybird-full-2.2.5.jar
  

Part 5 - Connect and Feed Data
Now launch TDI Configuration Editor ( {TDI_install_dir}ibmditk.bat ) and add a new JDBC connector

We would like this connector to be used in Iterator mode because we want to loop thru the data later on.
When you click on "Next >" you will be prompted to specify additional connection parameters.
The syntax for the JDBC URL is

jdbc:firebirdsql://host[:port]/database

JDBC URL = jdbc:firebirdsql://sqlserver:23053/C:\Database\GDI.GDB?sql_dialect=1&charset=WIN1252
JDBC Driver = org.firebirdsql.jdbc.FBDriver

and of course you must define your database credentials and the table you want to connect to. In our case the table is "CM_ADRESSEN"

Click Finish to add the connector as your input feed.


Part 6 - Data Map
Now lets use the connection and define the input map:

• Within the connector, use to connect button to establish a first connection for reading the database schema.
• Select the fields which you want to make use of by either dragging/dropping them from the schema or by using the button "Add"
  

Part 7 - Output to Notes/Domino
Lets write this data to Domino...
(Remark: assuming the target database already exists and is using a standard pubnames template)

• Add a Notes Connector in Update mode
  
  When you click on "Next >" you will be prompted to specify additional connection parameters.
  This example will connect to a remote database hosted on "DominoServer/Org/O", you can of course leave the server name empty to connect to a local database.
  
  Click Finish to add the connector as your Data Flow.
• Click the output connector again to define which data to write to which field in Notes/Domino
  Here is an example, feel free to modify or extend:
  
• In the connector define the Link Criteria
  It seems the field SATZUUID is used as a unique key, so we are going to use it as well. Of course you need to make sure to write this field to the target database, otherwise the lookup will always fail and duplicate entries are the result.
  

Part 8 - Fine Tuning
This part is to be done by yourself. You should probably add some special handling to handle different address types such as if the record is using...
"Adresstyp=1" = Contact
"Adresstyp=4" = Company
"Adresstyp=16" = Person

or updating the full text index when the assemblyline has finished...

try{
  notes=NotesConnector.getConnector
  dbname=notes.getParam("notesDatabase")
  srvname=notes.getParam("notesServer")
  sess=notes.getDominoSession()
  db=sess.getDatabase(srvname,dbname)
  if (db.isOpen())         {
          message="Requesting to update FTIndex on " + srvname + "!!" + dbname ;
          task.logmsg ("INFO",message) ;
          db.updateFTIndex(true);
  } else {
          message="Unable to open target notes database." + srvname + "!!" + dbname
          task.logmsg ("ERROR",message) ;
          java.lang.System.out.println (message);
}
 
} catch (ex) {
  message="Unable to update FTIndex in target Notes database. , "  + ex
  task.logmsg ("ERROR",message)
  java.lang.System.out.println (message)
}

Part 9 - Run it
Run the assemblyline and (optionally) have a beer while you will see new person documents showing up in Domino.

Summary
For those of you who are very lazy, here is the TDI AssemblyLine for further use.
GDIDataImportExample.xml

Please note that you must adjust it to fit your needs!  Concluding with Notes Sensei's words : YMMV