Hi all

I need a DB2 function to Update a table and Insert some records to another table

Then return 1

No IF statements..

This will help me to further develop this function in future..

I have done this..

CREATE FUNCTION "DB2ADMIN"."DTV_SCHEDULE_PUBLISH1" ( "PERIOD" INTEGER )
RETURNS INTEGER
LANGUAGE SQL
MODIFIES SQL DATA
NO EXTERNAL ACTION
DETERMINISTIC

BEGIN ATOMIC
UPDATE DB2ADMIN.PROGRAMME_SCHEDULE_HDR_TEST
SET PUBLISHED_STATUS = 1 , INIT_PUBLISHED_DATE = CURRENT_DATE ,LAST_UPDATE_DATE = CURRENT_DATE
WHERE PERIOD = TO_CHAR (SCHEDULE_DATE, 'YYYYMM');

INSERT INTO DTV_SCHEDULE_PUBLISH_TEST (CHANNEL_ID,SCHEDULE_PERIOD,INIT_PUB_DATE,LAST_PUB _DATE,ACTIVE_STATUS,USER_ENTERED,DATE_ENTERED)
SELECT P.CHANNEL_ID,PERIOD,CURRENT_DATE,CURRENT_DATE,'1', CURRENT_USER,CURRENT_DATE FROM PROGRAMME_SCHEDULE_HDR_TEST P WHERE PERIOD = TO_CHAR (SCHEDULE_DATE, 'YYYYMM');

RETURN 1;

END;

But getting a error of..

DB2 Database Error: ERROR [42613] [IBM][DB2/NT] SQL0628N Multiple or conflicting keywords involving the "MODIFIES SQL DATA" clause are present. LINE NUMBER=18. SQLSTATE=42613

I was taken the help of this link [Example 4]

DB2 Database for Linux, UNIX, and Windows

Plz help me..

The manual states that MODIFIES SQL DATA can only be used for a function that returns a TABLE.

Andy

Make sure you reading the correct InfoCenter webpage for the release of DB2 you have. The link you provided is for V9 (which is 9.1). There are separate InfoCenters for V9R5 and V9R7.

Have a look at a recent discussion :

DB2 Function Return
DB2 Function Return

HTH

Sathyaram