Odd Create Table Issue - DB2 V7 for z/OS

Jay Reavill

Odd Create Table Issue - DB2 V7 for z/OS
Hello all,

I have a scenario occurring that doesn't make sense to me. We set the
sqlid, create a differently qualified table, drop the qualified table, and
then attempt to create another version of the same tablename with a default
creator. This results in a -679 on the create of the 2nd version eventho
it has a different creator than the first table that was dropped. The
first table being in drop pending status makes sense because a commit has
not occurred. But why is DB2 considering the 2nd version the same as the
first? And what's up with the object name it's complaining about? That
doesn't match anything. This is happening on a number of tables doing the
same thing. And for all of them the object name is truncated at 8
characters and a 'R' is placed where the underscore would have been. Also,
everything works fine if a commit is added after the drop.

SET CURRENT SQLID = 'APPDBA'
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION

CREATE TABLE PPMS2D.TMP_FUNCTS LIKE PPMS2D.FUNCTS
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION

DROP TABLE PPMS2D.TMP_FUNCTS
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION

CREATE TABLE TMP_FUNCTS LIKE PPMS2D.FUNCTS
SQL ERROR DURING EXECUTE IMMEDIATE
DSNT408I SQLCODE = -679, ERROR: THE OBJECT APPDBA.TMPRFUNC CANNOT BE
CREATED BECAUSE A DROP IS PENDING ON THE OBJECT
DSNT418I SQLSTATE = 57006 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXICTS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 105 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000069' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC
INFORMATION

Any ideas?! What am I missing?

Thanks,
Jay


----------------------------------------
Jay Reavill DBA
Certegy Card Services
11601 Roosevelt Blvd.
St. Petersburg, FL. 33716
Office (727) 227-2144
----------------------------------------





