IC SunsetThe developerWorks Connections platform will be sunset on December 31, 2019. On January 1, 2020, this forum will no longer be available. More details available on our FAQ.
Topic
  • 5 replies
  • Latest Post - ‏2018-07-02T14:59:27Z by klough
klough
klough
9 Posts

Pinned topic MERGE

‏2018-06-13T20:15:15Z | count inserted or row updated

Just performed a MERGE sql operation in an RPGLE program. I had a subfile and I looped though it executing the merge operation on each subfile record. What I would like to know, is there is some easy way to get the number of rows inserted and updated at the end of the loop operation?

Thanks

Kevin

 

  • Jim_IT
    Jim_IT
    20 Posts
    ACCEPTED ANSWER

    Re: MERGE

    ‏2018-07-02T01:12:36Z  
    • klough
    • ‏2018-06-20T12:59:05Z

    Thanks Jim,

     

    I am trying to distinguish between the number of inserts and the number of updates.

    Unless I am misunderstanding the documentation,  the ROW_COUNT is giving me the combine total of updates and inserts.

    How do I distinguish between an insert and update.

    I want to be able to state for example 10 records inserted and 25 records updated from the Merge operation.

     

    Kevin

     

     

    Kevin,

    When you run a MERGE statement in STRSQL, the SQL Message SQL793E - MERGE statement complete is returned with additional text information for the number of rows inserted, updated & deleted. The tokens are available as well in condition-information-item: DB2_TOKEN_STRING.

     

    dcl-proc Diagnostics;
      exec sql GET DIAGNOSTICS
        :RowsCount =        ROW_COUNT;
      exec sql GET DIAGNOSTICS CONDITION 1
        :ReturnedSqlCode =  DB2_RETURNED_SQLCODE,
        :ReturnedSQLState = RETURNED_SQLSTATE,
        :MessageLength =    MESSAGE_LENGTH,
        :MessageText =      MESSAGE_TEXT,
        :TokenString =      DB2_TOKEN_STRING,
        :TokenCount =       DB2_TOKEN_COUNT,
        :MessageId =        DB2_MESSAGE_ID,
        :MessageId1 =       DB2_MESSAGE_ID1,
        :MessageId2 =       DB2_MESSAGE_ID2;
      if MessageId = 'SQL793E';
        StrPos = 1;
        EndPos = %scan(x'FF':TokenString:StrPos);
        MergeFile = %subst(TokenString:StrPos:EndPos-StrPos);
        StrPos = EndPos+1;
        EndPos = %scan(x'FF':TokenString:StrPos);
        MergeLib = %subst(TokenString:StrPos:EndPos-StrPos);
        StrPos = EndPos+1;
        EndPos = %scan(x'FF':TokenString:StrPos);
        InsertCount = %int(%subst(TokenString:StrPos:EndPos-StrPos));
        StrPos = EndPos+1;
        EndPos = %scan(x'FF':TokenString:StrPos);
        UpdateCount = %int(%subst(TokenString:StrPos:EndPos-StrPos));
        StrPos = EndPos+1;
        EndPos = %len(%trim(TokenString));
        DeleteCount = %int(%subst(TokenString:StrPos:EndPos-StrPos+1));
      endif;
    end-proc;

     

    Jim

    Updated on 2018-07-02T02:48:43Z at 2018-07-02T02:48:43Z by Jim_IT
  • Jim_IT
    Jim_IT
    20 Posts

    Re: MERGE

    ‏2018-06-19T16:17:58Z  

    It's very easy Kevin.

     

    exec sql GET DIAGNOSTICS        
      :RowsCount = ROW_COUNT;

     

     

    ROW_COUNT
    Identifies the number of rows associated with the previous SQL statement that 
    was executed. If the previous SQL statement is a DELETE, INSERT, REFRESH,
    or UPDATE statement, ROW_COUNT identifies the number of rows deleted,
    inserted, or updated by that statement, excluding rows affected by either
    triggers or referential integrity constraints. If the previous SQL statement is a
    MERGE statement, ROW_COUNT identifies the total number of rows deleted,
    inserted, and updated by that statement, excluding rows affected by either
    triggers or referential integrity constraints. If the previous SQL statement is a
    multiple-row-fetch, ROW_COUNT identifies the number of rows fetched.
    Otherwise, the value zero is returned.

     

    Jim

  • klough
    klough
    9 Posts

    Re: MERGE

    ‏2018-06-20T12:59:05Z  
    • Jim_IT
    • ‏2018-06-19T16:17:58Z

    It's very easy Kevin.

     

    exec sql GET DIAGNOSTICS        
      :RowsCount = ROW_COUNT;

     

     

    ROW_COUNT
    Identifies the number of rows associated with the previous SQL statement that 
    was executed. If the previous SQL statement is a DELETE, INSERT, REFRESH,
    or UPDATE statement, ROW_COUNT identifies the number of rows deleted,
    inserted, or updated by that statement, excluding rows affected by either
    triggers or referential integrity constraints. If the previous SQL statement is a
    MERGE statement, ROW_COUNT identifies the total number of rows deleted,
    inserted, and updated by that statement, excluding rows affected by either
    triggers or referential integrity constraints. If the previous SQL statement is a
    multiple-row-fetch, ROW_COUNT identifies the number of rows fetched.
    Otherwise, the value zero is returned.

     

    Jim

    Thanks Jim,

     

    I am trying to distinguish between the number of inserts and the number of updates.

    Unless I am misunderstanding the documentation,  the ROW_COUNT is giving me the combine total of updates and inserts.

    How do I distinguish between an insert and update.

    I want to be able to state for example 10 records inserted and 25 records updated from the Merge operation.

     

    Kevin

     

     

  • tonycate
    tonycate
    3 Posts

    Re: MERGE

    ‏2018-06-28T19:06:07Z  
    • klough
    • ‏2018-06-20T12:59:05Z

    Thanks Jim,

     

    I am trying to distinguish between the number of inserts and the number of updates.

    Unless I am misunderstanding the documentation,  the ROW_COUNT is giving me the combine total of updates and inserts.

    How do I distinguish between an insert and update.

    I want to be able to state for example 10 records inserted and 25 records updated from the Merge operation.

     

    Kevin

     

     

    This is what I had to do:

    start DBMonitor on your job. Make sure you specify the job your in, else DBMonitor could collect info on everything going on.

     command = 'STRDBMON OUTFILE(someLib/SQPERFMON) +                                           
                        TYPE(*SUMMARY) +                                                                
                        JOB('+ %char(sdsJobNbr) + '/' +                                                 
                               %trim(sdsUser)   + '/' +                                                 
                               %trim(sdsJobName) +')';    

     

    after each sql statement you execute:

    exec sql                                                                                   
                select case qqc11 when 'D' then 'DELETE'                                                
                                  when 'I' then 'INSERT'                                                
                                  when 'S' then 'SELECT'                                                
                                  when 'U' then 'UPDATE'                                                
                                  else qqc11                                                            
                                  end as operation                                                      
                  into :sqlOp                                                                           
                  from someLib/sqperfmon                                                                
                 where qqc103 = ::programName
                   and qqc11 not in('O','S')                                                            
                 order by qqtime desc                                                                   
                 fetch first 1 row only;  

     

    A little messy, but does work.

    Tony

  • Jim_IT
    Jim_IT
    20 Posts

    Re: MERGE

    ‏2018-07-02T01:12:36Z  
    • klough
    • ‏2018-06-20T12:59:05Z

    Thanks Jim,

     

    I am trying to distinguish between the number of inserts and the number of updates.

    Unless I am misunderstanding the documentation,  the ROW_COUNT is giving me the combine total of updates and inserts.

    How do I distinguish between an insert and update.

    I want to be able to state for example 10 records inserted and 25 records updated from the Merge operation.

     

    Kevin

     

     

    Kevin,

    When you run a MERGE statement in STRSQL, the SQL Message SQL793E - MERGE statement complete is returned with additional text information for the number of rows inserted, updated & deleted. The tokens are available as well in condition-information-item: DB2_TOKEN_STRING.

     

    dcl-proc Diagnostics;
      exec sql GET DIAGNOSTICS
        :RowsCount =        ROW_COUNT;
      exec sql GET DIAGNOSTICS CONDITION 1
        :ReturnedSqlCode =  DB2_RETURNED_SQLCODE,
        :ReturnedSQLState = RETURNED_SQLSTATE,
        :MessageLength =    MESSAGE_LENGTH,
        :MessageText =      MESSAGE_TEXT,
        :TokenString =      DB2_TOKEN_STRING,
        :TokenCount =       DB2_TOKEN_COUNT,
        :MessageId =        DB2_MESSAGE_ID,
        :MessageId1 =       DB2_MESSAGE_ID1,
        :MessageId2 =       DB2_MESSAGE_ID2;
      if MessageId = 'SQL793E';
        StrPos = 1;
        EndPos = %scan(x'FF':TokenString:StrPos);
        MergeFile = %subst(TokenString:StrPos:EndPos-StrPos);
        StrPos = EndPos+1;
        EndPos = %scan(x'FF':TokenString:StrPos);
        MergeLib = %subst(TokenString:StrPos:EndPos-StrPos);
        StrPos = EndPos+1;
        EndPos = %scan(x'FF':TokenString:StrPos);
        InsertCount = %int(%subst(TokenString:StrPos:EndPos-StrPos));
        StrPos = EndPos+1;
        EndPos = %scan(x'FF':TokenString:StrPos);
        UpdateCount = %int(%subst(TokenString:StrPos:EndPos-StrPos));
        StrPos = EndPos+1;
        EndPos = %len(%trim(TokenString));
        DeleteCount = %int(%subst(TokenString:StrPos:EndPos-StrPos+1));
      endif;
    end-proc;

     

    Jim

    Updated on 2018-07-02T02:48:43Z at 2018-07-02T02:48:43Z by Jim_IT
  • klough
    klough
    9 Posts

    Re: MERGE

    ‏2018-07-02T14:59:27Z  
    • Jim_IT
    • ‏2018-07-02T01:12:36Z

    Kevin,

    When you run a MERGE statement in STRSQL, the SQL Message SQL793E - MERGE statement complete is returned with additional text information for the number of rows inserted, updated & deleted. The tokens are available as well in condition-information-item: DB2_TOKEN_STRING.

     

    dcl-proc Diagnostics;
      exec sql GET DIAGNOSTICS
        :RowsCount =        ROW_COUNT;
      exec sql GET DIAGNOSTICS CONDITION 1
        :ReturnedSqlCode =  DB2_RETURNED_SQLCODE,
        :ReturnedSQLState = RETURNED_SQLSTATE,
        :MessageLength =    MESSAGE_LENGTH,
        :MessageText =      MESSAGE_TEXT,
        :TokenString =      DB2_TOKEN_STRING,
        :TokenCount =       DB2_TOKEN_COUNT,
        :MessageId =        DB2_MESSAGE_ID,
        :MessageId1 =       DB2_MESSAGE_ID1,
        :MessageId2 =       DB2_MESSAGE_ID2;
      if MessageId = 'SQL793E';
        StrPos = 1;
        EndPos = %scan(x'FF':TokenString:StrPos);
        MergeFile = %subst(TokenString:StrPos:EndPos-StrPos);
        StrPos = EndPos+1;
        EndPos = %scan(x'FF':TokenString:StrPos);
        MergeLib = %subst(TokenString:StrPos:EndPos-StrPos);
        StrPos = EndPos+1;
        EndPos = %scan(x'FF':TokenString:StrPos);
        InsertCount = %int(%subst(TokenString:StrPos:EndPos-StrPos));
        StrPos = EndPos+1;
        EndPos = %scan(x'FF':TokenString:StrPos);
        UpdateCount = %int(%subst(TokenString:StrPos:EndPos-StrPos));
        StrPos = EndPos+1;
        EndPos = %len(%trim(TokenString));
        DeleteCount = %int(%subst(TokenString:StrPos:EndPos-StrPos+1));
      endif;
    end-proc;

     

    Jim

    Thanks Jim,

     

    This is exactly what I was looking for.

     

    Kevin