Declared Temporary Table on DB2 Z/OS

Jeffrey Schade

Declared Temporary Table on DB2 Z/OS
I have an interesting problem. My developer runs the following sequence
in SPUFI on TSO and it works perfectly but when we run it via an ODBC
connection such as Command Editor it never returns any rows as if the
INSERT never occurred even though all commands execute successfully:



DECLARE GLOBAL TEMPORARY TABLE RI_ADJACENCY_LIST

( PARENT_TABLE VARCHAR(36) ,

CHILD_TABLE VARCHAR(36) ) ;



INSERT INTO SESSION.RI_ADJACENCY_LIST

(PARENT_TABLE, CHILD_TABLE)

VALUES ('PARENT', 'CHILD');



SELECT COUNT(*) FROM SESSION.RI_ADJACENCY_LIST;



SELECT * FROM SESSION.RI_ADJACENCY_LIST;



Jeff

_________________________________________
Jeffrey Schade

Systems Consultant, Technology Engineering


Insurance Services Office, Inc.
545 Washington Boulevard
Jersey City, NJ 07310
Voice: (201) 469-3738

FAX: (201) 748-1500
[login to unmask email] <mailto:[login to unmask email]>




---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Phil Grainger

Re: Declared Temporary Table on DB2 Z/OS
(in response to Jeffrey Schade)
Your ODBC connection may be committing or connecting/disconnecting between each SQL statement

Do you have a monitor you can use to determine that they are all in the same UOW??

Phil Grainger
CA

________________________________

From: DB2 Data Base Discussion List on behalf of Schade, Jeffrey
Sent: Thu 18/01/2007 13:43
To: [login to unmask email]
Subject: [DB2-L] Declared Temporary Table on DB2 Z/OS



I have an interesting problem. My developer runs the following sequence in SPUFI on TSO and it works perfectly but when we run it via an ODBC connection such as Command Editor it never returns any rows as if the INSERT never occurred even though all commands execute successfully:



DECLARE GLOBAL TEMPORARY TABLE RI_ADJACENCY_LIST

( PARENT_TABLE VARCHAR(36) ,

CHILD_TABLE VARCHAR(36) ) ;



INSERT INTO SESSION.RI_ADJACENCY_LIST

(PARENT_TABLE, CHILD_TABLE)

VALUES ('PARENT', 'CHILD');



SELECT COUNT(*) FROM SESSION.RI_ADJACENCY_LIST;



SELECT * FROM SESSION.RI_ADJACENCY_LIST;



Jeff

_________________________________________
Jeffrey Schade

Systems Consultant, Technology Engineering


Insurance Services Office, Inc.
545 Washington Boulevard
Jersey City, NJ 07310
Voice: (201) 469-3738

FAX: (201) 748-1500
[login to unmask email] <mailto:[login to unmask email]>



--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Mauro Moschelli

Re: Declared Temporary Table on DB2 Z/OS
(in response to Phil Grainger)
Maybe odbc connection as some form of auto-commit? Rows in declared tables are deleted at commit, so if commit occurs after each statement the row is deleted after the insert statement.

HTH

Mauro Moschelli
Intesa Sanpaolo S.p.A.

IBM Certified Database Administrator - DB2 UDB V8.1 for z/OS


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of Schade, Jeffrey
Sent: Thursday, January 18, 2007 2:44 PM
To: [login to unmask email]
Subject: [DB2-L] Declared Temporary Table on DB2 Z/OS



I have an interesting problem. My developer runs the following sequence in SPUFI on TSO and it works perfectly but when we run it via an ODBC connection such as Command Editor it never returns any rows as if the INSERT never occurred even though all commands execute successfully:



DECLARE GLOBAL TEMPORARY TABLE RI_ADJACENCY_LIST

( PARENT_TABLE VARCHAR(36) ,

CHILD_TABLE VARCHAR(36) ) ;



INSERT INTO SESSION.RI_ADJACENCY_LIST

(PARENT_TABLE, CHILD_TABLE)

VALUES ('PARENT', 'CHILD');



SELECT COUNT(*) FROM SESSION.RI_ADJACENCY_LIST;



SELECT * FROM SESSION.RI_ADJACENCY_LIST;



Jeff

_________________________________________
Jeffrey Schade

Systems Consultant, Technology Engineering


Insurance Services Office, Inc.
545 Washington Boulevard
Jersey City, NJ 07310
Voice: (201) 469-3738

FAX: (201) 748-1500
<mailto:[login to unmask email]> [login to unmask email]



--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Walter Trovijo Jr (UOL)

Re: Declared Temporary Table on DB2 Z/OS
(in response to Mauro Moschelli)
Use ON COMMIT PRESERVE ROWS when declaring table. Your ODBC app is probably commiting after each sql.

Walter Trovijo Jr.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Jeffrey Schade

Re: Declared Temporary Table on DB2 Z/OS
(in response to Walter Trovijo Jr (UOL))
This corrected my problem. Thank you to all who replied.

Jeff
_________________________________________
Jeffrey Schade
Systems Consultant, Technology Engineering

Insurance Services Office, Inc.
545 Washington Boulevard
Jersey City, NJ 07310
Voice: (201) 469-3738
FAX: (201) 748-1500
[login to unmask email]


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of wtrovijo
Sent: Thursday, January 18, 2007 9:05 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Declared Temporary Table on DB2 Z/OS

Use ON COMMIT PRESERVE ROWS when declaring table. Your ODBC app is
probably commiting after each sql.

Walter Trovijo Jr.

------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

John Miller

Re: Declared Temporary Table on DB2 Z/OS
(in response to Jeffrey Schade)
In Command Editor goto menu Tools->Tools Settings then to tab Command
Editor. Ensure "Automatically commit SQL statements" checkbox is not
checked.



________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Schade, Jeffrey
Sent: Thursday, January 18, 2007 6:44 AM
To: [login to unmask email]
Subject: [DB2-L] Declared Temporary Table on DB2 Z/OS



I have an interesting problem. My developer runs the following sequence
in SPUFI on TSO and it works perfectly but when we run it via an ODBC
connection such as Command Editor it never returns any rows as if the
INSERT never occurred even though all commands execute successfully:



DECLARE GLOBAL TEMPORARY TABLE RI_ADJACENCY_LIST

( PARENT_TABLE VARCHAR(36) ,

CHILD_TABLE VARCHAR(36) ) ;



INSERT INTO SESSION.RI_ADJACENCY_LIST

(PARENT_TABLE, CHILD_TABLE)

VALUES ('PARENT', 'CHILD');



SELECT COUNT(*) FROM SESSION.RI_ADJACENCY_LIST;



SELECT * FROM SESSION.RI_ADJACENCY_LIST;



Jeff

_________________________________________
Jeffrey Schade

Systems Consultant, Technology Engineering


Insurance Services Office, Inc.
545 Washington Boulevard
Jersey City, NJ 07310
Voice: (201) 469-3738

FAX: (201) 748-1500
[login to unmask email] <mailto:[login to unmask email]>



------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm


The information transmitted is intended only for the addressee and may contain confidential, proprietary and/or privileged material. Any unauthorized review, distribution or other use of or the taking of any action in reliance upon this information is prohibited. If you receive this in error, please contact the sender and delete or destroy this message and any copies.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Jeffrey Schade

Re: Declared Temporary Table on DB2 Z/OS
(in response to John Miller)
Hi John,

This also works. To be safe I think I will recommend to Development that
they code the ON COMMIT PRESERVE ROWS option so that they get what they
want without having to set the AUOTCOMMIT off in all their ODBC tools.
Thanks for the additional information.

Jeff
_________________________________________
Jeffrey Schade
Systems Consultant, Technology Engineering

Insurance Services Office, Inc.
545 Washington Boulevard
Jersey City, NJ 07310
Voice: (201) 469-3738
FAX: (201) 748-1500
[login to unmask email]


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of John Miller
Sent: Thursday, January 18, 2007 12:53 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Declared Temporary Table on DB2 Z/OS

In Command Editor goto menu Tools->Tools Settings then to tab Command
Editor. Ensure "Automatically commit SQL statements" checkbox is not
checked.



________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Schade, Jeffrey
Sent: Thursday, January 18, 2007 6:44 AM
To: [login to unmask email]
Subject: [DB2-L] Declared Temporary Table on DB2 Z/OS



I have an interesting problem. My developer runs the following sequence
in SPUFI on TSO and it works perfectly but when we run it via an ODBC
connection such as Command Editor it never returns any rows as if the
INSERT never occurred even though all commands execute successfully:



DECLARE GLOBAL TEMPORARY TABLE RI_ADJACENCY_LIST

( PARENT_TABLE VARCHAR(36) ,

CHILD_TABLE VARCHAR(36) ) ;



INSERT INTO SESSION.RI_ADJACENCY_LIST

(PARENT_TABLE, CHILD_TABLE)

VALUES ('PARENT', 'CHILD');



SELECT COUNT(*) FROM SESSION.RI_ADJACENCY_LIST;



SELECT * FROM SESSION.RI_ADJACENCY_LIST;



Jeff

_________________________________________
Jeffrey Schade

Systems Consultant, Technology Engineering


Insurance Services Office, Inc.
545 Washington Boulevard
Jersey City, NJ 07310
Voice: (201) 469-3738

FAX: (201) 748-1500
[login to unmask email] <mailto:[login to unmask email]>



------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm


The information transmitted is intended only for the addressee and may
contain confidential, proprietary and/or privileged material. Any
unauthorized review, distribution or other use of or the taking of any
action in reliance upon this information is prohibited. If you receive
this in error, please contact the sender and delete or destroy this
message and any copies.

------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm