DB2 vs MySQL ... Oh I have learned a lot! -- MIDRANGE-L

I am finding that I was quite spoiled with MySQL.

But as I always suspected, DB2 on i is the power house RDMS of the entire industry. Total FAN!

I am pleased with their implementation of Limit from MySQL. Would have been great if it had been implemented the same, and not so verbose...

as in this snippet from one of my scripts: "FETCH FIRST 80 ROWS ONLY" And what I read... this does not guarantee the same row with each call unless an "ORDER BY" is included.

I also understand that IBM has to be sure not to BREAK DB2 for others.

I understand IBM not wanting to change the character set from EBCDIC to UTF8. And I would never ask that of them, but at the same time, for someone that wants to use UTF8, to be in lock step with the rest of the world, at least give the option.

Although with the data sets that I am working with... I have not noticed a problem yet, and I have not had to do any data conversions.

In my thinking, once I get a SQL result from DB2 in my PHP script, I am simply comparing Strings To Strings at this point. Not sure if PASE or some other mechanism does any conversion or not.

I appreciate each and everyone's information.

And cheers to you Vern... as I do RTFM.... but when the manual does not answer a question, or makes it very difficult to solve a riddle.. and after several google attempts to find anything of relevance, I post in these forums.... I unlike you do not have the manuals in my head... and as most would understand, my projects are many, underfunded, and have meaningful time allotted. And I do not consider spending my down time reading through those horrible manuals.

I get so much more understand from examples when it comes to syntax.

I would prefer that you ignore me than refer me to the manual one more time. If you do not want to help, by all means, pass me by.

Thank You,
Rob

On 2016-01-13 09:52, Charles Wilt wrote:

The flip side of that is if you're used to working with DB2 for i, then
you've got a pretty good chance of the statements you're used to working on
another RDBMS...

DB2 for i follows the standards pretty closely. Exceptions being where it
absolutely has to have something special.

For instance, RCDFMT keyword of the CREATE TABLE statement. Though that's
not required and in fact relatively new.

Charles

On Wed, Jan 13, 2016 at 10:35 AM, Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>
wrote:

To expand on this, the DB2 team in Rochester get requests for this or that
syntax from another RDBMS, such as MySql or SQL Server or Oracle or
whatever.

They generally don't accept those requests, because they won't do a
"feature chase". There was even a request to have parity between all the
flavors of DB2 - mainframe and LUW (Linux/Unix/Windows). There IS a lot of
parity, but not everything. And not all features of the other flavors are
appropriate to our single-level-store architecture.

I think it is a trip down a yellow brick road to take something from, say,
MySQL, and see if it will work on IBM i. Decent question, perhaps, easily
answered by going to the manuals, which I'd have to do in many cases, to
see if some new parameter or clause has been added.

Cheers
Vern

On 1/13/2016 7:18 AM, Charles Wilt wrote:

Rob,

To answer you question. No. DB2 for i follows the standards.

Without an explicit column list, you have provide values for all columns.

Charles

On Tue, Jan 12, 2016 at 11:59 PM, Rob <rob.couch@xxxxxxxxxxxxxx> wrote:

Yes jim,

As I clearly stated... I know an insert standard syntax, as I
clearly
gave an example. My question, was... is there syntax anywhere close to
what MySQL offers.

That is a Yes, No, maybe kinda ... kind of answer.

Thank You
Rob

On 01/12/2016 08:28 PM, Jim It wrote:

Rob,

The DB2 for i syntax is:

INSERT INTO Table
(Column1, Column2, etc.)
Values (Value1, Value2, etc.)

Jim

________________________________________
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxx> on behalf of Rob <
rob.couch@xxxxxxxxxxxxxx>
Sent: Tuesday, January 12, 2016 7:11 PM
To: Midrange Systems Technical Discussion
Subject: INSERT INTO Table SET "Field"='Value" ;

This is valid in MySQL...

INSERT INTO Table SET "Field"='Value" ;

But I get a "Token SET is not a valid Token"...

I do not expect DB2 to support everything MySQL does... just looking
for some clarification.. is this possible? Or is my syntax just wrong?

I have a SaveRec function.... on an update... it uses the where clause
to look up the old rec and compare the fields and only replace those
that are different and log the changes, (who, when, table, fields,
before, after)...

MySQL made it convenient allowing a SET to be used with an INSERT as
well as an UPDATE... thus I only had to build a SET string and not
bother with a ( Fields ) & Values () string...

I did google right down to just INSERT INTO SET... got nothing...

Power8 v7r1
PHP
DB2

Thanks,
Rob

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

--

Your Out-Source IT Department,
Rob Couch
IT Serenity
214 682 7638
Skype: itserenity

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus