 |
Forums |
Admin  Start New Thread
By: Mark Davies
 RE: Upgrade to IBM_DB 0.9.0 [ reply ]
2007-11-14 15:30
|
Hi Alex,
Just to say I've tried the schema setting in database.yml and that works well (for a single-library application anyway!)
You also wrote:
> A more general note on Rails conventions and legacy support is probably worth making: much of the productivity and acclaimed goodness that Rails framework brings to the agile Web development comes from the "magic" generated by the convention-over-configuration principle. That also means one would start to miss important aspects of that magic once customization and exceptions start to creep in (i.e. multiple schemas, pluralization challenges). <
Absolutely so, but I think development always falls into one of two camps with little middle ground:
1. New development, where it's almost always possible and hugely beneficial to keep to the conventions of the framework.
2. Legacy redevelopment, where you're usually stuck with your database schema no matter how unwieldly and poorly-adapted to the framework it is.
I'm in (2), however Rails and ibm_db are still working pretty good for me!
|
By: Mark Davies
RE: Upgrade to IBM_DB 0.9.0 [ reply ]
2007-11-14 14:48
|
Hi Alex,
You wrote:
> There's no need to copy license file as long as DB2 Connect is properly installed, and there's no need to catalogue as long as the host and the port are provided in the connection configuration. The binding is part of the install and configuration of DB2 Connect and not really an independent requirement. <
Well I did install DB2 Connect properly, and it didn't copy over the licence file. All that binding stuff might indeed be part of the installation process, but I did not find any obvious documentation telling me that, or leading me through it. Perhaps I didn't look hard enough.
I think the main thing is, it would be good if this adapter were accessible to developers coming from a Java background (like me). We're not used to DB2 Connect, at least not for iSeries work; for us it's drop in JT400.jar and go. Coming to Ruby, database adapters look just as easy - certainly the MySQL one was - but initially it felt like running into a brick wall with ibm_db. A "dummy's guide" to getting DB2 Connect going would help an awful lot with that, and give clueless newbies like me a much easier time.
|
By: Alex Pitigoi
RE: Upgrade to IBM_DB 0.9.0 [ reply ]
2007-11-12 19:03
|
Mark, as long as you provide the schema name in the connection configuration (config\database.yml) you shouldn't need to make any further reference to it in any other place, unless you have more than one schema associated with your application.
Normally, your set_table_name should only occur in the model object that does not comply with Rails convention and should not include the schema qualifier. I hope this clarifies it, but please let me know if you need more help with this.
A more general note on Rails conventions and legacy support is probably worth making: much of the productivity and acclaimed goodness that Rails framework brings to the agile Web development comes from the "magic" generated by the convention-over-configuration principle. That also means one would start to miss important aspects of that magic once customization and exceptions start to creep in (i.e. multiple schemas, pluralization challenges).
|
By: Alex Pitigoi
RE: Upgrade to IBM_DB 0.9.0 [ reply ]
2007-11-12 18:55
|
Mark, as much as I agree the documentation needs some updates and some more details, I think some of the things you are saying need to be clarified. Install requirements are really just what is already listed:
- DB2 Connect 8 FP15, 9 FP2 or above (which includes license)
There's no need to copy license file as long as DB2 Connect is properly installed, and there's no need to catalogue as long as the host and the port are provided in the connection configuration. The binding is part of the install and configuration of DB2 Connect and not really an independent requirement.
|
By: Mark Davies
RE: Upgrade to IBM_DB 0.9.0 [ reply ]
2007-11-12 17:35
|
Hi Alex, you wrote:
> As for "base library for the environment", I can only suspect you are referring to DB2 schema. If that is the case, then the usual approach is to declare the schema in config\database.yml and also follow Rails convention of model/table pluralization (i.e. no need to set_table_name)
Hmm, OK, I'll try that. However I do have to call set_table_name (my database is very "legacy" and the files are not called anything I would want to name a model from!). I guess this will override any schema set in database.yml...?
|
By: Mark Davies
RE: Upgrade to IBM_DB 0.9.0 [ reply ]
2007-11-12 17:32
|
Hi Alex, yes I saw those docs - but all they say really is "Install DB2 Connect in order for your DB2 client to access an i5 data server". A specific document for iSeries listing the steps required would be good, i.e.:
1. Install DB2 Connect 9.1, ensuring SP2 is applied.
2. Copy the licence file "db2con*.lic" from the installation set to the IBM/SQLLIB/license directory.
3. Use DB2 Connect's "Connection Assistant" to connect to your iSeries database. (Some assistance here would be good, i.e. how to find out your iSeries database name, the fact that "service name" is not required).
4. Use DB2 Client to catalogue the iSeries database. If fails, instructions on how to do the binding bit.
I appreciate that some of this is outside the scope of the adapter, however potentially it's all required to get the adapter to work. At the very least, appropriate links to IBM documentation explaining how to do the various steps (although I failed to find anything terribly useful, certainly the DB2 Connect user manual was very light).
|
By: Alex Pitigoi
RE: Upgrade to IBM_DB 0.9.0 [ reply ]
2007-11-12 16:32
|
Mark,
I am not sure if you had a chance to visit RubyIBM project documentation page, but we do have a "Getting Started" that would describe most of what you were looking for:
http://rubyforge.org/docman/?group_id=2361
That document was uploaded when the IBM_DB gem was first released (0.6.0) and certainly needs some updates, but the most important aspects are already touched upon.
Oh, and you certainly don't need to apologies, it's us who need to do a better job in making the up-and-running experience better in the first place, and we do hope the promised refresh of the Starter Toolkit for DB2 on Rails version 3.0 would very much simplify all this.
Regarding your other questions:
1. Can you please provide your usage example on "packed and decimal fields".
2. As for "base library for the environment", I can only suspect you are referring to DB2 schema. If that is the case, then the usual approach is to declare the schema in config\database.yml and also follow Rails convention of model/table pluralization (i.e. no need to set_table_name):
development:
adapter: ibm_db
database: i5db
username: usrid
password: secret
host: i5db.domain.com
port: 446
schema: lib
|
By: Mark Davies
RE: Upgrade to IBM_DB 0.9.0 [ reply ]
2007-11-12 15:18
|
Hi Alex,
Thanks for your replies; OK, I still think it's a bit of a shame that Ruby access to the iSeries is going to have much bigger dependencies (licence, software) than Java/JDBC, but I can probably live with it, and hopefully force my customers to live with it too!
I guess a "how to" page somewhere detailing exactly what you need to do to get ibm_db working against iSeries would be useful - listing all the stuff I failed on: installing DB2 Connect, the need to copy the licence file, fixpack versions, binding, etc. That way, a developer should be able to get up and running with it in a day or so, and not have to waste your time for a week like I've been doing (for which my apologies!).
Returning to the usage of the adapter - what about the other issues I raised, the typing of packed and decimal fields - is there some way I can sort that? Also specifying the base library for the environment - is there a way of putting that into database.yml or config/environments/X.rb?
|
By: Alex Pitigoi
RE: Upgrade to IBM_DB 0.9.0 [ reply ]
2007-11-12 14:51
|
Mark, while I do understand your position w/r to having to bind some files when using DB2 Connect with any host AS/400 (or OS/390 system), this is just a natural requirement in order to store on i5 the access plans generated during precompilation. This may stand out as inconvenient in comparison to Java based access components, but it's an intrinsic part of the i5 access optimization.
That being said, a Rails application deployed in a customer environment would likely have to go through this one-time setup process, but that again should not surprising in an iSeries application environment.
|
By: Alex Pitigoi
RE: Upgrade to IBM_DB 0.9.0 [ reply ]
2007-11-12 14:24
|
|
Mark, as you probably noticed by now, IBM_DB adapter & driver requires DB2 8.1.15, 9.1.2 or above and that shouldn't be that surprising. This Ruby on Rails support was released independent from the DB2 client release and Rails framework mandated a "numerical value quoting" fix in DB2 CLI driver that was provided through those 2 fixpacks. Nobody likes a fixpack hassle, but that's how one can get underlying dependencies to support new features; it's the necessary burden.
|
By: Alex Pitigoi
RE: Upgrade to IBM_DB 0.9.0 [ reply ]
2007-11-12 14:09
|
Mark, you are quite right w/r to JTOpen capabilities and licensing. Following some in depth inquiry into this (less familiar product for me), I found out it's a self-sufficient package that does not rely on the JCC common JDBC driver. It's certainly centered on iSeries access and obviously does a good job in a development and production environment.
In comparison, DB2 Connect, through DB2 Driver for ODBC and CLI, is a more generic solution that interacts with all IBM Data Servers (LUW, i5, z/OS, Informix IDS) through the SQL API. Therefore, it's not only a different component (C binary, vs. Java bytecode) with a different programming interface, but it's a different set of capabilities with a different licensing policy.
What really matters w/r to licensing here is that Ruby support for all IBM Data Servers is delivered based on the DB2 CLI driver which provides such support across all platforms. As a result, Ruby and Rails development on i5 (and z/OS) is possible using a DB2 Connect license which could be obtained as you did through Partnerworld, or through the Starter Toolkit for DB2 on Rails (http://www.alphaworks.ibm.com/tech/db2onrails). The Starter Toolkit does contains a development license for i5 and z/OS, but I wouldn't recommend it before its new version 3.0 will provide deployment of the latest IBM_DB gem (currently version 2.1 deploys an obsolete version of the driver and adapter).
That being said, any subsequent deployment of a Ruby on Rails application using i5 (or z/OS) Data Servers requires appropriate DB2 connect licensing. Any of the development licenses provided by Partnerworld or Starter Toolkit would not suffice.
|
By: Mark Davies
RE: Upgrade to IBM_DB 0.9.0 [ reply ]
2007-11-11 17:45
|
|
Oh yes - and what does one do about libraries on the iSeries? My development files sit in library LIBMBD, but that library will change in test and production, and on customer sites. Is there any way of getting the library out of the ActiveRecord "set_table_name" and into e.g. database.yml?
|
By: Mark Davies
RE: Upgrade to IBM_DB 0.9.0 [ reply ]
2007-11-11 17:43
|
OK, I have my application up and running aganst the iSeries, which is very good news.
The adaptor is gratifyingly fast, so good work there chaps. Data typing is however a bit non-existent compared to MySQL - everything (alpha as well as zoned and packed decimal) is coming through to me as a string, and I'm having to convert types myself. Any ideas why this might be? It's not a problem on my small application, but I wouldn't want to do manual type conversion on a larger application/database.
The other thing that's still worrying me is the dependencies. If I ship this stuff to customers, are they going to have to go through the whole DB2 Connect - fixpack - connect - bind pain that I did?
|
By: Mark Davies
RE: Upgrade to IBM_DB 0.9.0 [ reply ]
2007-11-10 19:10
|
Argh! I don't believe it. SQLSTATE=42833 and
SQLCODE=-5016 is caused simply by having your library/table syntax wrong.
In SQL on iSeries I would normally do: SELECT * FROM LIB/FILE.
Via the CLI it must be: SELECT * FROM LIB.FILE. So in Ruby I must do set_table_name "LIB.FILE".
I seem to be up and running! Just got a small problem with column suffices to sort out, but nothing to do with ibm_db any more...
|
By: Mark Davies
RE: Upgrade to IBM_DB 0.9.0 [ reply ]
2007-11-10 18:31
|
This is difficult, isn't it? However I am now determined to update you with my progress.
I have seemingly resolved by previous problem, "Package NULLID .SYSSH200 was not found". The solution involved actually connecting to the iSeries using DB2 Connect. I used the "Connection Assistant" to do this, but nevertheless I found it to be a process of pain, with little detailed documentation to help, and several points in the process requiring information (e.g. "service name" - WTF?) which I did not have and found no resources to help me with. (In the end, a blank "service name" appeared to work).
That was not the end of the story. Although I got a successful "test connection" result in DB2 Connect, initially it had no effect on the "package" error reported in Rails. So I tried to catalogue the iSeries db in Db2 Connect, and found I got the same "package" error there - which I suppose was reassuring. The solution to this seemed to be to "bind" the DB2 utilities to the database, a process I managed to complete after half an hour or so of consulting DB2 Connect documentation which didn't appear to match the screens in my version of the software. (Why do I have to select the location for the bind files - how am I supposed to know where the software keeps them?)
More worryingly - are my customers going to have to do this? Install DB2 Connect (and its fixpack), and go through all this binding malarkey before my Rails server will work?
Anyway, result. After binding, I can now see the iSeries libraries in DB2 Connect, and on restarting Rails I move on to the next error:
ActiveRecord::StatementInvalid: [IBM][CLI Driver][AS] SQL0969N There is no message text corresponding to SQL error "-5016" in the message file on this workstation. The error was returned from module "QSQRPARS" with original tokens "TMEHDR". SQLSTATE=42833
SQLCODE=-5016: SELECT * FROM libmbd/tmehdr FETCH FIRST 1 ROWS ONLY
Websearches again don't return much for SQLCODE -5016, however I will attempt to plough on in what is left of my weekend. Wish me luck...
|
By: Mark Davies
RE: Upgrade to IBM_DB 0.9.0 [ reply ]
2007-11-10 16:28
|
OK, I think I've sorted my "Option type out of range" error. Another thread carried this - the problem was, I had installed DB2 Connect 9.1 but hadn't updated to fix pack 2. Why do I always forget IBM products must be fixpacked fixpacked and fixpacked??
So, I've updated to SP2 of the DB2 Client, which has presumably fixed whatever it was in the CLI that needed fixing, and that has got me another microstep further. Now I see this error from Rails:
ActiveRecord::StatementInvalid: [IBM][CLI Driver][AS] SQL0805N Package "NULLID .SYSSH200" was not found. SQLSTATE=51002
SQLCODE=-805: <My first SELECT statement>
This is getting quite discouraging! I'll continue my epic round of problem-diagnosis websearches, however do you have any further advice or succour for me Alex? Please??
|
By: Mark Davies
RE: Upgrade to IBM_DB 0.9.0 [ reply ]
2007-11-09 23:03
|
Hi again Alex,
I'm afraid I must take issue with you on the JTOpen licensing front: I've been using JTOpen in various versions for the last four years, and there is no requirement for any client-side driver install or licence files/jars. JTOpen is distributable under the IBM public licence, and provides not only pure-Java database access to the iSeries (JDBC) but also the ability to issue iSeries command calls, work with data queues, access the IFS, and so on. It works out of the box - drop in the JT400.jar and you've got JDBC access. There is no licence file within the JT400.jar apart from an HTML copy of the IBM Public Licence.
However I appreciate that to gain the productivity of Ruby I may have to suffer in other ways! So I have downloaded DB2 Connect 9.1 from the Partnerworld site and installed it. The install looked suspiciously identical to a DB2 Client install, and didn't give me any additional licence keys - although a couple of odd-looking "nodelock" files appeared in the SQLLIB/license folder. However I searched the install set and found files "db2conpe.lic" and "db2jcc_license_cisuz.jar", which I copied into my installed SQLLIB/license. I think you said that I should obtain a "db2consv_is.lic" file, but this did not seem to exist anywhere within the DB2 Connect install stuff.
Anyway, I restarted by Rails app and tried again and I have got another error, so perhaps the db2conpe.lic is working? The error now is:
"Failed to connect to the [S10B4E8A] due to: [IBM][CLI Driver] CLI0133E Option type out of range. SQLSTATE=HY092 SQLCODE=-99999"
As usual, any help very welcome...
Cheers
M
P.S. Good point on the port thing, didn't realise other server types ran on different ports. Port=446 it is.
|
By: Alex Pitigoi
RE: Upgrade to IBM_DB 0.9.0 [ reply ]
2007-11-09 17:56
|
Mark, regarding you TCP/IP default port question:
1. Yes, 446 is usually the default port for DB2 for i5 (although I suspect such default could be changed).
2. Would a default make sense to be used implicitly? Hardly possible, while the driver is expected to handle other Data Server defaults (LUW:50000, IDS:1526) without being able to discriminate the server target platform. Or maybe ask yourself the question: "Would I rather input in configuration platform name (i5)?"
|
By: Alex Pitigoi
RE: Upgrade to IBM_DB 0.9.0 [ reply ]
2007-11-09 17:46
|
Keefe, I believe any adapter developer can have access to a free DB2 Express-C system unless they're stuck on Mac OS X, while DB2 on Mac is in the works :-)
And yes, evidence builds up on the opportunity to support IBM Data Servers on JRuby through ActiveRecord-JDBC. It's only a matter of aligning some priorities, and in the meantime IBM_DB should serve as a fairly good alternative.
|
By: Alex Pitigoi
RE: Upgrade to IBM_DB 0.9.0 [ reply ]
2007-11-09 17:39
|
Mark,
DB2 CLI being the core component that provides client-server interaction, as I said below, it *is* bundled with all DB2 client and server packages, including DB2 Runtime Client.
So, once you have any DB2 Client installed, even the "bare-bones" DB2 Driver for ODBC and CLI, you have access to all IBM Data Server for which you have a license.
That being said, the license key required for DB2 CLI driver is available from any DB2 Connect package (your company might have purchased for your i5 Data Server).
A similar license is required for JDBC access too, and is normally available in the form of db2jcc_license_cisuz.jar license key associated with the db2jcc.jar Type 4 JDBC driver. It seems you might have had access through JTOpen for JDBC due to an existing JDBC license, but you might need to enquire your colleagues about an existing DB2 Connect license.
|
By: Keefe Hayes
RE: Upgrade to IBM_DB 0.9.0 [ reply ]
2007-11-09 16:46
|
|
This might be where using JRuby and ActiveRecord-JDBC with your JDBC drivers would be a good alternative. Except that there is not a valid jdbc_db2.rb adapter in ActiveRecord-JDBC. The adapter developers don't have access to a DB2 system. They have gotten Derby adapter written.
|
By: Mark Davies
RE: Upgrade to IBM_DB 0.9.0 [ reply ]
2007-11-09 16:31
|
Argh! Just realised I hadn't checked your third link. So the CLI driver *isn't* bundled with the DB2 Client!? So in fact the DB2 Client isn't any use to me, instead I must download and install the CLI driver... but then I still won't have the i5 licence key??
|
By: Mark Davies
RE: Upgrade to IBM_DB 0.9.0 [ reply ]
2007-11-09 16:14
|
Hi again Alex,
I'm not quite with you I'm afraid - as I said, I've got the DB2 Client (needs a fixpack, but I have it), but I've never used it to connect to iSeries and don't have this iSeries licence key you speak of.
I've never installed DB2 Connect - it's not required by the JTOpen JDBC driver, nor is the DB2 Client - and I don't really want to have to install it, although I will if I must.
So, can I get by with just the DB2 Client I have? If so, how can I get hold of the i5 licence key? I have Partnerworld access and can use that if necessary.
Thanks again for your help!
P.S. Oh yes, one thing I thought of - isn't 446 the default port for DB2 TCP/IP connections? If so, why do I need to specify it in database.yml?
|
By: Alex Pitigoi
RE: Upgrade to IBM_DB 0.9.0 [ reply ]
2007-11-08 22:31
|
Mark,
Let me clarify a bit: DB2 CLI (Call Level Interface) is the core DB2 client component that does all interaction from DB2 clients on LUW (Linux, Unix, Windows) to all IBM Data Servers (DB2 on LUW, i5, z/OS and Informix IDS).
This component is present in all DB2 packages and is now shipped independently. Please check "Support & Downloads / Download / Fixes, Updates, Drivers / Information Management - DB2 for LUW" at:
http://www-306.ibm.com/software/data/db2/ad/
http://www-1.ibm.com/support/docview.wss?uid=swg21255394&rs=0&cs=utf-8&context=SSEPGG&dc=D400&loc=en_US&lang=en&cc=US#r2
In the case of DB2 for i5 and z/OS Data Servers, historically, a DB2 Connect package was required, but ever since DB2 Driver for ODBC and CLI was released independently, only the license key is required (besides the CLI driver), in order to have access to such Data Servers. You should be able to use the license key provided by DB2 Connect as you probably had done in the past while implementing in Java. Once you have a valid license key for i5 you should also be able to run a Ruby script or a Ruby on Rails application in a LUW environment that only has DB2 Driver for ODBC and CLI installed (not even the DB2 Runtime Client). Please see DB2 CLI documentation:
http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.apdv.cli.doc/doc/t0023864.htm
I hope this is a more clear explanation, but please let me know if further help is required. Thanks.
|
|
|
