DB2 - L

  • 1.  [Db2 z/OS] extended index on a table?

    Posted Oct 20, 2021 03:16 AM
    g'day, could someone please explain to me what on EARTH is an extended index on a table, as in 

    alter table SCHEMA.TABLENAME drop column COLUMN restrict
    [2021-10-19 15:15:35] [42817][-196] COLUMN SCHEMA.TABLENAME.COLUMN CANNOT BE DROPPED. REASON = 11. SQLCODE=-196, SQLSTATE=42817

    ???

    and the solution is to drop the three indexes referencing the TABLE (not the column), then the column can be dropped from the table.

    I did try and look it up honestly I did but can'T figure it out - is it really connected with the recent changes we had to make due to storage constraints, as would seem to be indicated by the sibylline-but-casual "If the table space is EA-enabled, the data sets for the index must be defined to belong to a DFSMS data class that has the extended format and addressability attributes" I find in the create index page of the manuals?

    TIA, scrartching my head...


    ------------------------------
    Aurora


    Stay safe and healthy, y'all
    ------------------------------


  • 2.  RE: [Db2 z/OS] extended index on a table?

    Posted Oct 20, 2021 03:30 AM
    Nah - Extended Indexes are normally for XML...

    Roy Boxwell

    SOFTWARE ENGINEERING GmbH and SEGUS Inc.
    -Product Development-

    Vagedesstrasse 19
    40479 Dusseldorf/Germany
    Tel. +49 (0)211 96149-675
    Fax +49 (0)211 96149-32
    Email: R.Boxwell@seg.de
    Web http://www.seg.de
    Link zur Datenschutzerklärung

    Software Engineering GmbH
    Amtsgericht Düsseldorf, HRB 37894
    Geschäftsführung: Gerhard Schubert, Ulf Heinrich




  • 3.  RE: [Db2 z/OS] extended index on a table?

    Posted Oct 20, 2021 04:04 AM
    well how do they come about, I mean how do you define an index as extended in the first place, or what does extended MEAN?

    ------------------------------
    Aurora


    Stay safe and healthy, y'all
    ------------------------------



  • 4.  RE: [Db2 z/OS] extended index on a table?

    Posted Oct 20, 2021 05:06 AM

    It means that someone created an XML index like this snippet from my test SQLs:

     

        CREATE INDEX                       "BOXWELL"."CUST_ZIP1" 

               ON "BOXWELL"."XMLCUSTOMER"                        

               ("INFO"                                            

               )                                                 

        GENERATE KEY USING XMLPATTERN                            

    '//pcode-zip'                                                 

        AS SQL DECFLOAT(34)                                      

               NOT CLUSTER                                       

               NOT PADDED                                        

               USING STOGROUP SYSDEFLT                           

     

     

    This XMLPATTERN is what causes extended index to be created. Very special beasts!

     

    Roy Boxwell

     

    SOFTWARE ENGINEERING GmbH and SEGUS Inc.

    -Product Development-

     

    Vagedesstrasse 19

    40479 Dusseldorf/Germany

    Tel. +49 (0)211 96149-675

    Fax +49 (0)211 96149-32

    Email: R.Boxwell@seg.de

    Web  http://www.seg.de

    Link zur Datenschutzerklärung

     

    Software Engineering GmbH

    Amtsgericht Düsseldorf, HRB 37894

    Geschäftsführung: Gerhard Schubert, Ulf Heinrich

     






  • 5.  RE: [Db2 z/OS] extended index on a table?

    Posted Oct 20, 2021 06:54 AM
    I think any index on expression counts as extended.

    Regards
    Neil






  • 6.  RE: [Db2 z/OS] extended index on a table?

    Posted Oct 20, 2021 07:08 AM
    Or Spatial I see...

    IX_EXTENSION_TYPE CHAR(1) NOT NULL WITH DEFAULT
    Identifies the type of extended index:
    N Node ID index
    S Index on a scalar expression
    T Spatial index
    V XML index
    Blank Simple index


    Roy Boxwell

    SOFTWARE ENGINEERING GmbH and SEGUS Inc.
    -Product Development-

    Vagedesstrasse 19
    40479 Dusseldorf/Germany
    Tel. +49 (0)211 96149-675
    Fax +49 (0)211 96149-32
    Email: R.Boxwell@seg.de
    Web http://www.seg.de
    Link zur Datenschutzerklärung

    Software Engineering GmbH
    Amtsgericht Düsseldorf, HRB 37894
    Geschäftsführung: Gerhard Schubert, Ulf Heinrich




  • 7.  RE: [Db2 z/OS] extended index on a table?

    Posted Oct 20, 2021 10:14 AM
    blimey, whole new world, they totally passed me by (I'm glad I'm not a developer).

    Thanks Roy and Neil!

    ------------------------------
    Aurora


    Stay safe and healthy, y'all
    ------------------------------