Support for DB2 databases?

I'm not having any luck so far. Maybe Sphericus can help (this is the first time I've seen the MTK, so I'm not really familiar with it and I might be missing something).

First of all, I don't see any 'replacement UDFs' in the MTK. My understanding that the MTK 'generates' code, it does not 'provide additional UDFs'.

What I did with the MTK was:

1) Read the doc
2) Start the MTK app
3) Created a new Project. For the 'From' platform I selected "MS SQL Server". (Other choices were Sybase, Oracle, Informix.)
4) Open up the SQL Translator, copied the query into it:

5) Clicked on the "Convert" button.

The conversion failed with various 'Unexpected Syntax' errors.

Then I tried setting the 'From' platform to Oracle, and got similar errors.

The end result is that I cannot get the MTK to convert this statement.

Sphericus, do you know if I'm doing something wrong?

Thanks.

There is NO code in the MTK. Take my word for it. All there is is two directories... "doc" (the documentation) and "jre" (the class files which make up the MTK itself).

I've now managed to get a very simple scenario to work.

But it does not like either form of concatenation:

Both of them choke with "Unexpected Syntax" on any kind of concatenation.

we'll have to wait for Sphericus for a real answer.

but we're not interested in concat / a replacement for concat.
we're just interested in a replacement for LIKE
so maybe try

SELECT mycolumn1 FROM mytable WHERE mycolumn2 LIKE mycolumn3;

or something like that... yeah. won't help, i'm pretty sure :/

There is UDF code in the MTK package.
All the UDF codes are in the root directory of the MTK installation, typically c:\MTK.
The UDF are in files *.UDF with the executables JAR files with similar names. A number of the LIKE functions are provided by the compiled java they provide. But certain functions hey were able to provide without the java.

Also, Larry, I believe that the output would be very similar or the same as the original as the MTK probably acknowledges that the new LIKE it has can do it without trouble.

If you have a look at those. I found there wasn't any LIKE UDF's for oracle, but there were for Sybase and MS SQL.

Also while we are on the point, I assume you are familiar with the differences between "DB2" and "DB2/400 (a.k.a. DB2 for iSeries)" ?

No idea why the SQL translator isn't working to be honest.

Summary of the LIKE / CONCAT issue:
------------------------------------
DB2's LIKE statement can't deal with COLUMN data on the right side. We use AND iam1.g_parentSequence LIKE iam0.g_parentSequence || iam0.g_itemId || '/%' in two places, a LIKE statement with COLUMN data on the right side.
There are two alternatives to deal with it: a replacement for the whole clause:
1. LOCATE(iam0.g_parentSequence || CHAR(iam0.g_itemId) || '/', iam1.g_parentSequence) = 1
2. replace DB2's LIKE with a user defined function (UDF) "MS7.isLike1", so we'd call $storage->getFunctionSql('LIKE', $likeCriterion)

Locate solution:
------------------
Pro's:
+ it's simple and it works
+ don't have to ship with .jar
Con's:
- it's a ridicoulos level of abstraction replacing a LIKE + CONCAT(column, data) with a new statement. Other DBMS (mysql, oracle, pg) need to replace the CONCAT
actually we have to find every occurrence of a LIKE statement in G2 and make sure the stuff on the right isn't a column. We can't guarantee correct functionality of DB2 with G2 if not each and every module has been inspected for LIKE statements.
- for DB2 we have to replace the whole statement, for other DBMS just the CONCAT, it's more complicated than now

LIKE replacment:
-------------------
Pro's:
+ We'd just have to introduce a getFunctionSql() case for LIKE and it guarantee to work with all LIKE statements, it's much cleaner
Con's:
- Is there a license issue with this .jar file? Can we ship it with our GPL code?
- we'd have to be consequent and replace all LIKE statements with getFunctionSql(), but actually we'd have to replace it only in 2 places

Just to be clear:
- Shipping G2 with an additional .jar isn't a problem. In our install code it would register / declare this UDF

What statements would need to be replaced?
- modules/core/classes/GalleryItemHelper_simple.class LIKE + CONCAT(columns)
- modules/core/classes/GalleryItemHelper_simple.class LIKE + CONCAT(columns) (quite the same

that's it...for now. I searched for grep -r LIKE *, so hopefully no statements used lower-case "like".

Let's also see what bharat thinks about the two alternatives.

First, I don't see how solution #2 is lighter in footprint or maintainability. As for the porting effort, that's what I'm doing now. Both solutions require us to use $storage->getFunctionSql().

Second, the licensing question is a good one. I don't think G2 can legally redistribute any part of it. I've pasted the MTK's EULA into a Word doc nd am attaching it here.

Third, we don't even know if it will work yet, because:

1) I haven't done any testing of it yet.

2) There are still any number of unknowns with this UDF. There doesn't even seem to be any documentation on how to use it.

3) There seems to be several different versions of this UDF, labelled:

- mtkms7.udf
- mtkms7ISeries.udf
- mtkms7ISeriesV5R2.udf
- mtksyb.udf
- mtksybISeries.udf
- mtksybISeriesV5R2.udf

Which version should I try to use? I dunno. What's the difference between them? Beats me. Why does iSeries have its own version of each of the UDFs? I have no idea.

4) If it breaks in the future, we don't have the java source. Do you really want to rely on IBM to support this? Read the EULA. ;-)

@License:
Unless the UDF is open source, we'd most probably have to ask during step 5 of the installer (db step) to download the jar and put it into dir so and so if DB2 was selected. then it's not problem for us.

@why is the LIKE replacement better for us?
it's easier to maintain than the LIKE + column / LIKE + CONCAT replacement.
for the LIKE replacement, we'd only have to add the LIKE case to the db abstraction classes and return the same in all existing db classes and return the MS7.LIKE in the DB2 class.
what would be needed for the LIKE + CONCAT replacement?
an example from GalleryItemHelper_simple.class:
we use the following for our current db abstraction:

	list ($ret, $concat) = $storage->getFunctionSql(
	    'CONCAT',
	    array('[GalleryItemAttributesMap=1::parentSequence]',
		  '[GalleryItemAttributesMap=1::itemId]',
		  '\'/%\''));
