Hi, I am going to use a legacy DB2 on zOS as database in a banking project. I am proficient in programming on Oracle. Also I've used MySQL and SQL Server for many years but know nothing about DB2 and their SQL dialect and procedural language for writing stored procedures and functions.
I am looking for good resources to learn DB2 architecture + SQL dialect and their procedural language.
Thank you very much
There is a bunch of information on the DB2 Infocenters hosted at IBM. The Infocenter pages are version specific, here is a link to an Infocenter including information on DB2 UDB for z/OS v8 and DB2 v9.1 for z/OS:
Assuming you don't have a z10 EC and licences to use DB2/z at home, first step is to get DB2/LUW (the Linux/Unix/Windows version). The Express edition is here.
Then head on over to publib, the first site anyone should go to for IBM product related information.
And the Redbooks are another very good source of information. IBM employees frequently get time off to do these (I say "time off" but it's actually very gruelling, believe me).
As for the mainframe product, it's not always an exact match for LUW but it is close. Stored procedures can be written in any of the languages available on the mainframe (we mostly use REXX) and I think you can also use all the UNIX (USS) toolchain as well if you'd prefer bash, Perl and tools you may be more familiar with.
You will already know the basic principles of tables and sql from ORACLE.
There are numerous annoying differences in SQL function names and some keywords but that shouldnt slow you down too much.
Internally DB2 is vastly different from ORACLE especially in the way storage is allocated and the way locking and transactions are implemented. This should not bother you too much unless thay expect you to do some intense performance and tuning work.
The main areas of difference are specific to z/OS rather than DB2. Firstly most mainframe programs are written in COBOL or DB2 to run inside either CICS or IMS transaction monitors (think J2EE containers but for COBOL) and usually these programs use "STATIC" sql. So its definately worth reading the manual on how staic sql programs are written and implemented. The programming is actually easier as the precompiler does most of the hard work and delivers the data to actual fields in your program, but, there is extra messing around woth DBRMs, basiclilly the SQL is stripped from the source code and stored in a file, before you run a program the file must be loaded into the target database (using BIND PLAN ) and at this point the optimisation and access plan is done so when you come to run your program there is an access plan ready built and waiting.
The second major pain is you will need to learn JCL. Which is a pretty unique hangover from the very first 360 series circa 1968. Think of it as a very primative ant script!