SQL Server DBA working with DB2

Some things that I jotted down while doing some DB2 data migrations

You’ve probably had a go with another DBMS system at one time or another; I have recently with a JDE/DB2 project. Here are a few things that I jotted down…maybe one or more of the following might help someone one of these days. (Sure would have helped me). Otherwise, I hope that you find maybe a bit of humor in my post, or if nothing else, some solace knowing that you work with the greatness of Microsoft SQL Server!!!

Most all strings are nvarchar

                -- Expect to have to convert for destination
                -- Use data conversion or derived column task when moving data in SSIS

TOP doesnt work

                -- Have to use FETCH FIRST n ROWS ONLY
                -- FETCH FIRST, however, doesn't work in an expression. :( Still figuring this one out. Maybe use GROUP BY? LOL

Julian date

                -- They seem to use a lot of these in DB2
                -- Will need a conversion, something like DATE(DIGITS(DECIMAL(YourDateGoesHere + 1900000,7,0)))
                -- Julian date started January 1,4712 B.C.? Wow, that had to been around the time of Moses, no?

Indexed columns

                -- Have part of a column usually indexed
                -- Example: ItemShort of nvarchar (5) is indexed, and is a subset of ItemLong (nvarchar (10)), which is not indexed
                -- Seems like a waste of space, I/O to me.

Cool stuff

                -- Trim keyword - no need for LTRIM(RTRIM)) fun. Trim does both.
                -- Pages can be set at 4,8,16,32kb page sizes to match data access method. Sa-weeeeeeeeeeeeeet!

Don't forget all of the old-timey stuff

                --  No PKs on tables
                -- "Self referencing" tables
                --  Lots of defaults of char(32)

No GETDATE()

                -- Need a DATE(CURRENT TIME), anyone?
                -- Doesn't work well in an expression. For instance, try plugging into Julian date formula, above.             

ISNULL()

                -- Didn’t work
                -- But NULLIF() did. Interesting.
                -- Didn't search on this one, maybe you can find it.

*
                -- Acts a bit differently
                -- Two or more tables joined must use alias.* to get all columns; SQL Server, one * is enough for all columns.

String concatenation

                -- Remember to use two "pipes" ||
                -- Can't overload + operator. Huh?
                -- CONCAT works also                                       

CASE

                -- Works about the same
                -- Can't use Column = CASE.
                -- Think I read somewhere that "Column = CASE" will be deprecated in SQL Server anyway

Errors from DB2 engine

OUTER JOIN

                -- Use LEFT, RIGHT
                -- I always thought “OUTER” was ANSI compliant...doesn't work in DB2 (or at least the version that I'm working on)

File

                -- Get ready for tables to be called files
                -- Wonder if they like records instead of rows, too?!!!

Primary Keys

                -- Usually, someone actually took the time to identity/define/implement the natural key. Nice.
                -- No identities! Would make Celko giddy no doubt.
CAST

                -- Works
                -- Didn't try CONVERT. Thinking that I won't.

Naming conventions

                -- Lots of strange ones
                -- JDE for instance...table names have a random prefix, such as AA
                -- Column names are also random. Key might be XXX
                -- So column name is AAXXX
                -- Table name could be AA12345
                -- Example query:  SELECT AAXXX FROM AA12345 WHERE AAXXX = 'ABCDE12345&'

                   Ugh. Very nice, no???!!! 

fullselect            

                    -- Represents UNION and UNION ALL? Strange

Lee

-----------------------------------------

Ok – can I get back to what I was doing now???