writing compound/complex sql

[db2admin]

hello,

is it possible to write compound sql without stored procedure or
trigger.
can i just run in command center of db2.

regards,
jagdip singh

[Knut Stolze]

db2admin wrote:

hello,

is it possible to write compound sql without stored procedure or
trigger.
can i just run in command center of db2.

Yes, you can.

--
Knut Stolze
DB2 z/OS Admin Enablement
IBM Germany

[dunleav1]

On Mar 12, 1:52 pm, "db2admin" <[e-mail]:

hello,

is it possible to write compound sql without stored procedure or
trigger.
can i just run in command center of db2.

regards,
jagdip singh

You can use the command editor or right click on database and pick
query tab.
You can write complex sql in either place.

[jefftyzzer]

On Mar 12, 11:05 am, "dunleav1" <[e-mail]:

On Mar 12, 1:52 pm, "db2admin" <[e-mail]:

hello,

is it possible to write compound sql without stored procedure or
trigger.
can i just run in command center of db2.

regards,
jagdip singh

You can use the command editor or right click on database and pick
query tab.
You can write complex sql in either place.

Hi, Jagdip.

I think that what you might be asking about is ultimately the BEGIN
ATOMIC block, which is a bit analogous to an anonymous block in
Oracle, if that's helpful to you. In DB2 such constructs are known as
inline SQL PL.

Here's a trivial example (inserts 10000 random integers into a table):

BEGIN ATOMIC
DECLARE C_CNTR INTEGER DEFAULT 0;--
WHILE C_CNTR < 10000 DO
INSERT INTO IX_TEST (C1) VALUES (CAST((RAND()*100) AS INTEGER));--
SET C_CNTR = C_CNTR + 1;--
END WHILE;--
END;

You can do lots of things in inline SQL PL that you can do in SPs,
such as declare variables, loops, and cursor FOR LOOPs, but there are
several things you can't do, such as declare exception handlers, DDL,
and COMMIT/ROLLBACK.

HTH,

--Jeff

[db2admin]

On Mar 12, 5:36 pm, "jefftyzzer" <[e-mail]:

On Mar 12, 11:05 am, "dunleav1" <[e-mail]:

On Mar 12, 1:52 pm, "db2admin" <[e-mail]:

hello,

is it possible to write compound sql without stored procedure or
trigger.
can i just run in command center of db2.

regards,
jagdip singh

You can use the command editor or right click on database and pick
query tab.
You can write complex sql in either place.

Hi, Jagdip.

I think that what you might be asking about is ultimately the BEGIN
ATOMIC block, which is a bit analogous to an anonymous block in
Oracle, if that's helpful to you. In DB2 such constructs are known as
inline SQL PL.

Here's a trivial example (inserts 10000 random integers into a table):

BEGIN ATOMIC
DECLARE C_CNTR INTEGER DEFAULT 0;--
WHILE C_CNTR < 10000 DO
INSERT INTO IX_TEST (C1) VALUES (CAST((RAND()*100) AS INTEGER));--
SET C_CNTR = C_CNTR + 1;--
END WHILE;--
END;

You can do lots of things in inline SQL PL that you can do in SPs,
such as declare variables, loops, and cursor FOR LOOPs, but there are
several things you can't do, such as declare exception handlers, DDL,
and COMMIT/ROLLBACK.

HTH,

--Jeff

thanks a lot for help
i really appreciate help
can i declare global temporary tables in inline SQL PL

[jefftyzzer]

On Mar 12, 3:05 pm, "db2admin" <[e-mail]:

On Mar 12, 5:36 pm, "jefftyzzer" <[e-mail]:

On Mar 12, 11:05 am, "dunleav1" <[e-mail]:

On Mar 12, 1:52 pm, "db2admin" <[e-mail]:

hello,

is it possible to write compound sql without stored procedure or
trigger.
can i just run in command center of db2.

regards,
jagdip singh

You can use the command editor or right click on database and pick
query tab.
You can write complex sql in either place.

Hi, Jagdip.

I think that what you might be asking about is ultimately the BEGIN
ATOMIC block, which is a bit analogous to an anonymous block in
Oracle, if that's helpful to you. In DB2 such constructs are known as
inline SQL PL.

Here's a trivial example (inserts 10000 random integers into a table):

BEGIN ATOMIC
DECLARE C_CNTR INTEGER DEFAULT 0;--
WHILE C_CNTR < 10000 DO
INSERT INTO IX_TEST (C1) VALUES (CAST((RAND()*100) AS INTEGER));--
SET C_CNTR = C_CNTR + 1;--
END WHILE;--
END;

You can do lots of things in inline SQL PL that you can do in SPs,
such as declare variables, loops, and cursor FOR LOOPs, but there are
several things you can't do, such as declare exception handlers, DDL,
and COMMIT/ROLLBACK.

HTH,

--Jeff

thanks a lot for help
i really appreciate help
can i declare global temporary tables in inline SQL PL- Hide quoted text -

- Show quoted text -

I'm afraid you can't.

--Jeff

[Brian Tkatch]

On 12 Mar 2007 15:49:42 -0700, "jefftyzzer" <je********@sbcglobal.net>
wrote:

>On Mar 12, 3:05 pm, "db2admin" <[e-mail]:

>On Mar 12, 5:36 pm, "jefftyzzer" <[e-mail]:

On Mar 12, 11:05 am, "dunleav1" <[e-mail]:

On Mar 12, 1:52 pm, "db2admin" <[e-mail]:

hello,

is it possible to write compound sql without stored procedure or
trigger.
can i just run in command center of db2.

regards,
jagdip singh

You can use the command editor or right click on database and pick
query tab.
You can write complex sql in either place.

Hi, Jagdip.

I think that what you might be asking about is ultimately the BEGIN
ATOMIC block, which is a bit analogous to an anonymous block in
Oracle, if that's helpful to you. In DB2 such constructs are known as
inline SQL PL.

Here's a trivial example (inserts 10000 random integers into a table):

BEGIN ATOMIC
DECLARE C_CNTR INTEGER DEFAULT 0;--
WHILE C_CNTR < 10000 DO
INSERT INTO IX_TEST (C1) VALUES (CAST((RAND()*100) AS INTEGER));--
SET C_CNTR = C_CNTR + 1;--
END WHILE;--
END;

You can do lots of things in inline SQL PL that you can do in SPs,
such as declare variables, loops, and cursor FOR LOOPs, but there are
several things you can't do, such as declare exception handlers, DDL,
and COMMIT/ROLLBACK.

HTH,

--Jeff

thanks a lot for help
i really appreciate help
can i declare global temporary tables in inline SQL PL- Hide quoted text -

- Show quoted text -

I'm afraid you can't.

--Jeff

But they can be DECLAREd beforehand for use within it.

B.

[jefftyzzer]

On Mar 13, 7:56 am, Brian Tkatch <N/Awrote:

On 12 Mar 2007 15:49:42 -0700, "jefftyzzer" <[e-mail]>
wrote:

On Mar 12, 3:05 pm, "db2admin" <[e-mail]:

On Mar 12, 5:36 pm, "jefftyzzer" <[e-mail]:

On Mar 12, 11:05 am, "dunleav1" <[e-mail]:

On Mar 12, 1:52 pm, "db2admin" <[e-mail]:

hello,

is it possible to write compound sql without stored procedure or
trigger.
can i just run in command center of db2.

regards,
jagdip singh

You can use the command editor or right click on database and pick
query tab.
You can write complex sql in either place.

Hi, Jagdip.

I think that what you might be asking about is ultimately the BEGIN
ATOMIC block, which is a bit analogous to an anonymous block in
Oracle, if that's helpful to you. In DB2 such constructs are known as
inline SQL PL.

Here's a trivial example (inserts 10000 random integers into a table):

