Equivalent to PATINDEX in DB2

Ari_Weil
Ari_Weil used Ask the Experts™
on
I am writing a series of maintenance scripts to execute Runstats, generate various scripts, etc.  One of the scripts is to retrieve all foreign key associates from and to a particular table.  The db2look .out command is fine for finding the foreign keys FROM the table in question, but it falls short for foreign keys TO that table.  So I've got a script that queries the sysrels table via a formatted select statement, but I'm having to then take that script and either modify it by hand, or insert it into a SQL Server maintenance db I have and use PATINDEX there (I'd like to cut out the extra step if possible).  The problem is in the way the sysrels table keeps track of the PKCOLNAMES and PKCOLNAMES, they're listed and padded with spaces between the names, so there's nothing that I'm aware of in DB2, save a massive, complicated script using the LENGTH function on every column in the table in question, that will delimit the column names with spaces.  Any ideas?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi Ari
Please check the COLNAME,COLSEQ fields in the system view 'SYSCAT.KEYCOLUSE'. This will provide all the columns in the FK in seperate rows.

Hope this helps.

Thanks
Sumeet

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial