Rookie Questions about DB2 1

[slackah]

Hello

I am an old MS SQL Server guy that has been moved to a DB2 platform. No biggie. However, what is the flavor of SQL that is used to query on DB2?

Also, how does I know what UDB means, but how does that relate to DB2? What is EEE???

I told you these were rookie questions.

Thanks a ton!

 

[blom0344]

UDB = Universal Database (to distinguish from native DB2 versions)
EEE = Extended Enterprise Edition (version with most options)

If you are used to SQL server's T-SQL, then prepare for some differences. The best reference we can give you is here:

http://ourworld.compuserve.com/homepages/graeme_birchall/HTM_COOK.HTM

Your assumption that it is 'no biggie' is wrong. DB2 is in many ways more demanding, and in the end more rewarding.
Prepare having to pick up lots of new stuff

Ties Blom
Information analyst

 

[globularbob]

I don't know that IBM has a proprietary name for their version of sql. I know it's off the ANSI-92 standards, but they don't have a name like Oracle's PL/SQL if that's what you're asking?

 

[blom0344]

Globularbob,

You're mixing things up. PL/SQL is not ORACLE's SQL version.
It is a programming language (sort of) that allows you to write stored procedures. Yes, it does use SQL statements as blocks if required, but it is a different toolset.



Ties Blom
Information analyst

 

[globularbob]

Interesting. According to the SQL Bible (written by Alex Kriegel and published by Wiley), PL/SQL was introduced by Oracle for their platform. I've read propoganda of this sort as well from other sources as well.

 

[blom0344]

I did not deny that its an Oracle feature. It's purpose is quite different. To give you an idea , this is one PL/SQL procedure that I wrote for renumbering lines in a warehouse-table

PROCEDURE MODIFY_LINES_DIRECT(v_MOV_ID VARCHAR)AS
	   	  		CURSOR item_cursor IS SELECT MOV_PRO_CODE FROM STOCK_MOVEMENT
					   		 WHERE STOCK_MOVEMENT.MOV_ID = v_MOV_ID
			   			   	  ORDER BY MOV_PRO_CODE FOR UPDATE;
				I_item		  STOCK_MOVEMENT.MOV_PRO_CODE%TYPE;
				I_new_row_number 											NUMBER;
BEGIN

	 OPEN item_cursor;
	 LOOP
	 	  FETCH item_cursor INTO I_item;
		  EXIT WHEN item_cursor%NOTFOUND;
		  I_new_row_number := item_cursor%ROWCOUNT;
		  UPDATE STOCK_MOVEMENT
		  		 SET MOV_LINE = I_new_row_number + 1000
				 WHERE CURRENT OF item_cursor;
	END LOOP;
	CLOSE item_cursor;
-- Update Stock_movement table; reset mov_line with - 1000
	UPDATE STOCK_MOVEMENT
	SET STOCK_MOVEMENT.MOV_LINE = STOCK_MOVEMENT.MOV_LINE - 1000 WHERE STOCK_MOVEMENT.MOV_ID = v_MOV_ID;
	COMMIT;
END;

It uses blocks of raw SQL , but it is meant to be used in a program / package

Ties Blom
Information analyst

 

[slackah]

Thanks to both of you for answering my questions! You were both very helpful!

My comment that the transition was 'no biggie' was geared towards having to learn a somewhat new way of doing things. My DBA's and code reviewers are amazed at how fast I'm catching on. All I know is that when you're coding, people aren't shooting at you and that is a 'no biggie' in my book. But thanks for clarifying that.