Hi Peter,
Show quoted text> > 5. Edit Makefile and delete all other test and add manually t/746db2_400.t
>
> ^^^ never do this, editing the makefile is quite insane :)
>
> > 6. make test
>
> `prove -l t/746db2_400.t` is what you want
>
Ok, Understood, This it's my first time that help in something like
this, never gonna happen again. Thanks :)
Show quoted text> Consider what happens if you have a 5-row resultset, and you do:
>
> ->search({}, { rows => 3, offset => 4 });
>
> The resulting SQL will be roughly
>
> SELECT * FROM (
> SELECT * FROM (
> SELECT * FROM foo ORDER BY foo.id
> FETCH FIRST 7 ROWS ONLY
> )
> ORDER BY foo.id DESC
> FETCH FIRST 3 ROWS ONLY
> )
> ORDER BY foo.id
>
> instead of the expected 1 row you got 3(!).
You're right it's a horrible bug.
Show quoted text>
> Therefore what you need to do is
> 1) give me a reliable way to distinguish one db2 version from another, and
Jejeje, You broken my head with this homework, but at the end, the
solution it's a quite simple
After of broken my head (and my system) for many hours, and not found
any solution for obtain DB2 Version trough SQL, I look you patch and
advised from function get_info for obtain the system name separator,
then I make this simple perl program:
#!/usr/bin/perl -w
use DBI;
my $dbh = DBI->connect("dbi:ODBC:DSN","USER","password");
for($i=0; $i<=41;$i++){
print $i," => ",$dbh->func($i,GetInfo),"\n";
}
Note: I used 41 for you reference of separator.
So, the func->(18,GetInfo) answer the DB2(and OS version) in the next
format Version.Release.build_packs_and_other
I ran the test against two system: V5R1 and V5R3. (I don't have access
to another) this was the result:
V5R1 (Versión 5 release 1): 05.01.0010
V5R3 (Versión 5 release 3): 05.03.0014
(Another tip: with 16 Obtain the SYSTEM NAME )
Show quoted text> tell me which is the minimal version supporting ROW_NUMBER OVER. As I have
> shown above we always want to use RNO if it is available.
I read IBM Doc. and say's it's V5R4. I Talked with some friends to
research version of OS/DB (it' the same) but I have not positive
answers yet to probe. But in multiple sites on Internet the people ran
ROW_NUMBER OVER with V5R4.
Other tip:
V5R1 can not execute complex subquery Fetch First Not function, Order
By anidate no Function neither.
Another way to comunicate with AS400/DB2 it's with DBD::DB2 but it's
necesary the program (and licensing) the DB2Connector. and it's for
pay. for that I use ODBC.
The Versión of the ODBC iSeries that i use it's 5.04 (V5R4) but there are V5R7.
Show quoted text> 2) do some research and see if there are any other ways to do offset-limiting
> with your version of DB2 (again, since FETCH FIRST is potentially buggy).
>
Until now, nothing. I find the use of rrn() but it's not
functionally, by example:
Assuming one table (artistid,name) and 5 records:
Select rrn(artist) as row,* from artist:
row artistid name
1 1 Carlos
2 2 Pedro
3 3 Juan
4 4 Peter
5 5 Charlie
Next delete one id by example artistid 2
Delete from artist where artistid = 2
Now Select:
Select rrn(artist) as row,* from artist:
row artistid name
1 1 Carlos
3 3 Juan
4 4 Peter
5 5 Charlie
You See? This it's 'cause the rrn() it's a position relative when was
insert the record and identify each row simply and unique.
Show quoted text> > Again sorry for my english.
>
> This is the 3rd email in a row where you apologize for your decent english,
> can you please stop that? I can understand you and you can understand me,
> ergo your english is just fine :)
I learned by my self, with nobody to practice, so, I don't know if
people understand me well. But if you say that wer'e OK :)
Thanks a lot.
