Hi,

My company is switching from Microsoft SQL 2005 to IBM DB2 UDB 9.1.
I've been playing around with the environment on and off for about two
weeks now in my spare time, and have a bunch of questions. I've read
through this group, it has helped a bit, but I'm definitely still
struggling with some of the concepts and would appreciate some help.

For reference, I've been using Toad Freeware from a Windows XP machine
against a DB2 UDB 9.1 database running on AIX.

1. Case sensitivity: From what I understand and tested, commands are
case-insensitive (Select, Update, etc). However, anything in the where
clause seems to be case-sensitive (i.e. where last_name = "Smith" is
different that where last_name = "SMITH"). Reading online, the
solution seemed to use lcase() on the column. This works, but on the
off chance, is there a setting server-side to made this sort of
statement insensitive?

2. Can someone please explain "Begin Atomic"? I see it referenced
constantly but do not understand how it differs from "Begin".

3.Dynamic SQL: This seems to be a touchy subject. First off: I'm
really not concerned about performance at this point. For simplicity's
sake, how would one translate the following T-SQL into DB2? I know it
involves Cursor and Prepare, but can't seem to get it right.

declare @SQL as varchar(4000)
set @SQL = 'select * from table'
execute (@SQL)

4. I've tried using IBM's T-SQL to DB2 code converter with no luck.
Are there any other recommended resources for someone in my situation?
So far. I've downloaded the IBM redbook on this topic, Graeme
Birchall's DB2 Cookbook, and I've just ordered Understanding DB2:
Learning Visually with Examples and DB2 SQL PL: Essential Guide from
Amazon.

Thanks in advance,
T

Todd wrote:

> Hi,
>
> My company is switching from Microsoft SQL 2005 to IBM DB2 UDB 9.1.
> I've been playing around with the environment on and off for about two
> weeks now in my spare time, and have a bunch of questions. I've read
> through this group, it has helped a bit, but I'm definitely still
> struggling with some of the concepts and would appreciate some help.
>
> For reference, I've been using Toad Freeware from a Windows XP machine
> against a DB2 UDB 9.1 database running on AIX.

Toad is very highly regarded by many people. For SP development you should
check out the (Eclipse-based) Developer Workbench, which is a free download
from the IBM site but not installed with the DB2 client.

>
> 1. Case sensitivity: From what I understand and tested, commands are
> case-insensitive (Select, Update, etc). However, anything in the where
> clause seems to be case-sensitive (i.e. where last_name = "Smith" is
> different that where last_name = "SMITH"). Reading online, the
> solution seemed to use lcase() on the column. This works, but on the
> off chance, is there a setting server-side to made this sort of
> statement insensitive?

No, and neither should there be !!!

Beware of using LCASE() or UCASE() all over the place in SQL, as you will be
in table scan territory instantly with the resulting bad performance. If
you have columns you want to search on in either upper case or lower case,
then add a generated column to the table which uses LCASE() or UCASE() at
insert / update time to produce this, and then index and query on the
generated column.

>
> 2. Can someone please explain "Begin Atomic"? I see it referenced
> constantly but do not understand how it differs from "Begin".
>

It denotes a section of SQL/PL which will be executed and rollbacked as a
unit of work. If you had (say) three insert SQL statements within a BEGIN
ATOMIC ... END, and the third one failed, everything would be undone. With
just BEGIN ...END the first two would be left in situ.

> 3.Dynamic SQL: This seems to be a touchy subject. First off: I'm
> really not concerned about performance at this point. For simplicity's
> sake, how would one translate the following T-SQL into DB2? I know it
> involves Cursor and Prepare, but can't seem to get it right.
>

You may have come across the dynamic v static SQL debate. Many (especially)
ex-mainframe DB2 folks still believe that static SQL is the only way to
write SQL : but most of us have moved on from there and have learned to
deal with dynamic SQL.

> declare @SQL as varchar(4000)
> set @SQL = 'select * from table'
> execute (@SQL)

I'm assuming you mean in the context of a stored procedure ?

CREATE PROCEDURE MYSCHEMA.MYPROC
(
-- here you'd put input and output parameters
)
SPECIFIC MYPROC
DYNAMIC RESULT SETS 1
READS SQL DATA
LANGUAGE SQL
BEGIN
DECLARE MYCURSOR CURSOR WITH RETURN FOR
SELECT * FROM MYSCHENA.TABLE;
OPEN MYCURSOR;
END#

But don't use "SELECT *" as changing a table layout will break things (I
assume you just did this to minimise typing).

Calling this using -

CALL MYSCHEMA.MYPROC()

should return the table contents.

Note that I qualified my procedure and table with a schema. This is good
practice : you shouldn't create everything with the equivalent of dbo.

>
> 4. I've tried using IBM's T-SQL to DB2 code converter with no luck.
> Are there any other recommended resources for someone in my situation?
> So far. I've downloaded the IBM redbook on this topic, Graeme
> Birchall's DB2 Cookbook, and I've just ordered Understanding DB2:
> Learning Visually with Examples and DB2 SQL PL: Essential Guide from
> Amazon.

The last two books are thoroughly recommended. The first (Learning
Visually By Example) is IMO the best on the "admin" side of DB2, and the
SQL/PL book from the IBM press is a great resource. I think you'll find
that as you get into complex SQL you'll also keep turning to Graeme's
Cookbook. Lastly, make use of the Information Center on the web.

>
> Thanks in advance,
> T

You may also want to check out some other resources -

IDUG : http://www.idug.org (and the conference is in 3 weeks : if you are
going drop past and say hello at my presentation : F04).

IRC channel : #db2 on irc.freenode.net : not too many people around there
but those who are try to be helpful.

HTH

Phil Nelson
(teamdba@scotdb.com)

Todd wrote:
> Hi,
>
> 4. I've tried using IBM's T-SQL to DB2 code converter with no luck.
> Are there any other recommended resources for someone in my situation?
> So far. I've downloaded the IBM redbook on this topic, Graeme
> Birchall's DB2 Cookbook, and I've just ordered Understanding DB2:
> Learning Visually with Examples and DB2 SQL PL: Essential Guide from
> Amazon.
>
> Thanks in advance,
> T
>
I've used the IBM converter to convert a large application to UDB. The
tool can be very effective for converting code but frequently doesn't
appear to work at all. The project I did was attempted by an offshore
contractor who stated that less than 10% of the code would convert. I
was able to get it to convert better than 98% of the code.

Phil Sherman