DB2 Stored Procedures And UDFs: A Primer.pdf

Database Security and Auditing: Protecting Data Integrity and Accessibility Chapter 3 Administration of Users.\n \n \n \n \n "," \n \n \n \n \n \n Overview of JDBC and Pro*C 1 Overview of JDBC,Pro*C and Oracle connectivity on Omega CSE 5330 \u2013 Database Systems.\n \n \n \n \n "," \n \n \n \n \n \n Native Support for Web Services \uf0ab Native Web services access \uf0ab Enables cross platform interoperability \uf0ab Reduces middle-tier dependency (no IIS) \uf0ab Simplifies.\n \n \n \n \n "," \n \n \n \n \n \n Database Systems: Design, Implementation, and Management Tenth Edition Chapter 11 Database Performance Tuning and Query Optimization.\n \n \n \n \n "," \n \n \n \n \n \n Chapter Oracle Server An Oracle Server consists of an Oracle database (stored data, control and log files.) The Server will support SQL to define.\n \n \n \n \n "," \n \n \n \n \n \n Understanding the CORBA Model. What is CORBA? \uf077 The Common Object Request Broker Architecture (CORBA) allows distributed applications to interoperate.\n \n \n \n \n "," \n \n \n \n \n \n Java Stored Procedures in DB2 for OS\/390 Open, Portable Application Logic Julian Stuhler DB2\/CICS\/IMS GUIDE May 2000.\n \n \n \n \n "," \n \n \n \n \n \n Announcements Read JDBC Project Step 5, due Monday.\n \n \n \n \n "," \n \n \n \n \n \n By Lecturer \/ Aisha Dawood 1. \uf09e You can control the number of dispatcher processes in the instance. Unlike the number of shared servers, the number of.\n \n \n \n \n "," \n \n \n \n \n \n Course materials may not be reproduced in whole or in part without the prior written permission of IBM. 5.1 \u00a9 Copyright IBM Corporation 2008 DB2 9 Fundamentals.\n \n \n \n \n "," \n \n \n \n \n \n CICS-Int1 Introduction to On-Line BATCH \u2013 Transactions are accumulated into groups (batches) before processing. ON-LINE \u2013 Interactive Transactions entered.\n \n \n \n \n "," \n \n \n \n \n \n Database Systems Slide 1 Database Systems Lecture 5 Overview of Oracle Database Architecture - Concept Manual : Chapters 1,8 Lecturer : Dr Bela Stantic.\n \n \n \n \n "," \n \n \n \n \n \n Overview of JDBC and Pro*C 1 CSE 5330 \u2013 Database Systems.\n \n \n \n \n "," \n \n \n \n \n \n Computer Emergency Notification System (CENS)\n \n \n \n \n "," \n \n \n \n \n \n Oracle 10g Database Administrator: Implementation and Administration Chapter 2 Tools and Architecture.\n \n \n \n \n "," \n \n \n \n \n \n Introduction to the Adapter Server Rob Mace June, 2008.\n \n \n \n \n "," \n \n \n \n \n \n Oracle Data Integrator Procedures, Advanced Workflows.\n \n \n \n \n "," \n \n \n \n \n \n Triggers and Stored Procedures in DB 1. Objectives Learn what triggers and stored procedures are Learn the benefits of using them Learn how DB2 implements.\n \n \n \n \n "," \n \n \n \n \n \n Introduction to the new mainframe \u00a9 Copyright IBM Corp., All rights reserved. Chapter 12 Understanding database managers on z\/OS.\n \n \n \n \n "," \n \n \n \n \n \n Course materials may not be reproduced in whole or in part without the prior written permission of IBM. 5.1 \u00a9 Copyright IBM Corporation 2008 DB2 9 Fundamentals.\n \n \n \n \n "," \n \n \n \n \n \n Database Design and Management CPTG \/23\/2015Chapter 12 of 38 Functions of a Database Store data Store data School: student records, class schedules,\n \n \n \n \n "," \n \n \n \n \n \n F15 Philip K. Gunning, DGI Anaheim, CA Sept , 2002 Fundamentals of DB2 SQL Procedures.\n \n \n \n \n "," \n \n \n \n \n \n ICS 321 Fall 2010 SQL in a Server Environment (i) Asst. Prof. Lipyeow Lim Information & Computer Science Department University of Hawaii at Manoa 11\/1\/20101Lipyeow.\n \n \n \n \n "," \n \n \n \n \n \n Programmatic SQL Shaista Khan CS 157B. Topic Embedded SQL statements in high-level programming languages.\n \n \n \n \n "," \n \n \n \n \n \n Database Systems Design, Implementation, and Management Coronel Morris 11e \u00a92015 Cengage Learning. All Rights Reserved. May not be scanned, copied or.\n \n \n \n \n "," \n \n \n \n \n \n Data Sharing. Data Sharing in a Sysplex Connecting a large number of systems together brings with it special considerations, such as how the large number.\n \n \n \n \n "," \n \n \n \n \n \n Process Architecture Process Architecture - A portion of a program that can run independently of and concurrently with other portions of the program. Some.\n \n \n \n \n "," \n \n \n \n \n \n Dr Gordon Russell, Napier University Unit Embedde SQL - V2.0 1 Embedded SQL Unit 5.1.\n \n \n \n \n "," \n \n \n \n \n \n JS (Java Servlets). Internet evolution [1] The internet Internet started of as a static content dispersal and delivery mechanism, where files residing.\n \n \n \n \n "," \n \n \n \n \n \n Database Access Using JDBC BCIS 3680 Enterprise Programming.\n \n \n \n \n "," \n \n \n \n \n \n CSI 3125, Preliminaries, page 1 SERVLET. CSI 3125, Preliminaries, page 2 SERVLET A servlet is a server-side software program, written in Java code, that.\n \n \n \n \n "," \n \n \n \n \n \n Chapter 5 Introduction To Form Builder. Lesson A Objectives \uf07d Display Forms Builder forms in a Web browser \uf07d Use a data block form to view, insert, update,\n \n \n \n \n "," \n \n \n \n \n \n Development Overview Pertemuan 11 Matakuliah: T0413 Tahun: 2009.\n \n \n \n \n "," \n \n \n \n \n \n Stored Procedures \/ Session 4\/ 1 of 41 Session 4 Module 7: Introducing stored procedures Module 8: More about stored procedures.\n \n \n \n \n "," \n \n \n \n \n \n Text TCS INTERNAL Oracle PL\/SQL \u2013 Introduction. TCS INTERNAL PL SQL Introduction PLSQL means Procedural Language extension of SQL. PLSQL is a database.\n \n \n \n \n "," \n \n \n \n \n \n SQL Triggers, Functions & Stored Procedures Programming Operations.\n \n \n \n \n "," \n \n \n \n \n \n Copyright \u00a9 2016 Ramez Elmasri and Shamkant B. Navathe.\n \n \n \n \n "," \n \n \n \n \n \n 7.5 Using Stored-Procedure and Triggers NAME MATRIC NUM GROUP Muhammad Azwan Bin Khairul Anwar CS2305A Muhammad Faiz Bin Badrol Shah CS2305B.\n \n \n \n \n "," \n \n \n \n \n \n Oracle Database Architectural Components\n \n \n \n \n "," \n \n \n \n \n \n MQ Series Cross Platform Dominant Messaging sw \u2013 70% of market\n \n \n \n \n "," \n \n \n \n \n \n DEPTT. OF COMP. SC & APPLICATIONS\n \n \n \n \n "," \n \n \n \n \n \n 1 DB2 Access Recording Services Auditing DB2 on z\/OS with \u201cDBARS\u201d A product developed by Software Product Research.\n \n \n \n \n "," \n \n \n \n \n \n Chapter 14: System Protection\n \n \n \n \n "," \n \n \n \n \n \n Web Technologies IT230 Dr Mohamed Habib.\n \n \n \n \n "," \n \n \n \n \n \n Chapter 5: Advanced SQL Database System concepts,6th Ed.\n \n \n \n \n "," \n \n \n \n \n \n Introduction What is a Database?.\n \n \n \n \n "," \n \n \n \n \n \n DB2.\n \n \n \n \n "," \n \n \n \n \n \n MQ Series Cross Platform Dominant Messaging sw \u2013 70% of market\n \n \n \n \n "," \n \n \n \n \n \n Chapter 8 Advanced SQL.\n \n \n \n \n "," \n \n \n \n \n \n Chapter 11 Managing Databases with SQL Server 2000\n \n \n \n \n "]; Similar presentations

