Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2004
    Posts
    67

    Answered: Select to create an index

    DB2 9.7/10.5 for LUW
    Windows Server 2008/2012

    Is there a select that creates an index just like db2look does?

    I need to drop indexes and I want to have a backup of indexes to be dropped, so I want to have create index text based on a select rather than obtaining them via db2look.

  2. Best Answer
    Posted by mark.b

    "There is no such a standard select for that.
    Why don't you want to use Data Studio or db2look for that?"


  3. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    There is no such a standard select for that.
    Why don't you want to use Data Studio or db2look for that?
    Regards,
    Mark.

  4. #3
    Join Date
    Nov 2004
    Posts
    67
    When there are a lot of indexes that I have to drop, I would want to write

    SELECT ....
    FROM ...
    WHERE index_name in ('index1','index2')
    ...

    If this isn't possible I will search for another solution.

  5. #4
    Join Date
    Nov 2004
    Posts
    67
    I found a solution, using win-bash and gawk.

    Code:
    db2look -d database_name -t table_name -z schema_name -e -c -td ! | gawk "BEGIN{RS=\"!\";ORS=\"!\"} /CREATE/ && /INDEX/ {print}" | egrep -v DDL | tr -d "\n" | gawk "BEGIN {RS=\"!\";ORS=\"!\"\"\n\"}{gsub(/^[ \t]+|[ \t]+$/, \"\"); \$1=\$1; print}"
    Steps:
    • Extract information for a given schema and table, delimited by an exclamation mark.
    • Filter strings that contain CREATE and INDEX.
    • Exclude strings that contain DDL.
    • Deleting newlines.
    • Deleting whitespaces before, between and after fields.


    Some solutions found at http://www.catonmat.net/blog/awk-one...ined-part-two/ and http://serverfault.com/questions/391...reak-using-awk

    I am pretty sure this can be optimized.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •