Support for DB2 databases?

I'm already looking at the code in both those files. What do you think I'm putting my debug code in?

I think I've finally figured out for myself what you guys mean by "cachemap", so now I think I'm back on track.

Yes, I just reached the same conclusion myself. Confirmed in the DB2 docs:

The data type of the result is the same as the data type of the argument values, except that: 

  - The result is a large integer if the argument values are small integers. 
  - The result is double-precision floating point if the argument values are single-precision floating point.

If the type of the result is integer, the fractional part of the average is lost.

Yes, casting to double does seem to work.

	    query = '
	    SELECT
	    AVG(double([RatingMap::rating])),
	    COUNT([RatingMap::rating])
		FROM
		[RatingMap]
		WHERE
		[RatingMap::itemId] = ?
	    ...
	';
//	    AVG([RatingMap::rating]),

Does that mean an additional call to getFunctionSql()? Or will this work on other DBs?

Edit: fixing sample code snippet

Thanks, Valiant.

That should leave only the failures:

ConfirmImportControllerTest.testImportno_NO
ConfirmImportControllerTest.testImportfr_FR
ConfirmImportControllerTest.testImportKOI_8
ConfirmImportControllerTest.testConvertHtmlToBbcode (runs clean in isolation, only fails if run with KOI_8)
IndexDotPhpTest.testHttpRedirect

All of which have been deemed non-fatal.

Last Friday Dan Scott volunteered to do a test installation on Linux. If I don't hear today how it went for him, I'll ask him tomorrow.

So what more will be required to make DB2 officially supported?

As much as I would like to lose the 'Experimental' flag, let's wait until we see how a Linux implementation goes before we do.

Yes I've been meaning to clarify that version requirement... I'm pretty sure that Dan originally told me 8.2.2 was required for 'ibm_db2', but then later he said 8.2. I'll clarifty it ASAP.

Some thoughts regarding the Installer Step 5 changes for DB2...

Current requirement to put dbname in "Hostname" field:

Because of the parameter order difference between DB2 and other databases, we currently have to put the database name in the "Hostname" field. But there already is a "DB Name" field, so there's no need to make external changes to the page. I think that under the covers G2 should pass the args to the connect API in the right order for the database.

More flexibility for DB connections:

Since you have a Hostname field, I presume you want G2 to support connection to a database on remote systems.

DB2 does provide this ability in two ways, 'cataloged' and 'uncataloged'. 'Cataloged' means that the remote database particulars have been saved in a 'db alias' on the DB2 client, and that alias is used in the connection string. 'Uncataloged' means the remote database particulars are specified on the connection string, not stored in the DB2 client.

Here's some sample PHP code:

$driver = 'db2';
$server = 'localhost';
$user = 'lmenard';
$password = '........';
$database = 'sample';

if ($conn_type == 1)
{
  $db = ADONewConnection($driver);
  $db->Connect($server, $user, $password, $database);
}
if ($conn_type == 2)
{
  $dsn = "$driver://$user:$password@$server/$database";

  $db = ADONewConnection($dsn);
}
if ($conn_type == 3)
{
  $dsn = $driver . '://' . $user . ':' . $password . '@' . $server . '/' . $database . '?persist';

  $db = ADONewConnection($dsn);
}
// Connection type 4: Uncataloged connect string.
// See http://www.connectionstrings.com/ and the 'ibm_db2' db2_connect() doc
// (http://www.php.net/manual/en/function.db2-connect.php).
// I think this is an ODBC standard syntax, but I don't think ADOdb supports this,
// I don't even think PHP's ODBC API supports it, but 'ibm_db2' does.)
//
if ($conn_type == 4)
{
  $port = 50000;
  $uncatalogedDsn = 'DRIVER={IBM DB2 ODBC DRIVER};DATABASE=' . $database .
                    ';HOSTNAME=' . $server .
                    ';PORT=' . $port .
                    ';PROTOCOL=TCPIP' .
                    ';UID=' . $user .
                    ';PWD=' . $password;

  // Need to specify 2nd & 3rd args, even if empty.
  db2_connect($uncatalogedDsn, '', '');
}

Currently the DB2 support in G2 supports only 'cataloged' DB2 connections.

In order to support 'uncataloged' connections, we would need to ensure that all the required fields are on the Step 5 page. Currently, I believe all of them are there except Port Number.

If we could add an optional Port # field in some future release of G2, we could support uncataloged DB2 connections.

> isn't that an inconsistency in adodb that should be corrected, namely in your driver?

You're absolutely right. And even better, I already fixed it weeks ago. It is no longer necessary to put the database name in the "Hostname" field, I've just been doing it out of habit. I'll upddate the doc on codex.

> just use hostname:portnumber in the host name field

OK, I'll look into that.

Thanks!

FYI, I did hear from Dan yesterday (he actually does monitor this thread). He did try the install but hit a couple of problems:

1) It was he that discovered the SQL_CUR_USE_DRIVER being accidentally left in the ADOdb driver. He got an error because his PHP build did not have ODBC support. This is why I *really* want to see people test this on Linux. The more configurations we test on, the more bugs we'll shake out.

