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}%'"

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

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.

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).