Antwort: [DB2-L] CREATE TABLE ABC.table like XYZ.table question

Roy Boxwell

Antwort: [DB2-L] CREATE TABLE ABC.table like XYZ.table question
nope the primary key, RI etc are not done and must be ALTERed afterwards.
Perhaps it would be easier to get a DDL generator from somewhere.....


Roy Boxwell
SOFTWARE ENGINEERING GMBH
-Product Development-
Robert-Stolz-Strasse 5
40470 Duesseldorf/Germany
Tel. +49 (0)211 96149-0
Fax +49 (0)211 96149-35
E-mail [login to unmask email]
Homepage www.seg.de

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

Max Scarpa

Re: CREATE TABLE ABC.table like XYZ.table question
(in response to Roy Boxwell)
Unluckily LIKE (or implicit definition) don't propagate PK:

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

(From SQL ref.)

HTH

Max 'The Devil wears Java' Scarpa

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

Michael Ebert

Re: CREATE TABLE ABC.table like XYZ.table question
(in response to Max Scarpa)
This is described in the SQL Reference under "CREATE TABLE", subtopic
"LIKE"-clause: only name and description (datatype, length, default,
comment, check constraints) of the columns are copied. No "external"
references like PK, FK or indexes are copied.

I hate to say this, but the best recommendation is probably "get a tool".
I've done some parts of the job; the most complete is a SAS job that was
supposed to replace the SAP utility RSDB2MAS, or another one that creates
DROP/CREATE VIEW statements, and another one (on the IDUG Code Place) that
drops/recreates FK and PK constraints in order to migrate them from V6 to
V7. So I know that if you try this yourself, you've got a really big job
ahead of you (the DROP/CREATE VIEW, for example, still suffers from the
problem that there's no easy way to find out as which user the CREATE has
been run - also either SAS or EDIT mangle up a few places in files with
sizes in the 100,000-line-range). You have to take into account

- tablespaces
- tables (mostly taken into account by CREATE LIKE - but special
consideration for Identity columns)
- comments and labels
- views, and views on views (I've simply used the creation times to
resolve dependencies, no need to analyse SYSVIEWDEP)
- indexes (date, time and timestamp values in partitioning index limitkey
clauses need quoting)
- FK and PK constraints
- GRANTs (remember column-level GRANTs)

Actually sounds like a fun project, if you've got sufficient time...

Dr. Michael Ebert
DB2 & Oracle Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany




"Peter, Georg" <[login to unmask email]>
To
[login to unmask email]
cc

bcc

Subject
[DB2-L] CREATE TABLE ABC.table like XYZ.table question





"Peter, Georg" <[login to unmask email]>
Please respond to DB2 Database Discussion list at IDUG
<[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
11-12-06 15:13


DB2 for z/OS v7

Fellow Collegues,

I have to "clone" 172 tables, 298 views and about 224 indexes for an
"extra-copy" of an existing application.

The worst thing is that I do not have a cloning tool. And I do not have
the complete DDL especially for the 172 CREATE TABLE's.

When I run "CREATE TABLE ABC.table like XYZ.table..." I will get an
exact copy of the table XYZ.table - correct ?

But what happens with the primary key of this table ? Will this primary
key build also automatically ? Or do I have to ALTER the table (= adding
a PK) later ?

Some good ideas for me ?

Thanks in advance for any advice you may be able to provide.

With kind regards - mit freundlichen Gruessen,
G e o r g H . P e t e r
------------------------------------------------------------------
Datenzentrale Baden-Wuerttemberg
Development and Product Support (E3)
Krailenshaldenstrasse 44, 70469 Stuttgart, Germany, Europe
e:mail [login to unmask email]
Phone 0049-711-8108-271
PC-Fax 004971189696071
Internet (only in german language):http://www.dzbw.de
----------------------------------------------------------------------
>"Next time you think you're perfect - try walk on water!"<

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

Mae Bruce

Re: CREATE TABLE ABC.table like XYZ.table question
(in response to Michael Ebert)
Maybe the DB2PLI8 program could help you some. It has a DDL option. It
isn't shipped with DB2 but it can be downloaded.
http://www-1.ibm.com/support/docview.wss?rs=64&context=SSEPEK&dc=DB520&u
id=swg21206998&loc=en_US&cs=UTF-8&lang=en

It uses a table name as input so you wouldn't just be able to give it a
database name and it create everything in the db; you would have to tell
it each table name and each view name (I think).

Hope this helps.
Mae

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Peter, Georg
Sent: Monday, December 11, 2006 8:13 AM
To: [login to unmask email]
Subject: [DB2-L] CREATE TABLE ABC.table like XYZ.table question


DB2 for z/OS v7

Fellow Collegues,

I have to "clone" 172 tables, 298 views and about 224 indexes for an
"extra-copy" of an existing application.

The worst thing is that I do not have a cloning tool. And I do not have
the complete DDL especially for the 172 CREATE TABLE's.

When I run "CREATE TABLE ABC.table like XYZ.table..." I will get an
exact copy of the table XYZ.table - correct ?

But what happens with the primary key of this table ? Will this primary
key build also automatically ? Or do I have to ALTER the table (= adding
a PK) later ?

Some good ideas for me ?

Thanks in advance for any advice you may be able to provide.

With kind regards - mit freundlichen Gruessen,
G e o r g H . P e t e r
------------------------------------------------------------------
Datenzentrale Baden-Wuerttemberg
Development and Product Support (E3)
Krailenshaldenstrasse 44, 70469 Stuttgart, Germany, Europe
e:mail [login to unmask email]
Phone 0049-711-8108-271
PC-Fax 004971189696071
Internet (only in german language):http://www.dzbw.de
----------------------------------------------------------------------
>"Next time you think you're perfect - try walk on water!"<


Abonnieren Sie den monatlichen Infobrief der Datenzentrale
Baden-Wurttemberg und erfahren Sie regelma?ig die neuesten Nachrichten
uber aktuelle Projekte und Entwicklungen. Melden Sie sich an mit diesem
Link http://www.datenzentrale.de/Info-Brief

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

Ray Williams

Re: CREATE TABLE ABC.table like XYZ.table question
(in response to Mae Bruce)
There is a "freebe" REXX called DB2LOOK (similar to the DB2LOOK UDB LUW
command) that I modified to work with DB2 V7. It will generate primary
keys. Look in IDUG Code Source.

Ray Williams
DBA - WHQKO
United Airlines
(847) 700-4359


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Bruce, Mae
Sent: Monday, December 11, 2006 9:39 AM
To: [login to unmask email]
Subject: Re: [DB2-L] CREATE TABLE ABC.table like XYZ.table question

Maybe the DB2PLI8 program could help you some. It has a DDL option. It
isn't shipped with DB2 but it can be downloaded.
http://www-1.ibm.com/support/docview.wss?rs=64&context=SSEPEK&dc=DB520&u
id=swg21206998&loc=en_US&cs=UTF-8&lang=en

It uses a table name as input so you wouldn't just be able to give it a
database name and it create everything in the db; you would have to tell
it each table name and each view name (I think).

Hope this helps.
Mae

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Peter, Georg
Sent: Monday, December 11, 2006 8:13 AM
To: [login to unmask email]
Subject: [DB2-L] CREATE TABLE ABC.table like XYZ.table question


DB2 for z/OS v7

Fellow Collegues,

I have to "clone" 172 tables, 298 views and about 224 indexes for an
"extra-copy" of an existing application.

The worst thing is that I do not have a cloning tool. And I do not have
the complete DDL especially for the 172 CREATE TABLE's.

When I run "CREATE TABLE ABC.table like XYZ.table..." I will get an
exact copy of the table XYZ.table - correct ?

But what happens with the primary key of this table ? Will this primary
key build also automatically ? Or do I have to ALTER the table (= adding
a PK) later ?

Some good ideas for me ?

Thanks in advance for any advice you may be able to provide.

With kind regards - mit freundlichen Gruessen,
G e o r g H . P e t e r
------------------------------------------------------------------
Datenzentrale Baden-Wuerttemberg
Development and Product Support (E3)
Krailenshaldenstrasse 44, 70469 Stuttgart, Germany, Europe
e:mail [login to unmask email]
Phone 0049-711-8108-271
PC-Fax 004971189696071
Internet (only in german language):http://www.dzbw.de
----------------------------------------------------------------------
>"Next time you think you're perfect - try walk on water!"<


Abonnieren Sie den monatlichen Infobrief der Datenzentrale
Baden-Wurttemberg und erfahren Sie regelma?ig die neuesten Nachrichten
uber aktuelle Projekte und Entwicklungen. Melden Sie sich an mit diesem
Link http://www.datenzentrale.de/Info-Brief

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