2) The build of DB2 he currently had was not recent enough to support the 'easy' creation of 32 K tablespaces (only available in fix pack 9 and up). As a result, I updated the doc on codex so that it includes instructions for others in this situation.

He said he'd try it again once he has a more current build of DB2.

Hi, all.

Way back on 2005-11-25, a problem with calling SQLJ_INSTALL was described thus by Larry Menard:

Dan Scott reproduced this problem with code that looked like this:

SQLCHAR *stmt1 = (SQLCHAR *)"CALL SQLJ.INSTALL_JAR('file:///home/dan/g2_db2.jar', 'g2db2')";
SQLCHAR *stmt2 = (SQLCHAR *)"CALL SQLJ.INSTALL_JAR('file:///home/dan/g2_db2.jar', 'g2db2', 1)";

(Both repro the problem.)

If you surround the CALL command with curly braces like this:

SQLCHAR *stmt1 = (SQLCHAR *)"{CALL SQLJ.INSTALL_JAR('file:///home/dan/g2_db2.jar', 'g2db2')}";
SQLCHAR *stmt2 = (SQLCHAR *)"{CALL SQLJ.INSTALL_JAR('file:///home/dan/g2_db2.jar', 'g2db2', 1)}";

then you don't get the error.

When the parameters inside a CALL statement are string literals, the ODBC escape clause delimeters {} must be used. Here is some documentation that describes this:
http://publib.boulder.ibm.com/infocenter/db2luw/v8/topic/com.ibm.db2.udb.doc/ad/t0007399.htm

As it happens, this issue has been raised as problematic by others recently too. APAR JR23175 has been opened to address this.

For information about APARs, you can go to the DB2 database support page:
http://www-306.ibm.com/software/data/db2/udb/support/
and search for the APAR for more information.

*Because JR23175 has just been opened, and is still being investigated, you won't be able to read the details yet. When this fix is released, then you will be able to see more.

I realize this answer comes long after you asked the question, but I hope it helps anyway.

Sarah Packowski

Oh, and I'll update the doc on codex ASAP.

No, we're not jumping back & forth. We are simply moving to the right solution now that we know what it is.

The permissions issue raised by Dan was because at that time we wanted the server to execute the SQL using a Command Line Processor (CLP) command (via an exec() call). The "{...}" solution makes all of that unnecessary... it requires no special permissions for the web server, and is now processed just like any other normal SQL statement via the ADOdb/ibm_db2 layer... no exec().

Hope that explains it clearly.

Edit: Just making it even more clear.

