Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2007
    Posts
    1

    Unanswered: extracted referentiel intégrity from db2 catalog

    The following SQL request work, but l am not able to put the CHAR "," between more then one FK_COLNAMES. the output look like that:
    FOREIGN KEY (x,,,,,y) and I need FOREIGN KEY(x,y). Who can help me

    db2 +o -x +v +c -r/$DB2TMP/db2refint.bk "SELECT 'ALTER TABLE ' \
    ||RTRIM(SUBSTR(tabschema,1,10))|| '.' ||RTRIM(SUBSTR(TABNAME,1,50)), \
    'ADD CONSTRAINT ' || RTRIM(CONSTNAME),'FOREIGN KEY \
    (' ||REPLACE(RTRIM(LTRIM(substr(FK_COLNAMES,1,100))), ' ',',')|| ')', \
    'REFERENCES ' ||RTRIM(SUBSTR(tabschema,1,10)) || \
    '.' ||RTRIM(SUBSTR(REFTABNAME,1,17)) \
    ,' ON DELETE ' || case deleterule \
    when 'A' then 'NO ACTION' \
    when 'C' then 'CASCADE' \
    when 'N' then 'SET NULL' \
    when 'R' then 'RESTRICT' \
    end, \
    ' ON UPDATE ' || case updaterule \
    when 'A' then 'NO ACTION' \
    when 'R' then 'RESTRICT' \
    end \
    || ';' \
    FROM \
    SYSCAT.REFERENCES \
    where \
    tabschema like '${schema}%'"

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    It looks like you are trying to generate a script. Why not just edit it with your favorite text editor when it is generated by replacing ",," with "," several times until there is nothing that matches?

    Since FK_COLNAMES will be removed in future versions, you should use SYSCAT.KEYCOLUSE. Maybe you can come up with a subselect thaty does a pivot to do what you want.

    Andy

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Ii tried to tackle your problem. There is no simple solution. It seems DB2 reserves 20 characters per column in its FK_COLNAMES and PK_COLNAMES fields.

    The easiest solution to me seems to have the result reprocessed by a program to get the desired result. If that is not possible you will have to rely on recursive SQL.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    FK_COLNAMES is broken. As soon as you have longer column names or delimited column names, it doesn't work anymore. As Andy said, use SYSCAT.KEYCOLUSE instead. There you can use a simply recursive query to concatenate column names with ',' as separators (and enclosed in double-quotes for delimited names).
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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