Hi
I assume no one reading this today is eating turkey then?
The stuff in brackets following UNIQUE,PRIMARY KEY or FOREIGN KEY is a
column list, so it won't name the index.
Hal's recommendation (which is the same as mine a few months ago on this
list) is the way to go if you want to name indexes in line with site
standards and assign the index attributes you want.
However if the only concern is that constraint names and index names
differ, then the system generated names will guarantee that.
Regards
________
Alex Levy/Ireland/Contr/IBM
(Embedded IBM Certified Advanced Database
image moved to Administrator
file: - DB2 UDB V8 for Linux, Unix and
pic01009.gif) Windows
[Email address protected]
Tel. internal 504046
external +00353 (0)1 815 4046
"Gillis Mark "
<db2udbdba-ezmlms
hield-x28709652.x To
[Email Address Removed] "LazyDBA Discussion"
a.com> <[Email address protected]
cc
23/11/2006 10:29
Subject
RE: What are these indexes?
I'm happy to be corrected by the Senior Jedi out there, but you can
surely force a chosen name on an index in a CREATE TABLE statement with
format(s) like this:
CONSTRAINT Logbook_PK PRIMARY
KEY
(Logbook_Key) ,
CONSTRAINT Logbook_UK UNIQUE
(Brakes_Off) ,
CONSTRAINT Aircraft_FK
FOREIGN KEY
(Registration) REFERENCES Aircraft ON DELETE CASCADE,
What you can't do is use some of the other index functions like INCLUDE
and REVERSE SCAN, and it means the associated constraint name is defined
with the name of the index you supplied too. Is that what everyone
thinks happens? I'd be interested because there is some debate here at
the site I'm working at where index names and constraint names must be
different and the only way I can see to do that is to apply the commands
in the sequence Hal describes below.
Cheers
Mark Gillis
-----Original Message-----
From: hsteiner
[mailto:db2udbdba-ezmlmshield-x51642501.[Email address protected]
Sent: 22 November 2006 16:20
To: LazyDBA Discussion
Subject: Re: What are these indexes?
These are typically Primary key indexes (or UNIQUE constraint) generated
automatically by DB2 and given a name which looks like a string of
random numbers.
To find out what it is, try the command:
db2 "describe indexes for table myschema.tablname show detail"
To avoid this happening in the first place, don't use the PRIMARY KEY
clause or UNIQUE constraint in the CREATE TABLE statement, but rather,
db2 "create unique index myschema.inxname on myschema.tablname (col1,
col2) allow reverse scans"
then
db2 "alter table myschema.tablname add primary key (col1, col2)"
It will use the index you named (i.e. myschema.inxname) instead of
system generated name.
Best, Hal Steiner
-----Original Message-----
From: db2udbdba-ezmlmshield-x45581899.[Email address protected]
To: [Email address protected]
Sent: Wed, 22 Nov 2006 8:39 AM
Subject: What are these indexes?
I am running DB2 V8 fp12 and I see indexes with names such as:
SQL050510111916340 in SYSCAT.INDEXES. I understand they must be system
generated, but can anyone tell me what they are and why they are
created?
I am trying to find an index to sort on for reorg, but it won't allow me
to reorg on these indexes.
Thanks,
Andy
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
________________________________________________________________________
Check out the new AOL. Most comprehensive set of free safety and
security tools, free access to millions of high-quality videos from
across the web, free AOL Mail and more.
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
This e-mail (and any attachments) may contain privileged and/or
confidential information. If you are not the intended recipient please do
not disclose, copy, distribute, disseminate or take any action in reliance
on it. If you have received this message in error please reply and tell us
and then delete it. Should you wish to communicate with us by e-mail we
cannot guarantee the security of any data outside our own computer systems.
For the protection of Legal & General's systems and staff, incoming emails
will be automatically scanned.
Any information contained in this message may be subject to applicable
terms and conditions and must not be construed as giving investment advice
within or outside the United Kingdom.
The following companies are subsidiary companies of the Legal & General
Group Plc which are authorised and regulated by the Financial Services
Authority for advising and arranging the products shown: Legal & General
Partnership Services Limited (insurance and mortgages), Legal & General
Insurance Limited (insurance), Legal & General Assurance Society Limited
(life assurance, pensions and investments), Legal & General Unit Trust
Managers Limited and Legal & General Portfolio Management Services Limited
(investments).
They are registered in England under numbers shown.
The registered office is Temple Court, 11 Queen Victoria Street, London
EC4N 4TP.
Legal & General Partnership Services Limited: 5045000 Legal & General
Assurance Society Limited: 166055 Legal & General (Unit Trust Managers)
Limited: 1009418 Legal & General (Portfolio Management Services) Limited:
2457525 Legal & General Insurance Limited: 423930
They are registered with the Financial Services Authority under numbers
shown. You can check this at www.fsa.gov.uk/register
Legal & General Partnership Services Limited: 300792 Legal & General
Assurance Society Limited: 117659 Legal & General (Unit Trust Managers)
Limited: 119273 Legal & General (Portfolio Management Services) Limited:
146786 Legal & General Insurance Limited: 202050
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
DB2 & UDB email list listserv db2-l LazyDBA home page