------------------------------------------------------------------------------
This message contains information from Certegy, Inc which may be confidential and privileged. If you are not an intended recipient, please refrain from any disclosure, copying, distribution or use of this information and note that such actions are prohibited. If you have received this transmission in error, please notify by e:mail [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: Odd Create Table Issue - DB2 V7 for z/OS
(in response to Jay Reavill)
DDL always uses your PRIMARY id and NEVER the current id

So, the default owner for a created table does NOT change just because
you do a SET CURRENT SQLID


Phil Grainger
CA
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Jay Reavill
Sent: 01 December 2005 17:17
To: [login to unmask email]
Subject: [DB2-L] Odd Create Table Issue - DB2 V7 for z/OS

Hello all,

I have a scenario occurring that doesn't make sense to me. We set the
sqlid, create a differently qualified table, drop the qualified table,
and then attempt to create another version of the same tablename with a
default creator. This results in a -679 on the create of the 2nd
version eventho it has a different creator than the first table that was
dropped. The first table being in drop pending status makes sense
because a commit has not occurred. But why is DB2 considering the 2nd
version the same as the first? And what's up with the object name it's
complaining about? That doesn't match anything. This is happening on a
number of tables doing the same thing. And for all of them the object
name is truncated at 8 characters and a 'R' is placed where the
underscore would have been. Also, everything works fine if a commit is
added after the drop.

SET CURRENT SQLID = 'APPDBA'
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION

CREATE TABLE PPMS2D.TMP_FUNCTS LIKE PPMS2D.FUNCTS DSNT400I
SQLCODE = 000, SUCCESSFUL EXECUTION

DROP TABLE PPMS2D.TMP_FUNCTS
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION

CREATE TABLE TMP_FUNCTS LIKE PPMS2D.FUNCTS SQL ERROR DURING
EXECUTE IMMEDIATE DSNT408I SQLCODE = -679, ERROR: THE OBJECT
APPDBA.TMPRFUNC CANNOT BE CREATED BECAUSE A DROP IS PENDING ON THE
OBJECT
DSNT418I SQLSTATE = 57006 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXICTS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 105 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000069' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC
INFORMATION

Any ideas?! What am I missing?

Thanks,
Jay


----------------------------------------
Jay Reavill DBA
Certegy Card Services
11601 Roosevelt Blvd.
St. Petersburg, FL. 33716
Office (727) 227-2144
----------------------------------------





------------------------------------------------------------------------
------
This message contains information from Certegy, Inc which may be
confidential and privileged. If you are not an intended recipient,
please refrain from any disclosure, copying, distribution or use of this
information and note that such actions are prohibited. If you have
received this transmission in error, please notify by e:mail
[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

Jay Reavill

Re: Odd Create Table Issue - DB2 V7 for z/OS
(in response to Phil Grainger)
Hmm, well it's not using my id (which this job is running under) as the
default, it's using the current sqlid APPDBA (it's in the error message).
And the first table is not defaulted, it's qualified with creator PPMS2D.
So the end result should be 2 tables with the same name, but different
creators.

----------------------------------------
Jay Reavill DBA
Certegy Card Services
11601 Roosevelt Blvd.
St. Petersburg, FL. 33716
Office (727) 227-2144
----------------------------------------




"Grainger, Phil"
<Phil.Grainger@C To: [login to unmask email]
A.COM> cc:
Sent by: "DB2 Subject: Re: [DB2-L] Odd Create Table Issue - DB2 V7 for z/OS
Data Base
Discussion List"
<[login to unmask email]
.ORG>


12/01/2005 12:26
PM
Please respond
to "DB2 Database
Discussion list
at IDUG"





DDL always uses your PRIMARY id and NEVER the current id

So, the default owner for a created table does NOT change just because
you do a SET CURRENT SQLID


Phil Grainger
CA
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Jay Reavill
Sent: 01 December 2005 17:17
To: [login to unmask email]
Subject: [DB2-L] Odd Create Table Issue - DB2 V7 for z/OS

Hello all,

I have a scenario occurring that doesn't make sense to me. We set the
sqlid, create a differently qualified table, drop the qualified table,
and then attempt to create another version of the same tablename with a
default creator. This results in a -679 on the create of the 2nd
version eventho it has a different creator than the first table that was
dropped. The first table being in drop pending status makes sense
because a commit has not occurred. But why is DB2 considering the 2nd
version the same as the first? And what's up with the object name it's
complaining about? That doesn't match anything. This is happening on a
number of tables doing the same thing. And for all of them the object
name is truncated at 8 characters and a 'R' is placed where the
underscore would have been. Also, everything works fine if a commit is
added after the drop.

SET CURRENT SQLID = 'APPDBA'
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION

CREATE TABLE PPMS2D.TMP_FUNCTS LIKE PPMS2D.FUNCTS DSNT400I
SQLCODE = 000, SUCCESSFUL EXECUTION

DROP TABLE PPMS2D.TMP_FUNCTS
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION

CREATE TABLE TMP_FUNCTS LIKE PPMS2D.FUNCTS SQL ERROR DURING
EXECUTE IMMEDIATE DSNT408I SQLCODE = -679, ERROR: THE OBJECT
APPDBA.TMPRFUNC CANNOT BE CREATED BECAUSE A DROP IS PENDING ON THE
OBJECT
DSNT418I SQLSTATE = 57006 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXICTS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 105 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000069' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC
INFORMATION

Any ideas?! What am I missing?

Thanks,
Jay


----------------------------------------
Jay Reavill DBA
Certegy Card Services
11601 Roosevelt Blvd.
St. Petersburg, FL. 33716
Office (727) 227-2144
----------------------------------------





------------------------------------------------------------------------
------
This message contains information from Certegy, Inc which may be
confidential and privileged. If you are not an intended recipient,
please refrain from any disclosure, copying, distribution or use of this
information and note that such actions are prohibited. If you have
received this transmission in error, please notify by e:mail
[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








------------------------------------------------------------------------------
This message contains information from Certegy, Inc which may be confidential and privileged. If you are not an intended recipient, please refrain from any disclosure, copying, distribution or use of this information and note that such actions are prohibited. If you have received this transmission in error, please notify by e:mail [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

Jay Reavill

Re: Odd Create Table Issue - DB2 V7 for z/OS
(in response to Jay Reavill)
Here's another interesting twist... Same create statements without the
drop between works...

SET CURRENT SQLID = 'APPDBA'
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION

CREATE TABLE PPMS2D.TMP_FUNCTS LIKE PPMS2D.FUNCTS
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION

CREATE TABLE TMP_FUNCTS LIKE PPMS2D.FUNCTS
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION

DROP TABLE PPMS2D.TMP_FUNCTS
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION

DROP TABLE TMP_FUNCTS
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION


----------------------------------------
Jay Reavill DBA
Certegy Card Services
11601 Roosevelt Blvd.
St. Petersburg, FL. 33716
Office (727) 227-2144
----------------------------------------

----- Forwarded by Jay Reavill/Technology/STP/US/Certegy on 12/01/2005
01:00 PM -----

Jay Reavill
To: "DB2 Database Discussion list at IDUG" <[login to unmask email]>
12/01/2005 12:36 cc:
PM Subject: Re: [DB2-L] Odd Create Table Issue - DB2 V7 for z/OS(Document link: Jay
Reavill)




Hmm, well it's not using my id (which this job is running under) as the
default, it's using the current sqlid APPDBA (it's in the error message).
And the first table is not defaulted, it's qualified with creator PPMS2D.
So the end result should be 2 tables with the same name, but different
creators.

----------------------------------------
Jay Reavill DBA
Certegy Card Services
11601 Roosevelt Blvd.
St. Petersburg, FL. 33716
Office (727) 227-2144
----------------------------------------




"Grainger, Phil"
<Phil.Grainger@C To: [login to unmask email]
A.COM> cc:
Sent by: "DB2 Subject: Re: [DB2-L] Odd Create Table Issue - DB2 V7 for z/OS
Data Base
Discussion List"
<[login to unmask email]
.ORG>


12/01/2005 12:26
PM
Please respond
to "DB2 Database
Discussion list
at IDUG"





DDL always uses your PRIMARY id and NEVER the current id

So, the default owner for a created table does NOT change just because
you do a SET CURRENT SQLID


Phil Grainger
CA
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Jay Reavill
Sent: 01 December 2005 17:17
To: [login to unmask email]
Subject: [DB2-L] Odd Create Table Issue - DB2 V7 for z/OS

Hello all,

I have a scenario occurring that doesn't make sense to me. We set the
sqlid, create a differently qualified table, drop the qualified table,
and then attempt to create another version of the same tablename with a
default creator. This results in a -679 on the create of the 2nd
version eventho it has a different creator than the first table that was
dropped. The first table being in drop pending status makes sense
because a commit has not occurred. But why is DB2 considering the 2nd
version the same as the first? And what's up with the object name it's
complaining about? That doesn't match anything. This is happening on a
number of tables doing the same thing. And for all of them the object
name is truncated at 8 characters and a 'R' is placed where the
underscore would have been. Also, everything works fine if a commit is
added after the drop.

SET CURRENT SQLID = 'APPDBA'
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION

CREATE TABLE PPMS2D.TMP_FUNCTS LIKE PPMS2D.FUNCTS DSNT400I
SQLCODE = 000, SUCCESSFUL EXECUTION

DROP TABLE PPMS2D.TMP_FUNCTS
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION

CREATE TABLE TMP_FUNCTS LIKE PPMS2D.FUNCTS SQL ERROR DURING
EXECUTE IMMEDIATE DSNT408I SQLCODE = -679, ERROR: THE OBJECT
APPDBA.TMPRFUNC CANNOT BE CREATED BECAUSE A DROP IS PENDING ON THE
OBJECT
DSNT418I SQLSTATE = 57006 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXICTS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 105 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000069' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC
INFORMATION

Any ideas?! What am I missing?

Thanks,
Jay


----------------------------------------
Jay Reavill DBA
Certegy Card Services
11601 Roosevelt Blvd.
St. Petersburg, FL. 33716
Office (727) 227-2144
----------------------------------------





------------------------------------------------------------------------
------
This message contains information from Certegy, Inc which may be
confidential and privileged. If you are not an intended recipient,
please refrain from any disclosure, copying, distribution or use of this
information and note that such actions are prohibited. If you have
received this transmission in error, please notify by e:mail
[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












------------------------------------------------------------------------------
This message contains information from Certegy, Inc which may be confidential and privileged. If you are not an intended recipient, please refrain from any disclosure, copying, distribution or use of this information and note that such actions are prohibited. If you have received this transmission in error, please notify by e:mail [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

Jay Reavill

Re: Odd Create Table Issue - DB2 V7 for z/OS
(in response to Chad Walmer)
Here's another interesting twist... Same create statements without the
drop between works...

SET CURRENT SQLID = 'APPDBA'
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION

CREATE TABLE PPMS2D.TMP_FUNCTS LIKE PPMS2D.FUNCTS
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION

CREATE TABLE TMP_FUNCTS LIKE PPMS2D.FUNCTS
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION

DROP TABLE PPMS2D.TMP_FUNCTS
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION

DROP TABLE TMP_FUNCTS
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION


----------------------------------------
Jay Reavill DBA
Certegy Card Services
11601 Roosevelt Blvd.
St. Petersburg, FL. 33716
Office (727) 227-2144
----------------------------------------

----- Forwarded by Jay Reavill/Technology/STP/US/Certegy on 12/01/2005
01:00 PM -----

Jay Reavill
To: "DB2 Database Discussion list at IDUG" <[login to unmask email]>
12/01/2005 12:36 cc:
PM Subject: Re: [DB2-L] Odd Create Table Issue - DB2 V7 for z/OS(Document link: Jay
Reavill)




Hmm, well it's not using my id (which this job is running under) as the
default, it's using the current sqlid APPDBA (it's in the error message).
And the first table is not defaulted, it's qualified with creator PPMS2D.
So the end result should be 2 tables with the same name, but different
creators.

----------------------------------------
Jay Reavill DBA
Certegy Card Services
11601 Roosevelt Blvd.
St. Petersburg, FL. 33716
Office (727) 227-2144
----------------------------------------




"Grainger, Phil"
<Phil.Grainger@C To: [login to unmask email]
A.COM> cc:
Sent by: "DB2 Subject: Re: [DB2-L] Odd Create Table Issue - DB2 V7 for z/OS
Data Base
Discussion List"
<[login to unmask email]
.ORG>


12/01/2005 12:26
PM
Please respond
to "DB2 Database
Discussion list
at IDUG"





DDL always uses your PRIMARY id and NEVER the current id

So, the default owner for a created table does NOT change just because
you do a SET CURRENT SQLID


Phil Grainger
CA
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Jay Reavill
Sent: 01 December 2005 17:17
To: [login to unmask email]
Subject: [DB2-L] Odd Create Table Issue - DB2 V7 for z/OS

Hello all,

I have a scenario occurring that doesn't make sense to me. We set the
sqlid, create a differently qualified table, drop the qualified table,
and then attempt to create another version of the same tablename with a
default creator. This results in a -679 on the create of the 2nd
version eventho it has a different creator than the first table that was
dropped. The first table being in drop pending status makes sense
because a commit has not occurred. But why is DB2 considering the 2nd
version the same as the first? And what's up with the object name it's
complaining about? That doesn't match anything. This is happening on a
number of tables doing the same thing. And for all of them the object
name is truncated at 8 characters and a 'R' is placed where the
underscore would have been. Also, everything works fine if a commit is
added after the drop.

SET CURRENT SQLID = 'APPDBA'
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION

CREATE TABLE PPMS2D.TMP_FUNCTS LIKE PPMS2D.FUNCTS DSNT400I
SQLCODE = 000, SUCCESSFUL EXECUTION

DROP TABLE PPMS2D.TMP_FUNCTS
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION

CREATE TABLE TMP_FUNCTS LIKE PPMS2D.FUNCTS SQL ERROR DURING
EXECUTE IMMEDIATE DSNT408I SQLCODE = -679, ERROR: THE OBJECT
APPDBA.TMPRFUNC CANNOT BE CREATED BECAUSE A DROP IS PENDING ON THE
OBJECT
DSNT418I SQLSTATE = 57006 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXICTS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 105 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000069' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC
INFORMATION

Any ideas?! What am I missing?

Thanks,
Jay


----------------------------------------
Jay Reavill DBA
Certegy Card Services
11601 Roosevelt Blvd.
St. Petersburg, FL. 33716
Office (727) 227-2144
----------------------------------------





------------------------------------------------------------------------
------
This message contains information from Certegy, Inc which may be
confidential and privileged. If you are not an intended recipient,
please refrain from any disclosure, copying, distribution or use of this
information and note that such actions are prohibited. If you have
received this transmission in error, please notify by e:mail
[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












------------------------------------------------------------------------------
This message contains information from Certegy, Inc which may be confidential and privileged. If you are not an intended recipient, please refrain from any disclosure, copying, distribution or use of this information and note that such actions are prohibited. If you have received this transmission in error, please notify by e:mail [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

Chad Walmer

Re: Odd Create Table Issue - DB2 V7 for z/OS
(in response to Chad Walmer)
It looks like the failure is occurring on the table space. Since you
don't specify the "IN dbname.tsname" clause, DB2 is creating a table
space in DSNDB04 with the name TMPRFUNC and apparently wants to reuse it
on the subsequent create table but can't since there is a drop
pending(?).

Chad Walmer
Rite Aid

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Jay Reavill
Sent: Thursday, December 01, 2005 12:37 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Odd Create Table Issue - DB2 V7 for z/OS

Hmm, well it's not using my id (which this job is running under) as the
default, it's using the current sqlid APPDBA (it's in the error
message).
And the first table is not defaulted, it's qualified with creator
PPMS2D.
So the end result should be 2 tables with the same name, but different
creators.

----------------------------------------
Jay Reavill DBA
Certegy Card Services
11601 Roosevelt Blvd.
St. Petersburg, FL. 33716
Office (727) 227-2144
----------------------------------------





"Grainger, Phil"

<Phil.Grainger@C To:
[login to unmask email]

A.COM> cc:

Sent by: "DB2 Subject: Re: [DB2-L] Odd
Create Table Issue - DB2 V7 for z/OS
Data Base

Discussion List"

<[login to unmask email]

.ORG>





12/01/2005 12:26

PM

Please respond

to "DB2 Database

Discussion list

at IDUG"








DDL always uses your PRIMARY id and NEVER the current id

So, the default owner for a created table does NOT change just because
you do a SET CURRENT SQLID


Phil Grainger
CA
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Jay Reavill
Sent: 01 December 2005 17:17
To: [login to unmask email]
Subject: [DB2-L] Odd Create Table Issue - DB2 V7 for z/OS

Hello all,

I have a scenario occurring that doesn't make sense to me. We set the
sqlid, create a differently qualified table, drop the qualified table,
and then attempt to create another version of the same tablename with a
default creator. This results in a -679 on the create of the 2nd
version eventho it has a different creator than the first table that was
dropped. The first table being in drop pending status makes sense
because a commit has not occurred. But why is DB2 considering the 2nd
version the same as the first? And what's up with the object name it's
complaining about? That doesn't match anything. This is happening on a
number of tables doing the same thing. And for all of them the object
name is truncated at 8 characters and a 'R' is placed where the
underscore would have been. Also, everything works fine if a commit is
added after the drop.

SET CURRENT SQLID = 'APPDBA'
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION

CREATE TABLE PPMS2D.TMP_FUNCTS LIKE PPMS2D.FUNCTS DSNT400I
SQLCODE = 000, SUCCESSFUL EXECUTION

DROP TABLE PPMS2D.TMP_FUNCTS
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION

CREATE TABLE TMP_FUNCTS LIKE PPMS2D.FUNCTS SQL ERROR DURING
EXECUTE IMMEDIATE DSNT408I SQLCODE = -679, ERROR: THE OBJECT
APPDBA.TMPRFUNC CANNOT BE CREATED BECAUSE A DROP IS PENDING ON THE
OBJECT
DSNT418I SQLSTATE = 57006 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXICTS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 105 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000069' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC
INFORMATION

Any ideas?! What am I missing?

Thanks,
Jay


----------------------------------------
Jay Reavill DBA
Certegy Card Services
11601 Roosevelt Blvd.
St. Petersburg, FL. 33716
Office (727) 227-2144
----------------------------------------





------------------------------------------------------------------------
------
This message contains information from Certegy, Inc which may be
confidential and privileged. If you are not an intended recipient,
please refrain from any disclosure, copying, distribution or use of this
information and note that such actions are prohibited. If you have
received this transmission in error, please notify by e:mail
[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








------------------------------------------------------------------------
------
This message contains information from Certegy, Inc which may be
confidential and privileged. If you are not an intended recipient,
please refrain from any disclosure, copying, distribution or use of this
information and note that such actions are prohibited. If you have
received this transmission in error, please notify by e:mail
[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

Disclaimer: This e-mail message is intended only for the personal use of
the recipient(s) named above. If you are not an intended recipient, you
may not review, copy or distribute this message. If you have received this
communication in error, please notify us immediately by e-mail and delete
the original message.
This e-mail expresses views only of the sender, which are not to be
attributed to Rite Aid Corporation and may not be copied or distributed
without this statement.

---------------------------------------------------------------------------------
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

Chad Walmer

Re: Odd Create Table Issue - DB2 V7 for z/OS
(in response to Jay Reavill)
It looks like the failure is occurring on the table space. Since you
don't specify the "IN dbname.tsname" clause, DB2 is creating a table
space in DSNDB04 with the name TMPRFUNC and apparently wants to reuse it
on the subsequent create table but can't since there is a drop
pending(?).

Chad Walmer
Rite Aid

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Jay Reavill
Sent: Thursday, December 01, 2005 12:37 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Odd Create Table Issue - DB2 V7 for z/OS

Hmm, well it's not using my id (which this job is running under) as the
default, it's using the current sqlid APPDBA (it's in the error
message).
And the first table is not defaulted, it's qualified with creator
PPMS2D.
So the end result should be 2 tables with the same name, but different
creators.

----------------------------------------
Jay Reavill DBA
Certegy Card Services
11601 Roosevelt Blvd.
St. Petersburg, FL. 33716
Office (727) 227-2144
----------------------------------------





"Grainger, Phil"

<Phil.Grainger@C To:
[login to unmask email]

A.COM> cc:

Sent by: "DB2 Subject: Re: [DB2-L] Odd
Create Table Issue - DB2 V7 for z/OS
Data Base

Discussion List"

<[login to unmask email]

.ORG>





12/01/2005 12:26

PM

Please respond

to "DB2 Database

Discussion list

at IDUG"








DDL always uses your PRIMARY id and NEVER the current id

So, the default owner for a created table does NOT change just because
you do a SET CURRENT SQLID


Phil Grainger
CA
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Jay Reavill
Sent: 01 December 2005 17:17
To: [login to unmask email]
Subject: [DB2-L] Odd Create Table Issue - DB2 V7 for z/OS

Hello all,

I have a scenario occurring that doesn't make sense to me. We set the
sqlid, create a differently qualified table, drop the qualified table,
and then attempt to create another version of the same tablename with a
default creator. This results in a -679 on the create of the 2nd
version eventho it has a different creator than the first table that was
dropped. The first table being in drop pending status makes sense
because a commit has not occurred. But why is DB2 considering the 2nd
version the same as the first? And what's up with the object name it's
complaining about? That doesn't match anything. This is happening on a
number of tables doing the same thing. And for all of them the object
name is truncated at 8 characters and a 'R' is placed where the
underscore would have been. Also, everything works fine if a commit is
added after the drop.

SET CURRENT SQLID = 'APPDBA'
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION

CREATE TABLE PPMS2D.TMP_FUNCTS LIKE PPMS2D.FUNCTS DSNT400I
SQLCODE = 000, SUCCESSFUL EXECUTION

DROP TABLE PPMS2D.TMP_FUNCTS
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION

CREATE TABLE TMP_FUNCTS LIKE PPMS2D.FUNCTS SQL ERROR DURING
EXECUTE IMMEDIATE DSNT408I SQLCODE = -679, ERROR: THE OBJECT
APPDBA.TMPRFUNC CANNOT BE CREATED BECAUSE A DROP IS PENDING ON THE
OBJECT
DSNT418I SQLSTATE = 57006 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXICTS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 105 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000069' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC
INFORMATION

Any ideas?! What am I missing?

Thanks,
Jay


----------------------------------------
Jay Reavill DBA
Certegy Card Services
11601 Roosevelt Blvd.
St. Petersburg, FL. 33716
Office (727) 227-2144
----------------------------------------





------------------------------------------------------------------------
------
This message contains information from Certegy, Inc which may be
confidential and privileged. If you are not an intended recipient,
please refrain from any disclosure, copying, distribution or use of this
information and note that such actions are prohibited. If you have
received this transmission in error, please notify by e:mail
[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








------------------------------------------------------------------------
------
This message contains information from Certegy, Inc which may be
confidential and privileged. If you are not an intended recipient,
please refrain from any disclosure, copying, distribution or use of this
information and note that such actions are prohibited. If you have
received this transmission in error, please notify by e:mail
[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

Disclaimer: This e-mail message is intended only for the personal use of
the recipient(s) named above. If you are not an intended recipient, you
may not review, copy or distribute this message. If you have received this
communication in error, please notify us immediately by e-mail and delete
the original message.
This e-mail expresses views only of the sender, which are not to be
attributed to Rite Aid Corporation and may not be copied or distributed
without this statement.

---------------------------------------------------------------------------------
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: Odd Create Table Issue - DB2 V7 for z/OS
(in response to Jay Reavill)
So it is

Now I am confused as well......

Phil Grainger
CA

________________________________

From: DB2 Data Base Discussion List on behalf of Jay Reavill
Sent: Thu 01/12/2005 17:36
To: [login to unmask email]
Subject: Re: [DB2-L] Odd Create Table Issue - DB2 V7 for z/OS



Hmm, well it's not using my id (which this job is running under) as the
default, it's using the current sqlid APPDBA (it's in the error message).
And the first table is not defaulted, it's qualified with creator PPMS2D.
So the end result should be 2 tables with the same name, but different
creators.

----------------------------------------
Jay Reavill DBA
Certegy Card Services
11601 Roosevelt Blvd.
St. Petersburg, FL. 33716
Office (727) 227-2144
----------------------------------------




"Grainger, Phil"
<Phil.Grainger@C To: [login to unmask email]
A.COM> cc:
Sent by: "DB2 Subject: Re: [DB2-L] Odd Create Table Issue - DB2 V7 for z/OS
Data Base
Discussion List"
<[login to unmask email]
.ORG>


12/01/2005 12:26
PM
Please respond
to "DB2 Database
Discussion list
at IDUG"





DDL always uses your PRIMARY id and NEVER the current id

So, the default owner for a created table does NOT change just because
you do a SET CURRENT SQLID


Phil Grainger
CA
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Jay Reavill
Sent: 01 December 2005 17:17
To: [login to unmask email]
Subject: [DB2-L] Odd Create Table Issue - DB2 V7 for z/OS

Hello all,

I have a scenario occurring that doesn't make sense to me. We set the
sqlid, create a differently qualified table, drop the qualified table,
and then attempt to create another version of the same tablename with a
default creator. This results in a -679 on the create of the 2nd
version eventho it has a different creator than the first table that was
dropped. The first table being in drop pending status makes sense
because a commit has not occurred. But why is DB2 considering the 2nd
version the same as the first? And what's up with the object name it's
complaining about? That doesn't match anything. This is happening on a
number of tables doing the same thing. And for all of them the object
name is truncated at 8 characters and a 'R' is placed where the
underscore would have been. Also, everything works fine if a commit is
added after the drop.

SET CURRENT SQLID = 'APPDBA'
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION

CREATE TABLE PPMS2D.TMP_FUNCTS LIKE PPMS2D.FUNCTS DSNT400I
SQLCODE = 000, SUCCESSFUL EXECUTION

DROP TABLE PPMS2D.TMP_FUNCTS
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION

CREATE TABLE TMP_FUNCTS LIKE PPMS2D.FUNCTS SQL ERROR DURING
EXECUTE IMMEDIATE DSNT408I SQLCODE = -679, ERROR: THE OBJECT
APPDBA.TMPRFUNC CANNOT BE CREATED BECAUSE A DROP IS PENDING ON THE
OBJECT
DSNT418I SQLSTATE = 57006 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXICTS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 105 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000069' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC
INFORMATION

Any ideas?! What am I missing?

Thanks,
Jay


----------------------------------------
Jay Reavill DBA
Certegy Card Services
11601 Roosevelt Blvd.
St. Petersburg, FL. 33716
Office (727) 227-2144
----------------------------------------





------------------------------------------------------------------------
------
This message contains information from Certegy, Inc which may be
confidential and privileged. If you are not an intended recipient,
please refrain from any disclosure, copying, distribution or use of this
information and note that such actions are prohibited. If you have
received this transmission in error, please notify by e:mail
[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








------------------------------------------------------------------------------
This message contains information from Certegy, Inc which may be confidential and privileged. If you are not an intended recipient, please refrain from any disclosure, copying, distribution or use of this information and note that such actions are prohibited. If you have received this transmission in error, please notify by e:mail [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

Phil Grainger

Re: Odd Create Table Issue - DB2 V7 for z/OS
(in response to Phil Grainger)
So it is

Now I am confused as well......

Phil Grainger
CA

________________________________

From: DB2 Data Base Discussion List on behalf of Jay Reavill
Sent: Thu 01/12/2005 17:36
To: [login to unmask email]
Subject: Re: [DB2-L] Odd Create Table Issue - DB2 V7 for z/OS



Hmm, well it's not using my id (which this job is running under) as the
default, it's using the current sqlid APPDBA (it's in the error message).
And the first table is not defaulted, it's qualified with creator PPMS2D.
So the end result should be 2 tables with the same name, but different
creators.

----------------------------------------
Jay Reavill DBA
Certegy Card Services
11601 Roosevelt Blvd.
St. Petersburg, FL. 33716
Office (727) 227-2144
----------------------------------------




"Grainger, Phil"
<Phil.Grainger@C To: [login to unmask email]
A.COM> cc:
Sent by: "DB2 Subject: Re: [DB2-L] Odd Create Table Issue - DB2 V7 for z/OS
Data Base
Discussion List"
<[login to unmask email]
.ORG>


12/01/2005 12:26
PM
Please respond
to "DB2 Database
Discussion list
at IDUG"





DDL always uses your PRIMARY id and NEVER the current id

So, the default owner for a created table does NOT change just because
you do a SET CURRENT SQLID


Phil Grainger
CA
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Jay Reavill
Sent: 01 December 2005 17:17
To: [login to unmask email]
Subject: [DB2-L] Odd Create Table Issue - DB2 V7 for z/OS

Hello all,

I have a scenario occurring that doesn't make sense to me. We set the
sqlid, create a differently qualified table, drop the qualified table,
and then attempt to create another version of the same tablename with a
default creator. This results in a -679 on the create of the 2nd
version eventho it has a different creator than the first table that was
dropped. The first table being in drop pending status makes sense
because a commit has not occurred. But why is DB2 considering the 2nd
version the same as the first? And what's up with the object name it's
complaining about? That doesn't match anything. This is happening on a
number of tables doing the same thing. And for all of them the object
name is truncated at 8 characters and a 'R' is placed where the
underscore would have been. Also, everything works fine if a commit is
added after the drop.

SET CURRENT SQLID = 'APPDBA'
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION

CREATE TABLE PPMS2D.TMP_FUNCTS LIKE PPMS2D.FUNCTS DSNT400I
SQLCODE = 000, SUCCESSFUL EXECUTION

DROP TABLE PPMS2D.TMP_FUNCTS
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION

CREATE TABLE TMP_FUNCTS LIKE PPMS2D.FUNCTS SQL ERROR DURING
EXECUTE IMMEDIATE DSNT408I SQLCODE = -679, ERROR: THE OBJECT
APPDBA.TMPRFUNC CANNOT BE CREATED BECAUSE A DROP IS PENDING ON THE
OBJECT
DSNT418I SQLSTATE = 57006 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXICTS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 105 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000069' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC
INFORMATION

Any ideas?! What am I missing?

Thanks,
Jay


----------------------------------------
Jay Reavill DBA
Certegy Card Services
11601 Roosevelt Blvd.
St. Petersburg, FL. 33716
Office (727) 227-2144
----------------------------------------





------------------------------------------------------------------------
------
This message contains information from Certegy, Inc which may be
confidential and privileged. If you are not an intended recipient,
please refrain from any disclosure, copying, distribution or use of this
information and note that such actions are prohibited. If you have
received this transmission in error, please notify by e:mail
[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








------------------------------------------------------------------------------
This message contains information from Certegy, Inc which may be confidential and privileged. If you are not an intended recipient, please refrain from any disclosure, copying, distribution or use of this information and note that such actions are prohibited. If you have received this transmission in error, please notify by e:mail [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

---------------------------------------------------------------------------------
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

Jay Reavill

Re: Odd Create Table Issue - DB2 V7 for z/OS
(in response to Phil Grainger)
I think you nailed it Chad! That's the only thing that makes sense so far.
But I wonder why DB2 would try to use the same tablespace name for a
different table. I would think there would be a hashing routine or
something to make the name unique eventho the table names are the same.
Seems like this is an issue within DB2. I really don't think we're trying
to do something that shouldn't be allowed. Seems it's just a limitation of
DB2.

I ran another quick test creating both tables and this is the definition in
DB2...

SET CURRENT SQLID = 'APPDBA';
CREATE TABLE PPMS2D.TMP_FUNCTS LIKE PPMS2D.FUNCTS;
CREATE TABLE TMP_FUNCTS LIKE PPMS2D.FUNCTS;

---------+---------+---------+---------+---------+---------+---------+
NAME CREATOR TYPE DBNAME TSNAME DBID OBID
---------+---------+---------+---------+---------+---------+---------+
TMP_FUNCTS PPMS2D T DSNDB04 TMPRFUNC 4 49
TMP_FUNCTS APPDBA T DSNDB04 TMPR1SY@ 4 52

In this case the tablespace names are different because DB2 recognized that
there was one already existing with the same name and generated a new name.
So it would appear that DB2 handles it correctly unless there is a drop
pending. In that case DB2 doesn't recognize that the drop pending name is
potentially still be used and assigns it to the new table. Probably
because the entry is no longer in the catalog, but just hasn't been
committed yet. However, it is aware of it at some level because it then
gets the -679 when attempting to define it. Seems like there is a check
missing on DB2's part. I would think it should recognize the drop pending
and generate a new name.

Interesting!

Thanks!

----------------------------------------
Jay Reavill DBA
Certegy Card Services
11601 Roosevelt Blvd.
St. Petersburg, FL. 33716
Office (727) 227-2144
----------------------------------------




"Chad A. Walmer"
<[login to unmask email] To: [login to unmask email]
.COM> cc:
Sent by: "DB2 Subject: Re: [DB2-L] Odd Create Table Issue - DB2 V7 for z/OS
Data Base
Discussion List"
<[login to unmask email]
.ORG>


12/01/2005 01:05
PM
Please respond
to "DB2 Database
Discussion list
at IDUG"





It looks like the failure is occurring on the table space. Since you
don't specify the "IN dbname.tsname" clause, DB2 is creating a table
space in DSNDB04 with the name TMPRFUNC and apparently wants to reuse it
on the subsequent create table but can't since there is a drop
pending(?).

Chad Walmer
Rite Aid

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Jay Reavill
Sent: Thursday, December 01, 2005 12:37 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Odd Create Table Issue - DB2 V7 for z/OS

Hmm, well it's not using my id (which this job is running under) as the
default, it's using the current sqlid APPDBA (it's in the error
message).
And the first table is not defaulted, it's qualified with creator
PPMS2D.
So the end result should be 2 tables with the same name, but different
creators.

----------------------------------------
Jay Reavill DBA
Certegy Card Services
11601 Roosevelt Blvd.
St. Petersburg, FL. 33716
Office (727) 227-2144
----------------------------------------






"Grainger, Phil"

<Phil.Grainger@C To:
[login to unmask email]

A.COM> cc:

Sent by: "DB2 Subject: Re: [DB2-L] Odd
Create Table Issue - DB2 V7 for z/OS

Data Base

Discussion List"

<[login to unmask email]

.ORG>







12/01/2005 12:26

PM

Please respond

to "DB2 Database

Discussion list

at IDUG"










DDL always uses your PRIMARY id and NEVER the current id

So, the default owner for a created table does NOT change just because
you do a SET CURRENT SQLID


Phil Grainger
CA
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Jay Reavill
Sent: 01 December 2005 17:17
To: [login to unmask email]
Subject: [DB2-L] Odd Create Table Issue - DB2 V7 for z/OS

Hello all,

I have a scenario occurring that doesn't make sense to me. We set the
sqlid, create a differently qualified table, drop the qualified table,
and then attempt to create another version of the same tablename with a
default creator. This results in a -679 on the create of the 2nd
version eventho it has a different creator than the first table that was
dropped. The first table being in drop pending status makes sense
because a commit has not occurred. But why is DB2 considering the 2nd
version the same as the first? And what's up with the object name it's
complaining about? That doesn't match anything. This is happening on a
number of tables doing the same thing. And for all of them the object
name is truncated at 8 characters and a 'R' is placed where the
underscore would have been. Also, everything works fine if a commit is
added after the drop.

SET CURRENT SQLID = 'APPDBA'
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION

CREATE TABLE PPMS2D.TMP_FUNCTS LIKE PPMS2D.FUNCTS DSNT400I
SQLCODE = 000, SUCCESSFUL EXECUTION

DROP TABLE PPMS2D.TMP_FUNCTS
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION

CREATE TABLE TMP_FUNCTS LIKE PPMS2D.FUNCTS SQL ERROR DURING
EXECUTE IMMEDIATE DSNT408I SQLCODE = -679, ERROR: THE OBJECT
APPDBA.TMPRFUNC CANNOT BE CREATED BECAUSE A DROP IS PENDING ON THE
OBJECT
DSNT418I SQLSTATE = 57006 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXICTS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 105 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000069' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC
INFORMATION

Any ideas?! What am I missing?

Thanks,
Jay


----------------------------------------
Jay Reavill DBA
Certegy Card Services
11601 Roosevelt Blvd.
St. Petersburg, FL. 33716
Office (727) 227-2144
----------------------------------------





------------------------------------------------------------------------
------
This message contains information from Certegy, Inc which may be
confidential and privileged. If you are not an intended recipient,
please refrain from any disclosure, copying, distribution or use of this
information and note that such actions are prohibited. If you have
received this transmission in error, please notify by e:mail
[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








------------------------------------------------------------------------
------
This message contains information from Certegy, Inc which may be
confidential and privileged. If you are not an intended recipient,
please refrain from any disclosure, copying, distribution or use of this
information and note that such actions are prohibited. If you have
received this transmission in error, please notify by e:mail
[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

Disclaimer: This e-mail message is intended only for the personal use of

the recipient(s) named above. If you are not an intended recipient, you

may not review, copy or distribute this message. If you have received this
communication in error, please notify us immediately by e-mail and delete

the original message.
This e-mail expresses views only of the sender, which are not to be

attributed to Rite Aid Corporation and may not be copied or distributed

without this statement.

---------------------------------------------------------------------------------

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








------------------------------------------------------------------------------
This message contains information from Certegy, Inc which may be confidential and privileged. If you are not an intended recipient, please refrain from any disclosure, copying, distribution or use of this information and note that such actions are prohibited. If you have received this transmission in error, please notify by e:mail [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

[login to unmask email]

Re: Odd Create Table Issue - DB2 V7 for z/OS
(in response to Jay Reavill)
Jay,

My first question is the specific definition of "PPMS2D.FUNCTS". I assume
it's a table, but it would be nice to confirm that. Is it a View? Do any
of the columns have FIELDPROCs defined? Do any columns have (named)
Constraints?

The second question (already raised by someone else). After creating
Table PPMS2D.TMP_FUNCTS, in exactly what database and tablespace does it
reside? Is the definition complete? What other objects were created in the
database along with the Table object?

Last, your error message:

DSNT408I SQLCODE = -679, ERROR: THE OBJECT APPDBA.TMPRFUNC CANNOT BE
CREATED BECAUSE A DROP IS PENDING ON THE OBJECT
DSNT418I SQLSTATE = 57006 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXICTS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 105 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000069' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION

I don't see any indication as to exactly what kind of object
APPDBA.TMPRFUNC is. I would speculate that it is not a table, but some
other type of object. My knowledge of DSNTIAR output is a little sketchy;
is there any indication in this error message (or in the logs) what the
Object Type of APPDBA.TMPRFUNC is? I suspect we'll find out when you show
us the complete table definition for PPMS2D.FUNCTS, including any RI,
constraints, partitioning indexes, LOB AUX tables, Row ID columns, etc.,
etc.

Lock Lyon
Compuware Corp




Jay Reavill <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
12/01/2005 12:16 PM
Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>


To
[login to unmask email]
cc

Subject
[DB2-L] Odd Create Table Issue - DB2 V7 for z/OS






Hello all,

I have a scenario occurring that doesn't make sense to me. We set the
sqlid, create a differently qualified table, drop the qualified table, and
then attempt to create another version of the same tablename with a
default
creator. This results in a -679 on the create of the 2nd version eventho
it has a different creator than the first table that was dropped. The
first table being in drop pending status makes sense because a commit has
not occurred. But why is DB2 considering the 2nd version the same as the
first? And what's up with the object name it's complaining about? That
doesn't match anything. This is happening on a number of tables doing the
same thing. And for all of them the object name is truncated at 8
characters and a 'R' is placed where the underscore would have been. Also,
everything works fine if a commit is added after the drop.

SET CURRENT SQLID = 'APPDBA'
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION

CREATE TABLE PPMS2D.TMP_FUNCTS LIKE PPMS2D.FUNCTS
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION

DROP TABLE PPMS2D.TMP_FUNCTS
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION

CREATE TABLE TMP_FUNCTS LIKE PPMS2D.FUNCTS
SQL ERROR DURING EXECUTE IMMEDIATE
DSNT408I SQLCODE = -679, ERROR: THE OBJECT APPDBA.TMPRFUNC CANNOT BE
CREATED BECAUSE A DROP IS PENDING ON THE OBJECT
DSNT418I SQLSTATE = 57006 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXICTS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 105 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000069' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC
INFORMATION

Any ideas?! What am I missing?

Thanks,
Jay


----------------------------------------
Jay Reavill DBA
Certegy Card Services
11601 Roosevelt Blvd.
St. Petersburg, FL. 33716
Office (727) 227-2144
----------------------------------------

------------------------------------------------------------------------------
This message contains information from Certegy, Inc which may be
confidential and privileged. If you are not an intended recipient, please
refrain from any disclosure, copying, distribution or use of this
information and note that such actions are prohibited. If you have
received this transmission in error, please notify by e:mail
[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: Odd Create Table Issue - DB2 V7 for z/OS
(in response to LL581@DAIMLERCHRYSLER.COM)
AHA

(Ignore what I said about DDL and secondary authids - I must check up on
that), but anyway

After you DROP an object (like a table) it's catalog definition is gone
EVEN BEFORE THE COMMIT. So, DB2 IS doing a check, it does indeed check
in the catalog to see if you are creating a duplicate tablespace - it
doesn't find a duplicate so tries to create it only, whoops the drop
hasn't been committed yet. It doesn't seem to consider the possibility
that the reason it didn't find one was that there is a drop pending.


Phil Grainger
CA
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Jay Reavill
Sent: 01 December 2005 20:34
To: [login to unmask email]
Subject: Re: [DB2-L] Odd Create Table Issue - DB2 V7 for z/OS

I think you nailed it Chad! That's the only thing that makes sense so
far.
But I wonder why DB2 would try to use the same tablespace name for a
different table. I would think there would be a hashing routine or
something to make the name unique eventho the table names are the same.
Seems like this is an issue within DB2. I really don't think we're
trying to do something that shouldn't be allowed. Seems it's just a
limitation of DB2.

I ran another quick test creating both tables and this is the definition
in DB2...

SET CURRENT SQLID = 'APPDBA';
CREATE TABLE PPMS2D.TMP_FUNCTS LIKE PPMS2D.FUNCTS; CREATE TABLE
TMP_FUNCTS LIKE PPMS2D.FUNCTS;

---------+---------+---------+---------+---------+---------+---------+
NAME CREATOR TYPE DBNAME TSNAME DBID OBID
---------+---------+---------+---------+---------+---------+---------+
TMP_FUNCTS PPMS2D T DSNDB04 TMPRFUNC 4 49
TMP_FUNCTS APPDBA T DSNDB04 TMPR1SY@ 4 52

In this case the tablespace names are different because DB2 recognized
that there was one already existing with the same name and generated a
new name.
So it would appear that DB2 handles it correctly unless there is a drop
pending. In that case DB2 doesn't recognize that the drop pending name
is potentially still be used and assigns it to the new table. Probably
because the entry is no longer in the catalog, but just hasn't been
committed yet. However, it is aware of it at some level because it then
gets the -679 when attempting to define it. Seems like there is a check
missing on DB2's part. I would think it should recognize the drop
pending and generate a new name.

Interesting!

Thanks!

----------------------------------------
Jay Reavill DBA
Certegy Card Services
11601 Roosevelt Blvd.
St. Petersburg, FL. 33716
Office (727) 227-2144
----------------------------------------





"Chad A. Walmer"

<[login to unmask email] To:
[login to unmask email]

.COM> cc:

Sent by: "DB2 Subject: Re: [DB2-L] Odd
Create Table Issue - DB2 V7 for z/OS
Data Base

Discussion List"

<[login to unmask email]

.ORG>





12/01/2005 01:05

PM

Please respond

to "DB2 Database

Discussion list

at IDUG"








It looks like the failure is occurring on the table space. Since you
don't specify the "IN dbname.tsname" clause, DB2 is creating a table
space in DSNDB04 with the name TMPRFUNC and apparently wants to reuse it
on the subsequent create table but can't since there is a drop
pending(?).

Chad Walmer
Rite Aid

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Jay Reavill
Sent: Thursday, December 01, 2005 12:37 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Odd Create Table Issue - DB2 V7 for z/OS

Hmm, well it's not using my id (which this job is running under) as the
default, it's using the current sqlid APPDBA (it's in the error
message).
And the first table is not defaulted, it's qualified with creator
PPMS2D.
So the end result should be 2 tables with the same name, but different
creators.

----------------------------------------
Jay Reavill DBA
Certegy Card Services
11601 Roosevelt Blvd.
St. Petersburg, FL. 33716
Office (727) 227-2144
----------------------------------------






"Grainger, Phil"

<Phil.Grainger@C To:
[login to unmask email]

A.COM> cc:

Sent by: "DB2 Subject: Re: [DB2-L] Odd
Create Table Issue - DB2 V7 for z/OS

Data Base

Discussion List"

<[login to unmask email]

.ORG>







12/01/2005 12:26

PM

Please respond

to "DB2 Database

Discussion list

at IDUG"










DDL always uses your PRIMARY id and NEVER the current id

So, the default owner for a created table does NOT change just because
you do a SET CURRENT SQLID


Phil Grainger
CA
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Jay Reavill
Sent: 01 December 2005 17:17
To: [login to unmask email]
Subject: [DB2-L] Odd Create Table Issue - DB2 V7 for z/OS

Hello all,

I have a scenario occurring that doesn't make sense to me. We set the
sqlid, create a differently qualified table, drop the qualified table,
and then attempt to create another version of the same tablename with a
default creator. This results in a -679 on the create of the 2nd
version eventho it has a different creator than the first table that was
dropped. The first table being in drop pending status makes sense
because a commit has not occurred. But why is DB2 considering the 2nd
version the same as the first? And what's up with the object name it's
complaining about? That doesn't match anything. This is happening on a
number of tables doing the same thing. And for all of them the object
name is truncated at 8 characters and a 'R' is placed where the
underscore would have been. Also, everything works fine if a commit is
added after the drop.

SET CURRENT SQLID = 'APPDBA'
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION

CREATE TABLE PPMS2D.TMP_FUNCTS LIKE PPMS2D.FUNCTS DSNT400I
SQLCODE = 000, SUCCESSFUL EXECUTION

DROP TABLE PPMS2D.TMP_FUNCTS
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION

CREATE TABLE TMP_FUNCTS LIKE PPMS2D.FUNCTS SQL ERROR DURING
EXECUTE IMMEDIATE DSNT408I SQLCODE = -679, ERROR: THE OBJECT
APPDBA.TMPRFUNC CANNOT BE CREATED BECAUSE A DROP IS PENDING ON THE
OBJECT
DSNT418I SQLSTATE = 57006 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXICTS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 105 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000069' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC
INFORMATION

Any ideas?! What am I missing?

Thanks,
Jay


----------------------------------------
Jay Reavill DBA
Certegy Card Services
11601 Roosevelt Blvd.
St. Petersburg, FL. 33716
Office (727) 227-2144
----------------------------------------





------------------------------------------------------------------------
------
This message contains information from Certegy, Inc which may be
confidential and privileged. If you are not an intended recipient,
please refrain from any disclosure, copying, distribution or use of this
information and note that such actions are prohibited. If you have
received this transmission in error, please notify by e:mail
[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








------------------------------------------------------------------------
------
This message contains information from Certegy, Inc which may be
confidential and privileged. If you are not an intended recipient,
please refrain from any disclosure, copying, distribution or use of this
information and note that such actions are prohibited. If you have
received this transmission in error, please notify by e:mail
[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

Disclaimer: This e-mail message is intended only for the personal use of

the recipient(s) named above. If you are not an intended recipient, you

may not review, copy or distribute this message. If you have received
this communication in error, please notify us immediately by e-mail and
delete

the original message.
This e-mail expresses views only of the sender, which are not to be

attributed to Rite Aid Corporation and may not be copied or distributed

without this statement.

------------------------------------------------------------------------
---------

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








------------------------------------------------------------------------
------
This message contains information from Certegy, Inc which may be
confidential and privileged. If you are not an intended recipient,
please refrain from any disclosure, copying, distribution or use of this
information and note that such actions are prohibited. If you have
received this transmission in error, please notify by e:mail
[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

Jay Reavill

Re: Odd Create Table Issue - DB2 V7 for z/OS
(in response to Phil Grainger)
Well, I just noticed that the database name is DSNDB04 as I would expect,
but the error shows APPDBA as the first part of the name. So maybe it is
some other type of object.

----------------------------------------
Jay Reavill DBA
Certegy Card Services
11601 Roosevelt Blvd.
St. Petersburg, FL. 33716
Office (727) 227-2144
----------------------------------------




Jay
Reavill/Technology/STP/US/Cert To: [login to unmask email]
[login to unmask email] cc:
Sent by: "DB2 Data Base Subject: Re: [DB2-L] Odd Create Table Issue - DB2 V7 for z/OS
Discussion List"
<[login to unmask email]>


12/01/2005 03:33 PM
Please respond to "DB2
Database Discussion list at
IDUG"





I think you nailed it Chad! That's the only thing that makes sense so far.
But I wonder why DB2 would try to use the same tablespace name for a
different table. I would think there would be a hashing routine or
something to make the name unique eventho the table names are the same.
Seems like this is an issue within DB2. I really don't think we're trying
to do something that shouldn't be allowed. Seems it's just a limitation of
DB2.

I ran another quick test creating both tables and this is the definition in
DB2...

SET CURRENT SQLID = 'APPDBA';
CREATE TABLE PPMS2D.TMP_FUNCTS LIKE PPMS2D.FUNCTS;
CREATE TABLE TMP_FUNCTS LIKE PPMS2D.FUNCTS;

---------+---------+---------+---------+---------+---------+---------+
NAME CREATOR TYPE DBNAME TSNAME DBID OBID
---------+---------+---------+---------+---------+---------+---------+
TMP_FUNCTS PPMS2D T DSNDB04 TMPRFUNC 4 49
TMP_FUNCTS APPDBA T DSNDB04 TMPR1SY@ 4 52

In this case the tablespace names are different because DB2 recognized that
there was one already existing with the same name and generated a new name.
So it would appear that DB2 handles it correctly unless there is a drop
pending. In that case DB2 doesn't recognize that the drop pending name is
potentially still be used and assigns it to the new table. Probably
because the entry is no longer in the catalog, but just hasn't been
committed yet. However, it is aware of it at some level because it then
gets the -679 when attempting to define it. Seems like there is a check
missing on DB2's part. I would think it should recognize the drop pending
and generate a new name.

Interesting!

Thanks!

----------------------------------------
Jay Reavill DBA
Certegy Card Services
11601 Roosevelt Blvd.
St. Petersburg, FL. 33716
Office (727) 227-2144
----------------------------------------




"Chad A. Walmer"

<[login to unmask email] To:
[login to unmask email]

.COM> cc:

Sent by: "DB2 Subject: Re: [DB2-L] Odd
Create Table Issue - DB2 V7 for z/OS
Data Base

Discussion List"

<[login to unmask email]

.ORG>



12/01/2005 01:05

PM

Please respond

to "DB2 Database

Discussion list

at IDUG"






It looks like the failure is occurring on the table space. Since you
don't specify the "IN dbname.tsname" clause, DB2 is creating a table
space in DSNDB04 with the name TMPRFUNC and apparently wants to reuse it
on the subsequent create table but can't since there is a drop
pending(?).

Chad Walmer
Rite Aid

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Jay Reavill
Sent: Thursday, December 01, 2005 12:37 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Odd Create Table Issue - DB2 V7 for z/OS

Hmm, well it's not using my id (which this job is running under) as the
default, it's using the current sqlid APPDBA (it's in the error
message).
And the first table is not defaulted, it's qualified with creator
PPMS2D.
So the end result should be 2 tables with the same name, but different
creators.

----------------------------------------
Jay Reavill DBA
Certegy Card Services
11601 Roosevelt Blvd.
St. Petersburg, FL. 33716
Office (727) 227-2144
----------------------------------------






"Grainger, Phil"

<Phil.Grainger@C To:
[login to unmask email]

A.COM> cc:

Sent by: "DB2 Subject: Re: [DB2-L] Odd
Create Table Issue - DB2 V7 for z/OS

Data Base

Discussion List"

<[login to unmask email]

.ORG>







12/01/2005 12:26

PM

Please respond

to "DB2 Database

Discussion list

at IDUG"










DDL always uses your PRIMARY id and NEVER the current id

So, the default owner for a created table does NOT change just because
you do a SET CURRENT SQLID


Phil Grainger
CA
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Jay Reavill
Sent: 01 December 2005 17:17
To: [login to unmask email]
Subject: [DB2-L] Odd Create Table Issue - DB2 V7 for z/OS

Hello all,

I have a scenario occurring that doesn't make sense to me. We set the
sqlid, create a differently qualified table, drop the qualified table,
and then attempt to create another version of the same tablename with a
default creator. This results in a -679 on the create of the 2nd
version eventho it has a different creator than the first table that was
dropped. The first table being in drop pending status makes sense
because a commit has not occurred. But why is DB2 considering the 2nd
version the same as the first? And what's up with the object name it's
complaining about? That doesn't match anything. This is happening on a
number of tables doing the same thing. And for all of them the object
name is truncated at 8 characters and a 'R' is placed where the
underscore would have been. Also, everything works fine if a commit is
added after the drop.

SET CURRENT SQLID = 'APPDBA'
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION

CREATE TABLE PPMS2D.TMP_FUNCTS LIKE PPMS2D.FUNCTS DSNT400I
SQLCODE = 000, SUCCESSFUL EXECUTION

DROP TABLE PPMS2D.TMP_FUNCTS
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION

CREATE TABLE TMP_FUNCTS LIKE PPMS2D.FUNCTS SQL ERROR DURING
EXECUTE IMMEDIATE DSNT408I SQLCODE = -679, ERROR: THE OBJECT
APPDBA.TMPRFUNC CANNOT BE CREATED BECAUSE A DROP IS PENDING ON THE
OBJECT
DSNT418I SQLSTATE = 57006 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXICTS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 105 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000069' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC
INFORMATION

Any ideas?! What am I missing?

Thanks,
Jay


----------------------------------------
Jay Reavill DBA
Certegy Card Services
11601 Roosevelt Blvd.
St. Petersburg, FL. 33716
Office (727) 227-2144
----------------------------------------





------------------------------------------------------------------------
------
This message contains information from Certegy, Inc which may be
confidential and privileged. If you are not an intended recipient,
please refrain from any disclosure, copying, distribution or use of this
information and note that such actions are prohibited. If you have
received this transmission in error, please notify by e:mail
[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








------------------------------------------------------------------------
------
This message contains information from Certegy, Inc which may be
confidential and privileged. If you are not an intended recipient,
please refrain from any disclosure, copying, distribution or use of this
information and note that such actions are prohibited. If you have
received this transmission in error, please notify by e:mail
[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

Disclaimer: This e-mail message is intended only for the personal use of

the recipient(s) named above. If you are not an intended recipient, you

may not review, copy or distribute this message. If you have received this
communication in error, please notify us immediately by e-mail and delete

the original message.
This e-mail expresses views only of the sender, which are not to be

attributed to Rite Aid Corporation and may not be copied or distributed

without this statement.

---------------------------------------------------------------------------------


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








------------------------------------------------------------------------------

This message contains information from Certegy, Inc which may be
confidential and privileged. If you are not an intended recipient, please
refrain from any disclosure, copying, distribution or use of this
information and note that such actions are prohibited. If you have
received this transmission in error, please notify by e:mail
[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


------------------------------------------------------------------------------

This message contains information from Certegy, Inc which may be
confidential and privileged. If you are not an intended recipient, please
refrain from any disclosure, copying, distribution or use of this
information and note that such actions are prohibited. If you have
received this transmission in error, please notify by e:mail
[login to unmask email]
=====









------------------------------------------------------------------------------
This message contains information from Certegy, Inc which may be confidential and privileged. If you are not an intended recipient, please refrain from any disclosure, copying, distribution or use of this information and note that such actions are prohibited. If you have received this transmission in error, please notify by e:mail [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: Odd Create Table Issue - DB2 V7 for z/OS
(in response to Jay Reavill)
The message is misleading. The create table has failed because of a
pending drop on the TABLESPACE, but it's the tablename that is in the
message as that's what you are trying to create


Phil Grainger
CA
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Jay Reavill
Sent: 01 December 2005 20:51
To: [login to unmask email]
Subject: Re: [DB2-L] Odd Create Table Issue - DB2 V7 for z/OS

Well, I just noticed that the database name is DSNDB04 as I would
expect, but the error shows APPDBA as the first part of the name. So
maybe it is some other type of object.

----------------------------------------
Jay Reavill DBA
Certegy Card Services
11601 Roosevelt Blvd.
St. Petersburg, FL. 33716
Office (727) 227-2144
----------------------------------------





Jay

Reavill/Technology/STP/US/Cert To:
[login to unmask email]

[login to unmask email] cc:

Sent by: "DB2 Data Base Subject:
Re: [DB2-L] Odd Create Table Issue - DB2 V7 for z/OS

Discussion List"

<[login to unmask email]>





12/01/2005 03:33 PM

Please respond to "DB2

Database Discussion list at

IDUG"








I think you nailed it Chad! That's the only thing that makes sense so
far.
But I wonder why DB2 would try to use the same tablespace name for a
different table. I would think there would be a hashing routine or
something to make the name unique eventho the table names are the same.
Seems like this is an issue within DB2. I really don't think we're
trying to do something that shouldn't be allowed. Seems it's just a
limitation of DB2.

I ran another quick test creating both tables and this is the definition
in DB2...

SET CURRENT SQLID = 'APPDBA';
CREATE TABLE PPMS2D.TMP_FUNCTS LIKE PPMS2D.FUNCTS; CREATE TABLE
TMP_FUNCTS LIKE PPMS2D.FUNCTS;

---------+---------+---------+---------+---------+---------+---------+
NAME CREATOR TYPE DBNAME TSNAME DBID OBID
---------+---------+---------+---------+---------+---------+---------+
TMP_FUNCTS PPMS2D T DSNDB04 TMPRFUNC 4 49
TMP_FUNCTS APPDBA T DSNDB04 TMPR1SY@ 4 52

---------------------------------------------------------------------------------
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

Jay Reavill

Re: Odd Create Table Issue - DB2 V7 for z/OS
(in response to Phil Grainger)
Thanks Phil, that makes sense. It just had me wondering.

For Lock's benefit here's the DDL for the source table...

CREATE TABLE "PPMS2D"."FUNCTS"
( "FUNCTIONID" CHAR (30) NOT NULL
,"DESCR" CHAR (50) WITH DEFAULT NULL
,PRIMARY KEY
("FUNCTIONID"
)
)
IN "PSCS2D"."SCS00006"

It's pretty straight forward. It only has one index defined on column
functionid and no RI. Also, in the SQL Reference after talking about
datatypes and default values it has this to say...

"The implicit definition does not include any other attributes of the
identified table or view. For example, the new table does not have a
primary key or foreign key. The table is created in the table space
implicitly or explicitly specified by the IN clause, and the table has any
other optional clause only if the optional clause is specified."

So it would seem that it can't be anything other than the tablespace.

Thanks

----------------------------------------
Jay Reavill DBA
Certegy Card Services
11601 Roosevelt Blvd.
St. Petersburg, FL. 33716
Office (727) 227-2144
----------------------------------------




"Grainger, Phil"
<Phil.Grainger@C To: [login to unmask email]
A.COM> cc:
Sent by: "DB2 Subject: Re: [DB2-L] Odd Create Table Issue - DB2 V7 for z/OS
Data Base
Discussion List"
<[login to unmask email]
.ORG>


12/01/2005 03:55
PM
Please respond
to "DB2 Database
Discussion list
at IDUG"





The message is misleading. The create table has failed because of a
pending drop on the TABLESPACE, but it's the tablename that is in the
message as that's what you are trying to create


Phil Grainger
CA
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Jay Reavill
Sent: 01 December 2005 20:51
To: [login to unmask email]
Subject: Re: [DB2-L] Odd Create Table Issue - DB2 V7 for z/OS

Well, I just noticed that the database name is DSNDB04 as I would
expect, but the error shows APPDBA as the first part of the name. So
maybe it is some other type of object.

----------------------------------------
Jay Reavill DBA
Certegy Card Services
11601 Roosevelt Blvd.
St. Petersburg, FL. 33716
Office (727) 227-2144
----------------------------------------





Jay

Reavill/Technology/STP/US/Cert To:
[login to unmask email]

[login to unmask email] cc:

Sent by: "DB2 Data Base Subject:
Re: [DB2-L] Odd Create Table Issue - DB2 V7 for z/OS

Discussion List"

<[login to unmask email]>





12/01/2005 03:33 PM

Please respond to "DB2

Database Discussion list at

IDUG"








I think you nailed it Chad! That's the only thing that makes sense so
far.
But I wonder why DB2 would try to use the same tablespace name for a
different table. I would think there would be a hashing routine or
something to make the name unique eventho the table names are the same.
Seems like this is an issue within DB2. I really don't think we're
trying to do something that shouldn't be allowed. Seems it's just a
limitation of DB2.

I ran another quick test creating both tables and this is the definition
in DB2...

SET CURRENT SQLID = 'APPDBA';
CREATE TABLE PPMS2D.TMP_FUNCTS LIKE PPMS2D.FUNCTS; CREATE TABLE
TMP_FUNCTS LIKE PPMS2D.FUNCTS;

---------+---------+---------+---------+---------+---------+---------+
NAME CREATOR TYPE DBNAME TSNAME DBID OBID
---------+---------+---------+---------+---------+---------+---------+
TMP_FUNCTS PPMS2D T DSNDB04 TMPRFUNC 4 49
TMP_FUNCTS APPDBA T DSNDB04 TMPR1SY@ 4 52

---------------------------------------------------------------------------------

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








------------------------------------------------------------------------------
This message contains information from Certegy, Inc which may be confidential and privileged. If you are not an intended recipient, please refrain from any disclosure, copying, distribution or use of this information and note that such actions are prohibited. If you have received this transmission in error, please notify by e:mail [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

Basivi Inaganti

Re: Odd Create Table Issue - DB2 V7 for z/OS
(in response to Jay Reavill)
Hi Jay, I was deleted your original mail with SQL CODEs to explain with
your table names, but I would like to point two things here.

1) What happens when you create a table without an IN DATABASE clause or
IN database-name.tablespace-name (like IN "PSCS2D"."SCS00006").
DB2 implicitly creates a table space in DSNDB04 (assuming you don't
specify the database name). The implicitly created table space name truly
depends on your table name.
Creates the table with in table space created implicitly above.
2) What happen when you drop a your table that was created in implicitly
created table space.
Drops the table
Drop the corresponding implicitly created table space.

I hope this will help you understand your case. Because you are not using
the COMMIT between your SQL statements, the implicitly created table space
is in drop pending.

Thanks,
Basivi.

---------------------------------------------------------------------------------
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

Jay Reavill

Re: Odd Create Table Issue - DB2 V7 for z/OS
(in response to Basivi Inaganti)
Hi Basivi,

Thank you. Yes, thanks to the list I now realize that is what is
happening. I do however, still wonder why DB2 doesn't do the same check
when checking for an existing name as it does when defining the tablespace.
Since it is being implicitly defined we obviously don't care what the name
is. I can understand that it stops the duplicate definition incase the
drop is rolled back, but it should recognize the duplication up front and
generate a new name before it tries to define it.


----------------------------------------
Jay Reavill DBA
Certegy Card Services
11601 Roosevelt Blvd.
St. Petersburg, FL. 33716
Office (727) 227-2144
----------------------------------------




"Basivi Inaganti"
<[login to unmask email] To: [login to unmask email]
MCHASE.COM> cc:
Sent by: "DB2 Data Subject: Re: [DB2-L] Odd Create Table Issue - DB2 V7 for z/OS
Base Discussion List"
<[login to unmask email]>


12/01/2005 05:22 PM
Please respond to
"DB2 Database
Discussion list at
IDUG"






Hi Jay, I was deleted your original mail with SQL CODEs to explain with
your table names, but I would like to point two things here.

1) What happens when you create a table without an IN DATABASE clause or IN
database-name.tablespace-name (like IN "PSCS2D"."SCS00006").
DB2 implicitly creates a table space in DSNDB04 (assuming you don't
specify the database name). The implicitly created table space name
truly depends on your table name.
Creates the table with in table space created implicitly above.
2) What happen when you drop a your table that was created in implicitly
created table space.
Drops the table
Drop the corresponding implicitly created table space.

I hope this will help you understand your case. Because you are not using
the COMMIT between your SQL statements, the implicitly created table space
is in drop pending.

Thanks,
Basivi.
---------------------------------------------------------------------------------
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






------------------------------------------------------------------------------
This message contains information from Certegy, Inc which may be confidential and privileged. If you are not an intended recipient, please refrain from any disclosure, copying, distribution or use of this information and note that such actions are prohibited. If you have received this transmission in error, please notify by e:mail [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

Wayne Driscoll

Re: Odd Create Table Issue - DB2 V7 for z/OS
(in response to Jay Reavill)
Jay,
I believe that at create time of an implicit tablespace, it looks in the
catalog to see if there are collisions, and, since the drop is pending,
there are none, so it generates the tablespace name. DB2 then issues access
method calls to define the VSAM cluster. However, the define will fail,
because DB2 doesn't issue AMS calls to delete the VSAM cluster until the
COMMIT is performed. I believe that the -679 is due to the DEFINE CLUSTER
failure.
Wayne Driscoll
Product Developer
Western Metal Supply
NOTE: All opinions are strictly my own.


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Jay Reavill
Sent: Thursday, December 01, 2005 2:34 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Odd Create Table Issue - DB2 V7 for z/OS

I think you nailed it Chad! That's the only thing that makes sense so far.
But I wonder why DB2 would try to use the same tablespace name for a
different table. I would think there would be a hashing routine or
something to make the name unique eventho the table names are the same.
Seems like this is an issue within DB2. I really don't think we're trying
to do something that shouldn't be allowed. Seems it's just a limitation of
DB2.

I ran another quick test creating both tables and this is the definition in
DB2...

SET CURRENT SQLID = 'APPDBA';
CREATE TABLE PPMS2D.TMP_FUNCTS LIKE PPMS2D.FUNCTS; CREATE TABLE TMP_FUNCTS
LIKE PPMS2D.FUNCTS;

---------+---------+---------+---------+---------+---------+---------+
NAME CREATOR TYPE DBNAME TSNAME DBID OBID
---------+---------+---------+---------+---------+---------+---------+
TMP_FUNCTS PPMS2D T DSNDB04 TMPRFUNC 4 49
TMP_FUNCTS APPDBA T DSNDB04 TMPR1SY@ 4 52

In this case the tablespace names are different because DB2 recognized that
there was one already existing with the same name and generated a new name.
So it would appear that DB2 handles it correctly unless there is a drop
pending. In that case DB2 doesn't recognize that the drop pending name is
potentially still be used and assigns it to the new table. Probably because
the entry is no longer in the catalog, but just hasn't been committed yet.
However, it is aware of it at some level because it then gets the -679 when
attempting to define it. Seems like there is a check missing on DB2's part.
I would think it should recognize the drop pending and generate a new name.

Interesting!

Thanks!

---------------------------------------------------------------------------------
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

Jay Reavill

Re: Odd Create Table Issue - DB2 V7 for z/OS
(in response to Wayne Driscoll)
Thanks Wayne. Ok, that explains why DB2 doesn't know about it prior to
defining it. Guess it's too much to ask for DB2 to trap the failure when
it implicitly defined, rename, and try again. It's not a big issue. I
just found it puzzling and wanted to understand what was going on. Now I
believe I do.

Thanks again!

----------------------------------------
Jay Reavill DBA
Certegy Card Services
11601 Roosevelt Blvd.
St. Petersburg, FL. 33716
Office (727) 227-2144
----------------------------------------




"Wayne Driscoll"
<[login to unmask email] To: [login to unmask email]
SUPPLY.COM> cc:
Sent by: "DB2 Data Base Subject: Re: [DB2-L] Odd Create Table Issue - DB2 V7 for z/OS
Discussion List"
<[login to unmask email]>


12/02/2005 01:04 PM
Please respond to "DB2
Database Discussion list
at IDUG"





Jay,
I believe that at create time of an implicit tablespace, it looks in the
catalog to see if there are collisions, and, since the drop is pending,
there are none, so it generates the tablespace name. DB2 then issues
access
method calls to define the VSAM cluster. However, the define will fail,
because DB2 doesn't issue AMS calls to delete the VSAM cluster until the
COMMIT is performed. I believe that the -679 is due to the DEFINE CLUSTER
failure.
Wayne Driscoll
Product Developer
Western Metal Supply
NOTE: All opinions are strictly my own.


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Jay Reavill
Sent: Thursday, December 01, 2005 2:34 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Odd Create Table Issue - DB2 V7 for z/OS

I think you nailed it Chad! That's the only thing that makes sense so far.
But I wonder why DB2 would try to use the same tablespace name for a
different table. I would think there would be a hashing routine or
something to make the name unique eventho the table names are the same.
Seems like this is an issue within DB2. I really don't think we're trying
to do something that shouldn't be allowed. Seems it's just a limitation of
DB2.

I ran another quick test creating both tables and this is the definition in
DB2...

SET CURRENT SQLID = 'APPDBA';
CREATE TABLE PPMS2D.TMP_FUNCTS LIKE PPMS2D.FUNCTS; CREATE TABLE TMP_FUNCTS
LIKE PPMS2D.FUNCTS;

---------+---------+---------+---------+---------+---------+---------+
NAME CREATOR TYPE DBNAME TSNAME DBID OBID
---------+---------+---------+---------+---------+---------+---------+
TMP_FUNCTS PPMS2D T DSNDB04 TMPRFUNC 4 49
TMP_FUNCTS APPDBA T DSNDB04 TMPR1SY@ 4 52

In this case the tablespace names are different because DB2 recognized that
there was one already existing with the same name and generated a new name.
So it would appear that DB2 handles it correctly unless there is a drop
pending. In that case DB2 doesn't recognize that the drop pending name is
potentially still be used and assigns it to the new table. Probably
because
the entry is no longer in the catalog, but just hasn't been committed yet.
However, it is aware of it at some level because it then gets the -679 when
attempting to define it. Seems like there is a check missing on DB2's
part.
I would think it should recognize the drop pending and generate a new name.

Interesting!

Thanks!

---------------------------------------------------------------------------------

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








------------------------------------------------------------------------------
This message contains information from Certegy, Inc which may be confidential and privileged. If you are not an intended recipient, please refrain from any disclosure, copying, distribution or use of this information and note that such actions are prohibited. If you have received this transmission in error, please notify by e:mail [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