Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Sep 2011
    Posts
    220

    Unanswered: multiple insertion at a time

    Hi,

    DB2 9.5 Z/OS


    I have a question in in the INSERT statement

    i receives the VOL_TEXT values as a String in the form of having one seperator
    such as

    WS-VOL-TEXT variable is having the values like below

    CHERRY/FIG/YOGURT/BANANA

    we should split the value based on the Seperator ( / ) and has to insert to the
    BASE_TABLE like below(Expected Table value after insertion)
    Code:
    P_LOC        NO_FIRST   VOL_TEXT    UPDATION_TIME 
    AAA            111       CHERRY     2013-03-26 10:27:31.136815  
    AAA            111       FIG        2013-03-26 10:27:31.136816         
    AAA            111       YOGURT     2013-03-26 10:27:31.136817
    AAA            111       BANANA     2013-03-26 10:27:31.136818
    We have to insert the values as 4 records with different timestamp(can be increase one nano second) at one shot


    keys

    P_LOC char(3)
    NO_FIRST INTEGER
    VOL_TEXT VARCHAR(15)
    UPDATION_TIME TIMESTAMP

    P_LOC AND NO_FIRST values are always same like AAA and 111

    Code:
    INSERT INTO BASE_TABLE
    (P_LOC,
    NO_FIRST,
    VOL_TEXT,
    UPDATION_TIME)
    VALUES
    ('AAA',
    111,
    :WS-VOL-TEXT,
    CURRENT TIMESTAMP
    )
    How can i write a INSERT query to perform above..Please help

    Thanks,

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    An example.
    (Tested on DB2 9.7 for Windows)

    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE base_table
    ( p_loc         CHAR(3)     NOT NULL
    , no_first      INTEGER     NOT NULL
    , vol_text      VARCHAR(15)
    , updation_time TIMESTAMP
    );
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.

    Code:
    ------------------------------ Commands Entered ------------------------------
    INSERT INTO base_table
    ( p_loc
    , no_first
    , vol_text
    , updation_time
    )
    WITH
     separate_text( k , vol_text , remainder ) AS (
    SELECT 0
         , CAST('' AS VARCHAR(15) )
         , /* :WS-VOL-TEXT || '/' */
           'CHERRY/FIG/YOGURT/BANANA' || '/'
     FROM  sysibm.sysdummy1
    UNION ALL
    SELECT k + 1
         , LEFT(remainder , p - 1)
         , SUBSTR(remainder , p + 1)
     FROM  (SELECT s.*
                 , LOCATE('/' , remainder) AS p
             FROM  separate_text AS s
             WHERE k < 10
           )
     WHERE p > 0
    )
    SELECT 'AAA'
         , 111
         , vol_text
         , CURRENT TIMESTAMP + k MICROSECONDS
     FROM  separate_text
     WHERE k > 0
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.

    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM base_table;
    ------------------------------------------------------------------------------
    
    P_LOC NO_FIRST    VOL_TEXT        UPDATION_TIME             
    ----- ----------- --------------- --------------------------
    AAA           111 CHERRY          2013-03-27-18.15.22.804001
    AAA           111 FIG             2013-03-27-18.15.22.804002
    AAA           111 YOGURT          2013-03-27-18.15.22.804003
    AAA           111 BANANA          2013-03-27-18.15.22.804004
    
      4 record(s) selected.

  3. #3
    Join Date
    Sep 2011
    Posts
    220
    Thanks tonkuma...
    Could please give the query with out 'WITH' clause..because our DB2 doesnot support WITH clause

    DB2 9.5 Z/OS

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    DB2 9.1 for z/OS supports the syntax, according to the Information Center.

    DB2 9 - DB2 SQL - INSERT

    Syntax

    Code:
    >>-INSERT INTO--+-table-name-+--+-----------------------+------->
                    '-view-name--'  |    .-,-----------.    |   
                                    |    V             |    |   
                                    '-(----column-name-+--)-'   
    
    >--+--------------------+--+-----------------------+------------>
       '-| include-column |-'  '-OVERRIDING USER VALUE-'   
    
    >--+-VALUES-+-+-expression-+---------+-----------------------------------------------------------------+-><
       |        | +-DEFAULT----+         |                                                                 |   
       |        | '-NULL-------'         |                                                                 |   
       |        |   .-,--------------.   |                                                                 |   
       |        |   V                |   |                                                                 |   
       |        '-(---+-expression-+-+-)-'                                                                 |   
       |              +-DEFAULT----+                                                                       |   
       |              '-NULL-------'                                                                       |   
       +-+-----------------------------------+--fullselect--+----------------------+--+------------------+-+   
       | |       .-,-----------------------. |              '-| isolation-clause |-'  '-QUERYNO--integer-' |   
       | |       V                         | |                                                             |   
       | '-WITH----common-table-expression-+-'                                                             |   
       '-| multiple-row-insert |---------------------------------------------------------------------------'
    By the way,
    Is your DB2 really for z/OS?
    Because, I didn't see DB2 9.5 for z/OS.
    I saw DB2 Version 9.1 for z/OS, DB2 10 for z/OS, ...

  5. #5
    Join Date
    Sep 2011
    Posts
    220
    Thanks tonkuma..Usually I will execute the query in IBM DB2 Command Editor tool (DB2 V 9.7)..before the program ..yes my Z/os DB2 version is 9 release 1

  6. #6
    Join Date
    Sep 2011
    Posts
    220
    I ran the below query in the IBM DB2 Command Editor tool
    Code:
    INSERT INTO base_table
    ( p_loc
    , no_first
    , vol_text
    , updation_time
    )
    WITH
     separate_text( k , vol_text , remainder ) 
    AS (
    SELECT 0
         , CAST('' AS VARCHAR(15) )
         ,'CHERRY/FIG/YOGURT/BANANA' || '/'
     FROM  sysibm.sysdummy1
    UNION ALL
    SELECT k + 1
         , LEFT(remainder , p - 1)
         , SUBSTR(remainder , p + 1)
     FROM  (SELECT s.*
                 , LOCATE('/' , remainder) AS p
             FROM  separate_text AS s
             WHERE k < 10
           )
     WHERE p > 0
    )
    SELECT 'AAA'
         , 111
         , vol_text
         , CURRENT TIMESTAMP + k MICROSECONDS
     FROM  separate_text
     WHERE k > 0
    got below error
    Code:
    sqlcode: -104
    
    sqlstate: 42601
    SQL0104N  An unexpected token "<EMPTY>" was found following "".  Expected 
    tokens may include:  "CORRELATION NAME".  SQLSTATE=42601
    
    SQL0104N  An unexpected token "<EMPTY>" was found following "".  Expected tokens may include:  "CORRELATION NAME".
    Thanks,

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please try by adding a correlation name.

    Note: AFAIK, correlation name for a nested-table-expression is optional only on DB2 9.7 or later for LUW.
    Code:
    ...
    UNION ALL
    SELECT k + 1
         , LEFT(remainder , p - 1)
         , SUBSTR(remainder , p + 1)
     FROM  (SELECT s.*
                 , LOCATE('/' , remainder) AS p
             FROM  separate_text AS s
             WHERE k < 10
           ) AS q
     WHERE p > 0
    )
    ...

  8. #8
    Join Date
    Sep 2011
    Posts
    220
    Thanks, I ran the same query with the latest changes of correlation name,It throws some other error like below...

    "SQL0346N An invalid reference to common table expression "SEPARATE_TEXT"
    occurs in the first fullselect, as a second occurrence in the same FROM clause,
    or in the FROM clause of a subquery. SQLSTATE=42836
    "
    sqlcode: -346

    sqlstate: 42836

    Thanks,

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    On what DB2 version/release and platform OS did you run?

    Because, it worked on my environment.
    I already showed my test result, like the following quate.
    Quote Originally Posted by tonkuma View Post
    An example.
    (Tested on DB2 9.7 for Windows)

    ...
    ...

    Code:
    ------------------------------ Commands Entered ------------------------------
    INSERT INTO base_table
    ( p_loc
    , no_first
    , vol_text
    , updation_time
    )
    WITH
     separate_text( k , vol_text , remainder ) AS (
    SELECT 0
         , CAST('' AS VARCHAR(15) )
         , /* :WS-VOL-TEXT || '/' */
           'CHERRY/FIG/YOGURT/BANANA' || '/'
     FROM  sysibm.sysdummy1
    UNION ALL
    SELECT k + 1
         , LEFT(remainder , p - 1)
         , SUBSTR(remainder , p + 1)
     FROM  (SELECT s.*
                 , LOCATE('/' , remainder) AS p
             FROM  separate_text AS s
             WHERE k < 10
           )
     WHERE p > 0
    )
    SELECT 'AAA'
         , 111
         , vol_text
         , CURRENT TIMESTAMP + k MICROSECONDS
     FROM  separate_text
     WHERE k > 0
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    ...
    ...
    So, the error might depend on your environment.

    Please read the error message carefully.
    "SQL0346N An invalid reference to common table expression "SEPARATE_TEXT"
    occurs in the first fullselect, as a second occurrence in the same FROM clause,
    or in the FROM clause of a subquery. SQLSTATE=42836
    "
    If you couldn't understand some words/phrases/sentenses,
    please ask that part.

  10. #10
    Join Date
    Feb 2008
    Posts
    5
    I am testing your query in DB2 z/os 9.1 got the following error:

    ......................
    FROM (SELECT S.*
    , LOCATE('/' , REMAINDER) AS P
    FROM SEPARATE_TEXT AS S
    WHERE K < 10
    ) Z
    WHERE P > 0

    ELECT 'AAA'
    , VOL_TEXT
    , CURRENT TIMESTAMP + K MICROSECONDS
    FROM SEPARATE_TEXT
    WHERE K > 0;;
    -------+---------+---------+---------+---------+---------+---------+--
    SQLCODE = -346, ERROR: AN INVALID REFERENCE TO COMMON TABLE
    EXPRESSION SEPARATE_TEXT OCCURS IN THE FIRST FULLSELECT, AS A Second OCCURRENCE IN THE SAME FROM CLAUSE, OR IN THE FROM CLAUSE OF A SUBQUERY

    is there is a way around?
    Thank's in advance.



    Quote Originally Posted by tonkuma View Post
    Please try by adding a correlation name.

    Note: AFAIK, correlation name for a nested-table-expression is optional only on DB2 9.7 or later for LUW.
    Code:
    ...
    UNION ALL
    SELECT k + 1
         , LEFT(remainder , p - 1)
         , SUBSTR(remainder , p + 1)
     FROM  (SELECT s.*
                 , LOCATE('/' , remainder) AS p
             FROM  separate_text AS s
             WHERE k < 10
           ) AS q
     WHERE p > 0
    )
    ...

  11. #11
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    spoon feeding is still really loved by many adults..
    if you have a problem the least I would do is to see for the cause of the error, looking at the message, trying to understand.. instead of posting with your eyes closed
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I thought something same as przytula_guy.

    I already wrote (though, it was response to Billa007)
    Quote Originally Posted by tonkuma View Post
    ...
    ...

    Please read the error message carefully.
    "SQL0346N An invalid reference to common table expression "SEPARATE_TEXT"
    occurs in the first fullselect, as a second occurrence in the same FROM clause,
    or in the FROM clause of a subquery. SQLSTATE=42836
    "
    If you couldn't understand some words/phrases/sentenses,
    please ask that part.
    The error text might be slightly different,
    you got same error code
    SQLCODE = -346, ERROR: AN INVALID REFERENCE TO COMMON TABLE
    EXPRESSION SEPARATE_TEXT OCCURS IN THE FIRST FULLSELECT, AS A Second OCCURRENCE IN THE SAME FROM CLAUSE, OR IN THE FROM CLAUSE OF A SUBQUERY
    Please read the error message carefully, and try to understand.
    If you couldn't understand some words/phrases/sentenses,
    please ask that part.

  13. #13
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I apprehensive about one of lenygold or Billa007 couldn't got a required solution(SQL statement).

    If so,
    Would you please give the situations of you?
    I might had some hints to get the results.

  14. #14
    Join Date
    Sep 2011
    Posts
    220
    Thanks tonkuma,,I stuggled lot to get a result but i could not get..Please help if you have any solution..
    Thanks,

  15. #15
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Though, I have no z/OS test environment,
    looking error text..
    (1)
    AN INVALID REFERENCE TO COMMON TABLE EXPRESSION SEPARATE_TEXT
    OCCURS IN THE FIRST FULLSELECT, AS A Second OCCURRENCE IN THE SAME FROM CLAUSE, OR IN THE FROM CLAUSE OF A SUBQUERY
    The problem may be INVALID REFERENCE TO SEPARATE_TEXT.

    (2)
    AN INVALID REFERENCE TO COMMON TABLE EXPRESSION SEPARATE_TEXT
    OCCURS IN THE FIRST FULLSELECT, AS A Second OCCURRENCE IN THE SAME FROM CLAUSE, OR IN THE FROM CLAUSE OF A SUBQUERY
    (2-1) THE FIRST FULLSELECT:
    SEPARATE_TEXT was not used in THE FIRST FULLSELECT.
    So, this may be No.

    (2-2) AS A Second OCCURRENCE IN THE SAME FROM CLAUSE:
    SEPARATE_TEXT was referenced once inside the COMMON TABLE EXPRESSION.
    This may be also No.

    (2-3) IN THE FROM CLAUSE OF A SUBQUERY:
    Yes! It was used in the subquery Z(or q in my example).


    The subquery was used to avoid the repetition of the expression "LOCATE('/' , remainder)".
    So, please try by replacing all "p" with the expression and removing the subquery.

Posting Permissions

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