DB2, SQL PL newbie question

SQL PL newbie question

Author	Message
Thomas Theakana #1 / 9	SQL PL newbie question Is it possible to run procedural code from the db2 prompt (like the PL/SQL statements on SQL Plus in Oracle)? I want to do something similar to the following: begin declare some variable use cursors to fetch values update the database using sql statments end Actually, I need to port an Oracle database application and for that I have to port several PL/SQL code snippets to work with DB27.2. Any help to do this would be highly appreciated. Thomas.
Wed, 11 Aug 2004 09:50:55 GMT

Larry Menar #2 / 9	SQL PL newbie question Sure. Look in the "sqllib\samples\sqlproc" directory for examples. -- Larry Menard IBM Workstation Database (DB2) Performance Team Defender of Geese and of all things Natural Quote: > Is it possible to run procedural code from the db2 prompt (like the > PL/SQL statements on SQL Plus in Oracle)? > I want to do something similar to the following: > begin > declare some variable > use cursors to fetch values > update the database using sql statments > end > Actually, I need to port an Oracle database application and for that I > have to port several PL/SQL code snippets to work with DB27.2. Any > help to do this would be highly appreciated. > Thomas.
Wed, 11 Aug 2004 22:33:28 GMT

Serge Riela #3 / 9	SQL PL newbie question db2 -td% BEGIN ATOMIC DECLARE a INT; FOR cur AS SELECT c1 FROM T WHERE c2 = a DO SET a = a + cur.c1; END FOR; INSERT INTO T VALUES(5000, a); END % OK I have no clue what the use of this piece of logic would be, but it will work on V7.2 :-) Cheers Serge -- Serge Rielau DB2 UDB SQL Compiler Development IBM Software Lab, Canada
Thu, 12 Aug 2004 22:51:34 GMT

Thomas Theakana #4 / 9	SQL PL newbie question Thanks a ton to both Serge and Larry. This is the exact info I wanted. Also I received an Oracle-to-DB2 porting document from IBM support. Quote: > db2 -td% > BEGIN ATOMIC > DECLARE a INT; > FOR cur AS SELECT c1 FROM T WHERE c2 = a DO > SET a = a + cur.c1; > END FOR; > INSERT INTO T VALUES(5000, a); > END > % > OK I have no clue what the use of this piece of logic would be, but it > will work on V7.2 :-) > Cheers > Serge
Sat, 14 Aug 2004 01:59:28 GMT

Thomas Theakana #5 / 9	SQL PL newbie question Hi Serge, Is it required to use a cursor, even to query db and populate one variable? Is there a better way to implement the following logic? db2 (cont.) => for cur as select count() as cnt from employee do db2 (cont.) => set x = cur.cnt+30000; db2 (cont.) => end for; db2 (cont.) => update employee set salary=x; I was trying to do the equivalent of following Oracle PL/SQL code snippet: select count() into x from employee; x := x+30000; update employee set salary=x; Quote: > db2 -td% > BEGIN ATOMIC > DECLARE a INT; > FOR cur AS SELECT c1 FROM T WHERE c2 = a DO > SET a = a + cur.c1; > END FOR; > INSERT INTO T VALUES(5000, a); > END > % > OK I have no clue what the use of this piece of logic would be, but it > will work on V7.2 :-) > Cheers > Serge
Sat, 14 Aug 2004 09:19:42 GMT

Serge Riela #6 / 9	SQL PL newbie question Hi Thomas, Finally someone who likes efficient SQL :-) SELECT INTO is an equivalent to SET. However SELECT INTO is not enabled in the dynamic compound. The direct translation of what you presented: BEGIN ATOMIC DECLARE x INT; SET x = (select count() into x from employee); SET x = x+30000; update employee set salary=x; END % Or BEGIN ATOMIC DECLARE x INT; SET x = (select count() into x from employee) + 30000; update employee set salary=x; END % Or in pure SQL. UPDATE employee set salary=(select count(*) into x from employee) + 30000; Note that the original (silly) FOR loop does NOT open a cursor in a dynamic compound statement. The technology used is "inline" SQL PL. What you write is one big honking "query". Variable references are correlations (no bindin, bindout, context switches, etc...) Cheers Serge -- Serge Rielau DB2 UDB SQL Compiler Development IBM Software Lab, Canada
Sat, 14 Aug 2004 22:30:02 GMT

Thomas Theakana #7 / 9	SQL PL newbie question Thanks Serge. It seems, the "into x" part is not needed in the SQL statements. Without them, your code works as expected. But gives error otherwise. So I assume that it is a typo. Quote: > Hi Thomas, > Finally someone who likes efficient SQL :-) > SELECT INTO is an equivalent to SET. However SELECT INTO is not enabled in the dynamic > compound. > The direct translation of what you presented: > BEGIN ATOMIC > DECLARE x INT; > SET x = (select count() into x from employee); > SET x = x+30000; > update employee set salary=x; > END > % > Or > BEGIN ATOMIC > DECLARE x INT; > SET x = (select count() into x from employee) + 30000; > update employee set salary=x; > END > % > Or in pure SQL. > UPDATE employee set salary=(select count(*) into x from employee) + 30000; > Note that the original (silly) FOR loop does NOT open a cursor in a dynamic compound > statement. > The technology used is "inline" SQL PL. What you write is one big honking "query". Variable > references are correlations (no bindin, bindout, context switches, etc...) > Cheers > Serge
Sun, 15 Aug 2004 02:01:11 GMT

Thomas Theakana #8 / 9	SQL PL newbie question Thanks Serge. It seems, the "into x" part is not needed in the SQL statements. Without them, your code works as expected. But gives error otherwise. So I assume that it is a typo. Quote: > Hi Thomas, > Finally someone who likes efficient SQL :-) > SELECT INTO is an equivalent to SET. However SELECT INTO is not enabled in the dynamic > compound. > The direct translation of what you presented: > BEGIN ATOMIC > DECLARE x INT; > SET x = (select count() into x from employee); > SET x = x+30000; > update employee set salary=x; > END > % > Or > BEGIN ATOMIC > DECLARE x INT; > SET x = (select count() into x from employee) + 30000; > update employee set salary=x; > END > % > Or in pure SQL. > UPDATE employee set salary=(select count(*) into x from employee) + 30000; > Note that the original (silly) FOR loop does NOT open a cursor in a dynamic compound > statement. > The technology used is "inline" SQL PL. What you write is one big honking "query". Variable > references are correlations (no bindin, bindout, context switches, etc...) > Cheers > Serge
Sun, 15 Aug 2004 02:01:12 GMT

Serge Riela #9 / 9	SQL PL newbie question Oops.. indeed.. The famous copy-pase bug -- Serge Rielau DB2 UDB SQL Compiler Development IBM Software Lab, Canada
Sun, 15 Aug 2004 03:57:12 GMT

Page 1 of 1

[ 9 post ]