BEGIN ATOMIC
DECLARE C_CNTR INTEGER DEFAULT 0;--
WHILE C_CNTR < 10000 DO
INSERT INTO IX_TEST (C1) VALUES (CAST((RAND()*100) AS INTEGER));--
SET C_CNTR = C_CNTR + 1;--
END WHILE;--
END;

You can do lots of things in inline SQL PL that you can do in SPs,
such as declare variables, loops, and cursor FOR LOOPs, but there are
several things you can't do, such as declare exception handlers, DDL,
and COMMIT/ROLLBACK.

HTH,

--Jeff

thanks a lot for help
i really appreciate help
can i declare global temporary tables in inline SQL PL- Hide quoted text -

- Show quoted text -

I'm afraid you can't.

--Jeff

But they can be DECLAREd beforehand for use within it.

B.- Hide quoted text -

- Show quoted text -

Brian brings up an excellent point, so let me clarify:

While you can't DECLARE a DGTT in a BEGIN ATOMIC block, you could
certainly DECLARE one outside of such a block in, say, Command Center,
and then refer to it in your BEGIN ATOMIC block, as long as the DGTT
and BEGIN ATOMIC block are executed within the same session.

DECLARE GLOBAL TEMPORARY TABLE SESSION.IX_TEST ( C1 INTEGER ) ON
COMMIT PRESERVE ROWS NOT LOGGED ON ROLLBACK DELETE ROWS WITH REPLACE
DB20000I The SQL command completed successfully.

BEGIN ATOMIC
DECLARE C_CNTR INTEGER DEFAULT 0;--
WHILE C_CNTR < 10000 DO
INSERT INTO SESSION.IX_TEST (C1) VALUES (CAST((RAND()*100) AS
INTEGER));--
SET C_CNTR = C_CNTR + 1;--
END WHILE;--
END
DB20000I The SQL command completed successfully.

SELECT COUNT(*) FROM SESSION.IX_TEST

1
-----------
10000

1 record(s) selected.

--Jeff

[Brian Tkatch]

On 13 Mar 2007 12:12:20 -0700, "jefftyzzer" <je********@sbcglobal.net>
wrote:

>On Mar 13, 7:56 am, Brian Tkatch <N/Awrote:

>On 12 Mar 2007 15:49:42 -0700, "jefftyzzer" <[e-mail]>
wrote:

>On Mar 12, 3:05 pm, "db2admin" <[e-mail]:
On Mar 12, 5:36 pm, "jefftyzzer" <[e-mail]:

On Mar 12, 11:05 am, "dunleav1" <[e-mail]:

On Mar 12, 1:52 pm, "db2admin" <[e-mail]:

hello,

is it possible to write compound sql without stored procedure or
trigger.
can i just run in command center of db2.

regards,
jagdip singh

You can use the command editor or right click on database and pick
query tab.
You can write complex sql in either place.

Hi, Jagdip.

I think that what you might be asking about is ultimately the BEGIN
ATOMIC block, which is a bit analogous to an anonymous block in
Oracle, if that's helpful to you. In DB2 such constructs are known as
inline SQL PL.

Here's a trivial example (inserts 10000 random integers into a table):

BEGIN ATOMIC
DECLARE C_CNTR INTEGER DEFAULT 0;--
WHILE C_CNTR < 10000 DO
INSERT INTO IX_TEST (C1) VALUES (CAST((RAND()*100) AS INTEGER));--
SET C_CNTR = C_CNTR + 1;--
END WHILE;--
END;

You can do lots of things in inline SQL PL that you can do in SPs,
such as declare variables, loops, and cursor FOR LOOPs, but there are
several things you can't do, such as declare exception handlers, DDL,
and COMMIT/ROLLBACK.

HTH,

--Jeff

>thanks a lot for help
i really appreciate help
can i declare global temporary tables in inline SQL PL- Hide quoted text -

>- Show quoted text -

>I'm afraid you can't.

>--Jeff

But they can be DECLAREd beforehand for use within it.