style review:
- the indention in configureStore was off, should be 1 tab + 4 spaces (total 12 spaces)
- multiline comments should have /* on the first line and nothing else

DB2Storage changes are in cvs.
README changed appropriately (removed java jar instructions).

reported typo will be fixed after g2.1

in cvs.

Good spotting. No, we no longer need it for DB2, and since it wasn't there before DB2, therefore it should be safe to remove it.

> Why does it need the 32k bufferpool?

because we needed a equivalent to mysql/postgres colunm types which allow us to store up to 32kbyte of character data (long texts like descriptions).
don't remember if the db-based page cache of g2 is also limited by that.

> slowness

could be db2 related, could be g2. have you tried mysql? could be g2 / AIX, since we don't have many AIX or DB2 users i don't have enough data points to judge the situation. also note that weird slowness was also observed for g2 / windows on some servers. there was never a user that actually profiled such slowness to find out what actually caused it.

> ibm_db2 driver

it's recommended by the IBM guys and superior to the odbc one in php. and it has less bugs that matter for us.
and yes, we use blob for compressed html page level caching in the db.

already using eaccelerator? if not, you should. and are you using g2's page level caching? site admin -> performance.

Hey, wow, quick responses here!

Um...never heard of eaccelerator..but found it and that looks really nice. I'll be giving that a try in the next few days.

I did have the page-cacheing thing turned on. And I ran the optimizer thing.

Monitor says i'm ok on the bufferpool.

The activate database thing very well might be doing it. Only apps I have using db2 here are mine, and I leave the conn open until the end of the script. From looking at some low-level monitoring, it's getting open/closed/open/closed many times during a single page execution of g2. It's importing my largest (3000+ pics) album right now from g1..I don't want to disturb it, but I'll be trying that. I did a re-org and runstat just after installation.
I've run that performance monitor a few times in the past for problems, what a bear, but very nice job it does in the end. Mabey I'll get into that.
If this works, I can migrate a customer of mine (30000+ images) and they'll be very happy.
Thanks for all the help..i'll keep you posted.

Well..i think the DB2 driver is OK. There's a few areas producing DB2 errors, anyhow...i've been playing with G2 all day, and I just can't seem to get it to perform better than G1. Using DB2 monitor, it was paging out the bufferpool, so I increased to 1000 pages. That helped, the Activate databased help...but still not so good. I've got some other pretty heavy enterprise apps running on db2 here, none of them work DB2 as hard as G2 does here, so I'm just not sure where the problem lies. If the page isn't cache, it's a painful 5-8second wait while I see db2sysc process to the top of the cpu list and my 4 cpus are about 25% busy each. I've got tables for a PDM with over a million entries with 103 attributes, couple of clobs in there, and i see nary a blip on my cpus when a query is run.

FYI, for the pagecache option, i kept getting SQL0102 (statment too long) whenever that was turned on in the performance tab. I had to increase my databases stmtheap from the default of 4096 to 6144.
--> db2 update db cfg for wwwdb using STMTHEAP 6144

pagecache definatly makes performance much better..but without it the db still seems to be overworked. Mabey if I have more time in a couple weeks, I can attempt to turn on some monitors to see what exactly is going on.

PDM=Product data managment (My job is to write add-ons for enterpise PDM system..sorry..i sometime forget that 99% of the world probably has no idea what I'm talking about with PDM's )

The pagecache option...under site-admin -> left menu performance -> set them to full acceleration. This is what causes entries into cachemap table, which was exceeding the limits of stmtheap parameter. cachemap table appears to store the raw html of a page, it's looked at first, if an entry is found for that page, it uses the page that was inserted into that table instead of generating a new page. It's cheating though because it's essentially a cache, while performance there is great, it appears that there's a bit more work on that function I think because if your logged in, the cached paged might try to save that login state, then a normal user hits the page and it re-generates anyway, or when you go back to another cached page as a logged in user, it now shows you as logged out, but your really logged in, hit the login link and it gives a blank page, probably getting confused that your really already logged in.

Anyhow..i did re-org & runstats after i imported everything.
It's just really really slow. If I got a page with 40 thumbs, it takes 2-4 seconds to load the page, the another 4-8 seconds as it retrieves each thumb.(remember I'm sitting on the server running mozilla.. no network bandwidth problems) I can watch each row of thumbs come as the page is being displayed, and the CPU's are 25% busy.
I know it's not my machine either, this machine is an mid-range enterprise server. The reason I mention the PDM's, i can show a whole automobile tree, every single part, nut, bolt fastener, with configuration filters (car options like sunroof,radio type,engine,trans..etc), many hundreds of db queries from 100's of tables, most of which have 30-50 attributes, some over 100, i see my cpu's blip up to 30-40% for a few seconds, and the whole car is shown. So to show 40 thumbs, should take just milliseconds, not seconds.
Somethings not right. I'm leaning towards some db2 tuning issue..but I'm not sure. I'm no tuning expert so I'll have to muttle through it. performance is still quite unaccectable.
So I think more investigation is needed here...

Hello,

I'm trying to discover if I've done the install of ibm_db2 correctly. I installed it quite some time ago, and have since upgraded to DB2 8.2 (FP11). The PHPINFO page says it was compiled '--with-ibm-db2=/home/db2inst1/sqllib' but in reading on this site and others, I understand that : "To build the ibm_db2 extension, the DB2 application development header files and libraries must be installed on your system."

How can I tell if this step has been done? I have ESE running on the box, but the client install (when I try through ./db2setp) fails with an earlier version message.

If I could just find out what directory, and what headers/libraries I need, I can query the system and find out.

Thanks in advance for any help.

Oh- also, is Dan Scott still around? I got some wonderful help from him when I did the install.

Be careful. ibm-db2 is different than ibm_db2, or rather IBM_DB2. I built both into my PHP. Initially I just had ibm-db2 which just links db2 with php at compile time so the odbc driver can work. the IBM_DB2 driver is very different.
This was my configure line:

As Larry mentions, it's a PHP extenstion, there's instructions for how to install the extension in the link he provided, or check php.net site for installing PECL extension which is where the ibm_db2 extension page links to anyway i think. IIRC, there's 2 ways, the add-a-extension way, and the build it into PHP at compile time (which I did, because the add-a-extenstion way didn't work for me on my AIX machine).

Anyway..just FYI ibm-db2 is just the odbc driver and not the ibm_db2 extension. You'll notice at compile time ibm-db2 takes the instance sqllib as it's input and IBM_DB2 takes the DB2 unload directory as it's input.

Alright. Mabey you can make more sense of these than me. I'm no DB2 expert here...with monitor I've usally only run with help from a DBA for a guy on phone from the toronto lab.
I don't really see anything wrong...aside from the slowness.
I ran a loop then executed some g2 pick(s) then stopped the loop. They are all separated by =================================== Next Second (even though there 4-5 per second). The first and last couple are probably nothing as I was moving my mouse back to te window to start or stop the thing.

http://snyderworld.org/ftp/g2mon

No rush here. I was gone all last week! I just tappreciate the help. Thanks.
I might try and look up a few things on DB2 performance people taught me in the past and see if those tell me anything.

Hi, Sphericus.

I did successfully test G2 on a Linux (SLES9) VMWare image that IBM provides, but the more testing done, the better. So if you have the time to do at least a quick install & sanity test on your physical Linux box, I'd appreciate it.

Thanks.

run the unit tests

http://127.0.0.1/gallery2/lib/tools/phpunit/index.php
click on the filter input field and hit enter

which is the same as
http://127.0.0.1/gallery2/lib/tools/phpunit/index.php?filter=

which will run unit tests for all installed modules.

therefore, first activate all g2 modules.

run the unit tests in your codebase installation, not in the multisite.