DB2 Stored Procedures and UDFs: A Primer.pdf

> i've been thrown into a pit with DB2 and have to start writing things such> as tables, indexes, stored procedures, triggers, etc. The online reference> is only so helpful. The two pdf manuals are only so helpful. Googling is> only so helpful.> > So let's start with some simple SQL constructs, that i know so very well> in SQL Server, that seem to be like pulling teeth in DB2.> > 1. Selecting a value> > SQL Server:> SELECT 'Hello, world!'> (1 row(s) affected)

(I'm consciously not quoting anything.. fresh start)DB2 supports variables in the context of stored procedures, functions, methods, triggers and "dynamic compound statements".DB2 does not support "global variables" (and I thought SQL Server doesn't either, so I'm surprised you have an issue.DB2 supports the SQL/PSM standard for procedural logic.To learn about this language I strongly recommend:"DB2 SQL PL" by Paul Yip et al: =Y&cds2Pid=9030&isbn=0131477005I think the name SYSIBM.SYDUMMY1 is rooted in the fact that it returns 1 row.The VALUES clause is actually very powerful when embedded in the from clause.E.g. instead of doing:T-SQL:SELECT * FROM(SELECT 5UNIONSELECT 6) AS Xyou can do:SELECT * FROM (VALUES (5), (6)) AS X.This T-SQL: SELECT @@spid AS ProcessIDis NOT column aliasing.This is a SET statement.You can either use:SET ProcessID = spid;orVALUES spid INTO processID;Note the INTO clause. The same principle works for this T-SQL consruct:

This construct is like declaring a stored procedure in that eachstatement within the block must be terminated with semi-colon, whilethe block as a whole counts as a single SQL statement and must beterminated with some alternate character (hence why I used the -td!switch in the example above to set the statement terminator to bang).Where it differs from a stored procedure is that the ATOMIC keywordafter BEGIN is mandatory. ATOMIC indicates that the entire block ofinstructions will be executed in a single transaction (hence "atomic").Therefore, you can't use COMMIT / ROLLBACK within the block (onlyoutside it).However, I suspect mere syntactic differences are not the major problemhere. You're thinking of SQL in a procedural manner (which I guess isperfectly fine for SQL Server but will complicate things for youhorribly in DB2). You need to think of SQL as a "functional" language,not an "imperative" (procedural) language.Therefore, instead of writing something like this: BEGIN ATOMIC DECLARE var1 INTEGER; DECLARE var2 INTEGER; SET var1 = (SELECT afield FROM table1); SET var2 = (SELECT anotherfield FROM table2 WHERE yetanotherfield =var1); INSERT INTO table3 VALUES (var2); END!It'd be considered a lot more "normal" (at least, under DB2) to writesomething like this: INSERT INTO table3 SELECT anotherfield FROM table2 WHERE yetanotherfield = (SELECT afield FROM table1);If you're familiar with functional programming (Lisp, Haskell, ML,etc.), note the similarities: * No variable declarations * Expressions wrapped within each other (SELECT in a SELECT in an INSERT) instead of separate statements executed in an explicit order * Execution order determined "naturally" (i.e. evaluation of theouter most expression implicitly evaluates inner expressions)If you want to become comfortable with DB2's implementation of SQL, youneed to start thinking in this "functional" manner. That's not to sayit's all like this; as you've already discovered, there are storedprocedures, triggers and such like which are fairly procedural in theirnature.You might be able to get away with the BEGIN ATOMIC statement mentionedabove for a lot of things, but I'd encourage you to avoid it whereverpossible. As Knut mentioned in his post the DB2 optimizer will work alot better without procedural logic (again, this ties into thefunctional programming analogy).Don't give up on the VALUES expression either. The VALUES expressionallows you to generate a constant set (scalar or vector) within SQL.For example: db2 => VALUES 1; 1 ----------- 1 1 record(s) selected. db2 => VALUES 1, 2; 1 ----------- 1 2 2 record(s) selected. db2 => VALUES ('A', 1), ('B', 2), ('C', 3); 1 2 - ----------- A 1 B 2 C 3 3 record(s) selected.To answer your question about changing the names of the fieldsgenerated by the VALUES expression: SELECT * FROM ( VALUES ('A', 1), ('B', 2), ('C', 2) ) AS TEMP(LETTER, NUMBER); LETTER NUMBER ------ ----------- A 1 B 2 C 2 3 record(s) selected.VALUES itself has no way of controlling the names of the fields of theset it creates, but the fields can be aliased by the enclosingexpression (in this case a SELECT expression).The above example could also be written using "common tableexpressions" (something introduced in ANSI SQL-99, and implemented inDB2 v6 (?) if I recall correctly): WITH TEMP(LETTER, NUMBER) AS ( VALUES ('A', 1), ('B', 2), ('C', 2) ) SELECT * FROM TEMP; LETTER NUMBER ------ ----------- A 1 B 2 C 2 3 record(s) selected.Common table expressions can make a query involving a lot ofsub-SELECTs a hell of a lot more readable by defining all thesub-SELECTs before the main body of the query.Ahh, I've just read that common table expressions have been added tothe latest version of SQL Server (2005?), so maybe you're familiar withthem already?Incidentally, the VALUES expression as detailed above, and common tableexpressions are not available on DB2 for z/OS, just the Linux / Unix /Windows version. Weird.Anyway, hopefully the above will be enough to get one or two (perhapseven three!) lines of SQL working in DB2 :-)HTH,Dave.