...
	$query = sprintf('...
          AND
          [GalleryItemAttributesMap=2::parentSequence] LIKE %s
...
        ', ..., $concat, ...);

for DB2, we need something like
[code[
list ($ret, $likeConcat) = $storage->getFunctionSql(
'LIKECONCAT',
array('[GalleryItemAttributesMap=2::parentSequence]',
'[GalleryItemAttributesMap=1::parentSequence]',
'[GalleryItemAttributesMap=1::itemId]',
'\'/\''));
...
$query = sprintf('...
AND
%s
...
', ..., $likeConcat, ...);[/code]
or something like:

	list ($ret, $concat) = $storage->getFunctionSql(
	    'CONCAT',
	    array('[GalleryItemAttributesMap=1::parentSequence]',
		  '[GalleryItemAttributesMap=1::itemId]',
		  '\'/\''));
	list ($ret, $likeConcat) = $storage->getFunctionSql(
	    'LIKECONCAT',
	    array('[GalleryItemAttributesMap=2::parentSequence]',
		  $concat));
...
	$query = sprintf('...
          AND
          %s
...
        ', ..., $likeConcat, ...);

The challenge would be to have a single replacement for DB2 and our existing classes.
we don't want a if DB2 else statement in our application code. so we want something like this:

	list ($ret, $concat) = $storage->getFunctionSql(
	    'CONCAT',
	    array('[GalleryItemAttributesMap=1::parentSequence]',
		  '[GalleryItemAttributesMap=1::itemId]',
		  '\'/\''));
	list ($ret, $likeConcat) = $storage->getFunctionSql(
	    'LIKECONCAT',
	    array('[GalleryItemAttributesMap=2::parentSequence]',
		  $concat));
...
	$query = sprintf('...
          AND
          %s
...
        ', ..., $likeConcat, ...);

and LIKECONCAT in mysql, pg and oracle would append a '%' if possible ('\'/\'' -> '\'/%\'' is not just appending. Maybe just concat it with another '\'%\''. And db2 would use locate() instead. or the other way: your DB2 LIKECONCAT function should check for '%' and remove it. and if there was a % at the beginning, you need to check for > 0 instead of = 1. well that would make your DB2 LIKECONCAT quite a bit more flexible and more attractive.

that would work for our LIKE + CONCAT. Maybe we should call this function LIKECOLUMNPLUS or so :/, our specific replacement is quite unflexible anyway, it's not a genenral like. there could be more and more statements that need a different replacement.

here the code needed if we used the UDF LIKE:

	list ($ret, $concat) = $storage->getFunctionSql(
	    'CONCAT',
	    array('[GalleryItemAttributesMap=1::parentSequence]',
		  '[GalleryItemAttributesMap=1::itemId]',
		  '\'/%\''));
	list ($ret, $like) = $storage->getFunctionSql(
	    'LIKE',
	    array('[GalleryItemAttributesMap=2::parentSequence]',
		  $concat));

...
	$query = sprintf('...
          AND
          %s
...
        ', ..., $likeConcat, ...);

so given that your Locate / likeconcat replacement would do checking for '%' and handle it correctly, the LIKE + CONCAT alternative is about as easy / small in code footprint as the UDF LIKE replacement.

the question is now:
when would the LOCATE() like replacement not work?
- e.g. if we have a like pattern like '%the beginning does not mattter but the last characters must match!' of course the pattern would be a result of concat'ing columns or it would just be a column.
this case could maybe be fixed with a more complicated SQL replacment where we also use = LENGTH(iam1.column) - LENGTH(match)
- e.g. a LIKE pattern like '%somewhere % somewhere later % and so on %'
have fun finding a replacment SQL for this. of course the pattern would be the result of concat or so.

whereas we can expect a UDF LIKE replacement to just work as expected, i.e. work as the LIKE operator in ms sql / oracle, ...

the UDF LIKE replacement would be something that we wouldn't have to care much about. if we find another statement that needs it, just plug it in. if we go with the locate(), we'd have to make sure that the locate() also works for the new case.

thanks for testing.
i guess you still could write a SQL/PL / Java UDF on your own for DB2 that does actually work, but i tend to "let your go" and accept the LOCATE replacemnt.

let's just shortly explore how such a PL/SQL function would look like:
- handle special characers _ and % as single character and multicharacter wildcards
http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html
-> _ matches exactly one character
-> % Matches any number of characters, even zero characters

- preferably 100% replaces LIKE (no getSqlFunction call required), but a functin with another name is also fine

from the sql 92 standard: http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

then on page 213 section 8.5 there's more.
- we don't need the escape character feature right now, so this wouldn't have to be implemented

<like predicate> ::=
              <match value> [ NOT ] LIKE <pattern>
                [ ESCAPE <escape character> ]

so we have a function with 2 operands, and we iterate through <pattern> character by character with current character c to match current character mo f m of <match value> (c++, m++ means to set c/m = next character in the match/pattern string):

while (c) {
  if c == '_') {
    // doing already everything after if elseif else clause
  } else if (c == '%') {
    if c == last character of <pattern>, return true;
    else { 
      c++;
      do: if new c != m, m++; while (m not end of string)
      if (m end of string) { return false; }
    }
  } else (not _ or %):
    if c != m,
     return false;
  }
 c++;
 m++;
} // end while c

i'm not a PL/SQL guru, but that should be feasible.
if someone knows DB2 SQL/PL and feels like implementing, please do.
else we go with LOCATE.

Larry Menard
is there anything else that needs to be handled, what's holding you off from running g2/db2 right now? any other issues?

Question: will any of the SQL in g2 use more than one '%' wildcard in a LIKE? E.g., WHERE ... LIKE 'The quick % fox jumped %'

Handling multiple '%' wildcards is significantly more difficult than if we limit ourselves to just one.

You're killing me, you know that?

The max size of a VARCHAR in DB2 is 32,762. The fact that it's FOR BIT DATA doesn't change that.

However, to use VARCHAR of more than 4000 will require a bufferpool and tablespace with a page size of more than 4K are created (the current default pagesize is 4K). That's something that could be done within g2, but it's probably much easier to ensure the User does so when they create the database.

OK, I think I have a working PL/SQL "g2_like" UDF that handles concatenated parms and multiple wildcards. (Actually the concatenation is done at the time UDF is called... the value received into the UDF is already concatenated.)

It returns 0 if the match is successful, 1 if the match is not successful.

I've tested a number of scenarios:

Any concerns about this, or can you suggest any other scenaios I should test?

I think I fell into the "0 = success" trap. I can easily switch the return values.

I'll test the new test scenarios tonight and let you know how it goes.

Thanks for the input.

I'm stumped on something. I've trimmed down my UDF to bare bones to illustrate this, hopefully somebody can tell me what I'm doing wrong.

(Note, DB2 CLP scripts such as the following, "--" indicates comments.)

With the outer 'if' commented out, the pattern matches successfully:

But if we un-comment that outer 'if', all of a sudden it starts to fail, and it suddenly seems to think the '%' is in position 12 (it is in fact in position 11).

Can anybody see anything wrong with the logic?

I reworked a number of things and the problem went away.

('%' and '_' are special to DB2 only when used in a LIKE predicate... in a garden-variety PL/SQL UDF they're just plain characters.)

FYI, I'm down to only 4 failing testcases out of 20. I think these will boil down to only 2 problems (if I'm lucky, only 1). Will keep you posted.

OK, all 20 testcases are now successful.

There might be better ways to write it, but I never claimed to be a hardcore Developer. As it is, I think it's fairly clean and understandable.

As this has been a significant piece of work, I'm attaching the UDF source, testcases, and testcase output here for your review.

P.S.: I'm not familiar with the use of the term "GPL" as a verb. Exactly what does that entail?

OK, feel free to add my name anywhere you wish.

My time is somewhat booked up for tomorrow, but I want to implement this and one more change to see how far that gets me, then I'll send you my current changes on Friday evening.

Thanks.

Yes, I will also provide my BIT_AND. It already functions as you describe, bit-by-bit, and the arguments are sequences of 32 bits.

Thanks for the description of BIT_OR, I had assumed it was more similar to BIT_AND (receives 2 sequences of bits).

I have no experience with user-defined aggregate functions, and haven't yet read Knut's article, so before I look into them I don't know if they ever were or are now supported. Yet another thing I have to learn on the fly.

I'll send you my latest changes (since my last CVS UPDATE) tomorrow. (Only things that work.)

In integrating the G2_LIKE() UDF into the G2 code I've hit a (hopefully minor) snag.

The SQL generated is:

But the || fails because iam0.g_itemId is not a string literal. It works if I wrap it in a CAST(... AS CHAR(10)):

The problem is that I can't always assume that the 3rd arg needs to be cast, can I?

One solution is if I can programmatically test the datatype for each arg in the getFunctionSql() code. Is that possible? Then I could wrap the CAST around only the necessary columns.

Yeah, good idea. But for some reason the ||s in my SQL don't seem to be going through the case 'CONCAT'. Any idea why that might be happening?

Also, if you have three values concatenated, will you not be wrapping a char() wrapper around another char() wrapper in the 2nd arg? The first time through the code it will wrap char() around $arg[0] and $arg[1]. The second time through the code it will wrap another char() around $arg[1] (which already has a char() wrapper) and a wrapper around $arg[2]. So you'll wind up with char($arg[0]) || char(char($arg[1])) || char($arg[2]). You might want to first test it to see if it's already being cast.

(Re the CAST($arg[x] AS CHAR(n)) versus CHAR($arg[x]), sorry, old habits die hard.

Never mind, I retract my questions... I found and fixed the problem, and yes I realized how the implode() works. I should have thought about it a bit more before posting.

OK, the update to case 'CONCAT' is now working fine, as is the G2_LIKE(). Thanks.

@unchanged g2 sql code: that would be great, but from the example for a user defined workaround aggregate function in DB2 from the link, it looks like the returned format of the MAX(BIT_OR(column)) is counterBIT_OR_RESULT, whereas the counter part can't be removed without rewriting the SQL of the whole query (subquery).

the problem is, that you have to print each intermediate result in your user defined function.

i guess i have an idea:

say SELECT BIT_OR(column)
outputs:
00000001INTERMEDIATE_BIT_OR_RESULT
00000002INTERMEDIATE_BIT_OR_RESULT
00000003INTERMEDIATE_BIT_OR_RESULT
...
00009701INTERMEDIATE_BIT_OR_RESULT

so we have a counter in the first x nr of characters. the INTERMEDIATE_BIT_OR_RESULT is a NR_OF_BITS long string of 0's and 1's.

so instead of
SELECT MAX(BIT_OR(column)) FROM FOO
which would return
00009701INTERMEDIATE_BIT_OR_RESULT

we do now:

SELECT MAX(BIT_OR(column)) - (COUNT(*) * 10^NR_OF_BITS) FROM foo
which wil return:
00009701INTERMEDIATE_BIT_OR_RESULT - (9701 * 10^NR_OF_BITS) which DB2 of course will return as
INTERMEDIATE_BIT_OR_RESULT

explanation:
10^NR_OF_BITS is 10 to the power of NR_OF_BITS.
so since the internal counter of your UDF and COUNT(*) will have the same value, count(*) will be 9701. so we can remove the stuff in front of the actual result by subtracting COUNT(*) * 10^NR_OF_BITS.

10^NR_OF_BITS will have to be computed in PHP and we can just write 'COUNT(*) * ' . pow(10, 32) since we're using 32 bits right now.

i really hope that works

@java:
that's fine, i guess.

@code formatting in the forums:
you're always using the quote tag. there's also a code tag which is basically a html pre. just replace quote with code in your tags

@MAX():
my MAX() is from Knut's approach. I read parts of his article and he implements UD aggregate functions with UDFs and the help of MAX().
We'd have to replace BIT_OR with getSqlFunction and there we could apply the MAX(BIT_OR(column)) - (COUNT(*) * constant) logic.

@BIT_OR():
BIT_OR() is a built-in aggregate function in mysql and we have our own implementation of it for postgresql and oracle.
So BIT_OR() works just like SUM(), AVG(), MAX(), and other aggregate functions in SQL.
e.g.
SELECT category, AVG(price)
FROM products
GROUP BY category;

for a table product, category, price
would return the average price for each category;
you get a single line per category in the result.

the following is also a fictive query:

SELECT itemId, BIT_OR(permission)
FROM itemPermissions
WHERE user = 'joe'
GROUP BY itemId;

for a table itemId, permission, user
will return for each itemId a single line indicating the sum of all permissions user 'joe' has for the item.
a single line, e.g.
if the table has the rows;

itemId   permisssion   user
---------------------------
     5   10000000      tom
     5   00100000      joe
     5   00000001      joe
    99   00000010      joe
    99   00100001      joe
    99   11000000      joe

it will OR all rows that match the where clause (joe) for each itemId (group by).
that's exactly like SUM(), sum() would some the 3 rows with itemId 5, user = joe,
but BIT_OR ORs these 3 values. here's the result of the query:

itemId   permission
----------------------
     5   00100101
    99   11000011

so, no: our BIT_OR(), what we need as a 1:1 replacement for the mysql BIT_OR, is an aggregate function that just returns a value. not a sequence, not prefixed by a counter.
but since i thought (after reading Knut's article) that this is not possible, we need a workaround.
applying the same scheme as Knut, I'm using a counter and MAX(). the counter will ensure that we pick the last intermediate result, because this last intermediate result is prefixed by the MAX counter value.
the "- (COUNT(*) * constant)" thing is my extension to Knut's article, such that we can do the whole thing with a simple getSqlFunction replacement and don't need to rewrite the original SQL.

@your alternative:
Well, i don't know what's possible in DB2. After reading Knut's article, it seems you have to do it that way. And I was thinking with such limits in mind.
If you found a solution that doesn't need such a workaround, fine. i'm just trying to help to solve the problem.

committed to cvs, get tomorrows nightly snapshot. you can't use anonymous CVS right now, since sf.net is having hardware problems and anonymous cvs is still in the state of a few days back.