Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Linking to a Lotus Domino View
- Thread starter lynchg
- Start date
Linking may be a bit too far, but we use a Lotus Script to write data to a DB2 database with scheduling on the Domino server.
There are a lot of quirks however and transfer-speed of the notes data to DB2 is low......
There are commercial tools like LEI and DECS too....
T. Blom
Information analyst
tbl@shimano-eu.com
There are a lot of quirks however and transfer-speed of the notes data to DB2 is low......
There are commercial tools like LEI and DECS too....
T. Blom
Information analyst
tbl@shimano-eu.com
- Thread starter
- #3
Could you explain more about how that would work?
Let me explain what I am trying to do. I am working as a contractor hired to produce Crystal reports for a medical sevices company. The patient info is held in a DB2 database on a mainframe. Each day a patient appointment list is going to appear in a Lotus view. I need that data to begin the process of grinding thru the DB2 data to produce the reports.
The entire process is to be driven from Lotus, which will receive the appointment list data via faxes, load that data into a Lotus database to supply the view. Lotus will then kick off the Crystal report generation and then take the completed reports, convert them to pdf's, and fax them to the doctors' offices.
My idea is that since this is already being driven from Lotus, why not first send the appointment data directly into an existing table in DB2 so that I can incorporate that data into DB2 views and generate the SQL logic to feed the reports. The amount of data in the appointment list is small, 10 fields and maybe a few hundred records per day.
As part of the report generation I would also like to have some permanent working tables in DB2 emptied and repopulated by delete and append DB2 views, but have them called from code within Lotus.
I know this post is a little long but I wanted to lay out the strategy, we have tried many other ways to try to incorporate the appointment data with the DB2 data and have been unsuccessful. I have no Lotus development experience, I have worked with different kinds of database development but not with DB2. I have been meeting resistance from both the Lotus people and DB2 people, they are busy with other tasks and would prefer that I come up with a solution that doesn't require any work from them. I have even tried to use Crystal as a data conduit from Lotus to DB2 using subreports and parameters, but found no practical method.
Thank you for any help you may be able to offer.
Let me explain what I am trying to do. I am working as a contractor hired to produce Crystal reports for a medical sevices company. The patient info is held in a DB2 database on a mainframe. Each day a patient appointment list is going to appear in a Lotus view. I need that data to begin the process of grinding thru the DB2 data to produce the reports.
The entire process is to be driven from Lotus, which will receive the appointment list data via faxes, load that data into a Lotus database to supply the view. Lotus will then kick off the Crystal report generation and then take the completed reports, convert them to pdf's, and fax them to the doctors' offices.
My idea is that since this is already being driven from Lotus, why not first send the appointment data directly into an existing table in DB2 so that I can incorporate that data into DB2 views and generate the SQL logic to feed the reports. The amount of data in the appointment list is small, 10 fields and maybe a few hundred records per day.
As part of the report generation I would also like to have some permanent working tables in DB2 emptied and repopulated by delete and append DB2 views, but have them called from code within Lotus.
I know this post is a little long but I wanted to lay out the strategy, we have tried many other ways to try to incorporate the appointment data with the DB2 data and have been unsuccessful. I have no Lotus development experience, I have worked with different kinds of database development but not with DB2. I have been meeting resistance from both the Lotus people and DB2 people, they are busy with other tasks and would prefer that I come up with a solution that doesn't require any work from them. I have even tried to use Crystal as a data conduit from Lotus to DB2 using subreports and parameters, but found no practical method.
Thank you for any help you may be able to offer.
lynchg,
I will try to pry the code used by my fellow Notus guru as a possible example.
Did you ever contemplate using Notes SQL ODBC drivers for fetching data from Notes directly into a reporting tool? I have dabbled with it using Business Objects (a direct competitor to Crystal reports).
You can download the drivers directly from IBM's corporate site.......
Perhaps you can drop me a mail to send the code to you directly?
T. Blom
Information analyst
tbl@shimano-eu.com
I will try to pry the code used by my fellow Notus guru as a possible example.
Did you ever contemplate using Notes SQL ODBC drivers for fetching data from Notes directly into a reporting tool? I have dabbled with it using Business Objects (a direct competitor to Crystal reports).
You can download the drivers directly from IBM's corporate site.......
Perhaps you can drop me a mail to send the code to you directly?
T. Blom
Information analyst
tbl@shimano-eu.com
- Thread starter
- #5
I can link to Lotus directly from Crystal but the data from Lotus needs to be processed before producing the report output and Crystal is weak in it's ability to execute complex SQL, and the performance is poor for the amount of data we have in our DB2 database. We have many tables containing multiple millions of records.
I think what blom says is ok so far.
the next step after the ODBC driver for Lotus would be to create a wrapper for the ODBC data source. Then you can access the Lotus data like a DB2 table - so called federation.
That works fine with older ODBC drivers for lotus, but right now I have a problem with that and I have PMRs open with DB2 as well as Lotus.
(We have a similar setup, but just use it for DB2 reports or informatica)
Anyway, for Crystal I found that:
technote 7003517
Well, good luck !
Juliane
Juliane
the next step after the ODBC driver for Lotus would be to create a wrapper for the ODBC data source. Then you can access the Lotus data like a DB2 table - so called federation.
That works fine with older ODBC drivers for lotus, but right now I have a problem with that and I have PMRs open with DB2 as well as Lotus.
(We have a similar setup, but just use it for DB2 reports or informatica)
Anyway, for Crystal I found that:
technote 7003517
Well, good luck !
Juliane
Juliane
I am not sure about the version of DB2 you are using.
In DB2 V8 the ODBC Wrapper is shipped with DB2 Information Integrator, more specific the component IIRW (Relational Wrappers).
The steps you need to do are:
In the ODBC data sources:
- register your Lotus View as an ODBC data source
In DB2:
- create wrapper for ODBC
- create server for that wrapper, give your ODBC data source name
- optional: create user mapping
- create nickname for the table (I think it is best to use the discover feature in the GUI, especially when you are not familiar; the Control Center GUI helps you with that)
All that you find in the Control Center under 'Federated Database Objects'.
When dll's are missing then usually some path is not correct or some feature was not installed.
For user mappings: you need FP4 or higher to get that working, in lower fixpaks DB2 does not accept userids longer than 40 characters, which of course is not enough for Lotus. A workaround would be to give the user and password already in the ODBC data source settings, then this is not needed in DB2 - or whatever program you use to access - anymore. I found it useful to test the ODBC settings by accessing the data with Excel or Lotus123.
In DB2 V7 the ODBC Wrapper comes with Relational Connect I believe.
Let me know if you have problems, because I do have two PMRs open on that issue, one for DB2 UDB and one for Lotus.
The setup works fine with older versions of the Lotus ODBC driver, with the current one I have problems.
In DB2 V8 the ODBC Wrapper is shipped with DB2 Information Integrator, more specific the component IIRW (Relational Wrappers).
The steps you need to do are:
In the ODBC data sources:
- register your Lotus View as an ODBC data source
In DB2:
- create wrapper for ODBC
- create server for that wrapper, give your ODBC data source name
- optional: create user mapping
- create nickname for the table (I think it is best to use the discover feature in the GUI, especially when you are not familiar; the Control Center GUI helps you with that)
All that you find in the Control Center under 'Federated Database Objects'.
When dll's are missing then usually some path is not correct or some feature was not installed.
For user mappings: you need FP4 or higher to get that working, in lower fixpaks DB2 does not accept userids longer than 40 characters, which of course is not enough for Lotus. A workaround would be to give the user and password already in the ODBC data source settings, then this is not needed in DB2 - or whatever program you use to access - anymore. I found it useful to test the ODBC settings by accessing the data with Excel or Lotus123.
In DB2 V7 the ODBC Wrapper comes with Relational Connect I believe.
Let me know if you have problems, because I do have two PMRs open on that issue, one for DB2 UDB and one for Lotus.
The setup works fine with older versions of the Lotus ODBC driver, with the current one I have problems.
We have DB2 V7 Workgroup Edition, which may be a problem. EE and EEE editions have more functionality I guess.
I am also not sure if relational connect is something that will have to be bought additionally or that it comes with the standard license........
T. Blom
Information analyst
tbl@shimano-eu.com
I am also not sure if relational connect is something that will have to be bought additionally or that it comes with the standard license........
T. Blom
Information analyst
tbl@shimano-eu.com
Well, lucky we have one full licence for all that products company wide.
You are right, if that is not available, it is tough.
Licences are not cheap.
What still is possible - but I am not sure about automation - would be to extract the Lotus Data via the free ODBC driver to excel or any other ascii and import that. We used that to bypass our problems with the wrappers, but did not get it automated, since there were clashes in file access. But I am not a good windows programmer anyway, may be u manage that.
From Licencing at least no problem with a Workgroup or even personal edition.
Juliane
You are right, if that is not available, it is tough.
Licences are not cheap.
What still is possible - but I am not sure about automation - would be to extract the Lotus Data via the free ODBC driver to excel or any other ascii and import that. We used that to bypass our problems with the wrappers, but did not get it automated, since there were clashes in file access. But I am not a good windows programmer anyway, may be u manage that.
From Licencing at least no problem with a Workgroup or even personal edition.
Juliane
- Status