B.- Hide quoted text -

- Show quoted text -

Brian brings up an excellent point, so let me clarify:

While you can't DECLARE a DGTT in a BEGIN ATOMIC block, you could
certainly DECLARE one outside of such a block in, say, Command Center,
and then refer to it in your BEGIN ATOMIC block, as long as the DGTT
and BEGIN ATOMIC block are executed within the same session.

DECLARE GLOBAL TEMPORARY TABLE SESSION.IX_TEST ( C1 INTEGER ) ON
COMMIT PRESERVE ROWS NOT LOGGED ON ROLLBACK DELETE ROWS WITH REPLACE
DB20000I The SQL command completed successfully.

BEGIN ATOMIC
DECLARE C_CNTR INTEGER DEFAULT 0;--
WHILE C_CNTR < 10000 DO
INSERT INTO SESSION.IX_TEST (C1) VALUES (CAST((RAND()*100) AS
INTEGER));--
SET C_CNTR = C_CNTR + 1;--
END WHILE;--
END
DB20000I The SQL command completed successfully.

SELECT COUNT(*) FROM SESSION.IX_TEST

1
-----------
10000

1 record(s) selected.

--Jeff

Thanx for the clarification. :)

B.

[Frank Swarbrick]

>>On 3/13/2007 at 1:12 PM, in message
<11*********************@n33g2000cwc.googlegroups. com>,
jefftyzzer<je********@sbcglobal.netwrote:

BEGIN ATOMIC
DECLARE C_CNTR INTEGER DEFAULT 0;--
WHILE C_CNTR < 10000 DO
INSERT INTO SESSION.IX_TEST (C1) VALUES (CAST((RAND()*100) AS
INTEGER));--
SET C_CNTR = C_CNTR + 1;--
END WHILE;--
END

Can someone explain the use of '--' in dynamic compound SQL statements? It
appears to be necessary, in that it doesn't work without them, but I can't
find reference to what they are and when they need to be used.

Frank

[Dave Hughes]

Frank Swarbrick wrote:

>On 3/13/2007 at 1:12 PM, in message

<11*********************@n33g2000cwc.googlegroups. com>,
jefftyzzer<je********@sbcglobal.netwrote:

BEGIN ATOMIC
DECLARE C_CNTR INTEGER DEFAULT 0;--
WHILE C_CNTR < 10000 DO
INSERT INTO SESSION.IX_TEST (C1) VALUES (CAST((RAND()*100) AS
INTEGER));--
SET C_CNTR = C_CNTR + 1;--
END WHILE;--
END

Can someone explain the use of '--' in dynamic compound SQL
statements? It appears to be necessary, in that it doesn't work
without them, but I can't find reference to what they are and when
they need to be used.

Just guessing, but I'm assuming when the statement terminator is a
newline (as it is by default), the trailing '--' prevents the parser
from thinking that the statement has ended prematurely (by making the
newline part of a comment).

I typically use dynamic compound SQL in the CLP with a specific
alternate statement terminator (e.g. -td! or -td@ on the command line).
Under these circumstances, the trailing '--' is not required.

--
Dave.

[Lennart]

Frank Swarbrick wrote:

>>>On 3/13/2007 at 1:12 PM, in message

<11*********************@n33g2000cwc.googlegroups. com>,
jefftyzzer<je********@sbcglobal.netwrote:

>BEGIN ATOMIC
DECLARE C_CNTR INTEGER DEFAULT 0;--
WHILE C_CNTR < 10000 DO
INSERT INTO SESSION.IX_TEST (C1) VALUES (CAST((RAND()*100) AS
INTEGER));--
SET C_CNTR = C_CNTR + 1;--
END WHILE;--
END

Can someone explain the use of '--' in dynamic compound SQL statements? It
appears to be necessary, in that it doesn't work without them, but I can't
find reference to what they are and when they need to be used.

Serge gave an explanation for this in this thread:

http://groups.google.com/group/comp....a5698391281eee

/Lennart