I have three scripts written in PL/SQL that I'm trying to port to DB2. I've managed to get through one of them but am having an issue with one of them. Inthe script there's a reference to an object caled USER_TAB_COLUMNS.

When I try to run the script, I get:
SQL0204N "AMROBI2.USER_TAB_COLUMNS" is an undefined name. SQLSTATE=42704

I can't seem to find this table anywhere. Here's the Oracle script:
INSERT INTO SD_CALENDAR(BUSINESS_NUM, NUM, SD_DAY, DAY_OF_WEEK, DAY_TYPE,
SD_YEAR, SD_MONTH)
SELECT 0 AS BUSINESS_NUM,
N AS NUM,
(TO_DATE('09/01/2003', 'MM/DD/YYYY') + N) AS SD_DAY,
MOD(N+1, 7) AS DAY_OF_WEEK,
CASE MOD(N+1, 7)
WHEN 0 THEN 1
WHEN 6 THEN 1
ELSE 0
END AS DAY_TYPE,
0 AS SD_YEAR,
0 AS SD_MONTH
FROM
(select (rownum-1) AS N from user_tAB_COLUMNS WHERE ROWNUM<1000) SEQ;
COMMIT;

--- UPDATE HERE HOLIDAY SCHEDULE FOR 2003 / 2004 ---------------

UPDATE SD_CALENDAR
SET BUSINESS_NUM = (SELECT COUNT(*) FROM SD_CALENDAR S1 WHERE S1.NUM <
SD_CALENDAR.NUM AND S1.DAY_TYPE=0),
SD_YEAR = EXTRACT(YEAR FROM SD_DAY),
SD_MONTH = EXTRACT(MONTH FROM SD_DAY);
COMMIT;

CREATE INDEX CALENDAR_DAY ON SD_CALENDAR(SD_DAY);
CREATE INDEX CALENDAR_NUM ON SD_CALENDAR(BUSINESS_NUM);
CREATE INDEX CALENDAR_YEAR ON SD_CALENDAR(SD_YEAR, SD_MONTH);

Any insight would be greatly appreciated.

Thanks

I think the corresponding table is SYSCAT.COLUMNS in DB2 ...

There is no function equivalent to oracle's ROWNUM ... The nearest I know of is a rownum function in db2 ...

If I understand your query correctly, I think you can do 'fetch first 1000 rows only '

HTH

sathyaram

Oops - I should have posted the script I was trying to run...sorry:

INSERT INTO SD_CALENDAR(BUSINESS_NUM, NUM, SD_DAY, DAY_OF_WEEK, DAY_TYPE,
SD_YEAR, SD_MONTH)
SELECT 0 AS BUSINESS_NUM,
N AS NUM,
(TO_DATE('09/01/2003', 'MM/DD/YYYY') + N) AS SD_DAY,
MOD(N+1, 7) AS DAY_OF_WEEK,
CASE MOD(N+1, 7)
WHEN 0 THEN 1
WHEN 6 THEN 1
ELSE 0
END AS DAY_TYPE,
0 AS SD_YEAR,
0 AS SD_MONTH
FROM
(select (ROW_NUMBER() over()-1) AS N from SYSIBM.SQLCOLUMNS WHERE ROW_NUMBER() over()<1000) SEQ;
COMMIT;

I think the "USER" prefix in Oracle is used to name the tables that contain data owned by an specific user. Try with the column "CREATOR" in DB2 to solve your queries.

Regards, Aloz.