Insert bulk data in sql table.

We need to enter information into SQL tables in bulk and are currently using insert line by line and / or Load to load database tables .. The "add atomic" would be a third option ...

We would like to test the Atomic Add command in EGL, however we are not able to implement the code .. Has anyone ever implemented ??

Thank you

Osvaldo Menezes

After going back and forth ... could ..
We have seen examples in links below .. however the implementation was simpler than we think .. and it worked. Look.
 

a) First researched example, we try to implement in EGL without success.

http://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/apsg/src/tpc/db2z_insertmultiplerowshostvararray.html

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

b) Second researched example, we try to implement in EGL without success.

http://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/apsg/src/tpc/db2z_dynamicexecutedatachangestmt.html
/* Copy the INSERT string into the host variable sqlstmt */
strcpy(sqlstmt, "INSERT INTO DSN8B10.ACT VALUES (?, ?, ?)");

/* Copy the INSERT attributes into the host variable attrvar */
strcpy(attrvar, "FOR MULTIPLE ROWS");

/* Prepare and execute my_insert using the descriptor */
EXEC SQL PREPARE my_insert ATTRIBUTES :attrvar FROM :sqlstmt;
EXEC SQL EXECUTE my_insert USING DESCRIPTOR :*sqldaptr FOR :num_rows ROWS;

c) site also surveyed

   http://www.idug.org/p/bl/et/blogid=143&blogaid=369

d) finally implementation in successfully EGL ..
    After that declare an ARRAY with the instantiated type record like the SQL registration and add .. only that .. sample code ..

   function AddTabEnderPrinterAtomic(newRecordList TabEnderPrinter[], status StatusRec)
        myArray TabEnderPrinter[0];
        myArray.appendAll( newRecordList );

        for(i int from 1 to tam ) // Modify data for testing
             myArray[i].CODIGO_EMPRESA =  i;
            myArray[i].CODIGO_USUARIO =  ("OMENEZES" + i) ;
        end

        try

            add myArray  ;

            if(SysVar.sqlData.sqlCode == 100)
                syslib.writeStdout("SysVar.sqlData.sqlCode " + SysVar.sqlData.sqlCode);
                syslib.writeStdout("Status " + status.statusCode);
                HandleSuccess(status);
                commit();
            else
                if(SysVar.sqlData.sqlCode != 0)
                    syslib.writeStdout("SysVar.sqlData.sqlCode " + SysVar.sqlData.sqlCode);
                    syslib.writeStdout("Status " + status.statusCode);
                    HandleInvalidDBRecord(status);
                    rollback();
                else
                    commit();
                    HandleSuccess(status);
               end
            end
        onException(exp SQLException)
            syslib.writeStdout("AddTabEnderPrinterAtomic " + " COM EXCEPTION = " +
                            exp.sqlCode + " msg= " + exp.message);
            HandleException(status, exp);
            rollback();
        end

    end