Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253

    Unanswered: Translating ORacle to DB2

    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
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Translating ORacle to DB2

    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

    Originally posted by ansonee
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253
    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;
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  4. #4
    Join Date
    May 2003
    Location
    San Juan, PR
    Posts
    18
    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.

Posting Permissions

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