Can Crystal Report Access AS/400 Database(DB2) ?? 4

[krist]

Hi guys,
Can Crystal Report access AS/400 Database (DB2), suppose my Windows PC with Crystal using client access to AS/400 ??

If yes, how ?

Thanks,
Krist.

 

[balves]

Crystal comes with a DB2 DLL that works in conjunction with DB2 software. Never tried it.

Or, there are multiple companies that offer an ODBC driver for DB2 on its supported platforms. Brian J. Alves
Terrier Consulting, Inc.
Email: brian.alves@worldnet.att.net
VB / Crystal / SQLServer

 

[rhinok]

There's also a Client Access ODBC Driver for AS/400 that you can use.

 

[dataprincess]

Yeah, the fun part is mapping the fields and trying to figure out what is what. I'm currently working with JDE and can't get the OEM to provide me with a field mapping list or a relationship matrix so I'm left to figure out all of it for myself. Fun, fun, fun! If any of you are using Crystal against JD Edwards and have this info let me know where, how and who you got it from so I can bribe them. That or I'll forward you my room number from the mental institution I'll be staying at for a while. Thanks.

 

[bstedman]

I have used CR against DB2/400 db for years, but not JDE. The best way for me to see the layout, and very basic, is to run a native SQL on the AS/400 and view the "whole" db. Then I would determine the fields required for CR. Very hit and miss and time intensive. I am not a SQL or dba person, so this was my work around.

 

[Jinny]

Krist - I use ODBC with Crystal Reports 8. It is most helpful to have the File Field Definitions before starting. Our Programs are written in RPG. Since I with with the RPG also, I can print my own File Field Definitions. I am just getting started with Crystal, but have used ODBC quite a lot with Excel and Access.

I would be glad to try to answer questions.

jfollowell@ghtsc.com

 

[rhinok]

I've run into problems using left outer joins against AS/400 Db2 physical files. I needed to create Logical Files instead (same as view, essentially).

 

[krist]

Thanks guys,

all of you have given me clearer view about what we want to achieve : Update Transaction in RPG, then 'Rapid' report development with Crystal Rpt.

I'm not AS400 person , I'm just assigned to explore Crystal reporting on AS400. Another question :
What if the database in AS400 IS NOT DB2 ??, from what I know, our RPG application use what they call 'Native AS400 Database', Not DB2.
Can Crystal use this data ?? using Client Access ODBC Driver or anything else ??

Thanks for further info,
Krist

 

[Ericksco]

DB2 is AS/400's native database..It is actually DB2/400.

 

[be35g]

Krist,

I'm doing a fair amount of Crystal to AS/400 stuff. It took a lot of trial-and-error coding and research even to get simple things to work, especially since I'm not very accomplished in either VB or Crystal (the project was an emergency surprise!). Mostly what I do is automatic form generation from "green screen" and GUI AS/400 applications.

If I can do anything to help, feel free to ask.

Wesley Welsh
wwelsh@greenvillecounty.org

 

[PaulaJ]

