Results 1 to 4 of 4

Thread: Sysdummy1

  1. #1
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253

    Unanswered: Sysdummy1

    This may sound kinda goofy but I have no other place to turn.

    Currently, I'm developing some stired procedures that make use of the SYSIBM.SYSDUMMY1 "table". Here's a sample:

    LANGUAGE SQL
    RESULT SETS 1
    P1: BEGIN
    DECLARE decAIMRetrievedItemID DECIMAL(13,0);
    DECLARE SELECT_ALL CURSOR WITH RETURN FOR
    SELECT decAIMRetrievedItemID FROM SYSIBM.SYSDUMMY1;
    INSERT INTO AIM.AIMRetrievedItem (ArchiveRetrievalID, AIMDocumentID, CaptureDate, AccountNumber, PostingAccountNumber, RoutingTransitNumber, SequenceNumber, Location, "SERVER", Folder, WaiveFee)
    VALUES
    (decArchiveRetrievalID, strAIMDocumentID, dateCaptureDate, strAccountNumber, strPostingAccountNumber, strRoutingNumber, intSequenceNumber, strLocation, strServer, strFolder, strWaiveFee);
    SET decAIMRetrievedItemID = IDENTITY_VAL_LOCAL();
    OPEN SELECT_ALL;
    END P1

    We do this instead of using an output parameter for the new identity.

    My database group guys are freaking out about needing to grant me select permission for SYSDUMMY1. I have told them time amd time again, it's not a real table!!! But to no avail.

    Could someone (or all of you) provide an explanation as to exactly what SYSDUMMY is and what it is meant for? I've tried to tell them but they just don't get it.

    Any help would be asppreciated.

    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: Sysdummy1

    You can use VALUES instead ...

    VALUES(decAIMRetrievedItemID)

    HTH

    sathyaram
    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
    Have used VALUES before in ad hoc sql statements, but have never used within a stored proc. Here's what I tried - unsuccessfully:

    CREATE PROCEDURE DB2ADMIN.PROCEDURE2 (IN intBusinessDays INTEGER)
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    RESULT SETS 1
    LANGUAGE SQL
    P1: BEGIN
    DECLARE BusinessDate DATE;
    --DECLARE SELECT_ALL CURSOR WITH RETURN FOR SELECT BusinessDate FROM SYSIBM.SYSDUMMY1;
    SET BusinessDate = (AIM.NEXT_BUS_DATE (DATE(CURRENT TIMESTAMP), intBusinessDays));
    --OPEN SELECT_ALL;
    VALUES(BusinessDate)
    END P1

    Here's the error:

    DB2ADMIN.PROCEDURE2: 12: [IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token "VALUES(BusinessDate)" was found following " --OPEN SELECT_ALL; ". Expected tokens may include: "<psm_sql_stmt>". LINE NUMBER=12. SQLSTATE=42601

    DB2ADMIN.PROCEDURE2 - Build failed.
    DB2ADMIN.PROCEDURE2 - Roll back completed successfully.

    ????????
    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
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106
    I believe VALUES will not work in a procedure. If your DBAs don't want to grant SELECT to sysdummy1 (I don't understand their reasoning not to), then create your own table (you can even define a view):

    create view dummy
    (dummy)
    as
    values (1)
    @

    Originally posted by ansonee
    Have used VALUES before in ad hoc sql statements, but have never used within a stored proc. Here's what I tried - unsuccessfully:

    CREATE PROCEDURE DB2ADMIN.PROCEDURE2 (IN intBusinessDays INTEGER)
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    RESULT SETS 1
    LANGUAGE SQL
    P1: BEGIN
    DECLARE BusinessDate DATE;
    --DECLARE SELECT_ALL CURSOR WITH RETURN FOR SELECT BusinessDate FROM SYSIBM.SYSDUMMY1;
    SET BusinessDate = (AIM.NEXT_BUS_DATE (DATE(CURRENT TIMESTAMP), intBusinessDays));
    --OPEN SELECT_ALL;
    VALUES(BusinessDate)
    END P1

    Here's the error:

    DB2ADMIN.PROCEDURE2: 12: [IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token "VALUES(BusinessDate)" was found following " --OPEN SELECT_ALL; ". Expected tokens may include: "<psm_sql_stmt>". LINE NUMBER=12. SQLSTATE=42601

    DB2ADMIN.PROCEDURE2 - Build failed.
    DB2ADMIN.PROCEDURE2 - Roll back completed successfully.

    ????????

Posting Permissions

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