Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2009
    Location
    Dusseldorf, Germany
    Posts
    3

    Unanswered: Multiple row insert

    Hallo,

    I have a question about DB2 9.1.6 for LUW:

    For DB2 UDB V8 for z/OS, an application program can issue a multiple-row insert using static SQL like this:

    EXEC SQL
    INSERT INTO DSN8810.ACT
    (ACTNO, ACTKWD, ACTDESC)
    VALUES (:HVA1, :HVA2, :HVA3)
    FOR :NUM-ROWS ROWS
    END-EXEC.

    I checked the manuals for DB2 9.1.6 for LUW, but I didn't find anything similar...

    Does a feature like this exist for this DB2 version?
    If not, will it be available in a later version?

    Thanks in advance and kind regards!

    Frank

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    No, it does not exist for DB2 for LUW. You will have to as IBM if it will ever be implemented.

    Andy

  3. #3
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    It is not a possible in DB2.

    All about insert:
    DB2 Universal Database

    Lenny

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    What you can do in LUW is the standard stuff:
    Code:
    INSERT INTO ...
    VALUES ( 1st-row ),
           ( 2nd-row ),
           ...;
    Or you use dynamic SQL and compose the SQL statement that way.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    There is "insert buf" bind parameter for "buffered insert". I've only used it with import in a multi-partitioned env. Don't know if this is similar to multiple-row insert on z/OS.

  6. #6
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Unhappy

    Quote Originally Posted by db2girl View Post
    There is "insert buf" bind parameter for "buffered insert". I've only used it with import in a multi-partitioned env. Don't know if this is similar to multiple-row insert on z/OS.
    He want to insert from the AREA which is not a possible.
    He can create a file and use LOAD utility to solve this problem.

    Lenny

  7. #7
    Join Date
    Feb 2009
    Posts
    114
    Yep, like I have always said, UNIX is only 30 years or so behind z/OS

  8. #8
    Join Date
    Nov 2009
    Location
    Bangalore
    Posts
    25
    Multi-row insert is supported. Checkout the docs

    Multi-row SQL operations with the IBM Data Server Driver for JDBC and SQLJ


    In case of CLI it is supported via SQLBulkOperations API
    SQLBulkOperations


    Regards,
    Sawan Gupta
    Last edited by sawangupta; 11-06-09 at 00:54. Reason: More Info

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The above was static SQL. No multi-row insert as on z/OS is available in DB2 LUW for that API.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  10. #10
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by stratmf View Post
    Hallo,

    I have a question about DB2 9.1.6 for LUW:

    For DB2 UDB V8 for z/OS, an application program can issue a multiple-row insert using static SQL like this:

    EXEC SQL
    INSERT INTO DSN8810.ACT
    (ACTNO, ACTKWD, ACTDESC)
    VALUES (:HVA1, :HVA2, :HVA3)
    FOR :NUM-ROWS ROWS
    END-EXEC.

    I checked the manuals for DB2 9.1.6 for LUW, but I didn't find anything similar...Frank
    Yes, static SQL does exist in DB2/LUW, just use sqlj instead of JDBC.
    This example,however, looks like cobol with inline SQL. That also works for LUW, even with open-source cobol compilers. Just install open-cobol on your linux-box (together with db2) and copy this source and call it "cblsql2.sqb"
    Code:
           identification division.
           program-id.     cblsql2.
           environment    division.
           input-output    section.
           file-control.
           data           division.
           file            section.
          *
           working-storage section.
           77   w-start-ws            pic X(08) value 'Start WS'.
           77   w-sqlcode             pic z(8)9+.
           77   w-dis-count           pic z(8)9+.
           77   displ-salary                pic -Z,ZZZ,ZZ9.99.   
           01   switsjes.
                03 sw-800-curs         pic   9.
                88 sw-800-curs-open     value 1.
                88 sw-800-curs-fets     value 2.
                88 sw-800-curs-clos     value 3.
     
          /     DB2 thingies  
           EXEC SQL
                include sqlca
           END-EXEC.
           EXEC SQL begin declare section        END-EXEC.
           01   w-userid            pic  x(08).
           01   w-password          pic  x(08). 
           01   w-count             pic S9(04)     comp-5.
           01   w-empno             pic  x(06)     value space.
           01   w-1st-name.
                49 w-1st-name-len           pic S9(04)     comp-5 
                                                           value zero.
                49 w-1st-name-dat           pic  x(12)     value space.
           01   w-birthdate         pic  x(10)     value space.
           01   w-workdept                  pic  x(03)     value space.
           01   w-workdept-NULL             pic S9(04)     comp-5.
           01   w-salary                    pic S9(7)V9(2) comp-3.
      
           01   k-empno-start             pic  x(06) value  low-value.
           01   k-empno-stop              pic  x(06) value high-value.
           EXEC SQL end    declare section        END-EXEC.
          /
           PROCEDURE DIVISION.
           000-000-main               section.
          ************************************
           000-010.
               perform 020-000-init-connect.
               perform 100-000-main-process.
               perform 090-000-exit-reset.
        000-090.
               stop run.
          /
           020-000-init-connect       section.
          ************************************
           020-010.
               EXEC SQL 
                    connect to sample 
               END-EXEC.
               if SQLCODE not equal zero 
               then move SQLCODE                   to w-sqlcode
                    display 'CONNECT failed with rc ' w-sqlcode
               else display 'CONNECT :)' 
               end-if.            
           020-090.
               exit.
          * 
           090-000-exit-reset         section.
          ************************************
           0090-010.
               EXEC SQL 
                    connect reset  
               END-EXEC.
               if SQLCODE not equal zero 
               then move SQLCODE                         to w-sqlcode
                    display 'reset CONNECT failed with rc ' w-sqlcode
               else display 'reset CONNECT :)' 
               end-if.            
    
           0090-090.
               exit.
    
           100-000-main-process       section.
          ************************************
           100-010. 
               set  sw-800-curs-open            to true.
               perform 800-000-process-emply-cursor.
               if SQLCODE equal zero 
               then set  sw-800-curs-fets       to true
                    perform 800-000-process-emply-cursor
                    perform until SQLCODE  not  equal zero
                        perform 110-000-process-emply-row  
                        perform 800-000-process-emply-cursor
                    end-perform
               end-if
               if SQLCODE equal +100  
               then set  sw-800-curs-clos  to true
                    perform 800-000-process-emply-cursor
               end-if. 
           100-090. 
               exit.
    
           110-000-process-emply-row  section.
          ************************************
           110-010.
               if w-workdept-NULL less 
               than zero
               then move space             to w-workdept
               end-if
               move w-salary               to displ-salary
               display      w-empno        space
                            w-1st-name-dat space
                            w-workdept     space
                            w-birthdate    space 
                            displ-salary.  
           110-090. 
               exit.
    
          /  CURSOR 
           800-000-process-emply-cursor section.
          *************************************
          * Declare
           EXEC SQL declare c8000 cursor for 
                    select           empno
                         ,           firstnme
                         ,           workdept  
                         ,           birthdate
                         ,  coalesce(salary,-1)  
                      from  employee
                     where  empno between :k-empno-start 
                                      and :k-empno-stop
                     order by firstnme  
           END-EXEC.
           800-010.
               evaluate true 
          * Open
                  when sw-800-curs-open
                       EXEC SQL 
                           open c8000
                       END-EXEC
          * Fetch
                  when sw-800-curs-fets
                       initialize w-1st-name 
                       EXEC SQL 
                          fetch c8000
                           into :w-empno
                              , :w-1st-name
                              , :w-workdept 
                                :w-workdept-NULL 
                              , :w-birthdate 
                              , :w-salary
                       END-EXEC
          * Close  
                  when other
                       EXEC SQL
                          close  c8000
                       END-EXEC                
               end-evaluate.
               if SQLCODE equal zero or +100
               then continue
               else move SQLCODE           to w-sqlcode
                    display 'sqlCode     : '  w-sqlcode   space
                    display 'sw-800-curs : '  sw-800-curs space
                    display 'sqlerrm     : '  sqlerrm     space
               end-if. 
           800-090.
               exit.
    This cobol program will read & display your employee table. Use this script to (pre)compile, link & bind this:
    Code:
    #!/bin/sh -x
    #
    rm  ./${1}.cbl
    rm  ./${1}
    db2 connect to sample
    db2 prep ${1}.sqb bindfile target ANSI_COBOL
    /usr/bin/cobc ${1}.cbl -t ${1}.lst -Wall -L${HOME}/sqllib/lib -ldb2 -v -x -save-temps
    db2 bind ${1}.bnd
    db2 connect reset
    ./${1}
    and there you have it: cobol with static sql on you PC just like you're used to on the mainframe.

Tags for this Thread

Posting Permissions

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