The quickest way to find out the field names/sizes/position of an externally described file on the AS400 is to go to a command line and enter the command "DSPFFD" (Display Physical File Field Description) then press CMD-4. Enter the File Name, File Library, and whether you want to print the results or not. ("*" or "*print&quot Press ENTER. This will display or print out an explanation of each field in the file, it's size and exactly what it's starting position is. I keep a notebook of all of the files I need to use in Crystal, but use this method most often as you can scan for specific characters.

 

[krist]

Hi,
Do both of you (Wesley and Paula) use DB2/400 or other database ?
Our application do not use DB2/400. When I ask them(our AS400 RPG Developers) what database are they using, all they can say is 'some EBCDIC data format', but they don't know what is the official name of that data format, but surely not DB2!
Does anybody know what is that mysterious data format ??
(we use it from long time ago) and can Crystal access it ?

Thanks,
Krist

 

[PaulaJ]

My understanding of this confusing topic is this. The Native 400 database (EBCDIC Externally Described Physical Files) was used as the underlaying database for a PC package that IBM called DB2. If it is then connected to an AS400 directly, then the Software is named DB2/400 and uses the software created for the PC package and the Native AS400 Data for the BackEnd. It appears to be more of a SQL clone than the traditional RPG or COBOL used on the AS400. The DB2 package can be run on a PC Platform without an AS400, but the underlying database functions identically. Typical IBM Semantics. There appear to be some differences in the storage methods (ASCII vs EBCDIC)and languages used to access this data, but anyone familiar with AS400 Native files will find DB2 data similar.

Crystal accesses AS400 Native files if the connections are made correctly. We use a product call "Quick DB" that seems to bridge the Crystal to the AS400. I am not a hardware/connection person. I would contact "Crystal" for exactly how the connections to the AS400 could be set up.

 

[Ericksco]

Krist,

I don't recall any third party database for the AS/400. The operating system (OS/400) comes with the DB2/400 database - this is the "Native database" of the AS/400 ever since.

You can check this link
(

http://www-1.ibm.com/servers/eserver/iseries/db2/

)
out to give you more info on DB2. They use to call the database DB2/400, but now in the iSeries (newer machines with new OS) they call it DB2 UDB.. EBCDIC is DB2s data format.. PC databases will have ASCII format.

Hope this helps.

Erick

 

[Jinny]

Krist -

I am using Crystal 8 with Client Access ODBC for reports.

 

[s1972]

Jinny -
we are trying to connect to db2 from Crystal Reports, using crystal db2 odbc driver. For this Client appication enabler (CAE) requires to be installed from db2 CD.

Is there some specific settings that require from the crystal db2 odbc driver side & for the CAE install.

Appreciate if you could provide some guidance.

Thanks

satyajit

 

[kbrimley]

I have pulled data from our corporate AS/400 files via Client Access for years but, as a relatively new Crystal Reports (8.0) user, have been unsuccessful in connecting Crystal to the AS/400. I've experimented with various configurations of the ODBC driver ("Client Access ODBC driver 32-bit&quot but I'm wondering whether I even have the correct driver. [How can I know which one I need for our corporate AS/400?] The data processing folks, whom I call for AS/400 support, are mainframe specialists and they know very little about how Crystal (or even MS Access) can connect with it. As an analyst and report designer who knows the data fields, the software, and a bit about ODBC, I'm feeling stuck. I know it's possible to connect Crystal with the AS/400 but I don't really know what to tell the mainframe support personnel to do. Any suggestions would be greatly appreciated.

Thanks!

 

[DonMc67]

DataPrincess...
I haven't been in as tough a boat as you, but have you tried to use Visio to give you a ERD and work from that?

 

[Roadkill150]

Hi Peeps,
Being a simple RCAD guy, I don't usually get mixed up in this sort of thing, but hopefully I can help out Kbrimley and Dataprincess.. have you tried the following:>?

Requirements for connecting to the IBM DB2 database through ODBC:
======================================================================
0) Make sure that an IBM DB2 server is already setup and running on the backend AS/400 machine.

1) Acquire and install the IBM DB2 Client Software (obtained from IBM).

2) Once the IBM DB2 client has been properly installed and configured on the machine, then a connection via ODBC to DB2 requires that either:

a) Connectivity 5 (formerly known as DDCS - Distributed Database Connectivity Service). This software has to be purchased separately from IBM. CR does not ship this product.
OR
b) Install the ODBC driver which shipped with the IBM DB2 database. If this ODBC driver is used to connect, then Connect 5 does not need to be purchased.

3) The Connectivity 5 (or DDCS - Distributed Database Connectivity Software/Service) add-on piece is required in order to use our CRDB2xx.DLL ODBC driver or NATIVE DB2 driver called P2SDB2.DLL that Crystal Decisions ships with.

Method of access in the Report Designer via our ODBC driver and via our native DB2 driver:

method#1: Report Designer---->P2SODBC.DLL----->ODBC32.DLL---->ODBC DRIVER---->DDCS---->DB2 Client----->DB2 Server

method#2: Report Designer---->P2SDB2.DLL----->DDCS---->DB2 Client----->DB2 Server


(4) Install CR or SI (which also comes with a Report Designer component). They both ship with an Intersolv ODBC driver for IBM DB2 called CRDB2xx.DLL. In addtion, they will install a native DB2 driver called P2SDB2.DLL.

NOTE#1: There is a DB2 Native Connection Utility (that comes with the DB2 Universal Server product) called: "Client Configuration Assistant". To use this to test connectivity, do the following:
- Highlight Database.
- Click Test button.
- Enter UserID and Password.
- Either a successful or failed connection will occur. This will verify whether the IBM DB2 client has been properly installed and configured.

NOTE#2: When one sets up an ODBC datasource using our CRDB2xx.DLL, all that is required is the Datasource name and Database Name.


NOTE#3: Scenario: using CR or SI to connect with DB2 on an AS/400. A successful connection is made via ODBC but cannot be made via the DB2 native driver that ships with our products.

This does work on an NT and RS6000 platform but not on an AS/400. It is likely that the cause is the lack of the right messenger on AS/400 (DDCS vs DRDA). Apparently, IBM only ships the right one on AS/400 as an add-on that you buy and pay for per user. This is fine on an NT/RS6000 platform but it is an add-on for AS/400.

NOTE#4: A user does not have support for stored procedures unless the user uses crystal's native DB2 driver and this is working with Connect 5 with DB/2 on an AS/400.

Cheers,
-Bruce Seagate Certified RCAD Specialist.
-Bruce Thuel-Chassaigne
roadkill150@hotmail.com